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

来源：互联网发布：数据库外键怎么写编辑：程序博客网时间：2024/05/22 09:03

第一种：从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;