public void TestBadIndexes()
{
//too small
hssfPalette.SetColorAtIndex((short)2, (byte)255, (byte)255, (byte)255);
//too large
hssfPalette.SetColorAtIndex((short)0x45, (byte)255, (byte)255, (byte)255);
//should still Match defaults;
CompareToDefaults(new ColorComparator2());
}
private static HSSFCellStyle generate(HSSFWorkbook wb_, int index, int r, int g, int b, bool bold)
{
short colorIndex = (short)index;
HSSFCellStyle style = (HSSFCellStyle)(wb_.CreateCellStyle());
HSSFPalette palette = ((HSSFWorkbook)wb_).GetCustomPalette();
palette.SetColorAtIndex(colorIndex, (byte)r, (byte)g, (byte)b);
style.FillForegroundColor = palette.GetColor(colorIndex).Indexed;
style.FillPattern = FillPattern.SolidForeground;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//是否换行
style.WrapText = true;
if (bold)
{
IFont font = wb_.CreateFont();
font.Boldweight = short.MaxValue;
style.SetFont(font);
}
return(style);
}
/// <summary>
/// 设置单元格背景色
/// </summary>
/// <returns></returns>
private ICellStyle setCellBackColor()
{
ICellStyle backColorStyle = workbook.CreateCellStyle();
IFont boldFont = workbook.CreateFont();
boldFont.FontHeightInPoints = 10;
boldFont.FontName = "微软雅黑";
boldFont.Boldweight = (short)FontBoldWeight.Bold;
HSSFPalette palette = ((HSSFWorkbook)workbook).GetCustomPalette();
Color backColor = Color.FromArgb(238, 236, 225);
short FIRST_COLOR_INDEX = (short)0x8;
//index的取值范围 0x8 - 0x40
palette.SetColorAtIndex((short)(FIRST_COLOR_INDEX), backColor.R, backColor.G, backColor.B);
backColorStyle.FillPattern = FillPattern.SolidForeground;
var v1 = palette.FindColor(backColor.R, backColor.G, backColor.B);
if (v1 == null)
{
throw new Exception("Color is not in Palette");
}
backColorStyle.FillForegroundColor = v1.GetIndex();
backColorStyle.SetFont(boldFont);
return(backColorStyle);
}
internal IColor CreateColor(string htmlColor)
{
Color color = ColorTranslator.FromHtml(htmlColor);
byte[] array = new byte[]
{
color.R,
color.G,
color.B
};
IColor result;
switch (this.ExcelVersion)
{
case ExcelVersion.XLS:
{
HSSFPalette customPalette = this.ExcelXls.GetCustomPalette();
if (this._palleteColorSize >= 63)
{
HSSFColor hSSFColor = customPalette.FindColor(color.R, color.G, color.B);
if (hSSFColor == null)
{
hSSFColor = customPalette.FindSimilarColor(color.R, color.G, color.B);
}
short?palleteColorSize = this._palleteColorSize;
this._palleteColorSize = (palleteColorSize.HasValue
? new short?((short)(palleteColorSize.GetValueOrDefault() + 1))
: null);
result = hSSFColor;
}
else
{
if (!this._palleteColorSize.HasValue)
{
this._palleteColorSize = new short?(8);
}
else
{
short?palleteColorSize = this._palleteColorSize;
this._palleteColorSize = (palleteColorSize.HasValue
? new short?((short)(palleteColorSize.GetValueOrDefault() + 1))
: null);
}
customPalette.SetColorAtIndex(this._palleteColorSize.Value, color.R, color.G, color.B);
HSSFColor hSSFColor = customPalette.GetColor(this._palleteColorSize.Value);
result = hSSFColor;
}
break;
}
case ExcelVersion.XLSX:
result = new XSSFColor(color);
break;
default:
throw new Exception(ErrorMessage.Excel_BadVersion);
}
return(result);
}
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)");
byte[] expectedRGB = HSSFColor.Coral.Triplet;
byte[] actualRGB = color.RGB;
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.RGB;
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);
}
private void LoadCustomColors(IWorkbook wb, ExcelFormat format)
{
if (format == ExcelFormat.xls)
{
var wbHssf = wb as HSSFWorkbook;
palette = wbHssf.GetCustomPalette();
palette.SetColorAtIndex(SUB_TOTAL_BACK_COLOR_INDEX, (byte)239, (byte)240, (byte)241);
palette.SetColorAtIndex(ALTERNATE_BACK_COLOR_INDEX, (byte)210, (byte)210, (byte)210);
palette.SetColorAtIndex(DARK_BLUE_EXCEL_2007, (byte)31, (byte)73, (byte)125);
customColors.Add(SUB_TOTAL_BACK_COLOR_INDEX, palette.GetColor(SUB_TOTAL_BACK_COLOR_INDEX));
customColors.Add(ALTERNATE_BACK_COLOR_INDEX, palette.GetColor(ALTERNATE_BACK_COLOR_INDEX));
customColors.Add(DARK_BLUE_EXCEL_2007, palette.GetColor(DARK_BLUE_EXCEL_2007));
}
else
{
customColors.Add(SUB_TOTAL_BACK_COLOR_INDEX, new XSSFColor(System.Drawing.Color.FromArgb(239, 240, 241)));
customColors.Add(ALTERNATE_BACK_COLOR_INDEX, new XSSFColor(System.Drawing.Color.FromArgb(210, 210, 210)));
customColors.Add(DARK_BLUE_EXCEL_2007, new XSSFColor(System.Drawing.Color.FromArgb(31, 73, 125)));
}
}
private int colorSpool = 10; //颜色值的精度
/// <summary>
/// 设置单元格样式，测试。
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="color"></param>
public bool SetCellColor(int row, int col, Color color)
{
IRow oneRow = sheet.GetRow(row);
if (oneRow == null)
{
oneRow = sheet.CreateRow(row);
}
ICell cell = oneRow.GetCell(col);
if (cell == null)
{
cell = oneRow.CreateCell(col);
}
#region 设置颜色
ICellStyle cellStyle = ThisCheckNeighbor(color);
if (cellStyle == null)
{
int nowColorrgb = color.ToArgb();
if (hssfPalette == null)
{
hssfPalette = workBook.GetCustomPalette();
}
//NPOI的调色板只允许 0x08-0x40共56种颜色的设置，设置多了就无法展示了。
int index = cellStyles.Count + 8;
if (index > 64)
{
return(false);
//throw new Exception("调色板超过了56种颜色！降低颜色精度试试！");
}
hssfPalette.SetColorAtIndex((short)index, color.R, color.G, color.B);
HSSFColor hssFColor = hssfPalette.FindColor(color.R, color.G, color.B);
if (hssFColor == null)
{
return(false);
}
cellStyle = workBook.CreateCellStyle();
cellStyles.Add(nowColorrgb, cellStyle);
cellStyle.FillForegroundColor = hssFColor.Indexed;
cellStyle.FillPattern = FillPattern.SolidForeground;
}
#endregion
cell.CellStyle = cellStyle;
return(true);
}
public ICellStyle TimeDataStyle()
{
if (timeDataStyle == null)
{
IDataFormat dataformat = hssfworkbook.CreateDataFormat();
timeDataStyle = hssfworkbook.CreateCellStyle();
timeDataStyle.Alignment = HorizontalAlignment.Center;
timeDataStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Lime.Index;
timeDataStyle.FillPattern = FillPattern.SolidForeground;
HSSFPalette palette = hssfworkbook.GetCustomPalette(); //wb HSSFWorkbook对象
palette.SetColorAtIndex((short)61, (byte)(32), (byte)(218), (byte)(80));
timeDataStyle.FillForegroundColor = (short)61;
}
return(timeDataStyle);
}
static void Main(string[] args)
{
InitializeWorkbook();
HSSFPalette palette = workbook.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)255, (byte)234, (byte)222);
//HSSFColor myColor = palette.AddColor((byte)253, (byte)0, (byte)0);
ISheet sheet1 = workbook.CreateSheet("Sheet1");
ICellStyle style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Pink.Index;
style1.FillPattern = FillPatternType.SolidForeground;
sheet1.CreateRow(0).CreateCell(0).CellStyle = style1;
WriteToFile();
}
/// <summary>
/// 颜色字符串转short类型
/// </summary>
/// <param name="workbook"></param>
/// <param name="htmlcolor"></param>
/// <returns></returns>
private static short GetXLColor(HSSFWorkbook workbook, string htmlcolor)
{
if (htmlcolor.StartsWith("#"))
{
HSSFPalette XlPalette = workbook.GetCustomPalette();
System.Drawing.Color color = System.Drawing.ColorTranslator.FromHtml(htmlcolor);
HSSFColor hssfcolor = XlPalette.FindColor(color.R, color.G, color.B);
if (hssfcolor == null)
{
XlPalette.SetColorAtIndex(HSSFColor.Lavender.Index, color.R, color.G, color.B);
hssfcolor = XlPalette.GetColor(HSSFColor.Lavender.Index);//XlPalette.AddColor(color.R, color.G, color.B);
}
return(hssfcolor.Indexed);
}
else
{
return(htmlcolor.ToUpper().ConvertToColor());
}
}
public static void BackgroundFormat(ICell cell, IHeadCell headCell, byte[] rgb)
{
if (cell.Sheet.Workbook is HSSFWorkbook)
{
HSSFPalette palette = (cell.Sheet.Workbook as HSSFWorkbook).GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Black.Index, rgb[0], rgb[1], rgb[2]);
var style = headCell.CellStyle as HSSFCellStyle;
style.FillForegroundColor = HSSFColor.Black.Index;
style.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = style;
}
else
{
var style = (headCell.CellStyle as XSSFCellStyle);
style.SetFillForegroundColor(new XSSFColor(rgb));
style.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = style;
}
}
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()
);
book.Close();
}
public ICellStyle HeadStyle()
{
if (headStyle == null)
{
IDataFormat dataformat = hssfworkbook.CreateDataFormat();
headStyle = hssfworkbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
headStyle.SetFont(font); //HEAD 样式
HSSFPalette palette = hssfworkbook.GetCustomPalette(); //wb HSSFWorkbook对象
palette.SetColorAtIndex((short)60, (byte)(6), (byte)(238), (byte)(66));
headStyle.FillForegroundColor = (short)60;
headStyle.FillPattern = FillPattern.SolidForeground;
}
return(headStyle);
}
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();
}
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 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);
}
}
public static ICellStyle Getcellstyle(IWorkbook wb, stylexls str, NPOI.SS.Util.CellRangeAddress region, HSSFSheet sh)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 12;
font12.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font12.FontName = "微软雅黑";
IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Black.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "微软雅黑";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //BorderLeft
if (region != null)
{
for (int i = region.FirstRow; i <= region.LastRow; i++)
{
IRow row = HSSFCellUtil.GetRow(i, sh);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = cellStyle;
}
}
}
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.RightBorderColor = HSSFColor.OliveGreen.Black.Index;
//背景图形，我没有用到过。感觉很丑
//cellStyle.FillBackgroundColor = HSSFColor.OliveGreen.Black.Index;
//cellStyle.FillForegroundColor = HSSFColor.OliveGreen.Black.Index;
//cellStyle.FillPattern = FillPatternType.NO_FILL;
//cellStyle.FillForegroundColor = HSSFColor.White.Index;
//cellStyle.FillBackgroundColor = HSSFColor.Black.Index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
//cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.头:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palette = ((HSSFWorkbook)wb).GetCustomPalette();
//HSSFColor newColor = palette.AddColor((byte)153, (byte)204, (byte)255);
palette.SetColorAtIndex((short)10, (byte)227, (byte)232, (byte)227);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
cellStyle.SetFont(font12);
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy-mm-dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.钱:
//IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("¥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = FontUnderlineType.None;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.SetFont(font);
break;
case stylexls.居中:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.SetFont(font);
break;
case stylexls.商品导入头:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette pale = ((HSSFWorkbook)wb).GetCustomPalette();
pale.SetColorAtIndex((short)30, (byte)11, (byte)87, (byte)235);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
cellStyle.SetFont(font12);
break;
case stylexls.导入说明:
HSSFPalette pale1 = ((HSSFWorkbook)wb).GetCustomPalette();
pale1.SetColorAtIndex((short)61, (byte)227, (byte)232, (byte)227);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightCornflowerBlue.Index;
IFont font2 = wb.CreateFont();
font2.FontName = "微软雅黑";
font2.FontHeightInPoints = 9;
cellStyle.SetFont(font2);
break;
case stylexls.导入红字:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palebgss = ((HSSFWorkbook)wb).GetCustomPalette();
palebgss.SetColorAtIndex((short)62, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
IFont fontRed = wb.CreateFont();
fontRed.FontName = "微软雅黑";
fontRed.Color = HSSFColor.OliveGreen.Red.Index;
cellStyle.SetFont(fontRed);
break;
case stylexls.导入错误提示:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette paleError = ((HSSFWorkbook)wb).GetCustomPalette();
paleError.SetColorAtIndex((short)10, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
cellStyle.SetFont(font);
break;
case stylexls.导入背景色:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palebgs = ((HSSFWorkbook)wb).GetCustomPalette();
palebgs.SetColorAtIndex((short)62, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
cellStyle.SetFont(font);
break;
}
return(cellStyle);
}
/// <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="pattern"></param>
/// <param name="rgb"></param>
/// <param name="indexed"></param>
/// <returns></returns>
private static short SetCustomColor(this HSSFPalette pattern, string rgb, short indexed)
{
if (string.IsNullOrEmpty(rgb))
{
return(-1);
}
string[] colors = rgb.Split(',');
if (colors.Length != 3)
{
return(-1);
}
byte red = 0;
byte green = 0;
byte blue = 0;
// 处理RGB数据
bool result = DealRGB(colors, ref red, ref green, ref blue);
if (result == false)
{
return(-1);
}
var temp = pattern.FindColor(red, green, blue);
if (temp != null)
{
return(temp.Indexed);
}
if (indexed == -1)
{
indexed = 8;
}
// 此位置下画板 原始rgb颜色
string originalColor = originalRGBs.Where(t => t.Item1 == indexed).Select(t => t.Item2).FirstOrDefault();
// 此位置下画板 rgb颜色
string originalColor1 = string.Join(",", pattern.GetColor(indexed).RGB);
// 如果两种颜色不一致，说明此位置已经设置了其他颜色，换个位置去设置
if (originalColor != originalColor1)
{
indexed++;
// 循环判断此位置颜色是否是原始颜色，如果是则设置，否则找其他位置
// 如果此位置已经是最后位置了，则使用开始位置设置
while (originalColor != originalColor1 || indexed < 64)
{
originalColor = originalRGBs.Where(t => t.Item1 == indexed).Select(t => t.Item2).FirstOrDefault();
originalColor1 = string.Join(",", pattern.GetColor(indexed).RGB);
if (originalColor == originalColor1)
{
break;
}
indexed++;
}
if (indexed > 63)
{
indexed = 8;
}
}
pattern.SetColorAtIndex(indexed, red, green, blue);
return(indexed);
}
/// <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();
}
/// <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);
}
}
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 /*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 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
{
}
}
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 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);
}
}
