NPOI 操作Excel - sunwugang

（一）传统操作Excel遇到的问题：

1、如果是.NET，需要在服务器端装Office，且及时更新它，以防漏洞，还需要设定权限允许.NET访问COM+，如果在导出过程中出问题可能导致服务器宕机。

2、Excel会把只包含数字的列进行类型转换，本来是文本型的，Excel会将其转成数值型的，比如编号000123会变成123。

3、导出时，如果字段内容以“-”或“=”开头，Excel会把它当成公式进行，会报错。

4、Excel会根据Excel文件前8行分析数据类型，如果正好你前8行某一列只是数字，那它会认为该列为数值型，自动将该列转变成类似1.42702E+17格式，日期列变成包含日期和数字的。

（二）使用NPOI的优势

1、您可以完全免费使用该框架

2、包含了大部分EXCEL的特性(单元格样式、数据格式、公式等等)

3、专业的技术支持服务(24*7全天候) (非免费)

4、支持处理的文件格式包括xls, xlsx, docx.

5、采用面向接口的设计架构( 可以查看 NPOI.SS 的命名空间)

6、同时支持文件的导入和导出

7、基于.net 2.0 也支持xlsx 和 docx格式(当然也支持.net 4.0)

8、来自全世界大量成功且真实的测试Cases

9、大量的实例代码

11、你不需要在服务器上安装微软的Office，可以避免版权问题。

12、使用起来比Office PIA的API更加方便，更人性化。

13、你不用去花大力气维护NPOI，NPOI Team会不断更新、改善NPOI，绝对省成本。

NPOI之所以强大，并不是因为它支持导出Excel，而是因为它支持导入Excel，并能“理解”OLE2文档结构，这也是其他一些Excel读写库比较弱的方面。通常，读入并理解结构远比导出来得复杂，因为导入你必须假设一切情况都是可能的，而生成你只要保证满足你自己需求就可以了，如果把导入需求和生成需求比做两个集合，那么生成需求通常都是导入需求的子集，这一规律不仅体现在Excel读写库中，也体现在pdf读写库中，目前市面上大部分的pdf库仅支持生成，不支持导入。

实例如下所示：

NPOI使用步骤：
==>获取NPOI第三方DLL及相关信息V_2.1.1.0
==>引入相关DLL,如下图所示：
==>创建ExcelHelper帮助类
==>实现具体的保存(如下，从GridView中保存数据到Excel)
注意：本地需要有相关ExcelTemplate.xls模板

--------------Excel操作帮助类ExcelHelper--------------
public abstract class ExcelHelper
{
#region structure、define

public ExcelHelper() { }

/// <summary>
/// NPIO的workbook
/// </summary>
private IWorkbook Workbook;

/// <summary>
/// NPIO的sheet
/// </summary>
private ISheet Sheet;

#endregion

#region public method

/// <summary>
/// 将文件保存到指定的位置
/// </summary>
public void WriteToFile(string filePath)
{
try
{
using (FileStream file = new FileStream(filePath, FileMode.Create))
{
Workbook.Write(file);
file.Close();
}

DialogResult result = MessageBox.Show(
"保存测试结果完成\n是否查看测试结果",
"提示",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
System.Diagnostics.Process.Start(filePath);
}
}
catch
{
return;
}
}

public bool WriteToFile2(string filePath)
{
try
{
using (FileStream file = new FileStream(filePath, FileMode.Create))
{
Workbook.Write(file);
file.Close();
}
}
catch
{
return false;
}
return true;
}

/// <summary>
/// 将指定位置的文件复制到指定的新位置
/// </summary>
/// <param name="sourceFileName">选择文件路径</param>
/// <param name="targetFileName">目标文件路径</param>
public void CopyModel(string sourceFileName, string targetFileName)
{
File.Copy(sourceFileName, targetFileName, true);
}

/// <summary>
/// 是否存在指定的模版文件
/// </summary>
/// <param name="filePath">文件路径</param>
/// <returns>true/false</returns>
public bool IsExistFile(string filePath)
{
string excelModelPath = AppDomain.CurrentDomain.BaseDirectory + "\\Template.xlsx";
if (File.Exists(excelModelPath))
{
File.Copy(excelModelPath, filePath, true);
return true;
}
else
{
MessageBox.Show("加载Excel模板失败,请从新加载Execl模板!");
return false;
}
}

/// <summary>
///设置样式—标题
/// </summary>
/// <returns></returns>
public ICellStyle SetHeaderStyle(string headerTest)
{
//合并单元格
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
// Sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));

ICellStyle style = Workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

IFont font = Workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = short.MaxValue;
font.FontName = "微软雅黑";
style.SetFont(font);

//边框
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

return style;
}

/// <summary>
/// 设置样式—列表表头
/// </summary>
/// <param name="fontSize">字体大小</param>
/// <returns></returns>
public ICellStyle SetColumnStyle(int fontSize)
{
ICellStyle style = Workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
IFont font = Workbook.CreateFont();
font.FontHeightInPoints = (short)fontSize;
font.Boldweight = short.MaxValue;
font.FontName = "微软雅黑";
style.SetFont(font);

//边框
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

return style;
}

public ISheet GetSheet(string filePath, string sheetName)
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite))
{
if (Workbook == null)
{
Workbook = new XSSFWorkbook(file);
}

Sheet = Workbook.GetSheet(sheetName);
if (Sheet == null)
{
Sheet = Workbook.CreateSheet(sheetName);
}
//合并单元格
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
Sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));
file.Close();

