c#操作Excel整理总结

大家好，这是我在工作中总结的关于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){//转DataTabletry{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);}//如果大于100Wif (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文件的第一个sheetWorksheet 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}
}