Excel动态生成JSON

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

在最近的一个项目中，有大量的数据源来至Excel，转成JSON供前台使用。Excel数据是人工录入的，难免会有错误，所以中间会有逻辑检查。在C#中读取Excel的方式有很多，网上一搜一大堆，这里我也贴出一个ExcelHelper，提供根据Excel文件获取所有Sheet名称和获取Sheet内容两个方法。使用的时候记得注册AccessDatabaseEngine，该驱动有64位、32位两个版本，请根据自己的环境选择正确的版本。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;namespace Math.Library.Helper
{
public class ExcelHelper
{
public static 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=1;'", 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;
}
}
} public static 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=1;'", 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)
{
LogHelper.Logger.Error(ex);
return new List<string>();
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
} }
}

假如我的Excel数据如下，有班级和学生两张表（实际上有上百个Sheet，这里只做演示之用）。

当然我们不可能为每一个Sheet都对应做一个数据Model，不仅繁琐，还很容易出错。这时我想到了Newtonsoft.Json，Newtonsoft.Json是.NET下开源的JSON格式序列化和反序列化的类库。其中Newtonsoft.Json.Linq提供了对LINQ支持,支持动态对象、数组的序列化。

将整个Excel转换为一个JSON文件，每一个Sheet Name作为Key，Content就是Value，Value以数组形式存在，最终得到数据格式如下：

对应的代码如下，JArray和JObject是Newtonsoft.Json中的对象，支持动态属性和方法，表名和列名就是这样插入JSON中的。

class Program
{
static void ExcelToJson()
{
List<string> tableNames = ExcelHelper.GetExcelSheetNames("test.xlsx");
var json = new JObject();
tableNames.ForEach(tableName =>
{
var table = new JArray() as dynamic;
DataTable dataTable = ExcelHelper.GetExcelContent("test.xlsx", 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);
});
Console.WriteLine(json.ToString());
Console.WriteLine(json.ToString(Formatting.None));
} static void Main(string[] args)
{
ExcelToJson();
}
}