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
Copy worksheets
(Edit on 2021-02-23)
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.
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.
Top comments (4)
There is a
IXLWorksheet.CopyTo(XLWorkbook workbook, String newSheetName)
method to copy a worksheet directly to another workbook.Thank you for your information.
I missed that method :)
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.
Thank you so much.
I added a new issue at ClosedXML repository :)
github.com/ClosedXML/ClosedXML/iss...