none
C# Merge Excel Sheets into one Sheet RRS feed

  • Question

  • Hello Expert:

    I have Excel reports generated by application. the format is xls.

    Due to row limits, it will split the content into multiple sheets if the total number of records is bigger than 65536.

    I am trying to copy all records from each sheet and merge them into one sheet then save the file in xlsx format.

    Originally I copy all the cells into array then write it to the target sheet.

    It works fine but when I write it into the target sheet, the datetime is showing as double.

    I want to remove human interaction when doing the merge, so I switch to PasteSpecial, which keep the format, but it has one problem, it seems I can only copy and paste the first sheet. when it tries to paste the second time I receive an error.

    "The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following:

    • Click a single cell, and then paste.
    • Select a rectangle that's the same size and shape, and then paste."

    Following is my code:

    app = Globals.ThisAddIn.Application;

    sourceBook = app.ActiveWorkbook;

         

    targetBook = app.Workbooks.Add(Type.Missing);                
    targetSheet = targetBook.Worksheets.Add(Type.Missing);
    targetSheet.Name = "Merge Result";

    int rowCount = 0;
    string targetFilePath = "";
    foreach (Excel.Worksheet sheet in sourceBook.Worksheets)
    {
    Excel.Range workSheetRange = sheet.UsedRange;
    Excel.Range startPasteCell = (Excel.Range)targetSheet.Cells[rowCount + 1, 1];
    Excel.Range endPasteCell = (Excel.Range)targetSheet.Cells[rowCount + workSheetRange.Rows.Count, 
                                workSheetRange.Columns.Count];
    Excel.Range pasteArea = targetSheet.get_Range(startPasteCell, endPasteCell);
                        workSheetRange.Copy(Type.Missing);
    //I have tried both cell select then paste. or range paste same result.
    startPasteCell.Select();
    targetSheet.PasteSpecial(Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                            Type.Missing, Type.Missing, Type.Missing);

    //pasteArea.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
                        Type.Missing, Type.Missing);
        rowCount += workSheetRange.Rows.Count;

    }

    Thank you very much


    Chad Chen

    Wednesday, October 15, 2014 3:42 PM

Answers

  • Hi,

    >>Due to row limits, it will split the content into multiple sheets if the total number of records is bigger than 65536.

    In fact, 65536 is the maximum row number of worksheet in Excel 2003. Before pasting, Excel will check whether the content is larger than the limitation automatically. So you will get the error message above, and I don't think we can pass the 65536 limitation if you are working with Excel 2003 *.xls file.

    From Excel 2007, the maximum row number of worksheet is 1048576. So I suggest you upgrading your Office and working with *.xlsx file (for Excel 2007 and later version).


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 16, 2014 7:45 AM
    Moderator
  • Please try it this way.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    using Microsoft.Office.Interop.Excel;
    using System.IO;
    
    namespace WindowsFormsApplication3
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                Main();
            }
    
            public void Main()
            {
                string filePath = "C:\\Users\\Excel\\Desktop\\Ryan_Folder\\MainExcel.xls";
                Microsoft.Office.Interop.Excel.Application xlobj = new Microsoft.Office.Interop.Excel.Application();
                Workbook w = default(Workbook);
                Workbook w1 = default(Workbook);
                Worksheet s = default(Worksheet);
                Worksheet s1 = default(Worksheet);
                Worksheet xlsht = default(Worksheet);
                int intItem = 1;
                DirectoryInfo dirSrc = new DirectoryInfo(@"C:\Users\Excel\Desktop\Ryan_Folder\");
                foreach (FileInfo ChildFile in dirSrc.GetFiles())
                {
                    try
                    {
                        // Renaming the excel sheet
                        w = xlobj.Workbooks._Open(ChildFile.FullName, 
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing);
                        
                        w1 = xlobj.Workbooks.Open(filePath);
                        xlobj.Visible = true;
    
                        w1 = xlobj.Workbooks._Open(filePath,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing);
    
                        if (intItem > 3)
                        {
                            Excel.Worksheet lastSht =
                                 (Excel.Worksheet)w1.Worksheets[w1.Worksheets.Count];
                            xlsht = (Excel.Worksheet)w1.Worksheets.Add(Type.Missing,
                                lastSht, 
                                Type.Missing, Type.Missing);
                        }
                        s = (Excel.Worksheet)w.Worksheets[1];
                        s1 = (Excel.Worksheet)w1.Worksheets[intItem];
                        s1.Name = ChildFile.Name;
    
                        // it will copy and paste sheet from one to another with formula
                        s.UsedRange.Copy(Type.Missing);
                        Excel.Range r = s1.get_Range("A1",Type.Missing);
                        r.PasteSpecial(Excel.XlPasteType.xlPasteValues,
                              Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
                              Type.Missing, Type.Missing);
                        s1.UsedRange.Formula = s.UsedRange.Formula;
    
                        // Renaming the excel sheet
                        w.Save();
                        w1.Save();
                        w.Close(false, Type.Missing, Type.Missing);
                        w1.Close(false, Type.Missing, Type.Missing);
                    }
                    catch (Exception ex)
                    {
                        w.Save();
                        w1.Save();
                        w.Close(false, Type.Missing, Type.Missing);
                        w1.Close(false, Type.Missing, Type.Missing);
                    }
                    intItem = intItem + 1;
                }
                
            }
        }
    }
    
    


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Calgary123 Tuesday, October 28, 2014 1:44 AM
    Thursday, October 16, 2014 4:04 PM

