C#高效導出Excel(IList轉DataTable,DataSet)

微軟的Excel操做類導出Excel會很慢，此方法簡單的把表中內容以字符串的形式寫入到Excel中，用到的一個技巧就是"\t".正則表達式

C#中的\t至關於Tab鍵，寫入到Excel中時就是一列一列中寫入。ui

#region 導出Excel
public void WriteExcel(DataTable ds, string path)
{
long totalCount = ds.Rows.Count;
Thread.Sleep(1000);
long rowRead = 0;
float percent = 0;字符串

StreamWriter sw = new StreamWriter(path, false, Encoding.GetEncoding("gb2312"));
StringBuilder sb = new StringBuilder();
for (int k = 0; k < ds.Columns.Count; k++)
{
sb.Append(ds.Columns[k].ColumnName.ToString() + "\t");
}
sb.Append(Environment.NewLine);
for (int i = 0; i < ds.Rows.Count; i++)
{
for (int j = 0; j < ds.Columns.Count; j++)
{
sb.Append(ds.Rows[i][j].ToString() + "\t");
}
sb.Append(Environment.NewLine);
}
sw.Write(sb.ToString());
sw.Flush();
sw.Close();
}
public DataTable IListOut(IList<Users> list)
{
DataTable dt = new DataTable();cmd

//此處遍歷IList的結構並創建一樣的DataTable
System.Reflection.PropertyInfo[] p = list[0].GetType().GetProperties();
foreach (System.Reflection.PropertyInfo pi in p)
{
dt.Columns.Add(pi.Name, System.Type.GetType(pi.PropertyType.ToString()));
}string

for (int i = 0; i < list.Count; i++)
{
ArrayList TempList = new ArrayList();
//將IList中的一條記錄寫入ArrayList
foreach (System.Reflection.PropertyInfo pi in p)
{
object obj = pi.GetValue(list[i], null);
TempList.Add(obj);
}it

object[] itm = new object[p.Length];
//遍歷ArrayList向object[]裏放數據
for (int j = 0; j < TempList.Count; j++)
{
itm.SetValue(TempList[j], j);
}
//將object[]的內容放入DataTable
dt.LoadDataRow(itm, true);
}
//返回DataTable
return dt;
}io

#endregionemail

public ActionResult Index()
{
string connString = "Data Source=.;database=Test;uid=sa;pwd=123456";
SqlConnection con = new SqlConnection(connString);
con.Open();
string cmdText = "select * from User$";
SqlCommand cmd = new SqlCommand(cmdText,con);
SqlDataAdapter da = new SqlDataAdapter(cmdText,con);
DataSet ds=new DataSet ();
da.Fill(ds);

//正則表達式object

string phone = @"^13[0-9]{9}|020[0-9]{8}|[0-9]{8}$";
//string mail=@"\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*";
//string email = @"(\w*)@(\w*).com";
string mail = @"\s*([\w-]+(\.\w+)*@([\w-]+\.)+\w{2,3})\s*";

Regex rx = new Regex(phone);
Regex rx2 = new Regex(mail);

List<Users> user = new List<Users>();
List<Users> user2 = new List<Users>();
IList<Users> list=ds.ToList<Users>();

bool b=false;
bool b1 = false;
foreach (var item in list)
{
// if (!string.IsNullOrEmpty(item.CellPhone))
// {
// b = Regex.IsMatch(item.CellPhone, phone);
// }
foreach

////b1 = Regex.IsMatch(item.NickName,pattern);
// if (b == true)
// {
// user.Add(item);
// }

if (!string.IsNullOrEmpty(item.CellPhone))
{
if (rx2.IsMatch(item.Mail) && rx.IsMatch(item.CellPhone))
{
// 有電話號碼的
user.Add(item);
}
else
{
//沒有電話號碼的
user2.Add(item);
}

}
else
{
user2.Add(item);
}

}
ViewBag.Users = user;

ViewBag.Users2 = user2;

DataTable dt = IListOut(user); WriteExcel(dt, "d:\\a.xls"); DataTable dt1 = IListOut(user2); WriteExcel(dt1, "d:\\b.xls"); con.Close(); return View(); }