C#读取excel文件，并生成json

这次介绍两种方法，第一种是安装AccessDatabaseEngine，第二种是利用Npoi读取excel

一、第一种利用AccessDatabaseEngine进行读取excel文件

1.安装AccessDatabaseEngine

链接地址:http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe

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

整理之后，留着以后复习用的，如有问题，请留言