NPOI 整理

公共方法

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 { //code here } }

CreateBasicBook

/// <summary> /// 创建book的基础方法 /// </summary> public static void CreateBasicBook() { //2003格式 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

/// <summary> /// 设置单元格边框线,默认Thin /// </summary> /// <param name="cellStyle"></param> public static void SetBorderStyle(ICellStyle cellStyle) { cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = BorderStyle.Thin; } /// <summary> /// 设置单元格边框线,需要传入borderStyle /// </summary> /// <param name="cellStyle"></param> public static void SetBorderStyle(ICellStyle cellStyle, BorderStyle border) { cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = border; }

SetBackground

/// <summary> /// 设置前景色和背景色 /// </summary> /// <param name="cellStyle"></param> /// <param name="foreColor">HSSFColor.Black.Index</param> /// <param name="bgColor"></param> public static void SetBackground(ICellStyle cellStyle, short foreColor, short bgColor) { cellStyle.FillBackgroundColor = foreColor; cellStyle.FillForegroundColor = bgColor; cellStyle.FillPattern = FillPattern.SolidForeground; }

GetColourByRGB

/// <summary> /// 通过RGB获取颜色索引 /// </summary> /// <param name="workbook"></param> /// <param name="r"></param> /// <param name="g"></param> /// <param name="b"></param> /// <returns></returns> 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

/// <summary> /// 设置字体 /// </summary> /// <param name="workbook">book对象</param> /// <param name="cellStyle">单元格对象</param> /// <param name="fontName">字体名称</param> /// <param name="size">字体大小</param> /// <param name="isBold">加粗</param> /// <param name="isItalic">加谢</param> /// <param name="underlineType">下划线</param> 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

/// <summary> /// 自适应列宽 /// </summary> /// <param name="sheet">表格对象</param> /// <param name="totalCols">总共的列数</param> public static void SetAutoSizeColumn(ISheet sheet, int totalCols) { Enumerable.Range(0, totalCols).ToList().ForEach(x => { sheet.AutoSizeColumn(x); }); }

UnionCells

/// <summary> /// 指定区域合并单元格,都是闭区间，索引0开始 /// </summary> /// <param name="sheet">表格对象</param> /// <param name="firstRowIndex">开始行</param> /// <param name="lastRowIndex">结束行</param> /// <param name="firstColIndex">开始列</param> /// <param name="lastColIndex">结束列</param> public static void UnionCells(ISheet sheet, int firstRowIndex, int lastRowIndex, int firstColIndex, int lastColIndex) { sheet.AddMergedRegion(new CellRangeAddress(firstRowIndex, lastRowIndex, firstColIndex, lastColIndex)); }

SetAutoFilter

/// <summary> /// 数据自动筛选 /// </summary> /// <param name="sheet">表格对象</param> /// <param name="reference">单元格的引用字符串表达方式</param> public static void SetAutoFilter(ISheet sheet, S reference) { sheet.SetAutoFilter(CellRangeAddress.ValueOf(reference)); }

SetRowHeight

/// <summary> /// 设置行高 /// </summary> /// <param name="row">Irow对象</param> /// <param name="height">高度</param> public static void SetRowHeight(IRow row, float height) { row.HeightInPoints = height; }

SetAlignment

/// <summary> /// 设置单元格的水平和垂直对齐方式 /// </summary> /// <param name="cell"></param> /// <param name="horizontal"></param> /// <param name="vertical"></param> public static void SetAlignment(ICellStyle cell, HorizontalAlignment horizontal, VerticalAlignment vertical) { cell.Alignment = horizontal; cell.VerticalAlignment = vertical; }

SetFormula

/// <summary> /// 公式设置 /// </summary> /// <param name="cell">单元格</param> /// <param name="formula">excel的计算公式就行</param> public static void SetFormula(ICell cell, S formula) { //$"SUM(A1,A3)" cell.SetCellFormula(formula); }

SetColumnWidth

/// <summary> /// 设置列宽，列宽按照字符进行计算的所以有个固定字符256 /// </summary> /// <param name="sheet"></param> /// <param name="colIndex"></param> /// <param name="colwidthChar"></param> 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 { /// <summary> /// 创建book的基础方法 /// </summary> public static void CreateBasicBook() { //2003格式 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(); } /// <summary> /// 设置单元格边框线,需要传入borderStyle /// </summary> /// <param name="cellStyle"></param> public static void SetBorderStyle(ICellStyle cellStyle, BorderStyle border) { cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = border; } /// <summary> /// 设置单元格边框线,默认Thin /// </summary> /// <param name="cellStyle"></param> public static void SetBorderStyle(ICellStyle cellStyle) { cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = BorderStyle.Thin; } /// <summary> /// 设置前景色和背景色 /// </summary> /// <param name="cellStyle"></param> /// <param name="foreColor">HSSFColor.Black.Index</param> /// <param name="bgColor"></param> public static void SetBackground(ICellStyle cellStyle, short foreColor, short bgColor) { cellStyle.FillBackgroundColor = foreColor; cellStyle.FillForegroundColor = bgColor; cellStyle.FillPattern = FillPattern.SolidForeground; } /// <summary> /// 通过RGB获取颜色索引 /// </summary> /// <param name="workbook"></param> /// <param name="r"></param> /// <param name="g"></param> /// <param name="b"></param> /// <returns></returns> 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; } } /// <summary> /// 设置字体 /// </summary> /// <param name="workbook">book对象</param> /// <param name="cellStyle">单元格对象</param> /// <param name="fontName">字体名称</param> /// <param name="size">字体大小</param> /// <param name="isBold">加粗</param> /// <param name="isItalic">加谢</param> /// <param name="underlineType">下划线</param> 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); } /// <summary> /// 自适应列宽 /// </summary> /// <param name="sheet">表格对象</param> /// <param name="totalCols">总共的列数</param> public static void SetAutoSizeColumn(ISheet sheet, int totalCols) { Enumerable.Range(0, totalCols).ToList().ForEach(x => { sheet.AutoSizeColumn(x); }); } /// <summary> /// 指定区域合并单元格,都是闭区间，索引0开始 /// </summary> /// <param name="sheet">表格对象</param> /// <param name="firstRowIndex">开始行</param> /// <param name="lastRowIndex">结束行</param> /// <param name="firstColIndex">开始列</param> /// <param name="lastColIndex">结束列</param> public static void UnionCells(ISheet sheet, int firstRowIndex, int lastRowIndex, int firstColIndex, int lastColIndex) { sheet.AddMergedRegion(new CellRangeAddress(firstRowIndex, lastRowIndex, firstColIndex, lastColIndex)); } /// <summary> /// 数据自动筛选 /// </summary> /// <param name="sheet">表格对象</param> /// <param name="reference">单元格的引用字符串表达方式</param> public static void SetAutoFilter(ISheet sheet, S reference) { sheet.SetAutoFilter(CellRangeAddress.ValueOf(reference)); } /// <summary> /// 设置行高 /// </summary> /// <param name="row">Irow对象</param> /// <param name="height">高度</param> public static void SetRowHeight(IRow row, float height) { row.HeightInPoints = height; } /// <summary> /// 设置单元格的水平和垂直对齐方式 /// </summary> /// <param name="cell"></param> /// <param name="horizontal"></param> /// <param name="vertical"></param> public static void SetAlignment(ICellStyle cell, HorizontalAlignment horizontal, VerticalAlignment vertical) { cell.Alignment = horizontal; cell.VerticalAlignment = vertical; } /// <summary> /// 公式设置 /// </summary> /// <param name="cell">单元格</param> /// <param name="formula">excel的计算公式就行</param> public static void SetFormula(ICell cell, S formula) { //$"SUM(A1,A3)" cell.SetCellFormula(formula); } /// <summary> /// 设置列宽，列宽按照字符进行计算的所以有个固定字符256 /// </summary> /// <param name="sheet"></param> /// <param name="colIndex"></param> /// <param name="colwidthChar"></param> public static void SetColumnWidth(ISheet sheet, int colIndex, int colwidthChar) { sheet.SetColumnWidth(colIndex, colwidthChar * 256); } } }