数据到入到EXCEL文件

来源：互联网发布：网络家装行业发展趋势编辑：程序博客网时间：2024/05/21 16:59

//真正的导入数据的类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

using System.Collections;
using System.Data;
using System.Data.OleDb;

namespace mccn.BusinessSystem
{
public class ExcelReadToModleList
{
public List<object> ErrorMessageList { get; set; }

public ExcelReadToModleList()
{

}

public DataSet GetExcel(ExcelModel model)
{

DataSet ds = new DataSet();
string connStr = string.Empty;

try
{
connStr = GetConnString(model, connStr);
using (OleDbConnection conn = new OleDbConnection(connStr))
{

string sql = string.Format("SELECT * FROM [{0}$]", model.ExcelSheetName);

OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
da.Fill(ds);

}
}
catch (Exception ex)
{
throw ex;
}

return ds;
}

public List<T> GetModels<T>(ExcelModel model)
{
List<T> lstT = new List<T>();
DataSet ds = new DataSet();
string connStr = string.Empty;
try
{
connStr = GetConnString(model, connStr);

using (OleDbConnection conn = new OleDbConnection(connStr))
{

string sql = string.Format("SELECT {0} FROM [{1}$]", FormatColumns(model.Columns), model.ExcelSheetName);
//sql = string.Format("SELECT * FROM [{0}$]", model.ExcelSheetName);
OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
da.Fill(ds);
lstT = IntoEnity<T>(ds, model.Columns);
}
}
catch (Exception ex)
{
throw ex;
}
return lstT;
}

/// <summary>
/// 将一组对象导出成EXCEL
/// </summary>
/// <typeparam name="T">要导出对象的类型</typeparam>
/// <param name="objList">一组对象</param>
/// <param name="columnInfo">列名信息</param>
public static string ReportExcel<T>(List<T> objList, Dictionary<string, string> columnInfo)
{

if (columnInfo.Count == 0) { return ""; }
if (objList.Count == 0) { return ""; }
//生成EXCEL的HTML
string excelStr = "";

Type myType = objList[0].GetType();
//根据反射从传递进来的属性名信息得到要显示的属性
List<PropertyInfo> myPro = new List<PropertyInfo>();
foreach (string cName in columnInfo.Keys)
{
PropertyInfo p = myType.GetProperty(cName);
if (p != null)
{
myPro.Add(p);
excelStr += columnInfo[cName] + "\t";
}
}
//如果没有找到可用的属性则结束
if (myPro.Count == 0) { return ""; }
excelStr += "\n";

foreach (T obj in objList)
{
foreach (PropertyInfo p in myPro)
{
excelStr += p.GetValue(obj, null) + "\t";
}
excelStr += "\n";
}

return excelStr;

}

private string GetConnString(ExcelModel model, string connStr)
{
if (float.Parse(model.ExcelVersion.Trim()) < 8)
{
connStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties='Excel {1};HDR=Yes;IMEX=1;'",
model.ExcelPath, model.ExcelVersion);
}
else
{
connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties='Excel {1};HDR=Yes;IMEX=1;'",
model.ExcelPath, model.ExcelVersion);
}
return connStr;
}

private string FormatErrorFields(List<object> ErrorMessageList, ExcelModel model)
{
string Fields = "";

foreach (var item in ErrorMessageList)
{
PropertyInfo[] Ps = item.GetType().GetProperties();
var Property = Ps.First(W => W.Name.ToLower() == model.TableKey.ToLower());
Fields += "'" + Property.GetValue(item, null).ToString() + "',";
}

//Fields = Fields.Substring(0, Fields.Length - 1);
return Fields.Substring(0, Fields.Length - 1);
}

