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

首先是大家在网上能搜索一箩筐的仅导出数据的做法：view plaincopy to clipboardprint?

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;

}

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如下：（以下代码仅供参考，有更好方法的共享下。）

view plaincopy to clipboardprint?

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 = monImage.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; }