背景

最近在项目中有一个导入Excel的功能，下文中将介绍如何在C#项目中导入Excel文件，以及实现此功能的宏观思路。

宏观思路

因为项目中的导入功能是用户根据自己的需要去导入Excel文件，那么对于用户上传的格式是有要求的。所以需要让用户先下载我们提供的模板，然后用户可以去编辑模板，再进行上传。

下载模板

下载功能是的数据类型是使用的DataTable，也就是我们在DataTable中为用户提供列的名称放在一个DataTable中，让用户去下载。

具体代码实现

通过我们在代码中设置列的标题（或者让用户可配置），已list集合的方式传入此方法中

 /// /// 定义导入数据的模板/// 通过for循环给空白的DataTable添加列名称/// /// 模板的列的集合/// public DataTable ImportTemplate(List 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); //导出xlsbreak;case 2:ExportTxt(FileSavePath, dtSource); //导出txtbreak;case 3:ExportPDF(FileSavePath, dtSource); //导出pdfbreak;//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{/// /// 从Excel读取数据，只支持单表/// /// 文件路径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);//构建DataTableIRow 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 itemArray = new List();for (int j = 0; j 文件路径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 = 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 itemArray = new List();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;}}/// /// 将DataTable数据导入到excel中/// /// 要导入的数据/// DataTable的列名是否要导入/// 要导入的excel的sheet的名称/// 导出的文件途径/// 导入数据行数(包含列名那一行)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); //写入到excelreturn count;}catch (Exception ex){Console.WriteLine("Exception: " + ex.Message);return -1;}}}/// /// 将DataSet数据导入到excel中 每个datatable一个sheet,sheet名为datatable名/// /// 要导入的数据/// DataTable的列名是否要导入/// 导出的文件途径public static bool DataTableToExcel(DataSet ds, string fileName, bool isColumnWritten = true){if (ds == null || ds.Tables.Count  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 
 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;}/// /// 获取单元格类型/// /// /// 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 removelist = new List();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 ifelseif (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;}
相关文章
Copyright ©  maxssl.com 版权所有 浙ICP备2022011180号