/// <summary>
/// 执行导入成功的信息，剩余错误信息
/// </summary>
/// <param name="model"></param>
public void ExecuteSqlFroExcel(ExcelModel model)
{
string connStr = string.Empty;
string sql = string.Empty;
try
{
connStr = GetConnString(model, connStr);

using (OleDbConnection conn = new OleDbConnection(connStr))
{
if (null != ErrorMessageList) //删除到处成功的记录。
{
string ColKey = string.Empty;

ColKey = model.Columns.First(T => T.Key == model.TableKey).Value;

sql = string.Format("DELETE FROM [{0}$] WHERE {1} not in ({2})", model.ExcelSheetName, ColKey, FormatErrorFields(ErrorMessageList, model));
sql = string.Format("DELETE FROM [{0}$]", model.ExcelSheetName);
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
conn.Close();

}
}

}
catch (Exception err)
{
throw err;
}

}

private List<T> IntoEnity<T>(DataSet ds, Dictionary<string, string> Columns)
{
List<T> listT = new List<T>();
ErrorMessageList = new List<object>();

if (null == ds || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
return null;
int i = 2;//EXCEL从第2行开始读取数据。
foreach (DataRow dr in ds.Tables[0].Rows)
{
int drClear = dr.ItemArray.Where(I => I.ToString() != "").Count();
if (drClear == 0)//空行排除
{
continue;
}
T t = Activator.CreateInstance<T>();

PropertyInfo[] Ps = t.GetType().GetProperties();

foreach (DataColumn dc in ds.Tables[0].Columns)
{

var c = Columns.FirstOrDefault(C => C.Value == dc.ColumnName);
if (string.IsNullOrWhiteSpace(c.Key))
{
continue;
}
foreach (var p in Ps)
{

if (p.Name.ToLower().Equals(c.Key.ToLower()))
{
if (p.Name.ToLower().Equals("borrowamount"))
{

}
try
{
c = DataConvert<T>(dr, t, c, p);
}
catch (Exception err) //出错把错误信息添加到errorMessage
{
var Property = Ps.First(W => W.Name.ToLower() == "remarks");
if (Property.PropertyType.GetGenericArguments().Length == 0)
{
Property.SetValue(t, Convert.ChangeType("第" + i.ToString() + "行出错：" + err.Message, Property.PropertyType), null);
}
else
{
Property.SetValue(t, Convert.ChangeType("第" + i.ToString() + "行出错：" + err.Message, Property.PropertyType.GetGenericArguments()[0]), null);
}

ErrorMessageList.Add(t);
continue;
}
}
}
}

listT.Add(t);
i++;
}

return listT;
}

private KeyValuePair<string, string> DataConvert<T>(DataRow dr, T t, KeyValuePair<string, string> c, PropertyInfo p)
{

if ("性别" == c.Value)
{
p.SetValue(t, Convert.ChangeType(Sex(dr, c.Value), p.PropertyType.GetGenericArguments()[0]), null);
}
else if (p.PropertyType.GetGenericArguments().Length > 0)
{
switch (p.PropertyType.GetGenericArguments()[0].FullName)
{
case "System.Decimal":
string d = dr[c.Value] == null || dr[c.Value].ToString() == "" ? "0" : double.Parse(dr[c.Value].ToString()).ToString("N4");
decimal decTemp = decimal.Parse(d);
p.SetValue(t, decTemp, null);
break;
case "System.DateTime":

var ValueTemp = null == dr[c.Value] || string.IsNullOrWhiteSpace(dr[c.Value].ToString()) ? DateTime.Parse("01/01/0001") : Convert.ChangeType(dr[c.Value], p.PropertyType.GetGenericArguments()[0]);
p.SetValue(t, ValueTemp, null);
break;
case "System.Int32":
double dPut = 0;
int IntTemp = 0;
string varTemp = null == dr[c.Value] || string.IsNullOrWhiteSpace(dr[c.Value].ToString()) ? "0" : dr[c.Value].ToString();

if (Int32.TryParse(varTemp, out IntTemp))
{
p.SetValue(t, IntTemp, null);
break;
}
if (double.TryParse(varTemp, out dPut))
{
IntTemp = Convert.ToInt32(dPut);
}
p.SetValue(t, IntTemp, null);
break;
default:
p.SetValue(t, Convert.ChangeType(dr[c.Value], p.PropertyType.GetGenericArguments()[0]), null);
break;

}

}
else
{
switch (p.PropertyType.FullName)
{
case "System.Decimal":
string d = dr[c.Value] == null || dr[c.Value].ToString() == "" ? "0" : double.Parse(dr[c.Value].ToString()).ToString("N4");
decimal decTemp = decimal.Parse(d);
p.SetValue(t, decTemp, null);
break;
case "System.DateTime":

var ValueTemp = null == dr[c.Value] || string.IsNullOrWhiteSpace(dr[c.Value].ToString()) ? DateTime.Parse("01/01/0001") : Convert.ChangeType(dr[c.Value], p.PropertyType);
p.SetValue(t, ValueTemp, null);
break;
case "System.Int32":

var IntTemp = null == dr[c.Value] || string.IsNullOrWhiteSpace(dr[c.Value].ToString()) ? 0 : Int32.Parse(dr[c.Value].ToString());
p.SetValue(t, IntTemp, null);
break;
default:
p.SetValue(t, Convert.ChangeType(dr[c.Value], p.PropertyType), null);
break;

}
}
return c;
}

private bool Sex(DataRow dr, string Value)
{
bool BlSex = true;
if (dr[Value].ToString() == "女")
{
BlSex = false;
}
return BlSex;
}

private string FormatColumns(Dictionary<string, string> Col)
{
string Columns = "";

foreach (var item in Col)
{
Columns += item.Value + ",";
}

Columns = Columns.Substring(0, Columns.Length - 1);
return Columns;
}

//项目三层架构，导入项目信息使用 2013.7.11 mayy
public List<T> GetModels<T>(ExcelModel model,Dictionary<string, string> Columns)
{
List<T> lstT = new List<T>();
DataSet ds = new DataSet();
string connStr = string.Empty;
try
{
connStr = GetConnString(model, connStr);

using (OleDbConnection conn = new OleDbConnection(connStr))
{

string sql = string.Format("SELECT {0} FROM [{1}$]", FormatColumns(model.Columns), model.ExcelSheetName);
//sql = string.Format("SELECT * FROM [{0}$]", model.ExcelSheetName);
OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
da.Fill(ds);
lstT = IntoEnity<T>(ds, Columns);
}
}
catch (Exception ex)
{
throw ex;
}
return lstT;
}
}

