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();

            }

 

        }