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 XSSFEventBasedExcelExtractor(OPCPackage Container)
: base(null)
{
this.Container = Container;
properties = new POIXMLProperties(Container);
}
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);
}
