【.NET Core】【ClosedXML】Copy Excel worksheets

Intro

This time, I will try copying Microsoft Excel worksheets from one workbook to another workbook by ClosedXML.

• GitHub - ClosedXML/ClosedXML: ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API.

Environments

• .NET ver.5.0.103
• ClosedXML ver.0.95.4

Original worksheet

Copy worksheets

(Edit on 2021-02-23)

IXLWorksheet of ClosedXML only has a "CopyTo" method what copies from one worksheet to a new worksheet in same workbook.

I can copy worksheet like below.

using(XLWorkbook bookFrom = new XLWorkbook(fileFrom))
{
    using(MemoryStream streamTo = new MemoryStream())
    {
        using(XLWorkbook bookTo = new XLWorkbook())
        {
            foreach(IXLWorksheet sheetFrom in bookFrom.Worksheets)
            {
                sheetFrom.CopyTo(bookTo, sheetFrom.Name);
            }
            bookTo.SaveAs(streamTo);
        }
...

It can copy them.

• Cell values
• Formulas
• Colors
• Borders
• Merged cell
• Pictures
• Cell(Range name)

Thank you very much, @igitur :)

To copy to another book, I can convert the worksheet to Range and copy to another worksheet.

SpreadsheetLoader.cs

using System.IO;
using ClosedXML.Excel;
using System.Linq;
using System.Collections.Generic;
using ClosedXML.Excel.Drawings;

namespace CopySheetSample
{
    public class SpreadsheetLoader
    {
        public void Load(string fileFrom, string fileTo)
        {
            byte[] fileData;            
            using(XLWorkbook bookFrom = new XLWorkbook(fileFrom))
            {
                using(MemoryStream streamTo = new MemoryStream())
                {
                    using(XLWorkbook bookTo = new XLWorkbook())
                    {
                        foreach(IXLWorksheet sheetFrom in bookFrom.Worksheets)
                        {
                            IXLWorksheet sheetTo = bookTo.Worksheets.Add(sheetFrom.Name);
                            sheetFrom.AsRange().CopyTo(sheetTo.AsRange());
                        }
                        bookTo.SaveAs(streamTo);
                    }
                    fileData = streamTo.ToArray();
                }
            }
            using(var stream = new FileStream(fileTo, FileMode.Create))
            {
                stream.Write(fileData, 0, fileData.Length);
            }
        }
    }
}

Result

Copied

• Cell values
• Formulas
• Colors
• Borders
• Merged cell

Not copied

• Cell(Range) names
• Height, Width
• Pictures
• Shapes(Include graphs)

and Theme colors

Copy manually

Cell(Range) names

To copy cell(range) names, I have to get their ranges.

SpreadsheetLoader.cs

...
foreach(IXLWorksheet sheetFrom in bookFrom.Worksheets)
{
    IXLWorksheet sheetTo = bookTo.Worksheets.Add(sheetFrom.Name);
    IXLRange sheetRangeFrom = sheetFrom.AsRange();

    sheetRangeFrom.CopyTo(sheetTo.AsRange());
    // Copy range names what are named on workbooks
    var ranges = bookFrom.NamedRanges.Where(r =>
            r.Ranges.Any(range => range.Worksheet.Name == sheetFrom.Name))
        .ToList();
    if(ranges.Count > 0)
    {
        foreach(IXLNamedRange range in ranges)
        {
            range.CopyTo(sheetTo);
        }
    }
    // Copy range names what are named on worksheets
    foreach(IXLNamedRange range in sheetFrom.NamedRanges)
    {
        range.CopyTo(sheetTo);
    }
}
...

Height, Width

...
foreach(IXLWorksheet sheetFrom in bookFrom.Worksheets)
{
    IXLWorksheet sheetTo = bookTo.Worksheets.Add(sheetFrom.Name);
    IXLRange sheetRangeFrom = sheetFrom.AsRange();
    sheetRangeFrom.CopyTo(sheetTo.AsRange());
...
    IXLAddress firstCellAddress = sheetFrom.Cell(1, 1).Address;
    IXLAddress lastCellAddress = sheetFrom.LastCellUsed().Address;
    // set column width
    for(int column = 1; column <= lastCellAddress.ColumnNumber; column++)
    {
        sheetTo.Column(column).Width = sheetFrom.Column(column).Width;
        if(sheetFrom.Column(column).IsHidden)
        {
            sheetTo.Column(column).Hide();
        }
     }
     // set row height
     for(int row = 1; row <= lastCellAddress.RowNumber; row++)
     {
         sheetTo.Row(row).Height = sheetFrom.Row(row).Height;
         if(sheetFrom.Row(row).IsHidden)
         {
             sheetTo.Row(row).Hide();
         }
     }
}
...

Pictures

...
foreach(IXLWorksheet sheetFrom in bookFrom.Worksheets)
{
    IXLWorksheet sheetTo = bookTo.Worksheets.Add(sheetFrom.Name);
    IXLRange sheetRangeFrom = sheetFrom.AsRange();
    sheetRangeFrom.CopyTo(sheetTo.AsRange());
...
    foreach(IXLPicture picture in sheetFrom.Pictures)
    {
        picture.CopyTo(sheetTo);
    }
}
...

Get execptions?

I couldn't copy some pictures.
When I created an Excel workbook by using Excel templates, I got an exception like below.

Unhandled exception. System.ArgumentException: The picture format in the stream and the parameter don't match
   at ClosedXML.Excel.Drawings.XLPicture..ctor(IXLWorksheet worksheet, Stream stream, XLPictureFormat format)
   at ClosedXML.Excel.Drawings.XLPictures.Add(Stream stream, XLPictureFormat format)
   at ClosedXML.Excel.Drawings.XLPictures.Add(Stream stream, XLPictureFormat format, String name)
   at ClosedXML.Excel.XLWorksheet.AddPicture(Stream stream, XLPictureFormat format, String name)
   at ClosedXML.Excel.Drawings.XLPicture.CopyTo(XLWorksheet targetSheet)
   at ClosedXML.Excel.Drawings.XLPicture.CopyTo(IXLWorksheet targetSheet)
   at CopySheetSample.SpreadsheetLoader.Load(String loadFilePath, String saveFilePath) in C:\Users\masan\OneDrive\Documents\workspace\5_0_101\CopySheetSample\SpreadsheetLoader.cs:line 65
...

I checked the picture format.

foreach(IXLPicture picture in sheetFrom.Pictures)
{
    Console.WriteLine(picture.Format);
    picture.CopyTo(sheetTo);
}

Its format was "bmp".

So I add another bmp format picture and remove the first one.
But I didn't get the exception.

I don't know what the differences between the them :(.

Result

Shapes(Include graphs)

ClosedXML doesn't have copying shapes functions.

• c# - Insert shape in Worksheet with ClosedXML - Stack Overflow

Because EPPlus, OpenXML, etc. have the functions, when I need copy shapes, I may need choose other libraries.

Theme colors

I checked the ThemeColor like below.

for(int column = 1; column <= lastCellAddress.ColumnNumber; column++)
{    
    for(var row = 1; row <= lastCellAddress.RowNumber; row++)
    {
        switch(sheetFrom.Cell(row, column).Style.Fill.BackgroundColor.ColorType)
        {
            case XLColorType.Theme:
                Console.WriteLine($"[Theme] {sheetFrom.Cell(row, column).Style.Fill.BackgroundColor.ThemeColor}");
                break;
        }
    }
}

Result

[Theme] Accent1
[Theme] Accent3
[Theme] Background2
[Theme] Accent3
[Theme] Background2
[Theme] Accent3
[Theme] Background2
[Theme] Accent6

I thought the reason why the colors changed was defined colors hadn't been same as Excel.
So I try searching the way to get color by another format for example color codes.

Comments

[Francois Botha]

There is a IXLWorksheet.CopyTo(XLWorkbook workbook, String newSheetName) method to copy a worksheet directly to another workbook.

[Masui Masanori]

Thank you for your information.
I missed that method :)

[Francois Botha]

For the exception when copying a picture, please log a new issue at github.com/ClosedXML/ClosedXML and be sure to fill in the issue template well, including code sample and test file.

[Masui Masanori]

Thank you so much.
I added a new issue at ClosedXML repository :)
github.com/ClosedXML/ClosedXML/iss...