DEV Community

Masui Masanori
Masui Masanori

Posted on • Edited on

【.NET Core】【ClosedXML】Copy Excel worksheets

Intro

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

Environments

  • .NET ver.5.0.103
  • ClosedXML ver.0.95.4

Original worksheet

Alt Text

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);
        }
...
Enter fullscreen mode Exit fullscreen mode

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);
            }
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Result

Alt Text

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);
    }
}
...
Enter fullscreen mode Exit fullscreen mode

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();
         }
     }
}
...
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
...
Enter fullscreen mode Exit fullscreen mode

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
...
Enter fullscreen mode Exit fullscreen mode

I checked the picture format.

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

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

Shapes(Include graphs)

ClosedXML doesn't have copying shapes functions.

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;
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Result

[Theme] Accent1
[Theme] Accent3
[Theme] Background2
[Theme] Accent3
[Theme] Background2
[Theme] Accent3
[Theme] Background2
[Theme] Accent6
Enter fullscreen mode Exit fullscreen mode

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.

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

Top comments (4)

Collapse
 
igitur profile image
Francois Botha

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

Collapse
 
masanori_msl profile image
Masui Masanori

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

Collapse
 
igitur profile image
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.

Collapse
 
masanori_msl profile image
Masui Masanori

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

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more