private static short GetXLColour(HSSFWorkbook workbook, Color SystemColour)
{
short s = 0;
var XlPalette = workbook.GetCustomPalette();
var XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);
if (XlColour == null)
{
if (PaletteRecord.STANDARD_PALETTE_SIZE < 255)
{
XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B);
s = XlColour.Indexed;
}
}
else
{
s = XlColour.Indexed;
}
return(s);
}
private static short GetXLColour(Color SystemColour)
{
HSSFWorkbook workbook = new HSSFWorkbook();
short s = 0;
HSSFPalette XlPalette = workbook.GetCustomPalette();
NPOI.HSSF.Util.HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);
if (XlColour == null)
{
if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255)
{
XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B);
s = XlColour.Indexed;
}
}
else
{
s = XlColour.Indexed;
}
return(s);
}
public void Test48403()
{
HSSFWorkbook wb = new HSSFWorkbook();
var color = new Rgb24(0, 0x6B, 0x6B); //decode("#006B6B");
HSSFPalette palette = wb.GetCustomPalette();
HSSFColor hssfColor = palette.FindColor(color.R, color.G, color.B);
Assert.IsNull(hssfColor);
palette.SetColorAtIndex(
(short)(PaletteRecord.STANDARD_PALETTE_SIZE - 1),
(byte)color.R, (byte)color.G,
(byte)color.B);
hssfColor = palette.GetColor((short)(PaletteRecord.STANDARD_PALETTE_SIZE - 1));
Assert.IsNotNull(hssfColor);
Assert.AreEqual(55, hssfColor.Indexed);
CollectionAssert.AreEqual(new short[] { 0, 107, 107 }, hssfColor.GetTriplet());
wb.Close();
}
/// <summary>
/// 低版本设置自定义颜色
/// </summary>
/// <param name="workBook"></param>
/// <param name="rgbs"></param>
private static void SetCustomColor(this HSSFWorkbook workBook, IEnumerable <string> rgbs)
{
SetOriginalRGB();
// 获取调色板
HSSFPalette pattern = workBook.GetCustomPalette();
short indexed = 8;
foreach (var rgb in rgbs)
{
if (indexed > 63)
{
indexed = 8;
}
short tempIndex = pattern.SetCustomColor(rgb, indexed);
if (tempIndex == -1)
{
continue;
}
indexed = tempIndex;
indexed++;
}
}
private static void SetCustomColor(this HSSFWorkbook workBook, IEnumerable <ColorEntity> listColor)
{
SetOriginalRGB();
// 获取调色板
HSSFPalette pattern = workBook.GetCustomPalette();
short indexed = 8;
foreach (var color in listColor)
{
if (indexed > 63)
{
indexed = 8;
}
short tempIndex = pattern.SetCustomColor(color.RGB, color.Index);
if (tempIndex == -1)
{
continue;
}
indexed = tempIndex;
indexed++;
}
}
public static HSSFColor setColor(HSSFWorkbook workbook, byte r, byte g, byte b)
{
try
{
HSSFPalette palette = workbook.GetCustomPalette();
HSSFColor color = null;
color = palette.FindColor(r, g, b);
if (color == null)
{
palette.SetColorAtIndex(HSSFColor.Blue.Index, r, g, b);
color = palette.GetColor(HSSFColor.Blue.Index);
}
return(color);
}
catch (Exception ex)
{
throw ex;
}
}
public void SetColor(int k, int mrow, int mcol, short R, short G, short B)
{
k = k - 1;
string Result = "";
if (wb == null)
{
return;
}
ISheet sheet = wb.GetSheetAt(k);
mrow = mrow - 1;
mcol = mcol - 1;
IRow row = sheet.GetRow(mrow);
if (row == null)
{
row = sheet.CreateRow(mrow);
}
ICell cell = row.GetCell(mcol); //|| (cell.CellType != CellType.String)
if ((cell == null))
{
cell = row.CreateCell(mcol);
}
ICellStyle s = wb.CreateCellStyle();
//HSSFColor.GetIndexHash();
HSSFPalette palette = wb.GetCustomPalette();
HSSFColor hssFColor = palette.FindColor((Byte)R, (Byte)G, (Byte)B);
s.FillForegroundColor = hssFColor.Indexed;
s.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = s;
}
public override void SetFontColor(System.Drawing.Color color)
{
IWorkbook workbook = _npoiWorksheet.Workbook;
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(_npoiWorksheet.GetColumnStyle(_columnNum));
if (workbook is HSSFWorkbook)
{
HSSFWorkbook hssfWorkbook = (HSSFWorkbook)workbook;
HSSFPalette palette = hssfWorkbook.GetCustomPalette(); //调色板实例
//palette.SetColorAtIndex((short)8, color.R, color.G, color.B);
HSSFColor hssFColor = palette.FindSimilarColor(color.R, color.G, color.B);
IFont font = cellStyle.GetFont(workbook);
font.Color = hssFColor.Indexed;
cellStyle.SetFont(font);
}
else
{
//No way!
}
_npoiWorksheet.SetDefaultColumnStyle(_columnNum, cellStyle);
}
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 + "; ");
}
/// <summary>
/// 列头樣式
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
private static HSSFCellStyle GetColHeaderStyle(HSSFWorkbook workbook)
{
var headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.CENTER;
var font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
var palette = workbook.GetCustomPalette();
palette.SetColorAtIndex((short)10, (byte)0, (byte)176, (byte)240);
headStyle.FillForegroundColor = (short)10;
headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
headStyle.BorderBottom = CellBorderType.THIN;
headStyle.BorderLeft = CellBorderType.THIN;
headStyle.BorderTop = CellBorderType.THIN;
headStyle.BorderRight = CellBorderType.THIN;
return(headStyle);
}
void Test()
{
filePath = "E:/MyWork/test.xls";
wk = new HSSFWorkbook();
sheet = wk.CreateSheet("mySheet");
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); //工作簿实例
HSSFPalette palette = hssfWorkbook.GetCustomPalette(); //调色板实例
//palette.SetColorAtIndex((short)8, (byte)0, (byte)200, (byte)0);
//PaletteRecord p = new PaletteRecord();
PaletteRecord p = new PaletteRecord();
//调色板实例
//HSSFPalette palette = workbookAll.GetCustomPalette();
//RGB颜色值，第一个值：8~64之间，后面三个值为RGB色值
//palette.SetColorAtIndex((short)8, 179, 179, 179);
//颜色实例
//HSSFColor hSSFColor = palette.FindColor(179, 179, 179);
//style.FillPattern = CellFillPattern.SOLID_FOREGROUND;
//应用颜色到Style
//style.FillForegroundColor = hSSFColor.GetIndex();
//List<HSSFColor> colors = new List<HSSFColor>();
Dictionary <int, ICellStyle> colDic = new Dictionary <int, ICellStyle>();
for (int i = 0; i < size; i++)
{
row = sheet.CreateRow(i);
for (int j = 0; j < size / 4; j++)
{
cell = row.CreateCell(j);
//cell.SetCellValue(i + "-" + j);
Color col = texture.GetPixel(j * 4, size - i);
//Color col = GetArvCol(j * 4, size - i);
//if(col.a <= 0.1f)
//{
//}
short similarColor = palette.FindSimilarColor((byte)(col.r * 255), (byte)(col.g * 255), (byte)(col.b * 255)).Indexed;
//透明的地方用白色
if (col.a <= 0.1f)
{
similarColor = 9;
}
//Debug.LogError(similarColor.Indexed);
if (!colDic.ContainsKey(similarColor))
{
ICellStyle s = wk.CreateCellStyle();
colDic.Add(similarColor, s);
s.FillForegroundColor = similarColor;
s.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = s;
}
else
{
ICellStyle s = colDic[similarColor];
s.FillForegroundColor = similarColor;
s.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = s;
}
//ICellStyle s = wk.CreateCellStyle();
//s.FillForegroundColor = palette.FindSimilarColor((byte)(col.r * 255), (byte)(col.g * 255), (byte)(col.b * 255)).Indexed;
//s.FillPattern = FillPattern.SolidForeground;
//cell.CellStyle = s;
}
}
fs = File.Create(filePath);
wk.Write(fs);
fs.Close();
fs.Dispose();
Debug.Log("创建表格成功");
}
/// <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));
}
protected override byte[] Export <T>(IEnumerable <T> table, IEnumerable <KeyValuePair <PropertyInfo, TableOptionsAttribute> > tableColumns)
{
using (var ms = new MemoryStream())
{
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet();
var rowIndex = 0;
var colIndex = 0;
var colCount = tableColumns.Count();
var headerRow = sheet.CreateRow(0);
var headerRowCell = headerRow.CreateCell(colIndex);
headerRowCell.SetCellValue($"{resource?.Get("CreateDate")} {DateTime.Now.ToString(CultureInfo.CurrentUICulture)}".Trim());
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, colCount - 1));
var style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Right;
headerRowCell.CellStyle = style;
var tableHeaderRow = sheet.CreateRow(1);
// Creating styles
var headerCellStyle = workbook.CreateCellStyle();
var palette = workbook.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Lavender.Index, ExportConfiguration.HeaderBackgroundColor.R, ExportConfiguration.HeaderBackgroundColor.G, ExportConfiguration.HeaderBackgroundColor.B);
headerCellStyle.FillBackgroundColor = HSSFColor.Lavender.Index;
headerCellStyle.FillPattern = FillPattern.SolidForeground;
var doubleCellStyle = workbook.CreateCellStyle();
doubleCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00000");
var intCellStyle = workbook.CreateCellStyle();
intCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
// Creates the header cells
foreach (var tableColumn in tableColumns.Select(item => item.Value))
{
var headerCell = tableHeaderRow.CreateCell(colIndex);
headerCell.SetCellValue(tableColumn.Title);
// Add Style to Cell
headerCell.CellStyle = headerCellStyle;
colIndex++;
}
if (table != null)
{
// Writes the cells
foreach (var obj in table)
{
colIndex = 0;
rowIndex++;
var dataRow = sheet.CreateRow(rowIndex);
foreach (var tableColumn in tableColumns)
{
var val = tableColumn.Key.GetPropertyValue(obj, tableColumn.Value);
if (val != null)
{
var cell = dataRow.CreateCell(colIndex);
if (val is double)
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue((double)val);
cell.CellStyle = doubleCellStyle;
}
else if (val is short || val is int || val is long)
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(Convert.ToInt64(val));
cell.CellStyle = intCellStyle;
}
else
{
cell.SetCellValue(string.Format(tableColumn.Value.Format.IsNotNullOrEmpty() ? "{0:" + tableColumn.Value.Format + "}" : "{0}", val));
}
}
colIndex++;
}
}
}
// Auto size the columns
for (var i = 0; i < colCount; i++)
{
sheet.AutoSizeColumn(i);
}
// Create the stream
workbook.Write(ms);
return(ms.ToArray());
}
}
public ActionResult ExportData(string sheet, string subTitle, List <dynamic> exportData)
{
#region 加载模板文件到工作簿对象中
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(Server.MapPath("/Areas/STAT/Views/STATTemp/排队叫号分析.xls"), FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
#endregion
#region 根据模板设置工作表的内容
ISheet sheet1 = hssfworkbook.GetSheet("排队叫号分析");
string maintitle = subTitle.Replace("<span style='font-size:12px;'>", "").Replace("</span>", "");
sheet1.GetRow(0).Cells[0].SetCellValue(maintitle);
int rowIndex = 4;
if (null != exportData)
{
#region 数据单元格样式
var cellFont = hssfworkbook.CreateFont();
var cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index;
cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
#endregion
sheet1.GetRow(3).Cells[1].SetCellValue(sheet.Split('－')[1]);
foreach (var row in exportData)
{
IRow xlsrow = sheet1.CreateRow(rowIndex);
xlsrow.CreateCell(0).SetCellValue(rowIndex - 3);
xlsrow.CreateCell(1).SetCellValue(row.NAM);
xlsrow.CreateCell(2).SetCellValue(row.CALL_CNT);
xlsrow.CreateCell(3).SetCellValue(CommonHelper.DivisionOfPercent(row.CALL_CNT, exportData.Sum(obj => obj.CALL_CNT)));
xlsrow.CreateCell(4).SetCellValue(row.HANDLE_CNT);
xlsrow.CreateCell(5).SetCellValue(CommonHelper.DivisionOfPercent(row.HANDLE_CNT, exportData.Sum(obj => obj.CALL_CNT)));
xlsrow.CreateCell(6).SetCellValue(row.OVERTIME_WAIT_CNT);
xlsrow.CreateCell(7).SetCellValue(CommonHelper.DivisionOfPercent(row.OVERTIME_WAIT_CNT, exportData.Sum(obj => obj.CALL_CNT)));
xlsrow.CreateCell(8).SetCellValue(row.SECOND_SVR_CNT);
xlsrow.CreateCell(9).SetCellValue(CommonHelper.DivisionOfPercent(row.SECOND_SVR_CNT, exportData.Sum(obj => obj.CALL_CNT)));
xlsrow.CreateCell(10).SetCellValue(CommonHelper.DivisionOfTimeString(row.WAIT_DUR, row.CALL_CNT));
xlsrow.CreateCell(11).SetCellValue(row.LOCAL_CNT);
xlsrow.CreateCell(12).SetCellValue(CommonHelper.DivisionOfPercent(row.LOCAL_CNT, exportData.Sum(obj => obj.CALL_CNT)));
xlsrow.CreateCell(13).SetCellValue(row.VOTE_MULTI_CNT);
xlsrow.CreateCell(14).SetCellValue(CommonHelper.DivisionOfPercent(row.VOTE_MULTI_CNT, exportData.Sum(obj => obj.CALL_CNT)));
xlsrow.CreateCell(15).SetCellValue(row.ABANDON_CNT);
xlsrow.CreateCell(16).SetCellValue(CommonHelper.DivisionOfPercent(row.ABANDON_CNT, exportData.Sum(obj => obj.CALL_CNT)));
#region 设置单元格样式
foreach (var cell in xlsrow.Cells)
{
cell.CellStyle = cellStyle;
cell.CellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cell.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cell.CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cell.CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
}
#endregion
rowIndex++;
}
IRow footer = sheet1.CreateRow(rowIndex);
int totalCALLCNT = exportData.Sum(obj => obj.CALL_CNT);
footer.CreateCell(0).SetCellValue("合计"); //#fffdbb
footer.CreateCell(1).SetCellValue("合计"); //#fffdbb
footer.CreateCell(2).SetCellValue(totalCALLCNT);
footer.CreateCell(3).SetCellValue(CommonHelper.DivisionOfPercent(exportData.Sum(obj => obj.CALL_CNT), totalCALLCNT));
footer.CreateCell(4).SetCellValue(exportData.Sum(obj => obj.HANDLE_CNT));
footer.CreateCell(5).SetCellValue(CommonHelper.DivisionOfPercent(exportData.Sum(obj => obj.HANDLE_CNT), totalCALLCNT));
footer.CreateCell(6).SetCellValue(exportData.Sum(obj => obj.OVERTIME_WAIT_CNT));
footer.CreateCell(7).SetCellValue(CommonHelper.DivisionOfPercent(exportData.Sum(obj => obj.OVERTIME_WAIT_CNT), totalCALLCNT));
footer.CreateCell(8).SetCellValue(exportData.Sum(obj => obj.SECOND_SVR_CNT));
footer.CreateCell(9).SetCellValue(CommonHelper.DivisionOfPercent(exportData.Sum(obj => obj.SECOND_SVR_CNT), totalCALLCNT));
footer.CreateCell(10).SetCellValue(CommonHelper.DivisionOfTimeString(exportData.Sum(obj2 => (double)obj2.WAIT_DUR), exportData.Sum(obj2 => obj2.CALL_CNT)));
footer.CreateCell(11).SetCellValue(exportData.Sum(obj => obj.LOCAL_CNT));
footer.CreateCell(12).SetCellValue(CommonHelper.DivisionOfPercent(exportData.Sum(obj => obj.LOCAL_CNT), totalCALLCNT));
footer.CreateCell(13).SetCellValue(exportData.Sum(obj => obj.VOTE_MULTI_CNT));
footer.CreateCell(14).SetCellValue(CommonHelper.DivisionOfPercent(exportData.Sum(obj => obj.VOTE_MULTI_CNT), totalCALLCNT));
footer.CreateCell(15).SetCellValue(exportData.Sum(obj => obj.ABANDON_CNT));
footer.CreateCell(16).SetCellValue(CommonHelper.DivisionOfPercent(exportData.Sum(obj => obj.ABANDON_CNT), totalCALLCNT));
#region 底部样式
var footercellFont = hssfworkbook.CreateFont();
var footercellStyle = hssfworkbook.CreateCellStyle();
footercellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
footercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
footercellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
var HSSFPalette = hssfworkbook.GetCustomPalette();
Color c = ToColor("#fffdbb");
HSSFPalette.SetColorAtIndex(NPOI.HSSF.Util.HSSFColor.Yellow.Index, c.R, c.G, c.B);
#endregion
foreach (var cell in footer.Cells)
{
cell.CellStyle = footercellStyle;
cell.CellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cell.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cell.CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cell.CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
}
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex, 0, 1);
sheet1.AddMergedRegion(cellRangeAddress);
}
//强制Excel重新计算表中所有的公式
sheet1.ForceFormulaRecalculation = true;
#endregion
#region 写入到客户端
MemoryStream ms = new MemoryStream();
//将工作簿的内容放到内存流中
hssfworkbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", sheet + ".xls"));
#endregion
}
private void GenerarFormatos()
{
HSSFPalette Colores = hssfworkbook.GetCustomPalette();
Colores.SetColorAtIndex(HSSFColor.GREEN.index, (byte)83, (byte)147, (byte)91); //Verde
Colores.SetColorAtIndex(HSSFColor.DARK_YELLOW.index, (byte)231, (byte)204, (byte)45); //Amarillo Fuerte
Colores.SetColorAtIndex(HSSFColor.LIGHT_YELLOW.index, (byte)248, (byte)241, (byte)196); //Amarillo Suave
Colores.SetColorAtIndex(HSSFColor.WHITE.index, (byte)255, (byte)255, (byte)255); //Blanco
Colores.SetColorAtIndex(HSSFColor.BLACK.index, (byte)0, (byte)0, (byte)0); //Negro
Colores.SetColorAtIndex(HSSFColor.GREY_25_PERCENT.index, (byte)216, (byte)228, (byte)188); //Verde Suave
IFont FBlanca12 = hssfworkbook.CreateFont();
FBlanca12.Color = HSSFColor.WHITE.index;
FBlanca12.IsItalic = false;
FBlanca12.FontHeightInPoints = 12;
IFont FBlanca10 = hssfworkbook.CreateFont();
FBlanca10.Color = HSSFColor.WHITE.index;
FBlanca10.IsItalic = false;
FBlanca10.FontHeightInPoints = 10;
IFont FNegra = hssfworkbook.CreateFont();
FNegra.Color = HSSFColor.BLACK.index;
FNegra.IsItalic = false;
FNegra.FontHeightInPoints = 10;
EstTitulos = hssfworkbook.CreateCellStyle();
EstTitulos.Alignment = HorizontalAlignment.CENTER;
EstTitulos.FillPattern = FillPatternType.SOLID_FOREGROUND;
EstTitulos.FillForegroundColor = HSSFColor.GREEN.index;
EstTitulos.VerticalAlignment = VerticalAlignment.CENTER;
EstTitulos.SetFont(FBlanca10);
EstTitulos.IsLocked = true;
EstInvernadero = hssfworkbook.CreateCellStyle();
EstInvernadero.Alignment = HorizontalAlignment.CENTER;
EstInvernadero.FillPattern = FillPatternType.SOLID_FOREGROUND;
EstInvernadero.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
EstInvernadero.VerticalAlignment = VerticalAlignment.CENTER;
EstInvernadero.SetFont(FNegra);
EstInvernadero.IsLocked = true;
EstSubtitulos = hssfworkbook.CreateCellStyle();
EstSubtitulos.Alignment = HorizontalAlignment.CENTER;
EstSubtitulos.FillPattern = FillPatternType.SOLID_FOREGROUND;
EstSubtitulos.FillForegroundColor = HSSFColor.DARK_YELLOW.index;
EstSubtitulos.SetFont(FBlanca10);
EstSubtitulos.IsLocked = true;
EstCeldas = hssfworkbook.CreateCellStyle();
EstCeldas.Alignment = HorizontalAlignment.CENTER;
EstCeldas.FillPattern = FillPatternType.SOLID_FOREGROUND;
EstCeldas.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
EstCeldas.SetFont(FNegra);
EstCeldas.IsLocked = false;
EsString = hssfworkbook.CreateCellStyle();
EsString.Alignment = HorizontalAlignment.CENTER;
EsString.FillPattern = FillPatternType.SOLID_FOREGROUND;
EsString.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
EsString.SetFont(FNegra);
EsString.IsLocked = false;
var formatId = HSSFDataFormat.GetBuiltinFormat("@");
if (formatId == -1)
{
var newDataFormat = hssfworkbook.CreateDataFormat();
EsString.DataFormat = newDataFormat.GetFormat("@");
}
else
{
EsString.DataFormat = formatId;
}
RenglonAmarillo = hssfworkbook.CreateCellStyle();
RenglonAmarillo.Alignment = HorizontalAlignment.CENTER;
RenglonAmarillo.FillPattern = FillPatternType.SOLID_FOREGROUND;
RenglonAmarillo.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
RenglonAmarillo.SetFont(FNegra);
RenglonAmarillo.IsLocked = false;
RenglonBlanco = hssfworkbook.CreateCellStyle();
RenglonBlanco.Alignment = HorizontalAlignment.CENTER;
RenglonBlanco.FillPattern = FillPatternType.SOLID_FOREGROUND;
RenglonBlanco.FillForegroundColor = HSSFColor.WHITE.index;
RenglonBlanco.SetFont(FNegra);
RenglonBlanco.IsLocked = false;
formatId = HSSFDataFormat.GetBuiltinFormat("#,###,##0;-#,###,##0");
if (formatId == -1)
{
var newDataFormat = hssfworkbook.CreateDataFormat();
EstCeldas.DataFormat = newDataFormat.GetFormat("#,###,##0;-#,###,##0");
}
else
{
EstCeldas.DataFormat = formatId;
}
EstCeldasDate = hssfworkbook.CreateCellStyle();
EstCeldasDate.Alignment = HorizontalAlignment.CENTER;
EstCeldasDate.FillPattern = FillPatternType.SOLID_FOREGROUND;
EstCeldasDate.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
EstCeldasDate.SetFont(FNegra);
EstCeldasDate.IsLocked = false;
formatId = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
if (formatId == -1)
{
var newDataFormat = hssfworkbook.CreateDataFormat();
EstCeldasDate.DataFormat = newDataFormat.GetFormat("m/d/yy");
}
else
{
EstCeldasDate.DataFormat = formatId;
}
EsDateTime = hssfworkbook.CreateCellStyle();
EsDateTime.Alignment = HorizontalAlignment.CENTER;
EsDateTime.FillPattern = FillPatternType.SOLID_FOREGROUND;
EsDateTime.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
EsDateTime.SetFont(FNegra);
EsDateTime.IsLocked = false;
formatId = HSSFDataFormat.GetBuiltinFormat("YYYY-MM-DD");
if (formatId == -1)
{
var newDataFormat = hssfworkbook.CreateDataFormat();
EsDateTime.DataFormat = newDataFormat.GetFormat("YYYY-MM-DD");
}
else
{
EsDateTime.DataFormat = formatId;
}
EstCeldasP = hssfworkbook.CreateCellStyle();
EstCeldasP.Alignment = HorizontalAlignment.CENTER;
EstCeldasP.FillPattern = FillPatternType.SOLID_FOREGROUND;
EstCeldasP.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
EstCeldasP.SetFont(FNegra);
EstCeldasP.IsLocked = false;
formatId = HSSFDataFormat.GetBuiltinFormat("0%");
if (formatId == -1)
{
var newDataFormat = hssfworkbook.CreateDataFormat();
EstCeldasP.DataFormat = newDataFormat.GetFormat("0%");
}
else
{
EstCeldasP.DataFormat = formatId;
}
EstCeldasPercentLocked = hssfworkbook.CreateCellStyle();
EstCeldasPercentLocked.Alignment = HorizontalAlignment.CENTER;
EstCeldasPercentLocked.FillPattern = FillPatternType.SOLID_FOREGROUND;
EstCeldasPercentLocked.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index;
EstCeldasPercentLocked.SetFont(FNegra);
EstCeldasPercentLocked.IsLocked = true;
formatId = HSSFDataFormat.GetBuiltinFormat("0%");
if (formatId == -1)
{
var newDataFormat = hssfworkbook.CreateDataFormat();
EstCeldasPercentLocked.DataFormat = newDataFormat.GetFormat("0%");
}
else
{
EstCeldasPercentLocked.DataFormat = formatId;
}
}
public ActionResult excelPrint()
{
HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
// HSSFSheet sheet = workbook.CreateSheet();// 创建一个Excel的Sheet
ISheet sheet = workbook.CreateSheet();
sheet.CreateFreezePane(1, 3); // 冻结
// 设置列宽
sheet.SetColumnWidth(0, 1000);
sheet.SetColumnWidth(1, 3500);
sheet.SetColumnWidth(2, 3500);
sheet.SetColumnWidth(3, 6500);
sheet.SetColumnWidth(4, 6500);
sheet.SetColumnWidth(5, 6500);
sheet.SetColumnWidth(6, 6500);
sheet.SetColumnWidth(7, 2500);
// Sheet样式
ICellStyle sheetStyle = workbook.CreateCellStyle();
// 背景色的设定
HSSFPalette XlPalette = workbook.GetCustomPalette();
// sheetStyle.FillBackgroundColor = 0;
sheetStyle.FillPattern = FillPattern.FineDots;
// sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
// 前景色的设定
// sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// 填充模式
// sheetStyle.setFillPattern(HSSFCellStyle.Equals)
// 设置列的样式
for (int i = 0; i <= 14; i++)
{
sheet.SetDefaultColumnStyle((short)i, sheetStyle);
}
// 设置字体
IFont headfont = workbook.CreateFont();
//headfont.setFontName("黑体");
//headfont.setFontHeightInPoints((short)22);// 字体大小
//headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
headfont.FontName = "黑体";
headfont.FontHeightInPoints = (short)22;
headfont.Boldweight = (short)10;
headfont.IsBold = true;
// 另一个样式
ICellStyle headstyle = workbook.CreateCellStyle();
//headstyle.setFont(headfont);
//headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
//headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
//headstyle.setLocked(true);
//headstyle.setWrapText(true);// 自动换行
headstyle.SetFont(headfont);
headstyle.Alignment = HorizontalAlignment.Center;
headstyle.VerticalAlignment = VerticalAlignment.Center;
headstyle.IsLocked = true;
headstyle.WrapText = true;
// 另一个字体样式
IFont columnHeadFont = workbook.CreateFont();
//columnHeadFont.setFontName("宋体");
//columnHeadFont.setFontHeightInPoints((short)10);
//columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
columnHeadFont.FontName = "宋体";
columnHeadFont.FontHeightInPoints = (short)10;
columnHeadFont.Boldweight = (short)10;
// 列头的样式
ICellStyle columnHeadStyle = workbook.CreateCellStyle();
//columnHeadStyle.setFont(columnHeadFont);
//columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
//columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
//columnHeadStyle.setLocked(true);
//columnHeadStyle.setWrapText(true);
//columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色
//columnHeadStyle.setBorderLeft((short)1);// 边框的大小
//columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色
//columnHeadStyle.setBorderRight((short)1);// 边框的大小
//columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
//columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
columnHeadStyle.SetFont(columnHeadFont);
columnHeadStyle.Alignment = HorizontalAlignment.Center;
columnHeadStyle.VerticalAlignment = VerticalAlignment.Center;
columnHeadStyle.IsLocked = true;
columnHeadStyle.WrapText = true;
columnHeadStyle.LeftBorderColor = HSSFColor.Black.Index;
columnHeadStyle.BorderLeft = BorderStyle.Thin;
columnHeadStyle.RightBorderColor = HSSFColor.Black.Index;
columnHeadStyle.BorderRight = BorderStyle.Thin;
// 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式）
columnHeadStyle.FillForegroundColor = HSSFColor.White.Index;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
//font.setFontHeightInPoints((short)10);
font.FontHeightInPoints = 10;
// 普通单元格样式
ICellStyle style = workbook.CreateCellStyle();
style.SetFont(font);
//style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中
//style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
//style.setWrapText(true);
//style.setLeftBorderColor(HSSFColor.BLACK.index);
//style.setBorderLeft((short)1);
//style.setRightBorderColor(HSSFColor.BLACK.index);
//style.setBorderRight((short)1);
//style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
//style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
//style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色．
// 另一个样式
ICellStyle centerstyle = workbook.CreateCellStyle();
centerstyle.SetFont(font);
centerstyle.Alignment = HorizontalAlignment.Center; // 左右居中
centerstyle.VerticalAlignment = VerticalAlignment.Center; // 上下居中
centerstyle.WrapText = true;
//centerstyle.setLeftBorderColor(HSSFColor.BLACK.index);
//centerstyle.setBorderLeft((short)1);
//centerstyle.setRightBorderColor(HSSFColor.BLACK.index);
//centerstyle.setBorderRight((short)1);
//centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
//centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
//centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色．
try
{
// 创建第一行
IRow row0 = sheet.CreateRow(0);
// 设置行高
row0.Height = 900;
//row0.setHeight((short)900);
// 创建第一列
ICell cell0 = row0.CreateCell(0);
cell0.SetCellValue(new HSSFRichTextString("中非发展基金投资项目调度会工作落实情况对照表"));
cell0.CellStyle = headstyle;
//cell0.SetCellStyle()=headstyle;
/**
* 合并单元格
* 第一个参数：第一个单元格的行数（从0开始）
* 第二个参数：第二个单元格的行数（从0开始）
* 第三个参数：第一个单元格的列数（从0开始）
* 第四个参数：第二个单元格的列数（从0开始）
*/
CellRangeAddress range = new CellRangeAddress(0, 0, 0, 7);
sheet.AddMergedRegion(range);
// 创建第二行
IRow row1 = sheet.CreateRow(1);
ICell cell1 = row1.CreateCell(0);
cell1.SetCellValue(new HSSFRichTextString("本次会议时间：2009年8月31日 前次会议时间：2009年8月24日"));
//cell1.SetCellStyle(centerstyle);
cell1.CellStyle = centerstyle;
// 合并单元格
range = new CellRangeAddress(1, 2, 0, 7);
sheet.AddMergedRegion(range);
// 第三行
IRow row2 = sheet.CreateRow(3);
//row2.setHeight((short)750);
row2.Height = 750;
ICell cell = row2.CreateCell(0);
cell.SetCellValue(new HSSFRichTextString("责任者"));
cell.CellStyle = columnHeadStyle;
cell = row2.CreateCell(1);
cell.SetCellValue(new HSSFRichTextString("成熟度排序"));
cell.CellStyle = columnHeadStyle;
cell = row2.CreateCell(2);
cell.SetCellValue(new HSSFRichTextString("事项"));
cell.CellStyle = columnHeadStyle;
cell = row2.CreateCell(3);
cell.SetCellValue(new HSSFRichTextString("前次会议要求\n/新项目的项目概要"));
cell.CellStyle = columnHeadStyle;
cell = row2.CreateCell(4);
cell.SetCellValue(new HSSFRichTextString("上周工作进展"));
cell.CellStyle = columnHeadStyle;
cell = row2.CreateCell(5);
cell.SetCellValue(new HSSFRichTextString("本周工作计划"));
cell.CellStyle = columnHeadStyle;
cell = row2.CreateCell(6);
cell.SetCellValue(new HSSFRichTextString("问题和建议"));
cell.CellStyle = columnHeadStyle;
cell = row2.CreateCell(7);
cell.SetCellValue(new HSSFRichTextString("备 注"));
cell.CellStyle = columnHeadStyle;
// 访问数据库，得到数据集
List <Inventory_UserInfo> deitelVOList = null;
//int m = 4;
//int k = 4;
for (int i = 0; i < deitelVOList.Count(); i++)
{
}
// UserInfo vo = deitelVOList[i];
//String dname = vo.PerSonName;
//List<Workinfo> workList = vo.getWorkInfoList();
//IRow row = sheet.CreateRow(m);
//cell = row.createCell(0);
//cell.SetCellValue(new HSSFRichTextString(dname));
//cell.CellStyle=centerstyle;
//// 合并单元格
//range = new CellRangeAddress(m, m + workList.size() - 1, 0, 0);
//sheet.addMergedRegion(range);
//m = m + workList.size();
// for (int j = 0; j < workList.size(); j++)
// {
// Workinfo w = workList.get(j);
// // 遍历数据集创建Excel的行
// row = sheet.getRow(k + j);
// if (null == row)
// {
// row = sheet.createRow(k + j);
// }
// cell = row.createCell(1);
// cell.setCellValue(w.getWnumber());
// cell.setCellStyle(centerstyle);
// cell = row.createCell(2);
// cell.setCellValue(new HSSFRichTextString(w.getWitem()));
// cell.setCellStyle(style);
// cell = row.createCell(3);
// cell.setCellValue(new HSSFRichTextString(w.getWmeting()));
// cell.setCellStyle(style);
// cell = row.createCell(4);
// cell.setCellValue(new HSSFRichTextString(w.getWbweek()));
// cell.setCellStyle(style);
// cell = row.createCell(5);
// cell.setCellValue(new HSSFRichTextString(w.getWtweek()));
// cell.setCellStyle(style);
// cell = row.createCell(6);
// cell.setCellValue(new HSSFRichTextString(w.getWproblem()));
// cell.setCellStyle(style);
// cell = row.createCell(7);
// cell.setCellValue(new HSSFRichTextString(w.getWremark()));
// cell.setCellStyle(style);
// }
// k = k + workList.size();
//}
//// 列尾
//int footRownumber = sheet.getLastRowNum();
//HSSFRow footRow = sheet.createRow(footRownumber + 1);
//HSSFCell footRowcell = footRow.createCell(0);
//footRowcell.setCellValue(new HSSFRichTextString(" 审 定：XXX 审 核：XXX 汇 总：XX"));
//footRowcell.setCellStyle(centerstyle);
//range = new CellRangeAddress(footRownumber + 1, footRownumber + 1, 0, 7);
//sheet.addMergedRegion(range);
//HttpServletResponse response = getResponse();
//HttpServletRequest request = getRequest();
//String filename = "未命名.xls";//设置下载时客户端Excel的名称
// // 请见：http://zmx.iteye.com/blog/622529
//filename = Util.encodeFilename(filename, request);
//response.setContentType("application/vnd.ms-excel");
//response.setHeader("Content-disposition", "attachment;filename=" + filename);
//OutputStream ouputStream = response.getOutputStream();
//workbook.write(ouputStream);
//ouputStream.flush();
//ouputStream.close();
}
catch (Exception e)
{
string s = e.ToString();
}
return(null);
}
/// <summary>
/// 设置标题
/// </summary>
/// <param name="root"></param>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="columns"></param>
internal void SetHeader <T>(Root root, HSSFWorkbook workbook, ISheet sheet, List <PropertyInfo> columns)
{
if (root.head.defaultwidth.HasValue)
{
//设置表格默认宽高
sheet.DefaultColumnWidth = root.head.defaultwidth.Value; //12
}
if (root.head.defaultheight.HasValue)
{
//设置表格默认行高
sheet.DefaultRowHeight = (short)root.head.defaultheight.Value; //25
}
if (!root.head.borderstyle.IsNullOrEmpty())
{
string bStyle = root.head.borderstyle.Trim();
if (!string.IsNullOrEmpty(bStyle))
{
switch (bStyle)
{
case "none":
WholeBorderStyle = BorderStyle.None;
break;
case "solid":
WholeBorderStyle = BorderStyle.Thin;
break;
case "dashed":
WholeBorderStyle = BorderStyle.Dashed;
break;
case "dotted":
WholeBorderStyle = BorderStyle.Dotted;
break;
case "double":
WholeBorderStyle = BorderStyle.Double;
break;
default:
WholeBorderStyle = BorderStyle.Thin;
break;
}
}
}
XlPalette = workbook.GetCustomPalette();
if (!string.IsNullOrEmpty(root.head.bordercolor))
{
Color co = ColorTranslator.FromHtml(root.head.bordercolor);
XlPalette.SetColorAtIndex(HSSFColor.Plum.Index, (byte)co.R, (byte)co.G, (byte)co.B);
WholeBorderColor = NPOI.HSSF.Util.HSSFColor.Plum.Index;//这句代码根据16进制不起作用，起到颜色初始化
}
//创建行
List <IRow> rowList = new List <IRow>();
Int32 rowN = root.head.rowspan.GetInt32();
for (Int32 i = 0; i < rowN; i++)
{
IRow row = sheet.CreateRow(i);
rowList.Add(row);
}
//合并单元格
//填充内容
for (Int32 i = 0; i < root.head.columns.Count; i++)
{
//读取最重要的区域,0=fromRow,1=toRow,2=fromColumn,3=toColumn
ExportColumn ec = root.head.columns[i];
Int32[] c = ec.cellregion.Split(',').ToIntArray();
if (c[2] == c[3])
{
PropertyInfo pi = typeof(T).GetProperty(ec.dataIndex);
columns.Add(pi);
}
if (c[0] < c[1] || c[2] < c[3]) //例如1,1,2,2 第二行中的第3列,例如1,1,2,7 第二行中的(第3列到8列),合并列
{
CellRangeAddress cellr = new CellRangeAddress(c[0], c[1], c[2], c[3]);
sheet.AddMergedRegion(cellr);
//设置边框
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(cellr, WholeBorderStyle, WholeBorderColor);
}
}
// 是否创建表头
if (!rowList.IsNullOrEmpty() && rowList.Count > 0)
{
//填充内容
for (Int32 i = 0; i < root.head.columns.Count; i++)
{
//读取最重要的区域,0=fromRow,1=toRow,2=fromColumn,3=toColumn
ExportColumn ec = root.head.columns[i];
Int32[] c = ec.cellregion.Split(',').ToIntArray();
CellRangeAddress cellr = new CellRangeAddress(c[0], c[1], c[2], c[3]);
//计算text要插入的位置的索引
Int32 txtIndex = -1;
Int32 txtRow = -1;
ICellStyle cellStyle = GetCellStyle(workbook, ExcelStyle.Title, ec);
if ((c[0] == c[1] && c[2] == c[3]) || (c[0] == c[1] && c[2] < c[3]))
{ //例如1,1,2,2 第二行中的第3列,例如1,1,2,7 第二行中的(第3列到8列)
txtIndex = c[2];
txtRow = c[0];
ICell newCell = rowList[txtRow].CreateCell(txtIndex);
//设置单元格的高度
if (!ec.height.IsNullOrEmpty())
{
rowList[txtRow].Height = (short)(ec.height * 20);
}
SetHeadCellBold(ec);
newCell.SetCellValue(ec.text);
//水平对齐 默认居中
cellStyle.Alignment = ec.halign.IsNullOrEmpty() ? HorizontalAlignment.Center : ec.halign.ToHorAlign();
newCell.CellStyle = cellStyle;
sheet.AddMergedRegion(cellr);
//设置边框
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(cellr, WholeBorderStyle, WholeBorderColor);
}
if (c[0] < c[1] && c[2] == c[3]) //合并c[0]到c[1]行 ，列没变 ， 'cellregion':'0,1,1,1',
{
txtIndex = c[2];
txtRow = c[0];
ICell newCell = rowList[txtRow].CreateCell(txtIndex);
//设置单元格的高度
if (!ec.height.IsNullOrEmpty())
{
rowList[txtRow].Height = (short)(ec.height * 20);
}
SetHeadCellBold(ec);
newCell.SetCellValue(ec.text);
//水平对齐 默认居中
cellStyle.Alignment = ec.halign.IsNullOrEmpty() ? HorizontalAlignment.Center : ec.halign.ToHorAlign();
newCell.CellStyle = cellStyle;
sheet.AddMergedRegion(cellr);
//设置边框
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(cellr, WholeBorderStyle, WholeBorderColor);
}
if (c[0] < c[1] && c[2] < c[3]) //合并c[0]到c[1]行 ，列没变 ， 'cellregion':'4,5,2,4',
{
txtIndex = c[2];
txtRow = c[0];
ICell newCell = rowList[txtRow].CreateCell(txtIndex);
//设置单元格的高度
if (!ec.height.IsNullOrEmpty())
{
rowList[txtRow].Height = (short)(ec.height * 20);
}
SetHeadCellBold(ec);
newCell.SetCellValue(ec.text);
//水平对齐 默认居中
cellStyle.Alignment = ec.halign.IsNullOrEmpty() ? HorizontalAlignment.Center : ec.halign.ToHorAlign();
newCell.CellStyle = cellStyle;
}
//设置单元格的宽度
if (!root.head.defaultwidth.IsNullOrEmpty() && !ec.width.IsNullOrEmpty())
{
sheet.SetColumnWidth(i, ec.width * 40);
}
else
{
sheet.AutoSizeColumn(i); //每列宽度自适应
}
}
}
}
/// <summary>
/// 导出报表
/// </summary>
/// <param name="title"></param>
/// <param name="fileName"></param>
/// <param name="Listname"></param>
/// <param name="dic"></param>
/// <returns></returns>
public ActionResult ExportData(string title, string fileName, string Listname,
List <IGrouping <string, STAT_STAFF_LARGE_BUSI_D_Handle_SUB> > dic)
{
#region 加载模板文件到工作簿对象中
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(Server.MapPath("/Areas/STAT/Views/STATTemp/业务办理分析.xls"), FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
#endregion
#region 根据模板设置工作表的内容
ISheet sheet1 = hssfworkbook.GetSheet("业务办理分析");
int rowIndex = 2;
sheet1.GetRow(0).Cells[0].SetCellValue(title);
sheet1.GetRow(1).Cells[1].SetCellValue(Listname);
if (dic != null && dic.Count > 0)
{
#region 数据单元格样式
var cellFont = hssfworkbook.CreateFont();
var cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index;
cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
#endregion
var i = 0;
var beginRowIndex = rowIndex;
foreach (var stat in dic)
{
++i;
var index = i;
var hallname = stat.Key;
var templist = stat.ToList();
var busiTotal = templist.Sum(m => m.BUSI_CNT);
var convertTotal = templist.Sum(m => m.CONVERT_BUSI_CNT);
var handleTotal = templist.Sum(m => m.HANDLE_DUR);
var overTimeTotal = templist.Sum(m => m.OVERTIME_HANDLE_CNT);
var localTotal = templist.Sum(m => m.LOCAL_CNT);
beginRowIndex = rowIndex;
foreach (var item in templist)
{
var xlsrow = sheet1.CreateRow(rowIndex);
if (index == i)
{
xlsrow.CreateCell(0).SetCellValue(i);
xlsrow.CreateCell(1).SetCellValue(stat.Key);
}
else
{
xlsrow.CreateCell(0);
xlsrow.CreateCell(1);
}
xlsrow.CreateCell(2).SetCellValue(item.DLS_SERIALNAME);
xlsrow.CreateCell(3).SetCellValue(item.BUSI_CNT);
xlsrow.CreateCell(4).SetCellValue(item.CONVERT_BUSI_CNT.ToString("####"));
xlsrow.CreateCell(5).SetCellValue(item.AverageHANDLE.ToTimeString());
xlsrow.CreateCell(6).SetCellValue(item.OVERTIME_HANDLE_CNT);
xlsrow.CreateCell(7).SetCellValue(item.TimeoutRate.ToString("P"));
xlsrow.CreateCell(8).SetCellValue(item.LOCAL_CNT);
xlsrow.CreateCell(9).SetCellValue(item.CityRate.ToString("P"));
rowIndex++;
index++;
#region 设置单元格样式
SetCellStype(xlsrow, cellStyle);
#endregion
}
var totalrow = sheet1.CreateRow(rowIndex);
totalrow.CreateCell(0);
totalrow.CreateCell(1);
totalrow.CreateCell(2).SetCellValue("合计");
totalrow.CreateCell(3).SetCellValue(busiTotal);
totalrow.CreateCell(4).SetCellValue(convertTotal.ToString("####"));
totalrow.CreateCell(5).SetCellValue((busiTotal == 0 ? 0 : handleTotal / busiTotal).ToTimeString());
totalrow.CreateCell(6).SetCellValue(overTimeTotal);
totalrow.CreateCell(7).SetCellValue((busiTotal == 0 ? 0 : (decimal)overTimeTotal / busiTotal).ToString("P"));
totalrow.CreateCell(8).SetCellValue(localTotal);
totalrow.CreateCell(9).SetCellValue((busiTotal == 0 ? 0 : (decimal)localTotal / (decimal)busiTotal).ToString("P"));
var cellRangeAddress = new CellRangeAddress(beginRowIndex, beginRowIndex + templist.Count, 0, 0);
sheet1.AddMergedRegion(cellRangeAddress);
var cellRangeAddress2 = new CellRangeAddress(beginRowIndex, beginRowIndex + templist.Count, 1, 1);
sheet1.AddMergedRegion(cellRangeAddress2);
beginRowIndex++;
SetCellStype(sheet1.GetRow(rowIndex), cellStyle);
var footercellFont = hssfworkbook.CreateFont();
var footercellStyle = hssfworkbook.CreateCellStyle();
footercellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
footercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
footercellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
var HSSFPalette = hssfworkbook.GetCustomPalette();
Color c = ToColor("#fffdbb");
HSSFPalette.SetColorAtIndex(NPOI.HSSF.Util.HSSFColor.Yellow.Index, c.R, c.G, c.B);
SetCellStype(totalrow, footercellStyle);
rowIndex++;
}
}
//强制Excel重新计算表中所有的公式
sheet1.ForceFormulaRecalculation = true;
#endregion
#region 写入到客户端
var ms = new MemoryStream();
//将工作簿的内容放到内存流中
hssfworkbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", fileName + ".xls"));
#endregion
}
/// <summary>
/// 普通单表导出npoi（集合数据+样式）
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button2_Click(object sender, EventArgs e)
{
string filename = "test.xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
Response.Clear();
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1名称");
CellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.FillBackgroundColor = HSSFColor.PINK.index;
var row0 = sheet1.CreateRow(0).CreateCell(0);
row0.SetCellValue("This is a Sample");//sheet标题
row0.CellStyle = style;
var j = 17;
#region 居中/自动换行
CellStyle styleCenter = hssfworkbook.CreateCellStyle(); //样式
styleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //文字水平对齐方式
styleCenter.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER; //文字垂直对齐方式
styleCenter.WrapText = true; //自动换行
sheet1.CreateRow(j).CreateCell(j).CellStyle = styleCenter;
var cell17 = sheet1.CreateRow(j).CreateCell(j);
var cell172 = sheet1.CreateRow(j).CreateCell(j + 1);
cell17.CellStyle = styleCenter;
cell17.SetCellValue("VLOOKUP函数和“两列同时匹配”的应用,升的网易博客");
//cell172.SetCellValue("VLOOKUP函数和“两列同时匹配”的应用,升的网易博客");
j++;
#endregion
#region 设置宽高度
sheet1.SetColumnWidth(1, 20 * 256); //宽度-每个字符宽度是1/256。 所以20 * 256就是20个字符宽度。
var rowwh = sheet1.CreateRow(j);
rowwh.HeightInPoints = 50; //高度
rowwh.CreateCell(j).SetCellValue("宽高度");
j++;
#endregion
#region 自适应宽度（对中文不友好）+自动换行
/*场景：
* 12林学1班
* 12林学1班
*/
CellStyle autoAndWrap = hssfworkbook.CreateCellStyle(); //样式
autoAndWrap.WrapText = true; //自动换行
var rowwhauto = sheet1.CreateRow(j);
var cellauto = rowwhauto.CreateCell(j);
cellauto.SetCellValue(j + "自适应宽高度自适应宽高度\n自适应宽高度自适应宽高度\n自适应宽高度自适应宽高度");
sheet1.AutoSizeColumn(j);
cellauto.CellStyle = autoAndWrap;
j++;
#endregion
#region 设置背景色
CellStyle style1 = hssfworkbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index;
style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
sheet1.CreateRow(j).CreateCell(j).CellStyle = style1;
j++;
#endregion
#region 自定义背景色
HSSFPalette palette = hssfworkbook.GetCustomPalette(); //调色板实例
palette.SetColorAtIndex((short)8, (byte)184, (byte)204, (byte)228);
HSSFColor hssFColor = palette.FindColor((byte)184, (byte)204, (byte)228);
CellStyle style2 = hssfworkbook.CreateCellStyle();
style2.FillPattern = FillPatternType.SOLID_FOREGROUND;
style2.FillForegroundColor = hssFColor.GetIndex();
sheet1.CreateRow(j).CreateCell(j).CellStyle = style2;
j++;
#endregion
#region 设置字体颜色
CellStyle style3 = hssfworkbook.CreateCellStyle();
Font font1 = hssfworkbook.CreateFont();
font1.Color = hssFColor.GetIndex();//颜色
style3.SetFont(font1);
var cell20 = sheet1.CreateRow(j).CreateCell(j);
cell20.CellStyle = style3;
cell20.SetCellValue("666666666");
j++;
#endregion
List <ModelStu> data = StuDaTa.GetData();
string[] arrthead = { "ID", "name", "age", "pc" };
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, arrthead.Length - 1));
Row row1 = sheet1.CreateRow(1);
for (int i = 0; i < arrthead.Length; i++)
{
row1.CreateCell(i).SetCellValue(arrthead[i]);
}
for (int i = 0; i < data.Count; i++)
{
Row row = sheet1.CreateRow(i + 2);
var colIndex = 0;
row.CreateCell(colIndex++).SetCellValue(data[i].id);
row.CreateCell(colIndex++).SetCellValue(data[i].name);
row.CreateCell(colIndex++).SetCellValue(data[i].age);
row.CreateCell(colIndex).SetCellValue(data[i].pc);
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
Response.BinaryWrite(file.GetBuffer());
Response.End();
hssfworkbook = null;
file.Close();
file.Dispose();
}
private void ExportTickets(Tickets tickets)
{
string fileName;
using (var form = new SaveFileDialog())
{
form.Filter = "Excel (*.xls)|*.xls|All Files (*.*)|*.*";
form.RestoreDirectory = true;
if (form.ShowDialog(this) != DialogResult.OK)
{
return;
}
fileName = form.FileName;
}
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("Zendesk requests");
var headerStyle = CreateHeaderStyle(workbook);
var dateStyle = CreateDateStyle(workbook);
var wrapStyle = workbook.CreateCellStyle();
wrapStyle.WrapText = true;
var urlFont = workbook.CreateFont();
urlFont.Underline = FontUnderlineType.Single;
urlFont.Color = workbook.GetCustomPalette().FindColor(0, 0, 255).Indexed;
var urlStyle = workbook.CreateCellStyle();
urlStyle.SetFont(urlFont);
// Create the headers.
int offset = 0;
var row = sheet.CreateRow(0);
AddHeader(row, ref offset, "ID", headerStyle);
AddHeader(row, ref offset, "Status", headerStyle);
AddHeader(row, ref offset, "Priority", headerStyle);
AddHeader(row, ref offset, "Type", headerStyle);
AddHeader(row, ref offset, "Subject", headerStyle);
AddHeader(row, ref offset, "Description", headerStyle);
AddHeader(row, ref offset, "Requester", headerStyle);
AddHeader(row, ref offset, "Assignee", headerStyle);
AddHeader(row, ref offset, "Created", headerStyle);
AddHeader(row, ref offset, "Updated", headerStyle);
AddHeader(row, ref offset, "Due", headerStyle);
for (int i = 0; i < tickets.Items.Count; i++)
{
row = sheet.CreateRow(i + 1);
var ticket = tickets.Items[i];
offset = 0;
AddUrl(row, ref offset, (long)ticket["id"], (string)ticket["url"], urlStyle);
AddCell(row, ref offset, Prettify((string)ticket["status"]));
AddCell(row, ref offset, Prettify((string)ticket["priority"]));
AddCell(row, ref offset, Prettify((string)ticket["type"]));
AddCell(row, ref offset, (string)ticket["subject"]);
AddCell(row, ref offset, (string)ticket["description"], wrapStyle);
AddCell(row, ref offset, GetUser(tickets.Users, (long?)ticket["requester_id"]));
AddCell(row, ref offset, GetUser(tickets.Users, (long?)ticket["assignee_id"]));
AddCell(row, ref offset, (DateTime?)ticket["created_at"], dateStyle);
AddCell(row, ref offset, (DateTime?)ticket["updated_at"], dateStyle);
AddCell(row, ref offset, (DateTime?)ticket["due"], dateStyle);
}
using (var stream = File.Create(fileName))
{
workbook.Write(stream);
}
try
{
Process.Start(fileName);
}
catch
{
// Ignore exceptions.
}
}
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("生成成功！", "成功");
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="lists"></param>
/// <param name="head">中文列名对照</param>
/// <param name="fileName">文件名</param>
public static void ExportExcel(List <T> lists, Dictionary <string, string> head, string fileName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = null;
HSSFRow headerRow = null;
//设置表头样式
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeight = 14 * 14;
font.Boldweight = 1000;
headStyle.SetFont(font);
//设置分组样式
HSSFPalette palette = workbook.GetCustomPalette(); //调色板实例
palette.SetColorAtIndex((byte)8, (byte)204, (byte)204, (byte)0);
HSSFColor hssFColor = palette.FindColor((byte)204, (byte)204, (byte)0);
ICellStyle GroupStyle = workbook.CreateCellStyle();
GroupStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
GroupStyle.FillForegroundColor = hssFColor.GetIndex();
IFont Groupfont = workbook.CreateFont();
Groupfont.Boldweight = 1000;
GroupStyle.SetFont(Groupfont);
bool h = false;
Type type = typeof(T);
PropertyInfo[] properties = type.GetProperties();
var mod = lists.Count() % 65535;
var index = lists.Count() / 65535;
if (mod > 0)
{
index = index + 1;
}
//没有数据时导出表头
if (index == 0)
{
int i = 0;
sheet = workbook.CreateSheet("Sheet0") as HSSFSheet;
headerRow = sheet.CreateRow(0) as HSSFRow;
foreach (var dic in head)//循环列表头集合作为列数
{
string[] names = dic.Key.ToString().Split('@');
string colname = dic.Value.ToString();
ICell cell = headerRow.CreateCell(i);
cell.CellStyle = headStyle;
cell.SetCellValue(colname);
i++;
}
}
for (int idx = 1; idx <= index; idx++)
{
sheet = workbook.CreateSheet("Sheet" + idx) as HSSFSheet;
headerRow = sheet.CreateRow(0) as HSSFRow;
var count = 65535;
if (idx == index)
{
count = mod;
}
for (var j = 0; j < count; j++)//循环记录总数作为行数
{
HSSFRow dataRow = sheet.CreateRow(j + 1) as HSSFRow;
int i = 0;
foreach (var dic in head)//循环列表头集合作为列数
{
string[] names = dic.Key.ToString().Split('@');
string colname = dic.Value.ToString();
string name = names[0];
bool isTrue = false;//是否基础数据
if (names.Length == 2)
{
isTrue = bool.Parse(names[1]);
}
var info = properties.Where(x => x.Name == name).FirstOrDefault();
object value = info == null ? null : info.GetValue(lists[65535 * (idx - 1) + j], null);
string colvalue = value == null ? "" : value.ToString();
//if (isTrue)//获取基础数据
//{
// colvalue = HtmlExtensions.GetBasicObjectNameByValue(colvalue);
//}
if (!h)
{
if ((!name.Equals("IsGroup")))
{
ICell cell = headerRow.CreateCell(i);
cell.CellStyle = headStyle;
cell.SetCellValue(colname);
if (value != null)
{
Type t = value.GetType();
string strt = t.Name;
if (t.Name == "Nullable`1")
{
strt = t.GetGenericArguments()[0].Name;
}
switch (strt)
{
case "Decimal":
case "Double":
dataRow.CreateCell(i).SetCellValue(Double.Parse(value.ToString()));
break;
case "Int":
dataRow.CreateCell(i).SetCellValue(int.Parse(value.ToString()));
break;
case "Float":
dataRow.CreateCell(i).SetCellValue(float.Parse(value.ToString()));
break;
default:
dataRow.CreateCell(i).SetCellValue(colvalue);
break;
}
}
else
{
dataRow.CreateCell(i).SetCellValue(colvalue);
}
}
}
else
{
if ((!name.Equals("IsGroup")))
{
if (value != null)
{
Type t = value.GetType();
string strt = t.Name;
if (t.Name == "Nullable`1")
{
strt = t.GetGenericArguments()[0].Name;
}
switch (strt)
{
case "Decimal":
case "Double":
dataRow.CreateCell(i).SetCellValue(Double.Parse(value.ToString()));
break;
case "Int":
dataRow.CreateCell(i).SetCellValue(int.Parse(value.ToString()));
break;
case "Float":
dataRow.CreateCell(i).SetCellValue(float.Parse(value.ToString()));
break;
default:
dataRow.CreateCell(i).SetCellValue(colvalue);
break;
}
}
else
{
dataRow.CreateCell(i).SetCellValue(colvalue);
}
}
}
//========================对特定标志行设置颜色========================
if (name.Equals("IsGroup") && colvalue.Equals("Y"))
{
for (int m = 0; m < i; m++)
{
dataRow.GetCell(m).CellStyle = GroupStyle;
}
}
//================================end=================================
i++;
}
h = true;
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
ms.Close();
ms.Dispose();
}
public MemoryStream GetExcelPackage()
{
using (var fs = new MemoryStream())
{
var workbook = new HSSFWorkbook();
//var img = Image.FromFile(System.IO.Directory.GetCurrentDirectory() + "/gitea-sm.png");
//var stream = new MemoryStream();// img.Size
//img.Save(stream, System.Drawing.Imaging.ImageFormat.Png);
//FileStream stream = new FileStream(System.IO.Directory.GetCurrentDirectory() + "/gitea-sm.png", FileMode.Open, FileAccess.Read);
//var arr = new byte[stream.Length];
//stream.Read(arr, 0, arr.Length);
//var arr = File.ReadAllBytes(System.IO.Directory.GetCurrentDirectory() + "/gitea-sm.png");
// workbook.AddPicture(arr, PictureType.PNG);
//stream.Close();
LoadStyle(workbook);
var sheet = workbook.CreateSheet(SheetName);
//添加图片
var data = File.ReadAllBytes(System.IO.Directory.GetCurrentDirectory() + "/gitea-sm.png");
var pictureIndex = workbook.AddPicture(data, PictureType.PNG);
var helper = workbook.GetCreationHelper();
var drawing = sheet.CreateDrawingPatriarch();
var anchor = helper.CreateClientAnchor();
anchor.Col1 = 0; //0 index based column
anchor.Row1 = 0; //0 index based row
var picture = drawing.CreatePicture(anchor, pictureIndex);
picture.Resize(1, 2);
sheet.DefaultColumnWidth = 4;
sheet.DisplayGridlines = false;
SetCell(workbook, sheet, 2, 2, 22, 29, $"発行年月日 {PublishDate.ToString("yyyy年MM月dd日")}", _style12);
SetCell(workbook, sheet, 5, 11, 1, 13, $"{ToCompany} 御中", _style10);
SetCell(workbook, sheet, 5, 5, 17, 29, FromCompany, _style12);
SetCell(workbook, sheet, 6, 6, 17, 29, FromDepartment, _style11);
////设置标题
SetCell(workbook, sheet, 16, 17, 0, 30, Title, _style16);
//设置周期
SetCell(workbook, sheet, 20, 20, 1, 12, Period.ToString("yyyy年MM月度"), _style14);
#region 代理商信息
SetCell(workbook, sheet, 22, 23, 1, 4, "代理店コード", _styleTopLeft);
SetCell(workbook, sheet, 22, 23, 5, 13, AgentMessage.AgentCode, _styleTopRight);
SetCell(workbook, sheet, 24, 25, 1, 4, "振込日", _styleLeft);
SetCell(workbook, sheet, 24, 25, 5, 13, AgentMessage.DemittanceDate?.ToString("yyyy年MM月dd日"), _styleRight);
SetCell(workbook, sheet, 26, 27, 1, 4, "振込金額", _styleBottomLeft);
SetCell(workbook, sheet, 26, 27, 5, 13, AgentMessage.DemittanceAmount.ToString(), _styleBottomRight);
#endregion
#region 代理商帐户信息
SetCell(workbook, sheet, 22, 23, 17, 20, "金融機関名", _styleTopLeft);
SetCell(workbook, sheet, 22, 23, 21, 29, AgentAccount.BankName, _styleTopRight);
SetCell(workbook, sheet, 24, 25, 17, 20, "支店名", _styleLeft);
SetCell(workbook, sheet, 24, 25, 21, 29, AgentAccount.BranchBankName, _styleRight);
SetCell(workbook, sheet, 26, 27, 17, 20, "口座番号", _styleLeft);
SetCell(workbook, sheet, 26, 27, 21, 22, AgentAccount.AccountMode, _dataStyle);
SetCell(workbook, sheet, 26, 27, 23, 29, AgentAccount.Account, _styleRight);
SetCell(workbook, sheet, 28, 29, 17, 20, "口座名義", _styleBottomLeft);
SetCell(workbook, sheet, 28, 29, 21, 29, AgentAccount.AccountOwner, _styleBottomRight);
#endregion
#region 列表
HSSFPalette palette = workbook.GetCustomPalette(); //调色板实例
palette.SetColorAtIndex(11, 252, 228, 214);
_colotStyle.FillForegroundColor = 11;
SetCell(workbook, sheet, 32, 32, 1, 1, null, _colotStyle);
SetCell(workbook, sheet, 32, 32, 2, 4, "コード", _colotStyle);
SetCell(workbook, sheet, 32, 32, 5, 9, "加盟店", _colotStyle);
SetCell(workbook, sheet, 32, 32, 10, 14, "対象期間", _colotStyle);
SetCell(workbook, sheet, 32, 32, 15, 18, "決済利用額", _colotStyle);
SetCell(workbook, sheet, 32, 32, 19, 21, "手数料率", _colotStyle);
SetCell(workbook, sheet, 32, 32, 22, 25, "手数料額", _colotStyle);
SetCell(workbook, sheet, 32, 32, 26, 29, "備考", _colotStyle);
var rowIndex = 33;
var sn = 1;
var total = 0m;
foreach (var item in Data)
{
total += item.RateAmount;
SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, sn++.ToString(), _dataStyle);
SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, item.Code, _dataStyle);
SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, item.Name, _dataStyle);
SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, item.TimeSection, _dataStyle);
SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, item.Amount.ToString(), _dataStyle);
SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, item.Rate.ToString(), _dataStyle);
SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, item.RateAmount.ToString(), _dataStyle);
SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, item.Memo, _dataStyle);
rowIndex++;
}
SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, "計", _styleTotal);
SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, "", _styleTotal);
SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, "", _styleTotal);
SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, "税抜き", _styleTotal);
SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, "", _styleTotal);
SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, "", _styleTotal);
SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, total.ToString(), _styleTotal);
SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, "", _styleTotal);
#endregion
workbook.Write(fs);
return(fs);
}
}
//public IActionResult LoadExcel(IFormFile file)
//{
// IExcelService _IExcelService = new ExcelService();
// try
// {
// var rows = _IExcelService.ExcelToList<ImportExcelModel>(file.OpenReadStream(), file.FileName);
// return Content("导入成功！");
// }
// catch (Exception e) { return Content("导入失败"); }
//}
#endregion
#region Excel导出
public static byte[] ExportToExcel <T>(List <T> entities, ExcelModel model)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFPalette palette = workbook.GetCustomPalette();
HSSFColor hssFColor;
byte red, green, bule;
if (model.DataFields == null || model.DataFields.Length == 0)
{
model.DataFields = model.ColumnNames;
}
#region 标题
// 标题字体
IFont titleFont = workbook.CreateFont();
var titleColor = model.TitleRow.CellStyle.Font.Color;
red = titleColor[0];
green = titleColor[1];
bule = titleColor[2];
palette.SetColorAtIndex(8, red, green, bule);
hssFColor = palette.FindColor(red, green, bule);
titleFont.Color = hssFColor.Indexed;
titleFont.FontHeightInPoints = model.TitleRow.CellStyle.Font.FontHeightInPoints;
// 标题前景色
var titleForegroundColor = model.TitleRow.CellStyle.FillForegroundColor;
red = titleForegroundColor[0];
green = titleForegroundColor[1];
bule = titleForegroundColor[2];
palette.SetColorAtIndex(9, red, green, bule);
hssFColor = palette.FindColor(red, green, bule);
// 标题
ICellStyle titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(titleFont);
titleStyle.FillPattern = FillPattern.SolidForeground;
titleStyle.FillForegroundColor = hssFColor.Indexed;
titleStyle.Alignment = HorizontalAlignment.Center;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
ISheet sheet = workbook.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
row.HeightInPoints = model.DataRow.HeightInPoints;
ICell cell;
for (int i = 0; i < model.ColumnNames.Length; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = titleStyle;
cell.SetCellValue(model.ColumnNames[i]);
}
#endregion
if (entities.Count > 0)
{
ICellStyle cellStyle = workbook.CreateCellStyle();
IFont cellFont = workbook.CreateFont();
cellFont.FontHeightInPoints = model.DataRow.CellStyle.Font.FontHeightInPoints;
cellStyle.SetFont(cellFont);
cellStyle.VerticalAlignment = VerticalAlignment.Center;
for (int i = 0; i < entities.Count; i++)
{
row = sheet.CreateRow(i + 1);
row.HeightInPoints = model.DataRow.HeightInPoints;
object entity = entities[i];
for (int j = 0; j < model.DataFields.Length; j++)
{
// 数据行
object cellValue = entity.GetType().GetProperty(model.DataFields[j]).GetValue(entity);
cell = row.CreateCell(j);
cell.CellStyle = cellStyle;
cell.SetCellValue(Convert.ToString(cellValue));
}
}
// 调整列宽
for (int i = 0; i <= entities.Count; i++)
{
sheet.AutoSizeColumn(i);
}
for (int columnNum = 0; columnNum <= model.ColumnNames.Length; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow;
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
columnWidth = Math.Min(columnWidth, 255);
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
return(ms.GetBuffer());
}
}
public MemoryStream GetExcelPackage()
{
using (var fs = new MemoryStream())
{
var workbook = new HSSFWorkbook();
LoadStyle(workbook);
var sheet = workbook.CreateSheet(SheetName);
sheet.DefaultColumnWidth = 4;
sheet.DisplayGridlines = false;
SetCell(workbook, sheet, 2, 2, 22, 29, $"発行年月日 {PublishDate.ToString("yyyy年MM月dd日")}", _style12);
SetCell(workbook, sheet, 5, 11, 1, 13, $"{ToCompany} 御中", _style10);
SetCell(workbook, sheet, 5, 5, 17, 29, FromCompany, _style12);
SetCell(workbook, sheet, 6, 6, 17, 29, FromDepartment, _style11);
////设置标题
SetCell(workbook, sheet, 16, 17, 0, 30, Title, _style16);
//设置周期
SetCell(workbook, sheet, 20, 20, 1, 12, Period.ToString("yyyy年MM月度"), _style14);
#region 代理商信息
SetCell(workbook, sheet, 22, 23, 1, 4, "代理店コード", _styleTopLeft);
SetCell(workbook, sheet, 22, 23, 5, 13, AgentMessage.AgentCode, _styleTopRight);
SetCell(workbook, sheet, 24, 25, 1, 4, "振込日", _styleLeft);
SetCell(workbook, sheet, 24, 25, 5, 13, AgentMessage.DemittanceDate?.ToString("yyyy年MM月dd日"), _styleRight);
SetCell(workbook, sheet, 26, 27, 1, 4, "振込金額", _styleBottomLeft);
SetCell(workbook, sheet, 26, 27, 5, 13, AgentMessage.DemittanceAmount.ToString(), _styleBottomRight);
#endregion
#region 代理商帐户信息
SetCell(workbook, sheet, 22, 23, 17, 20, "金融機関名", _styleTopLeft);
SetCell(workbook, sheet, 22, 23, 21, 29, AgentAccount.BankName, _styleTopRight);
SetCell(workbook, sheet, 24, 25, 17, 20, "支店名", _styleLeft);
SetCell(workbook, sheet, 24, 25, 21, 29, AgentAccount.BranchBankName, _styleRight);
SetCell(workbook, sheet, 26, 27, 17, 20, "口座番号", _styleLeft);
SetCell(workbook, sheet, 26, 27, 21, 22, AgentAccount.AccountMode, _dataStyle);
SetCell(workbook, sheet, 26, 27, 23, 29, AgentAccount.Account, _styleRight);
SetCell(workbook, sheet, 28, 29, 17, 20, "口座名義", _styleBottomLeft);
SetCell(workbook, sheet, 28, 29, 21, 29, AgentAccount.AccountOwner, _styleBottomRight);
#endregion
#region 列表
HSSFPalette palette = workbook.GetCustomPalette(); //调色板实例
palette.SetColorAtIndex(11, 252, 228, 214);
_colotStyle.FillForegroundColor = 11;
SetCell(workbook, sheet, 32, 32, 1, 1, null, _colotStyle);
SetCell(workbook, sheet, 32, 32, 2, 4, "コード", _colotStyle);
SetCell(workbook, sheet, 32, 32, 5, 9, "加盟店", _colotStyle);
SetCell(workbook, sheet, 32, 32, 10, 14, "対象期間", _colotStyle);
SetCell(workbook, sheet, 32, 32, 15, 18, "決済利用額", _colotStyle);
SetCell(workbook, sheet, 32, 32, 19, 21, "手数料率", _colotStyle);
SetCell(workbook, sheet, 32, 32, 22, 25, "手数料額", _colotStyle);
SetCell(workbook, sheet, 32, 32, 26, 29, "備考", _colotStyle);
var rowIndex = 33;
var sn = 1;
var total = 0m;
foreach (var item in Data)
{
total += item.RateAmount;
SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, sn++.ToString(), _dataStyle);
SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, item.Code, _dataStyle);
SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, item.Name, _dataStyle);
SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, item.TimeSection, _dataStyle);
SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, item.Amount.ToString(), _dataStyle);
SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, item.Rate.ToString(), _dataStyle);
SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, item.RateAmount.ToString(), _dataStyle);
SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, item.Memo, _dataStyle);
rowIndex++;
}
SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, "計", _styleTotal);
SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, "", _styleTotal);
SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, "", _styleTotal);
SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, "税抜き", _styleTotal);
SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, "", _styleTotal);
SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, "", _styleTotal);
SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, total.ToString(), _styleTotal);
SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, "", _styleTotal);
#endregion
workbook.Write(fs);
return(fs);
}
}
private static void ApplyOptions(HSSFWorkbook wk, bool useTitle, int splitCount, ExcelOptions option)
{
try
{
#region 单元格样式
if (option != null && option.CellStyle != null && wk != null)
{
var cellStyle = wk.CreateCellStyle();
var cellStylex = wk.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
if (option.CellStyle.FontHeight.HasValue)
{
var font = wk.CreateFont();
font.FontHeight = option.CellStyle.FontHeight.Value * 100;
font.FontName = "宋体";
cellStyle.SetFont(font);
}
if (option.CellStyle.UseBorder)
{
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
}
cellStylex.CloneStyleFrom(cellStyle);
if (option.CellStyle.UseSkipColor)
{
HSSFPalette palette = wk.GetCustomPalette(); //调色板实例
palette.SetColorAtIndex((short)8, (byte)184, (byte)204, (byte)228);
var color = palette.FindColor((byte)184, (byte)204, (byte)228);
cellStylex.FillBackgroundColor = color.Indexed;
cellStylex.FillPattern = FillPattern.SolidForeground;
}
for (int i = 0; i < wk.NumberOfSheets; i++)
{
var sheet = wk.GetSheetAt(i);
if (sheet != null)
{
for (int j = sheet.FirstRowNum; j <= sheet.LastRowNum; j++)
{
var row = sheet.GetRow(j);
if (row != null)
{
for (int k = row.FirstCellNum; k <= row.LastCellNum; k++)
{
var cell = row.GetCell(k);
if (cell != null)
{
if (j % 2 == 0)
{
cell.CellStyle = cellStyle;
}
else
{
cell.CellStyle = cellStylex;
}
}
}
if (option.AutoSizeColumn)
{
sheet.AutoSizeColumn(j);
}
}
}
}
}
}
#endregion
#region 合并区域参数
if (option != null && option.MergedRegions != null && wk != null)
{
//不允许区域相交
if (option.IsValidMergedRegions)
{
foreach (var mg in option.MergedRegions)
{
//先确定属于那个Excel
var startSheetIndex = (mg.FirstRow + 1) / (splitCount - (useTitle ? 1 : 0)) + ((mg.FirstRow + 1) % (splitCount - (useTitle ? 1 : 0)) == 0 ? 0 : 1) - 1;
var endSheetIndex = (mg.LastRow + 1) / (splitCount - (useTitle ? 1 : 0)) + ((mg.LastRow + 1) % (splitCount - (useTitle ? 1 : 0)) == 0 ? 0 : 1) - 1;
var firstRow = 0;
var lastRow = 0;
//合并区域在同一Sheet
if (startSheetIndex == endSheetIndex)
{
firstRow = mg.FirstRow - (startSheetIndex * (splitCount - (useTitle ? 1 : 0))) + (useTitle ? 1 : 0);
lastRow = mg.LastRow - (endSheetIndex * (splitCount - (useTitle ? 1 : 0))) + (useTitle ? 1 : 0);
wk.GetSheetAt(startSheetIndex).AddMergedRegion(new CellRangeAddress(firstRow, lastRow, mg.FirstCol, mg.LastCol));
}
else //if (endSheetIndex - startSheetIndex > 1)
{
firstRow = mg.FirstRow - (startSheetIndex * (splitCount - (useTitle ? 1 : 0))) + (useTitle ? 1 : 0);
lastRow = wk.GetSheetAt(startSheetIndex).LastRowNum;
wk.GetSheetAt(startSheetIndex).AddMergedRegion(new CellRangeAddress(firstRow, lastRow, mg.FirstCol, mg.LastCol));
for (int i = startSheetIndex + 1; i <= endSheetIndex; i++)
{
firstRow = wk.GetSheetAt(i).FirstRowNum + (useTitle ? 1 : 0);
lastRow = i == endSheetIndex ? (mg.LastRow - (endSheetIndex * (splitCount - (useTitle ? 1 : 0))) + (useTitle ? 1 : 0)) : wk.GetSheetAt(i).LastRowNum;
wk.GetSheetAt(i).AddMergedRegion(new CellRangeAddress(firstRow, lastRow, mg.FirstCol, mg.LastCol));
}
}
}
}
}
#endregion
}
catch
{
}
}
public static void ListToExcel(string fileName, string templetName, List <RecordMode> dt, string titleName, string sheetName)
{
FileStream fs1 = new FileStream(templetName, FileMode.Open, FileAccess.Read);
IWorkbook workBook = new XSSFWorkbook(fs1);
ISheet sheet = workBook.GetSheet(sheetName);
List <Color> colorList = new List <Color>();
colorList.Add(Color.FromArgb(255, 0, 0)); //红色
colorList.Add(Color.FromArgb(255, 255, 255)); //白色
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFPalette palette = hssfWorkbook.GetCustomPalette();
palette.SetColorAtIndex(999, colorList[0].R, colorList[0].G, colorList[0].B);
palette.SetColorAtIndex(998, colorList[1].R, colorList[1].G, colorList[1].B);
////第一行
//IRow row0 = sheet.GetRow(0);
//ICell cellTitle = row0.GetCell(0);
//cellTitle.SetCellValue(titleName);
//第二行
IRow row1 = sheet.GetRow(0);
for (int j = 0; j < 8; j++)
{
ICell cell = row1.GetCell(j);
cell.SetCellValue(cell.ToString());
}
for (int i = 0; i < dt.Count; i++)
{
IRow rowi = sheet.CreateRow(i + 1);
for (int j = 0; j < 8; j++)
{
//创建单元格
ICell cell = rowi.CreateCell(j);
//给单元格赋值
cell.SetCellValue(dt[i][j].ToString());
//给单元格设置样式
ICellStyle colorStyle = workBook.CreateCellStyle();
colorStyle.FillPattern = FillPattern.SolidForeground;
//cell.CellStyle.BorderBottom = BorderStyle.Thin;
//cell.CellStyle.BorderLeft = BorderStyle.Thin;
if (dt[i].isYC == true && (j == 4 || j == 5))
{
var v1 = palette.FindColor(colorList[0].R, colorList[0].G, colorList[0].B);
if (v1 == null)
{
throw new Exception("Color is not in Palette");
}
colorStyle.FillForegroundColor = v1.Indexed;
cell.CellStyle = colorStyle;
//cell.CellStyle.BorderDiagonalColor = IndexedColors.Yellow.Index;
//cell.CellStyle.FillPattern = FillPattern.SolidForeground;
//cell.CellStyle.FillForegroundColor = HSSFColor.Yellow.Index;
}
else
{
var v1 = palette.FindColor(colorList[1].R, colorList[1].G, colorList[1].B);
if (v1 == null)
{
throw new Exception("Color is not in Palette");
}
colorStyle.FillForegroundColor = v1.Indexed;
cell.CellStyle = colorStyle;
//cell.CellStyle.BorderDiagonalColor = IndexedColors.Yellow.Index;
//cell.CellStyle.FillPattern = FillPattern.SolidForeground;
//cell.CellStyle.FillForegroundColor = HSSFColor.White.Index;
}
}
}
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
workBook.Write(fs);
}
}
