/// <summary>
/// Creates the excel workbook.
/// </summary>
/// <param name="subject">The subject.</param>
public void CreateWorkbook(string subject)
{
//Creating the excel workbook
NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
//Creating summary information to the document
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Thoris";
//Applying summary information to the document
wb.DocumentSummaryInformation = dsi;
//Creating summary information for the data
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Subject = subject;
//Applying summary information to the data
wb.SummaryInformation = si;
_workbook = wb;
//Creating the current styles
Font font = _workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
NPOI.SS.UserModel.CellStyle cellStyle = _workbook.CreateCellStyle();
cellStyle.SetFont(font);
_headerStyle = CreateHeaderStyle(_workbook);
_dataStyle = CreateStyle(_workbook, false);
}
public void ProcessDocument(HWPFDocumentCore wordDocument)
{
try
{
NPOI.HPSF.SummaryInformation summaryInformation = wordDocument.SummaryInformation;
if (summaryInformation != null)
{
ProcessDocumentInformation(summaryInformation);
}
}
catch (Exception exc)
{
logger.Log(POILogger.WARN, "Unable to process document summary information: ", exc, exc);
}
Range docRange = wordDocument.GetRange();
if (docRange.NumSections == 1)
{
ProcessSingleSection(wordDocument, docRange.GetSection(0));
AfterProcess();
return;
}
ProcessDocumentPart(wordDocument, docRange);
AfterProcess();
}
/// <summary>
/// 模板导入到NPOI Workbook中
/// </summary>
/// <param name="dataSourceFilePath">数据源路经</param>
/// <returns></returns>
private NPOI.HSSF.UserModel.HSSFWorkbook InitializeWorkbook(string dataSourceFilePath)
{
try
{
NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook = null;
System.IO.FileStream file = new System.IO.FileStream(dataSourceFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
if (null == file)
{
return(hssfworkbook);
}
hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(file);
if (null == hssfworkbook)
{
return(hssfworkbook);
}
//create a entry of DocumentSummaryInformation
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "test";
hssfworkbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Subject = "test";
hssfworkbook.SummaryInformation = si;
return(hssfworkbook);
}
catch (Exception ex)
{
return(null);
throw new Exception(ex.ToString());
}
}
/// <summary>
/// 添加Excel文件属性信息
/// </summary>
/// <param name="book"></param>
private static void AddBookPropertyInfo(HSSFWorkbook book)
{
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "PM";
book.DocumentSummaryInformation = dsi;
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Author = "品茗造价"; //填加xls文件作者信息
si.ApplicationName = "NPOI"; //填加xls文件创建程序信息
si.LastAuthor = "品茗造价"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "导出的Excel"; //填加xls文件标题信息
si.Subject = "数据导出"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
book.SummaryInformation = si;
}
protected override void ProcessDocumentInformation(NPOI.HPSF.SummaryInformation summaryInformation)
{
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);
}
}
protected abstract void ProcessDocumentInformation(NPOI.HPSF.SummaryInformation summaryInformation);
/// <summary>
/// Yield
/// </summary>
/// <param name="xlstpmname"></param>
/// <param name="xlsname"></param>
/// <param name="dtSrc"></param>
/// <param name="dtSum"></param>
public static void GridToExcelYield(string xlstpmname, string xlsname, DataTable dtSrc, DataTable dtSum)
{
FileStream file = new FileStream(xlstpmname, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
//create a entry of DocumentSummaryInformation
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "SMEE";
hssfworkbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Subject = "Wafer Review System";
hssfworkbook.SummaryInformation = si;
ISheet sheet1 = hssfworkbook.GetSheet("yield");
for (int i = 0; i < dtSrc.Columns.Count; i++)
{
//ICell cell = sheet1.GetRow(0).CreateCell(i);
ICell cell = sheet1.GetRow(0).GetCell(i);
cell.SetCellType(CellType.String);
ICellStyle style = hssfworkbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
style.FillPattern = FillPattern.NoFill;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
font.Color = (short)FontColor.Red;
style.SetFont(font);
cell.CellStyle = style;
cell.SetCellValue(dtSrc.Columns[i].ColumnName);
//sheet.AutoSizeColumn(i);
}
for (int i = 0, cnt = dtSrc.Rows.Count; i < cnt; i++)
{
if (dtSrc.Rows[i][0].ToString() == "")
{
continue;
}
for (int j = 0; j < dtSrc.Columns.Count; j++)
{
ICell cell = sheet1.GetRow(i + 1).GetCell(j);
object val = dtSrc.Rows[i][j];
if (val is string)
{
cell.SetCellValue(val.ToString());
}
else
{
cell.SetCellValue(double.Parse(val.ToString()));
}
if ((i + 1) == cnt)
{
ICellStyle style = hssfworkbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
style.FillPattern = FillPattern.NoFill;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
IFont font = hssfworkbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
font.FontHeightInPoints = 11;
font.FontName = cell.CellStyle.GetFont(hssfworkbook).FontName;
style.SetFont(font);
cell.CellStyle = style;
}
}
}
ISheet sheet2 = hssfworkbook.GetSheet("report");
//写入classify名称
for (int j = 1; j <= dtSum.Rows.Count; j++)
{
object val = dtSum.Rows[j - 1][0];
if (val.ToString() == "Inspected Die_Y2")
{
continue;
}
ICell cell = sheet2.GetRow(j).GetCell(1);
cell.SetCellValue(val.ToString());
}
//缺陷个数
for (int j = 1; j <= dtSum.Rows.Count; j++)
{
object valv = dtSum.Rows[j - 1][0];
if (valv.ToString() == "Inspected Die_Y2")
{
continue;
}
ICell cell = sheet2.GetRow(j).GetCell(2);
object val = dtSum.Rows[j - 1][1];
cell.SetCellValue(double.Parse(val.ToString()));
}
//被测的die总数
for (int j = 1; j <= dtSum.Rows.Count; j++)
{
object valv = dtSum.Rows[j - 1][0];
if (valv.ToString() == "Inspected Die_Y2")
{
continue;
}
ICell cell = sheet2.GetRow(j).GetCell(6);
object val = dtSum.Rows[j - 1][2];
cell.SetCellValue(double.Parse(val.ToString()));
}
//所有缺陷的总和
for (int j = 1; j <= dtSum.Rows.Count; j++)
{
object valv = dtSum.Rows[j - 1][0];
if (valv.ToString() == "Inspected Die_Y2")
{
continue;
}
ICell cell = sheet2.GetRow(j).GetCell(7);
object val = dtSum.Rows[j - 1][3];
cell.SetCellValue(double.Parse(val.ToString()));
}
if (dtSum.Rows.Count < 70)
{
for (int i = dtSum.Rows.Count; i < 70 - dtSum.Rows.Count; i++)
{
ICell cell2 = sheet2.GetRow(i).GetCell(3);
if (cell2 != null)
{
sheet2.GetRow(i).RemoveCell(cell2);
}
ICell cell3 = sheet2.GetRow(i).GetCell(4);
if (cell3 != null)
{
sheet2.GetRow(i).RemoveCell(cell3);
}
ICell cell4 = sheet2.GetRow(i).GetCell(5);
if (cell4 != null)
{
sheet2.GetRow(i).RemoveCell(cell4);
}
}
}
sheet2.ForceFormulaRecalculation = true;
//写入数据
FileStream file2 = new FileStream(xlsname, FileMode.Create);
hssfworkbook.Write(file2);
file2.Close();
//删除模板
try
{
file.Dispose();
File.Delete(xlstpmname);
}
catch { }
}
public static void GridToExcelPolat(string xlstpmname, string xlsname, DataTable dtSrc, string img)
{
FileStream file = new FileStream(xlstpmname, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
//create a entry of DocumentSummaryInformation
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "SMEE";
hssfworkbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Subject = "Wafer Review System";
hssfworkbook.SummaryInformation = si;
ISheet sheet1 = hssfworkbook.GetSheet("Sheet1");
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 255, 0, 15, 8, 50);
anchor.AnchorType = AnchorType.MoveAndResize;
FileStream fileI = new FileStream(img, FileMode.Open, FileAccess.Read);
byte[] buffer = new byte[fileI.Length];
fileI.Read(buffer, 0, (int)fileI.Length);
int hs = hssfworkbook.AddPicture(buffer, PictureType.JPEG);
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, hs);
picture.Resize();
picture.LineStyle = LineStyle.DotSys;
for (int i = 0; i < dtSrc.Columns.Count; i++)
{
//ICell cell = sheet1.GetRow(0).CreateCell(i);
ICell cell = sheet1.GetRow(0).GetCell(i);
cell.SetCellType(CellType.String);
ICellStyle style = hssfworkbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
style.FillPattern = FillPattern.NoFill;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
font.Color = (short)FontColor.Red;
style.SetFont(font);
cell.CellStyle = style;
cell.SetCellValue(dtSrc.Columns[i].ColumnName);
//sheet.AutoSizeColumn(i);
}
for (int i = 0, cnt = dtSrc.Rows.Count; i < cnt; i++)
{
if (dtSrc.Rows[i][0].ToString() == "")
{
continue;
}
for (int j = 0; j < dtSrc.Columns.Count; j++)
{
ICell cell = sheet1.GetRow(i + 1).GetCell(j);
object val = dtSrc.Rows[i][j];
if (val is string)
{
cell.SetCellValue(val.ToString());
}
else
{
cell.SetCellValue(double.Parse(val.ToString()));
}
}
}
sheet1.ForceFormulaRecalculation = true;
//写入数据
FileStream file2 = new FileStream(xlsname, FileMode.Create);
hssfworkbook.Write(file2);
file2.Close();
//删除模板
try
{
file.Dispose();
File.Delete(xlstpmname);
File.Delete(img);
}
catch { }
}
