封装对NPOIExcel的操作,方便的设置导出Excel的样式
下载:
http://pan.baidu.com/s/1boTpT5l
使用方法:
导入:
使用 ReadToDataTable方法
导出:
NPOIExcel.ExcelManager manger1 = new ExcelManager(this.textBox2.Text, ExcelManager.DealType.exportExcel); manger1.SetDataSource(dt, 1); //设置标题行 manger1.SetRangeStyle("A1", "D1") .SetCellMerge() .SetFontColor(NPOIStyle.NPOIColor.Blue) .SetBackgroundColor(NPOIStyle.NPOIColor.Yellow) .SetFontBold() .SetFontItalic() .SetFontSize(18) .SetCellText("2016 这里是标题") .SetEnd(); // 设置数据 manger1.SetRangeStyle("a2", "d5") .SetFontName("微软雅黑") .SetFontSize(12) .SetHorizontalAlignment(NPOIStyle.Alignment.Left) .SetBorderAll() .SetEnd(); //设置尾行 manger1.SetRangeStyle("e8", "f9") .SetCellMerge() .SetFontColor(NPOIStyle.NPOIColor.Red) .SetFontItalic() .SetFontSize(14) .SetCellText("2016 签名") .SetBorderBottom() .SetBorderRight() .SetEnd(); //导出文件 manger1.ExportExcelFile();
源码:
using System; using System.Collections.Generic; using System.Text; using System.Data; using NPOI.SS.UserModel; using System.IO; using NPOI.XSSF.UserModel; using NPOI.SS.Util; using NPOI.HSSF.Util; namespace NPOIExcel { public class Point { public int X { get; set; } public int Y { get; set; } } public class Range { public int FIRSTROW { get; set; } public int LASTROW { get; set; } public int FIRSTCOL { get; set; } public int LASTCOL { get; set; } } public class ExcelManager { #region 属性 private string inPutPath { get; set; } private string outPutPath { get; set; } private IWorkbook workbook { get; set; } private ISheet sheet { get; set; } #endregion #region 初始化 //public ExcelManager() { } /// <summary> /// 创建一个用于导入,导出 数据的管理类 /// </summary> /// <param name="Path">文件路径</param> /// <param name="type">设置路径为导入或导出</param> public ExcelManager(string Path, DealType type) { //设置导入文件的路径,workBook if (type == DealType.importExcel) { inPutPath = Path; using (FileStream file = new FileStream(inPutPath, FileMode.Open, FileAccess.Read)) { workbook = WorkbookFactory.Create(file); } } if (type == DealType.exportExcel) { outPutPath = Path; workbook = new XSSFWorkbook(); sheet = workbook.CreateSheet("sheet1"); } } /// <summary> /// 对Excel的操作类型 /// </summary> public enum DealType { importExcel, exportExcel } #endregion #region "基础样式设置---2016-05-17 fengylc add " /// <summary> /// 设置范围内的样式 /// </summary> /// <param name="startPoint">开始的单元格:例-A1</param> /// <param name="endPoint">结束的单元格:例-B1</param> /// <returns>样式</returns> public NPOIStyle SetRangeStyle(string startPoint, string endPoint) { return new NPOIStyle(workbook, sheet, startPoint, endPoint); } #endregion #region 导入 /// <summary> /// 读取Excel到DataTable,默认第一行为列名 /// </summary> /// <param name="headRowCount">表头的行数,从表头下一行开始是数据</param> /// <returns>DataTable</returns> public DataTable ReadExcelToDataTable(int headRowCount = 1) { using (FileStream file = new FileStream(inPutPath, FileMode.Open, FileAccess.Read)) { workbook = WorkbookFactory.Create(file); } sheet = workbook.GetSheetAt(0); DataTable dtl = new DataTable(sheet.SheetName.Trim()); IRow headRow = sheet.GetRow(0); int columnCount = headRow.Cells.Count; for (int index = 0; index <= columnCount - 1; index++) { if (headRow.Cells[index].Equals(null)) dtl.Columns.Add(string.Empty, typeof (string)); else { if(CellType.Numeric==headRow.Cells[index].CellType) dtl.Columns.Add(headRow.Cells[index].NumericCellValue.ToString().Trim(), typeof(string)); else dtl.Columns.Add(headRow.Cells[index].StringCellValue.Trim(), typeof(string)); } } for (int x = headRowCount; x <= sheet.LastRowNum; x++) { IRow contentRow = sheet.GetRow(x); if (contentRow.Equals(null)) continue; else { DataRow dr = dtl.NewRow(); bool isEmpty = true; for (int y = 0; y <= columnCount - 1; y++) { if (contentRow.GetCell(y).Equals(null)) { dr[y] = string.Empty; } else { ICell contentCell = contentRow.GetCell(y); switch (contentCell.CellType) { case CellType.Blank: dr[y] = string.Empty; break; case CellType.Boolean: break; case CellType.Error: break; case CellType.Formula: dr[y] = contentCell.StringCellValue.Trim(); break; case CellType.Numeric: { if (DateUtil.IsCellDateFormatted(contentCell)) dr[y] = contentCell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"); else dr[y] = contentCell.NumericCellValue.ToString().Trim(); } break; case CellType.String: dr[y] = contentCell.StringCellValue.Trim(); break; case CellType.Unknown: break; } isEmpty = string.IsNullOrEmpty(dr[y].ToString().Trim()) && isEmpty; } } //非全空添加行 if (!isEmpty) dtl.Rows.Add(dr); } } return dtl; } #endregion #region 导出 /// <summary> /// 设置数据源 /// </summary> /// <param name="dt">数据</param> /// <param name="headEmptyLineCount">最上方留出的空行</param> public void SetDataSource(DataTable dt, int headEmptyLineCount = 0) { try { int TotalNum = dt.Rows.Count + headEmptyLineCount + 1;// (加上 空行 ) //添加数据 addData(TotalNum, headEmptyLineCount + 1, sheet, dt); //标题设置 for (int i = 0; i < dt.Columns.Count; i++) { sheet.GetRow(headEmptyLineCount).GetCell(i).SetCellValue(dt.Columns[i].ColumnName); } } catch (Exception ex) { throw ex; } } /// <summary> /// 导出文件 /// </summary> /// <param name="fileName">文件名,不指定为随机生成</param> public void ExportExcelFile(string fileName = "") { Random ran = new Random(); if (string.IsNullOrEmpty(fileName)) { fileName = "\\" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ran.Next(1000, 9999).ToString() + ".xlsx"; } else { fileName = "\\" + fileName + ".xlsx"; } string CreatePath = outPutPath + fileName; FileStream NewFile = new FileStream(CreatePath, FileMode.Create); workbook.Write(NewFile); } private static void addData(int TotalNum, int headCount, ISheet sheet, DataTable dt) { //添加数据 for (int i = 0; i < TotalNum; i++) { IRow row = sheet.CreateRow(i); for (int j = 0; j < dt.Columns.Count; j++) { row.CreateCell(j); if (i >= headCount) { try { double cellData = double.Parse(dt.Rows[i - headCount][j].ToString()); row.CreateCell(j).SetCellValue(cellData); } catch { string cellData = dt.Rows[i - headCount][j].ToString(); if (cellData.Trim().Length > 1 && cellData.Substring(0, 1) == "'") { cellData = cellData.Substring(1, cellData.Length - 1); } row.CreateCell(j).SetCellValue(cellData); } } } } } #endregion } /// <summary> /// 设置样式 /// </summary> public class NPOIStyle { public Range range { get; set; } public ISheet sheet { get; set; } public ICellStyle cellStyle { get; set; } public IFont cellFontStyle { get; set; } public NPOIStyle(IWorkbook workbook, ISheet sheet, string startPoint, string endPoint) { cellStyle = workbook.CreateCellStyle(); cellFontStyle = workbook.CreateFont(); this.sheet = sheet; range = GetRange(startPoint, endPoint); } public enum Alignment { Center, Fill, Left, Right, Bottom, Top } public enum NPOIColor : short { Black = 8, Brown = 60, OliveGreen = 59, DarkGreen = 58, DarkTeal = 56, DarkBlue = 18, Indigo = 62, Grey80Percent = 63, DarkRed = 16, Orange = 53, DarkYellow = 19, Green = 17, Teal = 21, Blue = 12, BlueGrey = 54, Grey50Percent = 23, Red = 10, LightOrange = 52, Lime = 50, SeaGreen = 57, Aqua = 49, LightBlue = 48, Violet = 20, Grey40Percent = 55, Pink = 14, Gold = 51, Yellow = 13, BrightGreen = 11, Turquoise = 15, SkyBlue = 40, Plum = 61, Grey25Percent = 22, Rose = 45, Tan = 47, LightYellow = 43, LightGreen = 42, LightTurquoise = 41, PaleBlue = 44, Lavender = 46, White = 9, CornflowerBlue = 24, LemonChiffon = 26, Maroon = 25, Orchid = 28, Coral = 29, RoyalBlue = 30, LightCornflowerBlue = 31, Automatic = 64 } /// <summary> /// 把Excel列名字母转换为 数字坐标 /// </summary> /// <param name="character">Excel列名</param> /// <returns>转换后的数字</returns> public static int Asc(string character) { character = character.ToUpper(); char[] arry = character.ToCharArray(); int total = 0; for (int i = 0; i < character.Length; i++) { total += (Convert.ToInt32(arry[i]) - 65) + 26 * i; } return total; } /// <summary> /// 把‘A1’类型转换成Point类型 /// </summary> /// <param name="point">字符点</param> /// <returns>Point</returns> public static Point GetPoint(string point) { Point newPoint = new Point(); char[] arry = point.ToCharArray(); int index = 0; for (int i = 0; i < point.Length; i++) { if (Char.IsNumber(arry[i])) { index = i; break; } } newPoint.Y = Asc(point.Substring(0, index)); newPoint.X = int.Parse(point.Substring(index, point.Length - index)) - 1; return newPoint; } /// <summary> /// 根据两个点确定一个范围 /// </summary> /// <param name="startPosition">Excel上的开始点</param> /// <param name="endPosition">Excel上的结束点</param> /// <returns>范围</returns> public Range GetRange(string startPosition, string endPosition) { Range range = new Range(); Point start = GetPoint(startPosition); Point end = GetPoint(endPosition); range.FIRSTROW = start.X; range.LASTROW = end.X; range.FIRSTCOL = start.Y; range.LASTCOL = end.Y; for (int row = range.FIRSTROW; row <= range.LASTROW; row++) { if (sheet.GetRow(row) == null) sheet.CreateRow(row); for (int col = range.FIRSTCOL; col <= range.LASTCOL; col++) { if (sheet.GetRow(row).GetCell(col) == null) sheet.GetRow(row).CreateCell(col); } } return range; } /// <summary> /// 设置样式的必要设置:设置样式的范围 /// </summary> /// <param name="startPoint">范围起始点</param> /// <param name="endPoint">范围结束点</param> /// <returns>设置样式的范围</returns> public NPOIStyle SetRange(string startPoint, string endPoint) { this.range = GetRange(startPoint, endPoint); return this; } /// <summary> /// 合并单元格 /// </summary> /// <param name="startPosition">合并开始的单元格</param> /// <param name="endPosition">合并结束的单元格</param> /// <returns></returns> public NPOIStyle SetCellMerge() { //把excel单元格带字母坐标 转换为 数字坐标 CellRangeAddress cellRangeAddress = new CellRangeAddress(range.FIRSTROW, range.LASTROW, range.FIRSTCOL, range.LASTCOL); this.sheet.AddMergedRegion(cellRangeAddress); return this; } /// <summary> /// 设置字体大小 /// </summary> /// <param name="size">字体大小:9,10,11...</param> /// <returns>Manager</returns> public NPOIStyle SetFontSize(short size) { cellFontStyle.FontHeightInPoints = size; return this; } /// <summary> /// 设置sheet页面上列的宽 /// </summary> /// <param name="size"></param> /// <returns></returns> public NPOIStyle SetSheetColumnWidthSize(short size) { sheet.DefaultColumnWidth = size; return this; } /// <summary> /// 设置字体样式 /// </summary> /// <param name="FontName">字体:宋体,黑体...</param> /// <returns>Manager</returns> public NPOIStyle SetFontName(string FontName) { cellFontStyle.FontName = FontName; return this; } /// <summary> /// 设置字体加粗 /// </summary> /// <returns>Manager</returns> public NPOIStyle SetFontBold() { cellFontStyle.Boldweight = (short)FontBoldWeight.Bold; return this; } /// <summary> /// 设置字体下划线 /// </summary> /// <returns>Manager</returns> public NPOIStyle SetFontUnderline() { cellFontStyle.Underline = FontUnderlineType.Single; return this; } /// <summary> /// 设置字体下倾斜 /// </summary> /// <returns>Manager</returns> public NPOIStyle SetFontItalic() { cellFontStyle.IsItalic = true; return this; } /// <summary> /// 设置字体水平对齐 /// </summary> /// <returns>Manager</returns> public NPOIStyle SetHorizontalAlignment(Alignment alignment) { switch (alignment) { case Alignment.Center: cellStyle.Alignment = HorizontalAlignment.Center; break; case Alignment.Fill: cellStyle.Alignment = HorizontalAlignment.Fill; break; case Alignment.Left: cellStyle.Alignment = HorizontalAlignment.Left; break; case Alignment.Right: cellStyle.Alignment = HorizontalAlignment.Right; break; default: break; } return this; } /// <summary> /// 设置字体垂直对齐 /// </summary> /// <returns>Manager</returns> public NPOIStyle SetVerticalAlignment(Alignment alignment) { switch (alignment) { case Alignment.Center: cellStyle.VerticalAlignment = VerticalAlignment.Center; break; case Alignment.Fill: cellStyle.VerticalAlignment = VerticalAlignment.Justify; break; case Alignment.Top: cellStyle.VerticalAlignment = VerticalAlignment.Top; break; case Alignment.Bottom: cellStyle.VerticalAlignment = VerticalAlignment.Bottom; break; default: break; } return this; } /// <summary> /// 设置字体颜色 /// </summary> /// <param name="FontName">字体:宋体,黑体...</param> /// <returns>Manager</returns> public NPOIStyle SetFontColor(NPOIColor color) { cellFontStyle.Color = (short)color; return this; } /// <summary> /// 设置单元格背景色 /// </summary> /// <param name="color"></param> /// <returns></returns> public NPOIStyle SetBackgroundColor(NPOIColor color) { cellStyle.FillPattern = FillPattern.SolidForeground; cellStyle.FillForegroundColor = (short)color; return this; } /// <summary> /// 设置单元格的文字 /// </summary> /// <param name="position"></param> /// <param name="text"></param> public NPOIStyle SetCellText(string text) { sheet.GetRow(range.FIRSTROW).GetCell(range.FIRSTCOL).SetCellValue(text); return this; } /// <summary> /// 设置边框(Range内每个单元格的四边) /// </summary> /// <returns></returns> public NPOIStyle SetBorderAll() { cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; return this; } public NPOIStyle SetBorderBottom() { cellStyle.BorderBottom = BorderStyle.Thin; return this; } public NPOIStyle SetBorderLeft() { cellStyle.BorderLeft = BorderStyle.Thin; return this; } public NPOIStyle SetBorderRight() { cellStyle.BorderRight = BorderStyle.Thin; return this; } public NPOIStyle SetBorderTop() { cellStyle.BorderTop = BorderStyle.Thin; return this; } /// <summary> /// 样式设置结束,调用此方法生效 /// </summary> public void SetEnd() { for (int i = range.FIRSTROW; i <= range.LASTROW; i++) { for (int j = range.FIRSTCOL; j <= range.LASTCOL; j++) { cellStyle.SetFont(cellFontStyle); sheet.GetRow(i).GetCell(j).CellStyle = cellStyle; } } } } }