C # merge Excel files

Source: Internet
Author: User
Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;
Using Excel = Microsoft. Office. Interop. Excel;
Using System. Reflection;
Namespace ExcelOp
{
Class Program
{
Static void Main (string [] args)
{
String [] files = {"c: \ Furong district .xls", "c: \ Tianxin district .xls "};
Mege m = new Mege (files, "c: \ aaa.xls", "C", 1 );
Console. WriteLine ("Start Merge ");
M. DoMerge ();
Console. WriteLine ("End Merge ");
Console. ReadLine ();
}
}
Class Mege
{
Excel. Application app = new Microsoft. Office. Interop. Excel. ApplicationClass ();
// Destination book
Excel. Workbook bookDest = null;
Excel. Worksheet sheetDest = null;
// Source book
Excel. Workbook bookSource = null;
Excel. Worksheet sheetSource = null;
// Source files path
String [] sourceFiles = null;
// Destination file
String destFile = string. Empty;
// End column eg: A-C c is the end column
String columnEnd = string. Empty;
// The header rows 'count
Int headerRowCount = 1;
// Which row the pointer pointed
Int currentRowCount = 0;

Public Mege (string [] sFiles, string dFile, string cEnd, int hCount)
{
// Create a new excel file, sheet1 sheet2 and sheet3 work sheet will be created
BookDest = (Excel. WorkbookClass) app. Workbooks. Add (Missing. Value );
// Create a new work sheet
SheetDest = bookDest. Worksheets [1] as Excel. Worksheet;
// Or we can create a new work sheet like:
/* SheetDest = bookDest. Worksheets. Add (Missing. Value, Missing. Value) as Excel. Worksheet;
SheetDest. Name = "Sheet4 ";*/
SourceFiles = sFiles;

DestFile = dFile;

ColumnEnd = cEnd;

HeaderRowCount = hCount;
}

Protected void OpenBook (string filename)
{
// Open the source excel file
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 );
// Open the sheet in the source workbook
SheetSource = bookSource. Worksheets [2] as Excel. Worksheet;
}
Protected void CloseBook ()
{
// Close the excel file
BookSource. Close (false, Missing. Value, Missing. Value );
}
Protected void CopyHeader ()
{
// Get the range eg: from A1 to C5
Excel. Range range = sheetSource. get_Range ("A1", columnEnd + headerRowCount. ToString ());
// Copy the sheet header from source excel file
Range. Copy (sheetDest. get_Range ("A1", Missing. Value ));
// Move the record pointer
CurrentRowCount + = headerRowCount;
}
Protected void CopyData ()
{
// Compute the Row Count Of the Sheet
Int sheetRowCount = sheetSource. UsedRange. Rows. Count;
// Get the Rows that has Record
Excel. Range range = sheetSource. get_Range (String. Format ("A {0}", headerRowCount + 1), columnEnd + sheetRowCount );
// Copy the record to destination excel sheet
Range. Copy (sheetDest. get_Range (String. Format ("A {0}", currentRowCount + 1), Missing. Value ));
// Move the record pointer
CurrentRowCount + = sheetRowCount;
}
Protected void Save ()
{
// Sace the destination excel file
BookDest. Saved = true;
BookDest. SaveCopyAs (destFile );
}
/// <Summary>
/// Exit the process
/// </Summary>
Protected void Quit ()
{
// Current application quit
App. Quit ();
}

Public void DoMerge ()
{
// Add sheet header only once
Bool B = false;
// Iteration the source files
Foreach (string strFile in sourceFiles)
{
// Open the excel file
OpenBook (strFile );
If (B = false)
{
CopyHeader ();
B = true;
}
// Copy the data
CopyData ();
// Close the excel file
CloseBook ();
}
Save ();
Quit ();
}

}
}

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.