return Sheet;
}
}

/// <summary>
/// 将RadGridView中数据导入到Excel
/// 第一步，判断Excel是否存在
/// 第二步，获取或创建Sheet
/// 第三步，获取数据并保存到流
/// 第四步，保存数据到文件
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="sheetName">sheet名称</param>
/// <param name="dataview">RadGridView</param>
/// <param name="headerTest">Excel标题内容</param>
public void SaveDataToExcel(string filePath, string sheetName,
RadGridView dataview, string headerTest)
{
try
{
if (!IsExistFile(filePath))
{
return;
}
//获取Sheet
ISheet sheet = GetSheet(filePath, sheetName);
//获取数据
GetData(sheet, dataview, headerTest);
//保存数据
WriteToFile(filePath);
}
catch (Exception ex)
{
MessageBox.Show("error==" + ex.Message);
}
}

public bool SaveDataToExcel2(string filePath, string sheetName,
RadGridView dataview, string headerTest)
{
try
{
if (!IsExistFile(filePath))
{
return false;
}
//获取Sheet
ISheet sheet = GetSheet(filePath, sheetName);
//获取数据
GetData(sheet, dataview, headerTest);
//保存数据
//WriteToFile2(filePath);
}
catch (Exception ex)
{
MessageBox.Show("error==" + ex.Message);
}
return true;
}

#endregion

#region virtual/abstract method

/// <summary>
/// 将RadGridView中数据导入到Excel
/// 第一步，判断Excel是否存在
/// 第二步，获取或创建Sheet
/// 第三步，获取数据并保存到流
/// 第四步，保存数据到文件
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="sheetName">sheet名称</param>
/// <param name="modelList">modelList,实体对象集合</param>
/// <param name="headerTest">Excel标题内容</param>
public virtual void SaveDataToExcel<TModel>(string filePath, string sheetName,
List<TModel> modelList, string headerTest) { }

/// <summary>
/// 获取数据并保存到流
/// </summary>
/// <param name="sheet">sheet</param>
/// <param name="dataview">RadGridView</param>
/// <param name="headerTest">Excel标题内容</param>
public abstract void GetData(ISheet sheet, RadGridView dataview, string headerTest);

