目前只是用到导出Excel功能,导出大规模数据量速度也很快,而且比较容易操作(最起码导出是,暂时没有用到处理已存在的excel功能,有人说NPOI也好用,试了一下,最起码导出这个不如EPPlus --狗头保命)
- 引入Get包 :EPPlus
2. 这个东西不能商用,需要添加一个非商业的凭证许可,也可以去官网看看怎么商用。
//非商业EPPlus凭证许可 ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
3. 简单的代码示例
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/// <summary> ///导出EXCEL /// </summary> public static void ExportExcel() { //非商业EPPlus凭证许可 ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial; //excel路径 FileInfo newFile = new FileInfo("C:\\Users\\zhang\\Desktop\\数据.xlsx"); //判断excel是否存在了,因为目前是导出数据到新的excel,有相同文件名的excel就删除重新创建 if (newFile.Exists) { newFile.Delete(); } //1.根据路径文件,已存在就创建一个新实例来操作,即编辑execl操作 //2.若是文件不存在,就会创建新的EXCEL并操作, using (ExcelPackage package = new ExcelPackage(newFile)) { //创建shheet页,可以根据需求,添加多个sheet,例如: ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("数据2"); ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("数据"); #region 标头,即execl列名 worksheet.Cells[1, 1].Value = "ID"; worksheet.Cells[1, 2].Value = "QN"; worksheet.Cells[1, 3].Value = "ProjectID"; worksheet.Cells[1, 4].Value = "PoleID"; worksheet.Cells[1, 5].Value = "DeviceID"; worksheet.Cells[1, 6].Value = "DeviceMNCode"; worksheet.Cells[1, 7].Value = "DataTime"; worksheet.Cells[1, 8].Value = "UploadTime"; worksheet.Cells[1, 9].Value = "MetricA01001"; worksheet.Cells[1, 10].Value = "FlagA01001"; #endregion int num = 1; //data就是要循环的数据.根据个人要求自行修改即可 List<dynamic> data = new List<dynamic>() { }; for (int i = 0; i < data.Count; i++) { num++; worksheet.Cells[num, 1].Value = data[i].ID; worksheet.Cells[num, 2].Value = data[i].QN; worksheet.Cells[num, 3].Value = data[i].ProjectID; worksheet.Cells[num, 4].Value = data[i].PoleID; worksheet.Cells[num, 5].Value = data[i].DeviceID; worksheet.Cells[num, 6].Value = data[i].DeviceMNCode; worksheet.Cells[num, 7].Value = data[i].DataTime.ToString(); worksheet.Cells[num, 8].Value = data[i].UploadTime.ToString(); worksheet.Cells[num, 9].Value = data[i].MetricA01001.ToString(); worksheet.Cells[num, 10].Value = data[i].FlagA01001.ToString(); } //最后将实例保存,保存的位置就是刚才创建实例的路径 package.Save(); } }
4. 读取已有excel
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
static void ExcelDES(string fileinfo) { FileInfo file = new FileInfo("路径"); ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial; using (ExcelPackage package = new ExcelPackage(file)) { ExcelWorksheet wb = package.Workbook.Worksheets[0]; int row = wb.Dimension.Rows; Console.WriteLine(row); for (int i = 1; i <= row; i++) { string ip = wb.Cells[i, 1].Value.ToString(); } package.Save(); } }
![](https://common.cnblogs.com/images/loading.gif)