static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
//increase the width of Column A
sheet.SetColumnWidth(0, 5000);
//create the format instance
IDataFormat format = workbook.CreateDataFormat();
// Create a row and put some cells in it. Rows are 0 based.
ICell cell = sheet.CreateRow(0).CreateCell(0);
//number format with 2 digits after the decimal point - "1.20"
SetValueAndFormat(workbook, cell, 1.2, HSSFDataFormat.GetBuiltinFormat("0.00"));
//RMB currency format with comma - "¥20,000"
ICell cell2 = sheet.CreateRow(1).CreateCell(0);
SetValueAndFormat(workbook, cell2, 20000, format.GetFormat("¥#,##0"));
//scentific number format - "3.15E+00"
ICell cell3 = sheet.CreateRow(2).CreateCell(0);
SetValueAndFormat(workbook, cell3, 3.151234, format.GetFormat("0.00E+00"));
//percent format, 2 digits after the decimal point - "99.33%"
ICell cell4 = sheet.CreateRow(3).CreateCell(0);
SetValueAndFormat(workbook, cell4, 0.99333, format.GetFormat("0.00%"));
//phone number format - "021-65881234"
ICell cell5 = sheet.CreateRow(4).CreateCell(0);
SetValueAndFormat(workbook, cell5, 02165881234, format.GetFormat("000-00000000"));
//Chinese capitalized character number - 壹贰叁 元
ICell cell6 = sheet.CreateRow(5).CreateCell(0);
SetValueAndFormat(workbook, cell6, 123, format.GetFormat("[DbNum2][$-804]0 元"));
//Chinese date string
ICell cell7 = sheet.CreateRow(6).CreateCell(0);
SetValueAndFormat(workbook, cell7, new DateTime(2004, 5, 6), format.GetFormat("yyyy年m月d日"));
cell7.SetCellValue(new DateTime(2004, 5, 6));
//Chinese date string
ICell cell8 = sheet.CreateRow(7).CreateCell(0);
SetValueAndFormat(workbook, cell8, new DateTime(2005, 11, 6), format.GetFormat("yyyy年m月d日"));
//formula value with datetime style
ICell cell9 = sheet.CreateRow(8).CreateCell(0);
cell9.CellFormula = "DateValue(\"2005-11-11\")+TIMEVALUE(\"11:11:11\")";
ICellStyle cellStyle9 = workbook.CreateCellStyle();
cellStyle9.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
cell9.CellStyle = cellStyle9;
//display current time
ICell cell10 = sheet.CreateRow(9).CreateCell(0);
SetValueAndFormat(workbook, cell10, DateTime.Now, format.GetFormat("[$-409]h:mm:ss AM/PM;@"));
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
public void TestIdentifyDateFormats()
{
// First up, try with a few built in date formats
short[] builtins = new short[] { 0x0e, 0x0f, 0x10, 0x16, 0x2d, 0x2e };
for (int i = 0; i < builtins.Length; i++)
{
String formatStr = HSSFDataFormat.GetBuiltinFormat(builtins[i]);
Assert.IsTrue(DateUtil.IsInternalDateFormat(builtins[i]));
Assert.IsTrue(DateUtil.IsADateFormat(builtins[i], formatStr));
}
// Now try a few built-in non date formats
builtins = new short[] { 0x01, 0x02, 0x17, 0x1f, 0x30 };
for (int i = 0; i < builtins.Length; i++)
{
String formatStr = HSSFDataFormat.GetBuiltinFormat(builtins[i]);
Assert.IsFalse(DateUtil.IsInternalDateFormat(builtins[i]));
Assert.IsFalse(DateUtil.IsADateFormat(builtins[i], formatStr));
}
// Now for some non-internal ones
// These come after the real ones
int numBuiltins = HSSFDataFormat.NumberOfBuiltinBuiltinFormats;
Assert.IsTrue(numBuiltins < 60);
short formatId = 60;
Assert.IsFalse(DateUtil.IsInternalDateFormat(formatId));
// Valid ones first
String[] formats = new String[] {
"yyyy-mm-dd", "yyyy/mm/dd", "yy/mm/dd", "yy/mmm/dd",
"dd/mm/yy", "dd/mm/yyyy", "dd/mmm/yy",
"dd-mm-yy", "dd-mm-yyyy",
"DD-MM-YY", "DD-mm-YYYY",
"dd\\-mm\\-yy", // Sometimes escaped
"dd.mm.yyyy", "dd\\.mm\\.yyyy",
"dd\\ mm\\.yyyy AM", "dd\\ mm\\.yyyy pm",
"dd\\ mm\\.yyyy\\-dd", "[h]:mm:ss",
//"mm/dd/yy", "\"mm\"/\"dd\"/\"yy\"",
"mm/dd/yy",
"\\\"mm\\\"/\\\"dd\\\"/\\\"yy\\\"",
"mm/dd/yy \"\\\"some\\\" string\"",
"m\\/d\\/yyyy",
// These crazy ones are valid
"yyyy-mm-dd;@", "yyyy/mm/dd;@",
"dd-mm-yy;@", "dd-mm-yyyy;@",
// These even crazier ones are also valid
// (who knows what they mean though...)
"[$-F800]dddd\\,\\ mmm\\ dd\\,\\ yyyy",
"[$-F900]ddd/mm/yyy",
// These ones specify colours, who knew that was allowed?
"[BLACK]dddd/mm/yy",
"[yeLLow]yyyy-mm-dd"
};
for (int i = 0; i < formats.Length; i++)
{
Assert.IsTrue(
DateUtil.IsADateFormat(formatId, formats[i])
, formats[i] + " is a date format"
);
}
// Then time based ones too
formats = new String[] {
"yyyy-mm-dd hh:mm:ss", "yyyy/mm/dd HH:MM:SS",
"mm/dd HH:MM", "yy/mmm/dd SS",
"mm/dd HH:MM AM", "mm/dd HH:MM am",
"mm/dd HH:MM PM", "mm/dd HH:MM pm"
//"m/d/yy h:mm AM/PM"
};
for (int i = 0; i < formats.Length; i++)
{
Assert.IsTrue(
DateUtil.IsADateFormat(formatId, formats[i]),
formats[i] + " is a datetime format"
);
}
// Then invalid ones
formats = new String[] {
"yyyy*mm*dd",
"0.0", "0.000",
"0%", "0.0%",
"[]Foo", "[BLACK]0.00%",
"", null
};
for (int i = 0; i < formats.Length; i++)
{
Assert.IsFalse(
DateUtil.IsADateFormat(formatId, formats[i]),
formats[i] + " is not a date or datetime format"
);
}
// And these are ones we probably shouldn't allow,
// but would need a better regexp
formats = new String[] {
//"yyyy:mm:dd",
"yyyy:mm:dd", "\"mm\"/\"dd\"/\"yy\"",
};
for (int i = 0; i < formats.Length; i++)
{
// Assert.IsFalse( DateUtil.IsADateFormat(formatId, formats[i]) );
}
}
public static bool ToExcel(string savePath, string SheetName, string HeadName, IList listJob, string className, string note = "")
{
try
{
DataTable table = ToDataTable(listJob, className, note);
List <int> dateCellIndex = new List <int>();//日期格式列
bool header = true;
//创建工作薄
HSSFWorkbook wk = new HSSFWorkbook();
//创建一个名称为mySheet的表
ISheet tb = wk.CreateSheet(SheetName);
#region 表头样式
ICellStyle headStyle = wk.CreateCellStyle();
headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = wk.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 行样式
ICellStyle stylerow = wk.CreateCellStyle();
stylerow.Alignment = HorizontalAlignment.Center;
IFont fontrow = wk.CreateFont();
fontrow.FontName = "宋体";
fontrow.FontHeightInPoints = 12;
stylerow.SetFont(fontrow);
stylerow.WrapText = true;
#endregion
#region 日期行样式
ICellStyle dateStyle = wk.CreateCellStyle();
IDataFormat format = wk.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
stylerow.WrapText = true;
#endregion
#region 数字样式
ICellStyle numStyle = wk.CreateCellStyle();
numStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
//IDataFormat numformat = wk.CreateDataFormat();
//numStyle.DataFormat = numformat.GetFormat("0.00");
#endregion
Dictionary <string, string> excelDataMap = ReadXml(className, false, note);
//合并标题行
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelDataMap.Keys.Count));
IRow rowhead = tb.CreateRow(0); //创建一行
rowhead.HeightInPoints = 25; //行高
ICell cellhead = rowhead.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cellhead.CellStyle = headStyle;
cellhead.SetCellValue(HeadName); //写入表头
//SetCellRangeAddress(tb, 0, 0, 1, 20);
int rowIndex = 1;
if (header)
{
IRow row = tb.CreateRow(rowIndex); //创建一行
rowIndex++;
row.HeightInPoints = 25; //行高
int cellIndex = 0; //开始列索引
#region 弃用
//for (int i = 0; i < table.Columns.Count; i++)
//{
// if (!table.Columns[i].ToString().EndsWith("_wennull"))
// {
// if (table.Columns[i].DataType.ToString() == "System.DateTime")//日期型
// {
// tb.SetColumnWidth(cellIndex, 20 * 256);//行宽10个汉字
// dateCellIndex.Add(cellIndex);
// }
// else
// {
// tb.SetColumnWidth(cellIndex, 16 * 256);//行宽8个汉字
// }
// ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
// cell.CellStyle = headStyle;
// cell.SetCellValue(table.Columns[i].ToString());//循环往第二行的单元格中添加数据
// cellIndex++;
// }
//}
#endregion
foreach (var item in excelDataMap)
{
string columsName = item.Value;
if (table.Columns[columsName].DataType.ToString() == "System.DateTime") //日期型
{
tb.SetColumnWidth(cellIndex, 20 * 256); //行宽10个汉字
dateCellIndex.Add(cellIndex);
}
else
{
tb.SetColumnWidth(cellIndex, 16 * 256); //行宽8个汉字
}
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.CellStyle = headStyle;
cell.SetCellValue(table.Columns[columsName].ToString()); //循环往第二行的单元格中添加数据
cellIndex++;
}
}
for (int i = 0; i < table.Rows.Count; i++)
{
IRow row = tb.CreateRow(rowIndex + i); //创建一行
row.HeightInPoints = 20; //行高
int cellIndex = 0; //开始列索引
#region 弃用
//for (int c = 0; c < table.Columns.Count; c++)
//{
// if (!table.Columns[c].ToString().EndsWith("_wennull"))
// {
// ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
// if (table.Columns[c].DataType.ToString() == "System.DateTime")//日期型
// {
// string value = table.Rows[i][c].ToString();
// if (value != "")
// {
// cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));//循环往第二行的单元格中添加数据
// }
// cell.CellStyle = dateStyle;
// }
// else
// {
// cell.SetCellValue(table.Rows[i][c].ToString());//循环往第二行的单元格中添加数据
// cell.CellStyle = stylerow;
// }
// cellIndex++;
// }
//}
#endregion
foreach (var item in excelDataMap)
{
string columsName = item.Value;
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
if (table.Columns[columsName].DataType.ToString() == "System.DateTime") //日期型
{
string value = table.Rows[i][columsName].ToString();
if (value != "")
{
cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));//循环往第二行的单元格中添加数据
}
cell.CellStyle = dateStyle;
}
else if (table.Columns[columsName].DataType.ToString() == "System.Decimal")
{
string value = table.Rows[i][columsName].ToString();
if (value != "")
{
cell.SetCellValue(Convert.ToDouble(value));//循环往第二行的单元格中添加数据
}
//cell.CellStyle = numStyle;//数字格式化，不启用
}
else
{
cell.SetCellValue(table.Rows[i][columsName].ToString());//循环往第二行的单元格中添加数据
cell.CellStyle = stylerow;
}
cellIndex++;
}
}
//foreach (int cellIndex in dateCellIndex)
//{
// for (int y = table.Rows.Count; y < 500; y++)//至少设置500行格式
// {
// IRow row = tb.CreateRow(rowIndex + y);//创建一行
// row.HeightInPoints = 20; //行高
// ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
// //cell.SetCellValue("");//循环往第二行的单元格中添加数据
// cell.CellStyle = dateStyle;
// }
//}
using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
{
wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
return(true);
}
catch (Exception)
{
return(false);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// xls office 2003格式
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText, string sheetName)
{
IWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet;
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
else
{
sheet = workbook.CreateSheet(sheetName) as HSSFSheet;
}
#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
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.UTF8.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.UTF8.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 == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
#region 表头及样式
if (!string.IsNullOrEmpty(strHeaderText))
{
HSSFRow headerRow = sheet.CreateRow(rowIndex++) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = Npoi.Core.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
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));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(rowIndex++) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = Npoi.Core.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
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);
}
//headerRow.Dispose();
}
#endregion
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
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;
//sheet.Dispose();
//workbook.Dispose();
return(ms);
}
}
protected override byte[] Export <T>(IEnumerable <T> table, IEnumerable <KeyValuePair <PropertyInfo, TableOptionsAttribute> > tableColumns)
{
using (var ms = new MemoryStream())
{
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet();
var rowIndex = 0;
var colIndex = 0;
var colCount = tableColumns.Count();
var headerRow = sheet.CreateRow(0);
var headerRowCell = headerRow.CreateCell(colIndex);
headerRowCell.SetCellValue($"{resource?.Get("CreateDate")} {DateTime.Now.ToString(CultureInfo.CurrentUICulture)}".Trim());
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, colCount - 1));
var style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Right;
headerRowCell.CellStyle = style;
var tableHeaderRow = sheet.CreateRow(1);
// Creating styles
var headerCellStyle = workbook.CreateCellStyle();
var palette = workbook.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Lavender.Index, ExportConfiguration.HeaderBackgroundColor.R, ExportConfiguration.HeaderBackgroundColor.G, ExportConfiguration.HeaderBackgroundColor.B);
headerCellStyle.FillBackgroundColor = HSSFColor.Lavender.Index;
headerCellStyle.FillPattern = FillPattern.SolidForeground;
var doubleCellStyle = workbook.CreateCellStyle();
doubleCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00000");
var intCellStyle = workbook.CreateCellStyle();
intCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
// Creates the header cells
foreach (var tableColumn in tableColumns.Select(item => item.Value))
{
var headerCell = tableHeaderRow.CreateCell(colIndex);
headerCell.SetCellValue(tableColumn.Title);
// Add Style to Cell
headerCell.CellStyle = headerCellStyle;
colIndex++;
}
if (table != null)
{
// Writes the cells
foreach (var obj in table)
{
colIndex = 0;
rowIndex++;
var dataRow = sheet.CreateRow(rowIndex);
foreach (var tableColumn in tableColumns)
{
var val = tableColumn.Key.GetPropertyValue(obj, tableColumn.Value);
if (val != null)
{
var cell = dataRow.CreateCell(colIndex);
if (val is double)
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue((double)val);
cell.CellStyle = doubleCellStyle;
}
else if (val is short || val is int || val is long)
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(Convert.ToInt64(val));
cell.CellStyle = intCellStyle;
}
else
{
cell.SetCellValue(string.Format(tableColumn.Value.Format.IsNotNullOrEmpty() ? "{0:" + tableColumn.Value.Format + "}" : "{0}", val));
}
}
colIndex++;
}
}
}
// Auto size the columns
for (var i = 0; i < colCount; i++)
{
sheet.AutoSizeColumn(i);
}
// Create the stream
workbook.Write(ms);
return(ms.ToArray());
}
}
public static void GridToExcelByNPOI(string sheetname, string lotid, string date,
string[] summ1, string[] summ2, string[] summ3, DataGridView gv, string strExcelFileName, bool summflag, int mergeColIndex = -1)
{
HSSFWorkbook workbook = new HSSFWorkbook();
try
{
ISheet sheet = workbook.CreateSheet(sheetname);
//汇总栏
ICellStyle SummStyle = workbook.CreateCellStyle();
SummStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
SummStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont summfont = workbook.CreateFont();
summfont.Boldweight = (short)FontBoldWeight.Bold;
summfont.Color = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;
SummStyle.SetFont(summfont);
SummStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;
SummStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Lavender.Index;
//Lot ID
IRow summRow_1 = sheet.CreateRow(0);
summRow_1.Height = 20 * 20;
ICell cell_1 = summRow_1.CreateCell(0);
cell_1.SetCellValue("Lot ID:");
cell_1.CellStyle = SummStyle;
ICell cell_2 = summRow_1.CreateCell(1);
cell_2.SetCellValue(lotid);
cell_2.CellStyle = SummStyle;
ICell cell_3 = summRow_1.CreateCell(2);
cell_3.SetCellValue("Date:");
cell_3.CellStyle = SummStyle;
ICell cell_4 = summRow_1.CreateCell(3);
cell_4.SetCellValue(date);
cell_4.CellStyle = SummStyle;
ICell cell_5 = summRow_1.CreateCell(4);
cell_5.SetCellValue("Create Date:");
cell_5.CellStyle = SummStyle;
ICell cell_6 = summRow_1.CreateCell(5);
cell_6.SetCellValue(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
cell_6.CellStyle = SummStyle;
if (summflag)
{
//汇总
IRow summRow_2 = sheet.CreateRow(1);
summRow_2.Height = 20 * 20;
ICell cell_21 = summRow_2.CreateCell(0);
cell_21.SetCellValue(summ1[0]);
cell_21.CellStyle = SummStyle;
ICell cell_22 = summRow_2.CreateCell(1);
cell_22.SetCellValue(summ1[1]);
cell_22.CellStyle = SummStyle;
ICell cell_23 = summRow_2.CreateCell(2);
cell_23.SetCellValue(summ1[2]);
cell_23.CellStyle = SummStyle;
ICell cell_24 = summRow_2.CreateCell(3);
cell_24.SetCellValue(summ1[3]);
cell_24.CellStyle = SummStyle;
IRow summRow_3 = sheet.CreateRow(2);
summRow_3.Height = 20 * 20;
ICell cell_31 = summRow_3.CreateCell(0);
cell_31.SetCellValue(summ2[0]);
cell_31.CellStyle = SummStyle;
ICell cell_32 = summRow_3.CreateCell(1);
cell_32.SetCellValue(summ2[1]);
cell_32.CellStyle = SummStyle;
ICell cell_33 = summRow_3.CreateCell(2);
cell_33.SetCellValue(summ2[2]);
cell_33.CellStyle = SummStyle;
ICell cell_34 = summRow_3.CreateCell(3);
cell_34.SetCellValue(summ2[3]);
cell_34.CellStyle = SummStyle;
IRow summRow_4 = sheet.CreateRow(3);
summRow_4.Height = 20 * 20;
ICell cell_41 = summRow_4.CreateCell(0);
cell_41.SetCellValue(summ3[0]);
cell_41.CellStyle = SummStyle;
ICell cell_42 = summRow_4.CreateCell(1);
cell_42.SetCellValue(summ3[1]);
cell_42.CellStyle = SummStyle;
if (summ3.Length > 2)
{
ICell cell_43 = summRow_4.CreateCell(2);
cell_43.SetCellValue(summ3[2]);
cell_43.CellStyle = SummStyle;
ICell cell_44 = summRow_4.CreateCell(3);
cell_44.SetCellValue(summ3[3]);
cell_44.CellStyle = SummStyle;
}
}
//表格头
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HeadercellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//HeadercellStyle.FillBackgroundColorColor
HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Lavender.Index;
//用column name 作为列名
int icolIndex = 0;
int ridx = (summflag ? 4 : 1);
IRow headerRow = sheet.CreateRow(ridx);
headerRow.Height = 20 * 20;
foreach (DataGridViewColumn item in gv.Columns)
{
if (!item.Visible)
{
continue;
}
ICell cell = headerRow.CreateCell(icolIndex);
sheet.SetColumnWidth(icolIndex, item.Width * 50);
cell.SetCellValue(item.HeaderText);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = (summflag ? 5 : 2);
int iCellIndex = 0;
//merge params
string mergeColValue = string.Empty;
int mergeFirstRow = iRowIndex;
int mergeLastRow = iRowIndex;
if (gv.Rows.Count > 0)
{
foreach (DataGridViewRow Rowitem in gv.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataGridViewColumn Colitem in gv.Columns)
{
if (Colitem.Visible)
{
var colValue = Rowitem.Cells[Colitem.Name].FormattedValue.ToString();
if (iCellIndex == mergeColIndex)
{
if (mergeColValue != colValue && iRowIndex != mergeFirstRow)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(mergeFirstRow, iRowIndex - 1, mergeColIndex, mergeColIndex));
mergeFirstRow = iRowIndex;
}
mergeColValue = colValue;
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
}
else
{
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.General;
cellStyle.VerticalAlignment = VerticalAlignment.None;
}
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(colValue);
cell.CellStyle = cellStyle;
iCellIndex++;
}
}
iCellIndex = 0;
iRowIndex++;
}
if (mergeColIndex != -1)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(mergeFirstRow, iRowIndex - 1, mergeColIndex, mergeColIndex));
}
////自适应列宽度
//for (int i = 0; i < icolIndex; i++)
//{
// sheet.AutoSizeColumn(i);
//}
}
//写Excel
FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
}
catch (Exception ex)
{
throw ex;
}
finally { workbook = null; }
}
public Excel ExportReportToExcel(string basePath, string buildId, string energyCode, string[] regionIds, string date, string type)
{
type = type.ToUpper();
string templatePath = basePath + "/DayReportTemplate.xls";
string reportType = " 日报(" + date + ")";
if (type == "MM")
{
reportType = " 月报(" + date + ")";
templatePath = basePath + "/MonthReportTemplate.xls";
}
else if (type == "YY")
{
reportType = " 年报(" + date + ")";
templatePath = basePath + "/YearReportTemplate.xls";
}
else if (type == "DD")
{
reportType = " 日报(" + date + ")";
templatePath = basePath + "/DayReportTemplate.xls";
}
//根据模板生成Excel
FileStream file = new FileStream(templatePath, FileMode.Open, FileAccess.Read);
HSSFWorkbook book = new HSSFWorkbook(file);
HSSFSheet sheet = (HSSFSheet)book.GetSheet("Sheet1");
//创建单元格格式：数字型单元格默认取小数点后两位
ICellStyle style = book.CreateCellStyle();
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
//获取当前建筑名
IHomeDbContext homeContext = new HomeDbContext();
BuildInfo build = homeContext.GetBuildById(buildId);
//获取能源类型
EnergyItemDict energyItem = reportContext.GetUnitByEnergyCode(energyCode);
//string[] circuitIds = circuits.Split(',');
List <ReportValue> data = context.GetReportValueList(energyCode, regionIds, date, type);
var totalList = data.GroupBy(d => new { d.Time })
.Select(group => new ReportValue
{
Id = "Total",
Name = "总计",
Time = group.Key.Time,
Value = group.Sum(p => p.Value)
}).ToList();
data.AddRange(totalList);
//设置Excel标题
sheet.GetRow(0).GetCell(0).SetCellValue(build.BuildName + " 区域用能 " + reportType);
//设置Excel中报表的能源类别
sheet.GetRow(1).GetCell(1).SetCellValue(energyItem.EnergyItemName);
//设置Excel中报表的能源单位
sheet.GetRow(1).GetCell(5).SetCellValue(energyItem.EnergyItemUnit);
//根据传入circuitIds填充excel
int rowId = 0;
for (int i = 0; i < regionIds.Length + 1; i++)
{
//使用lamda表达式筛选List中Id与传入的Id对应的仪表：一次填充一行Excel
List <ReportValue> current;
if (i == regionIds.Length)
{
current = data.FindAll(p => p.Id == "Total");
}
else
{
current = data.FindAll(p => p.Id == regionIds[i]);
}
if (current.Count > 0)
{
IRow row = sheet.CreateRow(rowId + 3);
row.CreateCell(0).SetCellValue(current[0].Name);
decimal total = 0;
switch (type)
{
case "DD":
foreach (var item in current)
{ //遍历筛选出列表中当前回路中每个小时的数据，填充到Excel中
//当前行的第一列已经填充回路名称，根据时间设置向右偏移一位，将对应数据写入到当前单元格，并设置格式
if (item.Time != null)
{
DateTime time = Convert.ToDateTime(item.Time);
row.CreateCell(time.Hour + 1).SetCellValue((double)item.Value);
row.GetCell(time.Hour + 1).CellStyle = style;
total += Convert.ToDecimal(item.Value);
}
}
row.CreateCell(25).SetCellValue((double)total);
row.GetCell(25).CellStyle = style;
break;
case "MM":
foreach (var item in current)
{
if (item.Time != null)
{
DateTime time = Convert.ToDateTime(item.Time);
row.CreateCell(time.Day).SetCellValue((double)item.Value);
row.GetCell(time.Day).CellStyle = style;
total += Convert.ToDecimal(item.Value);
}
}
row.CreateCell(32).SetCellValue((double)total);
row.GetCell(32).CellStyle = style;
break;
case "YY":
foreach (var item in current)
{
if (item.Time != null)
{
DateTime time = Convert.ToDateTime(item.Time);
row.CreateCell(time.Month).SetCellValue((double)item.Value);
row.GetCell(time.Month).CellStyle = style;
total += Convert.ToDecimal(item.Value);
}
}
row.CreateCell(13).SetCellValue((double)total);
row.GetCell(13).CellStyle = style;
break;
}
rowId++;
}
}
Excel excel = new Excel();
excel.Name = build.BuildName + "区域用能" + reportType + "[" + Guid.NewGuid().ToString("N") + "].xls";
using (MemoryStream stream = new MemoryStream())
{
book.Write(stream);
stream.Seek(0, SeekOrigin.Begin);
excel.Data = stream.ToArray();
}
return(excel);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <Author> 2010-5-8 22:21:41</Author>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, Dictionary <string, string> columnNames, Dictionary <string, string> dataformats)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "kakake"; //填加xls文件作者信息
si.ApplicationName = ""; //填加xls文件创建程序信息
si.LastAuthor = ""; //填加xls文件最后保存者信息
si.Comments = "说明信息"; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
HSSFCellStyle customStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat customformat = (HSSFDataFormat)workbook.CreateDataFormat();
//取得列宽
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;
int index = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
if (columnNames.Count > 0)
{
sheet.AddMergedRegion(new Region(0, 0, 0, columnNames.Count - 1));
}
else
{
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
}
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
index = 0;
foreach (DataColumn column in dtSource.Columns)
{
if (columnNames.Count > 0)
{
if (columnNames.ContainsKey(column.ColumnName))
{
headerRow.CreateCell(index).SetCellValue(columnNames[column.ColumnName]);
headerRow.GetCell(index).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(index, (Encoding.GetEncoding(936).GetBytes(columnNames[column.ColumnName]).Length + 1) * 256);
index++;
}
}
else
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
index = 0;
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
if (columnNames.Count > 0)
{
if (columnNames.ContainsKey(column.ColumnName))
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(index);
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);
//HSSFCellStyle celldoubleStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//int pos = Convert.ToString(doubV).Length - Convert.ToString(doubV).IndexOf('.') - 1;
//if (pos == 4)
//{
// celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.0000");
//}
//else if (pos == 2)
//{
// celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
//}
//newCell.CellStyle = celldoubleStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
if (dataformats.ContainsKey(column.ColumnName))
{
customStyle.DataFormat = customformat.GetFormat(dataformats[column.ColumnName]);
newCell.CellStyle = customStyle;
}
index++;
}
}
else
{
HSSFCell newCell = (HSSFCell)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);
//HSSFCellStyle celldoubleStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//int pos = Convert.ToString(doubV).Length - Convert.ToString(doubV).IndexOf('.') - 1;
//if (pos == 4)
//{
// celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.0000");
//}
//else if (pos == 2)
//{
// celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
//}
//newCell.CellStyle = celldoubleStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
if (dataformats.ContainsKey(column.ColumnName))
{
customStyle.DataFormat = customformat.GetFormat(dataformats[column.ColumnName]);
newCell.CellStyle = customStyle;
}
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Workbook.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放 sheet
return(ms);
}
}
/// <summary>
/// 执行导出操作
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
public byte[] Export <T>(List <T> list)
{
//写入表格头
WriteHead();
//写入数据
ICellStyle cellStyle = _workbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.Alignment = HorizontalAlignment.Center;
IFont cellFont = _workbook.CreateFont();
cellFont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellFont);
//建立行内容，从1开始
int rowInex = 1;
foreach (var rowItem in list)
{
//创建行
IRow row = _sheet.CreateRow(rowInex);
row.HeightInPoints = 25;
int cellIndex = 0;
foreach (var cellItem in this.Fields)
{
//创建单元格
ICell cell = row.CreateCell(cellIndex);
//反射获取属性的值
PropertyInfo info = rowItem.GetType().GetProperty(cellItem.Key);
if (info == null)
{
cell.SetCellValue($"'{cellItem.Key}'属性不存在");
}
else
{
object value = info.GetValue(rowItem);
if (value != null && value.ToString() != "0001/1/1 0:00:00")
{
cell.SetCellValue(value.ToString());
}
}
cell.CellStyle = cellStyle;
cellIndex++;
}
//进入下一次循环
rowInex++;
}
byte[] resultByte = null;
//保存
using (MemoryStream ms = new MemoryStream())
{
_workbook.Write(ms);
resultByte = ms.ToArray();
ms.Dispose();
}
return(resultByte);
}
public static void DtToExcelByNPOI(DataTable dt, string strExcelFileName)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 1;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in dt.Columns)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//写Excel
FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
}
catch (Exception ex)
{
}
}
private void GenerarFormatos()
{
HSSFPalette Colores = hssfworkbook.GetCustomPalette();
Colores.SetColorAtIndex(HSSFColor.GREEN.index, (byte)83, (byte)147, (byte)91); //Verde
Colores.SetColorAtIndex(HSSFColor.DARK_YELLOW.index, (byte)231, (byte)204, (byte)45); //Amarillo Fuerte
Colores.SetColorAtIndex(HSSFColor.LIGHT_YELLOW.index, (byte)248, (byte)241, (byte)196); //Amarillo Suave
Colores.SetColorAtIndex(HSSFColor.WHITE.index, (byte)255, (byte)255, (byte)255); //Blanco
Colores.SetColorAtIndex(HSSFColor.BLACK.index, (byte)0, (byte)0, (byte)0); //Negro
Colores.SetColorAtIndex(HSSFColor.GREY_25_PERCENT.index, (byte)216, (byte)228, (byte)188); //Verde Suave
IFont FBlanca12 = hssfworkbook.CreateFont();
FBlanca12.Color = HSSFColor.WHITE.index;
FBlanca12.IsItalic = false;
FBlanca12.FontHeightInPoints = 12;
IFont FBlanca10 = hssfworkbook.CreateFont();
FBlanca10.Color = HSSFColor.WHITE.index;
FBlanca10.IsItalic = false;
FBlanca10.FontHeightInPoints = 10;
IFont FNegra = hssfworkbook.CreateFont();
FNegra.Color = HSSFColor.BLACK.index;
FNegra.IsItalic = false;
FNegra.FontHeightInPoints = 10;
EstTitulos = hssfworkbook.CreateCellStyle();
EstTitulos.Alignment = HorizontalAlignment.CENTER;
EstTitulos.FillPattern = FillPatternType.SOLID_FOREGROUND;
EstTitulos.FillForegroundColor = HSSFColor.GREEN.index;
EstTitulos.VerticalAlignment = VerticalAlignment.CENTER;
EstTitulos.SetFont(FBlanca10);
EstTitulos.IsLocked = true;
EstInvernadero = hssfworkbook.CreateCellStyle();
EstInvernadero.Alignment = HorizontalAlignment.CENTER;
EstInvernadero.FillPattern = FillPatternType.SOLID_FOREGROUND;
EstInvernadero.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
EstInvernadero.VerticalAlignment = VerticalAlignment.CENTER;
EstInvernadero.SetFont(FNegra);
EstInvernadero.IsLocked = true;
EstSubtitulos = hssfworkbook.CreateCellStyle();
EstSubtitulos.Alignment = HorizontalAlignment.CENTER;
EstSubtitulos.FillPattern = FillPatternType.SOLID_FOREGROUND;
EstSubtitulos.FillForegroundColor = HSSFColor.DARK_YELLOW.index;
EstSubtitulos.SetFont(FBlanca10);
EstSubtitulos.IsLocked = true;
EstCeldas = hssfworkbook.CreateCellStyle();
EstCeldas.Alignment = HorizontalAlignment.CENTER;
EstCeldas.FillPattern = FillPatternType.SOLID_FOREGROUND;
EstCeldas.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
EstCeldas.SetFont(FNegra);
EstCeldas.IsLocked = false;
EsString = hssfworkbook.CreateCellStyle();
EsString.Alignment = HorizontalAlignment.CENTER;
EsString.FillPattern = FillPatternType.SOLID_FOREGROUND;
EsString.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
EsString.SetFont(FNegra);
EsString.IsLocked = false;
var formatId = HSSFDataFormat.GetBuiltinFormat("@");
if (formatId == -1)
{
var newDataFormat = hssfworkbook.CreateDataFormat();
EsString.DataFormat = newDataFormat.GetFormat("@");
}
else
{
EsString.DataFormat = formatId;
}
RenglonAmarillo = hssfworkbook.CreateCellStyle();
RenglonAmarillo.Alignment = HorizontalAlignment.CENTER;
RenglonAmarillo.FillPattern = FillPatternType.SOLID_FOREGROUND;
RenglonAmarillo.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
RenglonAmarillo.SetFont(FNegra);
RenglonAmarillo.IsLocked = false;
RenglonBlanco = hssfworkbook.CreateCellStyle();
RenglonBlanco.Alignment = HorizontalAlignment.CENTER;
RenglonBlanco.FillPattern = FillPatternType.SOLID_FOREGROUND;
RenglonBlanco.FillForegroundColor = HSSFColor.WHITE.index;
RenglonBlanco.SetFont(FNegra);
RenglonBlanco.IsLocked = false;
formatId = HSSFDataFormat.GetBuiltinFormat("#,###,##0;-#,###,##0");
if (formatId == -1)
{
var newDataFormat = hssfworkbook.CreateDataFormat();
EstCeldas.DataFormat = newDataFormat.GetFormat("#,###,##0;-#,###,##0");
}
else
{
EstCeldas.DataFormat = formatId;
}
EstCeldasDate = hssfworkbook.CreateCellStyle();
EstCeldasDate.Alignment = HorizontalAlignment.CENTER;
EstCeldasDate.FillPattern = FillPatternType.SOLID_FOREGROUND;
EstCeldasDate.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
EstCeldasDate.SetFont(FNegra);
EstCeldasDate.IsLocked = false;
formatId = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
if (formatId == -1)
{
var newDataFormat = hssfworkbook.CreateDataFormat();
EstCeldasDate.DataFormat = newDataFormat.GetFormat("m/d/yy");
}
else
{
EstCeldasDate.DataFormat = formatId;
}
EsDateTime = hssfworkbook.CreateCellStyle();
EsDateTime.Alignment = HorizontalAlignment.CENTER;
EsDateTime.FillPattern = FillPatternType.SOLID_FOREGROUND;
EsDateTime.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
EsDateTime.SetFont(FNegra);
EsDateTime.IsLocked = false;
formatId = HSSFDataFormat.GetBuiltinFormat("YYYY-MM-DD");
if (formatId == -1)
{
var newDataFormat = hssfworkbook.CreateDataFormat();
EsDateTime.DataFormat = newDataFormat.GetFormat("YYYY-MM-DD");
}
else
{
EsDateTime.DataFormat = formatId;
}
EstCeldasP = hssfworkbook.CreateCellStyle();
EstCeldasP.Alignment = HorizontalAlignment.CENTER;
EstCeldasP.FillPattern = FillPatternType.SOLID_FOREGROUND;
EstCeldasP.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
EstCeldasP.SetFont(FNegra);
EstCeldasP.IsLocked = false;
formatId = HSSFDataFormat.GetBuiltinFormat("0%");
if (formatId == -1)
{
var newDataFormat = hssfworkbook.CreateDataFormat();
EstCeldasP.DataFormat = newDataFormat.GetFormat("0%");
}
else
{
EstCeldasP.DataFormat = formatId;
}
EstCeldasPercentLocked = hssfworkbook.CreateCellStyle();
EstCeldasPercentLocked.Alignment = HorizontalAlignment.CENTER;
EstCeldasPercentLocked.FillPattern = FillPatternType.SOLID_FOREGROUND;
EstCeldasPercentLocked.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
EstCeldasPercentLocked.SetFont(FNegra);
EstCeldasPercentLocked.IsLocked = true;
formatId = HSSFDataFormat.GetBuiltinFormat("0%");
if (formatId == -1)
{
var newDataFormat = hssfworkbook.CreateDataFormat();
EstCeldasPercentLocked.DataFormat = newDataFormat.GetFormat("0%");
}
else
{
EstCeldasPercentLocked.DataFormat = formatId;
}
}
private static void CreateSheets(HSSFWorkbook workbook, DataTable dt, string sheetName)
{
ISheet sheet = workbook.CreateSheet(sheetName);
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 1;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in dt.Columns)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
}
private void HSSFAnswer(HSSFWorkbook hssfworkbook, DataTable dt, ref int iCurrentRow)
{
ISheet sheet = hssfworkbook.GetSheetAt(0);
int iRow = 8, iColumn = 0;
IRow row;
ICell cell;
ICellStyle style = GetNormalStyle(hssfworkbook);
IFont font = style.GetFont(hssfworkbook);
font.FontHeightInPoints = 11;
font.FontName = "Times New Roman";
//style.VerticalAlignment = VerticalAlignment.Bottom;
style.Alignment = HorizontalAlignment.General;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
foreach (DataRow dr in dt.Rows)
{
ICellStyle styleCell1 = GetNormalStyle(hssfworkbook);
font = styleCell1.GetFont(hssfworkbook);
font.FontHeightInPoints = 11;
font.FontName = "Times New Roman";
//style.VerticalAlignment = VerticalAlignment.Bottom;
styleCell1.Alignment = HorizontalAlignment.General;
styleCell1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleCell1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
row = sheet.CreateRow(iRow++);
iColumn = 0;
cell = row.CreateCell(iColumn++, CellType.Numeric);
cell.CellStyle = styleCell1;
cell.CellStyle.Alignment = HorizontalAlignment.Center;
cell.CellStyle.VerticalAlignment = VerticalAlignment.Justify;
cell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Double;
ICellStyle styleTxt = GetNormalStyle(hssfworkbook);
if (Convert.ToInt32(dr[0]) == 1001 || Convert.ToInt32(dr[0]) == 1002)
{
//cell.SetCellValue(" ");
//string value = dr[0].ToString();// +".";
//cell.SetCellValue(Convert.ToInt32(dr[0]));
CellRangeAddress cellrange = new CellRangeAddress(iRow - 1, iRow - 1, 0, 1);
MergeAreaBorder(hssfworkbook, sheet, cellrange, dr[1].ToString());
sheet.AddMergedRegion(cellrange);
iColumn++;
}
else
{
cell.SetCellValue(Convert.ToInt32(dr[0]));
cell = row.CreateCell(iColumn++, CellType.String);
styleTxt.Alignment = HorizontalAlignment.General;
styleTxt.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleTxt.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cell.CellStyle = styleTxt;
cell.SetCellValue(dr[1].ToString());
}
for (; iColumn < dt.Columns.Count - 1; iColumn++)
{
cell = row.CreateCell(iColumn, CellType.Numeric);
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Justify;
cell.CellStyle = style;
if (Convert.ToInt32(dr[0]) == 1001 || Convert.ToInt32(dr[0]) == 1002)
{
styleTxt = GetHeaderStyle(hssfworkbook);
styleTxt.BorderBottom = NPOI.SS.UserModel.BorderStyle.Double;
styleTxt.BorderTop = NPOI.SS.UserModel.BorderStyle.Double;
styleTxt.GetFont(hssfworkbook).Boldweight = (short)FontBoldWeight.Normal;
//styleTxt.DataFormat = 0xa;
cell.CellStyle = styleTxt;
if (Convert.ToInt32(dr[0]) == 1002)
{
HSSFDataFormat formatPercent = (NPOI.HSSF.UserModel.HSSFDataFormat)hssfworkbook.CreateDataFormat();
cell.CellStyle.DataFormat = formatPercent.GetFormat("0.0%");
}
}
cell.SetCellValue(Convert.ToDouble(dr[iColumn]));
}
cell = row.CreateCell(iColumn);
styleTxt = GetHeaderStyle(hssfworkbook);
styleTxt.GetFont(hssfworkbook).Boldweight = (short)FontBoldWeight.Normal;
styleTxt.BorderBottom = NPOI.SS.UserModel.BorderStyle.Double;
styleTxt.BorderTop = NPOI.SS.UserModel.BorderStyle.Double;
styleTxt.BorderLeft = NPOI.SS.UserModel.BorderStyle.Double;
styleTxt.BorderRight = NPOI.SS.UserModel.BorderStyle.Double;
cell.CellStyle = styleTxt;
if (Convert.ToInt32(dr[0]) == 1002)
{
HSSFDataFormat formatPercent = (NPOI.HSSF.UserModel.HSSFDataFormat)hssfworkbook.CreateDataFormat();
cell.CellStyle.DataFormat = formatPercent.GetFormat("0.0%");
cell.SetCellValue(Convert.ToDouble(dr[iColumn]));
}
else
{
cell.SetCellValue(Convert.ToDouble(dr[iColumn]) * 100);
}
}
iCurrentRow = iRow;
}
/// <summary>DataTable导出到Excel的MemoryStream</summary>
static MemoryStream ExportDT(DataTable dtSource, string strHeaderText, string[] str)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
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 == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
#region 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontName = str[5]; //[5]
font.FontHeightInPoints = Convert.ToInt16(str[0]); //[0]
font.Boldweight = Convert.ToInt16(str[1]); //[1]
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontName = str[6]; //[6]
font.FontHeightInPoints = Convert.ToInt16(str[2]); //[2]
font.Boldweight = Convert.ToInt16(str[3]); //[3]
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) * Convert.ToInt32(str[4])); //[4]
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
string result = drValue;
newCell.SetCellValue(result);
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++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet;
//workbook.Dispose();
return(ms);
}
/// <summary>
/// 创建工作表
/// </summary>
/// <param name="workBook"></param>
/// <param name="dt"></param>
/// <param name="name"></param>
/// <param name="header"></param>
/// <returns></returns>
private static HSSFSheet CreateSheet(HSSFWorkbook workBook, DataTable dt, string name = "sheet1", string header = "")
{
HSSFSheet sheet = (HSSFSheet)workBook.CreateSheet(name);
HSSFCellStyle dateStyle = (HSSFCellStyle)workBook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workBook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dt.Columns.Count];
foreach (DataColumn item in dt.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dt.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workBook.CreateSheet();
}
#region 表头及样式
{
if (!string.IsNullOrEmpty(header))
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(header);
HSSFCellStyle headStyle = (HSSFCellStyle)workBook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workBook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
font.FontName = "微软雅黑";
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workBook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workBook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
font.FontName = "微软雅黑";
headStyle.SetFont(font);
foreach (DataColumn column in dt.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
HSSFCellStyle cellStyle = (HSSFCellStyle)workBook.CreateCellStyle();
HSSFFont cellFont = (HSSFFont)workBook.CreateFont();
cellFont.FontName = "微软雅黑";
cellStyle.SetFont(cellFont);
foreach (DataColumn column in dt.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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;
}
newCell.CellStyle = cellStyle;
}
#endregion
rowIndex++;
}
return(sheet);
}
static ICellStyle Getcellstyle(IWorkbook wb, stylexls str = stylexls.默认)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 10;
font12.FontName = "微软雅黑";
IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Black.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "微软雅黑";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;
//背景图形，我没有用到过。感觉很丑
//cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
cellStyle.FillForegroundColor = HSSFColor.White.Index;
// cellStyle.FillPattern = FillPatternType.NO_FILL;
cellStyle.FillBackgroundColor = HSSFColor.Maroon.Index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
return(cellStyle);
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.头:
// cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
cellStyle.SetFont(font12);
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = FontUnderlineType.Single;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.SetFont(font);
break;
}
return(cellStyle);
}
public static void GridToExcelLotYield(string sheetname, string lotid, string date,
string[] summ1, string[] summ2, string[] summ3, DataGridView gv, string strExcelFileName, bool summflag, string[] summ4 = null, DataGridView gv1 = null)
{
HSSFWorkbook workbook = new HSSFWorkbook();
try
{
var rIndex = 0;
ISheet sheet = workbook.CreateSheet(sheetname);
//汇总栏
ICellStyle SummStyle = workbook.CreateCellStyle();
SummStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
SummStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont summfont = workbook.CreateFont();
summfont.Boldweight = (short)FontBoldWeight.Bold;
summfont.Color = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;
SummStyle.SetFont(summfont);
SummStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;
SummStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Lavender.Index;
//Lot ID
IRow summRow_1 = sheet.CreateRow(0);
summRow_1.Height = 20 * 20;
if (summ4 == null)
{
ICell cell_1 = summRow_1.CreateCell(0);
cell_1.SetCellValue("Lot ID:");
cell_1.CellStyle = SummStyle;
ICell cell_2 = summRow_1.CreateCell(1);
cell_2.SetCellValue(lotid);
cell_2.CellStyle = SummStyle;
ICell cell_3 = summRow_1.CreateCell(2);
cell_3.SetCellValue("Date:");
cell_3.CellStyle = SummStyle;
ICell cell_4 = summRow_1.CreateCell(3);
cell_4.SetCellValue(date);
cell_4.CellStyle = SummStyle;
}
if (summflag)
{
//汇总
IRow summRow_2 = sheet.CreateRow(1);
summRow_2.Height = 20 * 20;
ICell cell_21 = summRow_2.CreateCell(0);
cell_21.SetCellValue(summ1[0]);
cell_21.CellStyle = SummStyle;
ICell cell_22 = summRow_2.CreateCell(1);
cell_22.SetCellValue(summ1[1]);
cell_22.CellStyle = SummStyle;
ICell cell_23 = summRow_2.CreateCell(2);
cell_23.SetCellValue(summ1[2]);
cell_23.CellStyle = SummStyle;
ICell cell_24 = summRow_2.CreateCell(3);
cell_24.SetCellValue(summ1[3]);
cell_24.CellStyle = SummStyle;
ICell cell_25 = summRow_2.CreateCell(4);
cell_25.SetCellValue("Create Date:");
cell_25.CellStyle = SummStyle;
ICell cell_26 = summRow_2.CreateCell(5);
cell_26.SetCellValue(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
cell_26.CellStyle = SummStyle;
IRow summRow_3 = sheet.CreateRow(2);
summRow_3.Height = 20 * 20;
ICell cell_31 = summRow_3.CreateCell(0);
cell_31.SetCellValue(summ2[0]);
cell_31.CellStyle = SummStyle;
ICell cell_32 = summRow_3.CreateCell(1);
cell_32.SetCellValue(summ2[1]);
cell_32.CellStyle = SummStyle;
ICell cell_33 = summRow_3.CreateCell(2);
cell_33.SetCellValue(summ2[2]);
cell_33.CellStyle = SummStyle;
ICell cell_34 = summRow_3.CreateCell(3);
cell_34.SetCellValue(summ2[3]);
cell_34.CellStyle = SummStyle;
IRow summRow_4 = sheet.CreateRow(3);
summRow_4.Height = 20 * 20;
ICell cell_41 = summRow_4.CreateCell(0);
cell_41.SetCellValue(summ3[0]);
cell_41.CellStyle = SummStyle;
ICell cell_42 = summRow_4.CreateCell(1);
cell_42.SetCellValue(summ3[1]);
cell_42.CellStyle = SummStyle;
if (summ3.Length > 2)
{
ICell cell_43 = summRow_4.CreateCell(2);
cell_43.SetCellValue(summ3[2]);
cell_43.CellStyle = SummStyle;
ICell cell_44 = summRow_4.CreateCell(3);
cell_44.SetCellValue(summ3[3]);
cell_44.CellStyle = SummStyle;
}
if (summ4 != null)
{
IRow r = sheet.CreateRow(4);
r.Height = 20 * 20;
IRow r4 = null;
for (int i = 0; i < summ4.Length; i++)
{
var cIndex = 1;
if (i % 2 == 0)
{
r4 = sheet.CreateRow(5 + rIndex);
r4.Height = 20 * 20;
cIndex = 0;
rIndex++;
}
ICell c = r4.CreateCell(cIndex);
c.SetCellValue(summ4[i]);
c.CellStyle = SummStyle;
}
rIndex++;
}
}
//表格头
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HeadercellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//HeadercellStyle.FillBackgroundColorColor
HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Lavender.Index;
var waferId = string.Empty;
int tDefects = 0, badDie = 0;
var yieldLoss = 0.00;
if (gv1 != null)
{
int icolIndex1 = 0;
int ridx1 = (summflag ? 4 + rIndex : 1 + rIndex);
IRow r = sheet.CreateRow(ridx1);
r.Height = 20 * 20; rIndex++; ridx1++;
IRow headerRow1 = sheet.CreateRow(ridx1); //rIndex++;
headerRow1.Height = 20 * 20;
foreach (DataGridViewColumn item in gv1.Columns)
{
if (!item.Visible)
{
continue;
}
ICell cell = headerRow1.CreateCell(icolIndex1);
sheet.SetColumnWidth(icolIndex1, item.Width * 50);
cell.SetCellValue(item.HeaderText);
cell.CellStyle = HeadercellStyle;
icolIndex1++;
}
ICellStyle cellStyle1 = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle1.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont1 = workbook.CreateFont();
cellfont1.Boldweight = (short)FontBoldWeight.Normal;
cellStyle1.SetFont(cellfont1);
//建立内容行
int iRowIndex1 = (summflag ? 5 + rIndex : 2 + rIndex);
int iCellIndex1 = 0;
if (gv1.Rows.Count > 0)
{
foreach (DataGridViewRow Rowitem in gv1.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex1);
foreach (DataGridViewColumn Colitem in gv1.Columns)
{
if (Colitem.Visible)
{
ICell cell = DataRow.CreateCell(iCellIndex1);
cell.SetCellValue(Rowitem.Cells[Colitem.Name].FormattedValue.ToString());
cell.CellStyle = cellStyle1;
iCellIndex1++;
if (Colitem.Name == "SumYieldLoss")
{
yieldLoss += Convert.ToDouble(Rowitem.Cells[Colitem.Name].Value);
}
}
}
iCellIndex1 = 0;
iRowIndex1++;
rIndex++;
}
IRow DataRows = sheet.CreateRow(iRowIndex1);
foreach (DataGridViewColumn Colitem in gv1.Columns)
{
if (Colitem.Visible)
{
ICell cell = DataRows.CreateCell(iCellIndex1);
if (Colitem.Name == "SumYieldLoss")
{
cell.SetCellValue(string.Format("{0}/{1}", yieldLoss, 100 - yieldLoss));
}
cell.CellStyle = cellStyle1;
iCellIndex1++;
}
}
iCellIndex1 = 0;
iRowIndex1++;
rIndex++;
}
rIndex++;
}
//用column name 作为列名
int icolIndex = 0;
int ridx = (summflag ? 4 + rIndex : 1 + rIndex);
IRow r2 = sheet.CreateRow(ridx);
r2.Height = 20 * 20; rIndex++; ridx++;
IRow headerRow = sheet.CreateRow(ridx); //rIndex++;
headerRow.Height = 20 * 20;
foreach (DataGridViewColumn item in gv.Columns)
{
if (!item.Visible)
{
continue;
}
ICell cell = headerRow.CreateCell(icolIndex);
sheet.SetColumnWidth(icolIndex, item.Width * 50);
cell.SetCellValue(item.HeaderText);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
ICellStyle sumStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
sumStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
sumStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
sumStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
sumStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
sumStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont sumfont = workbook.CreateFont();
sumfont.Boldweight = (short)FontBoldWeight.Bold;
sumStyle.SetFont(sumfont);
//建立内容行
int iRowIndex = (summflag ? 5 + rIndex : 2 + rIndex);
int iCellIndex = 0;
int rcnt = 0;
yieldLoss = 0;
if (gv.Rows.Count > 0)
{
foreach (DataGridViewRow Rowitem in gv.Rows)
{
if (!string.IsNullOrEmpty(waferId) && waferId != Rowitem.Cells["WaferId"].FormattedValue.ToString())
{
IRow DataRows = sheet.CreateRow(iRowIndex);
foreach (DataGridViewColumn Colitem in gv.Columns)
{
if (Colitem.Visible)
{
ICell cell = DataRows.CreateCell(iCellIndex);
if (Colitem.Name != "Code" && Colitem.Name != "Category" && Colitem.Name != "Pareto")
{
if (Colitem.Name == "TotalDefects")
{
cell.SetCellValue(tDefects);
}
else if (Colitem.Name == "DieQuantity")
{
cell.SetCellValue(badDie);
}
else if (Colitem.Name == "YieldLoss")
{
cell.SetCellValue(string.Format("{0}/{1}", yieldLoss, 100 - yieldLoss));
}
else if (Colitem.Name == "WaferId")
{
cell.SetCellValue(waferId);
}
else
{
cell.SetCellValue(Rowitem.Cells[Colitem.Name].FormattedValue.ToString());
}
}
cell.CellStyle = sumStyle;
iCellIndex++;
}
}
tDefects = 0;
badDie = 0;
yieldLoss = 0;
iCellIndex = 0;
iRowIndex++;
rIndex++;
sheet.CreateRow(iRowIndex);
iRowIndex++;
rIndex++;
}
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataGridViewColumn Colitem in gv.Columns)
{
if (Colitem.Visible)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem.Cells[Colitem.Name].FormattedValue.ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
if (Colitem.Name == "TotalDefects")
{
tDefects += Convert.ToInt16(Rowitem.Cells[Colitem.Name].Value);
}
else if (Colitem.Name == "DieQuantity")
{
badDie += Convert.ToInt16(Rowitem.Cells[Colitem.Name].Value);
}
else if (Colitem.Name == "YieldLoss")
{
yieldLoss += Convert.ToDouble(Rowitem.Cells[Colitem.Name].Value);
}
}
}
iCellIndex = 0;
iRowIndex++;
rIndex++;
rcnt++;
waferId = Rowitem.Cells["WaferId"].FormattedValue.ToString();
if (!string.IsNullOrEmpty(waferId) && rcnt == gv.Rows.Count)
{
IRow DataRows = sheet.CreateRow(iRowIndex);
foreach (DataGridViewColumn Colitem in gv.Columns)
{
if (Colitem.Visible)
{
ICell cell = DataRows.CreateCell(iCellIndex);
if (Colitem.Name != "Code" && Colitem.Name != "Category" && Colitem.Name != "Pareto")
{
if (Colitem.Name == "TotalDefects")
{
cell.SetCellValue(tDefects);
}
else if (Colitem.Name == "DieQuantity")
{
cell.SetCellValue(badDie);
}
else if (Colitem.Name == "YieldLoss")
{
cell.SetCellValue(string.Format("{0}/{1}", yieldLoss, 100 - yieldLoss));
}
else if (Colitem.Name == "WaferId")
{
cell.SetCellValue(waferId);
}
else
{
cell.SetCellValue(Rowitem.Cells[Colitem.Name].FormattedValue.ToString());
}
}
cell.CellStyle = sumStyle;
iCellIndex++;
}
}
tDefects = 0;
badDie = 0;
yieldLoss = 0;
iCellIndex = 0;
iRowIndex++;
rIndex++;
IRow drSum = sheet.CreateRow(iRowIndex);
drSum.Height = 20 * 20;
iRowIndex++;
rIndex++;
}
}
////自适应列宽度
//for (int i = 0; i < icolIndex; i++)
//{
// sheet.AutoSizeColumn(i);
//}
}
//写Excel
FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
}
catch (Exception ex)
{
throw ex;
}
finally { workbook = null; }
}
/// <summary>
/// 定义单元格常用到样式
/// </summary>
/// <param name="wb"></param>
/// <param name="st"></param>
/// <returns></returns>
static ICellStyle CreateCellStyle(IWorkbook wb, StyleType st = StyleType.normal)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
//水平垂直居中
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//背景颜色
cellStyle.FillForegroundColor = 9;
cellStyle.FillPattern = FillPattern.SolidForeground;
//默认
IFont font = wb.CreateFont();
font.FontHeightInPoints = 10;
font.Color = 8;
switch (st)
{
case StyleType.normal:
break;
case StyleType.headCenter:
font.FontHeightInPoints = 10;
font.IsBold = true;
break;
case StyleType.headLeft:
font.FontHeightInPoints = 10;
font.IsBold = true;
cellStyle.Alignment = HorizontalAlignment.Left;
break;
case StyleType.whiteGreen:
font.Color = 9;
cellStyle.FillForegroundColor = 17;
cellStyle.FillPattern = FillPattern.SolidForeground;
break;
case StyleType.whiteOrange:
font.Color = 9;
cellStyle.FillForegroundColor = 51;
cellStyle.FillPattern = FillPattern.SolidForeground;
break;
case StyleType.blue:
font.Color = 12;
break;
case StyleType.green:
font.Color = 17;
break;
case StyleType.blankGray:
font.Color = 8;
cellStyle.FillForegroundColor = 22;
cellStyle.FillPattern = FillPattern.SolidForeground;
break;
case StyleType.bold:
font.IsBold = true;
break;
case StyleType.headNoBorder:
font.FontHeightInPoints = 10;
font.IsBold = true;
break;
}
cellStyle.SetFont(font);
return(cellStyle);
}
/// <summary>
/// DataGridView导出到Excel的MemoryStream
/// </summary>
/// <param name="myDgv">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream DataGridViewToExcel(DataGridView myDgv, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
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 = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[myDgv.Columns.Count];
foreach (DataGridViewColumn item in myDgv.Columns)
{
arrColWidth[item.Index] = Encoding.GetEncoding(936).GetBytes(item.HeaderText.ToString()).Length;
}
for (int i = 0; i < myDgv.Rows.Count; i++)
{
for (int j = 0; j < myDgv.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(myDgv.Rows[i].Cells[j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataGridViewRow row in myDgv.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataGridViewColumn column in myDgv.Columns)
{
headerRow.CreateCell(column.Index).SetCellValue(column.HeaderText);
headerRow.GetCell(column.Index).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Index, (arrColWidth[column.Index] + 1) * 256);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
if (row.Index > 0)
{
foreach (DataGridViewColumn column in myDgv.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Index);
string drValue = myDgv[column.Index, row.Index - 1].Value.ToString();
switch (column.ValueType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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;
}
}
}
else
{
rowIndex--;
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// Exports to excel.
/// </summary>
/// <returns></returns>
public ActionResult ExportToExcel(int?showInActive)
{
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("DashboardIndicatorsData");
var format = workbook.CreateDataFormat();
sheet.CreateFreezePane(0, 1, 0, 1);
sheet.SetAutoFilter(CellRangeAddress.ValueOf("A1:L1"));
// Add header labels
var rowIndex = 0;
var row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue("Indicator Number");
row.CreateCell(1).SetCellValue("Dashboard");
row.CreateCell(2).SetCellValue("Description");
row.CreateCell(3).SetCellValue("Defination");
row.CreateCell(4).SetCellValue("Sub Category 1");
row.CreateCell(5).SetCellValue("Sub Category 2");
row.CreateCell(6).SetCellValue("Format Type");
row.CreateCell(7).SetCellValue("Decimal Numbers");
row.CreateCell(8).SetCellValue("Ferquency Type");
row.CreateCell(9).SetCellValue("OwnerShip");
row.CreateCell(10).SetCellValue("Facility");
row.CreateCell(11).SetCellValue("SortOrder");
rowIndex++;
using (var bal = new DashboardIndicatorsBal())
{
var indicatorsData = bal.GetDashboardIndicatorsListActiveInActive(Helpers.GetSysAdminCorporateID(), showInActive);
indicatorsData = indicatorsData.OrderBy(x => Convert.ToInt32(x.IndicatorNumber)).ToList();
var cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
foreach (var item in indicatorsData)
{
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellType(CellType.Numeric);
row.CreateCell(0).CellStyle = cellStyle;
row.CreateCell(0).SetCellValue(Convert.ToDouble(item.IndicatorNumber));
row.CreateCell(1).SetCellValue(item.Dashboard);
row.CreateCell(2).SetCellValue(item.Description);
row.CreateCell(3).SetCellValue(item.Defination);
row.CreateCell(4).SetCellValue(item.SubCategoryFirst);
row.CreateCell(5).SetCellValue(item.SubCategorySecond);
row.CreateCell(6).SetCellValue(item.FormatTypeStr);
row.CreateCell(7).SetCellType(CellType.Numeric);
row.CreateCell(7).CellStyle = cellStyle;
row.CreateCell(7).SetCellValue(item.DecimalNumbers);
row.CreateCell(8).SetCellValue(item.FerquencyTypeStr);
row.CreateCell(9).SetCellValue(item.OwnerShip);
row.CreateCell(10).SetCellValue(item.FacilityNameStr);
row.CreateCell(11).SetCellValue(Convert.ToInt32(item.SortOrder));
rowIndex++;
}
}
using (var exportData = new MemoryStream())
{
var currentDateTime = Helpers.GetInvariantCultureDateTime();
var cookie = new HttpCookie("Downloaded", "True");
Response.Cookies.Add(cookie);
workbook.Write(exportData);
var saveAsFileName = string.Format("DashboardIndicatorsData-{0:d}.xls", currentDateTime).Replace("/", "-");
return(File(exportData.ToArray(), "application/vnd.ms-excel", string.Format("{0}", saveAsFileName)));
}
}
/// <summary>
/// 导出数据行
/// </summary>
/// <param name="dtSource"></param>
/// <param name="drSource"></param>
/// <param name="currentExcelRow"></param>
/// <param name="excelSheet"></param>
/// <param name="excelWorkBook"></param>
protected static void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook)
{
for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
{
//列名称
string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
HSSFCell newCell = null;
System.Type rowType = drSource[columnsName].GetType();
string drValue = drSource[columnsName].ToString().Trim();
switch (rowType.ToString())
{
case "System.String": //字符串类型
drValue = drValue.Replace("&", "&");
drValue = drValue.Replace(">", ">");
drValue = drValue.Replace("<", "<");
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(dateV);
//格式化显示
HSSFCellStyle cellStyle = (HSSFCellStyle)excelWorkBook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)excelWorkBook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
newCell.CellStyle = cellStyle;
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
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 = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(intV.ToString());
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue("");
break;
default:
throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
}
}
}
public void ExportToExcel(int page, int size, int store, int supplier, string stockCode, string stockName, int status, string fd, string td, string enable)
{
// Get the data to report on
var masters = _service.ListCondition(page, size, store, supplier, stockCode, stockName, status, fd, td, enable);
//var requisitionDetails = _service.ListConditionDetailExcel(page, size, store, mrf, stockCode, stockName, status,
// fd, td, enable);
// Create a new workbook
var workbook = new HSSFWorkbook();
#region Cell Styles
#region HeaderLabel Cell Style
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER;
headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
headerLabelCellStyle.SetFont(headerLabelFont);
#endregion
#region RightAligned Cell Style
var rightAlignedCellStyle = workbook.CreateCellStyle();
rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT;
#endregion
#region Currency Cell Style
var currencyCellStyle = workbook.CreateCellStyle();
currencyCellStyle.Alignment = HorizontalAlignment.RIGHT;
var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
{
currencyCellStyle.DataFormat = formatId;
}
#endregion
#region Detail Subtotal Style
var detailSubtotalCellStyle = workbook.CreateCellStyle();
detailSubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailSubtotalFont = workbook.CreateFont();
detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailSubtotalCellStyle.SetFont(detailSubtotalFont);
#endregion
#region Detail Currency Subtotal Style
var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle();
detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN;
detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN;
var detailCurrencySubtotalFont = workbook.CreateFont();
detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD;
detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont);
formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
if (formatId == -1)
{
var newDataFormat = workbook.CreateDataFormat();
detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00");
}
else
{
detailCurrencySubtotalCellStyle.DataFormat = formatId;
}
#endregion
#endregion
#region Master sheet
var sheet = workbook.CreateSheet("Price");
// Add header labels
var rowIndex = 0;
// Undestand as row in excel. row + 3 = xuong 3 row.
var row = sheet.CreateRow(rowIndex);
var cell = row.CreateCell(0);
cell.SetCellValue("Store");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(1);
cell.SetCellValue("Supplier");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(2);
cell.SetCellValue("Stock Code");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(3);
cell.SetCellValue("Stock Name");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(4);
cell.SetCellValue("Price");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(5);
cell.SetCellValue("Currency");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(6);
cell.SetCellValue("Status");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(7);
cell.SetCellValue("Start Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(8);
cell.SetCellValue("End Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(9);
cell.SetCellValue("Created Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(10);
cell.SetCellValue("Created By");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(11);
cell.SetCellValue("Modified Date");
cell.CellStyle = headerLabelCellStyle;
cell = row.CreateCell(12);
cell.SetCellValue("Modified By");
cell.CellStyle = headerLabelCellStyle;
rowIndex++;
// Add data rows
foreach (var master in masters)
{
row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(master.Store);
row.CreateCell(1).SetCellValue(master.Supplier);
row.CreateCell(2).SetCellValue(master.Stock_Code);
row.CreateCell(3).SetCellValue(master.Stock_Name);
row.CreateCell(4).SetCellValue(master.Price.ToString());
row.CreateCell(5).SetCellValue(master.Currency);
row.CreateCell(6).SetCellValue(master.Status);
row.CreateCell(7).SetCellValue(master.Start != null
? master.Start.Value.ToString("dd/MM/yyyy")
: master.Start.ToString());
row.CreateCell(8).SetCellValue(master.End != null
? master.End.Value.ToString("dd/MM/yyyy")
: master.End.ToString());
row.CreateCell(9).SetCellValue(master.Created_Date != null
? master.Created_Date.Value.ToString("dd/MM/yyyy")
: master.Created_Date.ToString());
row.CreateCell(10).SetCellValue(master.Created_By);
row.CreateCell(11).SetCellValue(master.Modified_Date != null
? master.Modified_Date.Value.ToString("dd/MM/yyyy")
: master.Modified_Date.ToString());
row.CreateCell(12).SetCellValue(master.Modified_By);
rowIndex++;
}
// Auto-size each column
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
// Bump up with auto-sized column width to account for bold headers
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024);
}
// Add row indicating date/time report was generated...
sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy"));
#endregion
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
var saveAsFileName = string.Format("Price-{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")).Replace("/", "-");
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
}
}
/// <summary>
/// 设置Excel单元格样式（标题），数据格式
/// </summary>
/// <param name="dateType">数据类型</param>
/// <param name="porpetyIndex">单元格索引</param>
/// <param name="sheet">Sheet页</param>
/// <param name="dataSheet">数据Sheet页</param>
/// <param name="dataStyle">样式</param>
/// <param name="dataFormat">格式</param>
public void SetColumnFormat(ColumnDataType dateType, int porpetyIndex, ISheet sheet, ISheet dataSheet, ICellStyle dataStyle, IDataFormat dataFormat)
{
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
CellRangeAddressList CellRangeList = new CellRangeAddressList(1, 1048576 - 1, porpetyIndex, porpetyIndex); //超过1048576最大行数，打开Excel会报错
XSSFDataValidationConstraint dvConstraint = null;
XSSFDataValidation dataValidation = null;
switch (dateType)
{
case ColumnDataType.Date:
case ColumnDataType.DateTime:
//因为DateTime类型，添加Validation报错，所以去掉
dataStyle.DataFormat = dataFormat.GetFormat("yyyy-MM-dd HH:mm:ss");
break;
case ColumnDataType.Number:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? long.MinValue.ToString() : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? long.MaxValue.ToString() : this.MaxValuseOrLength;
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputNumber"]);
dataStyle.DataFormat = dataFormat.GetFormat("0");
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["PleaseInputNumberFormat"], CoreProgram.Callerlocalizer?["DataRange", MinValueOrLength, MaxValuseOrLength]);
break;
case ColumnDataType.Float:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? decimal.MinValue.ToString() : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? decimal.MaxValue.ToString() : this.MaxValuseOrLength;
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.DECIMAL, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputDecimal"]);
dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["PleaseInputDecimalFormat"], CoreProgram.Callerlocalizer?["DataRange", MinValueOrLength, MaxValuseOrLength]);
break;
case ColumnDataType.Bool:
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("Sheet1!$A$1:$B$1");
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]);
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["ComboBox"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]);
break;
case ColumnDataType.Text:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? "0" : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? "2000" : this.MaxValuseOrLength;
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.TEXT_LENGTH, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["WrongTextLength"]);
dataStyle.DataFormat = dataFormat.GetFormat("@");
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["PleaseInputText"], CoreProgram.Callerlocalizer?["DataRange", MinValueOrLength, MaxValuseOrLength]);
break;
case ColumnDataType.ComboBox:
case ColumnDataType.Enum:
int count = this.ListItems.Count() == 0 ? 1 : this.ListItems.Count();
string cloIndex = "";
if (porpetyIndex > 25)
{
cloIndex += Convert.ToChar((int)(Math.Floor(porpetyIndex / 26d)) - 1 + 65);
}
cloIndex += Convert.ToChar(65 + porpetyIndex % 26).ToString();
IName range = sheet.Workbook.CreateName();
range.RefersToFormula = "Sheet2!$" + cloIndex + "$1:$" + cloIndex + "$" + count;
range.NameName = "dicRange" + porpetyIndex;
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("dicRange" + porpetyIndex);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]);
var listItemsTemp = this.ListItems.ToList();
for (int rowIndex = 0; rowIndex < this.ListItems.Count(); rowIndex++)
{
IRow dataSheetRow = dataSheet.GetRow(rowIndex);
if (dataSheetRow == null)
{
dataSheetRow = dataSheet.CreateRow(rowIndex);
}
dataSheetRow.CreateCell(porpetyIndex).SetCellValue(listItemsTemp[rowIndex].Text);
dataStyle.DataFormat = dataFormat.GetFormat("@");
dataSheetRow.Cells.Where(x => x.ColumnIndex == porpetyIndex).FirstOrDefault().CellStyle = dataStyle;
}
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["ComboBox"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]);
break;
default:
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.TEXT_LENGTH, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["WrongTextLength"]);
dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
break;
}
if (dataValidation == null)
{
return;
}
if (!this.IsNullAble)
{
dataValidation.EmptyCellAllowed = false;
}
sheet.SetDefaultColumnStyle(porpetyIndex, dataStyle);
dataValidation.ShowErrorBox = true;
sheet.AddValidationData(dataValidation);
}
static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 10;
font12.FontName = "微软雅黑";
IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OLIVE_GREEN.BLUE.index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "微软雅黑";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.HAIR;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.HAIR;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.DOTTED;
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;
cellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//背景图形，我没有用到过。感觉很丑
//cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
cellStyle.FillForegroundColor = HSSFColor.WHITE.index;
// cellStyle.FillPattern = FillPatternType.NO_FILL;
cellStyle.FillBackgroundColor = HSSFColor.BLUE.index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.header:
// cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
cellStyle.SetFont(font12);
break;
case stylexls.time:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case stylexls.number:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.money:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = 1;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.percent:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.CHupper:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.Tnumber:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.defaults:
cellStyle.SetFont(font);
break;
}
return(cellStyle);
}
MemoryStream Export(bool isPrivate)
{
HSSFWorkbook workBook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workBook.CreateSheet();
#region 右击文件属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "DuGu";
workBook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "DuGu";
si.ApplicationName = "DuGu_NPOI_" + AppInfo.GetName();
si.LastAuthor = "DuGu";
si.Comments = "DuGu";
si.Title = "DuGu";
si.Subject = "DemarcateResults";
si.CreateDateTime = DateTime.Now;
workBook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = (HSSFCellStyle)workBook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workBook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int[] arrColWidth = { 20, 20, 20, 20, 20, 20, 20, 20 };
#region 表头样式
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workBook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workBook.CreateFont();
font.FontHeightInPoints = 25;
font.Boldweight = 400;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, ColumnCount - 1));
#endregion
headerRow = (HSSFRow)sheet.CreateRow(1);
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
font.FontHeightInPoints = 25;
font.Boldweight = 400;
headStyle.SetFont(font);
ExportOptions exportOptions = new ExportOptions();
for (int i = 0; i < ColumnCount; ++i)
{
headerRow.CreateCell(i).SetCellValue(exportOptions.TableString[i]);
headerRow.GetCell(i).CellStyle = headStyle;
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
for (int i = 0; i < 12; ++i)
{
headerRow = (HSSFRow)sheet.CreateRow(i + 2);
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
font.FontHeightInPoints = 15;
font.Boldweight = 400;
headStyle.SetFont(font);
headerRow.CreateCell(0).SetCellValue(i + 1);
headerRow.GetCell(0).CellStyle = headStyle;
if (demaMotionArray != null)
{
for (int j = 1; j < ColumnCount; ++j)
{
headerRow.CreateCell(j).SetCellValue(demaMotionArray[i].Para[j - 1].ToString());
headerRow.GetCell(j).CellStyle = headStyle;
sheet.SetColumnWidth(j, (arrColWidth[j] + 1) * 256);
}
}
if (demaMotionList != null)
{
for (int j = 1; j < ColumnCount; ++j)
{
headerRow.CreateCell(j).SetCellValue(demaMotionList[i].Para[j - 1].ToString());
headerRow.GetCell(j).CellStyle = headStyle;
sheet.SetColumnWidth(j, (arrColWidth[j] + 1) * 256);
}
}
}
using (MemoryStream ms = new MemoryStream())
{
workBook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
private static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
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
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)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++)
{
if (dtSource.Rows[i][j] == null)
{
continue;
}
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 = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)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;
if ((arrColWidth[column.Ordinal] + 1) * 256 >= 10000)
{
sheet.SetColumnWidth(column.Ordinal, 10000);
}
else
{
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)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++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
/// <summary>
/// 按ListColumnsName列名序列，在Excel的指定Sheet中写入行
/// </summary>
/// <param name="workbook">工作簿</param>
/// <param name="sheet">要写入数据的Sheet</param>
/// <param name="rowIndex">要写入的行索引</param>
/// <param name="tableRow">要写入的DataRow</param>
private static void CreateRow(HSSFWorkbook workbook, HSSFSheet sheet, int rowIndex, DataRow tableRow)
{
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
{
//列名称
string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
HSSFCell newCell = dataRow.CreateCell(cellIndex) as HSSFCell;
System.Type rowType = tableRow[columnsName].GetType();
string drValue = tableRow[columnsName].ToString().Trim();
switch (rowType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
//格式化显示
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
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;
}
}
}
public StringBuilder readXls(string file, EERRDataAndMethods eerr, XSSFWorkbook twb, bool applyRate, Double rate)
{
StringBuilder retVal = new StringBuilder("");
HSSFWorkbook wb;
wb = new HSSFWorkbook(new FileStream(file, FileMode.Open));
ISheet sheet = wb.GetSheetAt(0);
IRow r = sheet.GetRow(0);
ICell c = r.GetCell(0);
string company = c.StringCellValue;
XSSFSheet sh = (XSSFSheet)twb.GetSheet("Estado resultado");
string acct = "";
string acctDesc = "";
for (int i = 1; i < sheet.LastRowNum; i++)
{
r = sheet.GetRow(i);
if (r != null)
{
c = r.GetCell(0);
if (c != null)
{
if (c.CellType == CellType.String && (c.StringCellValue).StartsWith(C_STR_IN_ACCOUNT))
{
acct = c.StringCellValue;
acct = acct.Substring(C_STR_IN_ACCOUNT.Length).Trim();
int pos = acct.IndexOf(' ');
acctDesc = acct.Substring(pos + 1).Trim();
acct = acct.Substring(0, pos).Trim();
Console.WriteLine("Account: " + acct + " " + acctDesc);
}
else if (acct.Length > 0 && r.LastCellNum >= 16)
{
//"Estado" 1,"Empresa" 2,"Agrupacion" 3,"Marca," 4,"EERR" 5,"Detalle EERR" 6,"Cuenta" 7,"Desc Cuenta" 8,
//"Mes" 9,"Fecha" 10,"# Compte" 11,"Tipo;Glosa" 12,"Area" 13,"C.Costo" 14,"Item" 15,"Desc Item" 16, "F.Efec" 17,
//"Analisis" 18,"Refer" 19,"Fch Ref" 20,"Fch Vto" 21,"DEBE" 22,"HABER" 23,"SALDO" 24,"Sucursal" 25
string s = "";
XSSFRow row = (XSSFRow)sh.CreateRow(sh.LastRowNum + 1);
XSSFCell cell = (XSSFCell)row.CreateCell(C_OUT_STAT - 1);
cell.SetCellValue(C_DATA_STATUS);
cell = (XSSFCell)row.CreateCell(C_OUT_CIA - 1);
cell.SetCellValue(company);
c = r.GetCell(C_IN_AREA - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_DESC_AREA - 1);
cell.SetCellValue(eerr.getAgrupacion(c.ToString()));
cell = (XSSFCell)row.CreateCell(C_OUT_BRAND - 1);
cell.SetCellValue(eerr.getBrand(getCellValue(c)));
cell = (XSSFCell)row.CreateCell(C_OUT_DET_EERR - 1);
cell.SetCellValue(eerr.getLinea(acct));
cell = (XSSFCell)row.CreateCell(C_OUT_ACCT_NUM - 1);
cell.SetCellValue(acct);
cell = (XSSFCell)row.CreateCell(C_OUT_ACCT_DESC - 1);
cell.SetCellValue(acctDesc);
c = r.GetCell(C_IN_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
cell = (XSSFCell)row.CreateCell(C_OUT_MONTH - 1);
cell.SetCellValue(getMonth(c));
c = r.GetCell(C_IN_COMPTE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_COMPTE - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_TYPE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_TYPE - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_COMMENT - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_COMMENT - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_AREA - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_AREA - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_COST_CENTER - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_COST_CENT - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_ITEM - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_ITEM - 1);
cell.SetCellValue(getCellValue(c));
cell = (XSSFCell)row.CreateCell(C_OUT_ITEM_DESC - 1);
cell.SetCellValue(eerr.getItem(getCellValue(c)));
c = r.GetCell(C_IN_EFF_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_EFF_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
c = r.GetCell(C_IN_ANALISYS_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_ANALYSIS_DATE - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_REFERENCE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_REF - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_REF_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_REF_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
c = r.GetCell(C_IN_EXP_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_EXP_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
XSSFCell deb = null;
short doubleFormat = HSSFDataFormat.GetBuiltinFormat("#,##0"); //wb.CreateDataFormat().GetFormat("#,##0");
double v = 0;
c = r.GetCell(C_IN_DEBIT - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_DEBIT - 1);
deb = cell;
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v))
{
//cell = (XSSFCell)row.CreateCell(C_OUT_DEBIT - 1);
cell.SetCellValue((applyRate?rate:1) * v);
cell.SetCellType(CellType.Numeric);
cell.CellStyle.DataFormat = doubleFormat;
}
}
XSSFCell cred = null;
c = r.GetCell(C_IN_CREDIT - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_CREDIT - 1);
cred = cell;
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v))
{
//cell = (XSSFCell)row.CreateCell(C_OUT_CREDIT - 1);
cell.SetCellValue((applyRate ? rate : 1) * v);
cell.SetCellType(CellType.Numeric);
cell.CellStyle.DataFormat = doubleFormat;
}
}
c = r.GetCell(C_IN_BALANCE - 1);
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v))
{
cell = (XSSFCell)row.CreateCell(C_OUT_BALANCE - 1);
cell.SetCellValue((applyRate ? rate : 1) * v);
cell.SetCellType(CellType.Formula);
cell.SetCellFormula(String.Format("{0}{1}-{2}{3}", C_COL_DEBIT, cell.Row.RowNum + 1, C_COL_CREDIT, cell.Row.RowNum + 1));
cell.CellStyle.DataFormat = doubleFormat;
}
}
c = r.GetCell(C_IN_BRANCH - 1);
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s))
{
cell = (XSSFCell)row.CreateCell(C_OUT_BRANCH - 1);
cell.SetCellValue(eerr.getSucursal(s));
}
}
}
}
}
}
return(retVal);
}
private HSSFCellStyle PhaseCellStyle(CellStyle paramCellStyle)
{
HSSFCellStyle style = null;
//try
//{
style = (HSSFCellStyle)_workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)_workbook.CreateFont();
font.FontName = paramCellStyle.FontName.ToString();
font.FontHeightInPoints = paramCellStyle.FontHeightInPoints;
if (paramCellStyle.FontBoldFlag)
{
font.Boldweight = 700;
}
style.SetFont(font);
//设置水平位置
switch (paramCellStyle.HorizontalAlignment.ToString())
{
case "Center":
style.Alignment = HorizontalAlignment.Center;
break;
case "Left":
style.Alignment = HorizontalAlignment.Left;
break;
case "Right":
style.Alignment = HorizontalAlignment.Right;
break;
default:
style.Alignment = HorizontalAlignment.Center;
break;
}
//设置垂直位置
switch (paramCellStyle.VerticalAlignment.ToString())
{
case "Center":
style.VerticalAlignment = VerticalAlignment.Center;
break;
case "Top":
style.VerticalAlignment = VerticalAlignment.Top;
break;
case "Botton":
style.VerticalAlignment = VerticalAlignment.Bottom;
break;
default:
style.VerticalAlignment = VerticalAlignment.Center;
break;
}
//设置表格线样式宽度
switch (paramCellStyle.BorderStyle.ToString())
{
case "Thin":
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
break;
default:
break;
}
//设置表格线颜色
switch (paramCellStyle.BorderStyle.ToString())
{
case "Black":
style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
break;
default:
break;
}
//设置单元格是否换行
#region 设置为文本
style.WrapText = paramCellStyle.WrapText;
HSSFDataFormat format = (HSSFDataFormat)_workbook.GetCreationHelper().CreateDataFormat();
short index = format.GetFormat("@");
style.DataFormat = index;
#endregion
//}
//catch(Exception ex)
//{
// string ss = ex.Message;
// return null;
//}
return(style);
}
//Datatable导出Excel
public static void GridToExcelByNPOI(DataTable dt, string strExcelFileName, string title)
{
HSSFWorkbook workbook = new HSSFWorkbook();
try
{
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//标题头
int icolIndex = 0;
CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count > 0 ? dt.Columns.Count - 1 : 0);
sheet.AddMergedRegion(region);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 20;
NPOI.SS.UserModel.ICell celltitle = headerRow.CreateCell(0);
celltitle.SetCellValue(title);
celltitle.CellStyle = HeadercellStyle;
//用column name 作为列名
IRow headerRow1 = sheet.CreateRow(1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell1 = headerRow1.CreateCell(icolIndex);
cell1.SetCellValue(item.ColumnName);
cell1.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 2;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//写Excel
FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
workbook = null;
}
}
