Charts Not Copied

[markolbert]

NPOI Version

2.7.0

File Type

• [ x] XLSX
• XLS
• DOCX
• XLSM
• OTHER

Upload the Excel File

Cumulative Active Lapsed - Copy.xlsx

Reproduce Steps

Here is the code fragment I'm using to do the copy. existingWorkbook contains the charts, while workbook is a newly-created (by NPOI) XSSFWorkbook. The conditional test simply means "don't copy any sheet with the same name as a sheet that's already been created in workbook".

for( var sheetIdx = 0; sheetIdx < existingWorkbook.NumberOfSheets; sheetIdx++ )
{
    var sheetName = existingWorkbook.GetSheetName( sheetIdx );

    if( !_sheets.Any( s => s.Key.Equals( sheetName, StringComparison.OrdinalIgnoreCase ) ) )
        existingWorkbook.GetSheetAt( sheetIdx ).CopyTo( workbook, sheetName, true, true );

}

Issue Description

When I copy a sheet from an existing workbook (also created by NPOI) to a new workbook, charts do not get copied. Instead, for a chart sheet I end up with a blank sheet (with the correct name). For charts embedded in a sheet, the sheet gets copied (with the correct name), but the embedded chart does not.

[Bykiev]

Hi, I did some research I didn't find such method in original POI. Try to use XSSFWorkbook.CloneSheet instead.

[markolbert]

Thanx for the quick reply, @Bykiev. And thanx for helping maintain a very cool library!

I don't understand, though, how CloneSheet() will do what I need, which is to make a copy of an existing sheet in workbook #1 and move it to workbook #2. Or are you suggesting I first make a copy via CloneSheet() and then move the clone?

[markolbert]

I tried the following approach but it didn't work:

if( !File.Exists( filePath ) )
    return;

var existingWorkbook = new XSSFWorkbook( new FileInfo( filePath ) );

for( var idx = 0; idx < existingWorkbook.NumberOfSheets; idx++ )
{
    var existingSheetName = existingWorkbook.GetSheetName( idx );

    // skip sheets with the same name as ones we've created in the new workbook
    if( _sheets.Any( kvp => kvp.Value
                               .SheetName.Equals( existingSheetName, StringComparison.OrdinalIgnoreCase ) ) )
        continue;

    var clonedSheet = existingWorkbook.CloneSheet( idx );
    clonedSheet.CopyTo( workbook, existingSheetName, false, true );
}

// dispose of the existing workbook to release the file lock on filePath
existingWorkbook.Dispose();

The exception occurred on the line var clonedSheet = existingWorkbook.CloneSheet( idx );. Details:

System.ObjectDisposedException
HResult=0x80131622
Message=Cannot access a disposed object.
Object name: 'The stream with Id 6af7508c-dc76-4de7-afeb-e9365f549b9c and Tag is disposed.'.
Source=Microsoft.IO.RecyclableMemoryStream
StackTrace:
at Microsoft.IO.RecyclableMemoryStream.ThrowDisposedException()
at Microsoft.IO.RecyclableMemoryStream.CheckDisposed()
at Microsoft.IO.RecyclableMemoryStream.set_Position(Int64 value)
at NPOI.XSSF.UserModel.XSSFWorkbook.CloneSheet(Int32 sheetNum, String newName)
at NPOI.XSSF.UserModel.XSSFWorkbook.CloneSheet(Int32 sheetNum)
at J4JSoftware.Lgl.Common.ExportCollection.CloneSheets(IWorkbook workbook, String filePath) in C:\Programming\SSMC LGL\Common\exporters\ExportCollection.cs:line 83
at J4JSoftware.Lgl.Common.ExportCollection.Export(String filePath, Boolean copyOtherSheets) in C:\Programming\SSMC LGL\Common\exporters\ExportCollection.cs:line 60
at TestProject.ExportPhase.ActiveLapsed() in C:\Programming\SSMC LGL\TestProject\phases\ExportPhase.cs:line 146
at System.RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
at System.Reflection.MethodBaseInvoker.InvokeWithNoArgs(Object obj, BindingFlags invokeAttr)

[Bykiev]

Basically, you don't even need to create a copy of the sheet - just remove the other sheets and save it as a new workbook. It's just a workaround.

[markolbert]

Good point, thanx.

Unfortunately, the resulting workbook is corrupted. When I try to open it I get the following message from Excel after allowing it to try & fix what it can:

Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)

I'm wondering if it has to do with the fact the original workbook contains a chartsheet which references named ranges that are defined in a sheet I create (let's call that one the data sheet).

Since the first step in the process you described (if I understood it correctly) is to delete the sheets I'll be creating, the data sheet gets deleted before it is created. Might that be triggering a REF error -- because the named ranges the chart depends on are (temporarily) gone -- that ends up corrupting the file?

My goal is to take a workbook that contains a chartsheet that draws from a data sheet and replace the information in the datasheet with updated information. Perhaps I'm required to never delete the original data sheet, but just update its contents (and its named ranges, since the number of rows may change with new information)?

[markolbert]

Success! The trick was to not delete and re-create the data sheet but clear its contents -- but not its named ranges -- and then adjust the named ranges' formulas.

Thanx for your help, @Bykiev!

[markolbert]

closing...

[Bykiev]

We will close the issue after fixing the bug with XSSFSheet.CopyTo(), which should copy charts too

[markolbert]

Oops, sorry, forgot about that...

[markolbert]

Sigh... still running into problems.

When I try to write the workbook back to the file system, an exception is thrown about the file already being open.

I created the workbook from an existing file like this:

workbook = new XSSFWorkbook( new FileInfo( filePath ) );

Does that not close any streams opened to read the file? I assumed it would.

[markolbert]

Please disregard that last question. I over complicated handling existing files and screwed up the save process. I’ve fixed my code.

[tonyqus]

Closing this issue as duplicate. Please subscribe #1338