C#导出生成excel文件的方法小结(xml,html方式)

直接贴上代码,里面都有注释


代码如下:

/// <summary>

/// xml格式生成excel文件并存盘;
        /// </summary>
        /// <param name="page">生成报表的页面,没有传null</param>
        /// <param name="dt">数据表</param>
        /// <param name="TableTitle">报表标题,sheet1名</param>
        /// <param name="fileName">存盘文件名,全路径</param>
        /// <param name="IsDown">生成文件后是否提示下载,只有web下才有效</param>
        public static void CreateExcelByXml(System.Web.UI.Page page, DataTable dt, String TableTitle, string fileName, bool IsDown)
        {
            StringBuilder strb = new StringBuilder();
            strb.Append(" <html xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
            strb.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
            strb.Append("xmlns=\"");
            strb.Append(" <head> <meta http-equiv='Content-Type' content='text/html; charset=UTF-8'>");
            strb.Append(" <style>");
            strb.Append("body");
            strb.Append(" {mso-style-parent:style0;");
            strb.Append(" font-family:\"Times New Roman\", serif;");
            strb.Append(" mso-font-charset:0;");
            strb.Append(" mso-number-format:\"@\";}");
            strb.Append("table");
            //strb.Append(" {border-collapse:collapse;margin:1em 0;line-height:20px;font-size:12px;color:#222; margin:0px;}");
            strb.Append(" {border-collapse:collapse;margin:1em 0;line-height:20px;color:#222; margin:0px;}");
            strb.Append("thead tr td");
            strb.Append(" {background-color:#e3e6ea;color:#6e6e6e;text-align:center;font-size:14px;}");
            strb.Append("tbody tr td");
            strb.Append(" {font-size:12px;color:#666;}");
            strb.Append(" </style>");
            strb.Append(" <xml>");
            strb.Append(" <x:ExcelWorkbook>");
            strb.Append(" <x:ExcelWorksheets>");
            strb.Append(" <x:ExcelWorksheet>");
            //设置工作表 sheet1的名称
            strb.Append(" <x:Name>" + TableTitle + " </x:Name>");
            strb.Append(" <x:WorksheetOptions>");
            strb.Append(" <x:DefaultRowHeight>285 </x:DefaultRowHeight>");
            strb.Append(" <x:Selected/>");
            strb.Append(" <x:Panes>");
            strb.Append(" <x:Pane>");
            strb.Append(" <x:Number>3 </x:Number>");
            strb.Append(" <x:ActiveCol>1 </x:ActiveCol>");
            strb.Append(" </x:Pane>");
            strb.Append(" </x:Panes>");
            strb.Append(" <x:ProtectContents>False </x:ProtectContents>");
            strb.Append(" <x:ProtectObjects>False </x:ProtectObjects>");
            strb.Append(" <x:ProtectScenarios>False </x:ProtectScenarios>");
            strb.Append(" </x:WorksheetOptions>");
            strb.Append(" </x:ExcelWorksheet>");
            strb.Append(" <x:WindowHeight>6750 </x:WindowHeight>");
            strb.Append(" <x:WindowWidth>10620 </x:WindowWidth>");
            strb.Append(" <x:WindowTopX>480 </x:WindowTopX>");
            strb.Append(" <x:WindowTopY>75 </x:WindowTopY>");
            strb.Append(" <x:ProtectStructure>False </x:ProtectStructure>");
            strb.Append(" <x:ProtectWindows>False </x:ProtectWindows>");
            strb.Append(" </x:ExcelWorkbook>");
            strb.Append(" </xml>");
            strb.Append("");
            strb.Append(" </head> <body> ");
            strb.Append(" <table style=\"border-right: 1px solid #CCC;border-bottom: 1px solid #CCC;text-align:center;\"> <thead><tr>");
            //合格所有列并显示标题
            strb.Append(" <td style=\"text-align:center;background:#d3eeee;font-size:18px;\" colspan=\"" + dt.Columns.Count + "\" ><b>");
            strb.Append(TableTitle);
            strb.Append(" </b></td> ");
            strb.Append(" </tr>");
            strb.Append(" </thead><tbody><tr style=\"height:20px;\">");
            if (dt != null)
            {
                //写列标题
                int columncount = dt.Columns.Count;
                for (int columi = 0; columi < columncount; columi++)
                {
                    strb.Append(" <td style=\"width:110px;;text-align:center;background:#CCC;\"> <b>" + dt.Columns[columi] + " </b> </td>");
                }
                strb.Append(" </tr>");
                //写数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    strb.Append(" <tr style=\"height:20px;\">");
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        strb.Append(" <td style=\"width:110px;;text-align:center;\">" + dt.Rows[i][j].ToString() + " </td>");
                    }
                    strb.Append(" </tr>");
                }
            }
            strb.Append(" </tbody> </table>");
            strb.Append(" </body> </html>");

