/// <summary>
/// 创建excel workbook
/// </summary>
/// <returns></returns>
public static HSSFWorkbook CreateWorkbook()
{
HSSFWorkbook workbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "eContract";
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "eContract Sunny";
workbook.DocumentSummaryInformation = dsi;
workbook.SummaryInformation = si;
return(workbook);
}
private SummaryInformation SetSummaryInformation()
{
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;
return(si);
}
static void InitializeWorkbook()
{
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;
}
/// <summary>
/// 设置Workbook的2个属性信息
/// </summary>
private void SetFileInfo()
{
//设置Workbook的DocumentSummaryInformation信息
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "生意专家(www.i200.cn)";
this.Workbook.DocumentSummaryInformation = dsi;
//设置Workbook的SummaryInformation信息
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "生意专家(www.i200.cn) 导出数据";
this.Workbook.SummaryInformation = si;
}
private void InitializeWorkbook(HSSFWorkbook workbook)
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "深圳畅泊智能停车场有限公司";
workbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "记录导出";
si.Title = "记录导出";
si.Author = "深圳畅泊";
workbook.SummaryInformation = si;
}
public static HSSFWorkbook GetWorkbook(string companyName, string subject)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = companyName;
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = subject;
hssfworkbook.SummaryInformation = si;
return(hssfworkbook);
}
/// <summary>
/// 创建EXCEL文档
/// </summary>
/// <param name="path">保存路径</param>
/// <param name="dt">DataTable</param>
public static void CreatExcel(string fullPath, DataTable dt)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "主题";
hssfworkbook.SummaryInformation = si;
NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet("Sheet");
hssfSheet.DefaultColumnWidth = 18;
NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//表头
NPOI.SS.UserModel.IRow tagRow = hssfSheet.CreateRow(0);
tagRow.RowStyle = cellStyle;
for (int i = 0; i < dt.Columns.Count; i++)
{
tagRow.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
tagRow.GetCell(i).CellStyle = cellStyle;
}
int rowNum = 1;
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(rowNum);
if (rowNum == 50001)//超过五万条数据,新建一张工作表
{
hssfSheet = hssfworkbook.CreateSheet();
rowNum = 1;
NPOI.SS.UserModel.IRow newrow = hssfSheet.CreateRow(0);
row = hssfSheet.CreateRow(1);
for (int j = 0; j < dt.Columns.Count; j++)
{
newrow.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName.ToString());
}
}
//写入记录
for (int j = 0; j < dt.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(dt.Rows[i][dt.Columns[j].ColumnName].ToString());
}
rowNum++;
}
FileStream file = new FileStream(fullPath, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
//创建workbook
private static HSSFWorkbook CreateWorkBook()
{
HSSFWorkbook workbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "test";
workbook.SummaryInformation = si;
return(workbook);
}
/// <summary>
/// 初始化工作薄
/// </summary>
/// <param name="hssfworkbook"></param>
private static void InitializeWorkbook(out HSSFWorkbook hssfworkbook)
{
hssfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "";
hssfworkbook.SummaryInformation = si;
}
/// <summary>
/// Inicializa libro
/// </summary>
public void InitializeWorkbook()
{
//create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "LAN Airlines";
_workbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "SimuLAN report";
_workbook.SummaryInformation = si;
}
/// <summary>
/// 初始化execl
/// </summary>
public static void InitializeWorkbook()
{
hssfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Excel工具";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "数据导出";
hssfworkbook.SummaryInformation = si;
}
public string CreateExcelAndSaveLocalSingleSheet(List <MultiTableDataInfoDto> excelData)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "豫光";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "张承宇"; //填加xls文件作者信息
si.ApplicationName = "LIMS"; //填加xls文件创建程序信息
si.LastAuthor = "张承宇"; //填加xls文件最后保存者信息
si.Comments = "张承宇"; //填加xls文件作者信息
si.Title = "化验数据"; //填加xls文件标题信息
si.Subject = "化验数据"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
int row = 0;
sheet.CreateRow(row);
row++;
foreach (var tableDto in excelData)
{
if (tableDto.TableData.Count > 0)
{
CreateExcelSheet(workbook, sheet, tableDto.TableData, tableDto.TableHead, tableDto.TableTitle, ref row);
row += 2;
}
}
string fileName = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string filePath = DirPath + fileName;
try
{
using (FileStream fs = new FileStream(filePath, FileMode.CreateNew, FileAccess.Write))
{
workbook.Write(fs);
}
return(fileName);
}
catch
{
return(null);
}
}
/// <summary>
/// 初始化工作区
/// </summary>
private static void InitializeWorkbook(HSSFWorkbook hssfworkbook)
{
//信息
var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "";
hssfworkbook.DocumentSummaryInformation = dsi;
//工区名称
var si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "";
hssfworkbook.SummaryInformation = si;
}
private void CreateSummaries()
{
var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "中国石油天然气运输公司";
DocumentSummaryInformation = dsi;
var si = PropertySetFactory.CreateSummaryInformation();
si.Subject = defaultSheet.SheetName;
si.Author = "运输管理系统";
SummaryInformation = si;
}
/**
* Performs the check described in {@link #TestReCreate()} for a single
* POI filesystem.
*
* @param f the POI filesystem to check
*/
private void TestRecreate(FileInfo f)
{
Console.WriteLine("Recreating file \"" + f.Name + "\"");
/* Read the POI filesystem's property Set streams: */
POIFile[] psf1 = Util.ReadPropertySets(_samples.GetFile(f.Name));
/* Create a new POI filesystem containing the origin file's
* property Set streams: */
FileInfo fi = new FileInfo(f.Name);
FileStream copy = File.Create(fi.Name);
//copy.deleteOnExit();
FileStream out1 = copy;
POIFSFileSystem poiFs = new POIFSFileSystem();
for (int i = 0; i < psf1.Length; i++)
{
Stream in1 =
new ByteArrayInputStream(psf1[i].GetBytes());
PropertySet psIn = PropertySetFactory.Create(in1);
MutablePropertySet psOut = new MutablePropertySet(psIn);
MemoryStream psStream =
new MemoryStream();
psOut.Write(psStream);
psStream.Close();
byte[] streamData = psStream.ToArray();
poiFs.CreateDocument(new ByteArrayInputStream(streamData),
psf1[i].GetName());
poiFs.WriteFileSystem(out1);
}
/* Read the property Set streams from the POI filesystem just
* Created. */
POIFile[] psf2 = Util.ReadPropertySets(copy);
for (int i = 0; i < psf2.Length; i++)
{
byte[] bytes1 = psf1[i].GetBytes();
byte[] bytes2 = psf2[i].GetBytes();
Stream in1 = new ByteArrayInputStream(bytes1);
Stream in2 = new ByteArrayInputStream(bytes2);
PropertySet ps1 = PropertySetFactory.Create(in1);
PropertySet ps2 = PropertySetFactory.Create(in2);
/* Compare the property Set stream with the corresponding one
* from the origin file and check whether they are equal. */
Assert.AreEqual(ps1, ps2, "Equality for file " + f.Name);
}
out1.Close();
}
private static void InitializeWorkbook()
{
//Create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "广东百德朗科技有限公司";
workbook.DocumentSummaryInformation = dsi;
//Create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "预付费系统报表导出";
workbook.SummaryInformation = si;
}
void InitializeWorkbook()
{
hssfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Tesco Lotus";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "Report KPI";
hssfworkbook.SummaryInformation = si;
}
private void RunTest(FileStream file)
{
/* Read a Test document <em>doc</em> into a POI filesystem. */
POIFSFileSystem poifs = new POIFSFileSystem(file);
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, else Create a new one.
*/
DocumentSummaryInformation dsi;
if (dsiEntry != null)
{
DocumentInputStream dis = new DocumentInputStream(dsiEntry);
PropertySet ps = new PropertySet(dis);
dsi = new DocumentSummaryInformation(ps);
}
else
{
dsi = PropertySetFactory.CreateDocumentSummaryInformation();
}
CustomProperties cps = dsi.CustomProperties;
if (cps == null)
{
/* The document does not have custom properties. */
return;
}
foreach (var de in cps)
{
CustomProperty cp = (CustomProperty)de.Value;
Assert.IsNotNull(cp.Name);
Assert.IsNotNull(cp.Value);
}
}
public void SetProperty()
{
try
{
using (FileStream file = new FileStream(FileNameFullPath, FileMode.Open, FileAccess.Write))
{
POIFSFileSystem fs = new POIFSFileSystem(file);
var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.RemoveByteCount();
dsi.RemoveCategory();
dsi.RemoveCompany();
dsi.RemoveCustomProperties();
dsi.RemoveDocparts();
dsi.RemoveHeadingPair();
dsi.RemoveHiddenCount();
dsi.RemoveLineCount();
dsi.RemoveLinksDirty();
dsi.RemoveManager();
dsi.RemoveMMClipCount();
dsi.RemoveNoteCount();
dsi.RemoveParCount();
dsi.RemovePresentationFormat();
dsi.RemoveScale();
dsi.RemoveSlideCount();
var si = PropertySetFactory.CreateSummaryInformation();
si.RemoveApplicationName();
si.RemoveAuthor();
si.RemoveCharCount();
si.RemoveComments();
si.RemoveCreateDateTime();
si.RemoveEditTime();
si.RemoveKeywords();
si.RemoveLastAuthor();
si.RemoveLastPrinted();
si.RemoveLastSaveDateTime();
si.RemovePageCount();
si.RemoveRevNumber();
si.RemoveSecurity();
si.RemoveSubject();
si.RemoveTemplate();
si.RemoveThumbnail();
si.RemoveTitle();
si.RemoveWordCount();
}
}
catch (Exception ex)
{
throw ex;
}
}
public ExcelWrapper(string companyName, string subject, string sheetName)
{
hssfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = companyName;
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = subject;
hssfworkbook.SummaryInformation = si;
sheet1 = hssfworkbook.CreateSheet(sheetName);
row = sheet1.CreateRow(LinhaExcel);
}
public void InitializeWorkbook()
{
hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "红旭集团";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "xxx";
hssfworkbook.SummaryInformation = si;
}
protected virtual void InitializeExcel()
{
WorkBook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "XXX company";
WorkBook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "XXX Report";
si.Author = "zliancheng";
WorkBook.SummaryInformation = si;
}
/// <summary>
/// Экспорт данных в Excel
/// </summary>
/// <param name="sourceTable"></param>
/// <param name="fileName"></param>
/// <exception cref="Exception"></exception>
public void Export(DataTable sourceTable, string fileName)
{
using (var workbook = new HSSFWorkbook())
{
//Create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = _companyAttribute.Company;
workbook.DocumentSummaryInformation = dsi;
//Create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = _productAttribute.Product;
workbook.SummaryInformation = si;
using (var fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
using (var sheet = workbook.CreateSheet(Resource.SheetNameDefault))
{
Row headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
{
Cell dataCell = headerRow.CreateCell(column.Ordinal);
dataCell.SetCellValue(column.ColumnName);
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
Row dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
Cell dataCell = dataRow.CreateCell(column.Ordinal);
dataCell.SetCellValue(row[column].ToString());
}
rowIndex++;
}
workbook.Write(fs);
fs.Flush();
fs.Close();
}
}
}
public void ConvertKaIndexToXls(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(string.Format(@"./{0}/index.ka", 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("index");
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue(filepath + "//请保留文件头");
row.CreateCell(1).SetCellValue("偏移x");
row.CreateCell(2).SetCellValue("偏移y");
for (int i = 1; i < num; i++)
{
row = sheet1.CreateRow(i);
row.CreateCell(0).SetCellValue(i - 1);
row.CreateCell(1).SetCellValue(br.ReadInt16());
row.CreateCell(2).SetCellValue(br.ReadInt16());
}
using (FileStream fs = new FileStream(fileDest, FileMode.Create))
{
hssfworkbook.Write(fs);
}
}
}
Console.WriteLine("Saved in " + fileDest);
}
public static MemoryStream ExportExcel(List <ExportExcelInfo> exportExcelList)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//Excel文件的摘要信息
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "blog.csdn.net";
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "Export Excel";
hssfworkbook.SummaryInformation = si;
//下面代码输出的Excel有三列(姓名、年龄、性别)
ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
int i = 0;
IRow row0 = sheet1.CreateRow(i++);
row0.CreateCell(0).SetCellValue("编号");
row0.CreateCell(1).SetCellValue("姓名");
row0.CreateCell(2).SetCellValue("日期");
row0.CreateCell(3).SetCellValue("场次");
row0.CreateCell(4).SetCellValue("试卷");
row0.CreateCell(5).SetCellValue("分数");
row0.CreateCell(6).SetCellValue("是否提交");
foreach (ExportExcelInfo excelInfo in exportExcelList)
{
IRow row = sheet1.CreateRow(i++);
row.CreateCell(0).SetCellValue($"{excelInfo.LogId}");
row.CreateCell(1).SetCellValue($"{excelInfo.UserName}");
row.CreateCell(2).SetCellValue($"{excelInfo.Date}");
row.CreateCell(3).SetCellValue($"{excelInfo.Part}");
row.CreateCell(4).SetCellValue($"{excelInfo.Title}");
row.CreateCell(5).SetCellValue($"{excelInfo.Score}");
row.CreateCell(6).SetCellValue($"{(excelInfo.IsSubmit ? "是" : "否")}");
}
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
return(ms);
}
/// <summary>
/// 属性摘要信息
/// </summary>
/// <param name="fileName">文件名称</param>
private SummaryInformation SummaryInfo(string fileName)
{
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "CAISHAOFA";
si.LastAuthor = si.Author;
si.Comments = si.Author;
si.ApplicationName = fileName;
si.Title = fileName;
si.Subject = fileName;
si.CreateDateTime = DateTime.Now;
return si;
}
public void TestSectionMethods()
{
SummaryInformation si = (SummaryInformation)
PropertySetFactory.Create(new ByteArrayInputStream
(poiFiles[0].GetBytes()));
IList sections = si.Sections;
Section s = (Section)sections[0];
Assert.IsTrue(Arrays.Equals
(s.FormatID.Bytes, SectionIDMap.SUMMARY_INFORMATION_ID));
Assert.IsNotNull(s.Properties);
Assert.AreEqual(18, s.PropertyCount);
Assert.AreEqual("Titel", s.GetProperty(2));
Assert.AreEqual(1760, s.Size);
}
/**
* Will create whichever of SummaryInformation
* and DocumentSummaryInformation (HPSF) properties
* are not already part of your document.
* This is normally useful when creating a new
* document from scratch.
* If the information properties are already there,
* then nothing will happen.
*/
public void CreateInformationProperties()
{
if (!initialized)
{
ReadProperties();
}
if (sInf == null)
{
sInf = PropertySetFactory.CreateSummaryInformation();
}
if (dsInf == null)
{
dsInf = PropertySetFactory.CreateDocumentSummaryInformation();
}
}
public void ProcessPOIFSReaderEvent(POIFSReaderEvent evt)
{
try
{
psa[0] = PropertySetFactory.Create(evt.Stream);
}
catch (Exception ex)
{
throw new RuntimeException(ex.Message);
/* FIXME (2): Replace the previous line by the following
* one once we no longer need JDK 1.3 compatibility. */
// throw new RuntimeException(ex);
}
}
void reader2_StreamReaded(object sender, POIFSReaderEventArgs e)
{
try
{
psa[0] = PropertySetFactory.Create(e.Stream);
}
catch
{
throw;
/* FIXME (2): Replace the previous line by the following
* one once we no longer need JDK 1.3 compatibility. */
// throw new RuntimeException(ex);
}
}
