asp.net生成导出word表单,导出excel;dataTable生成xls文件，返回前台下载;asp.net启动 ...

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

   //导出工程放线通知单   
        private void butn_exportDrawLinePermission_Click(object sender, EventArgs e)
        {
            string tempdotpath = Application.StartupPath + "\\报表文件";
            tempdotpath += "\\" + "规划放线通知单.dot";  //获取模板  


            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "Word Document(*.doc)|*.doc";
            sfd.DefaultExt = "Word Document(*.doc)|*.doc";
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                ExportDocDrawlineNotice(tempdotpath, sfd.FileName); //保存位置
            }
        }


 //生成word表单
        private void ExportDocDrawlineNotice(string dotfullpath, string detifile)
        {
            object oMissing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Word._Application oWord = new Microsoft.Office.Interop.Word.Application();
            oWord.Visible = false;
            object oTemplate = dotfullpath;

            Microsoft.Office.Interop.Word._Document oDoc = oWord.Documents.Add(ref oTemplate, ref oMissing, ref oMissing, ref oMissing);
            object[] oBookMark = new object[13];

            oBookMark[0] = "fxArea_length";
            oBookMark[1] = "fxbackgreenline";
            oBookMark[2] = "fxbuildaddress";
            oBookMark[3] = "fxbuildcount";
            oBookMark[4] = "fxbuildUnit";
            oBookMark[5] = "fxDMKZBG";
            oBookMark[6] = "fxdrawlinecompany";
            oBookMark[7] = "fxfloornumradius";
            oBookMark[8] = "fxheightdepth";
            oBookMark[9] = "fxJZBG";
            oBookMark[10] = "fxprjname";
            oBookMark[11] = "fxprjname2";
            oBookMark[12] = "fxOther";


            oDoc.Bookmarks.get_Item(ref oBookMark[4]).Range.Text = tb_fxbuildunit.Text;
            oDoc.Bookmarks.get_Item(ref oBookMark[2]).Range.Text = tb_fxbuildaddress.Text;
            oDoc.Bookmarks.get_Item(ref oBookMark[10]).Range.Text = tb_fxprjname.Text;
            oDoc.Bookmarks.get_Item(ref oBookMark[11]).Range.Text = tb_fxprjname2.Text;
            oDoc.Bookmarks.get_Item(ref oBookMark[6]).Range.Text = tb_fxlinedrawingcompany.Text;
            oDoc.Bookmarks.get_Item(ref oBookMark[0]).Range.Text = tb_fxArea_length.Text;
            oDoc.Bookmarks.get_Item(ref oBookMark[7]).Range.Text = tb_fxFloorCount_radius.Text;
            oDoc.Bookmarks.get_Item(ref oBookMark[8]).Range.Text = tb_fxHeight_depth.Text;
            oDoc.Bookmarks.get_Item(ref oBookMark[3]).Range.Text = tb_fxBuildCount.Text;
            oDoc.Bookmarks.get_Item(ref oBookMark[1]).Range.Text = tb_fxbackgreenline.Text;
            oDoc.Bookmarks.get_Item(ref oBookMark[5]).Range.Text = tb_fxDMKZBG.Text;
            oDoc.Bookmarks.get_Item(ref oBookMark[9]).Range.Text = tb_fxJZBG.Text;
            oDoc.Bookmarks.get_Item(ref oBookMark[12]).Range.Text = this._programName;

            object filename = detifile;
            oDoc.SaveAs(ref filename, ref oMissing, ref oMissing, ref oMissing,
            ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
            ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
            ref oMissing, ref oMissing);
            oDoc.Close(ref oMissing, ref oMissing, ref oMissing);
            //关闭word  
            oWord.Quit(ref oMissing, ref oMissing, ref oMissing);
        }

导出word,方法2：

http://www.cnblogs.com/m-cnblogs/archive/2011/07/28/2708734.html

导出excel

ExportExcel("application/ms-excel", "测量标志报表.xls");



private void ExportExcel(string FileType, string FileName)
    {
        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
        Response.ContentType = FileType;
        this.EnableViewState = false;
        StringWriter tw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(tw);
        GridView1.RenderControl(hw);
        Response.Write(tw.ToString());
        Response.Flush();
        Response.End(); 
    }

二、dataTable生成xls文件，返回前台下载

算法：前台ajax发送查询xmldata<query residenttype="">到后台，后台解析xmldata,查询数据库，得到dataTable. dataTable转成xls文件，存在服务端某路径，

将xls完整路径返回给前台。

前台接ajax收到xls路径，将隐藏的a href=url, a.click(); 触发下载。

前台：发送查询请求

/*导出住户信息
*/
function exportsResidents(){
    var url="../AddedHandlers/ResidentInfo.ashx?type=exportResidents&time="+new Date().toString();
   
    //获取链接，然后下载 
    var pAjax=new ajax();
    if(resiPager.getQueryData()==""){ alert("记录数为0,无法导出表格");return;}
    
    pAjax.post(url,resiPager.getQueryData(),false,function(info){
        var xlsUrl=info.responseText;
        document.getElementById("xlsLink").setAttribute("href",xlsUrl);
        document.getElementById("xlsLink").click();//下载xls文件
    });
}

