//DocummentSummaryInformation和SummaryInformation
public ActionResult Index2()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.DocumentSummaryInformation = dsi;
hssfworkbook.SummaryInformation = si;
hssfworkbook.CreateSheet("mySheet1");
hssfworkbook.CreateSheet("mySheet2");
hssfworkbook.CreateSheet("mySheet3");
string path = Server.MapPath("/");
FileStream file = new FileStream(path + "\\test1_2.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
return(View());
}
public bool create(string fileName, string[] sheet = null)
{
if (fileName.Length < 1) return false;
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
//here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
//So we insert three sheet just like what Excel does
if (sheet == null) hssfworkbook.CreateSheet("Sheet1");
else
{
foreach (string s in sheet)
{
if (s.Length > 0) hssfworkbook.CreateSheet(s);
}
}
//Write the stream data of workbook to the root directory
if (fileName.IndexOf(".xls") <= 0) fileName += ".xls";
FileStream file = new FileStream(fileName, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
return true;
}
public DecorBalancesReport(string FileName)
{
BalancesDT = new DataTable();
BalancesMarketingReport(false);
BalancesZOVReport(true);
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
string tempFolder = System.Environment.GetEnvironmentVariable("TEMP");
FileInfo file = new FileInfo(tempFolder + @"\" + FileName + ".xls");
int j = 1;
while (file.Exists == true)
{
file = new FileInfo(tempFolder + @"\" + FileName + "(" + j++ + ").xls");
}
FileStream NewFile = new FileStream(file.FullName, FileMode.Create);
hssfworkbook.Write(NewFile);
NewFile.Close();
System.Diagnostics.Process.Start(file.FullName);
}
public ExcelDocument(bool parRemoveBr)
{
offset = 0;
workbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Cashflow Funding Limited";
workbook.DocumentSummaryInformation = dsi;
sheet = (HSSFSheet)workbook.CreateSheet("CFF Export Data");
cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellStyle.WrapText = true;
cellStyleTitle = (HSSFCellStyle)workbook.CreateCellStyle();
cellHeaderStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellFooterStyle = (HSSFCellStyle)workbook.CreateCellStyle();
this.removeBr = parRemoveBr;
if (this.removeBr == true)
{
cellStyle.BorderLeft = CellBorderType.NONE;
cellStyle.BorderRight = CellBorderType.NONE;
cellStyle.BorderBottom = CellBorderType.NONE;
cellStyle.BorderTop = CellBorderType.NONE;
}
fontNormal = (HSSFFont)workbook.CreateFont();
fontTitle = (HSSFFont)workbook.CreateFont();
fontHeader = (HSSFFont)workbook.CreateFont();
fontFooter = (HSSFFont)workbook.CreateFont();
df = (HSSFDataFormat)workbook.CreateDataFormat();
}
/// <summary>
/// 将DataTable转成Xls文件导出
/// </summary>
/// <param name="page"></param>
/// <param name="dt"></param>
/// <param name="FileName"></param>
/// <returns></returns>
public static Stream ExportExcelByNPOI(DataTable dt)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//设定文件相关信息
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Data";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "Data";
hssfworkbook.SummaryInformation = si;
//将DataTable数据写入
Sheet sheet1 = hssfworkbook.CreateSheet("DataExport_NPOI");
for (int i = 0; i < dt.Rows.Count; i++)
{
//创建行
Row row = sheet1.CreateRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
//创建单元格
row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
hssfworkbook.Dispose();
return(file);
}
public async Task <HSSFWorkbook> GetWorkbook(List <ExcelTestClass> list, string sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "耐心的雪球有限公司";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "耐心"; //填加xls文件作者信息
si.LastAuthor = "耐心"; //填加xls文件最后保存者信息
si.Comments = "耐心"; //填加xls文件作者信息
si.Subject = "导出文件记录"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
await ListToSheet(workbook, list, headStyle, sheetName);
return(workbook);
}
/// <summary>
/// Find, and Create objects for, the standard
/// Documment Information Properties (HPSF).
/// If a given property Set is missing or corrupt,
/// it will remain null;
/// </summary>
protected void ReadProperties()
{
PropertySet ps;
// DocumentSummaryInformation
ps = GetPropertySet(DocumentSummaryInformation.DEFAULT_STREAM_NAME);
if (ps != null && ps is DocumentSummaryInformation)
{
dsInf = (DocumentSummaryInformation)ps;
}
else if (ps != null)
{
//logger.Log(POILogger.WARN, "DocumentSummaryInformation property Set came back with wrong class - ", ps.GetType());
}
// SummaryInformation
ps = GetPropertySet(SummaryInformation.DEFAULT_STREAM_NAME);
if (ps is SummaryInformation)
{
sInf = (SummaryInformation)ps;
}
else if (ps != null)
{
//logger.Log(POILogger.WARN, "SummaryInformation property Set came back with wrong class - ", ps.GetType());
}
// Mark the fact that we've now loaded up the properties
initialized = true;
}
/// <summary>
/// 预置模板
/// </summary>
/// <param name="DocumentName"></param>
private void InitializeWorkbook(string DocumentName)
{
//读取模板通过编辑，这是建议使用fileaccess读防止文件锁定
FileStream file = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "template\\" + DocumentName, FileMode.Open, FileAccess.Read);
// hssfworkbook = new HSSFWorkbook(file);
if (DocumentName.IndexOf(".xlsx") > 0)
{
// 2007版本
hssfworkbook = new XSSFWorkbook(file);
}
else // 2003版本
{
hssfworkbook = new HSSFWorkbook(file);
}
//创建一个条目的文档概要信息
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "TES";
//hssfworkbook.DocumentSummaryInformation = dsi;
//创建一个条目的概要信息
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "TES NPOI Example";
// hssfworkbook.SummaryInformation = si;
}
public static void WriteXls(List <TestRun> runs, string[] args)
{
var currentDate = DateTime.Now.ToString("M-d-yyyy");
string path;
if (args.Length == 0)
{
path = Path.Combine(ConfigurationManager.AppSettings["xlsResultFileFolder"], currentDate + "_Daily_Tests_Run.xls");
}
else
{
path = Path.Combine(args[0], currentDate + "_Daily_Tests_Run.xls");
}
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "DFS Venue Regression Team";
hssfworkbook.DocumentSummaryInformation = dsi;
CreateCellStyles();
CreateAllRunsDataTab(runs);
CreateEnvironmentsResultsTab(runs);
CreateFailedTestsTab(runs);
FileStream file = new FileStream(path, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
private void AddDocumentInformation(IWorkbook wb, string subject, ExcelFormat excelFormat)
{
if (excelFormat == ExcelFormat.xls)
{
var wbHssf = wb as HSSFWorkbook;
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "BSE";
wbHssf.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = subject;
si.Author = "BSE";
wbHssf.SummaryInformation = si;
}
else
{
var wbXssf = wb as NPOI.XSSF.UserModel.XSSFWorkbook;
var xmlProps = wbXssf.GetProperties();
var coreProps = xmlProps.CoreProperties;
coreProps.Subject = subject;
coreProps.Creator = "BSE";
var appProps = xmlProps.ExtendedProperties.GetUnderlyingProperties();
appProps.Company = "BSE";
}
}
static void Main(string[] args)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
//here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
//So we insert three sheet just like what Excel does
hssfworkbook.CreateSheet("Sheet1");
hssfworkbook.CreateSheet("Sheet2");
hssfworkbook.CreateSheet("Sheet3");
hssfworkbook.CreateSheet("Sheet4");
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(@"test.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
public void TearDown()
{
bout = null;
poifs = null;
dir = null;
dsi = null;
}
public bool LoadXlsTemplate(string filePath)
{
if (!File.Exists(filePath))
{
return(false);
}
openFilePath = filePath;
try
{
using (FileStream file = new FileStream(openFilePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
//create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "XXX Team";
hssfworkbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "Template Example";
hssfworkbook.SummaryInformation = si;
return(true);
}
catch (Exception ex)
{
openFilePath = null;
throw;
}
}
/**
* Setting a lot of things to null.
*/
private void TearDown()
{
bout = null;
poifs = null;
dir = null;
dsi = null;
}
private DocumentSummaryInformation GetDocumentSummaryInformation()
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = Config.URL.Domain;
return(dsi);
}
/// <summary>
/// 创建workbook
/// created by sang at 2013.09.17
/// </summary>
/// <param name="companyXmlFileName">公司信息配置文件</param>
/// <returns></returns>
private static HSSFWorkbook ReBuildWorkbook(string companyXmlFileName = "CompanyConfig.xml")
{
//实例化工作薄
HSSFWorkbook _workbook = new HSSFWorkbook();
//添加工作薄的属性（鼠标右键->属性->详细信息）
//实例化公司信息的xml
XmlTwoHandler XmlCompany = new XmlTwoHandler(companyXmlFileName);
DataTable result = XmlCompany.GetAllData();
//文件的大概信息
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = result.Select("InnerText='Company'")[0]["cn"].ToString();
//主要信息
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = result.Select("InnerText='Author'")[0]["cn"].ToString(); //作者信息
si.ApplicationName = result.Select("InnerText='ApplicationName'")[0]["cn"].ToString(); //填加xls文件创建程序信息
si.LastAuthor = result.Select("InnerText='LastAuthor'")[0]["cn"].ToString(); //填加xls文件最后保存者信息
si.Comments = result.Select("InnerText='Comments'")[0]["cn"].ToString(); //填加xls文件作者信息
si.Title = result.Select("InnerText='Title'")[0]["cn"].ToString(); //填加xls文件标题信息
si.Subject = result.Select("InnerText='Subject'")[0]["cn"].ToString(); //填加文件主题信息
si.CreateDateTime = DateTime.Now;
_workbook.DocumentSummaryInformation = dsi;
_workbook.SummaryInformation = si;
return(_workbook);
}
public void Test54233()
{
DocumentInputStream dis;
POIFSFileSystem fs =
new POIFSFileSystem(_samples.OpenResourceAsStream("TestNon4ByteBoundary.doc"));
dis = fs.CreateDocumentInputStream(SummaryInformation.DEFAULT_STREAM_NAME);
SummaryInformation si = (SummaryInformation)PropertySetFactory.Create(dis);
dis = fs.CreateDocumentInputStream(DocumentSummaryInformation.DEFAULT_STREAM_NAME);
DocumentSummaryInformation dsi = (DocumentSummaryInformation)PropertySetFactory.Create(dis);
// Test
Assert.AreEqual("Microsoft Word 10.0", si.ApplicationName);
Assert.AreEqual("", si.Title);
Assert.AreEqual("", si.Author);
Assert.AreEqual("Cour de Justice", dsi.Company);
// Write out and read back, should still be valid
POIDocument doc = new HPSFPropertiesOnlyDocument(fs);
MemoryStream baos = new MemoryStream();
doc.Write(baos);
MemoryStream bais = new MemoryStream(baos.ToArray());
doc = new HPSFPropertiesOnlyDocument(new POIFSFileSystem(bais));
// Check properties are still there
Assert.AreEqual("Microsoft Word 10.0", si.ApplicationName);
Assert.AreEqual("", si.Title);
Assert.AreEqual("", si.Author);
Assert.AreEqual("Cour de Justice", dsi.Company);
}
static void Main(string[] args)
{
POIFSFileSystem fs = new POIFSFileSystem();
//get the root directory
DirectoryEntry dir = fs.Root;
//create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
//Write the stream data of the DocumentSummaryInformation entry to the root directory
dsi.Write(dir, DocumentSummaryInformation.DEFAULT_STREAM_NAME);
//create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
//Write the stream data of the SummaryInformation entry to the root directory
si.Write(dir, SummaryInformation.DEFAULT_STREAM_NAME);
//create a POIFS file called Foo.poifs
FileStream output = new FileStream("Foo.poifs", FileMode.OpenOrCreate);
fs.WriteFileSystem(output);
output.Close();
}
public void Main()
{
POIFSFileSystem fs = new POIFSFileSystem();
//get the root directory
DirectoryEntry dir = fs.Root;
//create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
CustomProperties customProperties = dsi.CustomProperties;
if (customProperties == null)
{
customProperties = new CustomProperties();
}
customProperties.Put("测试", "value A");
customProperties.Put("BB", "value BB");
customProperties.Put("CCC", "value CCC");
dsi.CustomProperties = customProperties;
//Write the stream data of the DocumentSummaryInformation entry to the root directory
dsi.Write(dir, DocumentSummaryInformation.DEFAULT_STREAM_NAME);
//create a POIFS file called Foo.poifs
FileStream output = new FileStream("Foo.xls", FileMode.OpenOrCreate);
fs.WriteFileSystem(output);
output.Close();
}
private DocumentSummaryInformation SetDocumentSummaryInformation()
{
//文档摘要信息
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "";
return(dsi);
}
public void TestReadDocumentSummaryInformation()
{
POIDataSamples _samples = POIDataSamples.GetHPSFInstance();
string[] files = _samples.GetFiles("Test*.*");
for (int i = 0; i < files.Length; i++)
{
FileStream doc = new FileStream(files[i], FileMode.Open);
Console.WriteLine("Reading file " + doc);
/* Read a Test document <em>doc</em> into a POI filesystem. */
POIFSFileSystem poifs = new POIFSFileSystem(doc);
DirectoryEntry dir = poifs.Root;
DocumentEntry dsiEntry = null;
try
{
dsiEntry = (DocumentEntry)dir.GetEntry(DocumentSummaryInformation.DEFAULT_STREAM_NAME);
}
catch (FileNotFoundException)
{
/*
* A missing document summary information stream is not an error
* and therefore silently ignored here.
*/
}
/*
* If there is a document summry information stream, Read it from
* the POI filesystem.
*/
if (dsiEntry != null)
{
POIFSDocumentReader dis = new POIFSDocumentReader(dsiEntry);
PropertySet ps = new PropertySet(dis);
DocumentSummaryInformation dsi = new DocumentSummaryInformation(ps);
/* Execute the Get... methods. */
Console.WriteLine(dsi.ByteCount);
Console.WriteLine(dsi.ByteOrder);
Console.WriteLine(dsi.Category);
Console.WriteLine(dsi.Company);
Console.WriteLine(dsi.CustomProperties);
// FIXME Console.WriteLine(dsi.Docparts);
// FIXME Console.WriteLine(dsi.HeadingPair);
Console.WriteLine(dsi.HiddenCount);
Console.WriteLine(dsi.LineCount);
Console.WriteLine(dsi.LinksDirty);
Console.WriteLine(dsi.Manager);
Console.WriteLine(dsi.MMClipCount);
Console.WriteLine(dsi.NoteCount);
Console.WriteLine(dsi.ParCount);
Console.WriteLine(dsi.PresentationFormat);
Console.WriteLine(dsi.Scale);
Console.WriteLine(dsi.SlideCount);
}
}
}
public void Test52337()
{
// document summary stream from test1-excel.doc attached to Bugzilla 52337
String hex =
"FE FF 00 00 05 01 02 00 00 00 00 00 00 00 00 00 00 00 00 00" +
"00 00 00 00 02 00 00 00 02 D5 CD D5 9C 2E 1B 10 93 97 08 00" +
"2B 2C F9 AE 44 00 00 00 05 D5 CD D5 9C 2E 1B 10 93 97 08 00" +
"2B 2C F9 AE 58 01 00 00 14 01 00 00 0C 00 00 00 01 00 00 00" +
"68 00 00 00 0F 00 00 00 70 00 00 00 05 00 00 00 98 00 00 00" +
"06 00 00 00 A0 00 00 00 11 00 00 00 A8 00 00 00 17 00 00 00" +
"B0 00 00 00 0B 00 00 00 B8 00 00 00 10 00 00 00 C0 00 00 00" +
"13 00 00 00 C8 00 00 00 16 00 00 00 D0 00 00 00 0D 00 00 00" +
"D8 00 00 00 0C 00 00 00 F3 00 00 00 02 00 00 00 E4 04 00 00" +
"1E 00 00 00 20 00 00 00 44 65 70 61 72 74 6D 65 6E 74 20 6F" +
"66 20 49 6E 74 65 72 6E 61 6C 20 41 66 66 61 69 72 73 00 00" +
"03 00 00 00 05 00 00 00 03 00 00 00 01 00 00 00 03 00 00 00" +
"47 03 00 00 03 00 00 00 0F 27 0B 00 0B 00 00 00 00 00 00 00" +
"0B 00 00 00 00 00 00 00 0B 00 00 00 00 00 00 00 0B 00 00 00" +
"00 00 00 00 1E 10 00 00 01 00 00 00 0F 00 00 00 54 68 69 73" +
"20 69 73 20 61 20 74 65 73 74 00 0C 10 00 00 02 00 00 00 1E" +
"00 00 00 06 00 00 00 54 69 74 6C 65 00 03 00 00 00 01 00 00" +
"00 00 00 00 A8 00 00 00 03 00 00 00 00 00 00 00 20 00 00 00" +
"01 00 00 00 38 00 00 00 02 00 00 00 40 00 00 00 01 00 00 00" +
"02 00 00 00 0C 00 00 00 5F 50 49 44 5F 48 4C 49 4E 4B 53 00" +
"02 00 00 00 E4 04 00 00 41 00 00 00 60 00 00 00 06 00 00 00" +
"03 00 00 00 74 00 74 00 03 00 00 00 09 00 00 00 03 00 00 00" +
"00 00 00 00 03 00 00 00 05 00 00 00 1F 00 00 00 0C 00 00 00" +
"4E 00 6F 00 72 00 6D 00 61 00 6C 00 32 00 2E 00 78 00 6C 00" +
"73 00 00 00 1F 00 00 00 0A 00 00 00 53 00 68 00 65 00 65 00" +
"74 00 31 00 21 00 44 00 32 00 00 00 ";
byte[] bytes = HexRead.ReadFromString(hex);
PropertySet ps = PropertySetFactory.Create(new ByteArrayInputStream(bytes));
DocumentSummaryInformation dsi = (DocumentSummaryInformation)ps;
Section s = (Section)dsi.Sections[0];
Object hdrs = s.GetProperty(PropertyIDMap.PID_HEADINGPAIR);
Assert.IsNotNull(hdrs, "PID_HEADINGPAIR was not found");
Assert.IsTrue(hdrs is byte[], "PID_HEADINGPAIR: expected byte[] but was " + hdrs.GetType());
// parse the value
Vector v = new Vector((short)Variant.VT_VARIANT);
v.Read((byte[])hdrs, 0);
TypedPropertyValue[] items = v.Values;
Assert.AreEqual(2, items.Length);
Assert.IsNotNull(items[0].Value);
Assert.IsTrue(items[0].Value is CodePageString, "first item must be CodePageString but was " + items[0].GetType());
Assert.IsNotNull(items[1].Value);
Assert.IsTrue(Number.IsInteger(items[1].Value), "second item must be Integer but was " + items[1].Value.GetType());
Assert.AreEqual(1, (int)items[1].Value);
}
/**
* Closes the MemoryStream and Reads it into a MemoryStream.
* When finished writing information this method is used in the Tests to
* start Reading from the Created document and then the see if the results match.
*
*/
private void CloseAndReOpen()
{
dsi.Write(dir, DocumentSummaryInformation.DEFAULT_STREAM_NAME);
si.Write(dir, SummaryInformation.DEFAULT_STREAM_NAME);
si = null;
dsi = null;
try
{
poifs.WriteFileSystem(bout);
bout.Flush();
}
catch (IOException)
{
////e.printStackTrace();
Assert.Fail();
}
Stream is1 = new MemoryStream(bout.ToArray());
Assert.IsNotNull(is1);
poifs = new POIFSFileSystem(is1);;
is1.Close();
Assert.IsNotNull(poifs);
/* Read the document summary information. */
dir = poifs.Root;
DocumentEntry dsiEntry = (DocumentEntry)
dir.GetEntry(DocumentSummaryInformation.DEFAULT_STREAM_NAME);
DocumentInputStream dis = new DocumentInputStream(dsiEntry);
PropertySet ps = new PropertySet(dis);
dis.Close();
dsi = new DocumentSummaryInformation(ps);
try
{
dsiEntry = (DocumentEntry)
dir.GetEntry(SummaryInformation.DEFAULT_STREAM_NAME);
dis = new DocumentInputStream(dsiEntry);
ps = new PropertySet(dis);
dis.Close();
si = new SummaryInformation(ps);
}
catch (FileNotFoundException)
{
/* There is no document summary information yet. We have to Create a
* new one. */
si = PropertySetFactory.CreateSummaryInformation();
Assert.IsNotNull(si);
}
}
/// <summary>
/// 导出EXCEL,可以导出多个sheet
/// </summary>
/// <param name="dtSources">原始数据数组类型</param>
/// <param name="strFileName">路径</param>
public static MemoryStream ExportEasy(DataTable[] dtSources)
{
HSSFWorkbook workbook = new HSSFWorkbook();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "上海至云信息科技";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "上海至云信息科技"; //填加xls文件作者信息
si.ApplicationName = "上海至云信息科技"; //填加xls文件创建程序信息
si.LastAuthor = "上海至云信息科技"; //填加xls文件最后保存者信息
si.Comments = "上海至云信息科技"; //填加xls文件作者信息
si.Title = "上海至云信息科技"; //填加xls文件标题信息
si.Subject = "上海至云信息科技"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
for (int k = 0; k < dtSources.Length; k++)
{
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(dtSources[k].TableName.ToString());
//填充表头
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in dtSources[k].Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
//填充内容
for (int i = 0; i < dtSources[k].Rows.Count; i++)
{
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dtSources[k].Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dtSources[k].Rows[i][j].ToString());
}
}
}
//保存
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public void ConvertKaFightToXls(string filepath, string fileDest)
{
HSSFWorkbook hssfworkbook;
hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "MythKAst";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = filepath;
hssfworkbook.SummaryInformation = si;
using (FileStream indexfs = new FileStream(filepath, FileMode.Open))
{
using (BinaryReader br = new BinaryReader(indexfs))
{
List <string> tmpstr = new List <string>();
int num = (int)indexfs.Length / 4;
tmpstr.Add(filepath + ":" + num.ToString());
//begin
ISheet sheet1 = hssfworkbook.CreateSheet("fight");
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue(filepath + "//请保留文件头");
row.CreateCell(1).SetCellValue("拳");
row.CreateCell(2).SetCellValue("剑");
row.CreateCell(3).SetCellValue("刀");
row.CreateCell(4).SetCellValue("特");
row.CreateCell(5).SetCellValue("暗");
for (int i = 1; i < 500 + 1; i++)
{
row = sheet1.CreateRow(i);
row.CreateCell(0).SetCellValue(i - 1);
for (int j = 1; j < 5 + 1; j++)
{
row.CreateCell(j).SetCellValue(br.ReadInt16());
}
}
using (FileStream fs = new FileStream(fileDest, FileMode.Create))
{
hssfworkbook.Write(fs);
}
}
}
Console.WriteLine("Saved in " + fileDest);
}
/// <summary>
/// 设置xls的信息
/// </summary>
private void InitializeWorkbook()
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI Example";
hssfworkbook.SummaryInformation = si;
}
/// <summary>
/// 导出Excel文件
/// </summary>
/// <param name="dt"></param>
/// <param name="path"></param>
public static void ExportExcel(DataTable dt, string path)
{
hssfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
hssfworkbook.SummaryInformation = si;
ISheet sheet1 = hssfworkbook.CreateSheet(string.IsNullOrEmpty(dt.TableName) ? "Sheet1" : dt.TableName);
IRow rowTitle = sheet1.CreateRow(0);
ICell cellTitle;
ICellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
for (int m = 0; m < dt.Columns.Count; m++)
{
cellTitle = rowTitle.CreateCell(m);
cellTitle.CellStyle = style;
cellTitle.SetCellValue(dt.Columns[m].ColumnName);
}
IRow row;
ICell cell;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
row = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = row.CreateCell(j);
cell.CellStyle = style;
string obj = dt.Rows[i][j].ToString();
cell.SetCellValue(obj);
}
}
}
var file = new FileStream(path, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
/// <summary>
/// 实例一个工作薄
/// </summary>
public Workbook()
{
workbook = new HSSFWorkbook();
#region 右击文件 属性信息
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "SiBu";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
#endregion
}
static void WriteDsiInfo()
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Amble";
hssfworkbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "Amble Order Info";
hssfworkbook.SummaryInformation = si;
}
/// <summary>
/// Funcion encargada de Insertar la información de las propiedades al documento Excel que se genere
/// </summary>
/// <remarks></remarks>
private void InfoExcel()
{
DocumentSummaryInformation DocInfo = default(DocumentSummaryInformation);
SummaryInformation SummaryInfo = default(SummaryInformation);
DocInfo = PropertySetFactory.CreateDocumentSummaryInformation();
DocInfo.Company = p_Company;
hssfworkbook.DocumentSummaryInformation = DocInfo;
SummaryInfo = PropertySetFactory.CreateSummaryInformation();
SummaryInfo.Subject = p_DescriptionReport;
hssfworkbook.SummaryInformation = SummaryInfo;
}
