【导出EXCEL汇总】asp.net 将数据简单导出到excel中

最新推荐文章于 2020-08-26 15:36:21 发布

wenle006

最新推荐文章于 2020-08-26 15:36:21 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： .net/c# 文章标签： excel asp.net string office dataset exception

本文链接：https://blog.youkuaiyun.com/wenle006/article/details/6542031

.net/c# 专栏收录该内容

15 篇文章

订阅专栏

本文介绍两种将数据导出为Excel的方法：一是直接从DataTable生成HTML并导出；二是利用COM组件创建Excel文件并设置样式。前者适用于无需AJAX请求的情况，后者则可在服务器端生成Excel文件供用户下载。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

第一种：从DataTable导出（此方法无法用在ajax发送请求的情况下）

HttpResponse resp = HttpContext.Current.Response; resp.Clear(); resp.Buffer = true; resp.Charset = "GB2312"; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); resp.ContentType = "application/vnd.ms-excel"; resp.AppendHeader("Content-Disposition","attachment;filename=temp.xls"); StringBuilder colHeaders= new StringBuilder(); int i=0; //定义表对象与行对像，同时用DataSet对其值进行初始化 DataTable dt=ds.Tables[0]; DataRow[] myRow=dt.Select(""); int cols = dt.Columns.Count; colHeaders.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'>"); colHeaders.Append("<head><title></title><meta http-equiv='Content-Type' content=/"text/html; charset=gb2312/"></head><body>"); colHeaders.Append("<table border='1'><tr>"); for(i=0;i < cols;i++) { colHeaders.Append("<th height='20'>" + (dt.Columns[i].Caption.ToString()) + "</th>"); } colHeaders.Append("</tr>/n"); resp.Write(colHeaders.ToString()); colHeaders.Length = 0; foreach(DataRow row in myRow) { colHeaders.Append("<tr height='20'>"); for(i=0;i < cols ;i++) { colHeaders.Append("<td " + (i == 3 ? "width='400'" : "") + ">" + (row[i].ToString()) + "</td>"); } colHeaders.Append("</tr>/n"); resp.Write(colHeaders.ToString()); colHeaders.Length = 0; } colHeaders.Append("</table></body></html>"); resp.Write(colHeaders.ToString()); //写缓冲区中的数据到HTTP头文件中 resp.End();

第二种：通过COM组件导出到Excel,最后返回生成文件的路径到前台通过触发超链接的事件下载保存

string path = HttpContext.Current.Server.MapPath(HttpContext.Current.Request.ApplicationPath.ToString()); string filename=""; bool filesaved=false; filename = path.Replace("////","//")+"/temp.xls"; Excel.Application xlapp=new Excel.ApplicationClass(); if(xlapp==null) { HttpContext.Current.Response.Write("<mce:script type="text/javascript"></mce:script>"); return ""; } Excel.Workbooks workbooks=xlapp.Workbooks; Excel._Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 Excel.Range range; long totalcount=ds.Tables[0].Rows.Count; long rowread=0; float percent=0; //写入字段 for(int i=0;i<ds.Tables[0].Columns.Count;i++) { worksheet.Cells[2,i+1]=ds.Tables[0].Columns[i].ColumnName; range=(Excel.Range)worksheet.Cells[2,i+1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } //写入数值 for(int r=0;r<ds.Tables[0].Rows.Count;r++) { for(int i=0;i<ds.Tables[0].Columns.Count;i++) { worksheet.Cells[r+3,i+1]=ds.Tables[0].Rows[r][i]; } rowread++; percent=((float)(100*rowread))/totalcount; } range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[ds.Tables[0].Rows.Count+2,ds.Tables[0].Columns.Count]); range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null); range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin; if(ds.Tables[0].Columns.Count>1) { range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex =Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; } if(filename!="") { try { workbook.Saved =true; workbook.SaveCopyAs(filename); filesaved=true; } catch(Exception ex) { filesaved=false; HttpContext.Current.Response.Write("导出文件时出错,文件可能正被打开！/n"); } } else { filesaved=false; } xlapp.Quit(); GC.Collect();//强行销毁 int intStart = filename.LastIndexOf("/")+1; string saveFileName = filename.Substring(intStart,filename.Length-intStart); string ip = HttpContext.Current.Request.ServerVariables.Get("Local_Addr").ToString(); string realPath = ip+"/"+path.Substring(path.LastIndexOf("//")+1)+"/"+saveFileName;; return realPath;