公司开发用到了NPOI,在开发过程中由于需求的变化,也是遇到各种问题(最最讨厌的就是开发好了改需求了!!!),但是也是这样,才更多的了解了一下NPOI。
当然,这篇文章也只是简单的介绍到本人在开发过程中遇到的问题,必然会很片面,写此文章也是为了给那些遇到同样问题的猿友们一个借鉴,不足之处还请大家多多指教。
话不多说进入正题,关于什么是NPOI我就不再详述了,自行问度娘。
先写一下目录吧,这样大家可以清楚本文的大致内容。
1.基本操作(创建工作薄、表、行及单元格)
2.合并单元格
3.NPOI中的单元格样式
4.关于单元格边框问题
5.筛选
6.导出/导入
7.使用
为了更直观,我直接在代码中一一介绍。
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.ss.Util;
using NPOI.HPSF;
public class MyNPOI
{
private HSSFWorkbook _hssfWorkbook;
public NPOI(){}
#region 一、基本操作
//新建一个工作薄
public void Create()
{
_hssfWorkbook = new HSSFWorkbook();
}
//创建表 提示:工作薄中至少要创建一个表
public HSSFSheet CreateSheet(string sheetName)
{
return _hssfWorkbook.CreateSheet(sheetName) as HSSFSheet;
}
//演示用的模板,后面单元格边框问题介绍使用
public void CreateTemplate(HSSFSheet sheet)
{
for(int i = 0; i < 50; i++)
{
//npoi中每行以及每个需要用到的单元格都要去创建,不创建就无法使用,当然,不用的单元格可以不用创建
HSSFRow row = sheet.CreateRow(i) as HSSFRow;
for(int j = 0; j < 6; j++)
{
HSSFCell cell = row.CreateCell(j) as HSSFCell;
}
}
}
#endregion
#region 二、合并单元格
//这里说一下,合并单元格可以在创建单元格之前使用,也就是不需要单元格真正的存在,所以使用合并单元格时只需要创建合并后的第一个单元格即可
//后面我会给使用案例
public void AddMergedRegion(HSSFSheet sheet, int firstRow, int lastRow, int firstCol, int lastCol)
{
sheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, FirstCol, lastCol));
}
#endregion
#region 三、单元格样式
//npoi中单个工作薄最多可以创建4000个单元格样式,当时我也是卡在单元格样式这块卡了半天
//npoi中会默认创建一个单元格样式,也就是我们在新建excel中看到的那种样式
//每当我们创建一个单元格时,单元格样式默认为上述的单元格样式
//为什么要强调这个默认的单元格样式呢?因为我被他坑过(泪奔。。。)
//如果你要设置某个单元格的单元格样式,一定要先创建新的单元格样式,切勿直接修改,因为你会发现影响的可不只是这一个单元格
//当时我傻傻的认为每个单元格都有自己的样式,结果他们的样式都指向系统创建的默认的样式,而不是自己new的
//具体的使用后面呈现
#endregion
#region 四、单元格边框问题
//我们在excel中设置边框非常方便,鼠标拖一下,右击设置单元格样式,设置边框即可,但是在npoi可没这么简单,这个过程我们要用代码写出来
//主观上边框分为外边框和内边框
//但实际上投射到单元格就是16种单元格样式的组合!!
private ICellStyle GetCellBorderStyle(int style, NPOI.SS.UserModel.BorderStyle outStyle, NPOI.SS.UserModel.BorderStyle inStyle)
{
ICellStyle cellStyle = _hssfWorkbook.CreateCellStyle();
cellStyle.BorderTop = inStyle;
cellStyle.BorderTop = inStyle;
cellStyle.BorderTop = inStyle;
cellStyle.BorderTop = inStyle;
switch(style)
{
//九宫格中间的样式
case 0:
break;
//九宫格左上角样式
case 1;
cellStyle.BorderTop = outStyle
cellStyle.BorderLeft = outStyle;
break;
//九宫格上方样式
case 2:
cellStyle.BorderTop = outStyle;
break;
//九宫格右上角样式
case 3:
cellStyle.BorderTop = outStyle;
cellStyle.BorderRight = outStyle;
break;
//九宫格右边样式
case 4:
cellStyle.BorderRight = outStyle;
break;
//九宫格右下角样式
case 5:
cellStyle.BorderBottom = outStyle;
cellStyle.BorderRight = outStyle;
break;
//九宫格下方样式
case 6:
cellStyle.BorderBottom = outStyle;
break;
//九宫格左下角样式
case 7:
cellStyle.BorderBottom = outStyle;
cellStyle.BorderLeft = outStyle;
break;
//九宫格左边样式
case 8:
cellStyle.BorderLeft =outStyle;
break;
//单行单列样式
case 9:
cellStyle.BorderTop = outStyle;
cellStyle.BorderLeft = outStyle;
cellStyle.BorderRight = outStyle;
cellStyle.BorderBottom = outStyle;
break;
//单列多行上方样式
case 10:
cellStyle.BorderTop = outStyle;
cellStyle.BorderLeft = outStyle;
cellStyle.BorderRight = outStyle;
break;
//单列多行中间样式
case 11:
cellStyle.BorderLeft = outStyle;
cellStyle.BorderRight = outStyle;
break;
//单列多行下方样式
case 12:
cellStyle.BorderLeft = outStyle;
cellStyle.BorderRight = outStyle;
cellStyle.BorderBottom = outStyle;
break;
//单行多列右边样式
case 13:
cellStyle.BorderTop = outStyle;
cellStyle.BorderRight = outStyle;
cellStyle.BorderBottom = outStyle;
break;
//单行多列中间样式
case 14:
cellStyle.BorderTop = outStyle;
cellStyle.BorderBottom = outStyle;
break;
//单行多列下方样式
case 15:
cellStyle.BorderTop = outStyle;
cellStyle.BorderLeft = outStyle;
cellStyle.BorderBottom = outStyle;
break;
default:
break;
}
}
//outStyle为外边框样式, inStyle为内边框样式,颜色如果想换的话可以自己写函数,这里例子就不再写了
public void SetCellBorder(HSSFSheet sheet, NPOI.SS.UserModel.BorderStyle outStyle, NPOI.SS.UserModel.BorderStyle inStyle)
{
ICellStyle style = GetCellBorderStyle(0, outStyle, inStyle);
ICellStyle styleTop = GetCellBorderStyle(2, outStyle, inStyle);
ICellStyle styleRight = GetCellBorderStyle(4, outStyle, inStyle);
ICellStyle styleBottom = GetCellBorderStyle(6, outStyle, inStyle);
ICellStyle styleLeft = GetCellBorderStyle(8, outStyle, inStyle);
int firstRowNum = sheet.FirstRowNum;
int lastRowNum = sheet.LastRowNum;
for (int i = firstRowNum; i <= lastRowNum; i++)
{
IRow row = sheet.GetRow(i);
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
for (int j = firstCellNum; j < lastCellNum; j++)
{
ICell cell = row.GetCell(j);
if (lastCellNum == 0)
{
//单行单列
if (lastRowNum == 0)
cell.CellStyle = GetCellBorderStyle(9, outStyle, inStyle);
else
{
//单列多行
if (i == 0)
cell.CellStyle = GetCellBorderStyle(10, outStyle, inStyle);
else if (i == lastRowNum)
cell.CellStyle = GetCellBorderStyle(12, outStyle, inStyle);
else
cell.CellStyle = GetCellBorderStyle(15, outStyle, inStyle);
}
}
else if(lastRowNum == 0)
{
//单行多列
if (j == 0)
cell.CellStyle = GetCellBorderStyle(11, outStyle, inStyle);
else if (j == lastCellNum - 1)
cell.CellStyle = GetCellBorderStyle(13, outStyle, inStyle);
else
cell.CellStyle = GetCellBorderStyle(14, outStyle, inStyle);
}
//多行多列
else
{
if (i == 0)
{
if (j == 0)
cell.CellStyle = GetCellBorderStyle(1, outStyle, inStyle);
else if (j == lastCellNum - 1)
cell.CellStyle = GetCellBorderStyle(3, outStyle, inStyle);
else
cell.CellStyle = styleTop;
}
else if (i == lastRowNum)
{
if (j == 0)
cell.CellStyle = GetCellBorderStyle(7, outStyle, inStyle);
else if (j == lastCellNum - 1)
cell.CellStyle = GetCellBorderStyle(5, outStyle, inStyle);
else
cell.CellStyle = styleBottom;
}
else
{
if (j == 0)
cell.CellStyle = styleLeft;
else if (j == lastCellNum - 1)
cell.CellStyle = styleRight;
else
cell.CellStyle = style;
}
}
}
}
}
#endregion
#region 五、筛选
//重点提一下 reference
//单列筛选时 类似 SetAutoFilter(sheet, "B1")即可
//多列筛选时 类型 SetAutoFilter(sheet, "A1:D1") 表示从A1单元格到D1单元格的4个单元格建立筛选
//每个表中只能建立一个筛选
//多次调用以最后一次为准
//除了上面两种方式没有别的方式,所以不可以间隔单元格建立筛选,所以提醒大家把需要做筛选的列放到一起去,中间不要有其他单元格间隔
public void SetAutoFilter(string sheetName, string reference)
{
CellRangeAddress range = CellRangeAddress.ValueOf(reference);
_hssfWorkbook.GetSheet(sheetName).SetAutoFilter(range);
}
#endregion
#region 六、导出
//选择保存路径
public SaveFileDialog SelectExportPath(string fileName)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "XLS files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出";
saveFileDialog.FileName = fileName;
if (DialogResult.OK == saveFileDialog.ShowDialog())
return saveFileDialog;
else
return null;
}
//导出
public void ExportToExcel(SaveFileDialog saveFileDialog)
{
if (saveFileDialog == null)
return;
Stream myStream = saveFileDialog.OpenFile();
_hssfWorkbook.Write(myStream);
myStream.Close();
}
//导入
public void ImportFile(importFilePath)
{
FileStream file = new FileStream(importFilePath, FileMode.Open, FileAccess.Read);
_hssfWorkbook = new HSSFWorkbook(file);
}
#endregion
#region 七、使用
//第一种情况,预先不知道多少行,但是表格格式统一,表格中没有特殊单元格,比如合并啊,居中啥的,这种情况边框放到最后加
//DataGridView是winform中类型表格的控件
public void ConvertDGVToSheet(DataGridView dv, string sheetName)
{
HSSFSheet sheet = CreateSheet(sheetName);
//冻结标题行
sheet.CreateFreezePane(0, 1, 0, 1);
//写标题
IRow colHeader = sheet.CreateRow(0);
for (int i = 0; i < dv.ColumnCount; i++)
{
if (dv.Columns[i].Visible)
{
colHeader.CreateCell(i).SetCellValue(dv.Columns[i].HeaderText);
}
}
//写内容
//int tmp = 1;
for (int j = 0; j < dv.Rows.Count; j++)
{
IRow row = sheet.CreateRow(j + 1);
for (int k = 0; k < dv.Columns.Count; k++)
{
if (dv.Columns[k].Visible)
row.CreateCell(k).SetCellValue(dv.Rows[j].Cells[k].FormattedValue.ToString());
}
}
//自动调整列间距
for (int i = 0; i < dv.ColumnCount; i++)
sheet.AutoSizeColumn(i);
SetCellBorder(sheet);
}
//第二种情况,有模板,知道那些行列需要合并等,固定行数列数
public void ExportAnalysisResult(DataGridView dv)
{
HSSFSheet sheet = _hssfworkbook.CreateSheet("分析结果") as HSSFSheet;
CreateTemplate(sheet);
SetCellBorder(sheet);
//设置列宽度
sheet.SetColumnWidth(0, 256 * 4);
sheet.SetColumnWidth(1, 256 * 22);
sheet.SetColumnWidth(2, 256 * 11);
sheet.SetColumnWidth(3, 256 * 21);
sheet.SetColumnWidth(4, 256 * 7);
sheet.SetColumnWidth(5, 256 * 28);
IRow row = sheet.GetRow(0);
ICell cell = row.GetCell(0);
cell.SetCellValue("分析结果单");
ICellStyle style = _hssfworkbook.CreateCellStyle();
//注意新建单元格样式的时候一定要克隆一下,否则单元格之前的样式就没了
style.CloneStyleFrom(cell.CellStyle);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
IFont font = _hssfworkbook.CreateFont();
font.FontHeight = 20 * 20;
style.SetFont(font);
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));
IRow colHeader = sheet.GetRow(2);
//写标题
for (int i = 0; i < dv.ColumnCount; i++)
{
if (dv.Columns[i].Visible)
{
if(i<3)
{
colHeader.GetCell(i).SetCellValue(dv.Columns[i].HeaderText);
if (i == 2)
colHeader.GetCell(i+1);
}
else
{
colHeader.GetCell(i+1).SetCellValue(dv.Columns[i].HeaderText);
}
}
}
sheet.AddMergedRegion(new CellRangeAddress(2, 2, 2, 3));
#endregion
public void Close()
{
_hssfworkbook.Close();
}
}
由于本人很懒,里面没有做异常处理,大家使用的时候最好加上异常处理。