EPPlus使用方法---Excel处理我觉得超级好用

目前只是用到导出Excel功能，导出大规模数据量速度也很快，而且比较容易操作（最起码导出是，暂时没有用到处理已存在的excel功能，有人说NPOI也好用，试了一下，最起码导出这个不如EPPlus   --狗头保命）

1.  引入Get包   ：EPPlus   

　　　　

 

 

　　2.    这个东西不能商用，需要添加一个非商业的凭证许可，也可以去官网看看怎么商用。

　　　　//非商业EPPlus凭证许可
      ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;

　　3. 简单的代码示例

 /// <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

 

      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();
            }
        }