public void TestWriteSheetFont()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
//ICell c = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
fnt.Color = (NPOI.HSSF.Util.HSSFColor.Red.Index);
fnt.Boldweight = (short)FontBoldWeight.Bold;
cs.SetFont(fnt);
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
r.RowStyle = (cs);
r.CreateCell(0);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
}
public void TestDoesNoHarmIfNothingToDo()
{
HSSFWorkbook wb = new HSSFWorkbook();
IFont f = wb.CreateFont();
f.FontName = ("Testing");
NPOI.SS.UserModel.ICellStyle s = wb.CreateCellStyle();
s.SetFont(f);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
// Optimise fonts
HSSFOptimiser.OptimiseFonts(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
// Optimise styles
HSSFOptimiser.OptimiseCellStyles(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
}
public void TestCloneStyleSameWB()
{
HSSFWorkbook wb = new HSSFWorkbook();
IFont fnt = wb.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(5, wb.NumberOfFonts);
NPOI.SS.UserModel.ICellStyle orig = wb.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = ((short)18);
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(fnt, orig.GetFont(wb));
Assert.AreEqual(18, orig.DataFormat);
NPOI.SS.UserModel.ICellStyle clone = wb.CreateCellStyle();
Assert.AreNotEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreNotEqual(fnt, clone.GetFont(wb));
Assert.AreNotEqual(18, clone.DataFormat);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(fnt, clone.GetFont(wb));
Assert.AreEqual(18, clone.DataFormat);
Assert.AreEqual(5, wb.NumberOfFonts);
orig.Alignment = HorizontalAlignment.Left;
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
}
/// <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
}
public void TestWriteSheetStyle()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetStyle",
".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs.BorderBottom = (BorderStyle.Thin);
cs.BorderLeft = (BorderStyle.Thin);
cs.BorderRight = (BorderStyle.Thin);
cs.BorderTop = (BorderStyle.Thin);
cs.FillForegroundColor = ( short )0xA;
cs.FillPattern = FillPattern.SolidForeground;
fnt.Color = ( short )0xf;
fnt.IsItalic = (true);
cs2.FillForegroundColor = ( short )0x0;
cs2.FillPattern = FillPattern.SolidForeground;
cs2.SetFont(fnt);
for (short rownum = ( short )0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
// r.SetRowNum(( short ) rownum);
for (short cellnum = ( short )0; cellnum < 50; cellnum += 2)
{
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ ((( double )rownum / 1000)
+ (( double )cellnum / 10000)));
c.CellStyle = (cs);
c = r.CreateCell(cellnum + 1);
c.SetCellValue("TEST");
c.CellStyle = (cs2);
}
}
wb.Write(out1);
out1.Close();
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
// assert((s.LastRowNum == 99));
}
public void TestWriteSheetFont()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetFont",
".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
fnt.Color = (NPOI.HSSF.Util.HSSFColor.Red.Index);
fnt.Boldweight = (short)FontBoldWeight.Bold;
cs.SetFont(fnt);
for (short rownum = ( short )0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
// r.SetRowNum(( short ) rownum);
for (short cellnum = ( short )0; cellnum < 50; cellnum += 2)
{
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ ((( double )rownum / 1000)
+ (( double )cellnum / 10000)));
c = r.CreateCell(cellnum + 1);
c.SetCellValue("TEST");
c.CellStyle = (cs);
}
}
wb.Write(out1);
out1.Close();
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
// assert((s.LastRowNum == 99));
}
public void TestCloneStyleDiffWB()
{
HSSFWorkbook wbOrig = new HSSFWorkbook();
IFont fnt = wbOrig.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(5, wbOrig.NumberOfFonts);
IDataFormat fmt = wbOrig.CreateDataFormat();
fmt.GetFormat("MadeUpOne");
fmt.GetFormat("MadeUpTwo");
NPOI.SS.UserModel.ICellStyle orig = wbOrig.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (fmt.GetFormat("Test##"));
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(fnt, orig.GetFont(wbOrig));
Assert.AreEqual(fmt.GetFormat("Test##"), orig.DataFormat);
// Now a style on another workbook
HSSFWorkbook wbClone = new HSSFWorkbook();
Assert.AreEqual(4, wbClone.NumberOfFonts);
IDataFormat fmtClone = wbClone.CreateDataFormat();
NPOI.SS.UserModel.ICellStyle clone = wbClone.CreateCellStyle();
Assert.AreEqual(4, wbClone.NumberOfFonts);
Assert.AreNotEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreNotEqual("TestingFont", clone.GetFont(wbClone).FontName);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual("TestingFont", clone.GetFont(wbClone).FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
Assert.AreNotEqual(fmtClone.GetFormat("Test##"), fmt.GetFormat("Test##"));
Assert.AreEqual(5, wbClone.NumberOfFonts);
}
public void TestDoesNoHarmIfNothingToDo()
{
HSSFWorkbook wb = new HSSFWorkbook();
// New files start with 4 built in fonts, and 21 built in styles
Assert.AreEqual(4, wb.NumberOfFonts);
Assert.AreEqual(21, wb.NumCellStyles);
// Create a test font and style, and use them
IFont f = wb.CreateFont();
f.FontName = ("Testing");
NPOI.SS.UserModel.ICellStyle s = wb.CreateCellStyle();
s.SetFont(f);
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
row.CreateCell(0).CellStyle = (s);
// Should have one more than the default of each
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
// Optimise fonts
HSSFOptimiser.OptimiseFonts(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
// Optimise styles
HSSFOptimiser.OptimiseCellStyles(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
}
/// <summary>
/// Sets the format properties of the given style based on the given map.
/// </summary>
/// <param name="style">The cell style</param>
/// <param name="workbook">The parent workbook.</param>
/// <param name="properties">The map of format properties (String -> Object).</param>
private static void SetFormatProperties(
NPOI.SS.UserModel.ICellStyle style, HSSFWorkbook workbook, Hashtable properties)
{
style.Alignment = (NPOI.SS.UserModel.HorizontalAlignment)GetShort(properties, ALIGNMENT);
style.BorderBottom = (NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_BOTTOM);
style.BorderLeft = (NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_LEFT);
style.BorderRight = (NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_RIGHT);
style.BorderTop = (NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_TOP);
style.BottomBorderColor = (GetShort(properties, BOTTOM_BORDER_COLOR));
style.DataFormat = (GetShort(properties, DATA_FORMAT));
style.FillBackgroundColor = (GetShort(properties, FILL_BACKGROUND_COLOR));
style.FillForegroundColor = (GetShort(properties, FILL_FOREGROUND_COLOR));
style.FillPattern = (NPOI.SS.UserModel.FillPattern)GetShort(properties, FILL_PATTERN);
style.SetFont(workbook.GetFontAt(GetShort(properties, FONT)));
style.IsHidden = (GetBoolean(properties, HIDDEN));
style.Indention = (GetShort(properties, INDENTION));
style.LeftBorderColor = (GetShort(properties, LEFT_BORDER_COLOR));
style.IsLocked = (GetBoolean(properties, LOCKED));
style.RightBorderColor = (GetShort(properties, RIGHT_BORDER_COLOR));
style.Rotation = (GetShort(properties, ROTATION));
style.TopBorderColor = (GetShort(properties, TOP_BORDER_COLOR));
style.VerticalAlignment = (NPOI.SS.UserModel.VerticalAlignment)GetShort(properties, VERTICAL_ALIGNMENT);
style.WrapText = (GetBoolean(properties, WRAP_TEXT));
}
/// <summary>
/// 将DataTable导出到Excel
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="fileName">仅文件名（非路径）</param>
/// <returns>返回Excel文件绝对路径</returns>
public void ExportDataSetToExcel(DataTable dt, string fileName, System.Web.HttpResponse Response)
{
#region 表头
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet(fileName);
hssfSheet.DefaultColumnWidth = 13;
hssfSheet.SetColumnWidth(0, 25 * 256);
hssfSheet.SetColumnWidth(3, 20 * 256);
// 表头
NPOI.SS.UserModel.IRow tagRow0 = hssfSheet.CreateRow(0);
tagRow0.Height = 40 * 40;
ICell cell0 = tagRow0.CreateCell(0);
//设置单元格内容
cell0.SetCellValue("力诺瑞特制造工厂");
hssfSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7));
NPOI.SS.UserModel.IRow tagRow1 = hssfSheet.CreateRow(1);
tagRow1.Height = 20 * 20;
ICell cell1 = tagRow1.CreateCell(0);
//设置单元格内容
cell1.SetCellValue("计划外领料单");
hssfSheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 7));
NPOI.SS.UserModel.ICellStyle tagStyle = hssfworkbook.CreateCellStyle();
tagStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle.VerticalAlignment = VerticalAlignment.Center;
//tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
//tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 16;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "宋体";
tagStyle.SetFont(font);//HEAD 样式
cell0.CellStyle = tagStyle;
NPOI.SS.UserModel.ICellStyle tagStyle1 = hssfworkbook.CreateCellStyle();
tagStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle1.VerticalAlignment = VerticalAlignment.Center;
cell1.CellStyle = tagStyle1;
// 标题样式
NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
#endregion
#region 表数据
// 表数据
for (int k = 0; k < 2; k++)
{
DataRow dr = dt.Rows[k];
NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2);
row.Height = 30 * 20;
for (int i = 0; i < dt.Columns.Count; i += 2)
{
row.CreateCell(i).SetCellValue(dr[i / 2].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
row.CreateCell(1).SetCellValue("");
row.GetCell(1).CellStyle = cellStyle;
row.CreateCell(3).SetCellValue("");
row.GetCell(3).CellStyle = cellStyle;
row.CreateCell(5).SetCellValue("");
row.GetCell(5).CellStyle = cellStyle;
row.CreateCell(7).SetCellValue("");
row.GetCell(7).CellStyle = cellStyle;
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 3));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 4, 5));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 6, 7));
}
// 表数据
for (int k = 2; k < dt.Rows.Count; k++)
{
if (k == dt.Rows.Count - 1)
{
DataRow drlast = dt.Rows[k];
NPOI.SS.UserModel.IRow rowlast = hssfSheet.CreateRow(k + 2);
rowlast.Height = 30 * 20;
for (int i = 0; i < dt.Columns.Count; i++)
{
rowlast.CreateCell(i).SetCellValue(drlast[i].ToString());
rowlast.GetCell(i).CellStyle = cellStyle;
}
//hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 1, 7));
}
else
{
DataRow dr = dt.Rows[k];
NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2);
row.Height = 30 * 20;
for (int i = 0; i < 4; i += 2)
{
row.CreateCell(i).SetCellValue(dr[i / 2].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 4; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dr[i - 2].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
row.CreateCell(1).SetCellValue("");
row.GetCell(1).CellStyle = cellStyle;
row.CreateCell(3).SetCellValue("");
row.GetCell(3).CellStyle = cellStyle;
#region 合并单元格
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 3));
#endregion
}
}
//NPOI.SS.UserModel.IRow tagRow5 = hssfSheet.CreateRow(dt.Rows.Count + 5);
//tagRow5.Height = 20 * 20;
//ICell cell12 = tagRow5.CreateCell(0);
////设置单元格内容报告
//cell12.SetCellValue("Rev: 1.0");
//hssfSheet.AddMergedRegion(new CellRangeAddress(dt.Rows.Count + 5, dt.Rows.Count + 5, 0, 7));
//cell12.CellStyle = TelNoStyle;
#endregion
hssfSheet.PrintSetup.NoColor = true;
hssfSheet.PrintSetup.Landscape = true;
hssfSheet.PrintSetup.PaperSize = (short)PaperSize.A4;
//是否自适应界面
hssfSheet.FitToPage = true;
string uploadPath = HttpContext.Current.Request.PhysicalApplicationPath + "Mfg/Temp/";
if (!Directory.Exists(uploadPath))
{
Directory.CreateDirectory(uploadPath);
}
FileStream file = new FileStream(uploadPath + fileName + ".xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
var basePath = VirtualPathUtility.AppendTrailingSlash(HttpContext.Current.Request.ApplicationPath);
//return (basePath + "Temp/" + fileName + ".xls");
string fileURL = HttpContext.Current.Server.MapPath((basePath + "Mfg/Temp/" + fileName + ".xls"));//文件路径，可用相对路径
FileInfo fileInfo = new FileInfo(fileURL);
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(fileInfo.Name.ToString())); //文件名
Response.AddHeader("content-length", fileInfo.Length.ToString()); //文件大小
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.WriteFile(fileURL);
}
/// <summary>
/// 将DataTable导出到Excel
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="fileName">仅文件名（非路径）</param>
/// <returns>返回Excel文件绝对路径</returns>
public void ExportDataSetToExcel(DataTable dt, string fileName, System.Web.HttpResponse Response)
{
#region 表头
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet(fileName);
hssfSheet.DefaultColumnWidth = 10;
hssfSheet.SetColumnWidth(0, 10 * 256);
hssfSheet.SetColumnWidth(3, 10 * 256);
// 表头
NPOI.SS.UserModel.IRow tagRow0 = hssfSheet.CreateRow(0);
tagRow0.Height = 40 * 40;
ICell cell0 = tagRow0.CreateCell(0);
//设置单元格内容
cell0.SetCellValue("力诺瑞特制造工厂");
hssfSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 13));
NPOI.SS.UserModel.IRow tagRow1 = hssfSheet.CreateRow(1);
tagRow1.Height = 30 * 20;
ICell cell1 = tagRow1.CreateCell(0);
//设置单元格内容
cell1.SetCellValue("反冲材料补料单");
hssfSheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 13));
NPOI.SS.UserModel.ICellStyle tagStyle = hssfworkbook.CreateCellStyle();
tagStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle.VerticalAlignment = VerticalAlignment.Center;
//tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
//tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 16;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "宋体";
tagStyle.SetFont(font);//HEAD 样式
cell0.CellStyle = tagStyle;
NPOI.SS.UserModel.ICellStyle tagStyle1 = hssfworkbook.CreateCellStyle();
tagStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle1.VerticalAlignment = VerticalAlignment.Center;
cell1.CellStyle = tagStyle1;
// 标题样式
NPOI.SS.UserModel.ICellStyle cellStyle1 = hssfworkbook.CreateCellStyle();
cellStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle1.VerticalAlignment = VerticalAlignment.Center;
cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.None;
cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
#endregion
//数据样式
NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
#region 表数据
// 表数据
for (int k = 0; k < 2; k++)
{
DataRow dr = dt.Rows[k];
NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2);
row.Height = 30 * 20;
for (int i = 0; i < 2; i += 2)
{
row.CreateCell(i).SetCellValue(dr[0].ToString());
row.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 2; i < 5; i += 3)
{
row.CreateCell(i).SetCellValue(dr[1].ToString());
row.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 5; i < 7; i += 2)
{
row.CreateCell(i).SetCellValue(dr[2].ToString());
row.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 7; i < 9; i += 2)
{
row.CreateCell(i).SetCellValue(dr[3].ToString());
row.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 9; i < 11; i += 2)
{
row.CreateCell(i).SetCellValue(dr[4].ToString());
row.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 11; i < 14; i += 3)
{
row.CreateCell(i).SetCellValue(dr[5].ToString());
row.GetCell(i).CellStyle = cellStyle1;
}
row.CreateCell(1).SetCellValue("");
row.GetCell(1).CellStyle = cellStyle1;
row.CreateCell(3).SetCellValue("");
row.GetCell(3).CellStyle = cellStyle1;
row.CreateCell(4).SetCellValue("");
row.GetCell(4).CellStyle = cellStyle1;
row.CreateCell(6).SetCellValue("");
row.GetCell(6).CellStyle = cellStyle1;
row.CreateCell(8).SetCellValue("");
row.GetCell(8).CellStyle = cellStyle1;
row.CreateCell(10).SetCellValue("");
row.GetCell(10).CellStyle = cellStyle1;
row.CreateCell(12).SetCellValue("");
row.GetCell(12).CellStyle = cellStyle1;
row.CreateCell(13).SetCellValue("");
row.GetCell(13).CellStyle = cellStyle1;
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 4));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 5, 6));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 7, 8));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 9, 10));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 11, 13));
}
// 表数据
for (int k = 2; k < dt.Rows.Count; k++)
{
if (k == dt.Rows.Count - 1)
{
DataRow drlast = dt.Rows[k];
NPOI.SS.UserModel.IRow rowlast = hssfSheet.CreateRow(k + 2);
rowlast.Height = 30 * 20;
for (int i = 0; i < 2; i++)
{
rowlast.CreateCell(i).SetCellValue(drlast[0].ToString());
rowlast.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 2; i < 4; i++)
{
rowlast.CreateCell(i).SetCellValue("");
rowlast.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 4; i < 6; i++)
{
rowlast.CreateCell(i).SetCellValue(drlast[2].ToString());
rowlast.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 6; i < 8; i++)
{
rowlast.CreateCell(i).SetCellValue("");
rowlast.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 8; i < 10; i++)
{
rowlast.CreateCell(i).SetCellValue(drlast[4].ToString());
rowlast.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 10; i < 14; i++)
{
rowlast.CreateCell(i).SetCellValue("");
rowlast.GetCell(i).CellStyle = cellStyle1;
}
//hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 1, 7));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 3));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 4, 5));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 6, 7));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 8, 9));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 10, 13));
}
else
{
DataRow dr = dt.Rows[k];
NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2);
row.Height = 30 * 20;
for (int i = 0; i < 1; i++)
{
row.CreateCell(i).SetCellValue(dr[i].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 1; i < 3; i++)
{
row.CreateCell(i).SetCellValue(dr[1].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 3; i < 6; i++)
{
row.CreateCell(i).SetCellValue(dr[2].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 6; i < 8; i++)
{
row.CreateCell(i).SetCellValue(dr[3].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 8; i < 10; i++)
{
row.CreateCell(i).SetCellValue(dr[4].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 10; i < 12; i++)
{
row.CreateCell(i).SetCellValue(dr[5].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 12; i < 14; i++)
{
row.CreateCell(i).SetCellValue(dr[6].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
row.CreateCell(2).SetCellValue("");
row.GetCell(2).CellStyle = cellStyle;
row.CreateCell(4).SetCellValue("");
row.GetCell(4).CellStyle = cellStyle;
row.CreateCell(5).SetCellValue("");
row.GetCell(5).CellStyle = cellStyle;
row.CreateCell(7).SetCellValue("");
row.GetCell(7).CellStyle = cellStyle;
row.CreateCell(9).SetCellValue("");
row.GetCell(9).CellStyle = cellStyle;
row.CreateCell(11).SetCellValue("");
row.GetCell(11).CellStyle = cellStyle;
row.CreateCell(13).SetCellValue("");
row.GetCell(13).CellStyle = cellStyle;
#region 合并单元格
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 1, 2));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 3, 5));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 6, 7));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 8, 9));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 10, 11));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 12, 13));
#endregion
}
}
#endregion
hssfSheet.PrintSetup.NoColor = true;
hssfSheet.PrintSetup.Landscape = true;
hssfSheet.PrintSetup.PaperSize = (short)PaperSize.A4;
//是否自适应界面
hssfSheet.FitToPage = true;
string uploadPath = HttpContext.Current.Request.PhysicalApplicationPath + "Mfg/Temp/";
if (!Directory.Exists(uploadPath))
{
Directory.CreateDirectory(uploadPath);
}
try
{
FileStream file = new FileStream(uploadPath + fileName + ".xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
var basePath = VirtualPathUtility.AppendTrailingSlash(HttpContext.Current.Request.ApplicationPath);
//return (basePath + "Temp/" + fileName + ".xls");
string fileURL = HttpContext.Current.Server.MapPath((basePath + "Mfg/Temp/" + fileName + ".xls"));//文件路径，可用相对路径
FileInfo fileInfo = new FileInfo(fileURL);
Response.Clear();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ELCO_ConnectionString"].ToString()))
{
SqlCommand cmd = new SqlCommand();
SqlTransaction transaction = null;
try
{
conn.Open();
transaction = conn.BeginTransaction();
cmd.Transaction = transaction;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
string str1 = string.Empty;
for (int i = 0; i < materialid.Length; i++)
{
str1 = "update MFG_WIP_BKF_MTL_Record set Status='3',ConfirmTime=GETDATE(),ConfirmUser='******',PrintTime=GETDATE() where ID=" + materialid[i];
cmd.CommandText = str1;
cmd.ExecuteNonQuery();
}
Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(fileInfo.Name.ToString())); //文件名
Response.AppendHeader("content-type", "application/x-msexcel");
Response.AddHeader("content-length", fileInfo.Length.ToString()); //文件大小
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.WriteFile(fileURL);
Response.Flush();
Response.Close();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
Response.Write(ex.Message);
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
public async Task <IActionResult> ExamRoomExportAsync([FromForm] IFormCollection fm)
{
string MODEL = fm["MODEL"].ToString();
string CLIENTNAME = fm["CLIENTNAME"].ToString();
string DATE1 = fm["DATE1"].ToString();
string DATE2 = fm["DATE2"].ToString();
string FileName = string.Empty;
string SheetName = string.Empty;
string[] temArr = { };
DataTable boundTable = new DataTable();
switch (MODEL)
{
case "2001":
SheetName = "生产日报";
FileName = SheetName + $"{DateTime.Now:yyyyMMddHHmmssfff}.xls";
string MO = fm["MO"].ToString();
string MANAGER = fm["MANAGER"].ToString();
string[] temArr1 = { "客户类型", "名称", "工单", "编码", "单价", "入库数量", "拉长", "在册", "借入", "借出", "缺勤", "实出勤", "工时", "入库日期", "创建人" };
temArr = temArr1;
IEnumerable <DA_GROSS> dTable = await _sqlRepository.GetGROSS(MO, CLIENTNAME, MANAGER, DATE1, DATE2);
IEnumerable <DA_GROSSDtos> dTableDtos = _mapper.Map <IEnumerable <DA_GROSSDtos> >(dTable);
boundTable = API_MES.Helper.DataTableExtensions.ToDataTable(dTableDtos);
break;
case "1001":
SheetName = "其他收入";
string MOD = fm["MOD"].ToString();
FileName = SheetName + $"{DateTime.Now:yyyyMMddHHmmssfff}.xls";
string[] temArr2 = { "客户类型", "入库类型", "入库数量", "入库日期", "创建人" };
temArr = temArr2;
IEnumerable <DA_ELSEGROSS> dTable1 = await _sqlRepository.GetELSEGROSS(CLIENTNAME, MOD, DATE1, DATE2);
IEnumerable <DA_ELSEGROSSDtos1> dTableDtos1 = _mapper.Map <IEnumerable <DA_ELSEGROSSDtos1> >(dTable1);
boundTable = API_MES.Helper.DataTableExtensions.ToDataTable(dTableDtos1);
break;
case "3001":
SheetName = "人员支出";
FileName = SheetName + $"{DateTime.Now:yyyyMMddHHmmssfff}.xls";
string[] temArr3 = { "客户类型", "支出类型", "间接人员出勤数", "间接人员月薪", "直接人员时薪", "支出日期", "创建人", "备注" };
temArr = temArr3;
IEnumerable <DA_PAYPERSON> dTable2 = await _sqlRepository.GetPAYPERSON(CLIENTNAME, MODEL, DATE1, DATE2);
IEnumerable <DA_PAYPERSONDtos1> dTableDtos2 = _mapper.Map <IEnumerable <DA_PAYPERSONDtos1> >(dTable2);
boundTable = API_MES.Helper.DataTableExtensions.ToDataTable(dTableDtos2);
break;
case "3002":
SheetName = "生产辅料消耗";
FileName = SheetName + $"{DateTime.Now:yyyyMMddHHmmssfff}.xls";
string[] temArr4 = { "客户类型", "支出类型", "编码", "单价", "消耗", "支出日期", "创建人", "备注" };
temArr = temArr4;
IEnumerable <DA_PAYLOSS> dTable4 = await _sqlRepository.GetPAYLOSS(CLIENTNAME, MODEL, DATE1, DATE2);
IEnumerable <DA_PAYLOSSDtos1> dTableDtos4 = _mapper.Map <IEnumerable <DA_PAYLOSSDtos1> >(dTable4);
boundTable = API_MES.Helper.DataTableExtensions.ToDataTable(dTableDtos4);
break;
case "3003":
SheetName = "低值易耗消耗";
FileName = SheetName + $"{DateTime.Now:yyyyMMddHHmmssfff}.xls";
string[] temArr5 = { "客户类型", "支出类型", "编码", "单价", "消耗", "支出日期", "创建人", "备注" };
temArr = temArr5;
IEnumerable <DA_PAYLOSS> dTable5 = await _sqlRepository.GetPAYLOSS(CLIENTNAME, MODEL, DATE1, DATE2);
IEnumerable <DA_PAYLOSSDtos1> dTableDtos5 = _mapper.Map <IEnumerable <DA_PAYLOSSDtos1> >(dTable5);
boundTable = API_MES.Helper.DataTableExtensions.ToDataTable(dTableDtos5);
break;
case "3004":
SheetName = "超损物料损耗";
FileName = SheetName + $"{DateTime.Now:yyyyMMddHHmmssfff}.xls";
string[] temArr6 = { "客户类型", "支出类型", "编码", "单价", "消耗", "支出日期", "创建人", "备注" };
temArr = temArr6;
IEnumerable <DA_PAYLOSS> dTable6 = await _sqlRepository.GetPAYLOSS(CLIENTNAME, MODEL, DATE1, DATE2);
IEnumerable <DA_PAYLOSSDtos1> dTableDtos6 = _mapper.Map <IEnumerable <DA_PAYLOSSDtos1> >(dTable6);
boundTable = API_MES.Helper.DataTableExtensions.ToDataTable(dTableDtos6);
break;
case "3005":
SheetName = "平台部门费用支出";
FileName = SheetName + $"{DateTime.Now:yyyyMMddHHmmssfff}.xls";
string[] temArr7 = { "客户类型", "支出类型", "支出", "支出日期", "创建人", "备注" };
temArr = temArr7;
IEnumerable <DA_PAYMONTH> dTable7 = await _sqlRepository.GetPAYMONTH(CLIENTNAME, MODEL, DATE1, DATE2);
IEnumerable <DA_PAYMONTHDtos1> dTableDtos7 = _mapper.Map <IEnumerable <DA_PAYMONTHDtos1> >(dTable7);
boundTable = API_MES.Helper.DataTableExtensions.ToDataTable(dTableDtos7);
break;
case "3006":
SheetName = "公摊费用支出";
FileName = SheetName + $"{DateTime.Now:yyyyMMddHHmmssfff}.xls";
string[] temArr8 = { "客户类型", "支出类型", "支出", "支出日期", "创建人", "备注" };
temArr = temArr8;
IEnumerable <DA_PAYMONTH> dTable8 = await _sqlRepository.GetPAYMONTH(CLIENTNAME, MODEL, DATE1, DATE2);
IEnumerable <DA_PAYMONTHDtos1> dTableDtos8 = _mapper.Map <IEnumerable <DA_PAYMONTHDtos1> >(dTable8);
boundTable = API_MES.Helper.DataTableExtensions.ToDataTable(dTableDtos8);
break;
case "3007":
SheetName = "其他支出";
FileName = SheetName + $"{DateTime.Now:yyyyMMddHHmmssfff}.xls";
string[] temArr9 = { "客户类型", "支出类型", "支出", "支出日期", "创建人", "备注" };
temArr = temArr9;
IEnumerable <DA_PAYMONTH> dTable9 = await _sqlRepository.GetPAYMONTH(CLIENTNAME, MODEL, DATE1, DATE2);
IEnumerable <DA_PAYMONTHDtos1> dTableDtos9 = _mapper.Map <IEnumerable <DA_PAYMONTHDtos1> >(dTable9);
boundTable = API_MES.Helper.DataTableExtensions.ToDataTable(dTableDtos9);
break;
default:
break;
}
List <string> lstTitle = new List <string>(temArr);
IWorkbook book = new HSSFWorkbook();
//ISheet sheet = book.CreateSheet("Sheet1");
ISheet sheet = book.CreateSheet(SheetName);
IRow rowTitle = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
NPOI.SS.UserModel.ICellStyle headStyle = book.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = book.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < lstTitle.Count; i++)
{
rowTitle.CreateCell(i).SetCellValue(lstTitle[i]);
rowTitle.GetCell(i).CellStyle = headStyle;
}
for (int i = 0; i < boundTable.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
DataRow dr = boundTable.Rows[i];
for (int j = 0; j < boundTable.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
cell.CellStyle = style;
cell.SetCellValue(dr[j].ToString());
}
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
//return File(ms, "application/vnd.ms-excel", "考场总表导出.xls");
return(File(ms, "application/vnd.ms-excel", FileName));
}
public void TestOptimiseFonts()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Add 6 fonts, some duplicates
IFont f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
IFont f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
IFont f3 = wb.CreateFont();
f3.FontHeight = ((short)33);
f3.FontName = ("Unique");
IFont f4 = wb.CreateFont();
f4.FontHeight = ((short)11);
f4.FontName = ("Testing");
IFont f5 = wb.CreateFont();
f5.FontHeight = ((short)22);
f5.FontName = ("Also Testing");
IFont f6 = wb.CreateFont();
f6.FontHeight = ((short)66);
f6.FontName = ("Also Unique");
// Use all three of the four in cell styles
Assert.AreEqual(21, wb.NumCellStyles);
NPOI.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
Assert.AreEqual(5, cs1.FontIndex);
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f4);
Assert.AreEqual(8, cs2.FontIndex);
NPOI.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f5);
Assert.AreEqual(9, cs3.FontIndex);
NPOI.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f6);
Assert.AreEqual(10, cs4.FontIndex);
Assert.AreEqual(25, wb.NumCellStyles);
// And three in rich text
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
HSSFRichTextString rtr1 = new HSSFRichTextString("Test");
rtr1.ApplyFont(0, 2, f1);
rtr1.ApplyFont(3, 4, f2);
r.CreateCell(0).SetCellValue(rtr1);
HSSFRichTextString rtr2 = new HSSFRichTextString("AlsoTest");
rtr2.ApplyFont(0, 2, f3);
rtr2.ApplyFont(3, 5, f5);
rtr2.ApplyFont(6, 8, f6);
r.CreateCell(1).SetCellValue(rtr2);
// Check what we have now
Assert.AreEqual(10, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// Optimise
HSSFOptimiser.OptimiseFonts(wb);
// Check font count
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// Check font use in cell styles
Assert.AreEqual(5, cs1.FontIndex);
Assert.AreEqual(5, cs2.FontIndex); // duplicate of 1
Assert.AreEqual(6, cs3.FontIndex); // duplicate of 2
Assert.AreEqual(8, cs4.FontIndex); // two have gone
// And in rich text
// RTR 1 had f1 and f2, unchanged
Assert.AreEqual(5, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(0));
Assert.AreEqual(5, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(1));
Assert.AreEqual(6, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(3));
Assert.AreEqual(6, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(4));
// RTR 2 had f3 (unchanged), f5 (=f2) and f6 (moved down)
Assert.AreEqual(7, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(0));
Assert.AreEqual(7, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(1));
Assert.AreEqual(6, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(3));
Assert.AreEqual(6, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(4));
Assert.AreEqual(8, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(6));
Assert.AreEqual(8, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(7));
}
public void TestOptimiseStyles()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Two fonts
Assert.AreEqual(4, wb.NumberOfFonts);
IFont f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
IFont f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
Assert.AreEqual(6, wb.NumberOfFonts);
// Several styles
Assert.AreEqual(21, wb.NumCellStyles);
NPOI.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f2);
NPOI.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f1);
NPOI.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f1);
cs4.Alignment = HorizontalAlignment.CenterSelection;// ((short)22);
NPOI.SS.UserModel.ICellStyle cs5 = wb.CreateCellStyle();
cs5.SetFont(f2);
cs5.Alignment = HorizontalAlignment.Fill; //((short)111);
NPOI.SS.UserModel.ICellStyle cs6 = wb.CreateCellStyle();
cs6.SetFont(f2);
Assert.AreEqual(27, wb.NumCellStyles);
// Use them
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
r.CreateCell(0).CellStyle = (cs1);
r.CreateCell(1).CellStyle = (cs2);
r.CreateCell(2).CellStyle = (cs3);
r.CreateCell(3).CellStyle = (cs4);
r.CreateCell(4).CellStyle = (cs5);
r.CreateCell(5).CellStyle = (cs6);
r.CreateCell(6).CellStyle = (cs1);
r.CreateCell(7).CellStyle = (cs2);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
Assert.AreEqual(26, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// Optimise
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(6, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(1)).CellValueRecord.XFIndex);
Assert.AreEqual(22, r.GetCell(1).CellStyle.GetFont(wb).FontHeight);
// cs3 = cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(2)).CellValueRecord.XFIndex);
// cs4 --> 24 -> 23
Assert.AreEqual(23, ((HSSFCell)r.GetCell(3)).CellValueRecord.XFIndex);
// cs5 --> 25 -> 24
Assert.AreEqual(24, ((HSSFCell)r.GetCell(4)).CellValueRecord.XFIndex);
// cs6 = cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(7)).CellValueRecord.XFIndex);
// Add a new duplicate, and two that aren't used
HSSFCellStyle csD = (HSSFCellStyle)wb.CreateCellStyle();
csD.SetFont(f1);
r.CreateCell(8).CellStyle = (csD);
HSSFFont f3 = (HSSFFont)wb.CreateFont();
f3.FontHeight = ((short)23);
f3.FontName = ("Testing 3");
HSSFFont f4 = (HSSFFont)wb.CreateFont();
f4.FontHeight = ((short)24);
f4.FontName = ("Testing 4");
HSSFCellStyle csU1 = (HSSFCellStyle)wb.CreateCellStyle();
csU1.SetFont(f3);
HSSFCellStyle csU2 = (HSSFCellStyle)wb.CreateCellStyle();
csU2.SetFont(f4);
// Check before the optimise
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(28, wb.NumCellStyles);
// Optimise, should remove the two un-used ones and the one duplicate
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// csD -> cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(8)).CellValueRecord.XFIndex);
}
/// <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);
}
}
/// <summary>
/// 将DataTable导出到Excel
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="fileName">仅文件名（非路径）</param>
/// <returns>返回Excel文件绝对路径</returns>
public void ExportDataSetToExcel(DataTable dt, string fileName, System.Web.HttpResponse Response)
{
int dtcolunmnum = dt.Columns.Count;
#region 表头
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet(fileName);
hssfSheet.DefaultColumnWidth = 13;
//hssfSheet.SetColumnWidth(0, 20 * 256);
//hssfSheet.SetColumnWidth(3, 20 * 256);
// 表头
NPOI.SS.UserModel.IRow tagRow0 = hssfSheet.CreateRow(0);
tagRow0.Height = 40 * 40;
ICell cell0 = tagRow0.CreateCell(0);
//设置单元格内容
cell0.SetCellValue("力诺瑞特制造工厂");
hssfSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtcolunmnum - 1));
NPOI.SS.UserModel.IRow tagRow1 = hssfSheet.CreateRow(1);
tagRow1.Height = 30 * 20;
ICell cell1 = tagRow1.CreateCell(0);
//设置单元格内容
cell1.SetCellValue(fileName);
hssfSheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, dtcolunmnum - 1));
// 标题样式
NPOI.SS.UserModel.ICellStyle tagStyle = hssfworkbook.CreateCellStyle();
tagStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 16;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "宋体";
tagStyle.SetFont(font);//HEAD 样式
cell0.CellStyle = tagStyle;
NPOI.SS.UserModel.ICellStyle tagStyle1 = hssfworkbook.CreateCellStyle();
tagStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle1.VerticalAlignment = VerticalAlignment.Center;
cell1.CellStyle = tagStyle1;
NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
#endregion
// 表数据
for (int k = 0; k < dt.Rows.Count; k++)
{
DataRow drlast = dt.Rows[k];
NPOI.SS.UserModel.IRow rowlast = hssfSheet.CreateRow(k + 2);
rowlast.Height = 30 * 20;
for (int i = 0; i < dt.Columns.Count; i++)
{
rowlast.CreateCell(i).SetCellValue(drlast[i].ToString());
rowlast.GetCell(i).CellStyle = cellStyle;
}
}
// 表数据
//for (int k = 0; k < dt.Rows.Count; k++)
//{
// hssfSheet.AutoSizeColumn(k);
//}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum <= dt.Columns.Count; columnNum++)
{
int columnWidth = hssfSheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= hssfSheet.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (hssfSheet.GetRow(rowNum) == null)
{
currentRow = hssfSheet.CreateRow(rowNum);
}
else
{
currentRow = hssfSheet.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;
}
}
}
hssfSheet.SetColumnWidth(columnNum, columnWidth * 256);
}
hssfSheet.PrintSetup.NoColor = true;
hssfSheet.PrintSetup.Landscape = true;
hssfSheet.PrintSetup.PaperSize = (short)PaperSize.A4;
//是否自适应界面
hssfSheet.FitToPage = true;
//保存excel文件
string uploadPath = HttpContext.Current.Request.PhysicalApplicationPath + "Report/Temp/";
if (!Directory.Exists(uploadPath))
{
Directory.CreateDirectory(uploadPath);
}
FileStream file = new FileStream(uploadPath + fileName + ".xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
var basePath = VirtualPathUtility.AppendTrailingSlash(HttpContext.Current.Request.ApplicationPath);
//return (basePath + "Temp/" + fileName + ".xls");
string fileURL = HttpContext.Current.Server.MapPath((basePath + "Report/Temp/" + fileName + ".xls"));//文件路径，可用相对路径
FileInfo fileInfo = new FileInfo(fileURL);
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(fileInfo.Name.ToString())); //文件名
Response.AddHeader("content-length", fileInfo.Length.ToString()); //文件大小
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.WriteFile(fileURL);
}
public void TestWriteSheetStyle()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs.BorderBottom = (BorderStyle.Thin);
cs.BorderLeft = (BorderStyle.Thin);
cs.BorderRight = (BorderStyle.Thin);
cs.BorderTop = (BorderStyle.Thin);
cs.FillForegroundColor = ((short)0xA);
cs.FillPattern = FillPatternType.SolidForeground;
fnt.Color = ((short)0xf);
fnt.IsItalic = (true);
cs2.FillForegroundColor = ((short)0x0);
cs2.FillPattern = FillPatternType.SolidForeground;
cs2.SetFont(fnt);
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
r.RowStyle = (cs);
r.CreateCell(0);
rownum++;
if (rownum >= 100)
{
break; // I feel too lazy to Check if this isreqd :-/
}
r = s.CreateRow(rownum);
r.RowStyle = (cs2);
r.CreateCell(0);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
s = wb.GetSheetAt(0);
Assert.IsNotNull(s, "Sheet is not null");
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.GetRow(rownum);
Assert.IsNotNull(r, "Row is not null");
cs = r.RowStyle;
Assert.AreEqual(cs.BorderBottom, BorderStyle.Thin, "Bottom Border Style for row: ");
Assert.AreEqual(cs.BorderLeft, BorderStyle.Thin, "Left Border Style for row: ");
Assert.AreEqual(cs.BorderRight, BorderStyle.Thin, "Right Border Style for row: ");
Assert.AreEqual(cs.BorderTop, BorderStyle.Thin, "Top Border Style for row: ");
Assert.AreEqual(0xA, cs.FillForegroundColor, "FillForegroundColor for row: ");
Assert.AreEqual((short)0x1, (short)cs.FillPattern, "FillPattern for row: ");
rownum++;
if (rownum >= 100)
{
break; // I feel too lazy to Check if this isreqd :-/
}
r = s.GetRow(rownum);
Assert.IsNotNull(r, "Row is not null");
cs2 = r.RowStyle;
Assert.AreEqual(cs2.FillForegroundColor, (short)0x0, "FillForegroundColor for row: ");
Assert.AreEqual((short)cs2.FillPattern, (short)0x1, "FillPattern for row: ");
}
}
private string GenerateExcelReport(int report, string savePath)
{
/// excel writer row index
int rowIndex = 0;
try
{
NPOI.XSSF.UserModel.XSSFWorkbook workbook =
new XSSFWorkbook();
NPOI.SS.UserModel.ISheet wkst = workbook.CreateSheet();
workbook.SetActiveSheet(0);
// sets up basic styles for Excel
NPOI.SS.UserModel.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.VerticalAlignment = VerticalAlignment.Center;
HeaderCellStyle.Alignment = HorizontalAlignment.Center;
HeaderCellStyle.FillPattern = FillPattern.SolidForeground;
HeaderCellStyle.FillForegroundColor = IndexedColors.LightCornflowerBlue.Index;
IFont headerFont = workbook.CreateFont();
headerFont.Boldweight = (short)FontBoldWeight.Bold;
HeaderCellStyle.SetFont(headerFont);
NPOI.SS.UserModel.ICellStyle DataCellStyle = workbook.CreateCellStyle();
DataCellStyle.VerticalAlignment = VerticalAlignment.Top;
DataCellStyle.WrapText = true;
ArrayList query_for_status = new ArrayList();
string query_columns = string.Empty,
ignore_columns = "id,cohort_id,status_timestamp,published",
column_headers = string.Empty;
switch (report)
{
case 0:
query_columns = "id,cohort_id,cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status,status_timestamp";
query_for_status.Add("pending");
query_for_status.Add("rejected");
query_for_status.Add("inprogress");
ignore_columns = "id,cohort_id";
column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Received,CEDCD Form Reviewed,Date CEDCD Form Published";
break;
case 1:
query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status,status_timestamp";
query_for_status.Add("pending");
query_for_status.Add("rejected");
query_for_status.Add("inprogress");
ignore_columns = string.Empty;
column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Current Status,Current Status Date";
break;
case 2:
query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status_timestamp";
query_for_status.Add("published");
ignore_columns = string.Empty;
column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Published";
break;
case 3:
query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status_timestamp [Date CEDCD Form was Unpublished]";
query_for_status.Add("unpublished");
ignore_columns = string.Empty;
column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Unpublished";
break;
case 4:
query_columns = "*";
query_for_status.Add("published");
query_for_status.Add("pending");
break;
default:
query_columns = "*";
query_for_status.Add("published");
break;
}
DataTable dt_records = CECWebSrv.GetCohortsWithStatusesWithColumns(UserToken, (string[])query_for_status.ToArray(typeof(string)), query_columns);
/// write header to excel
///
NPOI.SS.UserModel.IRow dataRow = wkst.CreateRow(rowIndex++);
//dataRow.CreateCell(0).SetCellValue(String.Format("Cohort Data Export Generated from the CEDCD Website ({0})", Request.Url.Authority));
//dataRow = wkst.CreateRow(rowIndex++);
//dataRow.CreateCell(0).SetCellValue("Export Date:");
//dataRow.CreateCell(1).SetCellValue(DateTime.Now.ToString("MM/dd/yyyy"));
//rowIndex += 2;
int colPos = 0;
///--------------------------------------------------------
/// column headers
///
NPOI.SS.UserModel.IRow headerRow = wkst.CreateRow(rowIndex++);
if (column_headers != string.Empty)
{
foreach (string s in column_headers.Split(','))
{
ICell c = headerRow.GetCell(colPos++, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(s);
c.CellStyle = HeaderCellStyle;
}
}
else
{
using (CECHarmPublicService ps = new CECHarmPublicService())
{
for (int _c = 0; _c < dt_records.Columns.Count; _c++)
{
if (ignore_columns.Contains(dt_records.Columns[_c].ColumnName))
{
continue;
}
ICell c = headerRow.GetCell(colPos);
if (c == null)
{
c = headerRow.CreateCell(colPos);
}
string column_name = ps.GetCohortWebFieldLabelByColumnName(UserToken, dt_records.Columns[_c].ColumnName);
if (String.IsNullOrWhiteSpace(column_name))
{
column_name = dt_records.Columns[_c].ColumnName;
}
c.SetCellValue(column_name);
c.CellStyle = HeaderCellStyle;
colPos++;
}
}
}
if (report != 0 && dt_records.Rows.Count >= 1)
{
/// data rows
for (int _i = 0; _i < dt_records.Rows.Count; _i++)
{
colPos = 0;
/// create data row object then step through each cell to populate the excel row
dataRow = wkst.CreateRow(rowIndex++);
for (int _p = 0; _p < dt_records.Columns.Count; _p++)
{
if (ignore_columns.Contains(dt_records.Columns[_p].ColumnName))
{
continue;
}
/// get first cell and check for null, if null create cell
ICell c = dataRow.GetCell(colPos);
if (c == null)
{
c = dataRow.CreateCell(colPos);
}
string cellVal = dt_records.Rows[_i][_p].ToString();
if (helper.IsStringEmptyWhiteSpace(cellVal) || cellVal == " " || cellVal == "-1")
{
cellVal = "N/P";
c.SetCellValue(cellVal);
}
else if (dt_records.Columns[_p].DataType == typeof(DateTime))
{
c.SetCellValue(DateTime.Parse(cellVal).ToString("MM/dd/yyyy"));
}
else if (dt_records.Columns[_p].ColumnName == "status")
{
switch (cellVal)
{
case "inprogress":
c.SetCellValue("Draft In Progress");
break;
case "pending":
c.SetCellValue("Under NCI Review");
break;
case "rejected":
c.SetCellValue("Returned to Cohort");
break;
default:
c.SetCellValue(cellVal);
break;
}
}
else
{
c.SetCellValue(cellVal);
}
c.CellStyle = DataCellStyle;
colPos++;
}
}
}
else if (report == 0 && dt_records.Rows.Count >= 1)
{
foreach (DataRow dr in dt_records.Rows)
{
dataRow = wkst.CreateRow(rowIndex++);
// cohort acronym
ICell c = dataRow.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["cohort_acronym"].ToString());
c.CellStyle = DataCellStyle;
// cohort name
c = dataRow.GetCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["cohort_name"].ToString());
c.CellStyle = DataCellStyle;
// pi name
c = dataRow.GetCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["pi_name_1"].ToString());
c.CellStyle = DataCellStyle;
// pi institution
c = dataRow.GetCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["pi_institution_1"].ToString());
c.CellStyle = DataCellStyle;
// pi email
c = dataRow.GetCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(dr["pi_email_1"].ToString());
c.CellStyle = DataCellStyle;
// the more complicated stuff...
if (dr["status"].ToString() == "pending")
{
c = dataRow.GetCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(((DateTime)dr["status_timestamp"]).ToString("MM/dd/yyyy"));
c.CellStyle = DataCellStyle;
c = dataRow.GetCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue("No");
c.CellStyle = DataCellStyle;
using (DataTable dt_temp = CECWebSrv.GetCohortRecordById(UserToken, (int)dr["cohort_id"], false))
{
if (dt_temp.Rows.Count > 0)
{
c = dataRow.GetCell(7, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(((DateTime)dt_temp.Rows[0]["status_timestamp"]).ToString("MM/dd/yyyy"));
c.CellStyle = DataCellStyle;
}
}
}
else if (dr["status"].ToString() == "rejected" || dr["status"].ToString() == "inprogress")
{
c = dataRow.GetCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK);
if (dr["status"].ToString() == "rejected")
{
using (DataTable dt_temp = (new CECHarmPublicService()).AuditLog_GetActivities(UserToken, "submitted", (int)dr["id"]))
{
if (dt_temp.Rows.Count == 0)
{
c.SetCellValue(" ");
}
else
{
c.SetCellValue(((DateTime)dt_temp.Rows[0]["create_date"]).ToString("MM/dd/yyyy"));
}
}
}
else
{
c.SetCellValue(((DateTime)dr["status_timestamp"]).ToString("MM/dd/yyyy"));
}
c.CellStyle = DataCellStyle;
c = dataRow.GetCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue("Pending Revisions");
c.CellStyle = DataCellStyle;
using (DataTable dt_temp = CECWebSrv.GetCohortRecordById(UserToken, (int)dr["cohort_id"], false))
{
if (dt_temp.Rows.Count > 0)
{
c = dataRow.GetCell(7, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(((DateTime)dt_temp.Rows[0]["status_timestamp"]).ToString("MM/dd/yyyy"));
c.CellStyle = DataCellStyle;
}
}
}
}
}
else
{
ICell c = wkst.CreateRow(rowIndex++).GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
c.SetCellValue(String.Format("Nothing to report"));
}
for (int _ic = 0; _ic <= headerRow.PhysicalNumberOfCells; _ic++)
{
wkst.AutoSizeColumn(_ic);
}
/// write output
FileStream fs = new FileStream(savePath, FileMode.Create);
workbook.Write(fs);
fs.Close();
return(savePath);
}
catch (Exception ex)
{
throw ex;
}
}
public void TestOptimiseStyles()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Two fonts
Assert.AreEqual(4, wb.NumberOfFonts);
IFont f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
IFont f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
Assert.AreEqual(6, wb.NumberOfFonts);
// Several styles
Assert.AreEqual(21, wb.NumCellStyles);
NPOI.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f2);
NPOI.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f1);
NPOI.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f1);
cs4.Alignment = HorizontalAlignment.CENTER_SELECTION;// ((short)22);
NPOI.SS.UserModel.ICellStyle cs5 = wb.CreateCellStyle();
cs5.SetFont(f2);
cs5.Alignment = HorizontalAlignment.FILL; //((short)111);
NPOI.SS.UserModel.ICellStyle cs6 = wb.CreateCellStyle();
cs6.SetFont(f2);
Assert.AreEqual(27, wb.NumCellStyles);
// Use them
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
r.CreateCell(0).CellStyle = (cs1);
r.CreateCell(1).CellStyle = (cs2);
r.CreateCell(2).CellStyle = (cs3);
r.CreateCell(3).CellStyle = (cs4);
r.CreateCell(4).CellStyle = (cs5);
r.CreateCell(5).CellStyle = (cs6);
r.CreateCell(6).CellStyle = (cs1);
r.CreateCell(7).CellStyle = (cs2);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
Assert.AreEqual(26, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// Optimise
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(6, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(1)).CellValueRecord.XFIndex);
// cs3 = cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(2)).CellValueRecord.XFIndex);
// cs4 --> 24 -> 23
Assert.AreEqual(23, ((HSSFCell)r.GetCell(3)).CellValueRecord.XFIndex);
// cs5 --> 25 -> 24
Assert.AreEqual(24, ((HSSFCell)r.GetCell(4)).CellValueRecord.XFIndex);
// cs6 = cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(7)).CellValueRecord.XFIndex);
}