首页 > 代码库 > C#实现多Excel多Sheet合并

C#实现多Excel多Sheet合并

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

int modelSheetHeadRows = 1;                    Excel.Application excel = null;                    try                    {                        excel = new Excel.Application();                        string newModel = tbModel.Text.Replace(".", "合并.");                        excel.Visible = true;                        File.Copy(tbModel.Text, newModel, true);                        Excel.Workbook modelWorkBook = excel.Workbooks.Open(newModel, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing                            , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);                        for (int i = 0; i < listViewSel.Items.Count; i++)                        {                            string pathName = listViewSel.Items[i].SubItems[2].Text + "\\" + listViewSel.Items[i].SubItems[1].Text;                            Excel.Workbook tmpWorkBook = excel.Workbooks.Open(pathName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing                                , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);                            for (int j = 1; j <= tmpWorkBook.Sheets.Count; j++)                            {                                Excel.Worksheet tmpSheet = (Excel.Worksheet)tmpWorkBook.Sheets[j];                                if (modelWorkBook.Sheets.Count < j)                                {                                    modelWorkBook.Sheets.Add(Missing.Value);                                }                                Excel.Worksheet modelSheet = (Excel.Worksheet)modelWorkBook.Sheets[j];                                if (i == 0)                                {                                    modelSheetHeadRows = modelSheet.UsedRange.Cells.Rows.Count;                                }                                int tmpSheetRows = tmpSheet.UsedRange.Cells.Rows.Count;                                int tmpSheetColumns = tmpSheet.UsedRange.Cells.Columns.Count;                                int modelSheetRows = modelSheet.UsedRange.Cells.Rows.Count;                                tmpSheet.get_Range((Excel.Range)tmpSheet.Cells[modelSheetHeadRows + 1, 1], (Excel.Range)tmpSheet.Cells[tmpSheetRows, tmpSheetColumns]).Copy(                                    modelSheet.get_Range((Excel.Range)modelSheet.Cells[modelSheetRows + 1, 1], (Excel.Range)modelSheet.Cells[tmpSheetRows + modelSheetRows - 1, tmpSheetColumns]));                            }                            tmpWorkBook.Close();                            modelWorkBook.Save();                        }                        modelWorkBook.Close();                    }                    catch (Exception ee)                    {                        MessageBox.Show("[错误]:" + ee.Message, "信息提示");                    }                    finally                    {                        pathtb.Text = null;                        tbModel.Text = null;                        listView.Items.Clear();                        listViewSel.Items.Clear();                        excel.Quit();                        MessageBox.Show("合并完成");                    }