/// <summary>
/// 传递dataTable
/// </summary>
/// <param name="dt"></param>
/// <param name="SheetName"></param>
/// <returns></returns>
public static MemoryStream RenderToMemory(DataTable dt, string SheetName, bool isShowTitle = true)
{
MemoryStream ms = new MemoryStream();
using (dt)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(SheetName);
int rowIndex = 0;
if (isShowTitle)
{
//sheet.DisplayGridlines = false;
IRow headerRow = sheet.CreateRow(0);
//设置行高度
headerRow.HeightInPoints = 25;
//表头取值Datatable列头
for (int c = 0; c < dt.Columns.Count; c++)
{
ICell headercell = headerRow.CreateCell(c);
headercell.SetCellValue(dt.Columns[c].ColumnName);
ICellStyle style = workbook.CreateCellStyle();
//设置字体为粗体
IFont font = workbook.CreateFont();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.Color = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;
//设置居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//设置边框
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
style.SetFont(font);
//设置背景颜色
//style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.RED.index;
////style.FillPattern = FillPatternType.SQUARES;
//style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.RED.index;
headercell.CellStyle = style;
}
rowIndex = 1;
}
ICellStyle styledt = workbook.CreateCellStyle();
styledt.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styledt.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styledt.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
styledt.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
styledt.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
styledt.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
styledt.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
styledt.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
foreach (DataRow row in dt.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.HeightInPoints = 19;
for (int c = 0; c < dt.Columns.Count; c++)
{
ICell cell = dataRow.CreateCell(c);
var currField = dt.Columns[c].ColumnName;
string strVale = row[currField].ToString();
if (dt.Columns[currField].DataType == typeof(decimal) || dt.Columns[currField].DataType == typeof(int))
{
cell.SetCellValue(double.Parse(strVale == "" ? "0" : strVale));
}
else
{
cell.SetCellValue(strVale);
}
cell.CellStyle = styledt;
}
rowIndex++;
}
/*为了导出列为中文*/
int colcount = dt.Columns.Count;
for (int c = 0; c < colcount; c++)
{
sheet.AutoSizeColumn(c);
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
return(ms);
}
public static void SaveTableToExcel(DataTable dt, string sheetName, string filepath, string postilColName = null, string postilText = null)
{
var fs = new FileStream(filepath, FileMode.CreateNew);
//MemoryStream ms = new MemoryStream();
IWorkbook workBook = new XSSFWorkbook();//创建工作薄
ISheet sheet = workBook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
//设置行高度
headerRow.HeightInPoints = 25;
//表头取值Datatable列头
for (int c = 0; c < dt.Columns.Count; c++)
{
ICell headercell = headerRow.CreateCell(c);
headercell.SetCellValue(dt.Columns[c].ColumnName);
if (!string.IsNullOrEmpty(postilColName) && dt.Columns[c].ColumnName == postilColName)
{
var patr = sheet.CreateDrawingPatriarch();
var comment1 = patr.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 0, 0));
comment1.String = new XSSFRichTextString(postilText);
headercell.CellComment = comment1;
}
ICellStyle style = workBook.CreateCellStyle();
//设置字体为粗体
IFont font = workBook.CreateFont();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.Color = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;
//设置居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//设置边框
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
style.SetFont(font);
//设置背景颜色
//style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.RED.index;
////style.FillPattern = FillPatternType.SQUARES;
//style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.RED.index;
headercell.CellStyle = style;
}
int rowIndex = 1;
ICellStyle styledt = workBook.CreateCellStyle();
styledt.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styledt.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styledt.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
styledt.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
styledt.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
styledt.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
styledt.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
styledt.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
foreach (DataRow row in dt.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.HeightInPoints = 19;
for (int c = 0; c < dt.Columns.Count; c++)
{
ICell cell = dataRow.CreateCell(c);
var currField = dt.Columns[c].ColumnName;
string strVale = row[currField].ToString();
if (dt.Columns[currField].DataType == typeof(decimal) || dt.Columns[currField].DataType == typeof(int))
{
cell.SetCellValue(double.Parse(strVale == "" ? "0" : strVale));
}
else
{
cell.SetCellValue(strVale);
}
cell.CellStyle = styledt;
}
rowIndex++;
}
/*为了导出列为中文*/
int colcount = dt.Columns.Count;
for (int c = 0; c < colcount; c++)
{
sheet.AutoSizeColumn(c);
}
//workBook.Write(ms);
//ms.Flush();
//ms.Position = 0;
//return ms;
workBook.Write(fs);
}
/// <summary>
/// 根据DataSet导出Excel，多个SheetName
/// </summary>
/// <param name="ds">DataSet数据源</param>
/// <param name="SheetName">SheetName数组</param>
/// <param name="TitleName">每个SheetName第一行标题栏名称，数组类型</param>
/// <param name="isShowTitle">是否显示第一行标题，默认为显示</param>
/// <returns></returns>
public static MemoryStream RenderToMemory(DataSet ds, string[] SheetName, string[] TitleName, bool isShowTitle = true)
{
MemoryStream ms = new MemoryStream();
using (ds)
{
IWorkbook workbook = new HSSFWorkbook();
//第一行样式
ICellStyle titleStyle = TitleStyle(workbook);
//标题栏位样式
ICellStyle headerStyle = HeaderStyle(workbook);
//数据单元格
ICellStyle cellStyle = BodyStyle(workbook);
for (int i = 0; i < ds.Tables.Count; i++)
{
DataTable dt = ds.Tables[i] as DataTable;
ISheet sheet = workbook.CreateSheet(SheetName[i]);
int rowIndex = 0;
if (isShowTitle)
{
IRow title = sheet.CreateRow(rowIndex);
title.HeightInPoints = 35;//设置行高度
ICell headercell = title.CreateCell(0);
headercell.SetCellValue(TitleName[i]);
SetCellRangeAddress(sheet, 0, 0, 0, dt.Columns.Count - 1);
headercell.CellStyle = titleStyle;
rowIndex++;
}
//标题栏位
IRow headerRow = sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 25;
for (int c = 0; c < dt.Columns.Count; c++)
{
ICell headercell = headerRow.CreateCell(c);
headercell.SetCellValue(dt.Columns[c].ColumnName);
headercell.CellStyle = headerStyle;
}
rowIndex++;
foreach (DataRow row in dt.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.HeightInPoints = 19;
for (int c = 0; c < dt.Columns.Count; c++)
{
ICell cell = dataRow.CreateCell(c);
var currField = dt.Columns[c].ColumnName;
string strVale = row[currField].ToString();
if (dt.Columns[currField].DataType == typeof(decimal) || dt.Columns[currField].DataType == typeof(int))
{
cell.SetCellValue(double.Parse(strVale == "" ? "0" : strVale));
}
else
{
cell.SetCellValue(strVale);
}
cell.CellStyle = cellStyle;
}
rowIndex++;
}
/*为了导出列为中文*/
int colcount = dt.Columns.Count;
for (int c = 0; c < colcount; c++)
{
sheet.AutoSizeColumn(c);
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
return(ms);
}
/// <summary>
/// 按照指定的列来导出
/// </summary>
/// <param name="ds"></param>
/// <param name="ExcelHeaderList"></param>
/// <param name="ColsNameList"></param>
/// <returns></returns>
public static MemoryStream RenderToMemory(DataSet ds, string[] ExcelHeaderList, string[] ColsNameList, string SheetName, Func <IWorkbook, ISheet, int> fnSetHeader = null, Action <IWorkbook, ISheet, int, int> fnSetFooter = null)
{
MemoryStream ms = new MemoryStream();
using (ds)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(SheetName);
sheet.DisplayGridlines = false;
int begindex = 0;
if (fnSetHeader != null)
{
begindex = fnSetHeader(workbook, sheet);
}
IRow headerRow = sheet.CreateRow(begindex);
//设置行高度
headerRow.HeightInPoints = 25;
ICellStyle style = workbook.CreateCellStyle();
//设置字体为粗体
IFont font = workbook.CreateFont();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.Color = NPOI.HSSF.Util.HSSFColor.BlueGrey.Index;
//设置居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//设置边框
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
style.SetFont(font);
//设置背景颜色
//style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.RED.index;
////style.FillPattern = FillPatternType.SQUARES;
//style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.RED.index;
for (int c = 0; c < ExcelHeaderList.Length; c++)
{
ICell headercell = headerRow.CreateCell(c);
headercell.SetCellValue(ExcelHeaderList[c]);
headercell.CellStyle = style;
}
// handling value.
int rowIndex = begindex + 1;
ICellStyle styledt = workbook.CreateCellStyle();
styledt.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styledt.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styledt.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
styledt.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
styledt.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
styledt.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
styledt.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
styledt.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
foreach (DataRow row in ds.Tables[0].Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.HeightInPoints = 19;
for (int c = 0; c < ColsNameList.Length; c++)
{
ICell cell = dataRow.CreateCell(c);
string strVale = row[ColsNameList[c]].ToString();
if (ds.Tables[0].Columns[ColsNameList[c]].DataType == typeof(decimal) || ds.Tables[0].Columns[ColsNameList[c]].DataType == typeof(int))
{
cell.SetCellValue(double.Parse(strVale == "" ? "0" : strVale));
}
else
{
cell.SetCellValue(strVale);
}
cell.CellStyle = styledt;
}
rowIndex++;
}
if (fnSetFooter != null)
{
fnSetFooter(workbook, sheet, begindex, rowIndex);
}
/*为了导出列为中文*/
int colcount = ExcelHeaderList.Length;
if (colcount == 13)
{
colcount = colcount - 7;
}
for (int c = 0; c < colcount; c++)
{
sheet.AutoSizeColumn(c);
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
return(ms);
}
public void ExportItemSourceToExcel(List <ProductiveTaskListModel> lists, string filePath)
{
if (lists.Count == 0)
{
return;
}
string part1 = lists[0].FProductionDate.ToLongDateString().ToString();
string fName = Path.Combine(filePath, $"{part1}生产任务清单.xls");
//如果存在此文件则添加1
if (File.Exists(fName))
{
fName = fName.Replace(".xls", DateTime.Now.ToString("HHmmss") + ".xls");
}
IWorkbook wb = new HSSFWorkbook();
//定义单元格格式
#region 缩小填充
ICellStyle fitStyle = wb.CreateCellStyle();
fitStyle.ShrinkToFit = true;
fitStyle.BorderBottom = BorderStyle.Thin; fitStyle.BorderLeft = BorderStyle.Thin; fitStyle.BorderTop = BorderStyle.Thin; fitStyle.BorderRight = BorderStyle.Thin;
IFont fitFont = wb.CreateFont();
fitFont.FontHeight = 14 * 20;
fitFont.FontName = "宋体";
fitStyle.SetFont(fitFont);
#endregion
#region 数字左对齐
ICellStyle numberStyle = wb.CreateCellStyle();
numberStyle.ShrinkToFit = true;
numberStyle.BorderBottom = BorderStyle.Thin; numberStyle.BorderLeft = BorderStyle.Thin; numberStyle.BorderTop = BorderStyle.Thin; numberStyle.BorderRight = BorderStyle.Thin;
numberStyle.Alignment = HorizontalAlignment.Right;
//numberStyle.DataFormat= HSSFDataFormat.GetBuiltinFormat("0");
IFont numberFont = wb.CreateFont();
numberFont.FontHeight = 14 * 20;
numberFont.FontName = "宋体";
numberStyle.SetFont(numberFont);
#endregion
#region 红色数字
ICellStyle warningStyle = wb.CreateCellStyle();
warningStyle.ShrinkToFit = true;
warningStyle.BorderBottom = BorderStyle.Thin; numberStyle.BorderLeft = BorderStyle.Thin; numberStyle.BorderTop = BorderStyle.Thin; numberStyle.BorderRight = BorderStyle.Thin;
IFont warningFont = wb.CreateFont();
warningFont.FontHeight = 14 * 20;
warningFont.FontName = "宋体";
warningFont.Color = HSSFColor.Red.Index;
warningStyle.SetFont(warningFont);
#endregion
#region 生产类型为返工时候，值为"灌"，设置样式
ICellStyle guanStyle = wb.CreateCellStyle();
guanStyle.Alignment = HorizontalAlignment.Center;
guanStyle.ShrinkToFit = true;
IFont guanFont = wb.CreateFont();
guanFont.Color = (short)FontColor.Red;
guanFont.FontName = "宋体";
guanFont.FontHeight = 15 * 20;
guanStyle.SetFont(guanFont);
#endregion
#region 标题样式
IFont titleFont = wb.CreateFont();
titleFont.FontName = "宋体";
titleFont.Boldweight = (short)FontBoldWeight.Bold;
titleFont.FontHeight = 24 * 20; //和行高一样？ 也是20倍
ICellStyle titleStyle = wb.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.Center;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
titleStyle.SetFont(titleFont);
#endregion
IDataFormat format = wb.CreateDataFormat();
//cellsylenumber.DataFormat = format.GetFormat("0.00");
//获取不同的类型
var countGroup = lists.Where(m => m.FType != null).GroupBy(m => m.FType).Select(t => new { FType = t.Key, Count = t.Count() });
foreach (var item in countGroup)
{
var typedlists = lists.Where(n => n.FType == item.FType).ToList();
string previousBatch = string.Empty;
double batchQuantity = 0;
//string prevoousProductionModel = string.Empty;
ISheet sheet = wb.CreateSheet(item.FType);
sheet.ForceFormulaRecalculation = true;
sheet.SetColumnWidth(0, 20);
//6.设置列宽 SetColumnWidth(列的索引号从0开始, N * 256) 第二个参数的单位是1/256个字符宽度。例：将第四列宽度设置为了30个字符
//7.设置行高 Height的单位是1/20个点。例：设置高度为50个点 row0.Height = 50 * 20;
sheet.SetColumnWidth(0, (int)19 * 256);
sheet.SetColumnWidth(1, (int)14 * 256);
sheet.SetColumnWidth(2, (int)11.5 * 256);
sheet.SetColumnWidth(3, (int)4 * 256);
sheet.SetColumnWidth(4, (int)13 * 256);
sheet.SetColumnWidth(5, (int)25.35 * 256);
sheet.SetColumnWidth(6, (int)6 * 256);
sheet.SetColumnWidth(7, (int)19.49 * 256);
sheet.SetColumnWidth(8, (int)18.2 * 256);
sheet.SetColumnWidth(9, (int)5.35 * 256);
sheet.SetColumnWidth(10, (int)9.92 * 256);
sheet.SetColumnWidth(11, (int)6 * 256);
sheet.SetColumnWidth(12, (int)7 * 256);
sheet.SetColumnWidth(13, (int)18 * 256); //灌和做货
sheet.SetColumnWidth(14, 8 * 256); //差额
sheet.SetColumnWidth(15, 0 * 256); //第几次做货
sheet.SetColumnWidth(16, 15 * 256); //订单号
sheet.SetColumnWidth(17, 40 * 256); //唯一值
sheet.SetColumnWidth(18, 14 * 256); //安全编号
sheet.SetColumnWidth(19, 10 * 256); //样油重量
#region Logo
ExportImgToExcel(sheet, (HSSFWorkbook)wb);
#endregion
#region 标题
IRow row0 = sheet.CreateRow(0);
row0.Height = 30 * 20;
//CellRangeAddress region = new CellRangeAddress(0, 0, 0, 13);//CellRangeAddress rg = new CellRangeAddress(j + 2, j + 2, 8, 11);
//sheet.AddMergedRegion(region);
ICell cell = row0.CreateCell(5);
cell.SetCellValue(typedlists[0].FProductionDate.ToString("yyyy年MM月dd日") + typedlists[0].FType + "生产任务清单");
/*第一个参数：从第几行开始合并 第二个参数：到第几行结束合并 第三个参数：从第几列开始合并 第四个参数：到第几列结束合并 */
cell.CellStyle = titleStyle;
#endregion
#region 表头
IRow row1 = sheet.CreateRow(1);
row1.Height = (short)20.5 * 20;
var A = row1.CreateCell(0); A.SetCellValue("产品型号"); A.CellStyle = fitStyle;
var B = row1.CreateCell(1); B.SetCellValue("产品批号"); B.CellStyle = fitStyle;
var C = row1.CreateCell(2); C.SetCellValue("生产数量"); C.CellStyle = fitStyle;
var D = row1.CreateCell(3); D.SetCellValue("小料"); D.CellStyle = fitStyle;
var E = row1.CreateCell(4); E.SetCellValue("包装桶数"); E.CellStyle = fitStyle;
var F = row1.CreateCell(5); F.SetCellValue("包装桶"); F.CellStyle = fitStyle;
var G = row1.CreateCell(6); G.SetCellValue("客户代码"); G.CellStyle = fitStyle;
var H = row1.CreateCell(7); H.SetCellValue("标签型号"); H.CellStyle = fitStyle;
var I = row1.CreateCell(8); I.SetCellValue("备注"); I.CellStyle = fitStyle;
var J = row1.CreateCell(9); J.SetCellValue("留样"); J.CellStyle = fitStyle;
var K = row1.CreateCell(10); K.SetCellValue("合格证"); K.CellStyle = fitStyle;
var L = row1.CreateCell(11); L.SetCellValue("接收人"); L.CellStyle = fitStyle;
var M = row1.CreateCell(12); M.SetCellValue("残液"); M.CellStyle = fitStyle;
var N = row1.CreateCell(13); N.SetCellValue(""); N.CellStyle = guanStyle;//灌不灌
var O = row1.CreateCell(14); O.SetCellValue("差额"); O.CellStyle = fitStyle;
var P = row1.CreateCell(15); P.SetCellValue("生产次数"); P.CellStyle = numberStyle;
var Q = row1.CreateCell(16); Q.SetCellValue("订单号"); Q.CellStyle = fitStyle;
var R = row1.CreateCell(17); R.SetCellValue("唯一值"); R.CellStyle = fitStyle;
var S = row1.CreateCell(18); S.SetCellValue("安全编号"); S.CellStyle = fitStyle;
var T = row1.CreateCell(19); T.SetCellValue("样油重量(g)"); T.CellStyle = fitStyle;
#endregion
for (int j = 0; j < typedlists.Count; j++)
{
IRow row = sheet.CreateRow(j + 2);
row.Height = (short)20.5 * 20;
string currentBatch = typedlists[j].FBatchNo;
var A0 = row.CreateCell(0); var B0 = row.CreateCell(1); var C0 = row.CreateCell(2); var D0 = row.CreateCell(3); var M0 = row.CreateCell(12); var O0 = row.CreateCell(14);
if (currentBatch != previousBatch)
{
batchQuantity = Convert.ToDouble(typedlists[j].FQuantity) - Convert.ToDouble(typedlists[j].RowQuantity) + Convert.ToDouble(typedlists[j].FResidue);
A0.SetCellValue(typedlists[j].FitemName);
B0.SetCellValue(currentBatch);
C0.SetCellValue(Convert.ToDouble(typedlists[j].FQuantity));
D0.SetCellValue(typedlists[j].FHasSmallMaterial);
M0.SetCellValue(Convert.ToDouble(typedlists[j].FResidue.ToString("0.0")));
O0.CellStyle = fitStyle;
}
else
{
batchQuantity -= Convert.ToDouble(typedlists[j].RowQuantity);
O0.CellStyle = warningStyle;
}
O0.SetCellValue(batchQuantity);
A0.CellStyle = fitStyle; B0.CellStyle = fitStyle; C0.CellStyle = numberStyle; M0.CellStyle = numberStyle; D0.CellStyle = fitStyle;
var E0 = row.CreateCell(4); E0.SetCellValue(typedlists[j].FPackage); E0.CellStyle = fitStyle;
var F0 = row.CreateCell(5); F0.SetCellValue(typedlists[j].FBucketName); F0.CellStyle = fitStyle;
var G0 = row.CreateCell(6); G0.SetCellValue(typedlists[j].FOrgID); G0.CellStyle = fitStyle;
var H0 = row.CreateCell(7); H0.SetCellValue(typedlists[j].FLabel); H0.CellStyle = fitStyle;
#region 备注字体设置
var I0 = row.CreateCell(8);
string value = typedlists[j].FNote;
I0.SetCellValue(value);
ICellStyle noteStyle = wb.CreateCellStyle();
noteStyle.BorderBottom = BorderStyle.Thin; noteStyle.BorderLeft = BorderStyle.Thin; noteStyle.BorderTop = BorderStyle.Thin;
IFont noteFont = wb.CreateFont();
noteFont.FontName = "宋体";
int dataLength = UniversalFunction.GetLength(value);
if (dataLength >= 50)
{
noteFont.FontHeight = 7 * 20;
}
else if (dataLength >= 40)
{
noteFont.FontHeight = 8.5 * 20;
}
else if (dataLength >= 30)
{
noteFont.FontHeight = 10 * 20;
}
else if (dataLength >= 25)
{
noteFont.FontHeight = 12 * 20;
}
else
{
noteFont.FontHeight = 14 * 20;
}
noteStyle.SetFont(noteFont);
I0.CellStyle = noteStyle;
#endregion
#region 留样开始3列不赋值(赋值空都会有个空格)
ICellStyle blankStyle = wb.CreateCellStyle();
blankStyle.BorderLeft = BorderStyle.Thin; blankStyle.BorderTop = BorderStyle.Thin; blankStyle.BorderBottom = BorderStyle.Thin;
var J0 = row.CreateCell(9); J0.CellStyle = blankStyle;
var K0 = row.CreateCell(10); K0.CellStyle = blankStyle;
var L0 = row.CreateCell(11); L0.CellStyle = blankStyle;
#endregion
string productionNumber = typedlists[j].ProductionNumber > 0 && typedlists[j].ProductionNumber <= 3 ? $"第{typedlists[j].ProductionNumber}次生产" : "";
string guan = typedlists[j].ProductionType == "返工" ? "灌" : "";
var N0 = row.CreateCell(13); N0.SetCellValue(guan + " " + productionNumber); N0.CellStyle = guanStyle;//灌不灌
//var P0 = row.CreateCell(15); P0.SetCellValue(); P0.CellStyle = fitStyle; // 第多少次生产和 灌写入同一个单元格，本单元格已设置宽度为0，
var Q0 = row.CreateCell(16); Q0.SetCellValue(typedlists[j].FBillNo); Q0.CellStyle = fitStyle;
var R0 = row.CreateCell(17); R0.SetCellValue(UniversalFunction.ToHexString(typedlists[j].RowHashValue)); R0.CellStyle = fitStyle;
var S0 = row.CreateCell(18); S0.SetCellValue(typedlists[j].SafeCode); S0.CellStyle = fitStyle;
var T0 = row.CreateCell(19); T0.SetCellValue(Convert.ToInt32(typedlists[j].PaintSampleTotal)); T0.CellStyle = numberStyle;
previousBatch = currentBatch.Length != 0 ? currentBatch : previousBatch;
}
}
FileStream fs = new FileStream(fName, FileMode.Create); //新建才不会报错
wb.Write(fs); //会自动关闭流文件 //fs.Flush();
fs.Close();
}
/// <summary>
/// 填充数据
/// </summary>
/// <param name="table"></param>
/// <param name="subject"></param>
/// <param name="sheetName"></param>
/// <param name="columnName"></param>
/// <param name="columnTitle"></param>
/// <returns></returns>
public static HSSFWorkbook GenerateData(DataTable table, string subject, string sheetName, string[] columnName, string[] columnTitle
, string[] roles = null, string[] photoStatus = null, string[] hospitals = null, string[] creators = null)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet(sheetName);
if (subject == "客资导入模板")
{
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 4, 4);
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(roles);
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
//CellRangeAddressList regions1 = new CellRangeAddressList(1, 65535, 5, 5);
//DVConstraint constraint1 = DVConstraint.CreateExplicitListConstraint(hospitals);
//HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions1, constraint1);
//sheet.AddValidationData(dataValidate1);
CreateDropDownListForExcel(sheet, hospitals, 1, 500, 6);
CellRangeAddressList regions4 = new CellRangeAddressList(1, 65535, 12, 12);
DVConstraint constraint4 = DVConstraint.CreateExplicitListConstraint(photoStatus);
HSSFDataValidation dataValidate4 = new HSSFDataValidation(regions4, constraint4);
sheet.AddValidationData(dataValidate4);
CellRangeAddressList regions2 = new CellRangeAddressList(1, 65535, 13, 13);
DVConstraint constraint2 = DVConstraint.CreateExplicitListConstraint(new string[] { "已领取", "未领取" });
HSSFDataValidation dataValidate2 = new HSSFDataValidation(regions2, constraint2);
sheet.AddValidationData(dataValidate2);
CellRangeAddressList regions3 = new CellRangeAddressList(1, 65535, 14, 14);
DVConstraint constraint3 = DVConstraint.CreateExplicitListConstraint(new string[] { "正常", "黑名单" });
HSSFDataValidation dataValidate3 = new HSSFDataValidation(regions3, constraint3);
sheet.AddValidationData(dataValidate3);
//CellRangeAddressList regions5 = new CellRangeAddressList(1, 65535, 8, 8);
//DVConstraint constraint5 = DVConstraint.CreateExplicitListConstraint(creators);
//HSSFDataValidation dataValidate5 = new HSSFDataValidation(regions5, constraint5);
//sheet.AddValidationData(dataValidate5);
CreateDropDownListForExcel(sheet, creators, 1, 500, 9);
CellRangeAddressList regions5 = new CellRangeAddressList(1, 65535, 15, 15);
DVConstraint constraint5 = DVConstraint.CreateExplicitListConstraint(new string[] { "是", "否" });
HSSFDataValidation dataValidate5 = new HSSFDataValidation(regions5, constraint5);
sheet.AddValidationData(dataValidate5);
CellRangeAddressList regions6 = new CellRangeAddressList(1, 65535, 2, 2);
DVConstraint constraint6 = DVConstraint.CreateExplicitListConstraint(new string[] { "备孕中", "孕妈妈", "宝妈妈" });
HSSFDataValidation dataValidate6 = new HSSFDataValidation(regions6, constraint6);
sheet.AddValidationData(dataValidate6);
}
ICellStyle dateStyle = hssfworkbook.CreateCellStyle();
IDataFormat format = hssfworkbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
#region 取得列宽
int[] colWidth = new int[columnName.Length];
for (int i = 0; i < columnName.Length; i++)
{
colWidth[i] = Encoding.GetEncoding(936).GetBytes(columnTitle[i]).Length;
}
if (table != null && table.Rows.Count > 0)
{
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < columnName.Length; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(table.Rows[i][columnName[j]].ToString()).Length;
if (intTemp > colWidth[j])
{
colWidth[j] = intTemp;
}
}
}
}
#endregion
int rowIndex = 0;
if (table == null || table.Rows.Count == 0)
{
IRow headerRow;
headerRow = sheet.CreateRow(0);
ICellStyle headStyle = hssfworkbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < columnName.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(columnTitle[i]);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(i, (colWidth[i] + 1) * 256 + 2 * 256);
}
}
else
{
foreach (DataRow row in table.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = hssfworkbook.CreateSheet(sheetName + ((int)rowIndex / 65535).ToString());
}
#region 列头及样式
{
IRow headerRow;
headerRow = sheet.CreateRow(0);
ICellStyle headStyle = hssfworkbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < columnName.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(columnTitle[i]);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
if (columnName[i].Contains("ItemPictureURL"))
{
sheet.SetColumnWidth(i, 60 * 256);
}
else
{
sheet.SetColumnWidth(i, (colWidth[i] + 1) * 256 + 2 * 256);
}
}
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充数据
IRow dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < columnName.Length; i++)
{
ICell newCell = dataRow.CreateCell(i);
string drValue = row[columnName[i]].ToString();
#region 赋值
switch (table.Columns[columnName[i]].DataType.ToString())
{
case "System.String": //字符串类型
if (drValue.ToUpper() == "TRUE")
{
newCell.SetCellValue("是");
}
else if (drValue.ToUpper() == "FALSE")
{
newCell.SetCellValue("否");
}
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
if (string.IsNullOrWhiteSpace(drValue))
{
newCell.SetCellValue("");
break;
}
DateTime dateV;
bool flag = DateTime.TryParse(drValue, out dateV);
if (flag)
{
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
}
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
if (boolV)
{
newCell.SetCellValue("是");
}
else
{
newCell.SetCellValue("否");
}
break;
case "System.Int16": //整型
case "System.Int32":
if (string.IsNullOrWhiteSpace(drValue))
{
newCell.SetCellValue("");
break;
}
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Int64":
newCell.SetCellValue(drValue);
break;
case "System.Byte":
newCell.SetCellValue(drValue);
break;
case "System.Decimal": //浮点型
case "System.Double":
case "System.Single":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue(drValue);
break;
}
#endregion
}
#endregion
rowIndex++;
}
}
return(hssfworkbook);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="strHeaderText">Excel表头文本（例如：车辆列表）</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = 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
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = 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;
}
}
#endregion
rowIndex++;
}
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 Export(DataTable dtSource)
{
XSSFWorkbook workbook = new XSSFWorkbook();
//HSSFWorkbook workbook = new HSSFWorkbook();
//HSSFSheet sheet = workbook.CreateSheet();
ISheet sheet = workbook.CreateSheet();
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(0);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
//解决导出excel表中的某个单元格数据过大，显示单元格最大列宽255错误
int colWidth = (arrColWidth[column.Ordinal] + 1) * 256;
if (colWidth < 255 * 256)
{
sheet.SetColumnWidth(column.Ordinal, colWidth < 3000 ? 3000 : colWidth);
}
else
{
sheet.SetColumnWidth(column.Ordinal, 6000);
}
//sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
workbook.Close();
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public static bool ExportToExcel(string filepath, string sheetname, string tablename, object[] m, params string[] colname)
{
//SaveFileDialog sdfExport = new SaveFileDialog();
//sdfExport.Filter = "Excel文件|*.xls";
//if (sdfExport.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
//{
// return false;
//}
string filename = filepath;
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetname);
object mo = new object();
try
{
mo = m[0];
}
catch
{
throw new Exception("数据为空，请检查数据！");
}
Type tp = mo.GetType();
IRow rowheader = sheet.CreateRow(0);
rowheader.Height = 25 * 20;
ICell cell5 = rowheader.CreateCell(0);
cell5.SetCellValue(tablename);
ICellStyle style5 = workbook.CreateCellStyle();
style5.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
IFont font5 = workbook.CreateFont();// 定义字体
font5.FontHeight = 20 * 20;
font5.FontName = "华文新魏";
style5.SetFont(font5);
cell5.CellStyle = style5;
NPOI.SS.Util.CellRangeAddress cellRangeAddress = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, tp.GetProperties().Count() - colname.Count() - 1);// ' 指定单元格合并的起止位置
sheet.AddMergedRegion(cellRangeAddress);
rowheader = sheet.CreateRow(1);
int i = 0;//列号
foreach (System.Reflection.PropertyInfo p in tp.GetProperties())
{
//object value = new object();
//value = p.GetValue(mo, null);
bool nothave = true;
foreach (string s in colname)
{
if (p.Name == s)
{
nothave = false;
break;
}
}
if (nothave)
{
rowheader.CreateCell(i, CellType.STRING).SetCellValue(p.Name);
i = i + 1;
}
}
for (i = 0; i < m.Length; i++)
{
object o = new object();
o = m[i];
tp = o.GetType();
IRow row = sheet.CreateRow(i + 2);
int j = 0;
foreach (System.Reflection.PropertyInfo p in tp.GetProperties())
{
object value = new object();
value = p.GetValue(o, null);
if (value != null)
{
bool nothave = true;
foreach (string s in colname)
{
if (p.Name == s)
{
nothave = false;
j--;
break;
}
}
if (nothave)
{
switch (value.GetType().ToString())
{
case "System.String":
row.CreateCell(j, CellType.STRING).SetCellValue(value.ToString());
break;
case "System.Decimal":
row.CreateCell(j, CellType.NUMERIC).SetCellValue(Convert.ToDouble(value));
break;
case "System.Guid":
row.CreateCell(j, CellType.STRING).SetCellValue(value.ToString());
break;
case "System.Int32":
row.CreateCell(j, CellType.NUMERIC).SetCellValue(Convert.ToInt32(value));
break;
case "System.Int64":
row.CreateCell(j, CellType.NUMERIC).SetCellValue(Convert.ToInt32(value));
break;
case "System.Int16":
row.CreateCell(j, CellType.NUMERIC).SetCellValue(Convert.ToInt32(value));
break;
case "System.Boolean":
row.CreateCell(j, CellType.STRING).SetCellValue(Convert.ToBoolean(value));
break;
case "System.DateTime":
DateTime d;
d = Convert.ToDateTime(value);
ICellStyle styledate = workbook.CreateCellStyle();
IDataFormat Format = workbook.CreateDataFormat();
styledate.DataFormat = Format.GetFormat("yyyy\"年\"m\"月\"d\"日\"");
ICell cellInDate = row.CreateCell(j, CellType.NUMERIC);
cellInDate.CellStyle = styledate;
cellInDate.SetCellValue(d);
break;
default:
row.CreateCell(j, CellType.STRING).SetCellValue(value.ToString());
break;
}
}
}
j = j + 1;
}
}
try
{
using (Stream stream = File.OpenWrite(filename))
{
workbook.Write(stream);
}
return(true);
}
catch
{
return(false);
}
}
//public void OutputBinayStream(System.IO.FileInfo file, HttpContext context)
//{
// #region Output Binary Stream
// context.Response.Clear();
// context.Response.Charset = "GB2312";
// context.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpContext.Current.Server.UrlEncode(file.Name));
// context.Response.AddHeader("Content-Length", file.Length.ToString());
// context.Response.ContentType = "application/ms-excel";
// context.Response.WriteFile(file.FullName);
// context.Response.End();
// #endregion
//}
#endregion
#region 动态转换成excel
public static bool ToExcelDynamic(string savePath, string SheetName, string HeadName, DataTable table, Dictionary <string, string> excelDataMap)
{
try
{
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);
#endregion
#region 日期行样式
ICellStyle dateStyle = wk.CreateCellStyle();
IDataFormat format = wk.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
#endregion
//合并标题行
/// <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; //开始列索引
foreach (var item in excelDataMap)
{
string columsName = item.Value;
tb.SetColumnWidth(cellIndex, 16 * 256); //行宽8个汉字
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.CellStyle = headStyle;
if (columsName == "DEPT_NAME")
{
cell.SetCellValue("单位名称");
}
else
{
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; //开始列索引
foreach (var item in excelDataMap)
{
string columsName = item.Value;
ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.SetCellValue(table.Rows[i][columsName].ToString()); //循环往第二行的单元格中添加数据
cell.CellStyle = stylerow;
cellIndex++;
}
}
using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
{
wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public static bool ToExcelDate(string savePath, string SheetName, string HeadName, IList listJob, string className)
{
try
{
DataTable table = ToDataTable(listJob, className);
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);
#endregion
#region 日期行样式
ICellStyle dateStyle = wk.CreateCellStyle();
IDataFormat format = wk.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#endregion
Dictionary <string, string> excelDataMap = ReadXml(className, false);
//合并标题行
/// <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"));//循环往第二行的单元格中添加数据
}
cell.CellStyle = dateStyle;
}
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);
}
}