【.NET Core】【ClosedXML】Copy Excel worksheets
Tags: dotnet, csharp, closedxml
Published: 2021-02-20 02:27:19

## 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.](https://github.com/closedxml/closedxml)

### Environments
* .NET ver.5.0.103
* ClosedXML ver.0.95.4

### Original worksheet
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hrxfio8ofvebcw2cxua8.jpg)

## Copy worksheets
(Edit on 2021-02-23)
<del>IXLWorksheet of ClosedXML only has a "CopyTo" method what copies from one worksheet to a new worksheet in same workbook.</del>
I can copy worksheet like below.
```cs
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](https://dev.to/igitur) :)

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

#### SpreadsheetLoader.cs
```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
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lb4r7mg2xxajlvft0kxf.jpg)

#### 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
```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

```cs
...
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
```cs
...
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.

```cs
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
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cjpminjyuqufx28cr9vu.jpg)

## Shapes(Include graphs)
ClosedXML doesn't have copying shapes functions.

* [c# - Insert shape in Worksheet with ClosedXML - Stack Overflow](https://stackoverflow.com/questions/10140949/insert-shape-in-worksheet-with-closedxml)

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.

```cs
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.