关于Gridview中使用控件和图片导出到Excel的处理

 首先是大家在网上能搜索一箩筐的仅导出数据的做法：public static void Export(Page pg, GridView gv,bool alowPage, string FileName) { pg.Response.Clear(); pg.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8"); pg.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString()); pg.Response.ContentEncoding = System.Text.Encoding.UTF8; pg.Response.ContentType = "application/vnd.ms-excel";//设置输出文件类型为excel文件。 pg.Response.Charset = ""; System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); if (!alowPage) { gv.AllowPaging = false; gv.DataBind(); } gv.RenderControl(oHtmlTextWriter); pg.Response.Output.Write(oStringWriter.ToString()); pg.Response.End(); if (!alowPage) { gv.AllowPaging = true; } }

 

这个可以处理导出当前页或导出所有数据。但是如果Gridview中包含图片列则就没有用了。经过网上查阅资料自己做了一个可以导出图片的方法，code如下：（以下代码仅供参考，有更好方法的共享下。）

public static Excel.Application app; public static Excel.Worksheet workSheet; public static Excel.Workbook workBook; public static string fileName = string.Empty; //单元格列号数组 private static string[] colString = new string[26] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; /// <summary> /// 将GridView数据写入Excel文件（自动分页） /// </summary> /// <param name="pag">Page</param> /// <param name="gv">GridView</param> /// <param name="rows">每个WorkSheet写入多少行数据</param> /// <param name="top">表格数据起始行索引</param> /// <param name="left">表格数据起始列索引</param> /// <param name="coluwidth">每列数据的宽度</param> public static void DataTableToExcel(Page pag, GridView gv, int rows, int top, int left,int[] coluwidth) { app = new Excel.Application(); app.Visible = false; app.DisplayAlerts = false; workBook = app.Workbooks.Add(Type.Missing); int rowCount = gv.Rows.Count; //DataTable行数 int colCount = gv.Columns.Count; //DataTable列数 int sheetCount = GetSheetCount(rowCount, rows); //WorkSheet个数 //复制sheetCount-1个WorkSheet对象 for (int i = 1; i < sheetCount; i++) { workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); workSheet.Copy(Missing.Value, workBook.Worksheets[i]); } for (int i = 1; i <= sheetCount; i++) { int startRow = (i - 1) * rows; //记录起始行索引 int endRow = i * rows; //记录结束行索引 //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数 if (i == sheetCount) endRow = rowCount; //获取要写入数据的WorkSheet对象，并重命名 workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); workSheet.Name = "页-" + i.ToString(); GridViewRow headrow = gv.HeaderRow; for (int h = 0; h < colCount; h++)//导出Gridview头部 { Excel.Range range1 = GetRang(h + 1, 0); range1.Font.Bold = 1; range1.Value2 = headrow.Cells[h].Text; } //将gv中的数据写入WorkSheet for (int j = 1; j <= endRow - startRow; j++) { if (rowCount < j) break; for (int k = 0; k < colCount; k++) { if (gv.Rows[startRow + j - 1].Cells[k].HasControls()) { Hashtable ht = GetControlNameAndType(gv, startRow + j - 1, k); if (ht.Count > 0) { string value = string.Empty; bool isImg = false; foreach (DictionaryEntry de in ht) { string []temp = de.Value.ToString().Split(','); if (temp[0] == "Image") { isImg = true; if (!AddImage(pag, temp[1], temp[2])) workSheet.Cells[top + j, left + k] = "未知图片"; } else { isImg = false; value += temp[1]+" "; } } if(!isImg) workSheet.Cells[top + j, left + k] = value; } } else workSheet.Cells[top + j, left + k] = gv.Rows[startRow + j - 1].Cells[k].Text; } } if (gv.ShowFooter == true)//导出Gridview的footer { GridViewRow footrow = gv.FooterRow; for (int h = 0; h < colCount; h++) { Excel.Range range1 = GetRang(h + 1, gv.Rows.Count + 1); range1.Font.Bold = 1; if (footrow.Cells[h].Text != null && footrow.Cells[h].Text != " ") range1.Value2 = footrow.Cells[h].Text; } } SetColumnStyle(gv.Columns.Count, gv.Rows.Count, workSheet, coluwidth);//设置样式 } bool result = true; string realSavePath = SaveFile(pag, out result); if (realSavePath != "" && result) { Export(pag,realSavePath); } else pag.Response.Write("<mce:script type="text/javascript"><!-- alert('导出失败！//n错误原因：" + realSavePath.Replace("//", "") + "'); // --></mce:script>"); } //为用户提供导出的Excel文件的下载 private static void Export(Page pg,string path) { FileInfo file = new FileInfo(path); if (file.Exists) { pg.Response.Clear(); pg.Response.ClearHeaders(); pg.Response.Buffer = true; pg.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-7"); pg.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(path.Substring(path.LastIndexOf("//") + 1), Encoding.UTF8).ToString()); pg.Response.ContentEncoding = System.Text.Encoding.UTF7; pg.Response.ContentType = "application/octet-stream";//设置输出文件类型为excel文件。 pg.Response.Charset = ""; pg.Response.WriteFile(file.FullName); pg.Response.Flush(); file.Delete();//下载完成后删除文件 pg.Response.Write("<mce:script type="text/javascript"><!-- window.close(); // --></mce:script>");//自动关闭下载浏览器，防止用户手动关闭中断下载进程引起的文件没有删除 pg.Response.End(); } else pg.Response.Write("<mce:script type="text/javascript"><!-- alert('导出失败！//n错误原因：" + path.Replace("//", "") + "'); // --></mce:script>"); } //制作缩微图并导出图片到Excel public static bool AddImage(Page pag,string url,string rangeName) { bool findImage = false; string path = pag.Server.MapPath(url); System.IO.FileInfo file = new System.IO.FileInfo(path); if (file.Exists) { string savapath = "C://WINDOWS//Temp" + path.Substring(path.LastIndexOf("//")); System.IO.FileInfo file2 = new System.IO.FileInfo(savapath); if (file2.Exists) file2.Delete(); ReturnValue val = ADOnline.Common.CommonImage.CreateAndSaveSmallImage(path, 80, 50, savapath); if (val == ReturnValue.SUCCEED) { InsertPicture(rangeName, savapath, 80, 50); findImage = true; } } return findImage; } //获取Gridview每列中的所有显示控件名称、类型和值 public static Hashtable GetControlNameAndType(GridView gv,int row,int cell) { Hashtable ht = new Hashtable(); if (gv.Rows[row].Cells[cell].Controls.Count > 0) { for (int i = 0; i < gv.Rows[row].Cells[cell].Controls.Count; i++) { Control temp = gv.Rows[row].Cells[cell].Controls[i]; if (temp.GetType() == typeof(Label) && temp.Visible==true) ht.Add(temp.ID, "Lable," + (temp as Label).Text + "," + colString[cell] + Convert.ToString(row + 2)); else if (temp.GetType() == typeof(DropDownList) && temp.Visible != false) ht.Add(temp.ID, "DropDownList," + (temp as DropDownList).SelectedItem.Text + "," + colString[cell]+Convert.ToString(row+2)); else if (temp.GetType() == typeof(LinkButton) && temp.Visible != false) ht.Add(temp.ID, "LinkButton," + (temp as LinkButton).Text + "," + colString[cell] + Convert.ToString(row + 2)); else if (temp.GetType() == typeof(CheckBox) && temp.Visible != false) ht.Add(temp.ID, "CheckBox," + ((temp as CheckBox).Checked==true ? "true" : "false") + "," + colString[cell] + Convert.ToString(row + 2)); else if (temp.GetType() == typeof(Image) && temp.Visible != false) ht.Add(temp.ID, "Image," + (temp as Image).ImageUrl + "," + colString[cell] + Convert.ToString(row + 2)); } } return ht; } //设置格式 public static void SetColumnStyle(int colCount, int row, Excel.Worksheet ws, int[] coluwidth) { for (int i = 1; i <= colCount; i++) { Excel.Range ran = ws.get_Range(ws.Cells[1, i], ws.Cells[row + 2, i]); //ran.Select(); ran.WrapText = true; ran.ColumnWidth = Convert.ToString(coluwidth[i - 1]); ran.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; ran.Borders.LineStyle = 1; if (i == 3) ran.RowHeight = Convert.ToString(51); } } //插入图片到Excel public static void InsertPicture(string RangeName, string PicturePath, float PictuteWidth, float PictureHeight) { Excel.Range rangeTemp = workSheet.get_Range(RangeName, Missing.Value); rangeTemp.Select(); float PicLeft, PicTop; PicLeft = Convert.ToSingle(rangeTemp.Left)+2; PicTop = Convert.ToSingle(rangeTemp.Top) + 1; rangeTemp.ColumnWidth = Convert.ToString(15); rangeTemp.RowHeight = Convert.ToString(51); workSheet.Shapes.AddPicture(PicturePath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, 87, 50); } /// <summary> /// 计算WorkSheet数量 /// </summary> /// <param name="rowCount">记录总行数</param> /// <param name="rows">每WorkSheet行数</param> public static int GetSheetCount(int rowCount, int rows) { int n = rowCount % rows; //余数 if (n == 0) return rowCount / rows; else return Convert.ToInt32(rowCount / rows) + 1; } //获取指定单元格 public static Excel.Range GetRang(int curColum, int rowNum) { if (curColum < 0 || curColum > 255) throw new Exception("列号出错！"); string rangName = string.Empty; if (curColum <= 26) rangName = colString[curColum - 1] + Convert.ToString(rowNum+1); else { int colNum = curColum / 26; int temp = curColum % 26; if (temp == 0) rangName = colString[colNum - 1] + colString[25] + Convert.ToString(rowNum + 1); else rangName = colString[colNum] + colString[temp - 1] + Convert.ToString(rowNum + 1); } return workSheet.get_Range(rangName, System.Reflection.Missing.Value); } /// <summary> /// 保存文件 /// </summary> public static string SaveFile(Page pag,out bool result) { string defaultPath = pag.Server.MapPath("~/Record/ExcelReportTempFolder/") + GetFileName(); result = true; try { workBook.SaveAs(defaultPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } catch (Exception ex) { result = false; defaultPath = ex.ToString(); } Close(); Dispose(); return defaultPath; } //获取文件名称 private static string GetFileName() { if (fileName == null || fileName.Trim() == "") return (DateTime.Now.ToString().Replace(':', '-') + "_report.xls"); else if (fileName.LastIndexOf('.') == -1) return fileName + ".xls"; else if (fileName.Substring(fileName.LastIndexOf('.')) != ".xls") return fileName.Substring(0, fileName.LastIndexOf('.')) + ".xls"; else return fileName; } /// <summary> /// 关闭应用程序 /// </summary> private static void Close() { workBook.Close(false, Missing.Value, Missing.Value); app.Quit(); } /// <summary> /// 释放所引用的COM对象。 /// </summary> public static void Dispose() { ReleaseObj(workSheet); ReleaseObj(workBook); ReleaseObj(app); app = null; System.GC.Collect(); System.GC.WaitForPendingFinalizers(); } /// <summary> /// 释放对象，内部调用 /// </summary> /// <param name="o"></param> private static void ReleaseObj(object o) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(o); } catch { } finally { o = null; } }

该方法提供了获取单元格、设置格式、判断Gridview中的数据、分页、保存零时文件并下载等功能。仅为一个测试代码。可以根据自己的需求来完善。