string ExcelFileName = fileName;
            //string ExcelFileName = Path.Combine(page.Request.PhysicalApplicationPath, path+"/guestData.xls");
            //报表文件存在则先删除
            if (File.Exists(ExcelFileName))
            {
                File.Delete(ExcelFileName);
            }
            StreamWriter writer = new StreamWriter(ExcelFileName, false);
            writer.WriteLine(strb.ToString());
            writer.Close();
            //如果需下载则提示下载对话框
            if (IsDown)
            {
                DownloadExcelFile(page, ExcelFileName);
            }
        }
---------
/// <summary>
        /// web下提示下载
        /// </summary>
        /// <param name="page"></param>
        /// <param name="filename">文件名,全路径</param>
        public static void DownloadExcelFile(System.Web.UI.Page page, string FileName)
        {
            page.Response.Write("path:" + FileName);
            if (!System.IO.File.Exists(FileName))
            {
                MessageBox.ShowAndRedirect(page, "文件不存在!", FileName);
            }
            else
            {
                FileInfo f = new FileInfo(FileName);
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + f.Name);
                HttpContext.Current.Response.AddHeader("Content-Length", f.Length.ToString());
                HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");
                HttpContext.Current.Response.ContentType = "application/octet-stream";
                HttpContext.Current.Response.WriteFile(f.FullName);
                HttpContext.Current.Response.End();
            }

}

需要cs类文件的可以去下载  点击下载

(0)

相关推荐

  • C#自定义导出数据到Excel的类实例

    本文实例讲述了C#自定义导出数据到Excel的类.分享给大家供大家参考.具体如下: C#自定义Excel操作类,可以用于将DataTable导出到Excel文件,从Excel文件读取数据. using System; using System.IO; using System.Data; using System.Collections; using System.Data.OleDb; using System.Web; using System.Web.UI; using System.We

  • C#数据导入/导出Excel文件及winForm导出Execl总结

    一.asp.net中导出Execl的方法: 在asp.net中导出Execl有两种方法,一种是将导出的文件存放在服务器某个文件夹下面,然后将文件地址输出在浏览器上:一种是将文件直接将文件输出流写给浏览器.在Response输出时,\t分隔的数据,导出execl时,等价于分列,\n等价于换行. 1.将整个html全部输出execl 此法将html中所有的内容,如按钮,表格,图片等全部输出到Execl中. 复制代码 代码如下: Response.Clear(); Response.Buffer= t

  • C#如何将DataTable导出到Excel解决方案

    最近,由于公司项目中需要将系统内用户操作的所有日志进行转存备份,考虑到以后可能还需要还原,所以最后决定将日志数据备份到Excel中. 下面是我项目当中Excel.cs这个类的全部代码,通过这个类可以很容易地将DataTable中的数据导入到Excel方法中. 首先,必须要下载NPOI.dll这个程序集, 类代码如下: 复制代码 代码如下: using System; using NPOI.HSSF; using NPOI.HPSF; using NPOI.HSSF.UserModel; usin

  • c#数据库与TXT导入导出的实例

    复制代码 代码如下: private void button1_Click(object sender, EventArgs e)              {                  if (openFileDialog1.ShowDialog() == DialogResult.OK)        {               using (FileStream fs = File.OpenRead(openFileDialog1.FileName))       {     

  • C# 各种导出的方法总结

    第一种:使用 Microsoft.Office.Interop.Excel.dll 首先需要安装 office 的 excel,然后再找到 Microsoft.Office.Interop.Excel.dll 组件,添加到引用. public void ExportExcel(DataTable dt) { if (dt != null) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Inter

  • C#导出数据到CSV文件的通用类实例

    本文实例讲述了C#导出数据到csv文件的通用类.分享给大家供大家参考.具体如下: 通过这个类可以很简单的定义数据格式,并导出到csv文件 //这里写了一个通用的类 using System; using System.Data; using System.Configuration; using System.Collections.Generic; using System.Web; using System.Web.Security; using System.Web.UI; using S

  • C#将html table 导出成excel实例

    复制代码 代码如下: public void ProcessRequest (HttpContext context) { string elxStr = "<table><tbody><tr><td>1</td><td>11</td></tr><tr><td>2</td><td>22</td></tr></tbody>

  • C#导入导出EXCEL文件的代码实例

    复制代码 代码如下: using System;using System.Data;using System.Data.OleDb; namespace ZFSoft.Joint{    public class ExcelIO    {        private int _ReturnStatus;        private string _ReturnMessage; /// <summary>        /// 执行返回状态        /// </summary&g

  • C#中把日志导出到txt文本的简单实例

    复制代码 代码如下: /// <summary>        /// 打日志        /// </summary>        /// <param name="log"></param> //首先还是要using system.io;             public void Write(ArrayList log)            {                      //将文件保存在桌面,文件名称为当前

  • C# WinForm导出Excel方法介绍

    .NET开发人员首选的方法,通过COM组件调用Office软件本身来实现文件的创建和读写,但是数据量较大的时候异常缓慢:如下代码所示已经做了优化,将一个二维对象数组赋值到一个单元格区域中(下面的代码中只能用于导出列数不多于26列的数据导出): Office PIA 复制代码 代码如下: public static void ExportToExcel(DataSet dataSet, string outputPath){    Excel.ApplicationClass excel = ne

随机推荐