读取表格数据转换为json/字典
需要的插件:
Excel.dll,
ICSharpCode.SharpZipLib.dll,
Newtonsoft.json.dll
读取出的数据类
public class ExcelUtility
{
/// <summary>
/// 表格数据集合
/// </summary>
private DataSet _ResultSet;
private string _excelName;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="excelFilePath">Excel file.</param>
public ExcelUtility(string excelFilePath)
{
FileStream mStream = File.Open(excelFilePath, FileMode.Open, FileAccess.Read);
IExcelDataReader mExcelReader = ExcelReaderFactory.CreateOpenXmlReader(mStream);
_ResultSet = mExcelReader.AsDataSet();
var checkNumber = excelFilePath.LastIndexOf('/');
string temp = excelFilePath.Substring(checkNumber+1);
_excelName = temp.Split('.')[0];
}
/// <summary>
/// 把一个Excel表格转换为一个实体类的List集合
/// </summary>
/// <typeparam name="T">要转换的类型</typeparam>
/// <returns></returns>
public List<T> ConvertToList<T>()
{
//判断Excel文件中是否存在数据表
if (_ResultSet.Tables.Count < 1)
return null;
//默认读取第一个数据表
System.Data.DataTable mSheet = _ResultSet.Tables[0];
//判断数据表内是否存在数据
if (mSheet.Rows.Count < 1)
return null;
//读取数据表行数和列数
int rowCount = mSheet.Rows.Count;
int colCount = mSheet.Columns.Count;
//准备一个列表以保存全部数据
List<T> list = new List<T>();
//读取数据
for (int i = 2; i < rowCount; i++)
{
//创建实例
Type t = typeof(T);