public WorkbookExcelRender()
{
_workbook = new HSSFWorkbook();
_isDisposed = false;
OddCellStyle = _workbook.CreateCellStyle();
EvenCellStyle = _workbook.CreateCellStyle();
OddCellStyleCenterAligned = _workbook.CreateCellStyle();
EvenCellStyleCenterAligned = _workbook.CreateCellStyle();
_nextCellIndex = -1;
RTFHelper = new RTFHelper()
{
NegFont = _workbook.CreateFont(),
NormalFont = _workbook.CreateFont(),
PosFont = _workbook.CreateFont(),
NegFontCrossed = _workbook.CreateFont(),
NormalFontCrossed = _workbook.CreateFont(),
PosFontCrossed = _workbook.CreateFont(),
Pallete = _workbook.GetCustomPalette(),
RTFRenderer = new RtfTextRender()
};
InitDefaultRTFHelper();
InitDefaultCellStyles();
}
/// <summary>
/// Lookup RGB from .NET system colour in Excel pallete - or nearest match.
/// </summary>
public static short GetXLColor(HSSFWorkbook xlWorkbook, Color color)
{
if (color == Color.Empty) {
// COLOR_NORMAL=unspecified.
return HSSFColor.COLOR_NORMAL;
}
HSSFPalette XlPalette = xlWorkbook.GetCustomPalette();
HSSFColor XlColour = XlPalette.FindColor(color.R, color.G, color.B);
if (XlColour == null) {
XlColour = XlPalette.FindSimilarColor(color.R, color.G, color.B);
return XlColour.GetIndex();
} else {
return XlColour.GetIndex();
}
}
public void TestCustomPalette()
{
//reading sample xls
HSSFWorkbook book = HSSFTestDataSamples.OpenSampleWorkbook("Simple.xls");
//creating custom palette
HSSFPalette palette = book.GetCustomPalette();
palette.SetColorAtIndex((short)0x12, (byte)101, (byte)230, (byte)100);
palette.SetColorAtIndex((short)0x3b, (byte)0, (byte)255, (byte)52);
//writing to disk; reading in and verifying palette
string tmppath = TempFile.GetTempFilePath("TestCustomPalette", ".xls");
FileStream fos = new FileStream(tmppath, FileMode.OpenOrCreate);
book.Write(fos);
fos.Close();
FileStream fis = new FileStream(tmppath, FileMode.Open,FileAccess.Read);
book = new HSSFWorkbook(fis);
fis.Close();
palette = book.GetCustomPalette();
HSSFColor color = palette.GetColor(HSSFColor.CORAL.index); //unmodified
Assert.IsNotNull(color, "Unexpected null in custom palette (unmodified index)");
short[] expectedRGB = HSSFColor.CORAL.triplet;
short[] actualRGB = color.GetTriplet();
String msg = "Expected palette position to remain unmodified";
Assert.AreEqual(expectedRGB[0], actualRGB[0], msg);
Assert.AreEqual(expectedRGB[1], actualRGB[1], msg);
Assert.AreEqual(expectedRGB[2], actualRGB[2], msg);
color = palette.GetColor((short)0x12);
Assert.IsNotNull(color, "Unexpected null in custom palette (modified)");
actualRGB = color.GetTriplet();
msg = "Expected palette modification to be preserved across save";
Assert.AreEqual((short)101, actualRGB[0], msg);
Assert.AreEqual((short)230, actualRGB[1], msg);
Assert.AreEqual((short)100, actualRGB[2], msg);
}
public void TestFindSimilar()
{
HSSFWorkbook book = new HSSFWorkbook();
HSSFPalette p = book.GetCustomPalette();
// Add a few edge colours in
p.SetColorAtIndex((short)8, unchecked((byte)-1), (byte)0, (byte)0);
p.SetColorAtIndex((short)9, (byte)0, unchecked((byte)-1), (byte)0);
p.SetColorAtIndex((short)10, (byte)0, (byte)0, unchecked((byte)-1));
// And some near a few of them
p.SetColorAtIndex((short)11, unchecked((byte)-1), (byte)2, (byte)2);
p.SetColorAtIndex((short)12, unchecked((byte)-2), (byte)2, (byte)10);
p.SetColorAtIndex((short)13, unchecked((byte)-4), (byte)0, (byte)0);
p.SetColorAtIndex((short)14, unchecked((byte)-8), (byte)0, (byte)0);
Assert.AreEqual(
"FFFF:0:0", p.GetColor((short)8).GetHexString()
);
// Now Check we get the right stuff back
Assert.AreEqual(
p.GetColor((short)8).GetHexString(),
p.FindSimilarColor(unchecked((byte)-1), (byte)0, (byte)0).GetHexString()
);
Assert.AreEqual(
p.GetColor((short)8).GetHexString(),
p.FindSimilarColor(unchecked((byte)-2), (byte)0, (byte)0).GetHexString()
);
Assert.AreEqual(
p.GetColor((short)8).GetHexString(),
p.FindSimilarColor(unchecked((byte)-1), (byte)1, (byte)0).GetHexString()
);
Assert.AreEqual(
p.GetColor((short)11).GetHexString(),
p.FindSimilarColor(unchecked((byte)-1), (byte)2, (byte)1).GetHexString()
);
Assert.AreEqual(
p.GetColor((short)12).GetHexString(),
p.FindSimilarColor(unchecked((byte)-1), (byte)2, (byte)10).GetHexString()
);
}
/// <summary>
/// get the color value for the font
/// </summary>
/// <param name="wb">HSSFWorkbook</param>
/// <returns></returns>
public HSSFColor GetHSSFColor(HSSFWorkbook wb)
{
HSSFPalette pallette = wb.GetCustomPalette();
return pallette.GetColor(Color);
}
void BuildStyle_Font(HSSFWorkbook workbook, StringBuilder style,
HSSFFont font)
{
switch (font.Boldweight)
{
case (short)FontBoldWeight.BOLD:
style.Append("font-weight: bold; ");
break;
case (short)FontBoldWeight.NORMAL:
// by default, not not increase HTML size
// style.Append( "font-weight: normal; " );
break;
}
HSSFColor fontColor = workbook.GetCustomPalette().GetColor(font.Color);
if (fontColor != null)
style.Append("color: " + ExcelToHtmlUtils.GetColor(fontColor) + "; ");
if (font.FontHeightInPoints != 0)
style.Append("font-size: " + font.FontHeightInPoints + "pt; ");
if (font.IsItalic)
{
style.Append("font-style: italic; ");
}
}
private void BuildStyle_Border(HSSFWorkbook workbook, StringBuilder style,
String type, BorderStyle xlsBorder, short borderColor)
{
if (xlsBorder == BorderStyle.NONE)
return;
StringBuilder borderStyle = new StringBuilder();
borderStyle.Append(ExcelToHtmlUtils.GetBorderWidth(xlsBorder));
borderStyle.Append(' ');
borderStyle.Append(ExcelToHtmlUtils.GetBorderStyle(xlsBorder));
HSSFColor color = workbook.GetCustomPalette().GetColor(borderColor);
if (color != null)
{
borderStyle.Append(' ');
borderStyle.Append(ExcelToHtmlUtils.GetColor(color));
}
style.Append("border-" + type + ": " + borderStyle + "; ");
}
protected String BuildStyle(HSSFWorkbook workbook, HSSFCellStyle cellStyle)
{
StringBuilder style = new StringBuilder();
HSSFPalette palette = workbook.GetCustomPalette();
style.Append("white-space: pre-wrap; ");
ExcelToHtmlUtils.AppendAlign(style, cellStyle.Alignment);
if (cellStyle.FillPattern == FillPatternType.NO_FILL)
{
// no fill
}
else if (cellStyle.FillPattern == FillPatternType.SOLID_FOREGROUND)
{
//cellStyle.
//HSSFColor.
HSSFColor foregroundColor = palette.GetColor(cellStyle.FillForegroundColor);
if (foregroundColor != null)
style.Append("background-color: " + ExcelToHtmlUtils.GetColor(foregroundColor) + "; ");
}
else
{
HSSFColor backgroundColor = palette.GetColor(cellStyle.FillBackgroundColor);
if (backgroundColor != null)
style.Append("background-color: " + ExcelToHtmlUtils.GetColor(backgroundColor) + "; ");
}
BuildStyle_Border(workbook, style, "top", cellStyle.BorderTop, cellStyle.TopBorderColor);
BuildStyle_Border(workbook, style, "right", cellStyle.BorderRight, cellStyle.RightBorderColor);
BuildStyle_Border(workbook, style, "bottom", cellStyle.BorderBottom, cellStyle.BottomBorderColor);
BuildStyle_Border(workbook, style, "left", cellStyle.BorderLeft, cellStyle.LeftBorderColor);
HSSFFont font = cellStyle.GetFont(workbook) as HSSFFont;
BuildStyle_Font(workbook, style, font);
return style.ToString();
}
public /*ActionResult*/ FileContentResult About()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
#region 定义表头
//RGB自定义背景色
HSSFPalette palette = book.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)54, (byte)96, (byte)146);
HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle();
///（自定义背景色）单元格背景颜色 和FillPattern必须一起设置
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Pink.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.Alignment = HorizontalAlignment.Center; //水平居中
cellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
//设置黑色边框
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
//创建字体
var cellStyleFont = (HSSFFont)book.CreateFont();
cellStyleFont.Color = HSSFColor.White.Index;
cellStyleFont.IsBold = true;
cellStyleFont.FontName = "宋体";
cellStyleFont.FontHeightInPoints = 11;
cellStyle.SetFont(cellStyleFont);
//设置单元格宽度
sheet.SetColumnWidth(0, 4000); //核销人
sheet.SetColumnWidth(1, 4000); //账号
sheet.SetColumnWidth(2, 5000); //核销时间
sheet.SetColumnWidth(3, 3500);
sheet.SetColumnWidth(4, 3500);
sheet.SetColumnWidth(5, 4000);
sheet.SetColumnWidth(6, 7000);
sheet.SetColumnWidth(7, 3500); //全面值
sheet.SetColumnWidth(8, 4500); //使用门槛
sheet.SetColumnWidth(9, 5000); //核销门店
sheet.SetColumnWidth(10, 10000); //appid
sheet.SetColumnWidth(11, 3500); //核销地址
sheet.SetColumnWidth(12, 3500); //核销id
// 第一行
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 350;
row.CreateCell(0).SetCellValue("核销人");
row.CreateCell(1).SetCellValue("核销人账号");
row.CreateCell(2).SetCellValue("核销时间");
row.CreateCell(3).SetCellValue("订单金额");
row.CreateCell(4).SetCellValue("核销金额");
row.CreateCell(5).SetCellValue("核销券码");
row.CreateCell(6).SetCellValue("有效期");
row.CreateCell(7).SetCellValue("券面值");
row.CreateCell(8).SetCellValue("使用门槛");
row.CreateCell(9).SetCellValue("核销门店");
row.CreateCell(10).SetCellValue("核销门店APPID");
row.CreateCell(11).SetCellValue("核销地址");
row.CreateCell(12).SetCellValue("核销ID");
for (int i = 0; i < 13; i++)
{
//设置高度
row.Height = 400;
row.Cells[i].CellStyle = cellStyle;
}
#endregion
//合并单元格
/**
* 第一个参数：从第几行开始合并
* 第二个参数：到第几行结束合并
* 第三个参数：从第几列开始合并
* 第四个参数：到第几列结束合并
**/
CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0);
sheet.AddMergedRegion(region);
HSSFCellStyle cellStyleItem = (HSSFCellStyle)book.CreateCellStyle();
cellStyleItem.Alignment = HorizontalAlignment.Center; //水平居中
cellStyleItem.VerticalAlignment = VerticalAlignment.Center; //垂直居中
HSSFCellStyle cellStyleItem2 = (HSSFCellStyle)book.CreateCellStyle();
cellStyleItem2.VerticalAlignment = VerticalAlignment.Center; //垂直居中
for (int i = 1; i < 100; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i);
row2.CreateCell(0).SetCellValue("收银员小吴");
row2.CreateCell(1).SetCellValue("13587639864");
row2.CreateCell(2).SetCellValue(DateTime.Now.ToString("yyyy/MM/dd HH:mm"));
row2.CreateCell(3).SetCellValue("1000");
row2.CreateCell(4).SetCellValue("180");
row2.CreateCell(5).SetCellValue("DJD84K6JFU");
row2.CreateCell(6).SetCellValue($"{DateTime.Now.ToString("yyyy-MM-dd")} ~ {DateTime.Now.AddDays(1).ToString("yyyy-MM-dd")}");
row2.CreateCell(7).SetCellValue("60");
row2.CreateCell(8).SetCellValue("满100元可用");
row2.CreateCell(9).SetCellValue("世纪新园中餐厅");
row2.CreateCell(10).SetCellValue(System.Guid.NewGuid().ToString());
var ignoreList = new List <int> {
5, 8, 9, 10
};
for (int j = 0; j < 11; j++)
{
if (ignoreList.Contains(j))
{
continue;
}
row2.Cells[j].CellStyle = cellStyleItem;
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
//Response.ContentType = "application/octet-stream";
//Response.AppendHeader("content-disposition", $"attachment;filename={DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xls;");
//Response.BinaryWrite(ms.ToArray());
//book = null;
//ms.Close();
//ms.Dispose();
//return null;
return(File(ms.ToArray(), "application/octet-stream", string.Format("冷链食品处理详情{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"))));
}
private void button1_Click(object sender, EventArgs e)
{
string fileName;
//fileName = System.IO.Path.GetDirectoryName(sourceFileTexBox.Text) + "\\";
fileName = "list_" + System.IO.Path.GetFileName(sourceFileTexBox.Text);
fileName += ".xls";
SaveFileDialog saveFileDia = new SaveFileDialog();
saveFileDia.Filter = "Excel文件|*.xls";
saveFileDia.FileName = fileName;
DialogResult result = saveFileDia.ShowDialog();
if (DialogResult.OK == result)
{
fileName = saveFileDia.FileName;
if (File.Exists(fileName))
{
MessageBox.Show("目标文件已经存在！", "错误");
return;
}
try
{
IWorkbook wb = new HSSFWorkbook();
ISheet tb = wb.CreateSheet(System.IO.Path.GetFileName(sourceFileTexBox.Text));
tb.DisplayGridlines = false;
tb.CreateRow(5).CreateCell(29).SetCellValue("備考");
tb.GetRow(5).CreateCell(3).SetCellValue("No.");
tb.GetRow(5).CreateCell(5).SetCellValue("関数名");
tb.GetRow(5).CreateCell(17).SetCellValue("テスト方法");
ICellStyle bg = (HSSFCellStyle)wb.CreateCellStyle();
IFont ft=wb.CreateFont();
ft.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
bg.SetFont(ft);
HSSFWorkbook wob = new HSSFWorkbook();
HSSFPalette pa=wob.GetCustomPalette();
NPOI.HSSF.Util.HSSFColor XlColour = pa.FindSimilarColor(23, 55, 93);
bg.FillForegroundColor = XlColour.Indexed; //15000;
bg.FillPattern = FillPattern.SolidForeground;
tb.CreateRow(3);
for (int i = 0; i < 50; i++)
{
tb.GetRow(3).CreateCell(i).CellStyle = bg;
}
tb.GetRow(3).GetCell(1).SetCellValue(System.IO.Path.GetFileName(sourceFileTexBox.Text));
ICellStyle Border3 = (HSSFCellStyle)wb.CreateCellStyle();
Border3.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick;
Border3.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thick;
Border3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
tb.GetRow(5).GetCell(3).CellStyle = Border3;
ICellStyle Border2 = (HSSFCellStyle)wb.CreateCellStyle();
Border2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick;
Border2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle Border4 = (HSSFCellStyle)wb.CreateCellStyle();
Border4.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick;
Border4.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
Border4.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle Border5 = (HSSFCellStyle)wb.CreateCellStyle();
Border5.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick;
Border5.BorderRight = NPOI.SS.UserModel.BorderStyle.Thick;
Border5.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle Border6 = (HSSFCellStyle)wb.CreateCellStyle();
Border6.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border6.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thick;
Border6.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle Border7 = (HSSFCellStyle)wb.CreateCellStyle();
Border7.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border7.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
Border7.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle Border8 = (HSSFCellStyle)wb.CreateCellStyle();
Border8.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border8.BorderRight = NPOI.SS.UserModel.BorderStyle.Thick;
Border8.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle Border9 = (HSSFCellStyle)wb.CreateCellStyle();
Border9.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border9.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thick;
Border9.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;
ICellStyle Border10 = (HSSFCellStyle)wb.CreateCellStyle();
Border10.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border10.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;
ICellStyle Border11 = (HSSFCellStyle)wb.CreateCellStyle();
Border11.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border11.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
Border11.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;
ICellStyle Border12 = (HSSFCellStyle)wb.CreateCellStyle();
Border12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thick;
Border12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;
tb.GetRow(5).CreateCell(4).CellStyle = Border2;
tb.GetRow(5).GetCell(5).CellStyle = Border4;
for (int i = 6; i < 17; i++)
{
tb.GetRow(5).CreateCell(i).CellStyle = Border2;
}
tb.GetRow(5).GetCell(17).CellStyle = Border4;
for (int i = 18; i < 29; i++)
{
tb.GetRow(5).CreateCell(i).CellStyle = Border2;
}
tb.GetRow(5).GetCell(29).CellStyle = Border4;
for (int i = 30; i < 41; i++)
{
tb.GetRow(5).CreateCell(i).CellStyle = Border2;
}
tb.GetRow(5).CreateCell(41).CellStyle = Border5;
ICellStyle Border1 = (HSSFCellStyle)wb.CreateCellStyle();
Border1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Border1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
int lastline=6;
for (int i = 0; i < funcListBox.SelectedItems.Count; i++)
{
tb.CreateRow(6 + i).CreateCell(5).SetCellValue(funcListBox.SelectedItems[i].ToString());
tb.GetRow(6 + i).CreateCell(3).SetCellValue(" " + (i + 1).ToString());
tb.GetRow(6 + i).GetCell(3).CellStyle = Border6;
tb.GetRow(6 + i).CreateCell(4).CellStyle = Border1;
tb.GetRow(6 + i).GetCell(5).CellStyle = Border7;
for (int j = 6; j < 17; j++)
{
tb.GetRow(6 + i).CreateCell(j).CellStyle = Border1;
}
tb.GetRow(6 + i).CreateCell(17).CellStyle = Border7;
for (int j = 18; j < 29; j++)
{
tb.GetRow(6 + i).CreateCell(j).CellStyle = Border1;
}
tb.GetRow(6 + i).CreateCell(29).CellStyle = Border7;
for (int j = 30; j < 41; j++)
{
tb.GetRow(6 + i).CreateCell(j).CellStyle = Border1;
}
tb.GetRow(6 + i).CreateCell(41).CellStyle = Border8;
lastline += 1;
}
tb.CreateRow(lastline).CreateCell(3).CellStyle = Border9;
tb.GetRow(lastline).CreateCell(4).CellStyle = Border10;
tb.GetRow(lastline).CreateCell(5).CellStyle = Border11;
for (int j = 6; j < 17; j++)
{
tb.GetRow(lastline).CreateCell(j).CellStyle = Border10;
}
tb.GetRow(lastline).CreateCell(17).CellStyle = Border11;
for (int j = 18; j < 29; j++)
{
tb.GetRow(lastline).CreateCell(j).CellStyle = Border10;
}
tb.GetRow(lastline).CreateCell(29).CellStyle = Border11;
for (int j = 30; j < 41; j++)
{
tb.GetRow(lastline).CreateCell(j).CellStyle = Border10;
}
tb.GetRow(lastline).CreateCell(41).CellStyle = Border12;
for (int i = 0; i < 50; i++)
{
tb.SetColumnWidth(i,1024);
}
using (FileStream fs = File.OpenWrite(fileName))
{
wb.Write(fs);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return;
}
MessageBox.Show("生成成功！", "成功");
}
}
private HSSFColor GetColor(HSSFWorkbook workbook, byte red, byte green, byte blue)
{
HSSFPalette palette = workbook.GetCustomPalette();
HSSFColor colour = palette.FindSimilarColor(red, green, blue);
if (colour == null)
{
// NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE++;
colour = palette.AddColor(red, green, blue);
}
return colour;
}
private static MemoryStream GetExcelMemoryStream(List <dynamic> orderItemList)
{
var first = orderItemList.FirstOrDefault();
//创建表格
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
#region 定义样式
//创建字体
var cellStyleFont = (HSSFFont)book.CreateFont();
cellStyleFont.IsBold = true;
cellStyleFont.FontName = "宋体";
cellStyleFont.FontHeightInPoints = 10;
//RGB自定义背景色
HSSFPalette palette = book.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)220, (byte)220, (byte)220);
// titleStyle
HSSFCellStyle titleStyle = (HSSFCellStyle)book.CreateCellStyle();
//（自定义背景色）单元格背景颜色 和FillPattern必须一起设置
titleStyle.FillForegroundColor = HSSFColor.Pink.Index;
titleStyle.FillPattern = FillPattern.SolidForeground;
titleStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
titleStyle.BorderBottom = BorderStyle.Thin;
titleStyle.BorderLeft = BorderStyle.Thin;
titleStyle.BorderRight = BorderStyle.Thin;
titleStyle.BorderTop = BorderStyle.Thin;
titleStyle.SetFont(cellStyleFont);//设置字体
//headStyle
HSSFCellStyle headStyle = (HSSFCellStyle)book.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center; //水平居中
headStyle.BorderBottom = BorderStyle.Thin;
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
headStyle.SetFont(cellStyleFont);
//borderStyle
HSSFCellStyle borderStyle = (HSSFCellStyle)book.CreateCellStyle();
borderStyle.BorderBottom = BorderStyle.Thin;
borderStyle.BorderLeft = BorderStyle.Thin;
borderStyle.BorderRight = BorderStyle.Thin;
borderStyle.BorderTop = BorderStyle.Thin;
#endregion
#region 表头
//设置单元格宽度
sheet.SetColumnWidth(0, 5000); //采购日期
sheet.SetColumnWidth(1, 5000); //门店名称
sheet.SetColumnWidth(2, 14000); //门店地址
sheet.SetColumnWidth(3, 4500); //采购数量
sheet.SetColumnWidth(4, 4500); //操作人
//表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(4);
row.Height = 350;
row.CreateCell(0).SetCellValue("采购日期");
row.CreateCell(1).SetCellValue("门店名称");
row.CreateCell(2).SetCellValue("门店地址");
row.CreateCell(3).SetCellValue("采购数量");
row.CreateCell(4).SetCellValue("操作人");
for (int i = 0; i < 5; i++)
{
row.Cells[i].CellStyle = titleStyle;
}
//合并单元格
CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, 4);
sheet.AddMergedRegion(region0);
IRow row0 = sheet.CreateRow(0);
row0.CreateCell(0).SetCellValue("冷链食品处理详情");
row0.Cells[0].CellStyle = headStyle;
var row0Cell4 = row0.CreateCell(4);
row0Cell4.CellStyle = headStyle;
IRow row1 = sheet.CreateRow(1);
row1.CreateCell(0).SetCellValue("处理商品批次码");
row1.CreateCell(1).SetCellValue(first.BatchNo.ToString());
row1.Cells[0].CellStyle = titleStyle;
IRow row2 = sheet.CreateRow(2);
row2.CreateCell(0).SetCellValue("处理日期");
row2.CreateCell(1).SetCellValue(first.NoticeTime?.ToString("yyyy.MM.dd"));
row2.Cells[0].CellStyle = titleStyle;
IRow row3 = sheet.CreateRow(3);
row3.CreateCell(0).SetCellValue("处理人");
row3.CreateCell(1).SetCellValue(first.NoticeUserName.ToString());
row3.Cells[0].CellStyle = titleStyle;
//设置黑色边框
for (int i = 1; i < 4; i++)
{
var iRow = sheet.GetRow(i);
for (int j = 1; j < 5; j++)
{
var jCell = (j == 1) ? iRow.GetCell(j) : iRow.CreateCell(j);
jCell.CellStyle = borderStyle;
}
}
#endregion
#region 循环数据
int index = 5;
foreach (var item in orderItemList)
{
IRow itemRow = sheet.CreateRow(index);
var rowCell0 = itemRow.CreateCell(0);
rowCell0.SetCellValue((item.ImportTime ?? item.SubTime).ToString("yyyy.MM.dd"));
rowCell0.CellStyle = borderStyle;
var rowCell1 = itemRow.CreateCell(1);
rowCell1.SetCellValue(item.TargetStoreName.ToString());
rowCell1.CellStyle = borderStyle;
var rowCell2 = itemRow.CreateCell(2);
rowCell2.SetCellValue(item.TargetStoreAddress.ToString());
rowCell2.CellStyle = borderStyle;
var rowCell3 = itemRow.CreateCell(3);
rowCell3.SetCellValue(item.Number?.ToString());
rowCell3.CellStyle = borderStyle;
var rowCell4 = itemRow.CreateCell(4);
rowCell4.SetCellValue(item.ImportUserNames.ToString());
rowCell4.CellStyle = borderStyle;
index++;
}
#endregion
MemoryStream ms = new MemoryStream();
book.Write(ms);
return(ms);
}
public static MemoryStream ExportToExcel(List<Group> list, List<string> date)
{
try
{
//文件流对像
MemoryStream stream = new MemoryStream();
//打开Excel对象
HSSFWorkbook workbook = new HSSFWorkbook();
Font aFont = workbook.CreateFont();
aFont.FontName = "Calibri";
aFont.FontHeightInPoints = 14;
//aFont.Boldweight = short.MaxValue;
CellStyle projectStyle = workbook.CreateCellStyle();
//对齐方式
projectStyle.Alignment = HorizontalAlignment.CENTER;
projectStyle.VerticalAlignment = VerticalAlignment.BOTTOM;
////边框
projectStyle.BorderTop = CellBorderType.MEDIUM;
projectStyle.BorderRight = CellBorderType.MEDIUM;
projectStyle.BorderBottom = CellBorderType.THIN;
projectStyle.BorderLeft = CellBorderType.MEDIUM;
//字体
projectStyle.SetFont(aFont);
//背景颜色
HSSFPalette palette = workbook.GetCustomPalette();
palette.SetColorAtIndex((short)9, (byte)(240), (byte)(240), (byte)(240));
palette.SetColorAtIndex((short)10, (byte)(220), (byte)(220), (byte)(220));
projectStyle.FillForegroundColor=(short)9;
projectStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
//projectStyle.FillBackgroundColor = (short)9;
CellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.BOTTOM;
headStyle.BorderTop = CellBorderType.MEDIUM;
headStyle.BorderRight = CellBorderType.THIN;
headStyle.SetFont(aFont);
//headStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
//headStyle.FillPattern = FillPatternType.SQUARES;
//headStyle.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
headStyle.FillForegroundColor = (short)9;
headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
CellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.Alignment = HorizontalAlignment.CENTER;
dateStyle.VerticalAlignment = VerticalAlignment.BOTTOM;
dateStyle.BorderTop = CellBorderType.MEDIUM;
dateStyle.BorderRight = CellBorderType.MEDIUM;
dateStyle.BorderLeft = CellBorderType.MEDIUM;
Font bFont = workbook.CreateFont();
bFont.FontName = "Calibri";
bFont.FontHeightInPoints = 12;
dateStyle.SetFont(bFont);
CellStyle projectstyle = workbook.CreateCellStyle();
//对齐方式
projectstyle.Alignment = HorizontalAlignment.CENTER;
projectstyle.VerticalAlignment = VerticalAlignment.CENTER;
////边框
projectstyle.BorderRight = CellBorderType.MEDIUM;
projectstyle.BorderBottom = CellBorderType.MEDIUM;
//projectstyle.BorderLeft = CellBorderType.MEDIUM;
projectstyle.SetFont(bFont);
CellStyle nameStyle = workbook.CreateCellStyle();
nameStyle.SetFont(bFont);
//对齐方式
nameStyle.Alignment = HorizontalAlignment.CENTER;
nameStyle.VerticalAlignment = VerticalAlignment.CENTER;
////边框
nameStyle.BorderTop = CellBorderType.THIN;
nameStyle.BorderRight = CellBorderType.THIN;
nameStyle.BorderBottom = CellBorderType.THIN;
nameStyle.BorderLeft = CellBorderType.MEDIUM;
CellStyle itemStyle = workbook.CreateCellStyle();
nameStyle.SetFont(bFont);
//对齐方式
//itemStyle.Alignment = HorizontalAlignment.CENTER;
itemStyle.VerticalAlignment = VerticalAlignment.BOTTOM;
////边框
itemStyle.BorderTop = CellBorderType.THIN;
itemStyle.BorderRight = CellBorderType.THIN;
itemStyle.BorderBottom = CellBorderType.THIN;
itemStyle.BorderLeft = CellBorderType.THIN;
CellStyle datestyle = workbook.CreateCellStyle();
datestyle.SetFont(bFont);
//对齐方式
datestyle.Alignment = HorizontalAlignment.RIGHT;
datestyle.VerticalAlignment = VerticalAlignment.BOTTOM;
////边框
datestyle.BorderTop = CellBorderType.THIN;
datestyle.BorderRight = CellBorderType.MEDIUM;
datestyle.BorderBottom = CellBorderType.THIN;
datestyle.BorderLeft = CellBorderType.MEDIUM;
CellStyle emptyStyle = workbook.CreateCellStyle();
//对齐方式
emptyStyle.Alignment = HorizontalAlignment.CENTER;
emptyStyle.VerticalAlignment = VerticalAlignment.BOTTOM;
//背景颜色
emptyStyle.FillForegroundColor = (short)10;
emptyStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
////边框
emptyStyle.BorderTop = CellBorderType.THIN;
emptyStyle.BorderRight = CellBorderType.MEDIUM;
emptyStyle.BorderBottom = CellBorderType.THIN;
emptyStyle.BorderLeft = CellBorderType.MEDIUM;
//emptyStyle.FillBackgroundColor = (short)8;
CellStyle lastLineStyle = workbook.CreateCellStyle();
lastLineStyle.BorderTop = CellBorderType.MEDIUM;
//Excel的Sheet对象
Sheet sheet = workbook.CreateSheet("sheet1");
sheet.SetColumnWidth(0, 22 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 24 * 256);
sheet.DefaultRowHeightInPoints = 20;
sheet.CreateFreezePane(3, 0);//Column Locking
//sheet.CreateFreezePane(3, 0, 0, 0);
////set date format
//CellStyle cellStyleDate = workbook.CreateCellStyle();
//DataFormat format = workbook.CreateDataFormat();
//cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日");
//将数据导入到excel表中
Row row;
Cell cell;
CellRangeAddress range;
int count = 1;
foreach (var project in list)
{
int column = 0;
row = sheet.CreateRow(count);
cell = row.CreateCell(0);
cell.SetCellValue("Project");
cell.CellStyle = projectStyle;
cell = row.CreateCell(1);
cell.SetCellValue("Name");
cell.CellStyle = headStyle;
cell = row.CreateCell(2);
cell.SetCellValue("Check Item");
cell.CellStyle = headStyle;
foreach (var dateitem in date)
{
sheet.SetColumnWidth(column + 3, 12 * 256);
cell = row.CreateCell(column + 3);
cell.SetCellValue(dateitem);
cell.CellStyle = dateStyle;
column++;
}
count++;
int startCount = count;
foreach (var user in project.Monthly)
{
Row row1 = sheet.CreateRow(count);
Row row2 = sheet.CreateRow(count + 1);
Row row3 = sheet.CreateRow(count + 2);
Row row4 = sheet.CreateRow(count + 3);
cell = row1.CreateCell(0);
cell.SetCellValue(project.GroupName);
cell.CellStyle = projectstyle;
//cell = row2.CreateCell(0);
//cell.CellStyle = projectstyle;
//cell = row3.CreateCell(0);
//cell.CellStyle = projectstyle;
//cell = row4.CreateCell(0);
//cell.CellStyle = projectstyle;
cell = row1.CreateCell(1);
cell.SetCellValue(user.UserName);
cell.CellStyle = nameStyle;
cell = row2.CreateCell(1);
cell.CellStyle = nameStyle;
cell = row3.CreateCell(1);
cell.CellStyle = nameStyle;
cell = row4.CreateCell(1);
cell.CellStyle = nameStyle;
cell = row1.CreateCell(2);
cell.SetCellValue("Work Done");
cell.CellStyle = itemStyle;
cell = row2.CreateCell(2);
cell.SetCellValue("Bug Created");
cell.CellStyle = itemStyle;
cell = row3.CreateCell(2);
cell.SetCellValue("Work Quality");
cell.CellStyle = itemStyle;
cell = row4.CreateCell(2);
cell.SetCellValue("Daily Performance");
cell.CellStyle = itemStyle;
range = new CellRangeAddress(count, count + 3, 1, 1);
sheet.AddMergedRegion(range);
int num = 0;
foreach (var item in user.MonthlyAppraisal)
{
cell = row1.CreateCell(num + 3);
if (item.WorkDone != 0)
{
cell.SetCellValue((Double)item.WorkDone);
cell.CellStyle = datestyle;
}
else
{
cell.CellStyle = emptyStyle;
}
cell = row2.CreateCell(num + 3);
if (item.BugCreated != 0)
{
cell.SetCellValue((Double)item.BugCreated);
cell.CellStyle = datestyle;
}
else
{
cell.CellStyle = emptyStyle;
}
cell = row3.CreateCell(num + 3);
if (item.WorkQuality != 0)
{
cell.SetCellValue((Double)item.WorkQuality);
cell.CellStyle = datestyle;
}
else
{
cell.CellStyle = emptyStyle;
}
cell = row4.CreateCell(num + 3);
if (item.DailyPerformance != 0)
{
cell.SetCellValue((Double)item.DailyPerformance);
cell.CellStyle = datestyle;
}
else
{
cell.CellStyle = emptyStyle;
}
num++;
}
count += 4;
}
range = new CellRangeAddress(startCount, count - 1, 0, 0);
sheet.AddMergedRegion(range);
}
row = sheet.CreateRow(count);
int m = date.Count;
for (int i = 0; i < m + 3; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = lastLineStyle;
}
//保存excel文档
sheet.ForceFormulaRecalculation = true;
workbook.Write(stream);
workbook.Dispose();
return stream;
}
catch
{
return new MemoryStream();
}
}
public static MemoryStream ExportDataToExcel(List<Tuple<string, string, string>> list)
{
try
{
//File stream object
MemoryStream stream = new MemoryStream();
//Open Excel object
HSSFWorkbook workbook = new HSSFWorkbook();
//custom color
HSSFPalette palette = workbook.GetCustomPalette();
palette.SetColorAtIndex((short)15, (byte)(184), (byte)(134), (byte)(11));
palette.SetColorAtIndex((short)16, (byte)(255), (byte)(240), (byte)(215));
//Title Style
CellStyle titlestyle = workbook.CreateCellStyle();
//Alignment
titlestyle.Alignment = HorizontalAlignment.CENTER;
titlestyle.VerticalAlignment = VerticalAlignment.CENTER;
//Font
Font titlefont = workbook.CreateFont();
titlefont.FontHeightInPoints = 16;
titlefont.FontName = "Calibri";
titlefont.Color = HSSFColor.WHITE.index;
titlefont.Boldweight = (short)FontBoldWeight.BOLD;
titlestyle.SetFont(titlefont);
//Background color
titlestyle.FillForegroundColor = (short)15;
titlestyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
//Border
titlestyle.BorderTop = CellBorderType.THIN;
titlestyle.BorderRight = CellBorderType.THIN;
titlestyle.BorderBottom = CellBorderType.THIN;
titlestyle.BorderLeft = CellBorderType.THIN;
//Head Style
CellStyle headstyle = workbook.CreateCellStyle();
//Alignment
headstyle.Alignment = HorizontalAlignment.CENTER;
headstyle.VerticalAlignment = VerticalAlignment.CENTER;
//Font
Font headfont = workbook.CreateFont();
headfont.FontHeightInPoints = 14;
headfont.FontName = "Calibri";
headfont.Boldweight = (short)FontBoldWeight.BOLD;
headstyle.SetFont(headfont);
//Background color
headstyle.FillForegroundColor = (short)16;
headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
//Border
headstyle.BorderTop = CellBorderType.THIN;
headstyle.BorderRight = CellBorderType.THIN;
headstyle.BorderBottom = CellBorderType.THIN;
headstyle.BorderLeft = CellBorderType.THIN;
//Name Style
CellStyle namestyle = workbook.CreateCellStyle();
//Alignment
namestyle.Alignment = HorizontalAlignment.CENTER;
namestyle.VerticalAlignment = VerticalAlignment.CENTER;
//Font
Font namefont = workbook.CreateFont();
namefont.FontHeightInPoints = 11;
namefont.FontName = "Calibri";
namefont.Boldweight = (short)FontBoldWeight.BOLD;
namestyle.SetFont(namefont);
//Border
namestyle.BorderTop = CellBorderType.THIN;
namestyle.BorderRight = CellBorderType.THIN;
namestyle.BorderBottom = CellBorderType.THIN;
namestyle.BorderLeft = CellBorderType.THIN;
//Body Style
CellStyle bodystyle = workbook.CreateCellStyle();
//Alignment
bodystyle.VerticalAlignment = VerticalAlignment.CENTER;
//Font
Font bodyfont = workbook.CreateFont();
bodyfont.FontHeightInPoints = 12;
bodyfont.FontName = "Times New Roman";
bodystyle.SetFont(bodyfont);
//Border
bodystyle.BorderTop = CellBorderType.THIN;
bodystyle.BorderRight = CellBorderType.THIN;
bodystyle.BorderBottom = CellBorderType.THIN;
bodystyle.BorderLeft = CellBorderType.THIN;
//Line Feed
bodystyle.WrapText = true;
//Sheet's object of Excel
Sheet sheet = workbook.CreateSheet("sheet1");
sheet.SetColumnWidth(0, (short)(35.7 * 160));
sheet.SetColumnWidth(1, (short)(35.7 * 400));
sheet.SetColumnWidth(2, (short)(35.7 * 600));
//Export to Excel
Row row;
Cell cell;
//cell.SetCellType();
int count = 2;
row = sheet.CreateRow(0);
row.HeightInPoints = 20;
cell = row.CreateCell(0);
cell.CellStyle = titlestyle;
cell.SetCellValue("Staff Report");
cell = row.CreateCell(1);
cell.CellStyle = titlestyle;
cell = row.CreateCell(2);
cell.CellStyle = titlestyle;
CellRangeAddress range = new CellRangeAddress(0, 0, 0, 2);
sheet.AddMergedRegion(range);
row = sheet.CreateRow(1);
row.HeightInPoints = 20;
cell = row.CreateCell(0);
cell.CellStyle = headstyle;
cell = row.CreateCell(1);
cell.CellStyle = headstyle;
cell.SetCellValue(" What you worked on today?");
cell = row.CreateCell(2);
cell.CellStyle = headstyle;
cell.SetCellValue(" What you will be working on tomorrow?");
foreach (var item in list)
{
row = sheet.CreateRow(count);
cell = row.CreateCell(0);
cell.SetCellValue(item.Item1);
cell.CellStyle = namestyle;
cell = row.CreateCell(1);
cell.SetCellValue(item.Item2);
cell.CellStyle = bodystyle;
cell = row.CreateCell(2);
cell.SetCellValue(item.Item3);
cell.CellStyle = bodystyle;
count++;
}
//Save excel
sheet.ForceFormulaRecalculation = true;
workbook.Write(stream);
workbook.Dispose();
return stream;
}
catch
{
return new MemoryStream();
}
}