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

最新推荐文章于 2018-09-12 13:51:40 发布

蓝水依依

最新推荐文章于 2018-09-12 13:51:40 发布

阅读量1.3k

点赞数

分类专栏： .net外部工具接口文章标签： excel image path null file string

本文链接：https://blog.youkuaiyun.com/zjlovety/article/details/6620578

版权

.net外部工具接口专栏收录该内容

5 篇文章

订阅专栏

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

view plain copy to clipboard print ?

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 plain copy to clipboard print ?

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; }
}