static void Main(string[] args)
{
InitializeWorkbook();
Sheet sheet1 = hssfworkbook.CreateSheet("first sheet");
hssfworkbook.CreateSheet("second sheet");
hssfworkbook.CreateSheet("third sheet");
Font boldFont = hssfworkbook.CreateFont();
boldFont.FontHeightInPoints = 22;
boldFont.Boldweight = (short)FontBoldWeight.BOLD;
CellStyle boldStyle = hssfworkbook.CreateCellStyle();
boldStyle.SetFont(boldFont);
Row row = sheet1.CreateRow(1);
Cell cell = row.CreateCell(0);
cell.SetCellValue("This quick brown fox");
cell.CellStyle = (boldStyle);
// Set the columns to repeat from column 0 to 2 on the first sheet
hssfworkbook.SetRepeatingRowsAndColumns(0, 0, 2, -1, -1);
// Set the rows to repeat from row 0 to 2 on the second sheet.
hssfworkbook.SetRepeatingRowsAndColumns(1, -1, -1, 0, 2);
// Set the the repeating rows and columns on the third sheet.
hssfworkbook.SetRepeatingRowsAndColumns(2, 4, 5, 1, 2);
WriteToFile();
}
static void Main(string[] args)
{
InitializeWorkbook();
////cell style for hyperlinks
////by default hyperlinks are blue and underlined
CellStyle hlink_style = hssfworkbook.CreateCellStyle();
Font hlink_font = hssfworkbook.CreateFont();
hlink_font.Underline = (byte)FontUnderlineType.SINGLE;
hlink_font.Color = HSSFColor.BLUE.index;
hlink_style.SetFont(hlink_font);
Cell cell;
Sheet sheet = hssfworkbook.CreateSheet("Hyperlinks");
//URL
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.URL);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new HSSFHyperlink(HyperlinkType.FILE);
link.Address = ("link1.xls");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new HSSFHyperlink(HyperlinkType.EMAIL);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a place in this workbook
//Create a target sheet and cell
Sheet sheet2 = hssfworkbook.CreateSheet("Target Sheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target Cell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.DOCUMENT);
link.Address = ("'Target Sheet'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
WriteToFile();
}
private void SetBold(Cell cell, CellStyle style)
{
if (cell.IsBold)
{
Font font = sheet.Workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
style.SetFont(font);
}
}
private void font_color(HSSFWorkbook hssfworkbook, Cell cell, short color)
{
CellStyle cellStyleFontColor = hssfworkbook.CreateCellStyle();
NPOI.SS.UserModel.Font font1 = hssfworkbook.CreateFont();
font1.Color = color;
cellStyleFontColor.SetFont(font1);
cell.CellStyle = cellStyleFontColor;
}
/// <summary>
/// 把DataTable 转为Excel 内容
/// </summary>
/// <param name="dt"></param>
/// <param name="startRow"></param>
/// <param name="endRow"></param>
/// <param name="book"></param>
/// <param name="sheetName"></param>
private void DataWrite2Sheet(DataTable dt, int startRow, int endRow, HSSFWorkbook book, string sheetName)
{
//头部样式
CellStyle headstyle = book.CreateCellStyle();
headstyle.Alignment = HorizontalAlignment.CENTER;
headstyle.VerticalAlignment = VerticalAlignment.CENTER;
Font headfont = book.CreateFont();
headfont.Boldweight = 700;
headstyle.SetFont(headfont);
headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
headstyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
headstyle.BorderBottom = CellBorderType.THIN;
headstyle.BorderLeft = CellBorderType.THIN;
headstyle.BorderRight = CellBorderType.THIN;
headstyle.BorderTop = CellBorderType.THIN;
NPOI.SS.UserModel.Sheet sheet = book.CreateSheet(sheetName);
NPOI.SS.UserModel.Row header = sheet.CreateRow(0);
header.Height = 20 * 20;
//表格内容样
CellStyle dataStyle = book.CreateCellStyle();
dataStyle.BorderBottom = CellBorderType.THIN;
dataStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
dataStyle.BorderLeft = CellBorderType.THIN;
dataStyle.BorderRight = CellBorderType.THIN;
dataStyle.BorderTop = CellBorderType.THIN;
for (int i = 0; i < dt.Columns.Count; i++)
{
NPOI.SS.UserModel.Cell cell = header.CreateCell(i);
cell.CellStyle = headstyle;
string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName;
cell.SetCellValue(val);
}
int rowIndex = 1;
for (int i = startRow; i <= endRow; i++)
{
DataRow dtRow = dt.Rows[i];
NPOI.SS.UserModel.Row excelRow = sheet.CreateRow(rowIndex++);
for (int j = 0; j < dtRow.ItemArray.Length; j++)
{
excelRow.CreateCell(j).CellStyle = dataStyle;
excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString());
}
}
}
private CellStyle GetSumStyle(HSSFWorkbook hssfworkbook)
{
CellStyle tstyle = hssfworkbook.CreateCellStyle();
tstyle.Alignment = HorizontalAlignment.RIGHT;
var font = hssfworkbook.CreateFont();
font.Boldweight = short.MaxValue;
tstyle.SetFont(font);
return(tstyle);
}
private CellStyle GetRowTitle(HSSFWorkbook hssfworkbook)
{
CellStyle style = hssfworkbook.CreateCellStyle();
var font = hssfworkbook.CreateFont();
font.Boldweight = short.MaxValue;
font.FontName = "微软雅黑";
style.SetFont(font);
style.FillPattern = FillPatternType.LEAST_DOTS; //前景图案
style.FillForegroundColor = 29; //前景色
style.FillBackgroundColor = 29; //背景色
return(style);
}
/// <summary>
/// 左对齐全边框
/// </summary>
/// <returns></returns>
private CellStyle cellLeftAllBorder()
{
CellStyle styleLeft = hssfworkbook.CreateCellStyle();
styleLeft.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
styleLeft.BorderLeft = CellBorderType.THIN;
styleLeft.BorderBottom = CellBorderType.THIN;
styleLeft.BorderRight = CellBorderType.THIN;
styleLeft.BorderTop = CellBorderType.THIN;
styleLeft.VerticalAlignment = VerticalAlignment.CENTER;
styleLeft.SetFont(cellFont());
return(styleLeft);
}
/// <summary>
/// Setea el estilo usado para los títulos de cada hoja
/// </summary>
/// <param name="hw"></param>
/// <returns></returns>
private CellStyle EstiloTitulo(HSSFWorkbook hw)
{
CellStyle style = hw.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.CENTER;
style.Alignment = HorizontalAlignment.LEFT;
Font font = hw.CreateFont();
SetFuenteTitulos(font);
style.SetFont(font);
style.FillBackgroundColor = HSSFColor.WHITE.index;
return(style);
}
/// <summary>
/// 创建四边框中对齐样式
/// </summary>
/// <returns></returns>
private CellStyle SetCellAllCenterBorder()
{
//创建其它单元格的样式
CellStyle styleCell = hssfworkbook.CreateCellStyle();
styleCell.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER_SELECTION;
styleCell.VerticalAlignment = VerticalAlignment.CENTER;
styleCell.BorderLeft = CellBorderType.THIN;
styleCell.BorderRight = CellBorderType.THIN;
styleCell.BorderTop = CellBorderType.THIN;
styleCell.BorderBottom = CellBorderType.THIN;
styleCell.SetFont(cellFont());
return(styleCell);
}
/// <summary>
/// 获取Cell统一样式
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
public static CellStyle GetCommonCellStyle(this Cell cell)
{
CellStyle style = cell.Sheet.Workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.BorderBottom = CellBorderType.THIN;
Font font = cell.Sheet.Workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = (short)FontBoldWeight.BOLD;
style.SetFont(font);
return(style);
}
private CellStyle GetTitleStyle(HSSFWorkbook hssfworkbook)
{
CellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
var font = hssfworkbook.CreateFont();
font.FontHeight = 20 * 20;
font.FontName = "微软雅黑";
font.Color = HSSFColor.BLACK.index;
style.SetFont(font);
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PINK.index; //前景色
style.FillPattern = FillPatternType.LEAST_DOTS; //前景图案
style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index; //背景色
return(style);
}
/// <summary>
/// 右对齐数字格式
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
public static CellStyle GetRightDataCellStyle(NPOI.SS.UserModel.Cell cell)
{
if (rightDataCellStyle == null)
{
CellStyle style = cell.Sheet.Workbook.CreateCellStyle();
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.BorderTop = CellBorderType.THIN;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
NPOI.SS.UserModel.Font font = cell.Sheet.Workbook.CreateFont();
font.FontHeightInPoints = 9;
style.SetFont(font);
style.DataFormat = cell.Sheet.Workbook.CreateDataFormat().GetFormat("_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * \" - \"??_ ;_ @_ ");
rightDataCellStyle = style;
}
return(rightDataCellStyle);
}
private void AddTitle(HSSFSheet sheet, int col, string content)
{
HSSFRow row = col == 0 ? sheet.CreateRow(0) as HSSFRow : sheet.GetRow(0) as HSSFRow;
HSSFCell cell = row.CreateCell(col) as HSSFCell;
cell.SetCellValue(new HSSFRichTextString(content));
CellStyle style = sheet.Workbook.CreateCellStyle();
Font font = sheet.Workbook.CreateFont();
font.Color = HSSFColor.LIGHT_BLUE.index;
font.Boldweight = (short)700;
style.Alignment = HorizontalAlignment.CENTER;
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.SetFont(font);
cell.CellStyle = style;
sheet.SetColumnWidth(col, mDynameter * 70);
}
/// <summary>
/// 设置excel中单元格的样式
/// djin--2012-5-25
/// </summary>
/// <param name="hssfworkbook"></param>
/// <param name="options"></param>
/// <returns></returns>
public CellStyle setCellstyle(HSSFWorkbook hssfworkbook, string[] options)
{
CellStyle style3 = hssfworkbook.CreateCellStyle();
foreach (string s in options)
{
if (s == "Border")
{
style3.BorderBottom = CellBorderType.THIN;
style3.BorderLeft = CellBorderType.THIN;
style3.BorderRight = CellBorderType.THIN;
style3.BorderTop = CellBorderType.THIN;
}
if (s == "BorderNone")
{
style3.BorderBottom = CellBorderType.NONE;
style3.BorderLeft = CellBorderType.NONE;
style3.BorderRight = CellBorderType.NONE;
style3.BorderTop = CellBorderType.NONE;
}
if (s == "Left")
{
style3.VerticalAlignment = VerticalAlignment.CENTER;
style3.Alignment = HorizontalAlignment.LEFT;
}
if (s == "Right")
{
style3.VerticalAlignment = VerticalAlignment.CENTER;
style3.Alignment = HorizontalAlignment.RIGHT;
}
if (s == "DateTime")
{
DataFormat format = hssfworkbook.CreateDataFormat();
style3.DataFormat = format.GetFormat("yyyy-mm-dd");
}
}
Font font3 = hssfworkbook.CreateFont();
font3.FontHeightInPoints = 9;
style3.SetFont(font3);
return(style3);
}
/// <summary>
/// 居中对齐加粗格式
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
public static CellStyle GetCenterBoldCellStyle(NPOI.SS.UserModel.Cell cell)
{
if (centerBoldCellStyle == null)
{
CellStyle style = cell.Sheet.Workbook.CreateCellStyle();
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.BorderTop = CellBorderType.THIN;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
NPOI.SS.UserModel.Font font = cell.Sheet.Workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = (short)FontBoldWeight.BOLD;
style.SetFont(font);
centerBoldCellStyle = style;
}
return(centerBoldCellStyle);
}
public void ExportarListaNegra(DataTable blackList, string caminhoSalvar)
{
doc = new HSSFWorkbook();
try
{
Sheet planilha = doc.CreateSheet();
CellStyle estilo = doc.CreateCellStyle();
estilo.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
estilo.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.ROYAL_BLUE.index;
estilo.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.ROYAL_BLUE.index;
estilo.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
NPOI.SS.UserModel.Font fonte = doc.CreateFont();
fonte.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
fonte.FontName = "Arial";
fonte.FontHeightInPoints = 16;
fonte.Color = NPOI.HSSF.Util.HSSFColor.WHITE.index;
estilo.SetFont(fonte);
Row linha = planilha.CreateRow(0);
linha.HeightInPoints = 22;
Cell celula = linha.CreateCell(0);
celula.CellStyle = estilo;
celula.SetCellValue(blackList.Rows[0][0].ToString());
planilha.AddMergedRegion(new CellRangeAddress(0, 0, 0, 8));
//pula 1 linha
int linhaInicial = 2;
//ignora a coluna com o nome do evento
int colunaInicial = 1;
GerarPlanilhaNPOI(blackList, caminhoSalvar, planilha, linhaInicial, colunaInicial);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Setea el formato de los encabezados de las columnas
/// </summary>
/// <param name="hw">Workbook</param>
/// <returns></returns>
private CellStyle EstiloEncabezadosFilas(HSSFWorkbook hw)
{
CellStyle style = hw.CreateCellStyle();
style.BorderBottom = CellBorderType.THIN;
style.BottomBorderColor = HSSFColor.BLACK.index;
style.BorderLeft = CellBorderType.THIN;
style.LeftBorderColor = HSSFColor.BLACK.index;
style.BorderRight = CellBorderType.THIN;
style.RightBorderColor = HSSFColor.BLACK.index;
style.BorderTop = CellBorderType.THIN;
style.TopBorderColor = HSSFColor.BLACK.index;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.Alignment = HorizontalAlignment.LEFT;
Font font = hw.CreateFont();
SetFuenteEncabezadoColumnas(font);
style.SetFont(font);
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
return(style);
}
/// <summary>
/// Setea el estilo usado para una celda normal
/// </summary>
/// <param name="hw">Workbook</param>
/// <returns></returns>
private CellStyle EstiloCeldaPorcentaje(HSSFWorkbook hw)
{
CellStyle style = hw.CreateCellStyle();
style.BorderBottom = CellBorderType.THIN;
style.BottomBorderColor = HSSFColor.BLACK.index;
style.BorderLeft = CellBorderType.THIN;
style.LeftBorderColor = HSSFColor.BLACK.index;
style.BorderRight = CellBorderType.THIN;
style.RightBorderColor = HSSFColor.BLACK.index;
style.BorderTop = CellBorderType.THIN;
style.TopBorderColor = HSSFColor.BLACK.index;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.Alignment = HorizontalAlignment.GENERAL;
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
Font font = hw.CreateFont();
SetFuenteCeldasNormal(font);
style.SetFont(font);
style.FillBackgroundColor = HSSFColor.WHITE.index;
return(style);
}
static void Main(string[] args)
{
InitializeWorkbook();
Sheet sheet = hssfworkbook.CreateSheet("new sheet");
Row row = sheet.CreateRow(0);
row.HeightInPoints = 30;
Cell cell = row.CreateCell(0);
//set the title of the sheet
cell.SetCellValue("Sales Report");
CellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
//create a font style
Font font = hssfworkbook.CreateFont();
font.FontHeight = 20 * 20;
style.SetFont(font);
cell.CellStyle = style;
//merged cells on single row
//ATTENTION: don't use Region class, which is obsolete
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));
//merged cells on mutiple rows
CellRangeAddress region = new CellRangeAddress(2, 4, 2, 4);
sheet.AddMergedRegion(region);
//set enclosed border for the merged region
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, CellBorderType.DOTTED, NPOI.HSSF.Util.HSSFColor.RED.index);
WriteToFile();
}
/// <summary>
/// Setea el estilo usado para una celda de estilo número sin decimales
/// </summary>
/// <param name="hw">Workbook</param>
/// <returns></returns>
private CellStyle EstiloCeldaNumeroEnteroNegrita(HSSFWorkbook hw)
{
CellStyle style = hw.CreateCellStyle();
style.BorderBottom = CellBorderType.THIN;
style.BottomBorderColor = HSSFColor.BLACK.index;
style.BorderLeft = CellBorderType.THIN;
style.LeftBorderColor = HSSFColor.BLACK.index;
style.BorderRight = CellBorderType.THIN;
style.RightBorderColor = HSSFColor.BLACK.index;
style.BorderTop = CellBorderType.THIN;
style.TopBorderColor = HSSFColor.BLACK.index;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.Alignment = HorizontalAlignment.GENERAL;
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("000");
Font font = hw.CreateFont();
SetFuenteEncabezadoColumnas(font);
font.FontHeightInPoints = 10;
style.SetFont(font);
style.FillBackgroundColor = HSSFColor.WHITE.index;
return(style);
}
private void GerarHeadersNPOI(DataTable tabela, Sheet planilha, ref int linhaInicial, int colunaInicial)
{
try
{
Row linha;
Cell celula;
NPOI.SS.UserModel.Font fonteCabecalho = doc.CreateFont();
fonteCabecalho.FontName = "Arial";
fonteCabecalho.Color = NPOI.HSSF.Util.HSSFColor.WHITE.index;
fonteCabecalho.FontHeightInPoints = 10;
fonteCabecalho.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
CellStyle estiloCabecalho = doc.CreateCellStyle();
estiloCabecalho.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.ROYAL_BLUE.index;
estiloCabecalho.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.ROYAL_BLUE.index;
estiloCabecalho.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
estiloCabecalho.SetFont(fonteCabecalho);
estiloCabecalho.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
linha = planilha.CreateRow(linhaInicial);
for (int colunaDt = colunaInicial, colunaPlan = 0; colunaDt < tabela.Columns.Count; colunaDt++, colunaPlan++)
{
DataColumn cabecalho = tabela.Columns[colunaDt];
celula = linha.CreateCell(colunaPlan, CellType.STRING);
celula.CellStyle = estiloCabecalho;
celula.SetCellValue(cabecalho.ColumnName);
}
linhaInicial++;
}
catch (Exception ex)
{
throw ex;
}
}
/*
* 设置单元格样式
*
* Param pageIndex 页码
* Param rowIndexRelative 相对行号
* Param cellIndex 列号
* Param fontName 字体名称
* Param fontSize 字体大小
*
* Return 生成文件的URL
*/
protected void SetRowCellStyle(int pageIndex, int rowIndexRelative, int cellIndex, String fontName, short fontSize)
{
int rowIndexAbsolute = this.GetRowIndexAbsolute(pageIndex, rowIndexRelative);
Row row = this.GetRow(rowIndexAbsolute);
Cell cell = row.GetCell((short)cellIndex);
if (cell == null)
{
cell = row.CreateCell((short)cellIndex);
}
CellStyle style = workbook.CreateCellStyle();
if (cell.CellStyle != null)
{
style.CloneStyleFrom(cell.CellStyle);
}
Font font = workbook.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontSize;
style.SetFont(font);
cell.CellStyle = workbook.CreateCellStyle();
cell.CellStyle.CloneStyleFrom(style);
}
private CellStyle GetDataStyle()
{
if (dataStyle == null)
{
//数据样式
dataStyle = workbook.CreateCellStyle();
dataStyle.Alignment = HorizontalAlignment.LEFT;//左对齐
//数据单元格的边框
dataStyle.BorderTop = CellBorderType.THIN;
dataStyle.TopBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderRight = CellBorderType.THIN;
dataStyle.RightBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderBottom = CellBorderType.THIN;
dataStyle.BottomBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderLeft = CellBorderType.THIN;
dataStyle.LeftBorderColor = HSSFColor.BLACK.index;
//数据的字体
var datafont = workbook.CreateFont();
datafont.FontHeightInPoints = 11;//字号
dataStyle.SetFont(datafont);
}
return(dataStyle);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://www.yongfa365.com/";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "柳永法"; //填加xls文件作者信息
si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
si.Comments = "说明信息"; //填加xls文件作者信息
si.Title = "NPOI测试"; //填加xls文件标题信息
si.Subject = "NPOI测试Demo"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
CellStyle dateStyle = workbook.CreateCellStyle();
DataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
Row headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
Font font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
Row headerRow = sheet.CreateRow(1);
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
Font font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
Row dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
Cell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook.Dispose();
return(ms);
}
}
private CellStyle GetDataStyle()
{
if (dataStyle == null)
{
//数据样式
dataStyle = workbook.CreateCellStyle();
dataStyle.Alignment = HorizontalAlignment.LEFT;//左对齐
//数据单元格的边框
dataStyle.BorderTop = CellBorderType.THIN;
dataStyle.TopBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderRight = CellBorderType.THIN;
dataStyle.RightBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderBottom = CellBorderType.THIN;
dataStyle.BottomBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderLeft = CellBorderType.THIN;
dataStyle.LeftBorderColor = HSSFColor.BLACK.index;
//数据的字体
var datafont = workbook.CreateFont();
datafont.FontHeightInPoints = 11;//字号
dataStyle.SetFont(datafont);
}
return dataStyle;
}
/**
* 填充报表
*
* Param list [0]huDetailList
*/
public override bool FillValues(String templateFileName, IList<object> list)
{
try
{
this.init(templateFileName, ROW_COUNT);
if (list == null || list.Count == 0) return false;
IList<Hu> huList = (IList<Hu>)list[0];
string userName = "";
if (list.Count == 2)
{
userName = (string)list[1];
}
int count = 0;
foreach (Hu hu in huList)
{
if (hu.Item.Type.Equals("M") || hu.Item.Type.Equals("P"))
{
count++;
}
}
if (count == 0) return false;
this.barCodeFontName = this.GetBarcodeFontName(0, 0);
int pageCount = (int)Math.Ceiling(count / (PAGE_DETAIL_ROW_COUNT * 1.0));
/*
for (int rowNum = 0; rowNum < 25; rowNum += 12)
{
this.SetRowCellBarCode(1, rowNum, 0);
this.SetRowCellBarCode(1, rowNum, 7);
this.SetRowCellBarCode(1, rowNum, 14);
}
*/
this.CopyPage(pageCount, COLUMN_COUNT, 1);
this.sheet.DisplayGridlines = false;
this.sheet.IsPrintGridlines = false;
/*
CellStyle cellStyleT = workbook.CreateCellStyle();
Font fontT = workbook.CreateFont();
fontT.FontHeightInPoints = (short)9;
fontT.FontName = "宋体";
fontT.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
cellStyleT.SetFont(fontT);
*/
int pageIndex = 1;
int num = 1;
string companyCode = entityPreferenceMgr.LoadEntityPreference(BusinessConstants.ENTITY_PREFERENCE_CODE_COMPANY_CODE).Value;
if (companyCode == null) companyCode = string.Empty;
//成品
cellStyle1 = workbook.CreateCellStyle();
Font font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 24;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
cellStyle1.SetFont(font);
cellStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cellStyle1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.TOP;
cellStyle1.Rotation = (short)-90;
//原材料画方框
cellStyle2 = workbook.CreateCellStyle();
cellStyle2.BorderBottom = NPOI.SS.UserModel.CellBorderType.NONE;
cellStyle2.BorderLeft = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle2.BorderRight = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle2.BorderTop = NPOI.SS.UserModel.CellBorderType.THIN;
//原材料画方框
cellStyle3 = workbook.CreateCellStyle();
cellStyle3.BorderLeft = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle3.BorderRight = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle3.BorderBottom = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle3.BorderTop = NPOI.SS.UserModel.CellBorderType.NONE;
foreach (Hu hu in huList)
{
//3行每行3个
if (hu.Item.Type.Equals("M") || hu.Item.Type.Equals("P"))
{
this.writeContent(companyCode, userName, pageIndex, num, hu);
if (num == count || num % PAGE_DETAIL_ROW_COUNT == 0)
{
pageIndex++;
}
num++;
}
/*
if ( num == huList.Count + 1)
{
for (int i = 1; i <= (PAGE_DETAIL_ROW_COUNT - (num % PAGE_DETAIL_ROW_COUNT)); i++)
{
//YFKSS
//this.SetRowCell(pageIndex - 1, 1, 4, string.Empty, i);
//PART NO.
this.CopyCell(pageIndex - 1, this.getRowIndex(2, num - 1 + i), 0, "");
this.SetRowCell(pageIndex - 1, 2, 0, string.Empty, num - 1 + i);
//LOT/SERIAL NO.
this.CopyCell(pageIndex - 1, this.getRowIndex(4, num - 1 + i), 0, "");
this.SetRowCell(pageIndex - 1, 4, 0, string.Empty, num - 1 + i);
//PRINTED DATE:
this.CopyCell(pageIndex - 1, this.getRowIndex(10, num - 1 + i), 0, "");
this.SetRowCell(pageIndex - 1, 10, 0, string.Empty, num - 1 + i);
//PRINTER USER:
this.CopyCell(pageIndex - 1, this.getRowIndex(10, num - 1 + i), 2, "");
this.SetRowCell(pageIndex - 1, 10, 2, string.Empty, num - 1 + i);
}
}
*/
}
foreach (Hu hu in huList)
{
if (hu.Item.Type.Equals("P") || hu.Item.Type.Equals("M")) //原材料
{
huMgr.UpdateHu(hu);
}
}
}
catch (Exception e)
{
return false;
}
return true;
}
private void Init()
{
#region Set Styles
//date style
this.dateStyle = this.Workbook.CreateCellStyle();
dateStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("d/m/yy h:mm");
//hyperlink style
this.hyperlinkStyle = this.Workbook.CreateCellStyle();
var font = this.Workbook.CreateFont();
font.Underline = (byte)FontUnderlineType.SINGLE;
font.Color = HSSFColor.BLUE.index;
hyperlinkStyle.SetFont(font);
#endregion
}
public void SetFont(JsValue value)
{
CellStyle.SetFont(value.Is <FontInstance>() ? value.As <FontInstance>().Font : null);
}
public override bool FillValues(String templateFileName, IList <object> list)
{
try
{
this.init(templateFileName, ROW_COUNT);
if (list == null || list.Count < 0)
{
return(false);
}
OrderHead orderHead = (OrderHead)(list[0]);
string userName = "";
if (list.Count == 2)
{
userName = (string)list[1];
}
/*
* this.sheet.DefaultRowHeightInPoints = 14.25F;
* //this.sheet.DefaultColumnWidth = 7;
* //this.sheet.SetColumnWidth(1, 100 * 256);
* sheet.DefaultColumnWidth = 7;
* //sheet.DefaultRowHeight = 30 * 20;
*
* //sheet.PrintSetup.PaperSize =
* sheet.SetColumnWidth(0, 60 * 32);
* sheet.SetColumnWidth(1, 60 * 32);
* sheet.SetColumnWidth(2, 60 * 32);
* sheet.SetColumnWidth(3, 60 * 32);
* sheet.SetColumnWidth(4, 58 * 32);
*/
this.sheet.DisplayGridlines = false;
this.sheet.IsPrintGridlines = false;
//this.sheet.DisplayGuts = false;
//加页删页
//this.CopyPage(pageCount, COLUMN_COUNT, 1);
this.barCodeFontName = this.GetBarcodeFontName(0, 0);
CellStyle cellStyleT = workbook.CreateCellStyle();
Font fontT = workbook.CreateFont();
fontT.FontHeightInPoints = (short)9;
fontT.FontName = "宋体";
fontT.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
cellStyleT.SetFont(fontT);
int pageIndex = 1;
string barCode = Utility.BarcodeHelper.GetBarcodeStr(orderHead.OrderNo, this.barCodeFontName);
this.SetRowCell(pageIndex, 0, 0, barCode);
this.SetRowCell(pageIndex, 1, 0, orderHead.OrderNo);
//生产线编号
Flow flow = this.flowMgr.LoadFlow(orderHead.Flow);
this.SetRowCell(pageIndex, 3, 0, flow.Code);
//班次编号
this.SetRowCell(pageIndex, 3, 2, orderHead.Shift == null ? string.Empty : orderHead.Shift.Code);
//生产线名称
this.SetRowCell(pageIndex, 4, 0, flow.Description);
//产品描述
if (orderHead.OrderDetails != null && orderHead.OrderDetails.Count > 0)
{
this.SetRowCell(pageIndex, 6, 0, orderHead.OrderDetails[0].Item.Description);
}
//开始时间
this.SetRowCell(pageIndex, 8, 0, orderHead.WindowTime.ToString("yyyy-MM-dd HH:mm"));
//Printed Date:
this.SetRowCell(pageIndex, 9, 3, DateTime.Now.ToString("MM/dd/yy"));
this.sheet.SetRowBreak(this.GetRowIndexAbsolute(pageIndex, ROW_COUNT - 1));
if (orderHead.IsPrinted == null || orderHead.IsPrinted == false)
{
orderHead.IsPrinted = true;
orderHeadMgr.UpdateOrderHead(orderHead);
}
}
catch (Exception e)
{
return(false);
}
return(true);
}
public static Dictionary <TextStyle, CellStyle> CreateDefaultStyels(HSSFWorkbook hssfworkbook)
{
var styles = new Dictionary <TextStyle, CellStyle>();
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 16;
font.Boldweight = 3000;
font.FontName = "Arial";
style.SetFont(font);
//style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
//style.FillPattern = FillPatternType.SOLID_FOREGROUND;
styles.Add(TextStyle.Header1, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 18;
font.Boldweight = 2500;
font.FontName = "Arial";
style.SetFont(font);
style.Alignment = HorizontalAlignment.CENTER;
styles.Add(TextStyle.BigHeader, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 14;
font.Boldweight = 2000;
font.FontName = "Arial";
style.SetFont(font);
//style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
//style.FillPattern = FillPatternType.SOLID_FOREGROUND;
styles.Add(TextStyle.Header2, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 14;
font.Boldweight = 2000;
font.FontName = "Arial";
style.SetFont(font);
style.Alignment = HorizontalAlignment.CENTER;
styles.Add(TextStyle.Header2Simple, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 12;
font.Boldweight = 1000;
font.FontName = "Arial";
style.SetFont(font);
//style.FillForegroundColor = HSSFColor.WHITE.index;
//style.FillPattern = FillPatternType.FINE_DOTS;
//style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
styles.Add(TextStyle.Header3, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 12;
font.Boldweight = 1000;
font.FontName = "Arial";
style.SetFont(font);
style.Alignment = HorizontalAlignment.CENTER;
styles.Add(TextStyle.Header3Simple, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 12;
font.Boldweight = 1000;
font.FontName = "Arial";
style.SetFont(font);
style.FillForegroundColor = HSSFColor.WHITE.index;
style.FillPattern = FillPatternType.FINE_DOTS;
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.Alignment = HorizontalAlignment.CENTER;
styles.Add(TextStyle.Header3Center, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 12;
font.Boldweight = 1000;
font.FontName = "Arial";
style.SetFont(font);
//style.FillForegroundColor = HSSFColor.WHITE.index;
//style.FillPattern = HSSFCellStyle.LESS_DOTS;
//style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.WrapText = true;
styles.Add(TextStyle.Header3Wrap, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.Alignment = HorizontalAlignment.LEFT;
styles.Add(TextStyle.NormalText, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
font.IsItalic = true;
style.SetFont(font);
style.Alignment = HorizontalAlignment.LEFT;
styles.Add(TextStyle.NormalTextItalic, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.FillForegroundColor = HSSFColor.WHITE.index;
style.FillPattern = FillPatternType.FINE_DOTS;
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.BorderTop = CellBorderType.THIN;
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.TopBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.LeftBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.RightBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.WrapText = true;
styles.Add(TextStyle.TableRowDark, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.FillForegroundColor = HSSFColor.WHITE.index;
style.FillPattern = FillPatternType.FINE_DOTS;
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.TOP;
styles.Add(TextStyle.TableRowDarkCenter, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.FillForegroundColor = HSSFColor.WHITE.index;
style.FillPattern = FillPatternType.FINE_DOTS;
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.Alignment = HorizontalAlignment.RIGHT;
style.VerticalAlignment = VerticalAlignment.TOP;
DataFormat format = hssfworkbook.CreateDataFormat();
style.DataFormat = format.GetFormat("# ### ### ##0.00");
styles.Add(TextStyle.TableRowDarkMoney, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.FillForegroundColor = HSSFColor.WHITE.index;
style.FillPattern = FillPatternType.FINE_DOTS;
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.Alignment = HorizontalAlignment.LEFT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.WrapText = true;
styles.Add(TextStyle.NormalDarkLeftTopWrap, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.FillForegroundColor = HSSFColor.WHITE.index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.BorderTop = CellBorderType.THIN;
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.TopBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.LeftBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.RightBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.WrapText = true;
styles.Add(TextStyle.TableRowNormal, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.WrapText = true;
styles.Add(TextStyle.WrapText, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.Boldweight = 3000;
font.FontName = "Arial";
style.SetFont(font);
styles.Add(TextStyle.BoldText, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.Boldweight = 3000;
font.FontName = "Arial";
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font);
styles.Add(TextStyle.BoldTextAlignCenter, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.TOP;
style.SetFont(font);
styles.Add(TextStyle.NormalAlignCenter, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.Alignment = HorizontalAlignment.LEFT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.SetFont(font);
styles.Add(TextStyle.NormalLeftTop, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.Alignment = HorizontalAlignment.LEFT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.SetFont(font);
style.WrapText = true;
styles.Add(TextStyle.NormalLeftTopWrap, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.Alignment = HorizontalAlignment.RIGHT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.SetFont(font);
styles.Add(TextStyle.NormalRightTop, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
font.Boldweight = 3000;
style.Alignment = HorizontalAlignment.RIGHT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.SetFont(font);
styles.Add(TextStyle.BoldRightTop, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.FontHeightInPoints = 10;
font.FontName = "Arial";
//font.Underline
font.Color = HSSFColor.BLUE.index;
style.SetFont(font);
styles.Add(TextStyle.Hyperlink, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.Boldweight = 3000;
font.FontName = "Arial";
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font);
styles.Add(TextStyle.TableHeader, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
styles.Add(TextStyle.SysDateTime, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
styles.Add(TextStyle.SysNumber, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
styles.Add(TextStyle.NormalPercent, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
DataFormat format = hssfworkbook.CreateDataFormat();
style.DataFormat = format.GetFormat("# ### ### ##0.00");
styles.Add(TextStyle.SysMoney, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
font.Boldweight = 3000;
style.Alignment = HorizontalAlignment.RIGHT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.SetFont(font);
style.BorderTop = CellBorderType.THIN;
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.TopBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.LeftBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.RightBorderColor = HSSFColor.GREY_50_PERCENT.index;
styles.Add(TextStyle.TableTotal, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
font.Boldweight = 3000;
style.Alignment = HorizontalAlignment.RIGHT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.SetFont(font);
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
style.BorderTop = CellBorderType.THIN;
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.TopBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.LeftBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.RightBorderColor = HSSFColor.GREY_50_PERCENT.index;
styles.Add(TextStyle.TableTotalPercent, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.Boldweight = 3000;
font.FontName = "Arial";
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.BorderTop = CellBorderType.THIN;
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.TopBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.LeftBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.RightBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.FillForegroundColor = HSSFColor.WHITE.index;
style.FillPattern = FillPatternType.FINE_DOTS;
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.WrapText = true;
style.SetFont(font);
styles.Add(TextStyle.TableHeaderGreyCenterdBorder, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 8;
font.FontName = "Arial";
style.SetFont(font);
styles.Add(TextStyle.SmallText, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 8;
font.FontName = "Arial";
style.SetFont(font);
style.Alignment = HorizontalAlignment.LEFT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.WrapText = true;
styles.Add(TextStyle.SmallTextWrap, style);
}
return(styles);
}
private void SetBold(Cell cell, CellStyle style)
{
if (cell.IsBold)
{
Font font = _sheet.Workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
style.SetFont(font);
}
}
/**
* 填充报表
*
* Param list [0]huDetailList
*/
public override bool FillValues(String templateFileName, IList <object> list)
{
try
{
this.init(templateFileName, ROW_COUNT);
if (list == null || list.Count == 0)
{
return(false);
}
IList <Hu> huList = (IList <Hu>)list[0];
string userName = "";
if (list.Count == 2)
{
userName = (string)list[1];
}
int count = 0;
foreach (Hu hu in huList)
{
if (hu.Item.Type.Equals("M") || hu.Item.Type.Equals("P"))
{
count++;
}
}
if (count == 0)
{
return(false);
}
this.barCodeFontName = this.GetBarcodeFontName(0, 0);
int pageCount = (int)Math.Ceiling(count / (PAGE_DETAIL_ROW_COUNT * 1.0));
/*
* for (int rowNum = 0; rowNum < 25; rowNum += 12)
* {
* this.SetRowCellBarCode(1, rowNum, 0);
* this.SetRowCellBarCode(1, rowNum, 7);
* this.SetRowCellBarCode(1, rowNum, 14);
* }
*/
this.CopyPage(pageCount, COLUMN_COUNT, 1);
this.sheet.DisplayGridlines = false;
this.sheet.IsPrintGridlines = false;
/*
* CellStyle cellStyleT = workbook.CreateCellStyle();
* Font fontT = workbook.CreateFont();
* fontT.FontHeightInPoints = (short)9;
* fontT.FontName = "宋体";
* fontT.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
* cellStyleT.SetFont(fontT);
*/
int pageIndex = 1;
int num = 1;
string companyCode = entityPreferenceMgr.LoadEntityPreference(BusinessConstants.ENTITY_PREFERENCE_CODE_COMPANY_CODE).Value;
if (companyCode == null)
{
companyCode = string.Empty;
}
//成品
cellStyle1 = workbook.CreateCellStyle();
Font font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 24;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
cellStyle1.SetFont(font);
cellStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cellStyle1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.TOP;
cellStyle1.Rotation = (short)-90;
//原材料画方框
cellStyle2 = workbook.CreateCellStyle();
cellStyle2.BorderBottom = NPOI.SS.UserModel.CellBorderType.NONE;
cellStyle2.BorderLeft = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle2.BorderRight = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle2.BorderTop = NPOI.SS.UserModel.CellBorderType.THIN;
//原材料画方框
cellStyle3 = workbook.CreateCellStyle();
cellStyle3.BorderLeft = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle3.BorderRight = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle3.BorderBottom = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle3.BorderTop = NPOI.SS.UserModel.CellBorderType.NONE;
foreach (Hu hu in huList)
{
//3行每行3个
if (hu.Item.Type.Equals("M") || hu.Item.Type.Equals("P"))
{
this.writeContent(companyCode, userName, pageIndex, num, hu);
if (num == count || num % PAGE_DETAIL_ROW_COUNT == 0)
{
pageIndex++;
}
num++;
}
/*
* if ( num == huList.Count + 1)
* {
* for (int i = 1; i <= (PAGE_DETAIL_ROW_COUNT - (num % PAGE_DETAIL_ROW_COUNT)); i++)
* {
* //YFKSS
* //this.SetRowCell(pageIndex - 1, 1, 4, string.Empty, i);
* //PART NO.
* this.CopyCell(pageIndex - 1, this.getRowIndex(2, num - 1 + i), 0, "");
* this.SetRowCell(pageIndex - 1, 2, 0, string.Empty, num - 1 + i);
* //LOT/SERIAL NO.
* this.CopyCell(pageIndex - 1, this.getRowIndex(4, num - 1 + i), 0, "");
* this.SetRowCell(pageIndex - 1, 4, 0, string.Empty, num - 1 + i);
* //PRINTED DATE:
* this.CopyCell(pageIndex - 1, this.getRowIndex(10, num - 1 + i), 0, "");
* this.SetRowCell(pageIndex - 1, 10, 0, string.Empty, num - 1 + i);
* //PRINTER USER:
* this.CopyCell(pageIndex - 1, this.getRowIndex(10, num - 1 + i), 2, "");
* this.SetRowCell(pageIndex - 1, 10, 2, string.Empty, num - 1 + i);
* }
*
* }
*/
}
foreach (Hu hu in huList)
{
if (hu.Item.Type.Equals("P") || hu.Item.Type.Equals("M")) //原材料
{
huMgr.UpdateHu(hu);
}
}
}
catch (Exception e)
{
return(false);
}
return(true);
}
public override bool FillValues(String templateFileName, IList <object> list)
{
try
{
this.init(templateFileName, ROW_COUNT);
if (list == null || list.Count == 0)
{
return(false);
}
IList <Hu> huList = (IList <Hu>)list[0];
string userName = "";
if (list.Count == 2)
{
userName = (string)list[1];
}
this.sheet.DisplayGridlines = false;
this.sheet.IsPrintGridlines = false;
//this.sheet.DisplayGuts = false;
int count = 0;
foreach (Hu hu in huList)
{
if (hu.Item.Type.Equals("M") || //成品
hu.Item.Type.Equals("P")) //原材料
{
count++;
}
}
if (count == 0)
{
return(false);
}
this.barCodeFontName = this.GetBarcodeFontName(0, 0);
//加页删页
this.CopyPage(count, COLUMN_COUNT, 1);
int pageIndex = 1;
string companyCode = entityPreferenceMgr.LoadEntityPreference(BusinessConstants.ENTITY_PREFERENCE_CODE_COMPANY_CODE).Value;
if (companyCode == null)
{
companyCode = string.Empty;
}
foreach (Hu hu in huList)
{
if (hu.Item.Type.Equals("M")) //成品
{
if (hu.PrintCount > 1)
{
this.SetRowCell(pageIndex, 1, 3, "(R)");
}
hu.PrintCount += 1;
//YFKSS
int rowIndexAbsolute = this.GetRowIndexAbsolute(pageIndex, 1);
this.SetMergedRegion(pageIndex, 1, 4, 9, 4);
this.SetRowCell(pageIndex, 1, 4, companyCode);
Cell cell = this.GetCell(rowIndexAbsolute, 4);
CellStyle cellStyle = workbook.CreateCellStyle();
Font font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 24;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
cellStyle.SetFont(font);
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.TOP;
cellStyle.Rotation = (short)-90;
cell.CellStyle = workbook.CreateCellStyle();
cell.CellStyle.CloneStyleFrom(cellStyle);
//hu id内容
string barCode = Utility.BarcodeHelper.GetBarcodeStr(hu.HuId, this.barCodeFontName);
this.SetRowCell(pageIndex, 0, 0, barCode);
//hu id内容
this.SetRowCell(pageIndex, 1, 0, hu.HuId);
//PART NO.内容
this.SetRowCell(pageIndex, 3, 0, hu.Item.Code);
//SHIFT
this.SetRowCell(pageIndex, 4, 2, "SHIFT");
//QUANTITY.
this.SetRowCell(pageIndex, 4, 3, "QUANTITY.");
//批号LotNo
this.SetRowCell(pageIndex, 5, 0, hu.LotNo);
//SHIFT内容
this.SetRowCell(pageIndex, 5, 2, Utility.BarcodeHelper.GetShiftCode(hu.HuId));
//QUANTITY内容
this.SetRowCell(pageIndex, 5, 3, hu.Qty.ToString("0.########"));
//CUSTPART
this.SetRowCell(pageIndex, 6, 0, "CUSTPART");
//WO DATE
this.SetRowCell(pageIndex, 6, 3, "WO DATE");
//CUSTPART内容
IList <ItemReference> itemReferenceList = itemReferenceMgr.GetItemReference(hu.Item.Code);
if (itemReferenceList != null && itemReferenceList.Count > 0)
{
this.SetRowCell(pageIndex, 7, 0, itemReferenceList[0].ReferenceCode);
}
//WO DATE内容
this.SetRowCell(pageIndex, 7, 3, hu.ManufactureDate.ToString("MM/dd/yy"));
//DESCRIPTION.
this.SetRowCell(pageIndex, 8, 0, "DESCRIPTION.");
//DESCRIPTION内容
this.SetRowCell(pageIndex, 9, 0, hu.Item.Description);
//PRINTED DATE:内容
this.SetRowCell(pageIndex, 10, 1, DateTime.Now.ToString("MM/dd/yy"));
//print name 内容
this.SetRowCell(pageIndex, 10, 3, userName);
this.sheet.SetRowBreak(this.GetRowIndexAbsolute(pageIndex, ROW_COUNT - 1));
pageIndex++;
}
else if (hu.Item.Type.Equals("P")) //原材料
{
if (hu.PrintCount > 1)
{
this.SetRowCell(pageIndex, 1, 3, "(R)");
}
hu.PrintCount += 1;
//hu id内容
string barCode = Utility.BarcodeHelper.GetBarcodeStr(hu.HuId, this.barCodeFontName);
this.SetRowCell(pageIndex, 0, 0, barCode);
//hu id内容
this.SetRowCell(pageIndex, 1, 0, hu.HuId);
//画方框
int rowIndexAbsolute = this.GetRowIndexAbsolute(pageIndex, 2);
Cell cell1 = this.GetCell(rowIndexAbsolute, 4);
CellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.BorderBottom = NPOI.SS.UserModel.CellBorderType.NONE;
cellStyle1.BorderLeft = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle1.BorderRight = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle1.BorderTop = NPOI.SS.UserModel.CellBorderType.THIN;
cell1.CellStyle = workbook.CreateCellStyle();
cell1.CellStyle.CloneStyleFrom(cellStyle1);
//PART NO.内容
this.SetRowCell(pageIndex, 3, 0, hu.Item.Code);
//画方框
rowIndexAbsolute = this.GetRowIndexAbsolute(pageIndex, 3);
CellStyle cellStyle2 = workbook.CreateCellStyle();
Cell cell2 = this.GetCell(rowIndexAbsolute, 4);
cellStyle2.BorderLeft = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle2.BorderRight = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle2.BorderBottom = NPOI.SS.UserModel.CellBorderType.THIN;
cellStyle2.BorderTop = NPOI.SS.UserModel.CellBorderType.NONE;
cell2.CellStyle = workbook.CreateCellStyle();
cell2.CellStyle.CloneStyleFrom(cellStyle2);
//QUANTITY.
this.SetRowCell(pageIndex, 4, 2, "QUANTITY.");
//批号LotNo
this.SetRowCell(pageIndex, 5, 0, hu.LotNo);
//QUANTITY.
this.SetRowCell(pageIndex, 5, 2, hu.Qty.ToString("0.########"));
//DESCRIPTION.
this.SetRowCell(pageIndex, 6, 0, "DESCRIPTION.");
//DESCRIPTION内容
this.SetRowCell(pageIndex, 7, 0, hu.Item.Description);
//SUPPLIER.
this.SetRowCell(pageIndex, 8, 0, "SUPPLIER.");
//SUPPLIER内容
this.SetRowCell(pageIndex, 9, 0, hu.ManufactureParty == null ? string.Empty : hu.ManufactureParty.Name);
//PRINTED DATE:内容
this.SetRowCell(pageIndex, 10, 1, DateTime.Now.ToString("MM/dd/yy"));
//print name 内容
this.SetRowCell(pageIndex, 10, 3, userName);
this.sheet.SetRowBreak(this.GetRowIndexAbsolute(pageIndex, ROW_COUNT - 1));
pageIndex++;
}
}
foreach (Hu hu in huList)
{
if (hu.Item.Type.Equals("M") || hu.Item.Type.Equals("P")) //成品 //原材料
{
huMgr.UpdateHu(hu);
}
}
}
catch (Exception e)
{
return(false);
}
return(true);
}
