C#读取excel文件，并生成json
2021-06-26 22:05
标签：split ret ref 技术 detail pat item dbconnect 读取 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名称 （2）获取每一个Sheet的内容组装dataTable （3）table转json 最终生成的字符串: 本文参考：https://blog.csdn.net/xiaoxiao520c/article/details/77962326 整理之后，留着以后复习用的，如有问题，请留言 C#读取excel文件，并生成json 标签：split ret ref 技术 detail pat item dbconnect 读取 原文地址：https://www.cnblogs.com/sas1231/p/10095479.html public Liststring> 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 Liststring>();
}
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 Liststring>();
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
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 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;
}
}
}
public object ExcelToJson(string filePath)
{
string localPath = Server.MapPath(filePath);
//临时测试
// string localPath = @"C:\Users\nilaijie\Desktop\OperationExcel\OperationExcel\OperationExcel\File\号码簿.xlsx";
Liststring> 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();
}
