Comments (12)
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.
Related Issues (20)
- Add API for SearchText for xlsx,xls HOT 4
- Reading large files HOT 3
- AVERAGEIF formula doesn't work in xls HOT 7
- Cells with Formulas Display as Blank HOT 3
- XWPF POI Bug Migration II
- How can set picture properties on "Move and size with cells" HOT 2
- XSSFSheet.CopySheet doesn't support cloning charts
- i cannot open a file with npoi HOT 5
- The npoi package conflicts with the Magicodes.IE package HOT 2
- 'Object reference not set to an instance of an object' in XSSFCell.ToString() HOT 4
- npoi will inexplicably digitize some cells with formulas HOT 5
- excel file damaged after adding images and saving HOT 2
- Unable to rotate image manually in DOCX
- Looking for a new job in Asia
- "Do you want us to try to recover as much as we can?" error message when opnening file when using 2.7.0 version HOT 1
- SXSSFWorkbook, it does not compress files which are saved on my local temp file HOT 1
- nsid is generated incorrectly for abstract num HOT 7
- NPOI corrupts a workbook with a simple calculation HOT 1
- Using unsafe package HOT 1
- Broken xlsx file created by SXSSFWorkbook - Open with Excel 2007 HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from npoi.