以前编程读取或写入Excel文档基本都是使用Microsoft Office的组件，当需要打开大量文档时，效率不高，极大的影响速度，最近需要读入或写出上千个文档，再使用原来的方法不能忍受其速度。于是想到用NPOI的方式，效率大大提升，而且还不用担心客户机不同的Office版本兼容性问题。

下面来详细介绍...

引用

使用NPOI方式读取Excel文档时需要添加引用，至少需要三个dll，分别是NPOI.dll、NPOI.OOXML.dll、NPOI.OpenXml4Net.dll，可以从此链接下载链接：https://pan.baidu.com/s/1B9zPC3yt25UkCr5vT2qVKA 密码：oyg9

读取Excel文档

private List<String> loadXlsDataByNPOI(String xls_templatePath)

{

List<String> xls_data = new List<String>();

ISheet sheet = null;

int startRow = 0;

try

{

IWorkbook workbook = null;

System.IO.FileStream fs = new System.IO.FileStream(xls_templatePath, FileMode.Open, FileAccess.Read);

String file_extension = System.IO.Path.GetExtension(xls_templatePath).ToUpper();

if (file_extension == ".XLSX") // 2007版本

workbook = new XSSFWorkbook(fs);

else if (file_extension == ".XLS") // 2003版本

workbook = new HSSFWorkbook(fs);

sheet = workbook.GetSheetAt(0);

if (sheet != null)

{

int rowCount = sheet.LastRowNum;

for (int i = startRow; i < rowCount; i++)

{

NPOI.SS.UserModel.IRow row = sheet.GetRow(i);

if (row == null) continue; //没有数据的行默认是null

xls_data.Add(row.GetCell(0).ToString());

}

}

}

catch (Exception ex)

{

Console.WriteLine("Exception: " + ex.Message);

return null;

}

return xls_data;

}

写入模板Excel文档

当需要打开模板Excel并写入数据时可以用下面的代码：

private void WriteXlsDataByNPOI(String xls_templatepath,String current_xmlpath)

{

//设置Xls路径

File.Copy(xls_templatepath, current_xmlpath, true);

HSSFWorkbook wk = null;

using (System.IO.FileStream fs = File.Open(current_xmlpath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))

{

wk = new HSSFWorkbook(fs);

fs.Close();

}

ISheet sheet = wk.GetSheetAt(0);

for (int uu = 0; uu < dataGridView_metadata.Columns.Count; uu++)

{

ICell cell = sheet.GetRow(uu).GetCell(1);

cell.SetCellValue(Convert.ToString(dataGridView_metadata.Rows[yy].Cells[uu].Value));

}

using (System.IO.FileStream fileStream = File.Open(current_xmlpath, FileMode.OpenOrCreate, FileAccess.ReadWrite))

{

wk.Write(fileStream);

fileStream.Close();

}

}

导出新Excel文档

private void exporttoexcel_Click()

{

SaveFileDialog open_save = new SaveFileDialog();

open_save.Title = "请选择存放位置";

open_save.Filter = "Excel文件 (*.xls)|*.xls|Excel文件 (*.xlsx)|*.xlsx";

open_save.ShowDialog();

String xls_path = open_save.FileName;

if (xls_path == "")

{

return;

}

else

{

IWorkbook workbook;

string fileExt = System.IO.Path.GetExtension(xls_path).ToLower();

if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }

if (workbook == null) { return; }

ISheet sheet = (string.IsNullOrEmpty(create_metadata_type) || create_metadata_type =="") ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(create_metadata_type);

//表头

NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);

for (int i = 0; i < dataGridView_metadata.Columns.Count; i++)

{

ICell cell = row.CreateCell(i);

cell.SetCellValue(dataGridView_metadata.Columns[i].HeaderText);

}

this.toolStripLabel_label.Text = "正在导出Excel表格...";

//数据

int rows_count = dataGridView_metadata.Rows.Count;

for (int i = 0; i < rows_count; i++)

{

NPOI.SS.UserModel.IRow current_row = sheet.CreateRow(i + 1);

for (int j = 0; j < dataGridView_metadata.Columns.Count; j++)

{

ICell cell = current_row.CreateCell(j);

cell.SetCellValue(dataGridView_metadata.Rows[i].Cells[j].Value.ToString());

}

}

//转为字节数组

MemoryStream stream = new MemoryStream();

workbook.Write(stream);

var buf = stream.ToArray();

//保存为Excel文件

using (System.IO.FileStream fs = new System.IO.FileStream(xls_path, FileMode.Create, FileAccess.Write))

{

fs.Write(buf, 0, buf.Length);

fs.Flush();

}

MessageBox.Show("导出Excel完成！");

}

}

回想起来，使用NPOI方式编辑Excel就这几种情况，做个笔记保存在这里，方面以后再来回味...