public class ExcelModel
{
public string ExcelSheetName { get; set; }
public Dictionary<string, string> Columns { get; set; }
public string ExcelVersion { get; set; }
public string ExcelPath { get; set; }
public string TableKey { get; set; } //主键
}
}

//导入数据的实例

private void ReadExcel()

{
if (string.IsNullOrWhiteSpace(txt_excelpath)) throw new ArgumentNullException("Excel地址不能为空!");
ExcelModel model;
ExcelReadToModleList help;

model = new ExcelModel();
model.ExcelPath = txt_excelpath;
model.ExcelSheetName = txt_sheetname;
model.ExcelVersion = "8.0";
model.Columns = SetColList();
model.TableKey = "";

help = new ExcelReadToModleList();
int productNum = 0;//商品总个数
DataSet ds = null;
try
{
ds = help.GetExcel(model);
}
catch (Exception ex)
{
MessageBox.Show("请确认要导入的EXCEL表名为:Sheet1!"+ex.Message, "美承业务系统", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}

}

/// <summary>
/// 读取字段说明
/// </summary>
/// <returns></returns>
private Dictionary<string, string> SetColList()
{
Dictionary<string, string> DicCol;
DicCol = new Dictionary<string, string>();
DicCol.Add("FCHRITEMNAME", "商品名称");
DicCol.Add("FCHRITEMSTYLE", "型号");
DicCol.Add("FCHRUNIT", "计量单位");
DicCol.Add("FLOTQUANTITY", "数量");
DicCol.Add("FLOTPRICE", "单价");
DicCol.Add("FLOTAMOUNT", "金额");
return DicCol;
}

0 0