#endregion

}
---------具体实现类---------
public class SaveGridViewToExcel : ExcelHelper
{
#region structure、single

public SaveGridViewToExcel() { }

private static SaveGridViewToExcel instance;

public static SaveGridViewToExcel Instance
{
get
{
if (instance ==null)
{
instance = new SaveGridViewToExcel();
}
return SaveGridViewToExcel.instance;
}
}

/// <summary>
/// Excel样式
/// </summary>
private ICellStyle Style = null;
private ICellStyle ColStyle = null;

#endregion

#region override method

public override void GetData(ISheet sheet, RadGridView dataview, string headerTest)
{
//获取样式
//获取样式
if (Style == null)
{
Style = SetHeaderStyle(headerTest);
}
if (ColStyle == null)
{
ColStyle = SetColumnStyle(16);
}
//添加标题并设置其样式
IRow headerRow = sheet.CreateRow(0);
sheet.GetRow(0).CreateCell(0).CellStyle = Style;
sheet.GetRow(0).CreateCell(1).CellStyle = Style;
sheet.GetRow(0).CreateCell(2).CellStyle = Style;
sheet.GetRow(0).CreateCell(3).CellStyle = Style;
sheet.GetRow(0).CreateCell(4).CellStyle = Style;
sheet.GetRow(0).CreateCell(5).CellStyle = Style;
sheet.GetRow(0).GetCell(0).SetCellValue(headerTest);
//列表标题
sheet.CreateRow(1);
sheet.GetRow(1).CreateCell(0).CellStyle = ColStyle;
sheet.GetRow(1).CreateCell(1).CellStyle = ColStyle;
sheet.GetRow(1).CreateCell(2).CellStyle = ColStyle;
sheet.GetRow(1).CreateCell(3).CellStyle = ColStyle;
sheet.GetRow(1).CreateCell(4).CellStyle = ColStyle;
sheet.GetRow(1).CreateCell(5).CellStyle = ColStyle;
sheet.GetRow(1).GetCell(0).SetCellValue(dataview.Columns[0].HeaderText);
sheet.GetRow(1).GetCell(1).SetCellValue(dataview.Columns[1].HeaderText);
sheet.GetRow(1).GetCell(2).SetCellValue(dataview.Columns[2].HeaderText);
sheet.GetRow(1).GetCell(3).SetCellValue(dataview.Columns[3].HeaderText);
sheet.GetRow(1).GetCell(4).SetCellValue(dataview.Columns[4].HeaderText);
sheet.GetRow(1).GetCell(5).SetCellValue(dataview.Columns[5].HeaderText);

int startpoint = 2;
ICellStyle colStyle2 = null;
for (int i = 0; i < dataview.Rows.Count; i++)
{
//首次需要创建行
sheet.CreateRow(startpoint).CreateCell(0).SetCellValue(dataview.Rows[i].Cells[0].Value.ToString());
sheet.GetRow(startpoint).CreateCell(1).SetCellValue(dataview.Rows[i].Cells[1].Value.ToString());
sheet.GetRow(startpoint).CreateCell(2).SetCellValue(dataview.Rows[i].Cells[2].Value.ToString());
sheet.GetRow(startpoint).CreateCell(3).SetCellValue(dataview.Rows[i].Cells[3].Value.ToString());
sheet.GetRow(startpoint).CreateCell(4).SetCellValue(dataview.Rows[i].Cells[4].Value.ToString());
sheet.GetRow(startpoint).CreateCell(5).SetCellValue(dataview.Rows[i].Cells[5].Value.ToString());
//设置样式
if (colStyle2 == null)
{
colStyle2 = SetColumnStyle(12);
}
sheet.GetRow(startpoint).GetCell(0).CellStyle = colStyle2;
sheet.GetRow(startpoint).GetCell(1).CellStyle = colStyle2;
sheet.GetRow(startpoint).GetCell(2).CellStyle = colStyle2;
sheet.GetRow(startpoint).GetCell(3).CellStyle = colStyle2;
sheet.GetRow(startpoint).GetCell(4).CellStyle = colStyle2;
sheet.GetRow(startpoint).GetCell(5).CellStyle = colStyle2;
startpoint++;
}
}

#endregion

}

-------------客户端调用---------------

UI