Handler

        else if (context.Request["type"] == "exportResidents") 
        {
            //获取查询字符串
            Stream streamInfo = context.Request.InputStream;
            StreamReader sr = new StreamReader(streamInfo, System.Text.Encoding.UTF8);
            string xmlstr = sr.ReadToEnd();
            
            //返回文件的下载链接
            context.Response.ContentType = "text/plain";
            string xlsPath=ResidentInfo.GetResidentsXlsUrl(xmlstr);
            context.Response.Write(xlsPath);
        }

BLL层

 public static string GetResidentsXlsUrl(string queryData) {
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(queryData);
            if (xmlDoc.DocumentElement.GetAttribute("isAll") == "true")//导出所有住户
            {
                string sqlString = "select ResidentName as 住户名,IdentityNum as 身份证号码,GenderType as 性别,Education as 学历,ResidentType as 人员属性,LiveType as 居住类型," +
                "SolidPhone as 固定电话,CellPhone as 移动电话,RelationType as 与户主关系,PeopleType as 民族,MarrigeType as 是否已婚,OldName as 曾用名,Health as 健康状况,Hometown as 籍贯,WorkUnit as 工作单位,WorkDuty as 职务," +
                "HukouPlace as 户口所在地,HukouType as 户口类型,IsPrisoner as 是否为重点保护人员,IsSingleOld as 是否为空巢老人,IsDisease as 是否为重点疾病人员,IsWillParent as 是否为计生人员" +
                " from tbResident";
                DataTable dt= DAL.DbHelperAcc.Query(sqlString).Tables[0];
                string dir = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["excelWorkSheetPath"]);
                string savePath = dir + "所有住户.xls";
                if (File.Exists(savePath)) { File.Delete(savePath); }//如果已经存在文件，则删除原有文件
                Utilities.DataTableToExcel(dt, savePath);
                string virPath = ConfigurationManager.AppSettings["excelWorkSheetPath"] + "所有住户.xls";
                return virPath;
            }
            else {//高级查询的住户 
                DataTable dt = AdvancedQueryGetExportTable(queryData);
                string dir = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["excelWorkSheetPath"]);
                string savePath = dir + "查询结果.xls";
                if (File.Exists(savePath)) { File.Delete(savePath); }//如果已经存在文件，则删除原有文件
                Utilities.DataTableToExcel(dt, savePath);
                string virPath = ConfigurationManager.AppSettings["excelWorkSheetPath"] + "查询结果.xls";
                return virPath;
            }
        }

Utilities的DataTableToExcel方法：

      /// <summary>
        /// dataTable存成excel表格
        ///  备注：如果strFileName处已经存在文件,则报错
        /// </summary>
        /// <param name="dtSource">dataTable</param>
        /// <param name="strFileName">在服务端完整路径</param>
        public static  void DataTableToExcel(System.Data.DataTable dtSource, string strFileName)
        {
            int rowNum = dtSource.Rows.Count;
            int columnNum = dtSource.Columns.Count;
            int rowIndex = 1;
            int columnIndex = 0;

            if (dtSource == null || string.IsNullOrEmpty(strFileName))
            {
                return;
            }
            if (rowNum > 0)
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                xlApp.DefaultFilePath = "";
                xlApp.DisplayAlerts = true;
                xlApp.SheetsInNewWorkbook = 1;
                Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
                //将DataTable的列名导入Excel表第一行
                foreach (DataColumn dc in dtSource.Columns)
                {
                    columnIndex++;
                    xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
                }
                //将DataTable中的数据导入Excel中
                for (int i = 0; i < rowNum; i++)
                {
                    rowIndex++;
                    columnIndex = 0;
                    for (int j = 0; j < columnNum; j++)
                    {
                        columnIndex++;
                        xlApp.Cells[rowIndex, columnIndex] = dtSource.Rows[i][j].ToString();
                    }
                }
                xlBook.SaveCopyAs(strFileName);
                xlApp = null;
                xlBook = null;
            }
        }

三、asp.net启动excel错误 80070005

原因：asp.net中执行以下语句

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

解决方式（适用于xp）:让asp.net有权访问excel

1.在“运行中”，启动 dcomcnfg.exe。找到设置Excel启动权限的地方

2.标识：设置为交互式用户

3.在”启动和激活权限中“添加”asp.net“

4.在”访问权限“中，添加asp.net

完成上述步骤，asp.net即可访问excel.

另一种解决方式：在webconfig中写入用户名密码，服务端启动excel时，通过用户名和密码访问。(这种方式更安全)

四、excel-xls columnName 不能改变

columnName A,B,C,行头1,2,3不是document的一部分.是用于定位Cell的。列标题在A1,B1,C1,...N1设置.

5.读写excel的开源利器NPOI

http://npoi.codeplex.com/documentation

codeplex是微软的开源项目网站. 很实用

6.设置excel Cell的数据类型

   //将DataTable中的数据导入Excel中
                xlApp.Cells.NumberFormat = "@";//设置所有cells格式为字符串。设置类型要在个Cell赋值之前
                for (int i = 0; i < rowNum; i++)
                {
                    rowIndex++;
                    columnIndex = 0;
                    for (int j = 0; j < columnNum; j++)
                    {
                        columnIndex++;
                        xlApp.Cells[rowIndex, columnIndex] = dtSource.Rows[i][j].ToString();
                    }
                }