コード例 #1
0
        /// <summary>
        /// 创建Excel
        /// </summary>
        /// <typeparam name="T">泛型实体类</typeparam>
        /// <param name="data">导出的数据</param>
        /// <param name="excelParameters">通过反射得到的列头对象</param>
        /// <param name="FileName">文件存放路径</param>
        /// <param name="dataIndex">预留参数,暂未用到</param>
        /// <param name="excelVersion">导出的格式后缀</param>
        public static void SaveExcel(IList <T> data, IList <ExcelParameterVo> excelParameters, string FileName, int dataIndex = 1, ExcelVersion excelVersion = ExcelVersion.xlsx)
        {
            FileInfo fileInfo = new FileInfo(FileName);

            if (fileInfo.Exists)
            {
                //删除原有文件
                fileInfo.Delete();
                fileInfo = new FileInfo(FileName);//创建新文件
            }
            try
            {
                using (ExcelPackage package = new ExcelPackage(fileInfo))
                {
                    if (data != null && data.Count > 0)
                    {
                        excelParameters = (from s in excelParameters orderby s.Sort select s).ToList();
                        object         obj       = null;
                        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("shett1");
                        //传入的列名 col用于得到list的下标,i用于写入Excel的某一列
                        for (int col = 0, i = 1; col < ((ICollection <ExcelParameterVo>)excelParameters).Count; col++, i++)
                        {
                            ExcelParameterVo excelParameterVo = excelParameters[col];   //得到列名对象
                            worksheet.Cells[1, i].Value = excelParameterVo.ColumnName;  //设置列名
                            worksheet.Column(i).Width   = excelParameterVo.ColumnWidth; //设置列宽
                        }
                        //传入的列名 row用于得到data的下标,j用于写入Excel的某一行
                        for (int row = 0, j = 2; row < data.Count; row++, j++)
                        {
                            //传入的列名 col用于得到excelParameters的下标,i用于写入Excel的某一列
                            for (int col = 0, i = 1; col < ((ICollection <ExcelParameterVo>)excelParameters).Count; col++, i++)
                            {
                                ExcelParameterVo excelParameterVo = excelParameters[col]; //得到列名对象
                                var item = data[row];
                                obj = excelParameterVo.Property.GetValue(item);           //通过反射获取item
                                if (obj == null)                                          //如果obj=null的话该列直接写空
                                {
                                    worksheet.Cells[j, i].Value = "";
                                }
                                else
                                {
                                    worksheet.Cells[j, i].Value = obj.ToString();
                                }
                            }
                        }
                    }
                    package.Save();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
コード例 #2
0
 /// <summary>
 /// 创建Excel;并返回文件流
 /// </summary>
 /// <typeparam name="T">泛型实体类</typeparam>
 /// <param name="data">导出的数据</param>
 /// <param name="excelParameters">通过反射得到的列头对象</param>
 /// <param name="FileName">文件存放路径</param>
 /// <param name="dataIndex">预留参数,暂未用到</param>
 /// <param name="excelVersion">导出的格式后缀</param>
 public static byte[] ToExcelbyByte(IList <T> data, IList <ExcelParameterVo> excelParameters, int dataIndex = 1, ExcelVersion excelVersion = ExcelVersion.xlsx)
 {
     using (ExcelPackage package = new ExcelPackage())
     {
         try
         {
             if (data != null && data.Count > 0)
             {
                 excelParameters = (from s in excelParameters orderby s.Sort select s).ToList();
                 object         obj       = null;
                 ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");
                 //传入的列名 col用于得到list的下标,i用于写入Excel的某一列
                 for (int col = 0, i = 1; col < ((ICollection <ExcelParameterVo>)excelParameters).Count; col++, i++)
                 {
                     ExcelParameterVo excelParameterVo = excelParameters[col];   //得到列名对象
                     worksheet.Cells[1, i].Value = excelParameterVo.ColumnName;  //设置列名
                     worksheet.Column(i).Width   = excelParameterVo.ColumnWidth; //设置列宽
                 }
                 //传入的列名 row用于得到data的下标,j用于写入Excel的某一行
                 for (int row = 0, j = 2; row < data.Count; row++, j++)
                 {
                     //传入的列名 col用于得到excelParameters的下标,i用于写入Excel的某一列
                     for (int col = 0, i = 1; col < ((ICollection <ExcelParameterVo>)excelParameters).Count; col++, i++)
                     {
                         ExcelParameterVo excelParameterVo = excelParameters[col]; //得到列名对象
                         var item = data[row];
                         obj = excelParameterVo.Property.GetValue(item);           //通过反射获取item
                         if (obj == null)                                          //如果obj=null的话该列直接写空
                         {
                             worksheet.Cells[j, i].Value = "";
                         }
                         else
                         {
                             worksheet.Cells[j, i].Value = obj.ToString();
                         }
                     }
                 }
                 return(package.GetAsByteArray());
             }
             return(package.GetAsByteArray());
         }
         catch (Exception ex)
         {
             throw ex;
         }
     }
 }