All replies

  • if the only problem is the date consider setting the .NumerFormat property on a Range class to desired format ie. "dd/mm/yyyy" 

    Excel by default stores date & time as a double.

    Wednesday, October 15, 2014 5:15 PM
  • Hi Michal:

    Thank you very much for your input.

    I have a couple of reports. All of them will need this merge function.

    So it is a bit hard to pre-define the column to datetime format.

    I hope the system can allow me to just copy the cells over.

    The pasteSpecial method is working ok and it preserve the datetime format when copying.

    Just it can not pass the 65536 limits.(whenever I try to paste the second sheet, it indicates area doesn't fit. But the size is ok. ) the behavior is very similar to when we open xls file and try to paste to 65537 row. Since I only open a new workbook I think it should be ok. If I use array then I can pass 65536 row with no issues.

    So I am just trying to figure the pasteSpecial limitation so I can walk around the error.

    Chad Chen



    Wednesday, October 15, 2014 5:32 PM
  • Hi,

    >>Due to row limits, it will split the content into multiple sheets if the total number of records is bigger than 65536.

    In fact, 65536 is the maximum row number of worksheet in Excel 2003. Before pasting, Excel will check whether the content is larger than the limitation automatically. So you will get the error message above, and I don't think we can pass the 65536 limitation if you are working with Excel 2003 *.xls file.

    From Excel 2007, the maximum row number of worksheet is 1048576. So I suggest you upgrading your Office and working with *.xlsx file (for Excel 2007 and later version).


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 16, 2014 7:45 AM
    Moderator
  • Please try it this way.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    using Microsoft.Office.Interop.Excel;
    using System.IO;
    
    namespace WindowsFormsApplication3
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                Main();
            }
    
            public void Main()
            {
                string filePath = "C:\\Users\\Excel\\Desktop\\Ryan_Folder\\MainExcel.xls";
                Microsoft.Office.Interop.Excel.Application xlobj = new Microsoft.Office.Interop.Excel.Application();
                Workbook w = default(Workbook);
                Workbook w1 = default(Workbook);
                Worksheet s = default(Worksheet);
                Worksheet s1 = default(Worksheet);
                Worksheet xlsht = default(Worksheet);
                int intItem = 1;
                DirectoryInfo dirSrc = new DirectoryInfo(@"C:\Users\Excel\Desktop\Ryan_Folder\");
                foreach (FileInfo ChildFile in dirSrc.GetFiles())
                {
                    try
                    {
                        // Renaming the excel sheet
                        w = xlobj.Workbooks._Open(ChildFile.FullName, 
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing);
                        
                        w1 = xlobj.Workbooks.Open(filePath);
                        xlobj.Visible = true;
    
                        w1 = xlobj.Workbooks._Open(filePath,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing);
    
                        if (intItem > 3)
                        {
                            Excel.Worksheet lastSht =
                                 (Excel.Worksheet)w1.Worksheets[w1.Worksheets.Count];
                            xlsht = (Excel.Worksheet)w1.Worksheets.Add(Type.Missing,
                                lastSht, 
                                Type.Missing, Type.Missing);
                        }
                        s = (Excel.Worksheet)w.Worksheets[1];
                        s1 = (Excel.Worksheet)w1.Worksheets[intItem];
                        s1.Name = ChildFile.Name;
    
                        // it will copy and paste sheet from one to another with formula
                        s.UsedRange.Copy(Type.Missing);
                        Excel.Range r = s1.get_Range("A1",Type.Missing);
                        r.PasteSpecial(Excel.XlPasteType.xlPasteValues,
                              Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
                              Type.Missing, Type.Missing);
                        s1.UsedRange.Formula = s.UsedRange.Formula;
    
                        // Renaming the excel sheet
                        w.Save();
                        w1.Save();
                        w.Close(false, Type.Missing, Type.Missing);
                        w1.Close(false, Type.Missing, Type.Missing);
                    }
                    catch (Exception ex)
                    {
                        w.Save();
                        w1.Save();
                        w.Close(false, Type.Missing, Type.Missing);
                        w1.Close(false, Type.Missing, Type.Missing);
                    }
                    intItem = intItem + 1;
                }
                
            }
        }
    }
    
    


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Calgary123 Tuesday, October 28, 2014 1:44 AM
    Thursday, October 16, 2014 4:04 PM