公共方法
using Microsoft.EntityFrameworkCore.Storage;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace NpoiDemo.Common
{
using S = String;
public class NpoiHelper
{
}
}
CreateBasicBook
public static void CreateBasicBook()
{
var book = new HSSFWorkbook();
var sheet = book.CreateSheet("book");
var row = sheet.CreateRow(0);
var cell = row.CreateCell(0);
var cellStyle = book.CreateCellStyle();
var font = book.CreateFont();
}
SetBorderStyle
public static void SetBorderStyle(ICellStyle cellStyle)
{
cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = BorderStyle.Thin;
}
public static void SetBorderStyle(ICellStyle cellStyle, BorderStyle border)
{
cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = border;
}
SetBackground
public static void SetBackground(ICellStyle cellStyle, short foreColor, short bgColor)
{
cellStyle.FillBackgroundColor = foreColor;
cellStyle.FillForegroundColor = bgColor;
cellStyle.FillPattern = FillPattern.SolidForeground;
}
GetColourByRGB
public static short GetColourByRGB(HSSFWorkbook workbook, byte r, byte g, byte b)
{
var palette = workbook.GetCustomPalette();
var hssfColor = palette.FindColor(r, g, b);
if (hssfColor == null)
{
palette.SetColorAtIndex(HSSFColor.Lavender.Index, r, g, b);
hssfColor = palette.GetColor(HSSFColor.Lavender.Index);
}
if (hssfColor != null)
{
return hssfColor.Indexed;
}
else
{
return short.MinValue;
}
}
SetFont
public static void SetFont(HSSFWorkbook workbook, ICellStyle cellStyle, S fontName = "等线",
double size = 10, bool isBold = false, bool isItalic = false,
FontUnderlineType underlineType = FontUnderlineType.None)
{
var font = workbook.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = size;
font.IsBold = isBold;
font.IsItalic = isItalic;
font.Underline = underlineType;
cellStyle.SetFont(font);
}
SetAutoSizeColumn
public static void SetAutoSizeColumn(ISheet sheet, int totalCols)
{
Enumerable.Range(0, totalCols).ToList().ForEach(x =>
{
sheet.AutoSizeColumn(x);
});
}
UnionCells
public static void UnionCells(ISheet sheet, int firstRowIndex, int lastRowIndex, int firstColIndex, int lastColIndex)
{
sheet.AddMergedRegion(new CellRangeAddress(firstRowIndex, lastRowIndex, firstColIndex, lastColIndex));
}
SetAutoFilter
public static void SetAutoFilter(ISheet sheet, S reference)
{
sheet.SetAutoFilter(CellRangeAddress.ValueOf(reference));
}
SetRowHeight
public static void SetRowHeight(IRow row, float height)
{
row.HeightInPoints = height;
}
SetAlignment
public static void SetAlignment(ICellStyle cell, HorizontalAlignment horizontal, VerticalAlignment vertical)
{
cell.Alignment = horizontal;
cell.VerticalAlignment = vertical;
}
SetFormula
public static void SetFormula(ICell cell, S formula)
{
cell.SetCellFormula(formula);
}
SetColumnWidth
public static void SetColumnWidth(ISheet sheet, int colIndex, int colwidthChar)
{
sheet.SetColumnWidth(colIndex, colwidthChar * 256);
}
完整代码
using Microsoft.EntityFrameworkCore.Storage;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace NpoiDemo.Common
{
using S = String;
public class NpoiHelper
{
public static void CreateBasicBook()
{
var book = new HSSFWorkbook();
var sheet = book.CreateSheet("book");
var row = sheet.CreateRow(0);
var cell = row.CreateCell(0);
var cellStyle = book.CreateCellStyle();
var font = book.CreateFont();
}
public static void SetBorderStyle(ICellStyle cellStyle, BorderStyle border)
{
cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = border;
}
public static void SetBorderStyle(ICellStyle cellStyle)
{
cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = BorderStyle.Thin;
}
public static void SetBackground(ICellStyle cellStyle, short foreColor, short bgColor)
{
cellStyle.FillBackgroundColor = foreColor;
cellStyle.FillForegroundColor = bgColor;
cellStyle.FillPattern = FillPattern.SolidForeground;
}
public static short GetColourByRGB(HSSFWorkbook workbook, byte r, byte g, byte b)
{
var palette = workbook.GetCustomPalette();
var hssfColor = palette.FindColor(r, g, b);
if (hssfColor == null)
{
palette.SetColorAtIndex(HSSFColor.Lavender.Index, r, g, b);
hssfColor = palette.GetColor(HSSFColor.Lavender.Index);
}
if (hssfColor != null)
{
return hssfColor.Indexed;
}
else
{
return short.MinValue;
}
}
public static void SetFont(HSSFWorkbook workbook, ICellStyle cellStyle, S fontName = "等线",
double size = 10, bool isBold = false, bool isItalic = false,
FontUnderlineType underlineType = FontUnderlineType.None)
{
var font = workbook.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = size;
font.IsBold = isBold;
font.IsItalic = isItalic;
font.Underline = underlineType;
cellStyle.SetFont(font);
}
public static void SetAutoSizeColumn(ISheet sheet, int totalCols)
{
Enumerable.Range(0, totalCols).ToList().ForEach(x =>
{
sheet.AutoSizeColumn(x);
});
}
public static void UnionCells(ISheet sheet, int firstRowIndex, int lastRowIndex, int firstColIndex, int lastColIndex)
{
sheet.AddMergedRegion(new CellRangeAddress(firstRowIndex, lastRowIndex, firstColIndex, lastColIndex));
}
public static void SetAutoFilter(ISheet sheet, S reference)
{
sheet.SetAutoFilter(CellRangeAddress.ValueOf(reference));
}
public static void SetRowHeight(IRow row, float height)
{
row.HeightInPoints = height;
}
public static void SetAlignment(ICellStyle cell, HorizontalAlignment horizontal, VerticalAlignment vertical)
{
cell.Alignment = horizontal;
cell.VerticalAlignment = vertical;
}
public static void SetFormula(ICell cell, S formula)
{
cell.SetCellFormula(formula);
}
public static void SetColumnWidth(ISheet sheet, int colIndex, int colwidthChar)
{
sheet.SetColumnWidth(colIndex, colwidthChar * 256);
}
}
}