当前位置:学院首页 >> 编程开发 >> C#/CSHARP >> C#合并多个结构一样的Excel

C#/CSHARP

C#合并多个结构一样的Excel

发布于 2008-08-20   次阅读 字体:

标签:C#合并多个结构一样的Excel   

合并代码如下:


using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
namespace ConsoleApplication20
{
    //添加引用-COM-MicroSoft Excel 11.0 Object Libery
    class Program
    {
        static void Main(string[] args)
        {
            //M为表格宽度标志(Excel中的第M列为最后一列),3为表头高度
            MergeExcel.DoMerge(new string[]
            {
                @"E:\excel\类型A\公司A.xls",
                @"E:\excel\类型A\公司B.xls"
            },
                @"E:\excel\类型A\合并测试.xls", "M", 3);
            MergeExcel.DoMerge(new string[]
            {
                @"E:\excel\类型B\统计表A.xls",
                @"E:\excel\类型B\统计表B.xls"
            },
                @"E:\excel\类型B\合并测试.xls", "I", 4);
        }

 

       
    }
    public class MergeExcel
    {
       
        Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
        //保存目标的对象
        Excel.Workbook bookDest = null;
        Excel.Worksheet sheetDest = null;
        //读取数据的对象
        Excel.Workbook bookSource = null;
        Excel.Worksheet sheetSource = null;


        string[] _sourceFiles = null;
        string _destFile = string.Empty;
        string _columnEnd = string.Empty;
        int _headerRowCount = 1;
        int _currentRowCount = 0;

        public MergeExcel(string[] sourceFiles,string destFile,string columnEnd,int headerRowCount)
        {
           
            bookDest = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
            sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
            sheetDest.Name = "Data";

            _sourceFiles = sourceFiles;
            _destFile = destFile;
            _columnEnd = columnEnd;
            _headerRowCount = headerRowCount;

        }
        /// <summary>
        /// 打开工作表
        /// </summary>
        /// <param name="fileName"></param>
        void OpenBook(string fileName)
        {
            bookSource = app.Workbooks._Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value
                , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                , Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            sheetSource = bookSource.Worksheets[1] as Excel.Worksheet;
        }
        /// <summary>
        /// 关闭工作表
        /// </summary>
        void CloseBook()
        {
            bookSource.Close(false, Missing.Value, Missing.Value);
        }
        /// <summary>
        /// 复制表头
        /// </summary>
        void CopyHeader()
        {
            Excel.Range range = sheetSource.get_Range("A1", _columnEnd + _headerRowCount.ToString());
            range.Copy(sheetDest.get_Range("A1",Missing.Value));
            _currentRowCount += _headerRowCount;
        }
        /// <summary>
        /// 复制数据
        /// </summary>
        void CopyData()
        {
            int sheetRowCount = sheetSource.UsedRange.Rows.Count;
            Excel.Range range = sheetSource.get_Range(string.Format("A{0}", _headerRowCount + 1), _columnEnd + sheetRowCount.ToString());
            range.Copy(sheetDest.get_Range(string.Format("A{0}", _currentRowCount + 1), Missing.Value));
            _currentRowCount += range.Rows.Count;
        }
        /// <summary>
        /// 保存结果
        /// </summary>
        void Save()
        {
            bookDest.Saved = true;
            bookDest.SaveCopyAs(_destFile);
        }
        /// <summary>
        /// 退出进程
        /// </summary>
        void Quit()
        {
            app.Quit();
        }
        /// <summary>
        /// 合并
        /// </summary>
        void DoMerge()
        {
            bool b = false;
            foreach (string strFile in _sourceFiles)
            {
                OpenBook(strFile);
                if (b == false)
                {
                    CopyHeader();
                    b = true;
                }
                CopyData();
                CloseBook();
            }
            Save();
            Quit();
        }
        /// <summary>
        /// 合并表格
        /// </summary>
        /// <param name="sourceFiles">源文件</param>
        /// <param name="destFile">目标文件</param>
        /// <param name="columnEnd">最后一列标志</param>
        /// <param name="headerRowCount">表头行数</param>
        public static void DoMerge(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
        {
            new MergeExcel(sourceFiles, destFile, columnEnd, headerRowCount).DoMerge();
        }
    }

}

网站地图