.Net源码论坛 [ 繁体中文 ]

返回列表 12345678» / 16
发新话题 回复该主题

套用模板输出Excel,并对数据进行分页 [复制链接]

楼主
引用:通过Com 引用 Microsoft Excel 5.0 对象程序库,引用后 bin 文件夹中会出现 Interop.Excel.dll ,Microsoft.Vbe.Interop.dll , Office.dll 三个文件。
//=====================ExcelHelper(套用模板输出Excel,并对数据进行分页)==============
using System;
using System.IO;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using cfg = System.Configuration;
using Excel;

namespace ExcelHelper
{
    
    ///   <summary>
    /// 功能说明:套用模板输出Excel,并对数据进行分页
    ///   </summary>
    public class ExcelHelper
    {
        protected string templetFile = null;
        protected string outputFile = null;
        protected object missing = Missing.Value;

        ///   <summary>
        /// 构造函数,需指定模板文件和输出文件完整路径
        ///   </summary>
        ///   <param name="templetFilePath"> Excel模板文件路径 </param>
        ///   <param name="outputFilePath"> 输出Excel文件路径 </param>
        public ExcelHelper(string templetFilePath, string outputFilePath)
        {
            if (templetFilePath == null)
                throw new Exception(" Excel模板文件路径不能为空! ");

            if (outputFilePath == null)
                throw new Exception(" 输出Excel文件路径不能为空! ");

            if (!File.Exists(templetFilePath))
                throw new Exception(" 指定路径的Excel模板文件不存在! ");

            this.templetFile = templetFilePath;
            this.outputFile = outputFilePath;

        }
        /**/
        ///   <summary>
        /// 将DataTable数据写入Excel文件(套用模板并分页)
        ///   </summary>
        ///   <param name="dt"> DataTable </param>
        ///   <param name="rows"> 每个WorkSheet写入多少行数据 </param>
        ///   <param name="top"> Excel中行索引 </param>
        ///   <param name="left"> Excel中列索引 </param>
        ///   <param name="sheetPrefixName"> WorkSheet前缀名,比如:前缀名为“Sheet”,那么WorkSheet名称依次为“Sheet-1,Sheet-2” </param>
        public void DataTableToExcel(System.Data.DataTable dt, int rows, int top, int left, string sheetPrefixName)
        {
            int rowCount = dt.Rows.Count;         // 源DataTable行数
            int colCount = dt.Columns.Count;     // 源DataTable列数
            int sheetCount = this.GetSheetCount(rowCount, rows);     // WorkSheet个数
            DateTime beforeTime;
            DateTime afterTime;

            if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
                sheetPrefixName = " Sheet ";

            // 创建一个Application对象并使其可见
            beforeTime = DateTime.Now;
            Excel.Application app = new Excel.ApplicationClass();
            app.Visible = true;
            afterTime = DateTime.Now;

            // 打开模板文件,得到WorkBook对象 (网上都是13个参数,现在变成14个参数了)
            Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
                               missing, missing, missing, missing, missing, missing, missing, missing, missing);

            // 得到WorkSheet对象
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

            // 复制sheetCount-1个WorkSheet对象
            for (int i = 1; i < sheetCount; i++)
            {
                ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets);
            }


            for (int i = 1; i <= sheetCount; i++)
            {
                int startRow = (i - 1) * rows;         // 记录起始行索引
                int endRow = i * rows;             // 记录结束行索引

                // 若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数
                if (i == sheetCount)
                    endRow = rowCount;

                // 获取要写入数据的WorkSheet对象,并重命名
                Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                sheet.Name = sheetPrefixName + " - " + i.ToString();

                // 将dt中的数据写入WorkSheet
                for (int j = 0; j < endRow - startRow; j++)
                {
                    for (int k = 0; k < colCount; k++)
                    {
                        sheet.Cells[top + j, left + k] = dt.Rows[startRow + j][k].ToString();
                    }
                }

                //// ====================写文本框数据(有错误注释掉了)=============
                //Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes(" txtAuthor ");
                //Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes(" txtDate ");
                //Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes(" txtVersion ");

                //txtAuthor.Text = " KLY.NET的Blog ";
                //txtDate.Text = DateTime.Now.ToShortDateString();
                //txtVersion.Text = " 1.0.0.0 ";


                sheet.Cells[rows + 1, left + 1] = "总计:10000";
                sheet.Cells[rows + 1, left + 5] = "编码:10000";

            }

            // 输出Excel文件并退出
            try
            {  

                //(网上是11个参数,现在是12个参数)
                workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
                workBook.Close(null, null, null);
                app.Workbooks.Close();
                app.Application.Quit();
                app.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

                workSheet = null;
                workBook = null;
                app = null;

                GC.Collect();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                Process[] myProcesses;
                DateTime startTime;
                myProcesses = Process.GetProcessesByName(" Excel ");

                // 得不到Excel进程ID,暂时只能判断进程启动时间
                foreach (Process myProcess in myProcesses)
                {
                    startTime = myProcess.StartTime;

                    if (startTime > beforeTime && startTime < afterTime)
                    {
                        myProcess.Kill();
                    }
                }
            }

        }

        /**/
        ///   <summary>
        /// 获取WorkSheet数量
        ///   </summary>
        ///   <param name="rowCount"> 记录总行数 </param>
        ///   <param name="rows"> 每WorkSheet行数 </param>
        private int GetSheetCount(int rowCount, int rows)
        {
            int n = rowCount % rows;         // 余数

            if (n == 0)
                return rowCount / rows;
            else
                return Convert.ToInt32(rowCount / rows) + 1;
        }
    }
}

使用示例:
public partial class _Default : System.Web.UI.Page
{

    
    ExcelHelper.ExcelHelper excel =null;
    protected void Page_Load(object sender, EventArgs e)
    {
        excel = new ExcelHelper.ExcelHelper(Server.MapPath("tempt/BJStock.xls"), Server.MapPath("ExcelFile/test.xls"));
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        excel.DataTableToExcel(DbHelperSQL.DbHelperSQL.GetDataTable("select * from excel"), 2, 1, 1, "Sheet");
        Response.Redirect("ExcelFile/test.xls");
    }
}
附件:您需要登录才可以下载或查看附件。没有帐号? 注册

    已有3评分我要评分查看所有评分

    本主题由 超级管理员 自由极光 于 6/4/2013 2:18:50 PM 执行 审核帖子 操作
    分享 转发
    http://blog.163.com/chy2z/
    TOP
    沙发

    顶一下
    TOP
    板凳

    非常不错
    南京网站建设 http://www.025-web.com
    TOP
    地板

    谢谢1111
    TOP
    五楼

    谢谢
    TOP
    六楼

    学习

    学习中,不知word能否也能实现同样的效果?
    TOP
    七楼

    顶一下!
    TOP
    八楼

    hao bucuo
    TOP
    九楼

    学习一下,谢谢!
    TOP
    十楼

    真事太棒了!
    TOP
    发新话题 回复该主题