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(" ", "");
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;
}
