Read cell values from currently writing spreadsheet

Aspose.Cells Product Family
1

Hi Guys,

I am using Aspose 5.3.3 in VB.Net 2010 (.Net Framework 4). I am currently writing into a worksheet, but at different points in the code, i need to sum up values from different cells (like A10 to A20 at one point, A25 to A45 at another, A30 to A65, etc). How can I do this? I cannot use a variable which sums up the values as i write to the cells. I need a way to read the cell values at that time. Please help.

Thanks,

Sreejith

2

Hi,


Please see the topic:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/retrieving-data-from-cells.html

I think you may try to use your own code to loop through your desired cells area to retrieve the values accordingly, you may use Cell.Value, Cell.IntValue, Cell.DoubleValue, Cell.StringValue methods accordingly.

You can also use Excel “SUM” function to sum up your desired values accordingly. See a sample code below:

Sample code:
Dim workbook As New Workbook(“e:\test\Book1.xls”)
Dim ws As Worksheet = workbook.Worksheets(0)
Dim cells As Cells = ws.Cells
‘condition
Dim mode As String = “first”
Select Case mode
Case “first”
cells(“A100”).Formula = “=Sum(A1:A20)”
Case “second”
’…
'…
End Select

'Calculate the formulas
workbook.CalculateFormula()

Dim calcval As Double = cells(“A100”).DoubleValue
MessageBox.Show(calcval.ToString())

'Delete the formula if you need or comment out the below line otherwise.
cells(“A100”).PutValue(Nothing)

workbook.Save(“e:\test2\output.xls”)



3

Thank you very much Amjad Sahi. I'll get back to you if i have any more questions on this.

Sreejith

4

Hi Amjad,

I tried a similar version to what you have here. Here it is -

Xcel.SetFormula("C90", String.Format("=+SUBTOTAL(9,C{0}:C{1})",10,89))

where SetFormula is defined as (here it is sum of C10 to C89)

Protected Friend Sub SetFormula(ByVal Range As String, ByVal strFormula As String)

objcells(Range).Formula = strFormula

End Sub

Then later in the code (about 2 lines later) i say,

dblReadValue = Convert.ToDouble(Xcel.ReadCell("C90"))

where ReadCell is defined as

Protected Friend Function ReadCell(ByVal range As String) As String

Return objcells(range).StringValue 'where objcells is Worksheet.Cells

End Function

But when i execute this line of code, it returns empty string. Its like as if that formula will only be applied when the Excel sheet is saved and done, or something like that. Till then, you'll get only its original value (which was a blank). Please help.

Thank you very much again for your prompt responses.

Sreejith

5

Hi Amjad,

So basically, what i am doing here is, writing to a spreadsheet and reading from the cells that i just wrote (before finalising or saving the workbook). Which seems so much like the example you showed me, but i dont see the results as expected.

Thanks,

Sreejith

6

Hi,


I can see that you don’t call Workbook.CalculateFormula() method before retrieving calculated value from the cell, please call it as I used this method before obtaining the calculated values of the cell.

If you still find the issue, create a simple console application, zip it and post it here (with all the template file). We will check it soon.

Thank you.
7

Thanks Amjad. That worked. I forgot to include that. You guys are awesome. And thanks for replying to my queries so soon.

Sreejith