Excel 数据导入导出

//导出控件Aspose.Cells 必须引用该控件

using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Configuration;
using System.Collections.Generic;

using System.IO;
using Aspose.Cells;




namespace BLL
{
    public class ImportExportExcel
    {
        private static string outFileName = "";
        private static Workbook workbook = null;
        private static Worksheet worksheet = null;

        private static void AddTitle(string title, int columnCount)
        {   //创建标题
            //合并单元格
            worksheet.Cells.Merge(0, 0, 1, columnCount);
            worksheet.Cells.Merge(1, 0, 1, columnCount);
            Cell cell_title = worksheet.Cells[0, 0];
            Cell cell_date  = worksheet.Cells[1, 0];

            cell_title.PutValue(title, true);
            cell_title.Style.HorizontalAlignment = TextAlignmentType.Center;
            cell_title.Style.Font.Name = "黑体";
            cell_title.Style.Font.Size = 18;
            cell_title.Style.Font.IsBold = true;
            cell_date.PutValue("查询时间：" + DateTime.Now.ToLocalTime(), true);
            cell_date.SetStyle(cell_title.Style);
        }

        private static void AddHeader(DataTable dt)
        {   //生成列名标题
            Cell cell = null;
            for (int col = 0; col < dt.Columns.Count; col++)
            {
                cell = worksheet.Cells[2, col];
                cell.PutValue(dt.Columns[col].ColumnName);
                cell.Style.Font.IsBold = true;
            }
        }

        private static void AddBody(DataTable dt)
        {   //生成指定数据到EXCEL中；
            long ll_count;
            ll_count = dt.Rows.Count;
            for (int r = 0; r < ll_count; r++)
            {
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    worksheet.Cells[r + 3, c].PutValue(dt.Rows[r][c].ToString());
                }
            }
        }

        public static string DatatableToExcel(DataTable dt, string fileTitle, string sheetName,string fileName)
        {

            try
            {
                workbook = new Workbook();
                workbook.Worksheets.Clear();
                string ls_path_dir;
                ls_path_dir = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
                string ls_filename;
                string ls_filepath;
                //按时间生成指定文件名
                ls_filename = fileName + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                //生成文件在服务器的地址
                ls_filepath = ls_path_dir + "Exportfile\\" + ls_filename;
                //判断文件是否存在，存在则删除文件；

                if (File.Exists(ls_filepath))
                { File.Delete(ls_filepath); }

                if(sheetName.Length == 0)
                { sheetName = "Sheet"; }

                workbook.Worksheets.Add(sheetName);
                worksheet = workbook.Worksheets[0];

                //worksheet.Name = sheetName;
                outFileName = ls_filepath;

                AddTitle(fileTitle, dt.Columns.Count);
                AddHeader(dt);
                AddBody(dt);
                //保存生成文件
                worksheet.AutoFitColumns();
                worksheet.AutoFitRows();
                workbook.Save(outFileName);
                return ls_filepath;
            }
            catch (SystemException)

            { return "";
            }
        }


        public static DataTable ExcelToDatatable(string excelFileName)
        {
            Workbook book = new Workbook();
            book.Open(excelFileName);
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;
            //获取excel中的数据保存到一个datatable中
            DataTable dt_Import = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, false);
            //dt_Import.
            return dt_Import;
        }
    }
}