大家好,又见面了,我是你们的朋友全栈君。
//如果要支持xlsx格式表格,请在本机电脑安装这个
//http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe
参考案例:https://www.cnblogs.com/fengxiang/p/3551621.html
使用的时候记得注册AccessDatabaseEngine,该驱动有64位、32位两个版本,请根据自己的环境选择正确的版本。
需求:有大量的数据源来自Excel,需要转成JSON供程序读取使用
假如我的Excel数据如下:
这里我们用到了Newtonsoft.Json,Newtonsoft.Json是.NET下开源的JSON格式序列化和反序列化的类库。其中Newtonsoft.Json.Linq提供了对LINQ支持,支持动态对象、数组的序列化。JArray和JObject是Newtonsoft.Json中的对象,支持动态属性和方法,表名和列名就是这样插入JSON中的。
将整个Excel转换为一个JSON文件,每一个Sheet Name作为Key,Content就是Value,Value以数组形式存在,最终得到数据格式如下:
为了减少前后端传输数据的流量,可以使用ToString(Formatting.None),这样生成出来的数据就没有格式了。
代码如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
namespace ExcelTool
{
public class ExcelHelper
{
/// <summary>
/// 获取excel的DataTable
/// </summary>
/// <param name="filePath"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
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 = 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;
}
}
}
/// <summary>
/// 获取excel文件里面的所有的工作表名称
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
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 = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString().Split('$')[0];
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();
}
}
}
/// <summary>
/// excel转换为json
/// </summary>
/// <param name="filePath"></param>
public static void ExcelToJson(string filePath)
{
List<string> tableNames = ExcelHelper.GetExcelSheetNames(filePath);
var json = new JObject();
tableNames.ForEach(tableName =>
{
var table = new JArray() as dynamic;
DataTable dataTable = ExcelHelper.GetExcelContent(filePath, 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));
}
}
}
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/132085.html原文链接:https://javaforall.cn