背景
最近在项目中有一个导入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; }
