/// <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);
}
/// <summary>
/// 设置单元格样式
/// </summary>
/// <param name="rownum"></param>
/// <param name="isFill">是否填充背景色</param>
/// <returns></returns>
//private HSSFCellStyle SetStyle(int rownum,bool isFill)
//{
// if (isFill)
// {
// if (rownum%2 == 0)
// {
// cellstyle.FillForegroundColor = HSSFColor.LIGHT_ORANGE.index;
// }
// else
// cellstyle.FillForegroundColor = HSSFColor.SKY_BLUE.index;
// cellstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
// //cellstyle.FillBackgroundColor = HSSFColor.RED.index;
// }
// return cellstyle;
//}
/// <summary>
/// 获取颜色
/// </summary>
/// <param name="sysColor"></param>
/// <returns></returns>
private short GetColor(System.Drawing.Color sysColor)
{
short s = 0;
HSSFPalette xlPalette = WorkBook.GetCustomPalette();
HSSFColor xlColor = xlPalette.FindColor(sysColor.R, sysColor.G, sysColor.B);
if (xlColor == null)
{
if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE <= 255)
{
if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64)
{
NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64;
NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1;
xlColor = xlPalette.AddColor(sysColor.R, sysColor.G, sysColor.B);
}
else
{
xlColor = xlPalette.FindSimilarColor(sysColor.R, sysColor.G, sysColor.B);
}
s = xlColor.GetIndex();
}
}
else
{
s = xlColor.GetIndex();
}
return(s);
}
/// <summary>
/// 获取自定义颜色位置
/// </summary>
/// <param name="workBook"></param>
/// <param name="rgb"></param>
/// <returns></returns>
private static short GetCustomColor(this HSSFWorkbook workBook, string rgb)
{
SetOriginalRGB();
short indexed = defaultColorIndexed;
if (string.IsNullOrEmpty(rgb))
{
return(indexed);
}
string[] colors = rgb.Split(',');
if (colors.Length != 3)
{
return(indexed);
}
byte red = 0;
byte green = 0;
byte blue = 0;
bool result = DealRGB(colors, ref red, ref green, ref blue);
if (result == false)
{
return(indexed);
}
HSSFPalette pattern = workBook.GetCustomPalette();
NPOI.HSSF.Util.HSSFColor hssfColor = pattern.FindColor(red, green, blue);
if (hssfColor == null)
{
return(pattern.SetCustomColor(rgb, -1));
}
indexed = hssfColor.Indexed;
return(indexed);
}
/// <summary>
/// 获取RGB对应NPOI颜色值
/// </summary>
/// <param name="workbook">当前wb</param>
/// <param name="R"></param>
/// <param name="G"></param>
/// <param name="B"></param>
/// <returns></returns>
public static short GetXLColour(this HSSFWorkbook workbook, int R, int G, int B)
{
short s = 0;
HSSFPalette XlPalette = workbook.GetCustomPalette();
HSSFColor XlColour = XlPalette.FindColor((byte)R, (byte)G, (byte)B);
if (XlColour == null)
{
if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255)
{
if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64)
{
NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64;
NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1;
XlColour = XlPalette.AddColor((byte)R, (byte)G, (byte)B);
}
else
{
XlColour = XlPalette.FindSimilarColor((byte)R, (byte)G, (byte)B);
}
s = XlColour.GetIndex();
}
}
else
{
s = XlColour.GetIndex();
}
return(s);
}
private short GetXLColour(HSSFWorkbook workbook, System.Drawing.Color SystemColour)
{
short s = 0;
HSSFPalette XlPalette = workbook.GetCustomPalette();
HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);
if (XlColour == null)
{
if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255)
{
if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64)
{
NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64; NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1;
XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B);
}
else
{
XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B);
}
s = XlColour.GetIndex();
}
}
else
{
s = XlColour.GetIndex();
}
return(s);
}
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);
}
private short GetXLColour(System.Drawing.Color SystemColour)
{
HSSFPalette XlPalette = document.GetCustomPalette();
NPOI.HSSF.Util.HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);
XlColour = XlColour ?? XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B);
return(XlColour.Indexed);
}
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 static short ConvertToGroundColor(this string v)
{
//PaletteRecord palette = new PaletteRecord();
int r = Convert.ToInt32("0x" + v.Substring(1, 2), 16);
int g = Convert.ToInt32("0x" + v.Substring(3, 2), 16);
int b = Convert.ToInt32("0x" + v.Substring(5, 2), 16);
HSSFPalette mypalette = new HSSFPalette(new PaletteRecord());
HSSFColor hssFColor = mypalette.FindColor((Byte)r, (Byte)g, (Byte)(b));
return(hssFColor.Indexed);
}
private static short GetXlColour(HSSFWorkbook workbook, Color systemColor)
{
short s = 0;
HSSFPalette xlPalette = workbook.GetCustomPalette();
NPOI.HSSF.Util.HSSFColor xlColour = xlPalette.FindColor(systemColor.R, systemColor.G, systemColor.B);
if (xlColour == null)
{
xlColour = xlPalette.FindSimilarColor(systemColor.R, systemColor.G, systemColor.B);
s = xlColour.Indexed;
}
else
{
s = xlColour.Indexed;
}
return(s);
}
/// <summary>
/// 获取Xls颜色
/// </summary>
/// <param name="workbook">工作簿</param>
/// <param name="color">系统颜色</param>
/// <returns></returns>
public static short GetXlsColour(this HSSFWorkbook workbook, Color color)
{
short s = 0;
HSSFPalette palette = workbook.GetCustomPalette(); //调色板实例
HSSFColor hssfColor = palette.FindColor(color.R, color.G, color.B);
if (hssfColor == null)
{
hssfColor = palette.FindSimilarColor(color.R, color.G, color.B);
s = hssfColor.Indexed;
}
else
{
s = hssfColor.Indexed;
}
return(s);
}
/// <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());
}
}
private static short GetXLColour(HSSFWorkbook workbook, Color SystemColour)
{
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();
}
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;
}
/// <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 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 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 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);
}
}
/// <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>
/// 导出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 static short ConvertToColor(this string v)
{
int r = Convert.ToInt32("0x" + v.Substring(1, 2), 16);
int g = Convert.ToInt32("0x" + v.Substring(3, 2), 16);
int b = Convert.ToInt32("0x" + v.Substring(5, 2), 16);
HSSFPalette mypalette = new HSSFPalette(new PaletteRecord());
HSSFColor hssFColor = mypalette.FindColor((Byte)r, (Byte)g, (Byte)(b));
return(hssFColor.Indexed);
/*switch (v)
* {
* case "AQUA":
* return 49;
*
* case "AUTOMATIC":
* return 64;
*
* case "BLACK":
* return 8;
*
* case "BLUE":
* return 39;
*
* case "BLUE_GREY":
* case "BLUEGREY":
* return 54;
*
* case "BRIGHT_GREEN":
* case "BRIGHTGREEN":
* return 35;
*
* case "BROWN":
* return 60;
*
* case "CORAL":
* return 29;
*
* case "CORNFLOWER_BLUE":
* case "CORNFLOWERBLUE":
* return 24;
*
* case "DARK_BLUE":
* case "DARKBLUE":
* return 32;
*
* case "DARK_GREEN":
* case "DARKGREEN":
* return 58;
* case "DARK_RED":
* case "DARKRED":
* return 37;
*
* case "DARK_TEAL":
* case "DARKTEAL":
* return 56;
*
* case "DARK_YELLOW":
* case "DARKYELLOW":
* return 19;
*
* case "GOLD":
* return 51;
*
* case "GREEN":
* return 17;
* case "GREY_25_PERCENT":
* case "GREY25PERCENT":
* return 22;
*
* case "GREY_40_PERCENT":
* case "GREY40PERCENT":
* return 55;
*
* case "GREY_50_PERCENT":
* case "GREY50PERCENT":
* return 23;
*
* case "GREY_80_PERCENT":
* case "GREY80PERCENT":
* return 63;
*
* case "INDIGO":
* return 62;
*
* case "LAVENDER":
* return 46;
*
* case "LEMON_CHIFFON":
* case "LEMONCHIFFON":
* return 26;
*
* case "LIGHT_BLUE":
* case "LIGHTBLUE":
* return 48;
*
* case "LIGHT_CORNFLOWERBLUE":
* case "LIGHTCORNFLOWERBLUE":
* return 31;
*
* case "LIGHT_GREEN":
* case "LIGHTGREEN":
* return 42;
*
* case "LIGHT_ORANGE":
* case "LIGHTORANGE":
* return 52;
*
* case "LIGHT_TURQUOISE":
* case "LIGHTTURQUOISE":
* return 27;
*
* case "LIGHT_YELLOW":
* case "LIGHTYELLOW":
* return 43;
*
* case "LIME":
* return 50;
*
* case "MAROON":
* return 25;
*
* case "OLIVE_GREEN":
* case "OLIVEGREEN":
* return 59;
*
* case "ORANGE":
* return 53;
*
* case "ORCHID":
* return 28;
*
* case "PALE_BLUE":
* case "PALEBLUE":
* return 44;
*
* case "PINK":
* return 33;
*
* case "PLUM":
* return 25;
*
* case "RED":
* return 10;
*
* case "ROSE":
* return 45;
*
* case "ROYAL_BLUE":
* case "ROYALBLUE":
* return 30;
*
* case "SEA_GREEN":
* case "SEAGREEN":
* return 57;
*
* case "SKY_BLUE":
* case "SKYBLUE":
* return 40;
*
* case "TAN":
* return 47;
*
* case "TEAL":
* return 38;
*
* case "TURQUOISE":
* return 35;
*
* case "VIOLET":
* return 36;
*
* case "WHITE":
* return 9;
*
* case "YELLOW":
* return 34;
*
* default:
* return 32767;
* };*/
}
