Charts Not Copied about npoi HOT 12 CLOSED

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!

from npoi.

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

from npoi.

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?

from npoi.

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)

from npoi.

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.

from npoi.

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)?

from npoi.

closing...

from npoi.

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

from npoi.

Oops, sorry, forgot about that...

from npoi.

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.

from npoi.

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

from npoi.

Closing this issue as duplicate. Please subscribe #1338

from npoi.