I have an Excel workbook that has 72 worksheets, however, I only need to load 7. Following the example here (http://www.aspose.com/docs/display/cellsnet/Load+only+Specific+Sheets+in+a+Workbook), does not work. I still get all 72 worksheets (ie workbook.WorkSheets.Count = 72). Even using just a simple Excel workbook (see attached), with 3 worksheets and using the exact code mentioned in link, still has a WorkSheets.Count = 3.
I need to be able to do the following.... Load specific worksheets, without prior knowledge of the load format and with the LoadDataOnly = false.
Well, when you load specific sheets in a workbook, it does load those worksheets but for the sheets which you do not need in the workbook, Aspose.Cells would generate blank worksheets when you save the file. Therefore the worksheets’ count you get is always equal to the number of worksheets in the actual workbook, but again this is due to those blank worksheets in the generated workbook. I think for your needs, you may remove your unwanted worksheets by using WorksheetCollection.RemoveAt() method except your desired sheets before saving to Excel file.
However, Aspose.Cells does not generate blank worksheets. It loads all the worksheets including the data, formatting, etc. As a simple test, using the file that I uploaded, if you modify the code (from the link that I mentioned in my original post) and simply save the workbook to a different filename, you will see that all worksheets and data are present in the new file and not just the worksheet that I want to load only.
Yes as a workaround, I can enumerate through the worksheet collection and remove all but the specified worksheets, but I am trying to prevent loading of all the worksheets initally due to the size of the Excel files that our clients are sending to us.
Well, I tested your scenario using your template file with our latest fix/version v7.4.3.2: Aspose.Cells for .NET v7.4.3.2
it works fine.
Here is my sample code using your template file and please find attached the output file. When you open the output file, you will see that although the blank worksheets are added for Sheet1 and Sheet3, but only Sheet2 is rendered retaining its data only.
Sample code:
//Define a new Workbook.
Workbook workbook;
//Set the load data option with selected sheet(s).
LoadDataOptiondataOption = new LoadDataOption();
dataOption.SheetNames = new string[] { “Sheet2” };
//Load the workbook with the spcified worksheet only.
LoadOptionsloadOptions = new LoadOptions(LoadFormat.Excel97To2003);
loadOptions.LoadDataOptions = dataOption;
loadOptions.LoadDataOnly = true;
//Creat the workbook.
workbook = new Workbook(“e:\test2\ExcelImportTest.xls”, loadOptions);
I downloaded Aspose.Cells 7.4.3.2 and tested it. Here are my results...
1. When LoadDataOnly = false All worksheets, including data and formatting are still present
2. When LoadDataOnly = true a. I get an error ("Excel found unreadable content...") when trying to open with Excel. When it does open, then I get a message asking me to repair the file. This also happen with the outFile.xls file that was attached to previous post/reply. b. Even though there is no data in Sheet1 and Sheet3, Sheet2 is also missing its data when viewing through Excel.
Please open my attached output file into MS Excel that is generated based on your input file. Could you confirm if it gives you the error as per 2 and you don’t see Sheet2 anymore. My Excel e.g 2007 does show the file perfectly and it does have Sheet2 with data and have other sheets without data.
Please make sure that you are using my attached version/fix v7.4.3.2.
If you find the issue on other files, please give us those files. Also, we appreciate if you could create a sample console application with v7.4.3.2, zip it and post it here with your template file, we will check your project and run your project soon.
I openned your outFile.xls using Excel 2010 and I receive the error messages as described in my previous reply. Attached are 2 files. 1st showing the initial error message and 2nd showing no data in sheet2.
I did use v7.4.3.2 of which I posted my results in my previous reply. Prior to version 7.4.3.2, I was using 7.4.3.0 of which is the reason of my original post.
Attached is a zip file that contains program.cs to run the code as well as sample input and output files
When processing a .xlsx file, the output file can be openned by Excel 2010 and I can see the data in Sheet2. However, that only works with the LoadDataOnly = true; When LoadDataOnly = false, all sheets and their data are in the output file.
When processing a .xls file, the output file cannot be openned by Excel 2010 without generating an error. However, that error is only generated with the LoadDataOnly = true. When LoadDataOnly = false, all sheets and their data are in the output file.
It looks like an issue with the product when opening the file into MS Excel 2010 as I can see the error message prompted by MS Excel 2010. It works fine opening the file into MS Excel 2007 though. Anyways, we will further evaluate your issue and log a ticket for it into our database, so we could fix it soon.
Tested it and now I can view .xls files in Excel 2010.
Couple of questions... it loads specific worksheets when the LoadDataOnly = true. But it loads all worksheets when the LoadDataOnly = false.
1. When the LoadDataOnly = true, then (according to the documentation) will load only the data no formating, correct? 2. If answer to #1 is Yes, then how can I load speciific worksheets and keep all the formating, if the only way to load specific worksheets is when the LoanDataOnly = true?
Is it possible to have Aspose.Cells load specific worksheets independent of the LoadDataOnly option? That way I can load specific worksheets with all formatting or just the data.
I did test, using the latest hotfix, and everything worked when using the LoadDataOnly = true
Yes, I notice the Loading specific worksheets feature only works when we set LoadDataOnly to set to True, it is not working when we set it to “false”. I have logged it and reopened your existing issue “CELLSNET-41702”. We will look into it soon.
Once we have any update on it, we will let you know here.