public bool ExportExcel(DataTable tb, string path, string tbName)
{
//excel 2003格式
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
//Excel 2007格式
//string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 Xml;";
try
{
using (OleDbConnection con = new OleDbConnection(connString))
{
con.Open();
StringBuilder strSQL = new StringBuilder();
strSQL.Append("CREATE TABLE ").Append("[" + tbName + "]");
strSQL.Append("(");
for (int i = ; i < tb.Columns.Count; i++)
{
switch (tb.Columns[i].ColumnName)
{
case "ID":
tb.Columns[i].ColumnName = "编号";
break;
case "name":
tb.Columns[i].ColumnName = "姓名";
break;
case "phone":
tb.Columns[i].ColumnName = "电话";
break;
case "address":
tb.Columns[i].ColumnName = "地址";
break;
case "scheduleLength":
tb.Columns[i].ColumnName = "预定下单长度";
break;
case "welfare":
tb.Columns[i].ColumnName = "福利的类型";
break;
case "commodityType":
tb.Columns[i].ColumnName = "抢购商品类型";
break;
case "discount":
tb.Columns[i].ColumnName = "折扣";
break;
case "time":
tb.Columns[i].ColumnName = "日期";
break;
case "remarks":
tb.Columns[i].ColumnName = "备注";
break; }
strSQL.Append("[" + tb.Columns[i].ColumnName + "] text,");
}
strSQL = strSQL.Remove(strSQL.Length - , );
strSQL.Append(")"); OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con);
cmd.ExecuteNonQuery();
string column = string.Empty;
for (int i = ; i < tb.Rows.Count; i++)
{
strSQL.Clear();
StringBuilder strfield = new StringBuilder();
StringBuilder strvalue = new StringBuilder();
for (int j = ; j < tb.Columns.Count; j++)
{
switch (tb.Columns[j].ColumnName)
{
case "ID":
tb.Columns[j].ColumnName = "编号";
break;
case "name":
tb.Columns[j].ColumnName = "姓名";
break;
case "phone":
tb.Columns[j].ColumnName = "电话";
break;
case "address":
tb.Columns[j].ColumnName = "地址";
break;
case "scheduleLength":
tb.Columns[j].ColumnName = "预定下单长度";
break;
case "welfare":
tb.Columns[j].ColumnName = "福利的类型";
break;
case "commodityType":
tb.Columns[j].ColumnName = "抢购商品类型";
break;
case "discount":
tb.Columns[j].ColumnName = "折扣";
break;
case "time":
tb.Columns[j].ColumnName = "日期";
break;
case "remarks":
tb.Columns[j].ColumnName = "备注";
break; }
strfield.Append("[" + tb.Columns[j].ColumnName + "]");
strvalue.Append("'" + tb.Rows[i][j].ToString() + "'");
if (j != tb.Columns.Count - )
{
strfield.Append(",");
strvalue.Append(",");
}
else
{
}
}
cmd.CommandText = strSQL.Append(" insert into [" + tbName + "]( ")
.Append(strfield.ToString())
.Append(") values (").Append(strvalue).Append(")").ToString();
cmd.ExecuteNonQuery();
}
con.Close();
}
return true;
}
catch (Exception ex)
{
return false;
}
}