第22课：读写Excel文件
在数据驱动的应用中交换数据，Excel一种常用的格式，比如，将服务器端导出的数据转换为Excel文件，下载后可以直接加工使用，数据加工完成后可以上传并批量更新，为用户带来了极大的便利性。本课将讨论Excel数据的读取与写入。
本节操作使用了第三方资源NPOI，可以从https://github.com/nissl-lab/npoi获取相关信息。
在Visual Studio 2019集成开发环境中，可以通过菜单项“工具”>>“NuGet包管理器”>>“管理解决方案的NuGet程序包”选择安装相关资源，主要包括NPOI.dll、NPOI.OOXML.dll、NPOI.OpenXml4Net.dll和NPOI.OpenXmlFormats.dll等文件，这些文件会自动安装到网站根目录下的bin目录中。
将数据写入Excel文件时，.xls和.xlsx格式的操作是不同的，需要分别编码实现；而读取Excel文件时，则可以使用相同的代码完成。代码中使用tExcel类完成Excel文件的读取和写入操作。
写入Excel文件
首先来看Excel文件的写入操作，如下面的代码（/app_code/office/tExcel.cs）。
| C# |
using System;
using System.IO;
using System.Data;
using System.Collections.Generic;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
public static class tExcel
{
// 将DataTable对象写入Excel文件的第一个工作表
public static bool WriteExcel(DataTable tbl, string path,
bool writeColName = true)
{
string ext = Path.GetExtension(path).ToLower();
if (ext == ".xls")
return WriteXls(tbl, path, writeColName);
else
return WriteXlsx(tbl, path, writeColName);
}
// 将DataTable对象写入.xls文件第一个工作表
private static bool WriteXls(DataTable tbl, string xlsFile,
bool writeColName = true)
{
try
{
if (tbl == null || tbl.Columns.Count < 1)
return false;
//
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
int curRow = 0;
// 写入列名
if (writeColName)
{
IRow sheetRow = sheet.CreateRow(0);
for (int col = 0; col < tbl.Columns.Count; col++)
{
ICell cell = sheetRow.CreateCell(col);
cell.SetCellType(CellType.String);
cell.SetCellValue(tbl.Columns[col].ColumnName);
}
curRow++;
}
// 日期和时间格式
IDataFormat dataFormat = wb.CreateDataFormat();
ICellStyle datetimeStyle = wb.CreateCellStyle();
datetimeStyle.DataFormat =
dataFormat.GetFormat("yyyy/MM/dd HH:mm:ss");
// 写入数据行
for (int row = 0; row < tbl.Rows.Count; row++)
{
IRow sheetRow = sheet.CreateRow(curRow++);
for (int col = 0; col < tbl.Columns.Count; col++)
{
ICell cell = sheetRow.CreateCell(col);
Type dataType = tbl.Columns[col].DataType;
if (tType.IsNumeric(dataType))
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(tDbl.GetValue(tbl.Rows[row][col]));
}
else if (dataType.Name == "DateTime")
{
cell.CellStyle = datetimeStyle;
cell.SetCellValue(tDate.GetValue(tbl.Rows[row][col]));
}
else if (dataType.Name == "Boolean")
{
cell.SetCellType(CellType.Boolean);
cell.SetCellValue(tBool.GetValue(tbl.Rows[row][col]));
}
else
{
cell.SetCellType(CellType.String);
cell.SetCellValue(tStr.GetValue(tbl.Rows[row][col]));
}
}
}
//
FileStream fs = new FileStream(xlsFile, FileMode.Create);
wb.Write(fs);
fs.Close();
//
sheet = null;
wb = null;
return true;
}
catch (Exception ex)
{
tLog.E(ex, -1000L, "tExcel.WriteXls");
return false;
}
}
// 将DataTable对象写入.xlsx文件第一个工作表
private static bool WriteXlsx(DataTable tbl, string xlsxFile,
bool writeColName = true)
{
try
{
if (tbl == null || tbl.Columns.Count < 1)
return false;
//
XSSFWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
int curRow = 0;
// 写入列名
if (writeColName)
{
IRow sheetRow = sheet.CreateRow(0);
for (int col = 0; col < tbl.Columns.Count; col++)
{
ICell cell = sheetRow.CreateCell(col);
cell.SetCellValue(tbl.Columns[col].ColumnName);
}
curRow++;
}
// 日期和时间格式
IDataFormat dataFormat = wb.CreateDataFormat();
ICellStyle datetimeStyle = wb.CreateCellStyle();
datetimeStyle.DataFormat =
dataFormat.GetFormat("yyyy/MM/dd HH:mm:ss");
// 写入数据行
for (int row = 0; row < tbl.Rows.Count; row++)
{
IRow sheetRow = sheet.CreateRow(curRow++);
for (int col = 0; col < tbl.Columns.Count; col++)
{
ICell cell = sheetRow.CreateCell(col);
Type dataType = tbl.Columns[col].DataType;
if (tType.IsNumeric(dataType))
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(tDbl.GetValue(tbl.Rows[row][col]));
}
else if (dataType.Name == "DateTime")
{
cell.CellStyle = datetimeStyle;
cell.SetCellValue(tDate.GetValue(tbl.Rows[row][col]));
}
else if (dataType.Name == "Boolean")
{
cell.SetCellType(CellType.Boolean);
cell.SetCellValue(tBool.GetValue(tbl.Rows[row][col]));
}
else
{
cell.SetCellType(CellType.String);
cell.SetCellValue(tStr.GetValue(tbl.Rows[row][col]));
}
}
}
//
FileStream fs = new FileStream(xlsxFile, FileMode.Create);
wb.Write(fs);
fs.Close();
//
sheet = null;
wb = null;
//
return true;
}
catch (Exception ex)
{
tLog.E(ex, -1000L, "tExcel.WriteXlsx()");
return false;
}
}
// 其它代码...
}
|
代码中，WriteExcel()方法是Excel文件写入的通用方法，其中包括三个参数，分别是：
- tbl，定义为DataTable对象，指定需要写入的数据。
- path，指定写入Excel文件的路径。
- writeColName，指定是否在表的第一行写入列名，默认值为true。
WriteExcel()方法中，会根据指定Excel文件名判断是.xls或.xlsx文件，然后分别调用WriteXls()或WriteXlsx()方法，两个方法的参数相同，分别是需要写入的数据（DateTable对象）、Excel文件路径，以及是否将DataTable对象中的列名作为第一行数据，即作为标题行（默认为true）；方法中的大部分代码是相同的，只是在处理Excel文档（Workbook对象）时使用的类型不同，处理.xls文件时使用了HSSFWorkbook类型，处理.xlsx文件时使用了XSSFWorkbook类型。
数据写入单元格时会调用单元格对象的SetCellValue()方法，此方法有四个重载版本，分别用于写入double、DateTime、bool和string类型数据。代码中，首先会判断DataTable对象中对应列的数据类型，当数据为数值类型时，调用将数据转换为double类型后写入；数据类型为DateTime类型时转换为DateTime类型后写入，并设置日期和时间格式为“yyyy/MM/dd HH:mm:ss”，即4位年份、2位月份、2位日期、2位24小时制时间、2位分名和2位秒数；数据类型为Boolean时转换为bool类型后写入；其它类型的数据则转换为字符串后写入单元格。
下面的代码，我们将user_main表中的username、email和locked字段数据分别导出到D:盘下的user.xls和user.xlsx文件。
| C# |
using System;
using System.Data;
public partial class Test : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
tSelect sel = tApp.Db.GetSelect("user_main");
DataTable tbl = sel.GetTable("username,email,locked", "");
tWeb.WriteLine(tExcel.WriteExcel(tbl, @"d:\user.xls"));
tWeb.WriteLine(tExcel.WriteExcel(tbl, @"d:\user.xlsx"));
}
}
|
操作成功时，页面会显示两个True，此时，可以在D:盘下查看导出的Excel文件，两个文件包含了相同的数据。
读取Excel文件
读取Excel文件中的数据时，需要注意一个问题，即每行中包含有效数据的单元格数量有可能不同，NPIO代码库处理可能会有读取数据不完整的问题；针对这一问题，在读取数据前可以先检查一定行数的数据，如检查前100行数据，不足100行检查全部行的列数，以判断最大列数。
实际应用中，也可以在读取方法中设置一个参数指定检查的行数；也可以在应用中对Excel文件格式作出约定，即第一行作为标题行，也是数据最完整的一行。
下面的代码（/app_code/office/tExcel.cs）就是tExcel.ReadExcel()方法的实现，其功能是读取Excel文件中指定表单（Sheet）的数据。
| C# |
public static DataTable ReadAll(string path,
int sheetIndex = 0,
bool readColName = true)
{
try
{
IWorkbook wb = WorkbookFactory.Create(path);
ISheet ws = wb.GetSheetAt(sheetIndex);
// 处理标题，创建数据表的列
DataTable tbl = new DataTable();
// 通过前100行（或全部行）判断最大列
int cols = ws.GetRow(0).LastCellNum;
int rows = ws.LastRowNum + 1;
int chkRows = (rows >= 100) ? 100 : rows;
int tmp;
for (int row = 1; row < chkRows; row++)
{
tmp = ws.GetRow(row).LastCellNum;
if (tmp > cols) cols = tmp;
}
// 添加DataTable对象的列
for (int col = 0; col < cols; col++)
tbl.Columns.Add();
// 读取数据
IRow readRow;
for (int row = 0; row < rows; row++)
{
readRow = ws.GetRow(row);
DataRow dataRow = tbl.NewRow();
for (int col = 0; col < cols; col++)
{
try
{
ICell cell = readRow.GetCell(col);
if (cell == null)
{
dataRow[col] = DBNull.Value;
}
else if (cell.CellType == CellType.Numeric)
{
// Excel中的日期时间值是Double类型
if (DateUtil.IsCellDateFormatted(cell))
dataRow[col] = cell.DateCellValue;
else
dataRow[col] = cell.NumericCellValue;
}
else if (cell.CellType == CellType.Boolean)
{
dataRow[col] = cell.BooleanCellValue ? 1 : 0;
}
else
{
string val = cell.StringCellValue.Trim();
if (val == "") dataRow[col] = DBNull.Value;
else dataRow[col] = val;
}
}
catch
{
dataRow[col] = DBNull.Value;
}
}
tbl.Rows.Add(dataRow);
}
// 处理标题
if (readColName)
{
string sName;
for (int col = 0; col < cols; col++)
{
sName = tStr.GetValue(tbl.Rows[0][col]).Trim();
if (sName != "") tbl.Columns[col].ColumnName = sName;
}
tbl.Rows.RemoveAt(0);
}
//
return tbl;
}
catch (Exception ex)
{
tLog.E(ex, -1000L, "tExcel.ReadExcel()");
return null;
}
}
|
代码中，ReadAll()方法需要三个参数，分别是：
- path，指定读取Excel文件的路径。
- sheetIndex，指定读取工作表（Sheet）的索引值。请注意，与使用官方API操作不同，这里，第一个工作表的的索引值为0，这也是默认读取的工作表。
- readColName，指定是否读取第一行数据作为列名，默认值为true。方法中，在读取全部数据后，如果指定第一行作为标题行，会其数据设置为DataTable对象的列名（ColumnName），然后从DataTable对象中删除第一行。
读取单元格数据时，首先会判断是否为空值，如果不是空值，则分别按数字类型、布尔类型或文本类型读取数据。需要注意的是，当单元格数据是数字时，也可能保存了日期和时间数据，即使用OLE自动化标准时间格式的日期和时间数据，此时使用单元格是否定义了日期和时间格式来判断。最终，从Excel文件中读取的数据会以DataTable对象返回。
下面的代码，会读取d:\user.xlsx文件的数据，并绑定到GridView控件。
| C# |
using System;
using System.Data;
public partial class Test : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable tbl= tExcel.ReadAll(@"d:\user.xlsx");
grd1.DataSource = tbl;
grd1.DataBind();
}
}
|