发布：2024/1/26 19:27:51作者：管理员 来源：本站 浏览次数：763
背景
最近在项目中有一个导入Excel的功能，下文中将介绍如何在C#项目中导入Excel文件，以及实现此功能的宏观思路。
宏观思路
因为项目中的导入功能是用户根据自己的需要去导入Excel文件，那么对于用户上传的格式是有要求的。所以需要让用户先下载我们提供的模板，然后用户可以去编辑模板，再进行上传。
下载模板
下载功能是的数据类型是使用的DataTable，也就是我们在DataTable中为用户提供列的名称放在一个DataTable中，让用户去下载。
具体代码实现
通过我们在代码中设置列的标题（或者让用户可配置），已list集合的方式传入此方法中
/// <summary>
/// 定义导入数据的模板
/// 通过for循环给空白的DataTable添加列名称
/// </summary>
/// <param name="ColumnNames">模板的列的集合</param>
/// <returns></returns>
public DataTable ImportTemplate(List<string> ColumnNames)
{
DataTable template = new DataTable();
DataRow dr = template.NewRow() ;
template.Rows.Add(dr);
for(int i = 0; i < ColumnNames.Count; i++)
{
template.Columns.Add(ColumnNames[i]);
}
return template;
}
导出文件的具体代码
public void Export(DataTable dtSource,string FileName)
{
try {
SaveFileDialog fileDialog = new SaveFileDialog();
fileDialog.Filter = "Excel|*.xls|TXT|*.txt|PDF|*.pdf";
fileDialog.FileName = FileName+"-"+DateTime.Now.ToString("D");
if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
string FileSavePath = fileDialog.FileName;
switch (fileDialog.FilterIndex)
{
case 1:
ExportXls(FileSavePath, dtSource); //导出xls
break;
case 2:
ExportTxt(FileSavePath, dtSource); //导出txt
break;
case 3:
ExportPDF(FileSavePath, dtSource); //导出pdf
break;
//case 4:
// ExportDocx(FileSavePath, dtSource); //导出docx
// break;
}
MessageBox.Show("文件 "+ FileSavePath + " 导出成功");
}
catch(Exception e){
MessageBox.Show("导出文件失败，请稍后重新尝试"+ e);
}
}
有了以上两个方法之后，我们可以在某事件中去触发此这些方法，然后下载文件到用户的本地。
导入文件
上文中已经把导入的下载模板的工作完成，接下来是需要用把编辑好的Excel文件上传到我们程序并同步到数据库中。
上传Excel这里使用的NPOI，在“管理NuGet程序包”中，搜索NPOI找到，下载并安装。
这里可能出现问题是
解决办法：先安装“SixLabors.Fonts”，需要注意的是：如果直接搜索的话可能找不到，需要把“包括预发行版”勾选上
安装好之后，再去下载NPOI。
在项目中添加类ExcelHelper
internal class ExcelHelper
{
/// <summary>
/// 从Excel读取数据，只支持单表
/// </summary>
/// <param name="FilePath">文件路径</param>
public static DataTable ReadFromExcel(string FilePath)
{
IWorkbook wk = null;
string extension = System.IO.Path.GetExtension(FilePath); //获取扩展名
try
{
using (FileStream fs = File.OpenRead(FilePath))
{
if (extension.Equals(".xls")) //2003
{
wk = new HSSFWorkbook(fs);
}
else //2007以上
{
wk = new XSSFWorkbook(fs);
}
}
//读取当前表数据
ISheet sheet = wk.GetSheetAt(0);
//构建DataTable
IRow row = sheet.GetRow(0);
DataTable result = BuildDataTable(row);
if (result != null)
{
if (sheet.LastRowNum >= 1)
{
for (int i = 1; i < sheet.LastRowNum + 1; i++)
{
IRow temp_row = sheet.GetRow(i);
if (temp_row == null) { continue; }//2019-01-14 修复 行为空时会出错
List<object> itemArray = new List<object>();
for (int j = 0; j < result.Columns.Count; j++)//解决Excel超出DataTable列问题 lqwvje20181027
{
//itemArray.Add(temp_row.GetCell(j) == null ? string.Empty : temp_row.GetCell(j).ToString());
itemArray.Add(GetValueType(temp_row.GetCell(j)));//解决 导入Excel 时间格式问题 lqwvje 20180904
}
result.Rows.Add(itemArray.ToArray());
}
}
}
return result;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 从Excel读取数据，支持多表
/// </summary>
/// <param name="FilePath">文件路径</param>
public static DataSet ReadFromExcels(string FilePath)
{
DataSet ds = new DataSet();
IWorkbook wk = null;
string extension = System.IO.Path.GetExtension(FilePath); //获取扩展名
try
{
using (FileStream fs = File.OpenRead(FilePath))
{
if (extension.Equals(".xls")) //2003
{
wk = new HSSFWorkbook(fs);
}
else //2007以上
{
wk = new XSSFWorkbook(fs);
}
}
int SheetCount = wk.NumberOfSheets;//获取表的数量
if (SheetCount < 1)
{
return ds;
}
for (int s = 0; s < SheetCount; s++)
{
//读取当前表数据
ISheet sheet = wk.GetSheetAt(s);
//构建DataTable
IRow row = sheet.GetRow(0);
if (row == null) { continue; }
DataTable tempDT = BuildDataTable(row);
tempDT.TableName = wk.GetSheetName(s);
if (tempDT != null)
{
if (sheet.LastRowNum >= 1)
{
for (int i = 1; i < sheet.LastRowNum + 1; i++)
{
IRow temp_row = sheet.GetRow(i);
if (temp_row == null) { continue; }//2019-01-14 修复 行为空时会出错
List<object> itemArray = new List<object>();
for (int j = 0; j < tempDT.Columns.Count; j++)//解决Excel超出DataTable列问题 lqwvje20181027
{
itemArray.Add(GetValueType(temp_row.GetCell(j)));//解决 导入Excel 时间格式问题 lqwvje 20180904
}
tempDT.Rows.Add(itemArray.ToArray());
}
}
ds.Tables.Add(tempDT);
}
}
return ds;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 将DataTable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <param name="fileName">导出的文件途径</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public static int DataTableToExcel(DataTable data, string sheetName, string fileName, bool isColumnWritten = true)
{
IWorkbook workbook = null;
using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook();
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook();
}
if (workbook == null) { return -1; }
try
{
ISheet sheet = workbook.CreateSheet(sheetName);
int count = 0;
if (isColumnWritten) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (int j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
count = 1;
}
for (int i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (int j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
count++;
}
workbook.Write(fs,true); //写入到excel
return count;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return -1;
}
}
}
/// <summary>
/// 将DataSet数据导入到excel中 每个datatable一个sheet,sheet名为datatable名
/// </summary>
/// <param name="ds">要导入的数据</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <param name="fileName">导出的文件途径</param>
public static bool DataTableToExcel(DataSet ds, string fileName, bool isColumnWritten = true)
{
if (ds == null || ds.Tables.Count < 1)
{
return false;
}
IWorkbook workbook = null;
using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook();
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook();
}
if (workbook == null) { return false; }
try
{
foreach (DataTable dt in ds.Tables)
{
ISheet sheet = workbook.CreateSheet(dt.TableName);
if (isColumnWritten) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (int j = 0; j < dt.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName);
}
}
for (int i = 0; i < dt.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(isColumnWritten ? i + 1 : i);
for (int j = 0; j < dt.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
}
workbook.Write(fs,true); //写入到excel
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return false;
}
}
return true;
}
private static DataTable BuildDataTable(IRow Row)
{
DataTable result = null;
if (Row.Cells.Count > 0)
{
result = new DataTable();
for (int i = 0; i < Row.LastCellNum; i++)
{
if (Row.GetCell(i) != null)
{
result.Columns.Add(Row.GetCell(i).ToString());
}
}
}
return result;
}
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue;
}
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
cell.SetCellType(CellType.String);
return cell.StringCellValue;
default:
return "=" + cell.CellFormula;
}
}
}
在winform的中某触发事件中代码，调用刚刚的ExcelHelper对象，（这里还可以返回一个DataTable对象）
private void ExcelFile()
{
OpenFileDialog openFile = new OpenFileDialog();
if (openFile.ShowDialog() == DialogResult.OK)
{
string filePath = openFile.FileName;
//获取本地的Excel文件
DataTable excelDt = ExcelHelper.ReadFromExcel(filePath);
}
}
需要注意的是，在导入的时候如果有空白行的话，会把空白行也插入到数据库中，所以在读取本地的Excel数据之后，需要把空白行也去除掉。
List<DataRow> removelist = new List<DataRow>();
for (int i = 0; i < excelDt.Rows.Count; i++)
{
bool IsNull = true;
for (int j = 0; j < excelDt.Columns.Count; j++)
{
if (!string.IsNullOrEmpty(excelDt.Rows[i][j].ToString().Trim()))
{
IsNull = false;
}
}
if (IsNull)
{
removelist.Add(excelDt.Rows[i]);
}
}
for (int i = 0; i < removelist.Count; i++)
{
excelDt.Rows.Remove(removelist[i]);
}
到这里以上的操作步骤是获取本地的Excel数据了，那么接下来就可以去写入数据库中，需要注意的是：读取的数据，需要和数据库中的字段一一对应，也就是位置、名称都要对应上才行。
开发中遇到的问题：
1、用户需要提供的数据和数据库少于数据库中的字段，比如：用户需要提供姓名、手机号这两个字段，但是数据库中还需要有地址这个字段，那怎么办呢？我们可以在代码中给把这些信息补全。再写入数据库中。
示例代码
ProductTable.Columns.Add("creator");
ProductTable.Columns["creator"].SetOrdinal(26);//在第26列插入字段
ProductTable.Columns.Add("create_time");
ProductTable.Columns["create_time"].SetOrdinal(27);
2、在写数据库时，遇到数据类型不匹配。因为从本地获取的Excel数据基本上是string类型，那么数据库中有写字段是bool类型或者其他，如果插库的话就会报错，我这里解决这个问题的思路大概是在代码中把类型给转换一下，这样插入数据库时就不会出错了。
解决思路：比较笨的办法是把获取本地的数据（存放到了DataTable对象中）克隆出来一份，在克隆出来的那份中进行数据转换，因为在原来的数据不能进行转换，而且这里克隆不会把原来的数据进行克隆，所以在完成数据类型转换之后，还要把旧表中的数据同步到克隆出来的表中。
示例代码：
表克隆：
DataTable NewTable = new DataTable();
NewTable = ProductTable.Clone();//把原来的表进行克隆
修改类型：
//把新表中的表结构进行修改数据类型，和数据库中表字段进行保持一致
foreach (DataColumn col in NewTable.Columns)
{
#region ifelse
if (col.ColumnName == "conveyor_sel")
{
col.DataType = typeof(int);
}
else if (col.ColumnName == "p_length")
{
col.DataType = typeof(int);
}
else if (col.ColumnName == "p_width")
{
col.DataType = typeof(int);
}
}
旧表中的数据同步到新表中
foreach (DataRow item in ProductTable.Rows)
{
DataRow NewDtRow = NewTable.NewRow();
//获取对应行的产品代码的值
data = item.ItemArray[0];
NewDtRow["recipe"] = item["recipe"].ToString();
NewDtRow["p_name"] = item["p_name"].ToString();
NewDtRow["cust_name"] = item["cust_name"].ToString();
NewDtRow["cust_abbr"] = item["cust_abbr"].ToString();
NewDtRow["cust_field"] = item["cust_field"].ToString();
}
3、读取到本地Excel表中的的列标题是中文，但是数据库的形式是英文。这里也是用代码转换
示例代码：
ProductTable.Columns["产品代码"].ColumnName = "recipe";
ProductTable.Columns["产品名称"].ColumnName = "p_name";
使用SqlBulkCopy，把DataTable的数据写入数据库
这里用的EF框架。
public long AddDataTable(DataTable dt, String TableName)
{
PmsTestEntities db = new PmsTestEntities();
SqlBulkCopy copy = new SqlBulkCopy(db.Database.Connection.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction);//把本地文件复制
copy.BatchSize = 100;//每次传输行数
copy.NotifyAfter = dt.Rows.Count;//传输多少行后提示
copy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
copy.DestinationTableName = TableName;//表名
copy.WriteToServer(dt);
return DataIncoming;
}
//显示传入了多少的数据
private void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
long count = e.RowsCopied;
DataIncoming = count;
}
