`

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

    博客分类:
  • .NET
阅读更多

首先是大家在网上能搜索一箩筐的仅导出数据的做法: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 = 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; }
}
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中的数据、分页、保存零时文件并下载等功能。仅为一个测试代码。可以根据自己的需求来完善。

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/Rock870210/archive/2009/09/26/4598173.aspx

分享到:
评论

相关推荐

    将gridview数据导出到word和excel

    在.NET开发环境中,GridView控件是ASP.NET网页中常用的数据展示工具,它可以方便地从数据库或其他数据源获取数据并进行显示。然而,有时我们需要将GridView中的数据导出到其他格式,比如Word或Excel,以便于进一步...

    导出devexpress插件的GridControl控件中GridView表格中的数据到excel

    只能导出导出devexpress插件的GridControl控件中GridView表格中的数据,表格中有什么数据导出什么数据,列顺序也不变。 文件说明: 1.ExportTableExcel.cs:实现导出逻辑的类 2.ExportExcelStyle.cs:导出的...

    GridView 导出到 Excel文件中

    而将GridView的数据导出到Excel文件是常见的需求,这通常涉及网页到服务器端的数据处理以及文件响应。下面将详细解释如何实现这个功能。 首先,了解`VerifyRenderingInServerForm`方法。这是一个在服务器控件呈现前...

    GridView中导出数据到 Excel

    在Asp.Net开发中,GridView控件是一种常用的数据展示组件,它可以方便地...以上就是关于“GridView中导出数据到Excel”的详细解释,希望对你有所帮助。在实际开发中,可以根据项目需求和环境选择适合的方法进行实现。

    DEV GridControl GridView导出到Excel 支持多个Sheet 源码

    本文将详细探讨如何使用DEV GridControl的GridView组件将数据导出到Excel,并且支持将多个GridView导出到同一个Excel文件的不同Sheet中。这是一项实用的技术,能够帮助开发者提高工作效率,便于用户对大量数据进行...

    GridView导出Excel DataTable导出Excel 实例源码

    在.NET开发环境中,GridView控件是经常用于展示数据表格的工具,而Excel则是常见的数据处理和分析软件。本文将深入探讨如何使用C#语言,在VS2008中结合Access数据库,实现GridView控件的数据导出到Excel以及直接将...

    防止GridView导出到excel中自动转成科学计数法

    当使用ASP.NET GridView控件导出数据到Excel文件时,如果某一列包含较大的数值(通常超过11位数字),Excel会默认将其格式转换为科学记数法,而非原始数值显示。这种情况下,原始的数值如“123456789012”可能会被...

    ASP.NET中把Gridview导出为EXCEL

    在ASP.NET开发中,将Gridview控件中的数据导出到Excel是一种常见的需求,这有助于用户方便地管理和处理大量数据。下面将详细讲解如何实现这一功能,并解决中文乱码问题。 首先,我们需要理解ASP.NET Gridview的基本...

    asp.net GridView导出excel

    ASP.NET GridView控件是Web开发中常用的数据展示组件,它能够方便地展示数据库或其他数据源中的数据,并提供了丰富的自定义和操作功能。在许多实际应用中,用户可能需要将GridView中的数据导出到Excel文件中,以便...

    在新手学习中gridview控件中的数据导出到excel表中的实例教程

    本教程将详细介绍如何在新手学习过程中,使用GridView控件将数据显示导出到Excel表格中,这对于数据分析、数据备份或用户下载数据等场景非常实用。 首先,我们需要创建一个数据库来存储数据。在这个例子中,我们...

    asp.net 导出GridView里的数据到Excel中

    首先,需要在ASP.NET页面中添加GridView控件以及用于触发导出操作的按钮。例如,在`Default.aspx`文件中添加以下代码: ```html &lt;asp:GridView ID="gvwjdccx" runat="server"&gt; &lt;!-- GridView配置项 --&gt; &lt;/asp:...

    GridView导出Excel实例

    在.NET开发环境中,GridView控件是ASP.NET网页中常用的数据展示工具,它可以方便地绑定数据库数据并进行排序、分页和筛选操作。然而,有时我们需要将GridView中的数据导出为Excel格式,以便用户进行进一步处理或存储...

    将GridView显示的内容导出为Excel

    这个过程通常涉及到将GridView控件中的内容导出为Excel格式。以下是如何实现这一功能的详细步骤: 首先,确保在你的ASP.NET页面中有一个名为`GridView_CheckStat`的GridView控件,它已经绑定了要导出的数据。用户在...

    gridview导出数据到Excel

    总结来说,这个示例代码展示了如何在ASP.NET中使用GridView控件显示数据库数据,并提供一个功能,让用户可以将这些数据导出到Excel文件中。通过处理数据绑定、样式设置和HTTP响应,我们可以实现一个简单的数据导出...

    gridview导出excel表格

    本文将详细介绍如何通过ASP.NET Web Forms技术,利用GridView控件将数据导出到Excel文件中。 #### 二、准备工作 1. **环境搭建**:确保已经安装了.NET Framework开发环境,并且创建了一个新的ASP.NET Web Forms项目...

    Gridview 导出Excel

    如果需要保持样式,可以在HTML字符串中添加样式信息,或者在Excel中使用样式公式。此外,对于日期、数字等特殊数据类型,可能需要在导出前进行格式化。 5. 高效导出大量数据:如果数据量非常大,一次性加载到...

    C#中GridView导出Excel

    总结来说,"C#中GridView导出Excel"涉及到的主要知识点有:C#的事件处理、对象操作(如创建Excel应用程序和工作簿)、数据遍历与复制、以及文件保存操作。掌握这些技能,可以让你在开发Web应用时更高效地处理数据...

    GridView和Excel的导入导出

    在.NET开发环境中,GridView控件是ASP.NET网页中常用的数据展示工具,它可以方便地将数据库中的数据呈现为表格形式。而Excel则是常见的电子表格软件,广泛用于数据管理和分析。GridView与Excel之间的导入导出功能,...

    GridView导出到Excel的C#控件源码

    在实际应用中,我们经常需要将GridView中的数据导出到Excel,以便用户可以进行离线查看或进一步处理。本文将详细探讨如何使用C#控件实现GridView到Excel的导出功能。 首先,我们需要理解 GridView 和 Excel 的基本...

    GridView导出Excel的方法

    在本篇文章中,我们将探讨一种简单的方法来将ASP.NET中的GridView控件中的数据导出到Excel文件中。这种方法利用了C#语言的强大功能以及ASP.NET框架提供的工具,使得导出过程既高效又简洁。 #### GridView简介 `...

Global site tag (gtag.js) - Google Analytics