/// <summary>
/// Set the font for this style
/// </summary>
/// <param name="font">a font object Created or retreived from the HSSFWorkbook object</param>
public void SetFont(NPOI.SS.UserModel.IFont font)
{
_format.IsIndentNotParentFont = (true);
short fontindex = font.Index;
_format.FontIndex = (fontindex);
}
private void CopyFontStyle(IWorkbook wb, XSSFCell oldCell, XSSFCellStyle newCellStyle)
{
NPOI.SS.UserModel.IFont font = destinationWb.CreateFont();
NPOI.SS.UserModel.IFont sourceFont = oldCell.CellStyle.GetFont(wb);
font.FontName = sourceFont.FontName;
font.FontHeightInPoints = sourceFont.FontHeightInPoints;
font.Boldweight = sourceFont.Boldweight;
newCellStyle.SetFont(font);
}
private void ExportDataItem(System.Data.DataTable TotalDT)
{
string filename = "成本调整单" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename)));
HttpContext.Current.Response.Clear();
using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("成本调整单查询.xls")))
{
IWorkbook wk = new HSSFWorkbook(fs);
ISheet sheet0 = wk.GetSheetAt(0);
#region 写入数据
for (int i = 0; i < TotalDT.Rows.Count; i++)
{
IRow row = sheet0.CreateRow(i + 3);
row.CreateCell(0).SetCellValue(Convert.ToString(i + 1));//序号
row.CreateCell(1).SetCellValue(TotalDT.Rows[i]["SI_MARID"].ToString());
row.CreateCell(2).SetCellValue(TotalDT.Rows[i]["MNAME"].ToString());
row.CreateCell(3).SetCellValue(TotalDT.Rows[i]["CAIZHI"].ToString());
row.CreateCell(4).SetCellValue(TotalDT.Rows[i]["GUIGE"].ToString());
row.CreateCell(5).SetCellValue(TotalDT.Rows[i]["SI_YEAR"].ToString());
row.CreateCell(6).SetCellValue(TotalDT.Rows[i]["SI_PERIOD"].ToString());
row.CreateCell(7).SetCellValue(TotalDT.Rows[i]["DIFF"].ToString());
NPOI.SS.UserModel.IFont font1 = wk.CreateFont();
font1.FontName = "仿宋"; //字体
font1.FontHeightInPoints = 11; //字号
ICellStyle cells = wk.CreateCellStyle();
cells.SetFont(font1);
cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
for (int j = 0; j <= 7; j++)
{
row.Cells[j].CellStyle = cells;
}
}
#endregion
for (int i = 0; i <= 7; i++)
{
sheet0.AutoSizeColumn(i);
}
sheet0.ForceFormulaRecalculation = true;
MemoryStream file = new MemoryStream();
wk.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
}
/// <summary>
/// 写入DataTable到Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="excelFile"></param>
public virtual void writeDataTableToExcel(DataTable dt, string excelFile)
{
//Excel数据
MemoryStream memoryStream = new MemoryStream();
//创建Workbook
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
#region 设置Excel样式
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle();
cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.WrapText = true;
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle();
cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.WrapText = true;
//创建设置字体对象(内容字体)
NPOI.SS.UserModel.IFont fontA = workbook.CreateFont();
fontA.FontHeightInPoints = 16;//设置字体大小
fontA.FontName = "宋体";
cellStyleA.SetFont(fontA);
//创建设置字体对象(标题字体)
NPOI.SS.UserModel.IFont fontB = workbook.CreateFont();
fontB.FontHeightInPoints = 16;//设置字体大小
fontB.FontName = "宋体";
fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellStyleB.SetFont(fontB);
#endregion
//写入基本数据
writeSheet(workbook, cellStyleA, cellStyleB, dt);
#region 输出文件
//输出到流
workbook.Write(memoryStream);
//写Excel文件
File.WriteAllBytes(excelFile, memoryStream.ToArray());
#endregion
}
private static ICellStyle GetCellStyleHead(IWorkbook hssfworkbook)
{
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
CellStyleBasic(cellStyle);
cellStyle.WrapText = true;//表头自动换行
NPOI.SS.UserModel.IFont font = hssfworkbook.CreateFont();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellStyle.SetFont(font);//字体加粗
return(cellStyle);
}
private void CopyCellStyle(IWorkbook toBook, IWorkbook fromBook, ICell toCell, ICell fromCell)
{
ICellStyle style = toBook.CreateCellStyle();
IFont toFont = toBook.CreateFont();
CopyCellStyle(fromCell.CellStyle, style);
IFont fromFont = fromCell.CellStyle.GetFont(fromBook);
CopyFont(toFont, fromFont);
//style.SetFont(toFont);
toCell.CellStyle = style;
}
private static void CopyFont(IFont toFont, IFont fromFont)
{
toFont.Color = fromFont.Color;
toFont.FontHeightInPoints = fromFont.FontHeightInPoints;
toFont.IsBold = fromFont.IsBold;
toFont.IsItalic = fromFont.IsItalic;
toFont.Underline = fromFont.Underline;
//toFont.Boldweight = fromFont.Boldweight;
//toFont.Charset = fromFont.Charset;
//toFont.FontHeight = fromFont.FontHeight;
toFont.FontName = fromFont.FontName;
//toFont.IsStrikeout = fromFont.IsStrikeout;
//toFont.TypeOffset = fromFont.TypeOffset;
}
/// <summary>
/// 设置字体颜色大小
/// </summary>
public static Stream ApplyStyleToFile(Stream inputStream, string fontName, short fontSize)
{
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
ICellStyle style = workbook.CreateCellStyle();
ICellStyle style1 = workbook.CreateCellStyle();
IFont cellFont = workbook.CreateFont();
cellFont.FontHeightInPoints = 10;
cellFont.FontName = "黑体";
IFont font = workbook.CreateFont();
font.FontHeightInPoints = fontSize;
font.FontName = fontName;
font.Boldweight = short.MaxValue;
style.SetFont(font);
style.Alignment = HorizontalAlignment.Center;
style1.SetFont(cellFont);
style1.Alignment = HorizontalAlignment.Center;
MemoryStream ms = new MemoryStream();
int i;
for (i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheets = workbook.GetSheetAt(0);
for (int k = sheets.FirstRowNum; k <= sheets.LastRowNum; k++)
{
sheets.AutoSizeColumn(k);
IRow row = sheets.GetRow(k);
for (int l = row.FirstCellNum; l < row.LastCellNum; l++)
{
if (k == 0)
{
ICell cell = row.GetCell(l);
cell.CellStyle = style;
}
else
{
ICell cell = row.GetCell(l);
cell.CellStyle = style1;
}
}
}
}
workbook.Write(ms);
ms.Flush();
return(ms);
}
private void ExportDataItem(DataTable dt)
{
string filename = "售后服务处理" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename)));
HttpContext.Current.Response.Clear();
//1.读取Excel到FileStream
using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("售后服务.xls")))
{
IWorkbook wk = new HSSFWorkbook(fs);
ISheet sheet0 = wk.GetSheetAt(0);
NPOI.SS.UserModel.IFont font1 = wk.CreateFont();
font1.FontName = "仿宋"; //字体
font1.FontHeightInPoints = 9; //字号
ICellStyle cells = wk.CreateCellStyle();
cells.SetFont(font1);
cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
for (int i = 0, length = dt.Rows.Count; i < length; i++)
{
IRow row = sheet0.CreateRow(i + 2);
row.CreateCell(0).SetCellValue(i + 1);
row.Cells[0].CellStyle = cells;
for (int j = 0, m = dt.Columns.Count; j < m; j++)
{
row.CreateCell(j + 1).SetCellValue(dt.Rows[i][j].ToString());
row.Cells[j + 1].CellStyle = cells;
}
}
MemoryStream file = new MemoryStream();
wk.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
}
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 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; }
}
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 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);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string passaord = null)
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
if (string.IsNullOrEmpty(passaord) == false)
{
sheet.ProtectSheet(passaord);
}
#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
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
NPOI.SS.UserModel.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();
if (string.IsNullOrEmpty(passaord) == false)
{
sheet.ProtectSheet(passaord);
}
}
#region 表头及样式
{
if (string.IsNullOrEmpty(strHeaderText) == false)
{
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.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));
rowIndex += 1;
}
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.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);
}
//headerRow.Dispose();
rowIndex += 1;
}
#endregion
}
#endregion
#region 填充内容
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
NPOI.SS.UserModel.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;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream xlsx
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">文件名</param>
public static MemoryStream ExportXlsx(IEnumerable <DataTable> dataTables, string strHeaderText, string passaord = null)
{
XSSFWorkbook workbook = new XSSFWorkbook();
int i = 0;
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
NPOI.SS.UserModel.IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 右击文件 属性信息
{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "NPOI";
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "文件作者信息"; //填加xlsx文件作者信息
// si.ApplicationName = "创建程序信息"; //填加xlsx文件创建程序信息
// si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
// si.Comments = "作者信息"; //填加xls文件作者信息
// si.Title = "标题信息"; //填加xls文件标题信息
// si.Subject = "主题信息";//填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.GetProperties().CustomProperties.AddProperty("Company", "NPOI");
// if (!workbook.GetProperties().CustomProperties.Contains("Company"))
// workbook.GetProperties().CustomProperties.AddProperty("Company", dsi.Company);
}
#endregion
foreach (DataTable dt in dataTables)
{
string sheetName = string.IsNullOrEmpty(dt.TableName)
? "Sheet " + (++i).ToString()
: dt.TableName;
ISheet sheet = workbook.CreateSheet(sheetName);
if (string.IsNullOrEmpty(passaord) == false)
{
sheet.ProtectSheet(passaord);
}
int rowIndex = 0;
#region 表头及样式
{
if (string.IsNullOrEmpty(strHeaderText) == false)
{
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.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, dt.Columns.Count - 1));
rowIndex += 1;
}
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
//取得列宽
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 k = 0; k < dt.Rows.Count; k++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[k][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
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();
rowIndex += 1;
}
#endregion
//#region 表头
//for (int j = 0; j < dt.Columns.Count; j++)
//{
// string columnName = string.IsNullOrEmpty(dt.Columns[j].ColumnName)
// ? "Column " + j.ToString()
// : dt.Columns[j].ColumnName;
// headerRow.CreateCell(j).SetCellValue(columnName);
//}
//#endregion
#region 内容
for (int a = 0; a < dt.Rows.Count; a++)
{
DataRow dr = dt.Rows[a];
IRow row = sheet.CreateRow(a + rowIndex);
for (int b = 0; b < dt.Columns.Count; b++)
{
row.CreateCell(b).SetCellValue(dr[b] != DBNull.Value ? dr[b].ToString() : string.Empty);
DataColumn dc = dt.Columns[b];
NPOI.SS.UserModel.ICell newCell = row.CreateCell(dc.Ordinal);
string drValue = dr[b].ToString();
switch (dc.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
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
// ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public string ExportToExcel(string Name, DataTable dt)
{
try
{
if (dt.Rows.Count > 0)
{
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 = HorizontalAlignment.Center;
HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
HeadercellStyle.FillPattern = FillPattern.SolidForeground;
HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerfont.FontHeightInPoints = 12;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn dc in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(dc.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 = 0;
foreach (DataRow dr in dt.Rows)
{
int iCellIndex = 0;
IRow irow = sheet.CreateRow(iRowIndex + 1);
for (int i = 0; i < dt.Columns.Count; i++)
{
string strsj = string.Empty;
if (dr[i] != null)
{
strsj = dr[i].ToString();
}
ICell cell = irow.CreateCell(iCellIndex);
cell.SetCellValue(strsj);
cell.CellStyle = cellStyle;
iCellIndex++;
}
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(Name + "_导出文件_" + DateTime.Now.Ticks + ".xls", Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
workbook = null;
ms.Close();
ms.Dispose();
curContext.Response.End();
}
}
return("");
}
catch (Exception ex)
{
return("导出失败！");
}
}
private void ExportDataItem(DataTable dt)
{
DataRow dr = dt.Rows[0];
string filename = "培训讲师档案--" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename)));
HttpContext.Current.Response.Clear();
//1.读取Excel到FileStream
using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("培训讲师档案.xls")))
{
IWorkbook wk = new HSSFWorkbook(fs);
ISheet sheet0 = wk.GetSheetAt(0);
for (int i = 0, length = dt.Rows.Count; i < length; i++)
{
IRow row = sheet0.CreateRow(i + 1);
row.HeightInPoints = 20;
row.CreateCell(0).SetCellValue(Convert.ToString(i + 1));//序号
row.CreateCell(1).SetCellValue(dt.Rows[i]["PX_ZJR"].ToString());
row.CreateCell(2).SetCellValue(dt.Rows[i]["PX_BM"].ToString());
row.CreateCell(3).SetCellValue(dt.Rows[i]["PX_BH"].ToString());
row.CreateCell(4).SetCellValue(dt.Rows[i]["PX_FS"].ToString() == "n" ? "内部" : "外部");
row.CreateCell(5).SetCellValue(dt.Rows[i]["PX_XMMC"].ToString());
row.CreateCell(6).SetCellValue(dt.Rows[i]["PX_SJSJ"].ToString());
row.CreateCell(7).SetCellValue(dt.Rows[i]["PX_SJDD"].ToString());
row.CreateCell(8).SetCellValue(dt.Rows[i]["PX_ZJR"].ToString());
row.CreateCell(9).SetCellValue(dt.Rows[i]["PX_SJXS"].ToString());
row.CreateCell(10).SetCellValue(dt.Rows[i]["PX_ZJRDF"].ToString());
row.CreateCell(11).SetCellValue(dt.Rows[i]["PX_SJBZ"].ToString());
NPOI.SS.UserModel.IFont font1 = wk.CreateFont();
font1.FontName = "仿宋"; //字体
font1.FontHeightInPoints = 9; //字号
ICellStyle cells = wk.CreateCellStyle();
cells.SetFont(font1);
cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
for (int j = 0; j <= 11; j++)
{
row.Cells[j].CellStyle = cells;
}
}
NPOI.SS.UserModel.IFont font2 = wk.CreateFont();
font2.FontName = "仿宋"; //字体
font2.FontHeightInPoints = 9; //字号
ICellStyle cells2 = wk.CreateCellStyle();
cells2.SetFont(font2);
cells2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cells2.VerticalAlignment = VerticalAlignment.CENTER;
//CellRangeAddress range1 = new CellRangeAddress(dt.Rows.Count + 2, dt.Rows.Count + 2, 0, 16);
//sheet0.AddMergedRegion(range1);
//CellRangeAddress range2 = new CellRangeAddress(dt.Rows.Count + 4, dt.Rows.Count + 4, 7, 17);
//sheet0.AddMergedRegion(range2);
MemoryStream file = new MemoryStream();
wk.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
}
// /**
// * Drawing context to measure text
// */
//private static FontRenderContext fontRenderContext = new FontRenderContext(null, true, true);
/**
* Compute width of a column and return the result
*
* @param sheet the sheet to calculate
* @param column 0-based index of the column
* @param useMergedCells whether to use merged cells
* @return the width in pixels
*/
public static double GetColumnWidth(ISheet sheet, int column, bool useMergedCells)
{
//AttributedString str;
//TextLayout layout;
IWorkbook wb = sheet.Workbook;
DataFormatter formatter = new DataFormatter();
IFont defaultFont = wb.GetFontAt((short)0);
//str = new AttributedString((defaultChar));
//copyAttributes(defaultFont, str, 0, 1);
//layout = new TextLayout(str.Iterator, fontRenderContext);
//int defaultCharWidth = (int)layout.Advance;
Font font = IFont2Font(defaultFont);
int defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width;
DummyEvaluator dummyEvaluator = new DummyEvaluator();
double width = -1;
using (Bitmap bmp = new Bitmap(2048, 100))
{
Graphics g = Graphics.FromImage(bmp);
//rows:
bool skipthisrow = false;
for (IEnumerator it = sheet.GetRowEnumerator(); it.MoveNext();)
{
IRow row = (IRow)it.Current;
ICell cell = row.GetCell(column);
if (cell == null)
{
continue;
}
int colspan = 1;
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress region = sheet.GetMergedRegion(i);
if (ContainsCell(region, row.RowNum, column))
{
if (!useMergedCells)
{
// If we're not using merged cells, skip this one and Move on to the next.
//continue rows;
skipthisrow = true;
}
cell = row.GetCell(region.FirstColumn);
colspan = 1 + region.LastColumn - region.FirstColumn;
}
}
if (skipthisrow)
{
continue;
}
ICellStyle style = cell.CellStyle;
NPOI.SS.UserModel.IFont font1 = wb.GetFontAt(style.FontIndex);
CellType cellType = cell.CellType;
// for formula cells we compute the cell width for the cached formula result
if (cellType == CellType.FORMULA)
{
cellType = cell.CachedFormulaResultType;
}
if (cellType == CellType.STRING)
{
IRichTextString rt = cell.RichStringCellValue;
String[] lines = rt.String.Split("\n".ToCharArray());
for (int i = 0; i < lines.Length; i++)
{
String txt = lines[i] + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.Length);
font = IFont2Font(font1);
if (rt.NumFormattingRuns > 0)
{
// TODO: support rich text fragments
}
//layout = new TextLayout(str.Iterator, fontRenderContext);
if (style.Rotation != 0)
{
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is Added by the standard Excel autosize.
*/
double angle = style.Rotation * 2.0 * Math.PI / 360.0;
//AffineTransform trans = new AffineTransform();
//trans.Concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.Concatenate(
//AffineTransform.GetScaleInstance(1, fontHeightMultiple)
//);
SizeF sf = g.MeasureString(txt, font);
double x1 = Math.Abs(sf.Height * Math.Sin(angle));
double x2 = Math.Abs(sf.Width * Math.Cos(angle));
double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
//width = Math.Max(width, ((layout.GetOutline(trans).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
}
else
{
//width = Math.Max(width, ((layout.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
double w = Math.Round(g.MeasureString(txt, font).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
else
{
String sval = null;
if (cellType == CellType.NUMERIC)
{
// Try to Get it formatted to look the same as excel
try
{
sval = formatter.FormatCellValue(cell, dummyEvaluator);
}
catch (Exception)
{
sval = cell.NumericCellValue.ToString("F", CultureInfo.InvariantCulture);
}
}
else if (cellType == CellType.BOOLEAN)
{
sval = cell.BooleanCellValue.ToString().ToUpper();
}
if (sval != null)
{
String txt = sval + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.Length);
//layout = new TextLayout(str.Iterator, fontRenderContext);
if (style.Rotation != 0)
{
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is Added by the standard Excel autosize.
*/
double angle = style.Rotation * 2.0 * Math.PI / 360.0;
//AffineTransform trans = new AffineTransform();
//trans.Concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.Concatenate(
//AffineTransform.GetScaleInstance(1, fontHeightMultiple)
//);
//width = Math.Max(width, ((layout.GetOutline(trans).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
SizeF sf = g.MeasureString(txt, font);
double x1 = sf.Height * Math.Sin(angle);
double x2 = sf.Width * Math.Cos(angle);
double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
else
{
//width = Math.Max(width, ((layout.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
double w = Math.Round(g.MeasureString(txt, font).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
}
}
return(width);
}
/// <summary>
/// 将DataTable导出
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="saveFilePath">保存位置</param>
private void ExportByNPOI(DataTable dt, string saveFilePath)
{
//NPOI到处excel
IWorkbook workbook;
string fileExt = Path.GetExtension(saveFilePath);
if (fileExt.Equals(".xls"))
{
workbook = new HSSFWorkbook();
}
else if (fileExt.Equals(".xlsx"))
{
workbook = new XSSFWorkbook();
}
else
{
return;
}
ISheet sheet = workbook.CreateSheet("Sheet1");
try {
//设置格式
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);
//用Caption 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
//配置列名
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.Caption);
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;
//字体
IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//建立内容行
int iRowIndex = 1;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in dt.Columns)
{
ICell newCell = DataRow.CreateCell(iCellIndex);
string drValue = Rowitem[Colitem].ToString();
switch (Colitem.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
if (dateV.Year == 1900)
{
newCell.SetCellValue("");
break;
}
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;
}
//cell.SetCellValue(rowObject[columnIds[columnIndex]].ToString());
//cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
for (int columnNum = 0; columnNum <= dt.Columns.Count; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
//写Excel
if (File.Exists(saveFilePath))
{
if (
DigiwinMessageBox.Show(Properties.Resources.String12, MessageBoxButtons.OKCancel,
MessageBoxIcon.Information) == DialogResult.Cancel)
{
return;
}
}
using (FileStream file = new FileStream(saveFilePath, FileMode.Create, FileAccess.Write)) {
workbook.Write(file);
}
//导出后打开文件
//DialogResult dialogResult = DigiwinMessageBox.Show(Properties.Resources.String3,
// MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
//if (dialogResult == DialogResult.OK)
//{
// //打开文件
// System.Diagnostics.Process process = new System.Diagnostics.Process();
// process.StartInfo.FileName = saveFilePath;
// try
// {
// process.Start();
// }
// catch (System.ComponentModel.Win32Exception we)
// {
// MessageBox.Show(we.Message);
// return;
// }
//}
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
finally {
workbook = null;
}
}
/// <summary>
/// Excel 导出
/// </summary>
/// <param name="SourceTable">来源DataTable数据集</param>
/// <param name="strFileName">输出文件名.xls</param>
/// <param name="strTemplateUrl">模板路径../sheet.xls</param>
/// <param name="strSheetName">填充的工作表名</param>
/// <param name="intTitleRowCount">标题行数</param>
/// <param name="HasColumns">是否自动生成列名，针对动态生成列名数据导出</param>
/// <param name="arrTitle">标题数组string[3]{"存放位置","内容","是否正标题(true/false)"}</param>
/// <param name="arrRow">列数组string[2]{"列号","列名"}</param>
/// <param name="arrEnd">列数组string[2]{"排放顺序","内容"}</param>
/// <returns></returns>
public void RenderDataTableToExcel(System.Data.DataTable SourceTable, string strFileName, string strTemplateUrl, string strSheetName, int intTitleRowCount, bool HasColumns, ArrayList arrTitle, ArrayList arrRow, ArrayList arrEnd)
{
if (!File.Exists(HttpContext.Current.Server.MapPath(strTemplateUrl)))
{
FileStream stream = File.Create(HttpContext.Current.Server.MapPath(strTemplateUrl));
stream.Close();//立即关闭创建文件进程，以便下面读取文件
}
FileStream file = new FileStream(HttpContext.Current.Server.MapPath(strTemplateUrl), FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheet(strSheetName);
if (sheet == null)
{
sheet = hssfworkbook.GetSheetAt(0);//取第一个工作表
}
try
{
//列标题数据填充
for (int i = 0; i < arrTitle.Count; i++)
{
string[] arr = arrTitle[i] as string[];
//获取行
IRow row = sheet.CreateRow(Int32.Parse(arr[0].ToString()) - 1);
ICell cell = row.CreateCell(0);
cell.SetCellValue(arr[1].ToString());
//合并列
sheet.AddMergedRegion(new CellRangeAddress(i, i, 0, SourceTable.Columns.Count - 1));
//格式设置
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.LEFT;
//字体设置
NPOI.SS.UserModel.IFont font = hssfworkbook.CreateFont();
//font.Boldweight = (short)FontBoldWeight.BOLD;
if (arr[2].ToString() == "true")//正标题 字体放大
{
font.FontHeight = 400;
cellStyle.Alignment = HorizontalAlignment.CENTER;
}
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
}
//构建表头
if (HasColumns)
{
IRow row = sheet.CreateRow(intTitleRowCount - 1);
//表头所在行号 即intTitleRowCount标题行数
for (int i = 0; i < arrRow.Count; i++)
{
string[] arr = arrRow[i] as string[];
//获取行
ICell cell = row.CreateCell(Int32.Parse(arr[0].ToString()) - 1);
cell.SetCellValue(arr[1].ToString());
//格式设置
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.CENTER;
//字体设置
NPOI.SS.UserModel.IFont font = hssfworkbook.CreateFont();
//font.Boldweight = (short)FontBoldWeight.BOLD;
cellStyle.SetFont(font);
//边框设置
cellStyle.BorderBottom = BorderStyle.THIN;
cellStyle.BorderTop = BorderStyle.THIN;
cellStyle.BorderLeft = BorderStyle.THIN;
cellStyle.BorderRight = BorderStyle.THIN;
cell.CellStyle = cellStyle;
//宽度设置
sheet.SetColumnWidth(Int32.Parse(arr[0].ToString()) - 1, (arr[1].ToString().Length) * 420);
}
}
//表尾填充
for (int i = 0; i < arrEnd.Count; i++)
{
string[] arr = arrEnd[i] as string[];
int intEnd = Int32.Parse(arr[0].ToString()) + SourceTable.Rows.Count + intTitleRowCount - 1;//插入表尾位置
//获取行
IRow row = sheet.CreateRow(intEnd);
ICell cell = row.CreateCell(0);
cell.SetCellValue(arr[1].ToString());
//构建一个合并区域
sheet.AddMergedRegion(new CellRangeAddress(intEnd, intEnd, 0, arrRow.Count - 1));
//格式设置
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.RIGHT;
//字体设置
NPOI.SS.UserModel.IFont font = hssfworkbook.CreateFont();
//font.Boldweight = (short)FontBoldWeight.BOLD;
cellStyle.SetFont(font);
cell.CellStyle = cellStyle;
}
//列主体数据填充
for (int i = 0; i < SourceTable.Rows.Count; i++)
{
int intTopWidth = 0;
int intMainWidth = 0;
//创建新行
IRow row = sheet.CreateRow(i + intTitleRowCount);
for (int j = 0; j < arrRow.Count; j++)
{
string[] arr = arrRow[j] as string[];
//创建新列
ICell cell = row.CreateCell(Int32.Parse(arr[0].ToString()) - 1);
cell.SetCellValue(SourceTable.Rows[i][arr[1].ToString()].ToString());
//列宽度设置
intTopWidth = sheet.GetColumnWidth(Int32.Parse(arr[0].ToString()) - 1); //表头宽度
intMainWidth = intMainWidth > (SourceTable.Rows[i][arr[1].ToString()].ToString().Length) * 420 ? intMainWidth : (SourceTable.Rows[i][arr[1].ToString()].ToString().Length) * 420; //表体宽度
if (intTopWidth < intMainWidth) //如果数据宽度比表头宽度大，则以数据宽度重新设置
{
sheet.SetColumnWidth(Int32.Parse(arr[0].ToString()) - 1, intMainWidth);
}
//边框设置
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.LEFT;
cellStyle.BorderBottom = BorderStyle.THIN;
cellStyle.BorderTop = BorderStyle.THIN;
cellStyle.BorderLeft = BorderStyle.THIN;
cellStyle.BorderRight = BorderStyle.THIN;
cell.CellStyle = cellStyle;
}
//固定列
//sheet.CreateFreezePane(4, 0, 4, 0);
}
using (MemoryStream stream = new MemoryStream())
{
hssfworkbook.Write(stream);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
curContext.Response.BinaryWrite(stream.GetBuffer());
curContext.Response.End();
}
}
catch (Exception e)
{
//AppendToFile(e.ToString());
}
}
public static void ToExcelByNPOI(System.Collections.IEnumerable list, string strExcelFileName, List <KeyValuePair <string, string> > fields = null)
{
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;
HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index;
HeadercellStyle.VerticalAlignment = VerticalAlignment.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);
headerRow.HeightInPoints = 30;
Type type = null;
foreach (var item in list)
{
type = item.GetType();
break;
}
PropertyInfo[] Properties = type.GetProperties();
if (fields == null)
{
foreach (PropertyInfo objProperty in Properties) //遍历T的属性
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(objProperty.Name);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
}
else
{
foreach (var field in fields) //遍历T的属性
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(field.Value);
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;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.Alignment = HorizontalAlignment.Center;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 1;
int iCellIndex = 0;
foreach (var item in list)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
DataRow.HeightInPoints = 25;
//PropertyInfo[] PI = typeof(T).GetProperties();
if (fields == null)
{
//循环获取该对象的所有属性和值
foreach (PropertyInfo info in Properties)
{
ICell cell = DataRow.CreateCell(iCellIndex);
var value = info.GetValue(item);
if (value != null)
{
cell.SetCellValue(value.ToString());
}
cell.CellStyle = cellStyle;
iCellIndex++;
}
}
else
{
foreach (var field in fields)
{
ICell cell = DataRow.CreateCell(iCellIndex);
var p = Properties.FirstOrDefault(a => a.Name == field.Key);
if (p != null)
{
var value = p.GetValue(item);
if (value != null)
{
cell.SetCellValue(value.ToString());
}
}
cell.CellStyle = cellStyle;
iCellIndex++;
}
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//string excelPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\" + strExcelFileName + ".xls";
//写Excel
FileStream file = new System.IO.FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
}
catch
{
throw;
}
}
//Datatable导出Excel
/// <summary>
/// 从DataTable导出Excel
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="strExcelFileName">excel文件路径</param>
/// <param name="fields">对应要导出的字段</param>
public static void ToExcelByNPOI(DataTable dt, string strExcelFileName, List <KeyValuePair <string, string> > fields = null)
{
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;
HeadercellStyle.VerticalAlignment = VerticalAlignment.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);
headerRow.HeightInPoints = 30;
if (fields == null)
{
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
}
else
{
foreach (var item in fields)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.Value);
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;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.Alignment = HorizontalAlignment.Center;
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);
DataRow.HeightInPoints = 25;
if (fields != null)
{
foreach (var Colitem in fields)
{
ICell cell = DataRow.CreateCell(iCellIndex);
var value = Rowitem[Colitem.Key];
if (value != null)
{
cell.SetCellValue(value.ToString());
}
cell.CellStyle = cellStyle;
iCellIndex++;
}
}
else
{
foreach (DataColumn Colitem in dt.Columns)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
var value = Rowitem[Colitem];
if (value != null)
{
cell.SetCellValue(value.ToString());
}
cell.CellStyle = cellStyle;
iCellIndex++;
}
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//string excelPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\" + strExcelFileName + ".xls";
//写Excel
FileStream file = new System.IO.FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
}
catch
{
throw;
}
}
private void ExportDataItem(DataTable dt)
{
DataRow dr = dt.Rows[0];
string sql = "";
string[] ddbh = dr["HT_DDBH"].ToString().Split('|');
sql = "select orderno,zdrnm,zdtime,PO_ZJE,marid,marnm,suppliernm,";
sql += "PO_TUHAO= stuff((select '|'+PO_TUHAO from View_TBPC_PURORDERDETAIL_PLAN_TOTAL as t where t.marid=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.marid and t.orderno=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.orderno FOR xml path('')),1,1,''),";
sql += "margg,marcz,margb,PO_MASHAPE,sum(convert(float,zxnum)) as zxnum,marunit,sum(convert(float,fznum))as fznum,PO_TECUNIT,";
sql += "stuff((select '|'+convert(varchar(50),length) from View_TBPC_PURORDERDETAIL_PLAN_TOTAL as a where a.marid=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.marid and a.orderno=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.orderno FOR xml path('')),1,1,'') as length,";
sql += "stuff((select '|'+convert(varchar(50),width) from View_TBPC_PURORDERDETAIL_PLAN_TOTAL as b where b.marid=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.marid and b.orderno=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.orderno FOR xml path('')),1,1,'') as width,";
sql += "stuff((select '|'+convert(varchar(50),PO_PZ) from View_TBPC_PURORDERDETAIL_PLAN_TOTAL as c where c.marid=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.marid and c.orderno=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.orderno FOR xml path('')),1,1,'') as PO_PZ,";
sql += "ctprice,sum(convert(float,ctamount))as ctamount,cgtimerq,";
sql += "detailnote =stuff((select '|'+detailnote from View_TBPC_PURORDERDETAIL_PLAN_TOTAL as d where d.marid=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.marid and d.orderno=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.orderno FOR xml path('')),1,1,''),";
sql += "ptcode=stuff((select ' | '+ptcode from View_TBPC_PURORDERDETAIL_PLAN_TOTAL as e where e.marid=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.marid and e.orderno=View_TBPC_PURORDERDETAIL_PLAN_TOTAL.orderno FOR xml path('')),1,1,'')";
sql += "from View_TBPC_PURORDERDETAIL_PLAN_TOTAL where orderno in (";
//sql = "select * from View_TBPC_PURORDERDETAIL_PLAN_TOTAL where orderno in (";
for (int i = 0, length = ddbh.Length; i < length; i++)
{
sql += "'" + ddbh[i] + "',";
}
sql = sql.Trim(',');
//sql += ")";
sql += ") group by orderno,zdrnm,zdtime,suppliernm,PO_ZJE,marid,marnm,margg,marcz,margb,PO_MASHAPE,marunit,PO_TECUNIT,ctprice,cgtimerq order by ptcode";
DataTable dt1 = DBCallCommon.GetDTUsingSqlText(sql);
//string filename = "采购合同" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
string filename = "采购合同--" + dr["HT_GF"].ToString() + dr["HT_QDSJ"].ToString() + ".xls";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename)));
HttpContext.Current.Response.Clear();
//1.读取Excel到FileStream
using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("采购合同.xls")))
{
IWorkbook wk = new HSSFWorkbook(fs);
#region //*********************导出到采购合同条款***************************//
ISheet sheet0 = wk.GetSheetAt(0);
IRow row1 = sheet0.GetRow(1); //买方合同号编号
row1.GetCell(3).SetCellValue(dr["HT_XFHTBH"].ToString());
IRow row2 = sheet0.GetRow(2);//卖方合同号编号
row2.GetCell(3).SetCellValue(dr["HT_GFHTBH"].ToString());
IRow row3 = sheet0.GetRow(3);//签订时间
row3.GetCell(3).SetCellValue(dr["HT_QDSJ"].ToString().Substring(0, 4) + "年" + dr["HT_QDSJ"].ToString().Substring(5, 2) + "月" + dr["HT_QDSJ"].ToString().Substring(8, 2) + "日");
IRow row4 = sheet0.GetRow(4);
row4.GetCell(1).SetCellValue(dr["HT_GF"].ToString());//卖方（供方）
IRow row7 = sheet0.GetRow(7);
row7.GetCell(1).SetCellValue(dr["HT_HTZJ"].ToString());//合同总价
IRow row22 = sheet0.GetRow(22);
IRow row23 = sheet0.GetRow(23);
IRow row24 = sheet0.GetRow(24);
IRow row25 = sheet0.GetRow(25);
IRow row26 = sheet0.GetRow(26);
IRow row27 = sheet0.GetRow(27);
if (dr["HT_JSFS"].ToString() == "1")//付款方式
{
row22.GetCell(0).SetCellValue("货到付款型");
row23.GetCell(0).SetCellValue("备注：" + dr["HT_JSFSBZ"].ToString());
}
if (dr["HT_JSFS"].ToString() == "2")
{
row22.GetCell(0).SetCellValue("款到发货型");
row23.GetCell(0).SetCellValue("备注：" + dr["HT_JSFSBZ"].ToString());
}
if (dr["HT_JSFS1BZ"].ToString() != "")
{
row24.GetCell(0).SetCellValue(dr["HT_JSFS1BZ"].ToString());
}
else
{
row24.HeightInPoints = 0;
}
if (dr["HT_JSFS2BZ"].ToString() != "")
{
row25.GetCell(0).SetCellValue(dr["HT_JSFS2BZ"].ToString());
}
else
{
row25.HeightInPoints = 0;
}
if (dr["HT_JSFS3"].ToString() != "")
{
row26.GetCell(0).SetCellValue(dr["HT_JSFS3"].ToString());
}
else
{
row26.HeightInPoints = 0;
}
row27.HeightInPoints = 0;
IRow row52 = sheet0.GetRow(52);
row52.GetCell(3).SetCellValue(dr["HT_GF"].ToString());//单位名称
IRow row53 = sheet0.GetRow(53);
row53.GetCell(3).SetCellValue(dr["HT_DZ"].ToString());//单位地址
IRow row54 = sheet0.GetRow(54);
row54.GetCell(3).SetCellValue(dr["HT_FDDBR"].ToString());//法定代表人
IRow row55 = sheet0.GetRow(55);
row55.GetCell(3).SetCellValue(dr["HT_WTDLR"].ToString());//委托代理人
IRow row56 = sheet0.GetRow(56);
row56.GetCell(3).SetCellValue(dr["HT_DH"].ToString());//电话
IRow row57 = sheet0.GetRow(57);
row57.GetCell(3).SetCellValue(dr["HT_CZ"].ToString());//传真
IRow row58 = sheet0.GetRow(58);
row58.GetCell(3).SetCellValue(dr["HT_KHYH"].ToString());//开户银行
IRow row59 = sheet0.GetRow(59);
row59.GetCell(3).SetCellValue(dr["HT_ZH"].ToString());//账号
IRow row60 = sheet0.GetRow(60);
row60.GetCell(3).SetCellValue(dr["HT_SH"].ToString());//税号
IRow row61 = sheet0.GetRow(61);
row61.GetCell(3).SetCellValue(dr["HT_YB"].ToString());//邮编
sheet0.ForceFormulaRecalculation = true;
#endregion
#region //******************导出到采购合同附件***********************//
//int[] a = new int[dt1.Rows.Count];
//for (int i = 1,length=dt1.Rows.Count; i < length; i++)
//{
// if (dt1.Rows[i]["orderno"].ToString()!=dt1.Rows[i-1]["orderno"].ToString())
// {
// a[i] = i;
// }
//}
int leixing = 0;
for (int i = 0, length = dt1.Rows.Count; i < length; i++)
{
if (dt1.Rows[i]["PO_MASHAPE"].ToString().Contains("板") || dt1.Rows[i]["PO_MASHAPE"].ToString().Contains("圆") || dt1.Rows[i]["PO_MASHAPE"].ToString().Contains("型"))
{
leixing = 1;
}
}
if (leixing == 0)//非钢材类
{
ISheet sheet1 = wk.GetSheetAt(1);
IRow rowddbh = sheet1.GetRow(1);
rowddbh.GetCell(0).SetCellValue("订单编号:" + dr["HT_DDBH"].ToString());
for (int i = 0, length = dt1.Rows.Count; i < length; i++)
{
IRow row = sheet1.CreateRow(i + 3);
row.HeightInPoints = 14;
row.CreateCell(0).SetCellValue(Convert.ToString(i + 1)); //序号
//row.CreateCell(1).SetCellValue(dt1.Rows[i]["orderno"].ToString());//计划单号
row.CreateCell(1).SetCellValue(dt1.Rows[i]["marid"].ToString()); //物料编码
row.CreateCell(2).SetCellValue(dt1.Rows[i]["marnm"].ToString()); //物料名称
row.CreateCell(3).SetCellValue(dt1.Rows[i]["PO_TUHAO"].ToString()); //图号
row.CreateCell(4).SetCellValue(dt1.Rows[i]["margg"].ToString()); //规格
row.CreateCell(5).SetCellValue(dt1.Rows[i]["marcz"].ToString()); //材质
row.CreateCell(6).SetCellValue(dt1.Rows[i]["margb"].ToString()); //国标
row.CreateCell(7).SetCellValue(dt1.Rows[i]["PO_MASHAPE"].ToString()); //类型
row.CreateCell(8).SetCellValue(dt1.Rows[i]["zxnum"].ToString()); //数（重）量
row.CreateCell(9).SetCellValue(dt1.Rows[i]["marunit"].ToString()); //单位
row.CreateCell(10).SetCellValue(dt1.Rows[i]["fznum"].ToString()); //辅助数量
row.CreateCell(11).SetCellValue(dt1.Rows[i]["PO_TECUNIT"].ToString()); //辅助单位
//row.CreateCell(13).SetCellValue(dt1.Rows[i]["length"].ToString());//长度
//row.CreateCell(14).SetCellValue(dt1.Rows[i]["width"].ToString());//宽度
//row.CreateCell(15).SetCellValue(dt1.Rows[i]["PO_PZ"].ToString());//片支
row.CreateCell(12).SetCellValue(dt1.Rows[i]["ctprice"].ToString()); //含税单价
row.CreateCell(13).SetCellValue(dt1.Rows[i]["ctamount"].ToString()); //含税金额
row.CreateCell(14).SetCellValue(dt1.Rows[i]["cgtimerq"].ToString()); //交货日期
row.CreateCell(15).SetCellValue(dt1.Rows[i]["detailnote"].ToString()); //备注
row.CreateCell(16).SetCellValue(dt1.Rows[i]["ptcode"].ToString()); //计划跟踪号
NPOI.SS.UserModel.IFont font1 = wk.CreateFont();
font1.FontName = "仿宋"; //字体
font1.FontHeightInPoints = 9; //字号
ICellStyle cells = wk.CreateCellStyle();
cells.SetFont(font1);
cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
for (int j = 0; j <= 16; j++)
{
row.Cells[j].CellStyle = cells;
}
}
double number = 0;
double money = 0;
for (int i = 0, length = dt1.Rows.Count; i < length; i++)
{
number += Convert.ToDouble(dt1.Rows[i]["zxnum"].ToString());
money += Convert.ToDouble(dt1.Rows[i]["ctamount"].ToString());
}
IRow rowhz = sheet1.CreateRow(dt1.Rows.Count + 3);
for (int i = 0; i <= 16; i++)
{
rowhz.CreateCell(i);
}
rowhz.GetCell(0).SetCellValue("合计");
rowhz.GetCell(8).SetCellValue(number);
rowhz.GetCell(13).SetCellValue(money);
NPOI.SS.UserModel.IFont font2 = wk.CreateFont();
font2.FontName = "仿宋"; //字体
font2.FontHeightInPoints = 9; //字号
ICellStyle cells2 = wk.CreateCellStyle();
cells2.SetFont(font2);
cells2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cells2.VerticalAlignment = VerticalAlignment.CENTER;
for (int i = 0; i <= 16; i++)
{
rowhz.Cells[i].CellStyle = cells2;
}
IRow rowbzts = sheet1.CreateRow(dt1.Rows.Count + 4);
rowbzts.CreateCell(0).SetCellValue("备注：");
rowbzts.Cells[0].CellStyle = cells2;
IRow rowbz = sheet1.CreateRow(dt1.Rows.Count + 5);
for (int i = 0; i <= 16; i++)
{
rowbz.CreateCell(i);
}
rowbz.GetCell(0).SetCellValue(dt.Rows[0]["HT_DDBZ"].ToString());
for (int i = 0; i <= 16; i++)
{
rowbz.Cells[i].CellStyle = cells2;
}
//ICellStyle cellstyle = wk.CreateCellStyle();
//cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
//cellstyle.VerticalAlignment = VerticalAlignment.CENTER;
//for (int i = 0; i <= 16; i++)
//{
// rowhz.Cells[i].CellStyle = cellstyle;
//}
CellRangeAddress range1 = new CellRangeAddress(dt1.Rows.Count + 3, dt1.Rows.Count + 3, 0, 7);
sheet1.AddMergedRegion(range1);
CellRangeAddress range2 = new CellRangeAddress(dt1.Rows.Count + 3, dt1.Rows.Count + 3, 9, 12);
sheet1.AddMergedRegion(range2);
CellRangeAddress range3 = new CellRangeAddress(dt1.Rows.Count + 3, dt1.Rows.Count + 3, 14, 16);
sheet1.AddMergedRegion(range3);
CellRangeAddress range4 = new CellRangeAddress(dt1.Rows.Count + 5, dt1.Rows.Count + 9, 0, 16);
sheet1.AddMergedRegion(range4);
for (int i = 0; i <= 16; i++)
{
sheet1.AutoSizeColumn(i);
}
sheet1.ForceFormulaRecalculation = true;
}
if (leixing == 1)
{
ISheet sheet2 = wk.GetSheetAt(2);
IRow rowddbh1 = sheet2.GetRow(1);
rowddbh1.GetCell(0).SetCellValue("订单编号：" + dr["HT_DDBH"].ToString());
for (int i = 0, length = dt1.Rows.Count; i < length; i++)
{
IRow row = sheet2.CreateRow(i + 3);
row.HeightInPoints = 14;
row.CreateCell(0).SetCellValue(Convert.ToString(i + 1)); //序号
//row.CreateCell(1).SetCellValue(dt1.Rows[i]["orderno"].ToString());//计划单号
row.CreateCell(1).SetCellValue(dt1.Rows[i]["marid"].ToString()); //物料编码
row.CreateCell(2).SetCellValue(dt1.Rows[i]["marnm"].ToString()); //物料名称
row.CreateCell(3).SetCellValue(dt1.Rows[i]["PO_TUHAO"].ToString()); //图号
row.CreateCell(4).SetCellValue(dt1.Rows[i]["margg"].ToString()); //规格
row.CreateCell(5).SetCellValue(dt1.Rows[i]["marcz"].ToString()); //材质
row.CreateCell(6).SetCellValue(dt1.Rows[i]["margb"].ToString()); //国标
row.CreateCell(7).SetCellValue(dt1.Rows[i]["PO_MASHAPE"].ToString()); //类型
row.CreateCell(8).SetCellValue(dt1.Rows[i]["zxnum"].ToString()); //数（重）量
row.CreateCell(9).SetCellValue(dt1.Rows[i]["marunit"].ToString()); //单位
//row.CreateCell(11).SetCellValue(dt1.Rows[i]["fznum"].ToString());//辅助数量
//row.CreateCell(12).SetCellValue(dt1.Rows[i]["marfzunit"].ToString());//辅助单位
row.CreateCell(10).SetCellValue(dt1.Rows[i]["length"].ToString()); //长度
row.CreateCell(11).SetCellValue(dt1.Rows[i]["width"].ToString()); //宽度
row.CreateCell(12).SetCellValue(dt1.Rows[i]["PO_PZ"].ToString()); //片支
row.CreateCell(13).SetCellValue(dt1.Rows[i]["ctprice"].ToString()); //含税单价
row.CreateCell(14).SetCellValue(dt1.Rows[i]["ctamount"].ToString()); //含税金额
row.CreateCell(15).SetCellValue(dt1.Rows[i]["cgtimerq"].ToString()); //交货日期
row.CreateCell(16).SetCellValue(dt1.Rows[i]["detailnote"].ToString()); //备注
row.CreateCell(17).SetCellValue(dt1.Rows[i]["ptcode"].ToString()); //计划跟踪号
NPOI.SS.UserModel.IFont font1 = wk.CreateFont();
font1.FontName = "仿宋"; //字体
font1.FontHeightInPoints = 9; //字号
ICellStyle cells = wk.CreateCellStyle();
cells.SetFont(font1);
cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
for (int j = 0; j <= 17; j++)
{
row.Cells[j].CellStyle = cells;
}
}
double number = 0;
double money = 0;
for (int i = 0, length = dt1.Rows.Count; i < length; i++)
{
number += Convert.ToDouble(dt1.Rows[i]["zxnum"].ToString());
money += Convert.ToDouble(dt1.Rows[i]["ctamount"].ToString());
}
IRow rowhz = sheet2.CreateRow(dt1.Rows.Count + 3);
NPOI.SS.UserModel.IFont font2 = wk.CreateFont();
font2.FontName = "仿宋"; //字体
font2.FontHeightInPoints = 9; //字号
ICellStyle cells2 = wk.CreateCellStyle();
cells2.SetFont(font2);
cells2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cells2.VerticalAlignment = VerticalAlignment.CENTER;
for (int i = 0; i < 18; i++)
{
rowhz.CreateCell(i);
}
rowhz.GetCell(0).SetCellValue("合计");
rowhz.GetCell(8).SetCellValue(number);
rowhz.GetCell(14).SetCellValue(money);
for (int i = 0; i < 18; i++)
{
rowhz.Cells[i].CellStyle = cells2;
}
IRow rowbzts = sheet2.CreateRow(dt1.Rows.Count + 4);
rowbzts.CreateCell(0).SetCellValue("备注：");
rowbzts.Cells[0].CellStyle = cells2;
IRow rowbz = sheet2.CreateRow(dt1.Rows.Count + 5);
for (int i = 0; i < 18; i++)
{
rowbz.CreateCell(i);
}
rowbz.GetCell(0).SetCellValue(dt.Rows[0]["HT_DDBZ"].ToString());
for (int i = 0; i < 18; i++)
{
rowbz.Cells[i].CellStyle = cells2;
}
//ICellStyle cellstyle = wk.CreateCellStyle();
//cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
//cellstyle.VerticalAlignment = VerticalAlignment.CENTER;
//for (int i = 0; i < 18; i++)
//{
// rowhz.Cells[i].CellStyle = cellstyle;
//}
CellRangeAddress range1 = new CellRangeAddress(dt1.Rows.Count + 3, dt1.Rows.Count + 3, 0, 7);
sheet2.AddMergedRegion(range1);
CellRangeAddress range2 = new CellRangeAddress(dt1.Rows.Count + 3, dt1.Rows.Count + 3, 9, 13);
sheet2.AddMergedRegion(range2);
CellRangeAddress range3 = new CellRangeAddress(dt1.Rows.Count + 3, dt1.Rows.Count + 3, 15, 17);
sheet2.AddMergedRegion(range3);
CellRangeAddress range4 = new CellRangeAddress(dt1.Rows.Count + 5, dt1.Rows.Count + 9, 0, 17);
sheet2.AddMergedRegion(range4);
for (int i = 0; i < 18; i++)
{
sheet2.AutoSizeColumn(i);
}
sheet2.ForceFormulaRecalculation = true;
}
#endregion
//sheet0.ProtectSheet("123456");
//sheet1.ProtectSheet("123456");
MemoryStream file = new MemoryStream();
wk.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
}
private void ExportDataItem(DataTable dt)
{
DataRow dr = dt.Rows[0];
string filename = "招聘计划--" + dr["JH_HZBH"].ToString() + ".xls";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename)));
HttpContext.Current.Response.Clear();
//1.读取Excel到FileStream
using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("招聘计划导出.xls")))
{
IWorkbook wk = new HSSFWorkbook(fs);
ISheet sheet0 = wk.GetSheetAt(0);
for (int i = 0, length = dt.Rows.Count; i < length; i++)
{
IRow row = sheet0.CreateRow(i + 4);
row.HeightInPoints = 20;
row.CreateCell(0).SetCellValue(Convert.ToString(i + 1));//序号
row.CreateCell(1).SetCellValue(dt.Rows[i]["JH_HZBH"].ToString());
row.CreateCell(2).SetCellValue(dt.Rows[i]["JH_ZPBM"].ToString());
row.CreateCell(3).SetCellValue(dt.Rows[i]["JH_GWMC"].ToString());
row.CreateCell(4).SetCellValue(dt.Rows[i]["JH_XQLY"].ToString());
row.CreateCell(5).SetCellValue(dt.Rows[i]["JH_ZPFS"].ToString());
row.CreateCell(6).SetCellValue(dt.Rows[i]["JH_ZPRS"].ToString());
row.CreateCell(7).SetCellValue(dt.Rows[i]["JH_ZPGW"].ToString());
row.CreateCell(8).SetCellValue(dt.Rows[i]["JH_ZPZY"].ToString());
row.CreateCell(9).SetCellValue(dt.Rows[i]["JH_ZPYX"].ToString());
row.CreateCell(10).SetCellValue(dt.Rows[i]["JH_ZPXL"].ToString());
row.CreateCell(11).SetCellValue(dt.Rows[i]["JH_ZPXB"].ToString());
row.CreateCell(12).SetCellValue(dt.Rows[i]["JH_ZPNL"].ToString());
row.CreateCell(13).SetCellValue(dt.Rows[i]["JH_ZPYQ"].ToString());
row.CreateCell(14).SetCellValue(dt.Rows[i]["JH_QTYQ"].ToString());
row.CreateCell(15).SetCellValue(dt.Rows[i]["JH_XWDGSJ"].ToString());
row.CreateCell(16).SetCellValue(dt.Rows[i]["JH_NGZDD"].ToString());
row.CreateCell(17).SetCellValue(dt.Rows[i]["JH_QT"].ToString());
NPOI.SS.UserModel.IFont font1 = wk.CreateFont();
font1.FontName = "仿宋"; //字体
font1.FontHeightInPoints = 9; //字号
ICellStyle cells = wk.CreateCellStyle();
cells.SetFont(font1);
cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
for (int j = 0; j <= 17; j++)
{
row.Cells[j].CellStyle = cells;
}
}
int num = 0;
for (int i = 0, length = dt.Rows.Count; i < length; i++)
{
num += Convert.ToInt32(dt.Rows[i]["JH_ZPRS"].ToString());
}
IRow rowhz = sheet0.CreateRow(dt.Rows.Count + 4);
for (int i = 0; i <= 17; i++)
{
rowhz.CreateCell(i);
}
rowhz.GetCell(0).SetCellValue("人数合计");
rowhz.GetCell(6).SetCellValue(num);
rowhz.HeightInPoints = 20;
NPOI.SS.UserModel.IFont font2 = wk.CreateFont();
font2.FontName = "仿宋"; //字体
font2.FontHeightInPoints = 9; //字号
ICellStyle cells2 = wk.CreateCellStyle();
cells2.SetFont(font2);
cells2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cells2.VerticalAlignment = VerticalAlignment.CENTER;
for (int i = 0; i <= 17; i++)
{
rowhz.Cells[i].CellStyle = cells2;
}
CellRangeAddress range1 = new CellRangeAddress(dt.Rows.Count + 4, dt.Rows.Count + 4, 0, 5);
sheet0.AddMergedRegion(range1);
CellRangeAddress range2 = new CellRangeAddress(dt.Rows.Count + 4, dt.Rows.Count + 4, 7, 17);
sheet0.AddMergedRegion(range2);
IRow rowqz1 = sheet0.CreateRow(dt.Rows.Count + 6);
for (int i = 0; i <= 17; i++)
{
rowqz1.CreateCell(i);
}
rowqz1.GetCell(0).SetCellValue("编制负责人：");
rowqz1.GetCell(2).SetCellValue(dr["ZDR"].ToString());
rowqz1.GetCell(5).SetCellValue("申报单位/部门主管：");
rowqz1.GetCell(7).SetCellValue(dr["SPR1"].ToString());
rowqz1.GetCell(8).SetCellValue(dr["SPR1_JL"].ToString() == "y" ? "同意" : dr["SPR1_JL"].ToString() == "n" ? "不同意" : "");
rowqz1.GetCell(12).SetCellValue("集团公司主管领导：");
rowqz1.GetCell(14).SetCellValue(dr["SPR2"].ToString());
rowqz1.GetCell(15).SetCellValue(dr["SPR2_JL"].ToString() == "y" ? "同意" : dr["SPR2_JL"].ToString() == "n" ? "不同意" : "");
for (int i = 0; i <= 17; i++)
{
rowqz1.Cells[i].CellStyle = cells2;
}
CellRangeAddress range3 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 0, 1);
sheet0.AddMergedRegion(range3);
//CellRangeAddress range4 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 3, 4);
//sheet0.AddMergedRegion(range4);
CellRangeAddress range5 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 5, 6);
sheet0.AddMergedRegion(range5);
//CellRangeAddress range6 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 9, 11);
//sheet0.AddMergedRegion(range6);
CellRangeAddress range7 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 12, 13);
sheet0.AddMergedRegion(range7);
IRow rowqz2 = sheet0.CreateRow(dt.Rows.Count + 7);
for (int i = 0; i <= 17; i++)
{
rowqz2.CreateCell(i);
}
rowqz2.GetCell(1).SetCellValue("日期：");
rowqz2.GetCell(2).SetCellValue(dr["ZDR_SJ"].ToString());
rowqz2.GetCell(6).SetCellValue("日期：");
rowqz2.GetCell(7).SetCellValue(dr["SPR1_SJ"].ToString());
rowqz2.GetCell(13).SetCellValue("日期：");
rowqz2.GetCell(14).SetCellValue(dr["SPR2_SJ"].ToString());
for (int i = 0; i <= 17; i++)
{
rowqz2.Cells[i].CellStyle = cells2;
}
MemoryStream file = new MemoryStream();
wk.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
}
/// <summary>
/// Datatable导出Excel
/// </summary>
/// <param name="dt">datatable</param>
/// <param name="strExcelFileName">Excel文件目录地址</param>
/// <param name="title">Excel文件标题</param>
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;
ICell celltitle = headerRow.CreateCell(0);
celltitle.SetCellValue(title);
celltitle.CellStyle = HeadercellStyle;
//用column name 作为列名
IRow headerRow1 = sheet.CreateRow(1);
foreach (DataColumn item in dt.Columns)
{
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)
{
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)
{
throw ex;
}
finally
{
workbook = null;
}
}
private void CreateSheet(HSSFWorkbook workbook, DataTable dtExcel, IDictionary htFields, int sheetIndex, int nextRowIndex)
{
string sheetName = string.Format("sheet{0}", sheetIndex);
// 构建工作台中的一张表，对应到excel文档中的sheet
ISheet sheet = workbook.CreateSheet(sheetName);
#region 构建Sheet的头部
// 新建一行，对应到excel文件中的一行
IRow row = sheet.CreateRow(0);
// 把值写入单元格中，此处生成表头
// 这个为列索引，从0开始
int cellIndex = 0;
if (_isWholeTable)
{
for (int i = 0; i < dtExcel.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dtExcel.Columns[i].ColumnName);
}
}
else
{
foreach (DictionaryEntry dictionaryEntry in htFields)
{
int columnIndex = dtExcel.Columns.IndexOf(dictionaryEntry.Key.ToString());
if (columnIndex > -1)
{
string fieldText = htFields[dictionaryEntry.Key].ToString();
ICell cell = row.CreateCell(cellIndex++);
cell.SetCellValue(fieldText);
// 头部样式设置
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.BorderBottom = BorderStyle.Thick;
cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BrightGreen.Index;
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
cellStyle.SetFont(headerfont);
cell.CellStyle = cellStyle;
}
}
}
#endregion
#region 单元格中填写数据
int totalCount = dtExcel.Rows.Count;
bool isEndSheet = sheetIndex * DefaultMaxRow >= totalCount;
int broundRowsCount = sheetIndex * DefaultMaxRow >= totalCount ? totalCount : sheetIndex * DefaultMaxRow;
for (int i = nextRowIndex; i < broundRowsCount; i++)
{
// 列索引置0
cellIndex = 0;
// 生成一行，对应到excel文件中的一条数据行
IRow row1 = sheet.CreateRow(i - nextRowIndex + 1);
// 把值写入单元格中，此处生成表身
foreach (DictionaryEntry dictionaryEntry in htFields)
{
row1.CreateCell(cellIndex++).SetCellValue(dtExcel.Rows[i][dictionaryEntry.Key.ToString()].ToString());
}
}
#endregion
#region 设置Sheet中列自适应
// 列宽度设置为自适应
for (int i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
}
#endregion
if (!isEndSheet)
{
CreateSheet(workbook, dtExcel, htFields, sheetIndex + 1, nextRowIndex + broundRowsCount);
}
}
/// <summary>
/// 采购比价单
/// </summary>
public static void ExportMSData(System.Data.DataTable dt)
{
//Object Opt = System.Type.Missing;
//Application m_xlApp = new Application();
//Workbooks workbooks = m_xlApp.Workbooks;
//Workbook workbook;// = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//Worksheet wksheet;
//workbook = m_xlApp.Workbooks.Open(System.Web.HttpContext.Current.Server.MapPath("采购比价单明细") + ".xls", Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt); ;
//m_xlApp.Visible = false; // Excel不显示
//m_xlApp.DisplayAlerts = false; // 关闭提示，采用默认的方案执行（合并单元格的时候，如果两个单元格都有数据，会出现一个确认提示）
//wksheet = (Worksheet)workbook.Sheets.get_Item(1);
////根据批号查询数据
////设置工作薄名称
//////填充数据
//int rowCount = dt.Rows.Count;
//int colCount = dt.Columns.Count;
//object[,] dataArray = new object[rowCount, colCount+1];
//for (int i = 0; i < rowCount; i++)
//{
// dataArray[i, 0] = i + 1;
// for (int j = 0; j < colCount; j++)
// {
// dataArray[i, j + 1] = dt.Rows[i][j];
// }
//}
//wksheet.get_Range("A3", wksheet.Cells[rowCount + 2, colCount+1]).Value2 = dataArray;
//wksheet.get_Range("A3", wksheet.Cells[rowCount + 2, colCount+1]).Borders.LineStyle = 1;
//////设置列宽
//wksheet.Columns.EntireColumn.AutoFit();//列宽自适应
//string filename = System.Web.HttpContext.Current.Server.MapPath("采购比价单明细" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls");
//ExportExcel_Exit(filename, workbook, m_xlApp, wksheet);
string filename = "采购比价单明细" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename)));
HttpContext.Current.Response.Clear();
//1.读取Excel到FileStream
using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("采购比价单明细.xls")))
{
IWorkbook wk = new HSSFWorkbook(fs);
ISheet sheet0 = wk.GetSheetAt(0);
#region 写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet0.CreateRow(i + 3);
row.CreateCell(0).SetCellValue(Convert.ToString(i + 1)); //序号
row.CreateCell(1).SetCellValue(dt.Rows[i]["picno"].ToString()); //比价单号
row.CreateCell(2).SetCellValue(dt.Rows[i]["ptcode"].ToString()); //计划跟踪号
row.CreateCell(3).SetCellValue(dt.Rows[i]["PIC_CHILDENGNAME"].ToString()); //部件名称
row.CreateCell(4).SetCellValue(dt.Rows[i]["PIC_MAP"].ToString()); //部件图号
row.CreateCell(5).SetCellValue(dt.Rows[i]["margb"].ToString()); //国标
row.CreateCell(6).SetCellValue(dt.Rows[i]["PIC_TUHAO"].ToString()); //图号
row.CreateCell(7).SetCellValue(dt.Rows[i]["marnm"].ToString()); //名称
row.CreateCell(8).SetCellValue(dt.Rows[i]["margg"].ToString()); //规格
row.CreateCell(9).SetCellValue(dt.Rows[i]["marcz"].ToString()); //材质
row.CreateCell(10).SetCellValue(dt.Rows[i]["length"].ToString()); //长度
row.CreateCell(11).SetCellValue(dt.Rows[i]["width"].ToString()); //宽度
row.CreateCell(12).SetCellValue(dt.Rows[i]["marnum"].ToString()); //数量
row.CreateCell(13).SetCellValue(dt.Rows[i]["marunit"].ToString()); //单位
row.CreateCell(14).SetCellValue(dt.Rows[i]["marfznum"].ToString()); //辅助数量
row.CreateCell(15).SetCellValue(dt.Rows[i]["marfzunit"].ToString()); //辅助单位
row.CreateCell(16).SetCellValue(dt.Rows[i]["supplierresnm"].ToString()); //供应商
//row.CreateCell(4).SetCellValue( dt.Rows[i]["pjnm"].ToString());//项目名称
//row.CreateCell(5).SetCellValue( dt.Rows[i]["engnm"].ToString());//工程名称
//row.CreateCell(7).SetCellValue( dt.Rows[i]["marid"].ToString());//物料编码
row.CreateCell(17).SetCellValue(dt.Rows[i]["price"].ToString()); //单价
row.CreateCell(18).SetCellValue(dt.Rows[i]["detamount"].ToString()); //金额
row.CreateCell(19).SetCellValue(dt.Rows[i]["detailnote"].ToString()); //备注
row.CreateCell(20).SetCellValue(dt.Rows[i]["supplieranm"].ToString()); //供应商1
row.CreateCell(21).SetCellValue(dt.Rows[i]["qoutefstsa"].ToString()); //供应商1
row.CreateCell(22).SetCellValue(dt.Rows[i]["qoutescdsa"].ToString()); //供应商1
row.CreateCell(23).SetCellValue(dt.Rows[i]["qoutelstsa"].ToString()); //供应商1
row.CreateCell(24).SetCellValue(dt.Rows[i]["supplierbnm"].ToString()); //供应商2
row.CreateCell(25).SetCellValue(dt.Rows[i]["qoutefstsb"].ToString()); //供应商2
row.CreateCell(26).SetCellValue(dt.Rows[i]["qoutescdsb"].ToString()); //供应商2
row.CreateCell(27).SetCellValue(dt.Rows[i]["qoutelstsb"].ToString()); //供应商2
row.CreateCell(28).SetCellValue(dt.Rows[i]["suppliercnm"].ToString()); //供应商3
row.CreateCell(29).SetCellValue(dt.Rows[i]["qoutefstsc"].ToString()); //供应商3
row.CreateCell(30).SetCellValue(dt.Rows[i]["qoutescdsc"].ToString()); //供应商3
row.CreateCell(31).SetCellValue(dt.Rows[i]["qoutelstsc"].ToString()); //供应商3
row.CreateCell(32).SetCellValue(dt.Rows[i]["supplierdnm"].ToString()); //供应商4
row.CreateCell(33).SetCellValue(dt.Rows[i]["qoutefstsd"].ToString()); //供应商4
row.CreateCell(34).SetCellValue(dt.Rows[i]["qoutescdsd"].ToString()); //供应商4
row.CreateCell(35).SetCellValue(dt.Rows[i]["qoutelstsd"].ToString()); //供应商4
row.CreateCell(36).SetCellValue(dt.Rows[i]["supplierenm"].ToString()); //供应商5
row.CreateCell(37).SetCellValue(dt.Rows[i]["qoutefstse"].ToString()); //供应商5
row.CreateCell(38).SetCellValue(dt.Rows[i]["qoutescdse"].ToString()); //供应商5
row.CreateCell(39).SetCellValue(dt.Rows[i]["qoutelstse"].ToString()); //供应商5
row.CreateCell(40).SetCellValue(dt.Rows[i]["supplierfnm"].ToString()); //供应商6
row.CreateCell(41).SetCellValue(dt.Rows[i]["qoutefstsf"].ToString()); //供应商6
row.CreateCell(42).SetCellValue(dt.Rows[i]["qoutescdsf"].ToString()); //供应商6
row.CreateCell(43).SetCellValue(dt.Rows[i]["qoutelstsf"].ToString()); //供应商6
NPOI.SS.UserModel.IFont font1 = wk.CreateFont();
font1.FontName = "仿宋"; //字体
font1.FontHeightInPoints = 11; //字号
ICellStyle cells = wk.CreateCellStyle();
cells.SetFont(font1);
for (int j = 0; j < 44; j++)
{
row.Cells[j].CellStyle = cells;
}
}
#endregion
for (int i = 0; i <= dt.Columns.Count; i++)
{
sheet0.AutoSizeColumn(i);
}
sheet0.ForceFormulaRecalculation = true;
MemoryStream file = new MemoryStream();
wk.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
}
/// <summary>
/// 采购订单
/// </summary>
public static void ExportMSData(System.Data.DataTable dt)
{
//Object Opt = System.Type.Missing;
//Application m_xlApp = new Application();
//Workbooks workbooks = m_xlApp.Workbooks;
//Workbook workbook;// = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//Worksheet wksheet;
//workbook = m_xlApp.Workbooks.Open(System.Web.HttpContext.Current.Server.MapPath("采购订单明细表模版") + ".xls", Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt); ;
//m_xlApp.Visible = false; // Excel不显示
//m_xlApp.DisplayAlerts = false; // 关闭提示，采用默认的方案执行（合并单元格的时候，如果两个单元格都有数据，会出现一个确认提示）
//wksheet = (Worksheet)workbook.Sheets.get_Item(1);
////根据批号查询数据
////设置工作薄名称
//////填充数据
//int rowCount = dt.Rows.Count;
//int colCount = dt.Columns.Count;
//object[,] dataArray = new object[rowCount, colCount];
//for (int i = 0; i < rowCount; i++)
//{
// dataArray[i, 0] = i + 1;
// for (int j = 0; j < colCount-2; j++)
// {
// dataArray[i, j+1] = dt.Rows[i][j];
// }
//}
//wksheet.get_Range("A3", wksheet.Cells[rowCount + 2, colCount-1]).Value2 = dataArray;
//wksheet.get_Range("A3", wksheet.Cells[rowCount + 2, colCount-1]).Borders.LineStyle = 1;
//////设置列宽
//wksheet.Columns.EntireColumn.AutoFit();//列宽自适应
//string filename = System.Web.HttpContext.Current.Server.MapPath("采购订单明细" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls");
//ExportExcel_Exit(filename, workbook, m_xlApp, wksheet);
string filename = "采购订单明细" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename)));
HttpContext.Current.Response.Clear();
//1.读取Excel到FileStream
using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("采购订单明细表模版.xls")))
{
IWorkbook wk = new HSSFWorkbook(fs);
ISheet sheet0 = wk.GetSheetAt(0);
#region 写入数据
IRow row1 = sheet0.GetRow(1);
row1.GetCell(2).SetCellValue(dt.Rows[0]["orderno"].ToString()); //订单编号
row1.GetCell(7).SetCellValue(dt.Rows[0]["suppliernm"].ToString()); //供应商
row1.GetCell(13).SetCellValue(dt.Rows[0]["cgtimerq"].ToString()); //交货日期
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet0.CreateRow(i + 3);
#region MyRegion
//row.CreateCell(0).SetCellValue(Convert.ToString(i + 1));//序号
//row.CreateCell(1).SetCellValue("'" + dt.Rows[i]["orderno"].ToString());//订单编号
//row.CreateCell(2).SetCellValue("'" + dt.Rows[i]["suppliernm"].ToString());//供应商
//row.CreateCell(3).SetCellValue("'" + dt.Rows[i]["zdtime"].ToString());//制单日期
//row.CreateCell(4).SetCellValue("'" + dt.Rows[i]["ptcode"].ToString());//计划跟踪号
//row.CreateCell(5).SetCellValue("'" + dt.Rows[i]["pjnm"].ToString());//项目名称
//row.CreateCell(6).SetCellValue("'" + dt.Rows[i]["engnm"].ToString());//工程名称
//row.CreateCell(7).SetCellValue("'" + dt.Rows[i]["PO_TUHAO"].ToString());//图号/标识号
//row.CreateCell(8).SetCellValue("'" + dt.Rows[i]["marid"].ToString());//物料编码
//row.CreateCell(9).SetCellValue("'" + dt.Rows[i]["marnm"].ToString());//名称
//row.CreateCell(10).SetCellValue("'" + dt.Rows[i]["margg"].ToString());//规格
//row.CreateCell(11).SetCellValue("'" + dt.Rows[i]["marcz"].ToString());//材质
//row.CreateCell(12).SetCellValue("'" + dt.Rows[i]["margb"].ToString());//国标
//row.CreateCell(13).SetCellValue(dt.Rows[i]["price"].ToString());//单价
//row.CreateCell(14).SetCellValue(dt.Rows[i]["amount"].ToString());//金额
//row.CreateCell(15).SetCellValue(dt.Rows[i]["ctprice"].ToString());//含税单价
//row.CreateCell(16).SetCellValue(dt.Rows[i]["ctamount"].ToString());//价税合计
//row.CreateCell(17).SetCellValue(dt.Rows[i]["zxnum"].ToString());//采购数量
//row.CreateCell(18).SetCellValue("'" + dt.Rows[i]["marunit"].ToString());//单位
//row.CreateCell(19).SetCellValue(dt.Rows[i]["zxfznum"].ToString());//辅助数量
//row.CreateCell(20).SetCellValue("'" + dt.Rows[i]["marfzunit"].ToString());//辅助单位
//row.CreateCell(21).SetCellValue(dt.Rows[i]["recgdnum"].ToString());//已到货数量
//row.CreateCell(22).SetCellValue("'" + dt.Rows[i]["recdate"].ToString());//到货日期
//row.CreateCell(23).SetCellValue("'" + dt.Rows[i]["cgtimerq"].ToString());//交货日期
//row.CreateCell(24).SetCellValue("'" + dt.Rows[i]["length"].ToString());//长度
//row.CreateCell(25).SetCellValue("'" + dt.Rows[i]["width"].ToString());//宽度
//row.CreateCell(26).SetCellValue("'" + dt.Rows[i]["detailnote"].ToString());//备注
#endregion
row.CreateCell(0).SetCellValue(Convert.ToString(i + 1)); //序号
row.CreateCell(1).SetCellValue(dt.Rows[i]["marid"].ToString()); //物料编码
row.CreateCell(2).SetCellValue(dt.Rows[i]["marnm"].ToString()); //名称
row.CreateCell(3).SetCellValue(dt.Rows[i]["PO_CHILDENGNAME"].ToString()); //部件名称
row.CreateCell(4).SetCellValue(dt.Rows[i]["PO_MAP"].ToString()); //部件图号
row.CreateCell(5).SetCellValue(dt.Rows[i]["margb"].ToString()); //国标
row.CreateCell(6).SetCellValue(dt.Rows[i]["PO_TUHAO"].ToString()); //图号/标识号
row.CreateCell(7).SetCellValue(dt.Rows[i]["margg"].ToString()); //规格
row.CreateCell(8).SetCellValue(dt.Rows[i]["marcz"].ToString()); //材质
row.CreateCell(9).SetCellValue(dt.Rows[i]["length"].ToString()); //长度
row.CreateCell(10).SetCellValue(dt.Rows[i]["width"].ToString()); //宽度
row.CreateCell(11).SetCellValue(dt.Rows[i]["marunit"].ToString()); //单位
row.CreateCell(12).SetCellValue(dt.Rows[i]["zxnum"].ToString()); //采购数量
row.CreateCell(13).SetCellValue(dt.Rows[i]["PO_TECUNIT"].ToString()); //辅助单位
row.CreateCell(14).SetCellValue(dt.Rows[i]["zxfznum"].ToString()); //辅助数量
row.CreateCell(15).SetCellValue(dt.Rows[i]["ctprice"].ToString()); //含税单价
row.CreateCell(16).SetCellValue(dt.Rows[i]["ctamount"].ToString()); //加税合计
//row.CreateCell(19).SetCellValue(dt.Rows[i]["price"].ToString());//单价（不含税）
//row.CreateCell(20).SetCellValue(dt.Rows[i]["amount"].ToString());//金额（不含税）
//row.CreateCell(22).SetCellValue(dt.Rows[i]["recdate"].ToString());//到货日期
row.CreateCell(17).SetCellValue(dt.Rows[i]["PO_MASHAPE"].ToString()); //类型
row.CreateCell(18).SetCellValue(dt.Rows[i]["detailnote"].ToString()); //备注
//row.CreateCell(25).SetCellValue(dt.Rows[i]["taxrate"].ToString());//税率
//row.CreateCell(26).SetCellValue(dt.Rows[i]["recgdnum"].ToString());//已到货数量
row.CreateCell(19).SetCellValue(dt.Rows[i]["ptcode"].ToString());//计划跟踪号
NPOI.SS.UserModel.IFont font1 = wk.CreateFont();
font1.FontName = "仿宋"; //字体
font1.FontHeightInPoints = 11; //字号
ICellStyle cells = wk.CreateCellStyle();
cells.SetFont(font1);
for (int j = 0; j < 20; j++)
{
row.Cells[j].CellStyle = cells;
}
}
#endregion
for (int i = 0; i <= dt.Columns.Count; i++)
{
sheet0.AutoSizeColumn(i);
}
sheet0.ForceFormulaRecalculation = true;
MemoryStream file = new MemoryStream();
wk.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
}
public static bool DataTableToExcel(DataTable dt, string relativeFileName)
{
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;
sheet.AutoSizeColumn(icolIndex);
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 datetimeCellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
IDataFormat datastyle = workbook.CreateDataFormat();
datetimeCellStyle.DataFormat = datastyle.GetFormat("yyyy/MM/dd");
datetimeCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
datetimeCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
datetimeCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
datetimeCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
datetimeCellStyle.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);
if (Colitem.DataType == typeof(DateTime))
{
var value = Rowitem[Colitem];
if (value.GetType() == typeof(DateTime))
{
cell.SetCellValue(Convert.ToDateTime(value));
}
else
{
cell.SetCellValue(value.ToString());
}
cell.CellStyle = datetimeCellStyle;
}
else
{
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
}
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//写Excel
var absolutePath = Path.Combine(SystemInfo.BaseDirectory, relativeFileName);
var dir = Path.GetDirectoryName(absolutePath);
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
FileStream file = new FileStream(absolutePath, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
return(true);
}
catch (Exception ex)
{
return(false);
//ILog log = LogManager.GetLogger("Exception Log");
//log.Error(ex.Message + Environment.NewLine + ex.StackTrace);
}
finally
{
//workbook = null;
}
}
private void ExportDataItem(DataTable dt)
{
DataRow dr = dt.Rows[0];
string filename = "培训实施记录--" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename)));
HttpContext.Current.Response.Clear();
//1.读取Excel到FileStream
using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("培训实施记录.xls")))
{
IWorkbook wk = new HSSFWorkbook(fs);
ISheet sheet0 = wk.GetSheetAt(0);
for (int i = 0, length = dt.Rows.Count; i < length; i++)
{
IRow row = sheet0.CreateRow(i + 2);
row.HeightInPoints = 20;
row.CreateCell(0).SetCellValue(Convert.ToString(i + 1));//序号
row.CreateCell(1).SetCellValue(dt.Rows[i]["PX_BM"].ToString());
row.CreateCell(2).SetCellValue(dt.Rows[i]["PX_BH"].ToString());
row.CreateCell(3).SetCellValue(dt.Rows[i]["SPLX"].ToString() == "NDPXJH" ? "年度" : "临时");
row.CreateCell(4).SetCellValue(dt.Rows[i]["PX_FS"].ToString() == "n" ? "内部" : "外部");
row.CreateCell(5).SetCellValue(dt.Rows[i]["PX_XMMC"].ToString());
row.CreateCell(6).SetCellValue(dt.Rows[i]["PX_SJ"].ToString() == "1" ? "第一季度" : dt.Rows[i]["PX_SJ"].ToString() == "2" ? "第二季度" : dt.Rows[i]["PX_SJ"].ToString() == "3" ? "第三季度" : "第四季度");
row.CreateCell(7).SetCellValue(dt.Rows[i]["PX_DD"].ToString());
row.CreateCell(8).SetCellValue(dt.Rows[i]["PX_ZJR"].ToString());
row.CreateCell(9).SetCellValue(dt.Rows[i]["PX_DX"].ToString());
row.CreateCell(10).SetCellValue(dt.Rows[i]["PX_RS"].ToString());
row.CreateCell(11).SetCellValue(dt.Rows[i]["PX_XS"].ToString());
row.CreateCell(12).SetCellValue(dt.Rows[i]["PX_FYYS"].ToString());
row.CreateCell(13).SetCellValue(dt.Rows[i]["PX_SJSJ"].ToString());
row.CreateCell(14).SetCellValue(dt.Rows[i]["PX_SJDD"].ToString());
row.CreateCell(15).SetCellValue(dt.Rows[i]["PX_SJRY"].ToString());
row.CreateCell(16).SetCellValue(dt.Rows[i]["PX_SJRS"].ToString());
row.CreateCell(17).SetCellValue(dt.Rows[i]["PX_SJXS"].ToString());
row.CreateCell(18).SetCellValue(dt.Rows[i]["PX_SJBZ"].ToString());
NPOI.SS.UserModel.IFont font1 = wk.CreateFont();
font1.FontName = "仿宋"; //字体
font1.FontHeightInPoints = 9; //字号
ICellStyle cells = wk.CreateCellStyle();
cells.SetFont(font1);
cells.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cells.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
for (int j = 0; j <= 18; j++)
{
row.Cells[j].CellStyle = cells;
}
}
double num = 0;
for (int i = 0, length = dt.Rows.Count; i < length; i++)
{
num += CommonFun.ComTryDouble(dt.Rows[i]["PX_SJXS"].ToString());
}
IRow rowhz = sheet0.CreateRow(dt.Rows.Count + 2);
for (int i = 0; i <= 18; i++)
{
rowhz.CreateCell(i);
}
rowhz.GetCell(0).SetCellValue("人数合计");
rowhz.GetCell(17).SetCellValue(num);
rowhz.HeightInPoints = 20;
NPOI.SS.UserModel.IFont font2 = wk.CreateFont();
font2.FontName = "仿宋"; //字体
font2.FontHeightInPoints = 9; //字号
ICellStyle cells2 = wk.CreateCellStyle();
cells2.SetFont(font2);
cells2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
cells2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cells2.VerticalAlignment = VerticalAlignment.CENTER;
for (int i = 0; i <= 18; i++)
{
rowhz.Cells[i].CellStyle = cells2;
}
CellRangeAddress range1 = new CellRangeAddress(dt.Rows.Count + 2, dt.Rows.Count + 2, 0, 16);
sheet0.AddMergedRegion(range1);
//CellRangeAddress range2 = new CellRangeAddress(dt.Rows.Count + 4, dt.Rows.Count + 4, 7, 17);
//sheet0.AddMergedRegion(range2);
//IRow rowqz1 = sheet0.CreateRow(dt.Rows.Count + 6);
//for (int i = 0; i <= 17; i++)
//{
// rowqz1.CreateCell(i);
//}
//rowqz1.GetCell(0).SetCellValue("编制负责人：");
//rowqz1.GetCell(2).SetCellValue(dr["ZDR"].ToString());
//rowqz1.GetCell(5).SetCellValue("申报单位/部门主管：");
//rowqz1.GetCell(7).SetCellValue(dr["SPR1"].ToString());
//rowqz1.GetCell(8).SetCellValue(dr["SPR1_JL"].ToString() == "y" ? "同意" : dr["SPR1_JL"].ToString() == "n" ? "不同意" : "");
//rowqz1.GetCell(12).SetCellValue("集团公司主管领导：");
//rowqz1.GetCell(14).SetCellValue(dr["SPR2"].ToString());
//rowqz1.GetCell(15).SetCellValue(dr["SPR2_JL"].ToString() == "y" ? "同意" : dr["SPR2_JL"].ToString() == "n" ? "不同意" : "");
//for (int i = 0; i <= 17; i++)
//{
// rowqz1.Cells[i].CellStyle = cells2;
//}
//CellRangeAddress range3 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 0, 1);
//sheet0.AddMergedRegion(range3);
////CellRangeAddress range4 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 3, 4);
////sheet0.AddMergedRegion(range4);
//CellRangeAddress range5 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 5, 6);
//sheet0.AddMergedRegion(range5);
////CellRangeAddress range6 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 9, 11);
////sheet0.AddMergedRegion(range6);
//CellRangeAddress range7 = new CellRangeAddress(dt.Rows.Count + 6, dt.Rows.Count + 6, 12, 13);
//sheet0.AddMergedRegion(range7);
//IRow rowqz2 = sheet0.CreateRow(dt.Rows.Count + 7);
//for (int i = 0; i <= 17; i++)
//{
// rowqz2.CreateCell(i);
//}
//rowqz2.GetCell(1).SetCellValue("日期：");
//rowqz2.GetCell(2).SetCellValue(dr["ZDR_SJ"].ToString());
//rowqz2.GetCell(6).SetCellValue("日期：");
//rowqz2.GetCell(7).SetCellValue(dr["SPR1_SJ"].ToString());
//rowqz2.GetCell(13).SetCellValue("日期：");
//rowqz2.GetCell(14).SetCellValue(dr["SPR2_SJ"].ToString());
//for (int i = 0; i <= 17; i++)
//{
// rowqz2.Cells[i].CellStyle = cells2;
//}
MemoryStream file = new MemoryStream();
wk.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
}
