We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Loading specific worksheets not working

Aspose.Cells Product Family
#1

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.

Thanks

#2

Hi,


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.

Thank you.
#3

Thank you for your quick reply.

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.

Thank you

#4

Hi,


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).
LoadDataOption dataOption = new LoadDataOption();
dataOption.SheetNames = new string[] { “Sheet2” };

//Load the workbook with the spcified worksheet only.
LoadOptions loadOptions = new LoadOptions(LoadFormat.Excel97To2003);
loadOptions.LoadDataOptions = dataOption;
loadOptions.LoadDataOnly = true;

//Creat the workbook.
workbook = new Workbook(“e:\test2\ExcelImportTest.xls”, loadOptions);

//Perform your desired task.

//Save the workbook.
workbook.Save(“e:\test2\outputFile.xls”);

Thank you.
#5

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.

Thanks

#6

Hi,


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.

Thank you.

#7

Thanks for your quick response.

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.

Thanks

#8

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.

Thanks

#9

Hi,


Thanks for the project with files.

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.

Thank you.
#10

Thank you. Looking forward to the fix.

#11

Hi,


We have evaluated your issue further. I have now logged a ticket with an id “CELLSNET-41702” for your issue. We will look into your issue soon.

Once we have any update on it, we will let you know here.

thank you.
#12

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have fixed the issue.

Please download and try this fix: Aspose.Cells for .NET v7.4.3.4 and let us know your feedback.

#13

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?

Thanks.

#14

Hi,


1) Yes, your understanding is correct.

2) Please do not use LoadDataOnly attribute in your code and let us know if it works fine for your needs.

Thank you.


#15

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

Thanks

#16

Hi,


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.

thank you.
#17

Hi,


We have fixed your issue now. We will soon provide you the fix here.
With the new fix, you can use custom LoadFilter to load specific worksheet only:
e.g
Sample code:

new Workbook(template, new LoadOptions() { LoadFilter = new LoadFilterSheet() });
…
class LoadFilterSheet : LoadFilter
{
public override void StartSheet(Worksheet sheet)
{
if (sheet.Index == 0)
{ //only load the first sheet
m_LoadDataFilterOptions = Aspose.Cells.LoadDataFilterOptions.All;
}
else
{
m_LoadDataFilterOptions = Aspose.Cells.LoadDataFilterOptions.None;
}
}
}

Thank you.
#18

Hi,


Please try our latest version/fix: Aspose.Cells for .NET v16.11.7

Your issue “CELLSNET-41702” should be fixed in it.

Let us know your feedback.

Thank you
#19

The issues you have found earlier (filed as CELLSNET-41702) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.