程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> C語言 >> 關於C語言 >> C#合並多個結構一樣的Excel

C#合並多個結構一樣的Excel

編輯:關於C語言

有多個結構一樣的Excel,帶復雜表頭需要合並為一個,且去掉多余的表頭數據,可以用COM組件來讀取每個Excel表格的Range來合並到一個新的表格中。樣例如圖

有很多相同格式的表格,合並代碼如下:

1.using  System;
2.using  System.Collections.Generic;
3.using  System.Text;
4.using  System.Reflection;
5.using  Excel = Microsoft.Office.Interop.Excel;
6.namespace  ConsoleApplication20
7.{
8.     //添加引用-COM-MicroSoft Excel 11.0 Object Libery
9.     class  Program
10.    {
11.         static   void  Main( string [] args)
12.        {
13.             //M為表格寬度標志(Excel中的第M列為最後一列),3為表頭高度
14.            MergeExcel.DoMerge( new   string []
15.            {
16.                @ "E:\excel\類型A\公司A.xls" ,
17.                @ "E:\excel\類型A\公司B.xls"
18.            },
19.                @ "E:\excel\類型A\合並測試.xls" ,  "M" , 3);
20.            MergeExcel.DoMerge( new   string []
21.            {
22.                @ "E:\excel\類型B\統計表A.xls" ,
23.                @ "E:\excel\類型B\統計表B.xls"
24.            },
25.                @ "E:\excel\類型B\合並測試.xls" ,  "I" , 4);
26.        }
27.
28.
29.
30.
31.    }
32.     public   class  MergeExcel
33.    {
34.
35.        Excel.Application app =  new  Microsoft.Office.Interop.Excel.ApplicationClass();
36.         //保存目標的對象
37.        Excel.Workbook bookDest =  null ;
38.        Excel.Worksheet sheetDest =  null ;
39.         //讀取數據的對象
40.        Excel.Workbook bookSource =  null ;
41.        Excel.Worksheet sheetSource =  null ;
42.
43.
44.         string [] _sourceFiles =  null ;
45.         string  _destFile =  string .Empty;
46.         string  _columnEnd =  string .Empty;
47.         int  _headerRowCount = 1;
48.         int  _currentRowCount = 0;
49.
50.         public  MergeExcel( string [] sourceFiles, string  destFile, string  columnEnd, int  headerRowCount)
51.        {
52.
53.            bookDest = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
54.            sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value)  as  Excel.Worksheet;
55.            sheetDest.Name =  "Data" ;
56.
57.            _sourceFiles = sourceFiles;
58.            _destFile = destFile;
59.            _columnEnd = columnEnd;
60.            _headerRowCount = headerRowCount;
61.
62.        }
63.         /// <summary>
64.         /// 打開工作表
65.         /// </summary>
66.         /// <param name="fileName"></param>
67.         void  OpenBook( string  fileName)
68.        {
69.            bookSource = app.Workbooks._Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value
70.                , Missing.Value, Missing.Value, Missing.Value, Missing.Value
71.                , Missing.Value, Missing.Value, Missing.Value, Missing.Value);
72.            sheetSource = bookSource.Worksheets[1]  as  Excel.Worksheet;
73.        }
74.         /// <summary>
75.         /// 關閉工作表
76.         /// </summary>
77.         void  CloseBook()
78.        {
79.            bookSource.Close( false , Missing.Value, Missing.Value);
80.        }
81.         /// <summary>
82.         /// 復制表頭
83.         /// </summary>
84.         void  CopyHeader()
85.        {
86.            Excel.Range range = sheetSource.get_Range( "A1" , _columnEnd + _headerRowCount.ToString());
87.            range.Copy(sheetDest.get_Range( "A1" ,Missing.Value));
88.            _currentRowCount += _headerRowCount;
89.        }
90.         /// <summary>
91.         /// 復制數據
92.         /// </summary>
93.         void  CopyData()
94.        {
95.             int  sheetRowCount = sheetSource.UsedRange.Rows.Count;
96.            Excel.Range range = sheetSource.get_Range( string .Format( "A{0}" , _headerRowCount + 1), _columnEnd + sheetRowCount.ToString());
97.            range.Copy(sheetDest.get_Range( string .Format( "A{0}" , _currentRowCount + 1), Missing.Value));
98.            _currentRowCount += range.Rows.Count;
99.        }
100.         /// <summary>
101.         /// 保存結果
102.         /// </summary>
103.         void  Save()
104.        {
105.            bookDest.Saved =  true ;
106.            bookDest.SaveCopyAs(_destFile);
107.        }
108.         /// <summary>
109.         /// 退出進程
110.         /// </summary>
111.         void  Quit()
112.        {
113.            app.Quit();
114.        }
115.         /// <summary>
116.         /// 合並
117.         /// </summary>
118.         void  DoMerge()
119.        {
120.             bool  b =  false ;
121.             foreach  ( string  strFile  in  _sourceFiles)
122.            {
123.                OpenBook(strFile);
124.                 if  (b ==  false )
125.                {
126.                    CopyHeader();
127.                    b =  true ;
128.                }
129.                CopyData();
130.                CloseBook();
131.            }
132.            Save();
133.            Quit();
134.        }
135.         /// <summary>
136.         /// 合並表格
137.         /// </summary>
138.         /// <param name="sourceFiles">源文件</param>
139.         /// <param name="destFile">目標文件</param>
140.         /// <param name="columnEnd">最後一列標志</param>
141.         /// <param name="headerRowCount">表頭行數</param>
142.         public   static   void  DoMerge( string [] sourceFiles,  string  destFile,  string  columnEnd,  int  headerRowCount)
143.        {
144.             new  MergeExcel(sourceFiles, destFile, columnEnd, headerRowCount).DoMerge();
145.        }
146.    }
147.
148.}
149.
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved