C#實現Excel跨文件多SHEET合並計算
老婆所在的公司有3個分公司， 每個分公司要做一份財務的報表。3個分公司的模板是一樣，總公司要將這3個分公司對應的SHEET的對應單元格合並相加。上級給了一個Excel文件的模板，這個模板文件真是復雜，共有四五十個SHEET，每個SHEET中還有兩個獨立的表格。表格中有大量的公式存在。（估計有上百個公式吧）。有公式的地方都進行了加密。
最開始將Excel以異構數據庫進行處理。但對於一個SHEET中只有一個表格還可以，如果一個SHEET有多個表格，讀取到DATATABLE時直接出錯。沒辦法，只好用下面的方式進行處理。
更多文件請參見：
http://hi.csdn.Net/xjzdr
以下是CS文件：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
//using Excel;
using System.Reflection;
namespace TSSH
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
Microsoft.Office.Interop.Excel.Workbook wb1;
System.Collections.ArrayList SheetList = new System.Collections.ArrayList();
Microsoft.Office.Interop.Excel._Worksheet ws1;
private void btnLoad_Click(object sender, EventArgs e)
{
string strFileName = @"F:\模板文件.xls";//打開這個文件，就知道哪些列要進行計算的
Object refmissing = System.Reflection.Missing.Value;
this.axWebBrowser1.Navigate(strFileName, ref refmissing, ref refmissing, ref refmissing, ref refmissing);
//加載並獲取所有的SHEET
wb1 = (Microsoft.Office.Interop.Excel.Workbook)axWebBrowser1.Document;
for (int i = 1; i < wb1.Worksheets.Count; i++)
{
ws1 = (Microsoft.Office.Interop.Excel._Worksheet)wb1.Worksheets.get_Item(i);
SheetList.Add(ws1.Name.ToString());
}
this.listBox1.DataSource = this.SheetList;
ws1 = (Microsoft.Office.Interop.Excel._Worksheet)wb1.Worksheets.get_Item(1);
ws1.Activate();
this.Text = ws1.Name.ToString();
}
private void listBox1_Click(object sender, EventArgs e)
{
ws1 = (Microsoft.Office.Interop.Excel._Worksheet)wb1.Worksheets.get_Item(this.listBox1.SelectedValue.ToString());
ws1.Activate();
this.Text = ws1.Name.ToString();
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
axWebBrowser1.Dispose();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void listBox1_SelectedIndExchanged(object sender, EventArgs e)
{
ws1 = (Microsoft.Office.Interop.Excel._Worksheet)wb1.Worksheets.get_Item(this.listBox1.SelectedValue.ToString());
ws1.Activate();
this.Text = ws1.Name.ToString();
}
private void textBox2_TextChanged(object sender, EventArgs e)
{
if (this.textBox2.Text != "")
{
this.textBox3.Text = this.textBox2.Text;
}
}
private void textBox1_KeyDown(object sender, KeyEventArgs e)
{
}
private void textBox1_KeyPress(object sender, KeyPressEventArgs e)
{
}
private void btnCal_Click(object sender, EventArgs e)
{
int a =0;
int b = 0;
try
{
a = Convert.ToInt32(this.textBox2.Text);
b = Convert.ToInt32(this.textBox3.Text);
if(b>a)
{
MessageBox.Show("請檢查輸入是否正確！");
}
for (int i = a; i <= b; i++)
{
CAl(i, this.textBox1.Text.ToUpper());
}
MessageBox.Show("計算完成！");
}
catch
{
MessageBox.Show("請檢查輸入是否正確！");
}
}
private void CAl(int Row_1, string Col_1)
{
string strDescFilePathName_1 = System.Windows.Forms.Application.StartupPath + "\\分公司1.xls";
string strDescFilePathName_2 = System.Windows.Forms.Application.StartupPath + "\\分公司2.xls";
string strDescFilePathName_3 = System.Windows.Forms.Application.StartupPath + "\\分公司3.xls";
string strDescFilePathName_4 = System.Windows.Forms.Application.StartupPath + "\\合並後表.xls";
object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.ApplicationClass xlApp_1 = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook_1 = xlApp_1.Workbooks.Open(strDescFilePathName_1, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
Microsoft.Office.Interop.Excel._Worksheet ws_1 = (Microsoft.Office.Interop.Excel._Worksheet)xlWorkbook_1.Worksheets.get_Item(this.listBox1.SelectedValue.ToString());
Microsoft.Office.Interop.Excel.ApplicationClass xlApp_2 = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook_2 = xlApp_2.Workbooks.Open(strDescFilePathName_2, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
Microsoft.Office.Interop.Excel._Worksheet ws_2 = (Microsoft.Office.Interop.Excel._Worksheet)xlWorkbook_2.Worksheets.get_Item(this.listBox1.SelectedValue.ToString());
Microsoft.Office.Interop.Excel.ApplicationClass xlApp_3 = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook_3 = xlApp_3.Workbooks.Open(strDescFilePathName_3, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
Microsoft.Office.Interop.Excel._Worksheet ws_3 = (Microsoft.Office.Interop.Excel._Worksheet)xlWorkbook_3.Worksheets.get_Item(this.listBox1.SelectedValue.ToString());
Microsoft.Office.Interop.Excel.ApplicationClass xlApp_4 = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook_4 = xlApp_3.Workbooks.Open(strDescFilePathName_4, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
Microsoft.Office.Interop.Excel._Worksheet ws_4 = (Microsoft.Office.Interop.Excel._Worksheet)xlWorkbook_4.Worksheets.get_Item(this.listBox1.SelectedValue.ToString());
Decimal t_1 = 0;
Decimal t_2 = 0;
Decimal t_3 = 0;
if (((Microsoft.Office.Interop.Excel.Range)ws_1.Cells[Row_1, Col_1]).Text.ToString() == "" || ((Microsoft.Office.Interop.Excel.Range)ws_1.Cells[Row_1, Col_1]).Text == null)
{
t_1 = 0;
}
}
if (((Microsoft.Office.Interop.Excel.Range)ws_2.Cells[Row_1, Col_1]).Text.ToString() == "" || ((Microsoft.Office.Interop.Excel.Range)ws_2.Cells[Row_1, Col_1]).Text == null)
{
t_2 = 0;
}
else
{
t_2 = Convert.ToDecimal(((Microsoft.Office.Interop.Excel.Range)ws_2.Cells[Row_1, Col_1]).Text);
}
if (((Microsoft.Office.Interop.Excel.Range)ws_3.Cells[Row_1, Col_1]).Text.ToString() == "" || ((Microsoft.Office.Interop.Excel.Range)ws_3.Cells[Row_1, Col_1]).Text == null)
{
t_3 = 0;
}
else
{
t_3 = Convert.ToDecimal(((Microsoft.Office.Interop.Excel.Range)ws_3.Cells[Row_1,Col_1]).Text);
}
Decimal t = t_1 + t_2 + t_3;
// this.textBox4.Text += "=" + t.ToString();
this.textBox4.AppendText(t_1.ToString()+"+"+t_2.ToString()+"+"+t_3.ToString()+"="+t.ToString());
this.textBox4.AppendText("\n\r\n\r");
try
{
ws_4.Cells[Row_1, Col_1] = t.ToString();
xlWorkbook_4.Save();//保存該xls文件，沒有這句話將不會保存數據。
}
catch
{
this.textBox4.AppendText("寫入第" + Row_1.ToString() + "行，第" + Col_1 + "列時出錯，可能該單元格有計算公式存在。");
this.textBox4.AppendText("\n\r\n\r");
//MessageBox.Show("寫入第" + Row_1.ToString() + "行，第" + Col_1+"列時出錯。");
}
finally
{
xlWorkbook_4.Save();//保存該xls文件，沒有這句話將不會保存數據。
//避免啟動多個Excel進程。
xlApp_1.Quit();
xlApp_2.Quit();
xlApp_3.Quit();
xlApp_4.Quit();
}
}
}
}
Form1.Designer.cs文件如下：
namespace TSSH
{
partial class Form1
{
/// <summary>
/// 必需的設計器變量。
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// 清理所有正在使用的資源。
/// </summary>
/// <param name="disposing">如果應釋放托管資源，為 true；否則為 false。</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows 窗體設計器生成的代碼
/// <summary>
/// 設計器支持所需的方法 - 不要
/// 使用代碼編輯器修改此方法的內容。
/// </summary>
private void InitializeComponent()
{
System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(Form1));
this.splitContainer1 = new System.Windows.Forms.SplitContainer();
this.textBox4 = new System.Windows.Forms.TextBox();
this.label3 = new System.Windows.Forms.Label();
this.textBox3 = new System.Windows.Forms.TextBox();
this.label2 = new System.Windows.Forms.Label();
this.label1 = new System.Windows.Forms.Label();
this.btnCal = new System.Windows.Forms.Button();
this.textBox2 = new System.Windows.Forms.TextBox();
this.textBox1 = new System.Windows.Forms.TextBox();
this.btnLoad = new System.Windows.Forms.Button();
this.splitContainer2 = new System.Windows.Forms.SplitContainer();
this.listBox1 = new System.Windows.Forms.ListBox();
this.axWebBrowser1 = new AxSHDocVw.AxWebBrowser();/> this.splitContainer1.Panel1.SuspendLayout();
this.splitContainer1.Panel2.SuspendLayout();
this.splitContainer1.SuspendLayout();
this.splitContainer2.Panel1.SuspendLayout();
this.splitContainer2.Panel2.SuspendLayout();
this.splitContainer2.SuspendLayout();
((System.ComponentModel.ISupportInitialize)(this.axWebBrowser1)).BeginInit();
this.SuspendLayout();
//
// splitContainer1
//
this.splitContainer1.Dock = System.Windows.Forms.DockStyle.Fill;
this.splitContainer1.FixedPanel = System.Windows.Forms.FixedPanel.Panel1;
this.splitContainer1.Location = new System.Drawing.Point(0, 0);
this.splitContainer1.Name = "splitContainer1";
this.splitContainer1.Orientation = System.Windows.Forms.OrIEntation.Horizontal;
//
// splitContainer1.Panel1
//
this.splitContainer1.Panel1.Controls.Add(this.textBox4);
this.splitContainer1.Panel1.Controls.Add(this.label3);
this.splitContainer1.Panel1.Controls.Add(this.textBox3);
this.splitContainer1.Panel1.Controls.Add(this.label2);
this.splitContainer1.Panel1.Controls.Add(this.label1);
this.splitContainer1.Panel1.Controls.Add(this.btnCal);
this.splitContainer1.Panel1.Controls.Add(this.textBox2);
this.splitContainer1.Panel1.Controls.Add(this.textBox1);
this.splitContainer1.Panel1.Controls.Add(this.btnLoad);
//
// splitContainer1.Panel2
//
this.splitContainer1.Panel2.Controls.Add(this.splitContainer2);
this.splitContainer1.Size = new System.Drawing.Size(1091, 409);
this.splitContainer1.SplitterDistance = 82;
this.splitContainer1.TabIndex = 0;
//
// textBox4
//
this.textBox4.Dock = System.Windows.Forms.DockStyle.Right;
this.textBox4.Location = new System.Drawing.Point(641, 0);
this.textBox4.Multiline = true;
this.textBox4.Name = "textBox4";
this.textBox4.ScrollBars = System.Windows.Forms.ScrollBars.Both;
this.textBox4.Size = new System.Drawing.Size(450, 82);
this.textBox4.TabIndex = 9;
//
// label3
//
this.label3.AutoSize = true;
this.label3.Location = new System.Drawing.Point(356, 20);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(89, 12);
this.label3.TabIndex = 8;
this.label3.Text = "要合並的終止行";
//
// textBox3
//
this.textBox3.Location = new System.Drawing.Point(358, 38);
this.textBox3.Name = "textBox3";
this.textBox3.Size = new System.Drawing.Size(85, 21);
this.textBox3.TabIndex = 7;
//
// label2
; //
//
this.FormClosing += new System.Windows.Forms.FormClosingEventHandler(this.Form1_FormClosing);
}
#endregion
private System.Windows.Forms.SplitContainer splitContainer1;
private System.Windows.Forms.Button btnLoad;
private System.Windows.Forms.SplitContainer splitContainer2;
private System.Windows.Forms.ListBox listBox1;
private AxSHDocVw.AxWebBrowser axWebBrowser1;
private System.Windows.Forms.TextBox textBox2;
private System.Windows.Forms.TextBox textBox1;
private System.Windows.Forms.Button btnCal;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.TextBox textBox3;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.TextBox textBox4;
}
}
//說明：
在界面上以WEBBROWSE加載一個模板文件，這樣就知道哪些列和行需要合並的。
輸入對應的行和列，就可以進行合並計算並寫入合並後的文件中。
在EXCEL中，還有另一個跨文件進行Excel合並計算的方法。
“數據/合並計算”通過引用其它的Excel文件，也可以達到合算的目的。只是根據我的需要，制作了一個簡單的程序。很多錯誤及異常沒有處理。
