This project has moved and is read-only. For the latest updates, please go here.

Copy to existing sheet

Apr 27, 2012 at 9:42 AM

Hello !

Is it possible to copy an .xlsx content into an existing sheet ?

I have several xlsx files and I wish to concatenate them in one sheet programatically, Can it be done using ClosedXML ?

Thanks alot,

Amir.

Apr 27, 2012 at 7:32 PM

I could have sworn there was a workbook.AddWorksheet(IXLWorksheet worksheet) overload. Anyway you can use:

    var excelFiles = new List<String>(); // Fill list with your files
    var wbCombined = new XLWorkbook();
    foreach (var file in excelFiles)
    {
        var wbSource = new XLWorkbook(file);
        foreach (var wsSource in wbSource.Worksheets)
        {
            wsSource.CopyTo(wbCombined, wsSource.Name);
        }
    }

Apr 27, 2012 at 7:33 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Apr 28, 2012 at 9:10 AM
Edited Apr 28, 2012 at 9:13 AM

Hi,

Thanks for your reply,

I believe that the code above will create new sheets in the wbCombined.

I am looking for a way that wbCombined will have only one sheet that contains a concatination of the excelFiles content.

Something like the following:

 

var excelFiles = new List<String>(); // Fill list with your files
var wbCombined = new XLWorkbook(); // A Workbook that contatins Only one sheet.
IXLWorksheet wsCombined = wbCombined.AddWorksheet("CombinedSheets"); // This is the only sheet.

foreach (var file in excelFiles)
{
      var wbSource = new XLWorkbook(file);
      foreach (var wsSource in wbSource.Worksheets)
      {
          // I wish to copy into the existing worksheet (wsCombined) rather then create a new one.
          // The third argument is the position in the ws, This argument exists also today.
          wsSource.CopyTo(wbCombined, wsCombined, wsCombined.LastRowUsed().RowBelow().RowNumber);
      }
}

 

If using your solution , I would then need to merge all the sheets in wbCombined into one sheet. (Maybe that's possible?)

Thanks for your reply,

Amir.