作者： zhongdaiqi

bla…bla…

private DataTable exeSP(string sp,string pars) {
    using (SqlConnection conn = new SqlConnection(_connectionString))
    {
        try
        {
            conn.Open();
            SqlCommand cmd = iniSqlCommand(sp);
            cmd.Connection = conn;
            prepareInPars(cmd, pars);
            prepareOutpars(cmd);
            var outpars = GetOutputParameters(cmd.CommandText);
            SqlDataAdapter sdr = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sdr.Fill(dt);
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            conn.Close();
        }
    }
}

        public HttpResponseMessage Get(string obj, string pars, string mappingRule)
        {

            try
            {
                string cmdtext = "Get" + obj;
                DataTable dt = exeSP(cmdtext, pars);
                mappingExcelCol(dt, mappingRule);
               //return WriteExcel(dt, "");
               // return DataTable2Excel(dt);
                //return StreamExport(dt, "doooo.xls");
                String dir = ConfigurationManager.AppSettings["ImportFileFolder"];
                String tmpExcel =  dir + "\\" + Guid.NewGuid().ToString() + ".xls";
                return TableToExcelFile(dt, tmpExcel, HttpContext.Current.Server.MapPath("~\\Files\\Blank.xls"));
            }
            catch (IOException)
            {
                return Request.CreateResponse(HttpStatusCode.InternalServerError);
            }

        }

        //dt为数据源（数据表）
        //ExcelFileName 为要导出的Ｅｘｃｌｅ文件
        //ModelFile为模板文件，该文件与数据源中的表一致。否则数据会导出失败。
        //ModelFile文件里，需要有一张 与 dt.TableName 一致的表，而且字段也要一致。
        //注明：如果不用ModelFile的话，可以用一个空白Excel文件，不过，要去掉下面创建表的注释，让OleDb自己创建一个空白表。
        public HttpResponseMessage TableToExcelFile(DataTable dt, string ExcelFileName, string ModelFile)
        {
            dt.TableName = "Sheet1";
            File.Copy(ModelFile, ExcelFileName);  //复制一个空文件，提供写入数据用

            if (File.Exists(ExcelFileName) == false)
            {
                throw new Exception( "系统创建临时文件失败，请与系统管理员联系！");
            }
            if (dt == null)
            {
                throw new Exception( "DataTable不能为空");
            }
            int rows = dt.Rows.Count;
            int cols = dt.Columns.Count;
            StringBuilder sb;
            string connString;
            if (rows == 0)
            {
                throw new Exception( "没有数据");
            }
            sb = new StringBuilder();
            connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFileName + ";Extended Properties=Excel 8.0;";

            //生成创建表的脚本
            //sb.AppendLine("DROP TABLE " + dt.TableName + "\r\n");

             sb.Append("CREATE TABLE ");
             sb.Append(dt.TableName + " ( ");
             for(int i=0;i<cols;i++)
             {
                 if(i < cols - 1)
                 sb.Append(string.Format("[{0}] varchar,",dt.Columns[i].ColumnName));
                 else
                 sb.Append(string.Format("[{0}] varchar)",dt.Columns[i].ColumnName));
             }    

            //return sb.ToString();
            OleDbConnection objConn = new OleDbConnection(connString);
            OleDbCommand objCmd = new OleDbCommand();
            objCmd.Connection = objConn;
            //del

            try
            {

                objConn.Open();

                objCmd.CommandText = "DROP TABLE " + dt.TableName;

                objCmd.ExecuteNonQuery();

                objCmd.CommandText = sb.ToString();

                objCmd.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                throw new Exception( "在Excel中创建表失败，错误信息：" + e.Message);
            }
            sb.Remove(0, sb.Length);
            sb.Append("INSERT INTO ");
            sb.Append(dt.TableName + " ( ");
            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("
@" + dt.Columns[i].ColumnName + ",");
                else
                    sb.Append("
@" + dt.Columns[i].ColumnName + ")");
            }
            //建立插入动作的Command
            objCmd.CommandText = sb.ToString();
            OleDbParameterCollection param = objCmd.Parameters;
            for (int i = 0; i < cols; i++)
            {
                param.Add(new OleDbParameter("
@" + dt.Columns[i].ColumnName, OleDbType.VarChar));
            }
            //遍历DataTable将数据插入新建的Excel文件中
            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < param.Count; i++)
                {
                    param[i].Value = row[i];
                }
                objCmd.ExecuteNonQuery();
            }
           // return "数据已成功导入Excel";
            objConn.Close();

            HttpResponseMessage response = new HttpResponseMessage();
            response.StatusCode = HttpStatusCode.OK;
            response.Content = new StreamContent(File.OpenRead(ExcelFileName));
            response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/ms-excel");
            response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
            {
                FileName = "foo.xls"
            };
            return response;
        }

        public HttpResponseMessage WriteExcel(DataTable dt, string fileName) {
            dt.TableName = "Excel";
            HttpResponseMessage response = new HttpResponseMessage();
            response.StatusCode = HttpStatusCode.OK;
            MemoryStream ms = new MemoryStream();
            dt.WriteXml(ms, XmlWriteMode.WriteSchema);
            response.Content = new StreamContent(ms);
            response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/ms-excel");
            response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
            {
                FileName = "foo.xls"
            };
            return response;
     }

        /// <summary>
        /// DataTable通过流导出Excel
        /// </summary>
        /// <param name="ds">数据源DataSet</param>
        /// <param name="columns">DataTable中列对应的列名(可以是中文),若为null则取DataTable中的字段名</param>
        /// <param name="fileName">保存文件名(例如：a.xls)</param>
        /// <returns></returns>
        public HttpResponseMessage StreamExport(DataTable dt, string fileName)
        {
            if (dt.Rows.Count > 65535) //总行数大于Excel的行数 
            {
                throw new Exception("预导出的数据总行数大于excel的行数");
            }
            //if (string.IsNullOrEmpty(fileName)) return false;

            StringBuilder content = new StringBuilder();
            StringBuilder strtitle = new StringBuilder();
            content.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>");
            content.Append("<head><title></title><meta http-equiv='Content-Type' content=\"text/html; charset=gb2312\">");
            //注意：[if gte mso 9]到[endif]之间的代码，用于显示Excel的网格线，若不想显示Excel的网格线，可以去掉此代码
            content.Append("<!--[if gte mso 9]>");
            content.Append("<xml>");
            content.Append(" <x:ExcelWorkbook>");
            content.Append("  <x:ExcelWorksheets>");
            content.Append("   <x:ExcelWorksheet>");
            content.Append("    <x:Name>Sheet1</x:Name>");
            content.Append("    <x:WorksheetOptions>");
            content.Append("      <x:Print>");
            content.Append("       <x:ValidPrinterInfo />");
            content.Append("      </x:Print>");
            content.Append("    </x:WorksheetOptions>");
            content.Append("   </x:ExcelWorksheet>");
            content.Append("  </x:ExcelWorksheets>");
            content.Append("</x:ExcelWorkbook>");
            content.Append("</xml>");
            content.Append("<![endif]-->");
            content.Append("</head><body><table style='border-collapse:collapse;table-layout:fixed;'><tr>");

            for (int j = 0; j < dt.Columns.Count; j++)
            {
                content.Append("<td><b>" + dt.Columns[j].ColumnName + "</b></td>");
            }
            content.Append("</tr>\n");

            for (int j = 0; j < dt.Rows.Count; j++)
            {
                content.Append("<tr>");
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    object obj = dt.Rows[j][k];
                    Type type = obj.GetType();
                    if (type.Name == "Int32" || type.Name == "Single" || type.Name == "Double" || type.Name == "Decimal")
                    {
                        double d = obj == DBNull.Value ? 0.0d : Convert.ToDouble(obj);
                        if (type.Name == "Int32" || (d - Math.Truncate(d) == 0))
                            content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0'>{0}</td>", obj);
                        else
                            content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0.00'>{0}</td>", obj);
                    }
                    else
                        content.AppendFormat("<td style='vnd.ms-excel.numberformat:@'>{0}</td>", obj);
                }
                content.Append("</tr>\n");
            }
            content.Append("</table></body></html>");
            content.Replace("&nbsp;", "");

            byte[] fileContents = Encoding.Default.GetBytes(content.ToString());
            var fileStream = new MemoryStream(fileContents);

            HttpResponseMessage response = new HttpResponseMessage();
            response.StatusCode = HttpStatusCode.OK;
            response.Content = new StreamContent(fileStream);
            response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/ms-excel");
            response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
            {
                FileName = "foo.xls"
            };
            return response;

            //pages.Response.Clear();
            //pages.Response.Buffer = true;
            //pages.Response.ContentType = "application/ms-excel";  //"application/ms-excel";
            //pages.Response.Charset = "UTF-8";
            //pages.Response.ContentEncoding = System.Text.Encoding.UTF7;
            //fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
            //pages.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
            //pages.Response.Write(content.ToString());
            ////pages.Response.End();  //注意，若使用此代码结束响应可能会出现“由于代码已经过优化或者本机框架位于调用堆栈之上,无法计算表达式的值。”的异常。
            //HttpContext.Current.ApplicationInstance.CompleteRequest(); //用此行代码代替上一行代码，则不会出现上面所说的异常。
            //return true;
        }

        /// <summary>
        /// 这种方式导出来的Excel，会提示格式不匹配，但是能正常查看
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private HttpResponseMessage DataTable2Excel(DataTable dt) {
            var sbHtml = new StringBuilder();
            sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>");
            sbHtml.Append("<tr>");
            foreach (DataColumn item in dt.Columns)
            {
                sbHtml.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.ColumnName);
            }
            sbHtml.Append("</tr>");
            foreach (DataRow dr in dt.Rows)
            {
                sbHtml.Append("<tr>");
                foreach (DataColumn dc in dt.Columns)
                {
                    sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", dr[dc]);
                }
                sbHtml.Append("</tr>");
            }
            sbHtml.Append("</table>");

            byte[] fileContents = Encoding.Default.GetBytes(sbHtml.ToString());
            var fileStream = new MemoryStream(fileContents);
            HttpResponseMessage response = new HttpResponseMessage();
            response.StatusCode = HttpStatusCode.OK;
            response.Content = new StreamContent(fileStream);
            response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/ms-excel");
            response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
            {
                FileName = "foo.xls"
            };
            return response;
        }

不先关闭SqlDataReader,获取不到返回参数