/// <summary> /// DataTable导出到Excel /// </summary> /// <param name="enumExceltype">Excel文件格式</param> /// <param name="encoding"></param> public DataTableExporter(EnumExcelType enumExceltype, Encoding encoding) { if (encoding == null) { throw new ArgumentNullException("encoding"); } switch (enumExceltype) { case EnumExcelType.XLS: iworkbook = new HSSFWorkbook(); break; case EnumExcelType.XLSX: iworkbook = new XSSFWorkbook(); break; default: throw new ArgumentException("参数enumExceltype的值“" + enumExceltype.ToString() + "”无效。"); } this.ExcelType = enumExceltype; this.Encoding = encoding; }
/// <summary> /// /// </summary> /// <typeparam name="T">Data Type which you want to write to excel file</typeparam> /// <param name="data">The data source which will be written to excel file</param> /// <returns></returns> public static Int32 Generate(Object data, String tempGeneratedFolder, String sheetName, out String generatedFileName, EnumExcelType excelType = EnumExcelType.NONE) { Int32 result = 0; IWorkbook wBook = null; StringBuilder fullFileName = new StringBuilder(); FileMode fsMode = FileMode.CreateNew; generatedFileName = String.Empty; if (ValidateTempGeneratedFolder(tempGeneratedFolder)) { generatedFileName = GenerateRandomFileName(); //Create new excel file based on Excel 97/2003 try { //Validate SheetName or generate default sheet name from filename if (String.IsNullOrWhiteSpace(sheetName)) sheetName = generatedFileName; if (excelType == EnumExcelType.XLSX) generatedFileName += ".xlsx"; else generatedFileName += ".xls"; fullFileName.Append(tempGeneratedFolder); fullFileName.Append("\\"); fullFileName.Append(generatedFileName); if (File.Exists(fullFileName.ToString())) fsMode = FileMode.Open; else fsMode = FileMode.CreateNew; using (FileStream fs = new FileStream(fullFileName.ToString(), fsMode, FileAccess.ReadWrite)) { // Check if (excelType == EnumExcelType.XLSX) { if (fsMode == FileMode.Open) wBook = new XSSFWorkbook(fs); else wBook = new XSSFWorkbook(); } else { if (fsMode == FileMode.Open) wBook = new HSSFWorkbook(fs); else wBook = new HSSFWorkbook(); } //Write data to sheet Int32 maxRow = 0; try { WriteDataToSheet(data, ref wBook, sheetName, 0, 0, out maxRow); } catch { } //Write and close the file. wBook.Write(fs); fs.Close(); } } catch { throw; } } return result; }
/// <summary> /// DataTable导出到Excel /// </summary> /// <param name="enumExceltype">Excel文件格式</param> public DataTableExporter(EnumExcelType enumExceltype) : this(enumExceltype, Encoding.GetEncoding(936)) { }
/// <summary> /// /// </summary> /// <typeparam name="T">Data Type which you want to write to excel file</typeparam> /// <param name="data">The data source which will be written to excel file</param> /// <returns></returns> public static Int32 GenerateEx( IEnumerable <ObjectDataWrapper> datas, String tempGeneratedFolder, out String generatedFileName, string workbookTitle = "", string workbookAuthor = "", string workbookSubject = "", string workbookKeywords = "", string fromTemplateFileName = "", string templatePassword = "", EnumExcelType excelType = EnumExcelType.NONE) { Int32 result = 0; IWorkbook wBook = null; String fullFileName; FileMode fsMode = FileMode.CreateNew; generatedFileName = String.Empty; if (ValidateTempGeneratedFolder(tempGeneratedFolder)) { generatedFileName = GenerateRandomFileName(); //Create new excel file based on Excel 97/2003 try { if (excelType == EnumExcelType.XLSX) { generatedFileName += ".xlsx"; } else { generatedFileName += ".xls"; } fullFileName = Path.Combine(tempGeneratedFolder, generatedFileName).ToString(); if (File.Exists(fullFileName.ToString())) { fsMode = FileMode.Open; } else { fsMode = FileMode.CreateNew; } if (String.IsNullOrWhiteSpace(fromTemplateFileName)) { using (FileStream fs = new FileStream(fullFileName.ToString(), fsMode, FileAccess.ReadWrite)) { // Check if (excelType == EnumExcelType.XLSX) { if (fsMode == FileMode.Open) { wBook = new XSSFWorkbook(fs); } else { wBook = new XSSFWorkbook(); } POIXMLProperties xmlProps = ((XSSFWorkbook)wBook).GetProperties(); xmlProps.CoreProperties.Title = workbookTitle; xmlProps.CoreProperties.Creator = workbookAuthor; xmlProps.CoreProperties.Subject = workbookSubject; xmlProps.CoreProperties.Keywords = workbookKeywords; } else { if (fsMode == FileMode.Open) { wBook = new HSSFWorkbook(fs); } else { wBook = new HSSFWorkbook(); } SummaryInformation summaryInformation = ((HSSFWorkbook)wBook).SummaryInformation; if (summaryInformation == null) { ((HSSFWorkbook)wBook).CreateInformationProperties(); summaryInformation = ((HSSFWorkbook)wBook).SummaryInformation; } summaryInformation.Title = workbookTitle; summaryInformation.Author = workbookAuthor; summaryInformation.Subject = workbookSubject; summaryInformation.Keywords = workbookKeywords; } //Write data to sheet Int32 maxRow = 0; try { foreach (var data in datas) { WriteDataToSheet(data.Data, ref wBook, data.SheetName, 0, 0, out maxRow); } } catch { } //Write and close the file. if (excelType == EnumExcelType.XLSX) { XSSFFormulaEvaluator.EvaluateAllFormulaCells(wBook); } else { HSSFFormulaEvaluator.EvaluateAllFormulaCells(wBook); } wBook.Write(fs); fs.Close(); } } else { FileInfo fileTemplate = new FileInfo(fromTemplateFileName); if (!fileTemplate.Exists) { throw new Exception("File template doesn't exists."); } using (FileStream fs = new FileStream(fromTemplateFileName.ToString(), FileMode.Open, FileAccess.ReadWrite)) { // Check if (excelType == EnumExcelType.XLSX) { wBook = new XSSFWorkbook(fs); POIXMLProperties xmlProps = ((XSSFWorkbook)wBook).GetProperties(); xmlProps.CoreProperties.Title = workbookTitle; xmlProps.CoreProperties.Creator = workbookAuthor; xmlProps.CoreProperties.Subject = workbookSubject; xmlProps.CoreProperties.Keywords = workbookKeywords; } else { wBook = new HSSFWorkbook(fs); SummaryInformation summaryInformation = ((HSSFWorkbook)wBook).SummaryInformation; if (summaryInformation == null) { ((HSSFWorkbook)wBook).CreateInformationProperties(); summaryInformation = ((HSSFWorkbook)wBook).SummaryInformation; } summaryInformation.Title = workbookTitle; summaryInformation.Author = workbookAuthor; summaryInformation.Subject = workbookSubject; summaryInformation.Keywords = workbookKeywords; } //Write data to sheet Int32 maxRow = 0; try { foreach (var data in datas) { WriteDataToSheet(data.Data, ref wBook, data.SheetName, 0, 0, out maxRow); } } catch { } //Write and close the file. FileStream fsOut = new FileStream(fullFileName, FileMode.CreateNew, FileAccess.ReadWrite); if (excelType == EnumExcelType.XLSX) { XSSFFormulaEvaluator.EvaluateAllFormulaCells(wBook); } else { HSSFFormulaEvaluator.EvaluateAllFormulaCells(wBook); } wBook.Write(fsOut); fs.Close(); fsOut.Close(); } } } catch { throw; } } return(result); }