C#读取excel文件，并生成json
这次介绍两种方法，第一种是安装AccessDatabaseEngine，第二种是利用Npoi读取excel
一、第一种利用AccessDatabaseEngine进行读取excel文件
1.安装AccessDatabaseEngine
2.根据Excel文件获取所有的Sheet名称，获取每一个sheet的内容组装dataTable
（1）根据Excel文件获取所有的sheet名称
public List<string> GetExcelSheetNames(string filePath) { OleDbConnection connection = null; System.Data.DataTable dt = null; try { String connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=2;'", filePath); connection = new OleDbConnection(connectionString); connection.Open(); dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return new List<string>(); } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString().Split('$')[0]; i++; } return excelSheets.Distinct().ToList(); } catch (Exception ex) { return new List<string>(); } finally { if (connection != null) { connection.Close(); connection.Dispose(); } if (dt != null) { dt.Dispose(); } } }
（2）获取每一个Sheet的内容组装dataTable
public DataTable GetExcelContent(String filePath, string sheetName) { if (sheetName == "_xlnm#_FilterDatabase") return null; DataSet dateSet = new DataSet(); String connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=NO;IMEX=2;'", filePath); String commandString = string.Format("SELECT * FROM [{0}$]", sheetName); using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); using (OleDbCommand command = new OleDbCommand(commandString, connection)) { OleDbCommand objCmd = new OleDbCommand(commandString, connection); OleDbDataAdapter myData = new OleDbDataAdapter(commandString, connection); myData.Fill(dateSet, sheetName); DataTable table = dateSet.Tables[sheetName]; for (int i = 0; i < table.Rows[0].ItemArray.Length; i++) { var cloumnName = table.Rows[0].ItemArray[i].ToString(); if (!string.IsNullOrEmpty(cloumnName)) table.Columns[i].ColumnName = cloumnName; } table.Rows.RemoveAt(0); return table; } } }
（3）table转json
public object ExcelToJson(string filePath) { string localPath = Server.MapPath(filePath); List<string> tableNames = GetExcelSheetNames(localPath); var json = new JObject(); tableNames.ForEach(tableName => { var table = new JArray() as dynamic; DataTable dataTable = GetExcelContent(localPath, tableName); foreach (DataRow dataRow in dataTable.Rows) { dynamic row = new JObject(); foreach (DataColumn column in dataTable.Columns) { row.Add(column.ColumnName, dataRow[column.ColumnName].ToString()); } table.Add(row); } json.Add(tableName, table); }); return json.ToString(); }
最终生成的字符串:
二、利用NPOI读取excel
1.将excel文件中的内容读取出来，存放到DataSet中
#region 将Excel中的内容转换成DataSet /// <summary> /// 将Excel中的内容转换成DataSet /// </summary> /// <param name="filePath">路径</param> /// <param name="excelHeader">第一行的文本</param> /// <returns></returns> public static DataSet ImportExcelToDataSet(string filePath,List<string> excelHead) { DataSet ds = new DataSet(); IWorkbook workbook; string fileExt = Path.GetExtension(filePath).ToLower(); try { using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs);//2007之后版本的excel } else { workbook = new HSSFWorkbook(fs);//2003版本的excel } for (int a = 0, b = workbook.NumberOfSheets; a < b; a++) { //获取读取的Sheet表的索引 ISheet sheet = workbook.GetSheetAt(a); DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(sheet.FirstRowNum); int cellCount = headerRow.LastCellNum; //将第一行的文本作为列名 for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column; object obj = GetValueType(headerRow.GetCell(i)); if (obj == null || obj.ToString() == string.Empty) { column = new DataColumn("Columns" + i.ToString()); }
else{
column = new DataColumn(GetType(obj.ToString()));
}
table.Columns.Add(column); } //读取第一行下面的数据,将他们作为数据行存储 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "") { // 如果遇到第一个空行，跳出本次循环，继续向下读取 continue; } DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dataRow[j] = row.GetCell(j).ToString(); } } table.Rows.Add(dataRow); } ds.Tables.Add(table); } workbook = null; return ds; } } catch (Exception ex) { return ds; } } #endregion
注意:这是获取单元格类型的方法
#region 获取单元格类型 /// <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: return cell.NumericCellValue; case CellType.String: //STRING: return cell.StringCellValue; case CellType.Error: //ERROR: return cell.ErrorCellValue; case CellType.Formula: //FORMULA: default: return "=" + cell.CellFormula; } } #endregion
2.将DataTable转换成对应的list对象
#region DataTable内容转成List /// <summary> /// 将Excel中的内容转换成List /// </summary> /// <param name="filePath">文件路径</param> /// <returns></returns> public static List<CompanyMobileViewModel> CompanyList(string filePath) { List<CompanyMobileViewModel> mobileList = new List<CompanyMobileViewModel>(); try { //获取excel中的内容 var excelData = ImportExcelToDataSet(filePath,SetPhoneHeader()); //遍历DataSet if (excelData.Tables.Count < 1) { return mobileList; } foreach (DataTable dt in excelData.Tables) { foreach (DataRow dr in dt.Rows) { for(var i=0;i<dr.ItemArray.Length;i++) { //验证是否包含特殊字符 if (dr.ItemArray[i].ToString() != "" && GBCustomsHelper.ValidateSymbol(dr.ItemArray[i].ToString()) == true) { mobileList.Add(new CompanyMobileViewModel() { CompanyID = "js", }); return mobileList; } } mobileList.Add(new CompanyMobileViewModel() { CompanyID = dr.ItemArray[0].ToString(), MobileBusiness = dr.ItemArray[1].ToString(), MobileStatutory = dr.ItemArray[2].ToString(), State = 0, }); } } return mobileList;//然后再用一个方法接收这个返回值，这样excel的内容就读取出来了 } catch (Exception ex) { return mobileList; } } #endregion
3.将list对象转换成json，传递到前端
#region 将上传的excel中的内容转换成json /// <summary> /// 将上传的excel中的内容转换成json /// </summary> /// <param name="filePath"></param> /// <returns></returns> public object ExcelToJson(string filePath) { //数据总表 List<CompanyMobileViewModel> mobileView = CompanyList(filePath);
var jsonData=new{Rows=mobileView，Total=mobileView.Count()};
return Json(jsonData,JsonRequestBehavior.AllowGet);
}
对于这两种方法，个人感觉第二种利用NPOI读取excel更方便，不用在安装软件，省去很多的麻烦
本文参考：https://blog.csdn.net/xiaoxiao520c/article/details/77962326
整理之后，留着以后复习用的，如有问题，请留言
