c#操作Excel整理总结

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using Aspose.Cells;

namespace MusicgrabTool
{
    public class FileExport
    {
        #region 公共参数

        /// <summary>
        /// 文件名
        /// </summary>
        public string ExportFilePath { get; set; }

        /// <summary>
        /// 标签名
        /// </summary>
        public string FileName { get; set; }

        #endregion

        #region 写入Excel数据

        /// <summary>
        /// 写入数据到Excel中
        /// </summary>
        /// <param name="dataSource">数据源</param>
        /// <param name="sheetName">标签名</param>
        /// <param name="headNames">列名</param>
        /// <returns></returns>
        public bool ExportExcel(List<string[]> dataSource, string sheetName, string[] headNames)
        {
            //转DataTable
            try
            {
                if (!Directory.Exists(ExportFilePath))
                {
                    Directory.CreateDirectory(ExportFilePath);
                }

                if (dataSource.Count == 0)
                {
                    return true;
                }
                DataTable dt = new DataTable();
                foreach (string headName in headNames)
                {
                    dt.Columns.Add(headName);
                }

                //如果大于100W
                if (dataSource.Count > 1000000)
                {
                    for (int i = 0; i < 1000000; i++)
                    {
                        DataRow row = dt.NewRow();
                        for (int k = 0; k < dataSource[i].Length; k++)
                        {
                            row[k] = dataSource[i][k];
                        }
                        dt.Rows.Add(row);
                    }
                    WriteToExcel(dt, sheetName + "01", headNames);

                    //100W 写入完成
                    dt.Rows.Clear();
                    for (int i = 1000000; i < dataSource.Count; i++)
                    {
                        DataRow row = dt.NewRow();
                        for (int k = 0; k < dataSource[i].Length; k++)
                        {
                            row[k] = dataSource[i][k];
                        }
                        dt.Rows.Add(row);
                    }
                    WriteToExcel(dt, sheetName + "02", headNames);
                }
                else
                {
                    foreach (string[] strings in dataSource)
                    {
                        DataRow row = dt.NewRow();
                        for (int i = 0; i < strings.Length; i++)
                        {
                            row[i] = strings[i];
                        }
                        dt.Rows.Add(row);
                    }
                    WriteToExcel(dt, sheetName, headNames);
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 将数据导出到Excel文件
        /// </summary>
        /// <returns></returns>
        private void WriteToExcel(DataTable source, string sheetName, string[] headNames)
        {
            Workbook workBook = new Workbook();
            Worksheet workSheet = null;
            string str = string.Format("{0}\\{1}", ExportFilePath.Trim(‘/‘).Trim(‘\\‘), this.FileName);
            if (!File.Exists(str))
            {
                workBook.Save(str);
            }

            if (File.Exists(str))
            {
                FileStream fs = new FileStream(str, FileMode.Open);
                workBook.Open(fs);
                workSheet = workBook.Worksheets.Add(sheetName);
                fs.Close();
                //添加列头
                workSheet.Cells.ImportArray(headNames, 0, 0, false);
                workSheet.Cells.ImportDataTable(source, false, 1, 0, true);
                //ImportArrayList(source, 1, 0, true);
                workSheet.AutoFitColumns();
                workBook.Save(str);
            }
        }

        #endregion

        #region 读取Excel中数据

        /// <summary>
        /// 从excel中读取数据至DataTable
        /// </summary>
        /// <param name="filePath">excel全路径</param>
        /// <returns></returns>
        public DataTable ReadExcel2DataTable(string filePath)
        {
            if (!File.Exists(filePath))
            {
                return null;
            }

            Workbook workbook = new Workbook();
            FileStream fs = new FileStream(filePath, FileMode.Open);
            workbook.Open(fs);

            // 获取该excel文件的第一个sheet
            Worksheet worksheet = workbook.Worksheets[0];
            // 读取出所有的数据
            // 第一个参数是：从该excel的哪一行开始读（计数从0开始）
            // 第二个参数是：从该excel的哪一列开始读（计数从0开始）
            // 第三个参数是：行数
            // 第四个参数是：列数

            int i = worksheet.Cells.MaxDataColumn;
            int j = worksheet.Cells.MaxDataRow;

            DataTable dataTableFromExcel = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxDataRow + 1,
                                                                           worksheet.Cells.MaxDataColumn + 1, false);
            fs.Close();
            return dataTableFromExcel;
        }

        #endregion

    }
}