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 = ;

                foreach (DataRow row in dt.Rows)

                {

                    excelSheets[i] = row["TABLE_NAME"].ToString().Split('$')[];

                    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 = ; i < table.Rows[].ItemArray.Length; i++)

                    {

                        var cloumnName = table.Rows[].ItemArray[i].ToString();

                        if (!string.IsNullOrEmpty(cloumnName))

                            table.Columns[i].ColumnName = cloumnName;

                    }

                    table.Rows.RemoveAt();

                    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 = , 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 + ); i <= sheet.LastRowNum; i++)

                        {

                            IRow row = sheet.GetRow(i);

                            if (row == null || row.GetCell() == null || row.GetCell().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 < )

                {

                    return mobileList;

                }

                foreach (DataTable dt in excelData.Tables)

                {

                    foreach (DataRow dr in dt.Rows)

                    {

                        for(var i=;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[].ToString(),

                            MobileBusiness = dr.ItemArray[].ToString(),

                            MobileStatutory = dr.ItemArray[].ToString(),

                            State = ,

                        });

                    }

                }

                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

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

巴特西

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

最新文章

热门文章