DataTableToExcel
public static string CreateExcel(DataTable dt, string FileName, string path, string columns) {
string excelPath = path + "Code\\ZipExcel\\" + FileName + "_" + DateTime.Now.ToString("yyyyMMddHHmmsss") + ".xlsx";
string connString = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + excelPath + ";Extended Properties='Excel 12.0 xml; HDR=Yes; IMEX=0;'"; if (dt == null) { return ""; }
int rows = dt.Rows.Count; int cols = dt.Columns.Count; StringBuilder sb;
if (rows == 0) { return ""; }
sb = new StringBuilder();
//生成创建表的脚本 sb.Append("CREATE TABLE "); sb.Append("[ReportData] " + " ( ");
for (int i = 0; i < cols; i++) { if (i < cols - 1) if (columns.IndexOf(dt.Columns[i].ColumnName) > -1) { sb.Append(string.Format(" [{0}] Numeric(18,2),", dt.Columns[i].ColumnName)); } else { sb.Append(string.Format(" [{0}] Varchar,", dt.Columns[i].ColumnName)); }
else if (columns.IndexOf(dt.Columns[i].ColumnName) > -1) { sb.Append(string.Format(" [{0}] Numeric(18,2))", dt.Columns[i].ColumnName)); } else { sb.Append(string.Format(" [{0}] Varchar )", dt.Columns[i].ColumnName)); }
}
OleDbConnection objConn = new OleDbConnection(connString); OleDbCommand objCmd = new OleDbCommand(); objCmd.Connection = objConn;
objCmd.CommandText = sb.ToString();
//try //{ objConn.Open(); objCmd.ExecuteNonQuery();
//} //catch (Exception e) //{ // return "";
//}
//生成插入数据脚本#region 生成插入数据脚本 sb.Remove(0, sb.Length); sb.Append("INSERT INTO "); sb.Append("[ReportData]" + " ( ");
for (int i = 0; i < cols; i++) { if (i < cols - 1) sb.Append("[" + dt.Columns[i].ColumnName + "]" + ","); else sb.Append("[" + dt.Columns[i].ColumnName + "]" + ") values ("); }
for (int i = 0; i < cols; i++) { if (i < cols - 1) sb.Append("@" + "para" + i.ToString() + ",");
else sb.Append("@" + "para" + i.ToString() + ")"); } //#endregion
//建立插入动作的Command objCmd.CommandText = sb.ToString(); OleDbParameterCollection param = objCmd.Parameters;
for (int i = 0; i < cols; i++) { if (columns.IndexOf(dt.Columns[i].ColumnName) > -1) { param.Add(new OleDbParameter("@" + "para" + i.ToString(), OleDbType.Numeric)); } else { param.Add(new OleDbParameter("@" + "para" + i.ToString(), OleDbType.VarChar)); } }
int curRow = 0;
//遍历DataTable将数据插入新建的Excel文件中 foreach (DataRow row in dt.Rows) {
for (int i = 0; i < param.Count; i++) { if (columns.IndexOf(dt.Columns[i].ColumnName) > -1) { if (row[i] == null || row[i].ToString() == "") { param[i].Value = 0; } else { param[i].Value = row[i]; }
} else { param[i].Value = row[i]; }
}
objCmd.ExecuteNonQuery();
curRow = curRow + 1; }
objConn.Close();
return excelPath; } public static void DataTableToExcel(DataTable dt, string FileName, string path, string columns) { ArrayList filelist = new ArrayList(); string IsZip = ""; string excelPath = ""; if (dt.Rows.Count < 50000) { IsZip = "0"; excelPath = Kit.CreateExcel(dt, FileName, path, columns); } else if (dt.Rows.Count < 1000000) { IsZip = "1"; excelPath = Kit.CreateExcel(dt, FileName, path, columns); if (excelPath != "") { filelist.Add(excelPath); } } else { DataTable dtOneSheet = dt.Clone(); int order = 1; for (int i = 0; i < dt.Rows.Count; i++) { dtOneSheet.ImportRow(dt.Rows[i]);
if (i > 0 && ((i + 1) == dt.Rows.Count || (i + 1) % 1000000 == 0)) { excelPath = Kit.CreateExcel(dtOneSheet, FileName + "_" + order.ToString(), path, columns); order = order + 1; if (excelPath != "") { filelist.Add(excelPath); } dtOneSheet.Clear(); } } IsZip = "2"; }
string Date = DateTime.Now.ToString("yyyyMMddHH");
if (IsZip == "0") { FileStream excelFile = new FileStream(excelPath, FileMode.Open);//实例化一个FileStream对象 byte[] byData = new byte[excelFile.Length];//建立一个FileStream要用的字节组 excelFile.Read(byData, 0, byData.Length); excelFile.Dispose(); File.Delete(excelPath);
HttpResponse resp; resp = HttpContext.Current.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlPathEncode(FileName + "_" + DateTime.Now.ToString("yyyyMMddHHmmsss") + ".xlsx"));
resp.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
resp.BinaryWrite(byData); resp.End(); } if (IsZip == "1" || IsZip == "2") { string downFileName = FileName + DateTime.Now.ToString("yyyyMMddHHmmsss") + ".zip"; string filename = path + "Code\\ZipExcel\\" + FileName + Date + ".zip"; CreateZip(path + "Code\\ZipExcel\\", filename, filelist);
FileStream excelFile = new FileStream(filename, FileMode.Open);//实例化一个FileStream对象 byte[] byData = new byte[excelFile.Length];//建立一个FileStream要用的字节组 excelFile.Read(byData, 0, byData.Length); excelFile.Dispose();
File.Delete(filename); for (int count = 0; count < filelist.Count; count++) { File.Delete(filelist[count].ToString()); }
HttpResponse resp; resp = HttpContext.Current.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlPathEncode(downFileName));
resp.ContentType = "application/zip";
resp.BinaryWrite(byData); resp.End();
}
}
posted on 2013-08-28 14:07 wanglgkaka 阅读(412) 评论(0) 编辑 收藏 举报