将DATAGRID的数据导出为一个标准EXCEL格式的文件

将GRID的数据导出为一个标准EXCEL格式的文件。

这个方式有一个问题,他需要创建临时文件,如果是在多用户的情况下,可能会导起文件死锁问题。

 如果不是必要,建议直接导出为EXCEL,而不要在服务器上生成临时文件

前言
None.gif
None.gif用传统的导出方法:只是将DataGrid信息用html输出,文件名后辍是.xls而已。
None.gif如果想将这个方法导入到Sql Server 中,会提示出错。因为它不是标准的Excel格式文件。
None.gif
None.gif用本例中的导出方法:会输出标准的Excel格式文件,非常稳定,不会死锁Excel进程,支持中文文件名,支持表头导出,
None.gif支持大多数数据库导入。
None.gif实现算法:
None.gif
None.gif   利用Excel组件将DataGrid控件内容生成Excel临时文件,并存放在服务器上,然后用Response方法将生成的Excel文件下载到客户
None.gif
None.gif端然后再将生成的临时文件删除。
None.gif
None.gif具体步骤:
None.gif
1.在项目中引用Excel组件
None.gifInterop.Excel.dll 文件版本1.
3.0.0
None.gif
2.项目中应有一个目录(本例中Template目录),以便存放Excel文件(名字自己定)
None.gif
3.导入方法类
None.gif
None.gif
protected void ExportToExcel(System.Web.UI.WebControls.DataGrid grid,string fileName)
ExpandedBlockStart.gifContractedBlock.gif       
...{
InBlock.gif           
string templetFilePath;
InBlock.gif            templetFilePath
= Server.MapPath("../").ToString() + @"Template";
InBlock.gif           
object missing = Missing.Value;
InBlock.gif            Excel.Application app;
InBlock.gif            Excel.Workbook workBook;
InBlock.gif            Excel.Worksheet workSheet;
InBlock.gif            Excel.Range range;
InBlock.gif
InBlock.gif
InBlock.gif           
//创建一个Application对象并使其不可见
InBlock.gif
            app = new Excel.ApplicationClass();
InBlock.gif            app.Visible
=false;
InBlock.gif
InBlock.gif           
InBlock.gif           
//打开模板文件,得到WorkBook对象
InBlock.gif           
//workBook = app.Workbooks.Open(templetFilePath + "SuperTemplet.xls", missing, missing, missing,
InBlock.gif

InBlock.gifmissing, missing,
InBlock.gif           
//    missing, missing, missing, missing, missing, missing, missing);
InBlock.gif
InBlock.gif           
//创建一个WorkBook对象
InBlock.gif
            workBook = app.Workbooks.Add(missing);
InBlock.gif           
//得到WorkSheet对象
InBlock.gif
            workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
InBlock.gif
InBlock.gif           
int rowCount = grid.Items.Count + 1//DataTable行数+GirdHead(因为DataGrid头还有一例所以得加1)
InBlock.gif
            int colCount = grid.Columns.Count;//DataTable列数
InBlock.gif
InBlock.gif           
//利用二维数组批量写入
InBlock.gif
            string[,] arr = new string[rowCount, colCount];
InBlock.gif
InBlock.gif           
for (int j = 0; j < rowCount; j++)
ExpandedSubBlockStart.gifContractedSubBlock.gif           
...{
InBlock.gif               
for (int k = 0; k < colCount; k++)
ExpandedSubBlockStart.gifContractedSubBlock.gif               
...{
InBlock.gif                   
if (j == 0)
ExpandedSubBlockStart.gifContractedSubBlock.gif                   
...{
InBlock.gif                        arr[j, k]
= grid.Columns[k].HeaderText;
InBlock.gif
ExpandedSubBlockEnd.gif                    }

InBlock.gif                   
else
ExpandedSubBlockStart.gifContractedSubBlock.gif                   
...{
InBlock.gif                        arr[j, k]
= grid.Items[j - 1].Cells[k].Text.ToString();
ExpandedSubBlockEnd.gif                    }

ExpandedSubBlockEnd.gif                }

ExpandedSubBlockEnd.gif            }

InBlock.gif
InBlock.gif            range
= (Excel.Range)workSheet.Cells[1, 1]; //写入Exel的坐标
InBlock.gif
            range = range.get_Resize(rowCount, colCount);
InBlock.gif            range.Value
= arr;
InBlock.gif           
InBlock.gif            workBook.SaveAs(templetFilePath
+ fileName, missing, missing, missing, missing,
InBlock.gif
InBlock.gifmissing,Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
InBlock.gif
InBlock.gif           
if (workBook.Saved)
ExpandedSubBlockStart.gifContractedSubBlock.gif           
...{
InBlock.gif                workBook.Close(
null, null, null);
InBlock.gif                app.Workbooks.Close();
InBlock.gif                app.Quit();
ExpandedSubBlockEnd.gif            }

InBlock.gif
InBlock.gif           
if (range != null)
ExpandedSubBlockStart.gifContractedSubBlock.gif           
...{
InBlock.gif                System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
InBlock.gif                range
= null;
ExpandedSubBlockEnd.gif            }

InBlock.gif
InBlock.gif           
if (workSheet != null)
ExpandedSubBlockStart.gifContractedSubBlock.gif           
...{
InBlock.gif                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
InBlock.gif                workSheet
= null;
ExpandedSubBlockEnd.gif            }

InBlock.gif           
if (workBook != null)
ExpandedSubBlockStart.gifContractedSubBlock.gif           
...{
InBlock.gif                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
InBlock.gif                workBook
= null;
ExpandedSubBlockEnd.gif            }

InBlock.gif           
if (app != null)
ExpandedSubBlockStart.gifContractedSubBlock.gif           
...{
InBlock.gif                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
InBlock.gif                app
= null;
ExpandedSubBlockEnd.gif            }

InBlock.gif
InBlock.gif            GC.Collect();
//强制代码垃圾回收
InBlock.gif           
InBlock.gif           
//下载文件
InBlock.gif
            DownLoadFile(templetFilePath,fileName);
ExpandedBlockEnd.gif        }

None.gif
4.下载文件方法类
ExpandedBlockStart.gifContractedBlock.gif 
/**//// <summary>
InBlock.gif       
/// 下载服务器文件
InBlock.gif       
/// </summary>
InBlock.gif       
/// <param name="_FilePath">文件路径</param>
InBlock.gif       
/// <param name="_FileName">文件名</param>
ExpandedBlockEnd.gif       
/// <returns>返回 bool型</returns>

None.gifprivate  bool DownLoadFile(string _FilePath,string _FileName)
ExpandedBlockStart.gifContractedBlock.gif
...{
InBlock.gif
try
ExpandedSubBlockStart.gifContractedSubBlock.gif
...{
InBlock.gif                System.IO.FileStream fs
= System.IO.File.OpenRead(_FilePath+_FileName);
InBlock.gif               
byte[] FileData = new byte[fs.Length];
InBlock.gif                fs.Read(FileData,
0, (int)fs.Length);
InBlock.gif                Response.Clear();
InBlock.gif                Response.AddHeader(
"Content-Type", "application/ms-excel");
InBlock.gif               
string FileName = System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(_FileName));
InBlock.gif                Response.AddHeader(
"Content-Disposition", "inline;filename=" + System.Convert.ToChar(34) + FileName
InBlock.gif
InBlock.gif
+ System.Convert.ToChar(34));
InBlock.gif                Response.AddHeader(
"Content-Length", fs.Length.ToString());
InBlock.gif                Response.BinaryWrite(FileData);
InBlock.gif                fs.Close();
InBlock.gif               
//删除服务器临时文件
InBlock.gif
                System.IO.File.Delete(_FilePath+_FileName);
InBlock.gif                Response.Flush();
InBlock.gif                Response.End();
InBlock.gif
InBlock.gif
return true;
ExpandedSubBlockEnd.gif}

InBlock.gif
catch(Exception ex)
ExpandedSubBlockStart.gifContractedSubBlock.gif
...{
InBlock.gif                ex.Message.ToString();
InBlock.gif
return false;
ExpandedSubBlockEnd.gif}

ExpandedBlockEnd.gif}

None.gif
5.应用方法
None.gif
protected void btnExportToExcel_Click(object sender, EventArgs e)
ExpandedBlockStart.gifContractedBlock.gif       
...{
InBlock.gif           
InBlock.gif           
this.ExportToExcel(grdBudget,"油管厂发料记录.xls");//grdBudget 是DataGrid的ID
InBlock.gif
      
ExpandedBlockEnd.gif        }

转载于:https://www.cnblogs.com/pbwf/archive/2007/05/26/761094.html

  • 0
    点赞
  • 0
    收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页
评论
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值