Example #1
0
        /// <summary>
        /// 导出excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">数据</param>
        /// <param name="excelTempPath">模板路径</param>
        /// <returns></returns>
        public static MemoryStream ExportExcel <T>(this IEnumerable <T> list, string excelTempPath)
        {
            if (!File.Exists(excelTempPath))
            {
                throw new Exception("模板文件不存在");
            }
            IWorkbook workbook = new XSSFWorkbook();

            using (FileStream fs = new FileStream(excelTempPath, FileMode.Open, FileAccess.Read))
            {
                workbook = new XSSFWorkbook(fs);
            }
            ISheet sheet = workbook.GetSheetAt(0);

            #region 单元格格式
            //货币格式
            var cellMoneyStyle = workbook.GetCellStyle(NPOICellStyle.Money);

            //四位小数个数
            var cell4DecimalStyle = workbook.GetCellStyle(NPOICellStyle.Decimal_Length_4);
            #endregion

            #region 遍历填充
            var       header = sheet.GetRow(0);
            IRow      row;
            ICell     cell;
            var       rowIndex = 1;
            ExcelItem excelItem;
            foreach (var item in list)
            {
                row = sheet.CreateRow(rowIndex);
                for (int i = 0; i < header.Cells.Count; i++)
                {
                    excelItem = GetValue(item.ToDynamic(), header.Cells[i].StringCellValue);
                    if (excelItem.IsContainsOperator)
                    {
                        cell = row.CreateCell(i, CellType.Numeric);
                        cell.SetCellValue(double.Parse(excelItem.Value));
                        cell.CellStyle = header.Cells[i].StringCellValue.Contains("比例") ? cell4DecimalStyle : cellMoneyStyle;
                    }
                    else if (!header.Cells[i].StringCellValue.Contains("FirstInvoiceDate") &&
                             (header.Cells[i].StringCellValue.Contains("InvoiceDate") ||
                              header.Cells[i].StringCellValue.Contains("PaymentDate")))
                    {
                        //日期格式化
                        cell = row.CreateCell(i, CellType.String);
                        cell.SetCellValue(string.IsNullOrEmpty(excelItem.Value)
                            ? excelItem.Value : Convert.ToDateTime(excelItem.Value).ToString("yyyy-MM-dd"));
                    }
                    else
                    {
                        row.CreateCell(i, CellType.String).SetCellValue(excelItem.Value);
                    }
                }
                rowIndex++;
            }
            #endregion

            #region 保留第一个sheet,修改header头
            //保留第一个sheet,其他的删除
            for (var i = 1; i < workbook.NumberOfSheets; i++)
            {
                workbook.RemoveSheetAt(1);
            }
            //修改header头
            for (int i = 0; i < header.Cells.Count; i++)
            {
                header.Cells[i].SetCellValue(GetHeaderString(header.Cells[i].StringCellValue));
            }
            #endregion

            var memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            memoryStream.Flush();
            memoryStream.Position = 0;

            return(memoryStream);
        }