2

Trying to tackle a lot of new tools at once, attempting to split each worksheet in a .xlsx file into individual workbooks.

  1. Is there a simple way to copy the entire worksheet (including images/lines) with spreadsheetlight or EPPlus?

  2. If direct copying one worksheet to another won't work, are you able to copy all cell data into an object and paste it over to the cells of another workbook?

Any advice is greatly appreciated!

Edit: Open to alternatives to Spreadsheetlight as well, looking into EPPlus at the moment.

TSga
  • 191
  • 1
  • 8

1 Answers1

1

EPPlus makes this fairly simple.

Something like this should do it:

using (var sourceExcel = new ExcelPackage(new FileInfo("multisheet.xlsx")))
{
    var sheetsToCopy = sourceExcel.Workbook.Worksheets;
    foreach(var sheetToCopy in sheetsToCopy)
    {
        using (var destExcel = new ExcelPackage())
        {
            destExcel.Workbook.Worksheets.Add(sheetToCopy.Name, sheetToCopy);
            destExcel.SaveAs(new FileInfo(sheetToCopy.Name + ".xlsx"));
        }
    }
}
Stewart_R
  • 13,764
  • 11
  • 60
  • 106
  • Thank you so much Stewart, really appreciate the reply. Is there a way to maintain references in cells to other worksheets? Say I have [Sheet1] cell 4, that references something in [Sheet2] cell 5, it looks like the copy leaves a #REF in that cell with this method. – TSga Oct 31 '17 at 17:00
  • my pleasure - glad it helped. Not sure about the references to be honest. I'd suspect you would need to sort the references out yourself - without you there is no way for one workbook to know the filename of another. – Stewart_R Oct 31 '17 at 17:06