本文已参与「新人创作礼」活动，一起开启掘金创作之路。
NPOI导入导出Excel
为什么选择NPOI
- NPOI是Apache POI的.Net 版本，Apache POI是一种流行的API，允许程序员使用Java程序创建，修改和显示MS Office文件。
- NPOI为国内开发的开源项目，文档完善，更新及时，开发便利
- 对NPOI的介绍和分析参考百度百科：(baike.baidu.com/item/NPOI/1…)
使用NPOI导出数据到Excel(Excel文件由代码生成)
- 该方案适用于Excel文件表头比较简单的情况（无多种单元格合并）
- 示例代码采用.net framework 窗体程序
private void exportButton_Click(object sender, EventArgs e)
{
// 填充学生信息
List<Student> students = new List<Student>();
for(int i = 0; i < 5; i++)
{
Student student = new Student();
student.id = "1";
student.sex = "男";
student.name = "帅" + i.ToString();
student.physics = "8" + i.ToString();
student.chemistry = "9" + i.ToString();
student.history = "7" + i.ToString();
student.geography = "6" + i.ToString();
students.Add(student);
}
// 创建DataTable 提供Excel数据
DataTable dataTable = new DataTable();
dataTable.Columns.Add("序号", typeof(string));
dataTable.Columns.Add("性别", typeof(string));
dataTable.Columns.Add("姓名", typeof(string));
dataTable.Columns.Add("物理", typeof(string));
dataTable.Columns.Add("化学", typeof(string));
dataTable.Columns.Add("历史", typeof(string));
dataTable.Columns.Add("地理", typeof(string));
for(int i = 0; i < students.Count; i++)
{
DataRow dataRow = dataTable.NewRow();
dataRow["序号"] = i + 1;
dataRow["性别"] = students[i].sex;
dataRow["姓名"] = students[i].name;
dataRow["物理"] = students[i].physics;
dataRow["化学"] = students[i].chemistry;
dataRow["历史"] = students[i].history;
dataRow["地理"] = students[i].geography;
// 将这行数据添加到dataTable中
dataTable.Rows.Add(dataRow);
}
// 将数据导出
// saveFileDialog1为窗体组件SaveFileDialog
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
string filePath = saveFileDialog1.FileName;
ExcelHelper.ExportExcel(dataTable, filePath);
}
}
// 工具类
public static void ExportExcel(DataTable dataTable,string filePath)
{
// 创建工作簿
HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
// 创建不同的工作表 存不同项目的数据
ISheet sheetA = hSSFWorkbook.CreateSheet("成绩1");
ISheet sheetB = hSSFWorkbook.CreateSheet("成绩2");
ISheet sheetC = hSSFWorkbook.CreateSheet("成绩3");
// 向excel中填充内容
// 填充表头
IRow dataRow = sheetA.CreateRow(0);
foreach (DataColumn column in dataTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// 填充内容
for (int i = 0; i < dataTable.Rows.Count; i++)
{
dataRow = sheetA.CreateRow(i + 1);
for (int j = 0; j < dataTable.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dataTable.Rows[i][j].ToString());
}
}
// 将文件输出
FileStream file = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write);
//创建一个IO流(内存流类，主要用于操作内存中的数据)
MemoryStream ms = new MemoryStream();
//写入到流
hSSFWorkbook.Write(ms);
var bytes = ms.ToArray();
file.Write(bytes, 0, bytes.Length);
//定时的强制写入文件，可以防止丢失大部分数据
file.Flush();
ms.Close();
ms.Dispose();
//写入到流
//workbook.Write(file);
file.Close();
file.Dispose();
hSSFWorkbook.Close();
}
使用NPOI导出数据到Excel(使用Excel模板)
- 该方案通过读取设计好的Excel模板，向文件中填充数据，模板包括表头列头和样式
// 工具类
public static void ExportExcel(DataTable dataTable,string filePath)
{
string filrUrl = "C:\\student.xls";
FileStream file = new FileStream(filrUrl, FileMode.Open, FileAccess.Read);
// 创建工作簿
HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(file);
// 创建不同的工作表 存不同项目的数据
ISheet sheetA = hSSFWorkbook.GetSheet("Sheet1");
ISheet sheetB = hSSFWorkbook.GetSheet("Sheet2");
ISheet sheetC = hSSFWorkbook.GetSheet("Sheet3");
// 向excel中填充内容
// 填充表头
IRow dataRow;
// 填充内容
for (int i = 0; i < dataTable.Rows.Count; i++)
{
dataRow = sheetA.CreateRow(i + 2);
for (int j = 0; j < dataTable.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dataTable.Rows[i][j].ToString());
}
}
// 将文件输出
FileStream resultFile = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write);
//创建一个IO流(内存流类，主要用于操作内存中的数据)
MemoryStream ms = new MemoryStream();
//写入到流
hSSFWorkbook.Write(ms);
var bytes = ms.ToArray();
resultFile.Write(bytes, 0, bytes.Length);
//定时的强制写入文件，可以防止丢失大部分数据
resultFile.Flush();
ms.Close();
ms.Dispose();
//写入到流
//workbook.Write(file);
resultFile.Close();
resultFile.Dispose();
hSSFWorkbook.Close();
}
使用NPOI读取Excel导入数据到数据库
private void importButton_Click(object sender, EventArgs e)
{
// 使用OpenFileDialog窗体组件打开文件
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
DataTable dataTable;
string sheetName = "成绩1";
dataTable = ExcelHelper.importExcel(openFileDialog1.FileName, sheetName);
if (dataTable != null)
{
for (int i = 0; i < dataTable.Rows.Count; i++)
{
Student student = new Student();
student.id = i.ToString();
student.sex = dataTable.Rows[i][1].ToString();
student.name = dataTable.Rows[i][2].ToString();
student.physics = dataTable.Rows[i][3].ToString();
student.chemistry = dataTable.Rows[i][4].ToString();
student.history = dataTable.Rows[i][5].ToString();
student.geography = dataTable.Rows[i][6].ToString();
Console.WriteLine(student.ToString());
}
}
}
}
// Excel工具类
public static DataTable importExcel(string filePath, string sheetName)
{
IWorkbook workbook;
using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
workbook = new HSSFWorkbook(file);
}
ISheet sheet = workbook.GetSheet(sheetName);
DataTable dataTable = new DataTable();
//第一行标题
var headerRow = sheet.GetRow(0);
var cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
var column = new DataColumn(headerRow.GetCell(i).StringCellValue);
dataTable.Columns.Add(column);
}
// 读取数据
for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)
{
var row = sheet.GetRow(j);
var dataRow = dataTable.NewRow();
if (row != null)
{
for (int i = 0; i < row.LastCellNum; i++)
{
var cell = row.GetCell(i);
if (cell == null)
{
dataRow[i] = null;
}
else
{
dataRow[i] = cell.ToString();
}
}
dataTable.Rows.Add(dataRow);
}
}
return dataTable;
}
