/// <summary>
/// datatable 转List<object>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="table"></param>
/// <returns></returns>
public static List <XMLObject> DataTableToList(System.Data.DataTable table)
{
List <XMLObject> ts = new List <XMLObject>(); // 定义集合
Type type = typeof(XMLObject); // 获得此模型的类型
string tempName = "";
foreach (System.Data.DataRow dr in table.Rows)
{
XMLObject t = new XMLObject();
System.Reflection.PropertyInfo[] propertys = t.GetType().GetProperties();// 获得此模型的公共属性
foreach (System.Reflection.PropertyInfo pi in propertys)
{
tempName = pi.Name;
if (table.Columns.Contains(tempName))
{
if (!pi.CanWrite)
{
continue;
}
object value = dr[tempName];
if (value != DBNull.Value)
{
pi.SetValue(t, value, null);
}
}
}
ts.Add(t);
}
return(ts);
}
/// <summary>
/// 将Excel文件生成无数个xml对象
/// </summary>
/// <param name="FilePath">excel文件的路径</param>
/// <returns></returns>
public List <XMLObject> ExcelToXMLObject(String FilePath)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
try
{
object missing = System.Reflection.Missing.Value;
excel.Visible = false;
excel.UserControl = true;
// 以只读的形式打开EXCEL文件
Workbook wb = excel.Application.Workbooks.Open(FilePath, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
//取得第一个工作薄
Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1);
//取得总记录行数 (包括标题列)
int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数
int columnsint = ws.UsedRange.Cells.Columns.Count; //得到列数
//取得数据范围区域 (不包括标题列)
XMLObject xmlObject = new XMLObject();
List <XMLObject> xmlObjectList = new List <XMLObject>();
System.Data.DataTable dataTable = new System.Data.DataTable();
Type entityType = xmlObject.GetType();
System.Reflection.PropertyInfo[] entityProperties = entityType.GetProperties();
for (int i = 0; i < entityProperties.Length; i++)
{
dataTable.Columns.Add(entityProperties[i].Name);
}
for (int i = 2; i <= rowsint; i++) //除去标题行开始，excel从1开始
{
Range xml = ws.Cells.Range[excel.Cells[i, 1], excel.Cells[i, columnsint]]; //一行excel
object[,] objData = new object[1, xml.Count];
objData = xml.Value2; //二维数据
object[] entityValues = new object[entityProperties.Length];
dataTable.Rows.Add();
for (int j = 0; j < entityProperties.Length; j++)
{
dataTable.Rows[i - 2][j] = objData[1, j + 1] == null ? "" : objData[1, j + 1];//datatable从0,0开始
}
}
return(DataTableToList(dataTable));
}
catch (Exception ex) {
ErrorLog.RecordExceptionToFile(ex, FilePath, "excel 文件 转 object时");
MessageBox.Show("excel文件读取数据时异常，请查看错误日志ErrorLog", "确定");
return(null);
}
finally
{
excel.Quit();
excel = null;
GC.Collect();
}
}