How can I read data from xlsm file in C#.Net?

Aspose.Cells Product Family
1

Hi
How can I read data from xlsm file in C#.Net? I want to read a particular cell values from it.
I can do this with interop services but interop services is taking too much time. So I want to try it with aspose.

Thanks
Nitin Sharma

2

@nitin495,

See the documents on how to read/retrieve data from Excel spreadsheets cell(s) via Aspose.Cells for your reference:

Hope, this helps a bit.

3

Hi,
Thanks for the reply and it is working fine.
Can you share the example of writing the data in to multiple cells at once in xlsm file?

4

@nitin495,

See the document on adding data to worksheet cells for your reference.

5

@nitin495
Please check

6

How can I import data in particular column(D3,D4,D5,D6…)?
I want to write data in 1 go and don’t want to use for loop in my object.
For the reference sample file is attached.

?Capture1.JPG (49.5 KB)

7

@nitin495,
You can achieve this by implementing the LightCellsDataHandler interface. When encountering columns that you need to save, make both StartCell (int column) and ProcessCell (Cell cell) return true, while other columns return false.
Please refer to the following documents:

10

Hi,
How can I import data in particular column(D3,D4,D5,D6…) of xlsm file, Also I want to read particular cell of XLSM file Using .Net Core application.
Thanks
Nitin Sharma

11

@nitin495,

We already told you that you can use different ways to accomplish your task. You may choose any of the below approaches:

1). You can use Cells.ImportData method (see the document on importing data for your reference) to import data from various data sources into your desired cells. For example, to import data starting from D3 cell, the line of code can be:

worksheet.Cells.ImportDataTable(dataTable, true, "D3");

2). You can use Smart Markers feature for the task.

3). You can achieve this by implementing the LightCellsDataHandler interface. When encountering columns that you need to save, make both StartCell (int column) and ProcessCell (Cell cell) return true, while other columns return false. Please refer to the document on using LightCells mode for your complete reference.

See the document on how to read/retrieve data from different cells in Excel spreadsheet via Aspose.Cells for your complete reference.

12

@amjad.sahi
Last time I was working with .Net 4.8 framework. This question is around .Net Core 5.0.
I am trying to build Core web api and using same code which was earlier suggested but in .net core web api getting below error.
image.png (4.7 KB)

13

@nitin495,

Thanks for the screenshot.

The error is not related to Aspose.Cells APIs by any means. It is related to VS.NET and it might be related to configurations. Please browse and try online resources for assistance and figure your issue out.

14

Hi Amjad,
In the same solution rest of the code is working fine. But when I am trying to access the workbook (Workbook workbook = new Workbook(filepath)) this line is throwing the exception attached as image in previous comment. Also the same code was working fine with .net 4.8 framework.
In a new solution I am using .Net Core3.1. Does Aspose.Cells is compatible with the .Net Core3.1 framework?

15

@nitin495,

Yes, Aspose.Cells for .NET is compatible with .NET Core 3.1. Which version of the APIs you are using? Please try using latest version of Aspose.Cells for .NET v23.4, please use/add reference to relevant Aspose.Cells.Dll from “\netstandard2.0” sub-folder at your installation directory (if you have manually downloaded the version from Downloads section) or import/install Aspose.Cells from Nuget repos.

In case you still find the issue with latest version of Aspose.Cells for .NET, kindly do provide a standalone sample VS.NET project (please zip the application prior attaching) with resource files, we will check it soon.

16

Hi @amjad.sahi
When I am writing the values for very first time using worksheetStramData.Cells.ImportArray(dataStream4, 14, 3, true); this code the very first time it is taking around 2 seconds.If I am writing same data in subsequent request it is taking around .5 sec.
May I know why is this time difference and How can I take this time down to around .5 sec everytime?

17

@nitin495,

When importing an array into Excel spreadsheet, the time cost may depend upon the array size and type of data inside it. Moreover, initialization of array with its elements may take time and will also add to time cost. So, you may also evaluate/check the time of the underlying array initialization and its creation with data into account for first time.

If you still think it is an issue with the APIs, kindly do provide a standalone sample console application, zip the project (with resource files) and provide us, we will check it soon.

18

@amjad.sahi
Please find the sample file and code for writing the data. Here issue is that I have 1 button which is responsible to read and write the data also it have 2 text boxes which have read and write time. When I run this application very first time it is taking almost 2 secs but when I click button 2nd time the time would reduced to less than a second. How can I reduce time to less than second at very first time?

19

@nitin495,

Thanks for the template file and sample.

I checked your sample project with template file. Could you please try commenting out the following line of code in “WriteDataFromAspose()” method:
workbook.CalculateFormula();

This is necessary when you are retrieving the results in “ReadFileFromAspose()” method though:

Let us know if it makes any difference on your end?

20

@amjad.sahi
When I run the code very first time which I has given to you is taking around 2 seconds. But when I run it 2nd time and in further subsequent request it is taking few milliseconds. I am not sure what is the main reason of this time difference. Also I have changed the code suggested by you, I move workbook.CalculateFormula() this line of code to ReadFileFromAspose() but difference is still there.
For reference I am attaching screen shot you can see there is significant time difference in “Total Time(ms)” field when I run it 1st and 2nd time.

image.png (6.7 KB)
image.png (5.4 KB)

21

@nitin4,

Did you first add/import data to worksheets in the template file using “WriteDataFromAspose()” and re-saved the file, then used ReadFileFromAspose() method to open the updated file via Aspose.Cells APIs to get the resultant values (after using workbook.CalculateFormula())?

Could you please share “newfile4.xlsm” which is not touched/processed by “WriteDataFromAspose()” method at all. We will test it soon.

22

@amjad.sahi
Yes, First I am writing the data in sheets “Data from EB” and “Stream data (EB)” and there are formulas written in file which update the few sheet cells “DP-Calcs” and after writing the values I read sheet “DP-Calcs”.
I am attaching the sample file which is fresh and not touched by Aspose because of some privacy concern I am deleting sheets which are not required rest of sheets are therein the file.

file.7z (130.3 KB)