public XSSFEventBasedExcelExtractor(OPCPackage Container) : base(null) { this.Container = Container; properties = new POIXMLProperties(Container); }
private void CreateExcel <T>(IEnumerable <T> source) { _wb = new XSSFWorkbook(); var sh = (XSSFSheet)_wb.CreateSheet("Sheet1"); POIXMLProperties props = _wb.GetProperties(); props.CoreProperties.Creator = "WYRMS"; props.CoreProperties.Created = DateTime.Now; var properties = typeof(T).GetProperties().ToList(); //构建表头 var header = sh.CreateRow(0); for (var j = 0; j < properties.Count; j++) { var display = properties[j].GetCustomAttributes(typeof(DisplayAttribute), true) .Cast <DisplayAttribute>() .FirstOrDefault(); header.CreateCell(j).SetCellValue(display != null ? display.Name : properties[j].Name); } var list = source.ToList(); //填充数据 for (var i = 0; i < list.Count; i++) { var r = sh.CreateRow(i + 1); for (var j = 0; j < properties.Count; j++) { var value = properties[j].GetValue(list[i], null); r.CreateCell(j).SetCellValue(value == null ? "" : value.ToString()); } } }
public void WorkbookProperties() { XSSFWorkbook workbook = new XSSFWorkbook(); try { POIXMLProperties props = workbook.GetProperties(); Assert.IsNotNull(props); //the Application property must be set for new workbooks, see Bugzilla #47559 Assert.AreEqual("NPOI", props.ExtendedProperties.GetUnderlyingProperties().Application); PackagePropertiesPart opcProps = props.CoreProperties.GetUnderlyingProperties(); Assert.IsNotNull(opcProps); opcProps.SetTitleProperty("Testing Bugzilla #47460"); Assert.AreEqual("NPOI", opcProps.GetCreatorProperty()); opcProps.SetCreatorProperty("*****@*****.**"); XSSFWorkbook wbBack = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook); Assert.AreEqual("NPOI", wbBack.GetProperties().ExtendedProperties.GetUnderlyingProperties().Application); opcProps = wbBack.GetProperties().CoreProperties.GetUnderlyingProperties(); Assert.AreEqual("Testing Bugzilla #47460", opcProps.GetTitleProperty()); Assert.AreEqual("*****@*****.**", opcProps.GetCreatorProperty()); } finally { workbook.Close(); } }
public void TestThumbnails() { POIXMLProperties noThumbProps = sampleNoThumb.GetProperties(); Assert.IsNotNull(_props.ThumbnailPart); Assert.IsNull(noThumbProps.ThumbnailPart); Assert.IsNotNull(_props.ThumbnailFilename); Assert.IsNull(noThumbProps.ThumbnailFilename); Assert.IsNotNull(_props.ThumbnailImage); Assert.IsNull(noThumbProps.ThumbnailImage); Assert.AreEqual("thumbnail.jpeg", _props.ThumbnailFilename); // Adding / changing noThumbProps.SetThumbnail("Testing.png", new ByteArrayInputStream(new byte[1])); Assert.IsNotNull(noThumbProps.ThumbnailPart); Assert.AreEqual("Testing.png", noThumbProps.ThumbnailFilename); Assert.IsNotNull(noThumbProps.ThumbnailImage); //Assert.AreEqual(1, noThumbProps.ThumbnailImage.Available()); Assert.AreEqual(1, noThumbProps.ThumbnailImage.Length - noThumbProps.ThumbnailImage.Position); noThumbProps.SetThumbnail("Testing2.png", new ByteArrayInputStream(new byte[2])); Assert.IsNotNull(noThumbProps.ThumbnailPart); Assert.AreEqual("Testing.png", noThumbProps.ThumbnailFilename); Assert.IsNotNull(noThumbProps.ThumbnailImage); //Assert.AreEqual(2, noThumbProps.ThumbnailImage.Available()); Assert.AreEqual(2, noThumbProps.ThumbnailImage.Length - noThumbProps.ThumbnailImage.Position); }
public void TestWorkbookProperties() { XWPFDocument doc = new XWPFDocument(); POIXMLProperties props = doc.GetProperties(); Assert.IsNotNull(props); Assert.AreEqual("NPOI", props.ExtendedProperties.GetUnderlyingProperties().Application); }
public void SetUp() { sampleDoc = XWPFTestDataSamples.OpenSampleDocument("documentProperties.docx"); sampleNoThumb = XWPFTestDataSamples.OpenSampleDocument("SampleDoc.docx"); Assert.IsNotNull(sampleDoc); Assert.IsNotNull(sampleNoThumb); _props = sampleDoc.GetProperties(); _coreProperties = _props.CoreProperties; Assert.IsNotNull(_props); }
private static void CreateExcel(Dictionary <Type, List <object> > source, XSSFWorkbook wb) { int sheetCount = 0; foreach (var key in source.Keys) { sheetCount++; string sheetName = "Sheet" + sheetCount; var classdisplay = key.GetCustomAttributes(typeof(DisplayNameAttribute), true) .Cast <DisplayNameAttribute>() .FirstOrDefault(); if (classdisplay != null) { sheetName = classdisplay.DisplayName; } var sh = (XSSFSheet)wb.CreateSheet(sheetName); POIXMLProperties props = wb.GetProperties(); props.CoreProperties.Creator = "MAX"; props.CoreProperties.Created = DateTime.Now; var properties = key.GetProperties().ToList(); //构建表头 var header = sh.CreateRow(0); for (var j = 0; j < properties.Count; j++) { var display = properties[j].GetCustomAttributes(typeof(DisplayAttribute), true) .Cast <DisplayAttribute>() .FirstOrDefault(); if (display != null) { header.CreateCell(j).SetCellValue(display.Name); } else { header.CreateCell(j).SetCellValue(properties[j].Name); } } var list = source[key]; //填充数据 for (var i = 0; i < list.Count; i++) { var r = sh.CreateRow(i + 1); for (var j = 0; j < properties.Count; j++) { var value = properties[j].GetValue(list[i], null); r.CreateCell(j).SetCellValue(value == null ? "" : value.ToString()); } } } }
protected void ProcessDocumentInformation(IWorkbook workbook) { if (workbook is NPOI.HSSF.UserModel.HSSFWorkbook) { SummaryInformation summaryInformation = ((HSSFWorkbook)workbook).SummaryInformation; if (summaryInformation != null) { if (!string.IsNullOrEmpty(summaryInformation.Title)) { htmlDocumentFacade.Title = summaryInformation.Title; } if (!string.IsNullOrEmpty(summaryInformation.Author)) { htmlDocumentFacade.AddAuthor(summaryInformation.Author); } if (!string.IsNullOrEmpty(summaryInformation.Keywords)) { htmlDocumentFacade.AddKeywords(summaryInformation.Keywords); } if (!string.IsNullOrEmpty(summaryInformation.Comments)) { htmlDocumentFacade.AddDescription(summaryInformation.Comments); } } } else if (workbook is NPOI.XSSF.UserModel.XSSFWorkbook) { POIXMLProperties props = ((NPOI.XSSF.UserModel.XSSFWorkbook)workbook).GetProperties(); if (!string.IsNullOrEmpty(props.CoreProperties.Title)) { htmlDocumentFacade.Title = props.CoreProperties.Title; } if (!string.IsNullOrEmpty(props.CoreProperties.Creator)) { htmlDocumentFacade.AddAuthor(props.CoreProperties.Creator); } if (!string.IsNullOrEmpty(props.CoreProperties.Keywords)) { htmlDocumentFacade.AddKeywords(props.CoreProperties.Keywords); } if (!string.IsNullOrEmpty(props.CoreProperties.Description)) { htmlDocumentFacade.AddDescription(props.CoreProperties.Description); } } }
public static XSSFWorkbook ListToXlsx <T>(List <T> list, string sheetName) { XSSFWorkbook workbook = new XSSFWorkbook(); POIXMLProperties props = workbook.GetProperties(); props.CoreProperties.Creator = "北京易天正诚信息技术有限公司"; props.CoreProperties.Created = DateTime.Now; props.CustomProperties.AddProperty("壁虎科技", "壁虎科技"); ListToSheetXlsx(workbook, list, sheetName); return(workbook); }
public async Task <BaseResponse> Xlsx() { List <ExcelTestClass> list = ExcelTestClass.GetList(); //BaseDirectory后面有\所以exel前面就不加\了 var storePath = AppDomain.CurrentDomain.BaseDirectory + "Excel"; if (!Directory.Exists(storePath)) { Directory.CreateDirectory(storePath); } string fileNam = "导出数据-" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; string fullPath = storePath + "\\" + fileNam; XSSFWorkbook workbook = new XSSFWorkbook(); POIXMLProperties props = workbook.GetProperties(); props.CoreProperties.Creator = "北京易天正诚信息技术有限公司"; props.CoreProperties.Subject = "壁虎科技"; props.CoreProperties.Title = "壁虎科技"; props.CoreProperties.Created = DateTime.Now; props.CustomProperties.AddProperty("壁虎科技", "壁虎科技"); XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; XSSFFont font = (XSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 12; font.Boldweight = 700; headStyle.SetFont(font); var sheet = workbook.CreateSheet(); IRow headerRow = sheet.CreateRow(0); var headCell = headerRow.CreateCell(0); headCell.SetCellValue("111"); FileStream file = new FileStream(fullPath, FileMode.Create); workbook.Write(file); file.Close(); return(BaseResponse.Ok(fullPath)); }
static void Main(string[] args) { XSSFWorkbook workbook = new XSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("Sheet1"); POIXMLProperties props = workbook.GetProperties(); props.CoreProperties.Creator = "NPOI 2.0.5"; props.CoreProperties.Created = DateTime.Now; props.CustomProperties.AddProperty("NPOI Team", "Hello World!"); FileStream sw = File.Create("test.xlsx"); workbook.Write(sw); sw.Close(); }
public void TestWorkbookExtendedProperties() { XSSFWorkbook workbook = new XSSFWorkbook(); POIXMLProperties props = workbook.GetProperties(); Assert.IsNotNull(props); ExtendedProperties properties = props.ExtendedProperties; CT_ExtendedProperties ctProps = properties.GetUnderlyingProperties(); String appVersion = "3.5 beta"; String application = "POI"; ctProps.Application = (application); ctProps.AppVersion = (appVersion); XSSFWorkbook newWorkbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook); workbook.Close(); Assert.IsTrue(workbook != newWorkbook); POIXMLProperties newProps = newWorkbook.GetProperties(); Assert.IsNotNull(newProps); ExtendedProperties newProperties = newProps.ExtendedProperties; Assert.AreEqual(application, newProperties.Application); Assert.AreEqual(appVersion, newProperties.AppVersion); CT_ExtendedProperties newCtProps = newProperties.GetUnderlyingProperties(); Assert.AreEqual(application, newCtProps.Application); Assert.AreEqual(appVersion, newCtProps.AppVersion); newWorkbook.Close(); }
public void TestZeroLengthLibreOfficeDocumentWithWaterMarkHeader() { XWPFDocument doc = XWPFTestDataSamples.OpenSampleDocument("zero-length.docx"); POIXMLProperties properties = doc.GetProperties(); Assert.IsNotNull(properties.CoreProperties); XWPFHeader headerArray = doc.GetHeaderArray(0); Assert.AreEqual(1, headerArray.AllPictures.Count); Assert.AreEqual("image1.png", headerArray.AllPictures[0].FileName); Assert.AreEqual("", headerArray.Text); ExtendedProperties extendedProperties = properties.ExtendedProperties; Assert.IsNotNull(extendedProperties); Assert.AreEqual(0, extendedProperties.GetUnderlyingProperties().Characters); }
/// <summary> /// Excelを出力する /// </summary> /// <param name="filePath"></param> /// <param name="sheetName"></param> /// <param name="fileOutputType"></param> internal void CreateExcel(string filePath, string sheetName, FileOutputType fileOutputType) { try { IWorkbook workbook = null; ISheet sheet = null; string ext = System.IO.Path.GetExtension(filePath); switch (fileOutputType) { case FileOutputType.追記: if (System.IO.File.Exists(filePath)) { using (var fs = File.OpenRead(filePath)) { workbook = WorkbookFactory.Create(fs); } sheet = workbook.GetSheet(sheetName); } else { if (ext.ToUpper() == ".xls") { workbook = new HSSFWorkbook(); sheet = workbook.CreateSheet(sheetName); } else { workbook = new XSSFWorkbook(); sheet = workbook.CreateSheet(sheetName); } } break; case FileOutputType.書き: if (ext.ToUpper() == ".xls") { workbook = new HSSFWorkbook(); sheet = workbook.CreateSheet(sheetName); } else { workbook = new XSSFWorkbook(); sheet = workbook.CreateSheet(sheetName); } break; } switch (fileOutputType) { case FileOutputType.追記: { int last = sheet.LastRowNum; if (last > 0) { last++; } for (int r = 0; r < ValueList.Count; r++) { IRow row = sheet.CreateRow(r + last); for (int c = 0; c < ValueList[r].Count; c++) { ICell cell = row.CreateCell(c); cell.SetCellValue(ValueList[r][c]); } } using (var fs = File.Create(filePath)) { try { if (workbook is XSSFWorkbook) { XSSFWorkbook b = (XSSFWorkbook)workbook; POIXMLProperties props = b.GetProperties(); props.CoreProperties.Creator = "Macrobo"; } } catch (Exception) {} workbook.Write(fs); workbook.Close(); } } break; case FileOutputType.書き: { for (int r = 0; r < ValueList.Count; r++) { IRow row = sheet.CreateRow(r); for (int c = 0; c < ValueList[r].Count; c++) { ICell cell = row.CreateCell(c); cell.SetCellValue(ValueList[r][c]); } } if (System.IO.File.Exists(filePath)) { System.IO.File.Delete(filePath); } using (var fs = File.Create(filePath)) { try { if (workbook is XSSFWorkbook) { XSSFWorkbook b = (XSSFWorkbook)workbook; POIXMLProperties props = b.GetProperties(); props.CoreProperties.Creator = "Macrobo"; } } catch (Exception) { } workbook.Write(fs); workbook.Close(); } } break; } } catch (Exception ex) { throw Program.ThrowException(ex); } }
/// <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); }
public ToxyMetadata Parse() { if (!System.IO.File.Exists(Context.Path)) { throw new System.IO.FileNotFoundException("File " + Context.Path + " is not found"); } ToxyMetadata metadata = new ToxyMetadata(); OPCPackage pack = null; try { pack = OPCPackage.Open(Context.Path, PackageAccess.READ); POIXMLProperties props = new POIXMLProperties(pack); if (props.CoreProperties != null) { if (props.CoreProperties.Title != null) { metadata.Add("Title", props.CoreProperties.Title); } else if (props.CoreProperties.Identifier != null) { metadata.Add("Identifier", props.CoreProperties.Identifier); } else if (props.CoreProperties.Keywords != null) { metadata.Add("Keywords", props.CoreProperties.Keywords); } else if (props.CoreProperties.Revision != null) { metadata.Add("Revision", props.CoreProperties.Revision); } else if (props.CoreProperties.Subject != null) { metadata.Add("Subject", props.CoreProperties.Subject); } else if (props.CoreProperties.Modified != null) { metadata.Add("Modified", props.CoreProperties.Modified); } else if (props.CoreProperties.LastPrinted != null) { metadata.Add("LastPrinted", props.CoreProperties.LastPrinted); } else if (props.CoreProperties.Created != null) { metadata.Add("Created", props.CoreProperties.Created); } else if (props.CoreProperties.Creator != null) { metadata.Add("Creator", props.CoreProperties.Creator); } else if (props.CoreProperties.Description != null) { metadata.Add("Description", props.CoreProperties.Description); } } if (props.ExtendedProperties != null && props.ExtendedProperties.props != null) { var extProps = props.ExtendedProperties.props.GetProperties(); if (extProps.Application != null) { metadata.Add("Application", extProps.Application); } if (extProps.AppVersion != null) { metadata.Add("AppVersion", extProps.AppVersion); } if (extProps.Characters > 0) { metadata.Add("Characters", extProps.Characters); } if (extProps.CharactersWithSpaces > 0) { metadata.Add("CharactersWithSpaces", extProps.CharactersWithSpaces); } if (extProps.Company != null) { metadata.Add("Company", extProps.Company); } if (extProps.Lines > 0) { metadata.Add("Lines", extProps.Lines); } if (extProps.Manager != null) { metadata.Add("Manager", extProps.Manager); } if (extProps.Notes > 0) { metadata.Add("Notes", extProps.Notes); } if (extProps.Pages > 0) { metadata.Add("Pages", extProps.Pages); } if (extProps.Paragraphs > 0) { metadata.Add("Paragraphs", extProps.Paragraphs); } if (extProps.Words > 0) { metadata.Add("Words", extProps.Words); } if (extProps.TotalTime > 0) { metadata.Add("TotalTime", extProps.TotalTime); } } } finally { if (pack != null) { pack.Close(); } } return(metadata); }
public void Bug54764() { OPCPackage pkg = XSSFTestDataSamples.OpenSamplePackage("54764.xlsx"); // Check the core properties - will be found but empty, due // to the expansion being too much to be considered valid POIXMLProperties props = new POIXMLProperties(pkg); Assert.AreEqual(null, props.CoreProperties.Title); Assert.AreEqual(null, props.CoreProperties.Subject); Assert.AreEqual(null, props.CoreProperties.Description); // Now check the spreadsheet itself try { new XSSFWorkbook(pkg); Assert.Fail("Should fail as too much expansion occurs"); } catch (POIXMLException) { //Expected } // Try with one with the entities in the Content Types try { XSSFTestDataSamples.OpenSamplePackage("54764-2.xlsx"); Assert.Fail("Should fail as too much expansion occurs"); } catch (Exception) { // Expected } // Check we can still parse valid files after all that IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("sample.xlsx"); Assert.AreEqual(3, wb.NumberOfSheets); }