源码
public partial class Form1 : RadForm
{
public Form1()
{
InitializeComponent();

InitData();
}

private void InitData()
{
dgvList.Rows.AddNew();
dgvList.Rows[0].Cells[0].Value = "1";
dgvList.Rows[0].Cells[1].Value = "西游记";
dgvList.Rows[0].Cells[2].Value = "380";
dgvList.Rows[0].Cells[3].Value = "180";
dgvList.Rows[0].Cells[4].Value = "陕西邮电出版社";
dgvList.Rows[0].Cells[5].Value = "哈哈";

dgvList.Rows.AddNew();
dgvList.Rows[1].Cells[0].Value = "2";
dgvList.Rows[1].Cells[1].Value = "红楼梦";
dgvList.Rows[1].Cells[2].Value = "390";
dgvList.Rows[1].Cells[3].Value = "100";
dgvList.Rows[1].Cells[4].Value = "中央邮电出版社";
dgvList.Rows[1].Cells[5].Value = "哈哈哈";

dgvList.Rows.AddNew();
dgvList.Rows[2].Cells[0].Value = "3";
dgvList.Rows[2].Cells[1].Value = "三国演义";
dgvList.Rows[2].Cells[2].Value = "360";
dgvList.Rows[2].Cells[3].Value = "120";
dgvList.Rows[2].Cells[4].Value = "山东出版社";
dgvList.Rows[2].Cells[5].Value = "哈哈哈哈";

dgvList.Rows.AddNew();
dgvList.Rows[3].Cells[0].Value = "4";
dgvList.Rows[3].Cells[1].Value = "水浒传";
dgvList.Rows[3].Cells[2].Value = "380";
dgvList.Rows[3].Cells[3].Value = "108";
dgvList.Rows[3].Cells[4].Value = "山东出版社";
dgvList.Rows[3].Cells[5].Value = "哈哈哈哈哈";
for (int i = 4; i < 5000; i++)
{
dgvList.Rows.AddNew();
dgvList.Rows[i].Cells[0].Value = (i + 1).ToString();
dgvList.Rows[i].Cells[1].Value = "水浒传";
dgvList.Rows[i].Cells[2].Value = "380";
dgvList.Rows[i].Cells[3].Value = "108";
dgvList.Rows[i].Cells[4].Value = "山东出版社";
dgvList.Rows[i].Cells[5].Value = "呵呵呵";
}
}

private void btnExport_Click(object sender, EventArgs e)
{
string sheetName = "图书信息列表";
SaveFileDialog sf = new SaveFileDialog();
sf.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
if (sf.ShowDialog() == DialogResult.OK)
{
string path = string.Empty;
if (!sf.FileName.Contains(".xlsx"))
{
path = sf.FileName + ".xlsx";
}
else
{
path = sf.FileName;
}
//ExcelManage.Instance.SaveDataToExcelByGridView(path,
// sheetName, this.dgvList, "基础数据信息列表");

//SaveGridViewToExcel.Instance.SaveDataToExcel(path,
// sheetName, this.dgvList, "基础数据信息列表");

ExcelHelper helper = new SaveGridViewToExcel();
helper.SaveDataToExcel(path, sheetName, this.dgvList, "基础数据信息列表");

}
}

string path = string.Empty;
private void btnExportAll_Click(object sender, EventArgs e)
{
SaveFileDialog sf = new SaveFileDialog();
sf.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
if (sf.ShowDialog() == DialogResult.OK)
{
this.lblStartTime.Text = DateTime.Now.ToString();
if (!sf.FileName.Contains(".xlsx"))
{
path = sf.FileName + ".xlsx";
}
else
{
path = sf.FileName;
}

SaveData();
}
}

private void SaveData()
{
ExcelHelper helper = new SaveGridViewToExcel();
string title = "基础数据信息列表";
for (int i = 1; i < 21; i++)
{
helper.SaveDataToExcel2(path, Convert.ToString(title + i), this.dgvList, "基础数据信息列表");
}

helper.WriteToFile2(path);

this.lblEndTime.Text = DateTime.Now.ToString();

DialogResult result = MessageBox.Show(
"保存测试结果完成\n是否查看测试结果",
"提示",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
System.Diagnostics.Process.Start(path);
}
}
}

欢迎莅临 SUN WU GANG 的园子！！！

公告