asp.net中通过html格式导出excel文件
生活随笔
收集整理的這篇文章主要介紹了
asp.net中通过html格式导出excel文件
小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.
//通過html格式生成導(dǎo)出excel文件，下載保存。??
StreamExport(wgMdcStaff5, fileName, ToDataTable<MdcDrugProcureStockViewDto>(response.responseObjects));
?? 調(diào)用方法//?
//把實(shí)體集合對(duì)象轉(zhuǎn)化成datatable類型的數(shù)據(jù)
public static System.Data.DataTable ToDataTable<T>(List<T> entitys)
??? {
??????? //檢查實(shí)體集合不能為空
??????? if (entitys == null || entitys.Count < 1)
??????? {
??????????? throw new Exception("需轉(zhuǎn)換的集合為空");
??????? } //取出第一個(gè)實(shí)體的所有Propertie
??????? Type entityType = entitys[0].GetType();
??????? PropertyInfo[] entityProperties = entityType.GetProperties(); //生成DataTable的structure
??????? //生產(chǎn)代碼中，應(yīng)將生成的DataTable結(jié)構(gòu)Cache起來，此處略
??????? System.Data.DataTable dt = new System.Data.DataTable();
??????? for (int i = 0; i < entityProperties.Length; i++)
??????? {
??????????? Type colType = entityProperties[i].PropertyType;
??????????? if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
??????????? {
??????????????? colType = colType.GetGenericArguments()[0];
??????????? }
??????????? dt.Columns.Add(entityProperties[i].Name, colType);
??????? } //將所有entity添加到DataTable中
??????? foreach (object entity in entitys)
??????? {
??????????? //檢查所有的的實(shí)體都為同一類型
??????????? if (entity.GetType() != entityType)
??????????? {
??????????????? throw new Exception("要轉(zhuǎn)換的集合元素類型不一致");
??????????? }
??????????? object[] entityValues = new object[entityProperties.Length];
??????????? for (int i = 0; i < entityProperties.Length; i++)
??????????? {
??????????????? entityValues[i] = entityProperties[i].GetValue(entity, null) == null ? DBNull.Value : entityProperties[i].GetValue(entity, null); }
??????????? dt.Rows.Add(entityValues);
??????? }
??????? return dt;
??? }
??? /// <summary>??
??? /// 通過流導(dǎo)出Excel??
??? /// </summary>??
??? /// <param name="ds">數(shù)據(jù)源DataSet</param>??
??? /// <param name="fileName">保存文件名(例如：a.xls)</param>??
??? /// <returns></returns>??
??? public bool StreamExport(WsdGrid wsdGrid, string fileName, System.Data.DataTable dt)
??? {
??????? if (fileName == string.Empty)
??????? {
??????????? return false;
??????? } StringBuilder content = new StringBuilder();
??????? int i = 0;
??????? int cl = wsdGrid.Columns.Count; 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的網(wǎng)格線，若不想顯示Excel的網(wǎng)格線，可以去掉此代碼
??????? 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>" + fileName + "</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> ");
??????? //標(biāo)題
??????? //content.Append("<tr align='center'><td colspan='" + wsdGrid.Columns.Count + "'><strong>" + fileName + "</strong></td></tr>");
??????? //content.Append("<tr align='center'></tr>"); content.Append("<tr align='center'> <td colspan='" + wsdGrid.Columns.Count + "'>");
??????? content.Append("<table border='1' >");
??????? content.Append("<tr align='center' >"); for (i = 0; i < cl; i++)
??????? {
??????????? content.Append("<td>" + wsdGrid.Columns[i].HeaderText.ToString() + "</td>");
??????? }
??????? content.Append("</tr>");
??????? //逐行處理數(shù)據(jù)??
??????? foreach (DataRow row in dt.Rows)
??????? { for (i = 0; i < cl; i++)
??????????? {
??????????????? //頭加tr
??????????????? if (i == 0)
??????????????????? content.Append("<tr align='center' >"); object obj = row[wsdGrid.Columns[i].UniqueName];
??????????????? Type type = obj.GetType();
??????????????
??????????????????? if (type.Name == "Int32" || type.Name == "Single" || type.Name == "Double" || type.Name == "Decimal" || type.Name == "DateTime")
??????????????????? {
??????????????????????? if (type.Name == "DateTime")
??????????????????????????? content.AppendFormat("<td style='vnd.ms-excel.numberformat:yyyy-mm-dd'>{0}</td>", obj);
??????????????????????? else if (type.Name == "Int32")
??????????????????????????? content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0'>{0}</td>", obj);
??????????????????????? else
??????????????????????? {
???????????????????????????? //設(shè)置如果為數(shù)字型的保留兩位小數(shù)
???????????????????????????? if (wsdGrid.Columns[i].UniqueName == "storageQty"|| wsdGrid.Columns[i].UniqueName =="storageSumQty")
??????????????????????????? {
??????????????????????????????? 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);
??????????????? //尾加/tr
??????????????? if (i == cl - 1)
??????????????????? content.Append("</tr>");
??????????? } }
??????? content.Append("</table></td></tr>"); content.Append("</table></body></html>");
??????? content.Replace(" ", "");
??????? System.Web.HttpContext.Current.Response.Clear();
??????? System.Web.HttpContext.Current.Response.Buffer = true;
??????? System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
??????? //System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
??????? System.Web.HttpContext.Current.Response.Charset = "GB2312";
??????? System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
??????? fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
??????? System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "p_w_upload; filename=" + fileName + ".xls");
??????? System.Web.HttpContext.Current.Response.Write(content.ToString());
??????? System.Web.HttpContext.Current.Response.End();
??????? return true;
??? }??
StreamExport(wgMdcStaff5, fileName, ToDataTable<MdcDrugProcureStockViewDto>(response.responseObjects));
?? 調(diào)用方法//?
//把實(shí)體集合對(duì)象轉(zhuǎn)化成datatable類型的數(shù)據(jù)
public static System.Data.DataTable ToDataTable<T>(List<T> entitys)
??? {
??????? //檢查實(shí)體集合不能為空
??????? if (entitys == null || entitys.Count < 1)
??????? {
??????????? throw new Exception("需轉(zhuǎn)換的集合為空");
??????? } //取出第一個(gè)實(shí)體的所有Propertie
??????? Type entityType = entitys[0].GetType();
??????? PropertyInfo[] entityProperties = entityType.GetProperties(); //生成DataTable的structure
??????? //生產(chǎn)代碼中，應(yīng)將生成的DataTable結(jié)構(gòu)Cache起來，此處略
??????? System.Data.DataTable dt = new System.Data.DataTable();
??????? for (int i = 0; i < entityProperties.Length; i++)
??????? {
??????????? Type colType = entityProperties[i].PropertyType;
??????????? if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
??????????? {
??????????????? colType = colType.GetGenericArguments()[0];
??????????? }
??????????? dt.Columns.Add(entityProperties[i].Name, colType);
??????? } //將所有entity添加到DataTable中
??????? foreach (object entity in entitys)
??????? {
??????????? //檢查所有的的實(shí)體都為同一類型
??????????? if (entity.GetType() != entityType)
??????????? {
??????????????? throw new Exception("要轉(zhuǎn)換的集合元素類型不一致");
??????????? }
??????????? object[] entityValues = new object[entityProperties.Length];
??????????? for (int i = 0; i < entityProperties.Length; i++)
??????????? {
??????????????? entityValues[i] = entityProperties[i].GetValue(entity, null) == null ? DBNull.Value : entityProperties[i].GetValue(entity, null); }
??????????? dt.Rows.Add(entityValues);
??????? }
??????? return dt;
??? }
??? /// <summary>??
??? /// 通過流導(dǎo)出Excel??
??? /// </summary>??
??? /// <param name="ds">數(shù)據(jù)源DataSet</param>??
??? /// <param name="fileName">保存文件名(例如：a.xls)</param>??
??? /// <returns></returns>??
??? public bool StreamExport(WsdGrid wsdGrid, string fileName, System.Data.DataTable dt)
??? {
??????? if (fileName == string.Empty)
??????? {
??????????? return false;
??????? } StringBuilder content = new StringBuilder();
??????? int i = 0;
??????? int cl = wsdGrid.Columns.Count; 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的網(wǎng)格線，若不想顯示Excel的網(wǎng)格線，可以去掉此代碼
??????? 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>" + fileName + "</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> ");
??????? //標(biāo)題
??????? //content.Append("<tr align='center'><td colspan='" + wsdGrid.Columns.Count + "'><strong>" + fileName + "</strong></td></tr>");
??????? //content.Append("<tr align='center'></tr>"); content.Append("<tr align='center'> <td colspan='" + wsdGrid.Columns.Count + "'>");
??????? content.Append("<table border='1' >");
??????? content.Append("<tr align='center' >"); for (i = 0; i < cl; i++)
??????? {
??????????? content.Append("<td>" + wsdGrid.Columns[i].HeaderText.ToString() + "</td>");
??????? }
??????? content.Append("</tr>");
??????? //逐行處理數(shù)據(jù)??
??????? foreach (DataRow row in dt.Rows)
??????? { for (i = 0; i < cl; i++)
??????????? {
??????????????? //頭加tr
??????????????? if (i == 0)
??????????????????? content.Append("<tr align='center' >"); object obj = row[wsdGrid.Columns[i].UniqueName];
??????????????? Type type = obj.GetType();
??????????????
??????????????????? if (type.Name == "Int32" || type.Name == "Single" || type.Name == "Double" || type.Name == "Decimal" || type.Name == "DateTime")
??????????????????? {
??????????????????????? if (type.Name == "DateTime")
??????????????????????????? content.AppendFormat("<td style='vnd.ms-excel.numberformat:yyyy-mm-dd'>{0}</td>", obj);
??????????????????????? else if (type.Name == "Int32")
??????????????????????????? content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0'>{0}</td>", obj);
??????????????????????? else
??????????????????????? {
???????????????????????????? //設(shè)置如果為數(shù)字型的保留兩位小數(shù)
???????????????????????????? if (wsdGrid.Columns[i].UniqueName == "storageQty"|| wsdGrid.Columns[i].UniqueName =="storageSumQty")
??????????????????????????? {
??????????????????????????????? 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);
??????????????? //尾加/tr
??????????????? if (i == cl - 1)
??????????????????? content.Append("</tr>");
??????????? } }
??????? content.Append("</table></td></tr>"); content.Append("</table></body></html>");
??????? content.Replace(" ", "");
??????? System.Web.HttpContext.Current.Response.Clear();
??????? System.Web.HttpContext.Current.Response.Buffer = true;
??????? System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
??????? //System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
??????? System.Web.HttpContext.Current.Response.Charset = "GB2312";
??????? System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
??????? fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
??????? System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "p_w_upload; filename=" + fileName + ".xls");
??????? System.Web.HttpContext.Current.Response.Write(content.ToString());
??????? System.Web.HttpContext.Current.Response.End();
??????? return true;
??? }??
轉(zhuǎn)載于:https://blog.51cto.com/zhjjzhjj/984429
總結(jié)
以上是生活随笔為你收集整理的asp.net中通过html格式导出excel文件的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 创建Server 2012 VHDX虚拟
- 下一篇： php-数据分析 余弦相似度实现