c#中兼並excel表格的辦法示例。本站提示廣大學習愛好者:(c#中兼並excel表格的辦法示例)文章只能為提供參考,不一定能成為您想要的結果。以下是c#中兼並excel表格的辦法示例正文
有多個構造一樣的Excel,帶復雜表頭需求兼並為一個,且去掉多余的表頭數據,可以用COM組件來讀取每個Excel表格的Range來兼並到一個新的表格中。樣例如圖
有很多相反格式的表格,兼並代碼如下:
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(); } } }
以上就是本文的全部內容,希望對大家的學習有所協助,也希望大家多多支持。