Asp.Net 导出Excel打印

现在好像很多地方都用到了将数据导出为Excel或Word格式,以及打印
百度了一下,发现有几种方法,如下:

导出Excel的代码:
            System.IO.StringWriter SW = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter HTW = new System.Web.UI.HtmlTextWriter(SW);

            //Page为要导出的对象,当前是Page,如果是DataGrid,DataList等都可以
            Page.Response.Buffer = true;
            Page.Response.Clear();
            Page.Response.ClearContent();
            Page.Response.ClearHeaders();
            Page.Response.ContentType = "application nd.ms-excel";
            //Response.ContentType是输出流的 HTTP MIME 类型
            //Response.ContentType     --- word文件
            //application nd.ms-excel --- excel文件

            Response.Charset = "gb2312";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            Response.AddHeader("Content-Disposition", "attachment;filename=Out.xls");
            //attachment --- 作为附件下载
            //inline --- 在线打开
            //filename如过是中文,则可以用HttpUtility.UrlEncode(fileName,System.Text.Encoding.UTF8)
            //进行进行编码,以解决文件名乱码的问题
            //ImportWord.RenderControl(HTW);
            Page.RenderControl(HTW);
            Page.ApplyStyleSheetSkin(Page);

            Response.Write(SW.ToString());
            Response.Flush();
            Response.Close();

一丶直接利用IE和CSS进行打印
用CSS来控制好页面的输入格式
<input type="button" value="print">在页面上添加"打印"按钮就OK
主要是通过JavaScript脚本语言调用window.print()方法来实现打印
但是这样的话它打印的是整个页面,包括上面所有的控件如"打印"按钮也会被打印出来了,而且不好进行"打印设置"和"打印预览"

二丶调用IE内置的WebBrowser控件实现打印
WebBrowser控件是IE内置的浏览器控件,是ActiveX控件,无需用户下载,可以通过调用WebBrowser控件的ExecWB方法实现打印,代码如下:
            <OBJECT id="WebBrowser" classid="CLSID:8856F961-340A-11D0-A96B-00C04FD705A2" height="0" width="0" class="Noprint"></OBJECT>
                <input type="button" value="打印" onclick="document.all.WebBrowser.ExecWB(6,1)" class="common_button"/>
                <input type="button" value="页面设置" onclick="document.all.WebBrowser.ExecWB(8,1)" class="common_button"/>
                <input type="button" value="打印预览" onclick="document.all.WebBrowser.ExecWB(7,1)" class="common_button" />

但是打印的时候整个页面都会被打印出来,因些要控件一下样式
<!--打印时不打印的区域-->
    <style type="text/css" media="print">
    .noprint{display : none }
    </style>
然后把对应的不要显示的内容 class设置为noprint 如:class="noprint"

有些时候有的不想要页眉和页脚的,清空页码:
<script language="JavaScript">
var hkey_root,hkey_path,hkey_key
hkey_root="HKEY_CURRENT_USER"
hkey_path="\\Software\\Microsoft\\Internet Explorer\\PageSetup\\"
//设置网页打印的页眉页脚为空
function pagesetup_null(){
try{
var RegWsh = new ActiveXObject("WScript.Shell")
hkey_key="header"
RegWsh.RegWrite(hkey_root+hkey_path+hkey_key,"")
hkey_key="footer"
RegWsh.RegWrite(hkey_root+hkey_path+hkey_key,"")
}catch(e){}
}
//设置网页打印的页眉页脚为默认值
function pagesetup_default(){
try{
var RegWsh = new ActiveXObject("WScript.Shell")
hkey_key="header"
RegWsh.RegWrite(hkey_root+hkey_path+hkey_key,"&w&b页码,&p/&P")
hkey_key="footer"
RegWsh.RegWrite(hkey_root+hkey_path+hkey_key,"&u&b&d")
}catch(e){}
}
</script>
<input type="button" value="清空页码" onclick=pagesetup_null()>
<input type="button" value="恢复页码" onclick=pagesetup_default()>

三丶利用.NET组件实现Web打印,代码如下:
//创建新的XslTransform对象
XslTransform xslt=new XslTransform();
//从XSL文件中导入样式表
xslt.Load(Server.MapPath("WorkersToHTML.xsl"));
//创建新的XPathDocument对象,并导入XML文件
XPathDocument XDoc=new XPathDocument(Server.MapPath("Workers.XML"));
//创建新的XmlTexWriter对象用于导出HTML格式文件
XmlTexWriter writer=new XmlTexWriter(Server.MapPath("Workers.html"));
System.Text.Encoding.UTF8;
//进行实际的XSLT转换操作
xslt.Transform(XDOC,null,writer);
//操作完成后关闭XmlTextWriter对象
writer.Close();

这种方法主要用于安全性考虑,不能直接连接数据库存时,利用XML进行中间的交互,适用数量小的应用

四丶下面这种方法是我搜索到我我也不知道叫什么,不过控制比较灵活,缺点就是要写很多代码
代码如下:
private Excel.ApplicationClass _x; 
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;
Excel._Worksheet sheet;

public void SaveExcel()
        {
            _x = new Excel.ApplicationClass();
            _x.UserControl = false;
            Excel.WorkbookClass wb = (Excel.WorkbookClass)_x.Workbooks.Add (System.Reflection.Missing.Value);
            try
            {
                //创建一个excel的应用对象
                oXL = new Excel.Application();
                oXL.Visible = true;
                //创建一个新的workboot
                oWB = (Excel._Workbook)(oXL.Workbooks.Add(true));
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;
                //往单元格中填入内容
                oSheet.Cells[1, 1] = "姓名";
                oSheet.Cells[1, 2] = txtName.Text;
                oSheet.Cells[1, 3] = "性别";
                oSheet.Cells[1, 4] = lblsex.Text;
                oSheet.Cells[2, 1] = "年龄";
                oSheet.Cells[2, 2] = txtAge.Text;
                oSheet.Cells[2, 3] = "家庭住址";
                oSheet.Cells[2, 4] = txtAddress.Text;
                oSheet.Cells[3, 1] = "国籍";
                oSheet.Cells[3, 2] = txtCountry.Text;
                 oSheet.Cells[3, 3] = "";
                oSheet.Cells[3, 4] ="";         

                //格式化A1到D1范围内文字的大小为"9",字体为"粗字体",对齐方式为"中间对齐"
                oSheet.get_Range("A1", "D3").Font.Size = 12;
                //oSheet.get_Range("A1", "D3").Font.Bold = true;
                oSheet.get_Range("A1", "D3").HorizontalAlignment = Excel.Constants.xlLeft;//居左

                //该范围内设置外面一层边框
                oSheet.get_Range("A1", "D20").BorderAround(Excel.XlLineStyle.xlContinuous,      Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());

                //该范围内设置里面所有单元格的边框
                oSheet.get_Range("A1", "D3").Borders.Weight = Excel.XlBorderWeight.xlThin;
                oSheet.get_Range("A1", "D3").Borders.Color = System.Drawing.Color.Black.ToArgb();
                 oSheet.get_Range("A1", "D3").HorizontalAlignment = Excel.Constants.xlLeft;//居左

                //合并该范围内的所有单位格为一个单元格
                oSheet.get_Range("B3", "D3").Merge(true);    //合并列
                oSheet.get_Range("A1", "A2").MergeCells = true; //合并行

                //范围内单元格自动适应文字的长短
                oRng = oSheet.get_Range("A1", "D3");
                oRng.EntireColumn.AutoFit();

                //范围内单元格添加背景颜色
                oSheet.get_Range("A1", "D3").Cells.Interior.Color = System.Drawing.Color.BurlyWood.ToArgb();
                oSheet.get_Range("A1", "D3").Cells.Interior.Pattern = Excel.XlBackground.xlBackgroundAutomatic;
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
            wb.Saved = true;
            _x.ActiveWorkbook.SaveCopyAs("MemberInfoTable.xls");

          //生成模板实现打印预览以及打印
            object missing = System.Reflection.Missing.Value;
            _x.Workbooks.Close();
            _x.Workbooks[1].Protect(Type.Missing, true, true);
            oSheet.Protect(missing, missing, missing, missing, true, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, false); //生成模板
            this.oSheet.SaveAs("CorpsMemberInfoTable.xls",missing,missing,missing,missing,missing,missing,missing,missing,missing);
            oSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait; //设置为纵向打印
            oSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;       //设置打印字为A4纸张
            oSheet.PrintPreview(true);            //打印预览

            _x.ActiveWorkbook.PrintOut(missing, missing, missing, missing, missing, missing, missing, missing);
            _x.Workbooks.Close();
            _x.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject((object)_x);
            System.GC.Collect();
      }
这个方法,就是代码写得多,好处就是可以灵活的控制,建议对格式要求严格时使用
 

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/zhaili1978/archive/2009/07/02/4315438.aspx

原文地址:https://www.cnblogs.com/luluping/p/1523988.html