Is it possiible?

[Vitalik]

Hello there,

Can anyone tell me if it is possible with OWC10 used on the server side
(under ASP.NET) to open an existing .xls file, update a number of cells
(individually, or using some group method), recalculate embedded formulas
and get updated values from formula fields to use within our application?
If in the process we don't need to save changed .xls file (as a result of
recalculation) in order to get updated results, then it would be even
better.

Thanks for any input,

Vitaliy

 

[cellbert]

You can't open an xls file in OWC10, but you can open an
xls that's been saved to an .xml or .htm file by Excel.
All the rest is then possible using COM interop.

 

[Vitalik]

Thank you Dan.

Using the info from the cellbert's post, I figured out the steps that you
described in detail and currently use XMLURL to load the .xml file.
Unfortunately, now I face more obstacles. First of all, let me say that our
objective is fast response time and it may turn out that OWC10 or up is not
up to the task. So, here are the issues that we face:

1. Our .xls files have many work sheets with formula and data fields in
tables. So, the size of the .xls file is not small (~ 2 MB for simple
files). When saved as XML file the size goes up by ~ 5 times. For the file
of this size (~10 MB) it takes ~ 25 sec to initially load and parse the .xml
file (Excel itself is much faster to load .xml file). This we kind of
solved by caching the OWC10.Spreadsheet object (1st user will wait longer or
we need to do it on app start up), but still it is sad that smaller .xls
file cannot be used and we are forced to use less efficient file format. I
tried both XMLData and XMLURL and the timing was pretty much identical.
2. Our spreadsheets accept large number of input values, so we are
interested in delayed recalculation (we also have many "output" fields.) But
it apprears that setting

OWC10_SS.Calculation = OWC10.XlCalculation.xlCalculationManual

is ignored, since there is a noticeable delay when we set each input value
using this code:

With OWC10_SS
.ActiveSheet.Range("B11").Select()
.Selection.Value = stringVariable

.ActiveSheet.Range("B11").Select()
.Selection.Value = stringVariable

....
....
End With

and later reading the outputs...

With OWC10_SS
.ActiveSheet.Range("F5").Select()
lblControl1.Text = .Selection.Value()

.ActiveSheet.Range("F11").Select()
lblControl2.Text = .Selection.Value()

End With

Is there a way to "batch" assign/read more values at once?

3. Since we are talking about using it from ASP.NET, we would be interested
to know how best to provide for multi-user environment. Our concern is
about processing >1 request at the sane time and mixing up inputs/outputs
from different users. Any ideas?

Thanks in advance,

Vitaliy

OWC can not open *.xls files however:

1) Excel can save files as XML ([File].[Save As...] dialog
2) OWC10 SpreadSheet can consume this data in two ways:
a) SpreadSheet.XMLData = StringValue
b) SpreadSheet.XMLURL = PathToFile

1) Within the ASPX page, retrieve the path to the
ExcelWorkbook.XML file.
2) Set XMLURL to the complete pathname for the file
Or
2) Load the ExcelWorkbook.XML file into a variable and set
XMLData to the variable
3) Change the data as needed in the OWC10 SpreadSheet
control.

At this point, the data could be displayed on the client,
or a specific value from the SpreadSheet control used to
send some other information to the client.
__________________
-----
Thx
Dan

Quality is not just the absence of defects, but the
presence of value throughout the entire transaction.

-----Original Message-----
Hello there,

Can anyone tell me if it is possible with OWC10 used on the server side
(under ASP.NET) to open an existing .xls file, update a number of cells
(individually, or using some group method), recalculate embedded formulas
and get updated values from formula fields to use within our application?
If in the process we don't need to save changed .xls file (as a result of
recalculation) in order to get updated results, then it would be even
better.

Thanks for any input,

Vitaliy


.

 

[Dan Ricker]

I "believe" that when you load the XML (either way) that
the Calculation property is reset to whatever is held in
the XML (I actually hope so anyway...).

I'm not sure if Excel writes out this property as part of
the XML file on save...

If Excel does write out the property, the save the file
with the property set to manual in Excel.

If Excel does not write out the property, then set the
property in OWC. Read OWC's XML to see where/how the
property is stored. Add the XML Tag to the Excel Saved XML

-----Original Message-----
There are definitely performance issues loading XML.
Regarding setting and getting cell values, never do
Select -
do:
With OWC10_SS
.ActiveSheet.Range("B11").Value = stringVariable
....

Also a trick for setting multiple cells in one call:
let's say we're setting b3 and d6

Dim v as Variant

v = .Range("b3:d6").Value
v(1,1) = 12 ' b3
v(4,3) = 19 ' d6
..Range("b3:d6").Value = v
Looks like more data sloshing around but often faster.
-- hk

-----Original Message-----
Thank you Dan.

Using the info from the cellbert's post, I figured out the steps that you
described in detail and currently use XMLURL to load the .xml file.
Unfortunately, now I face more obstacles. First of all, let me say that our
objective is fast response time and it may turn out that OWC10 or up is not
up to the task. So, here are the issues that we face:

1. Our .xls files have many work sheets with formula and data fields in
tables. So, the size of the .xls file is not small (~ 2 MB for simple
files). When saved as XML file the size goes up by ~ 5 times. For the file
of this size (~10 MB) it takes ~ 25 sec to initially

load
and parse the .xml

file (Excel itself is much faster to load .xml file). This we kind of
solved by caching the OWC10.Spreadsheet object (1st user will wait longer or
we need to do it on app start up), but still it is sad that smaller .xls
file cannot be used and we are forced to use less efficient file format. I
tried both XMLData and XMLURL and the timing was pretty much identical.
2. Our spreadsheets accept large number of input

values,
so we are

interested in delayed recalculation (we also have many "output" fields.) But
it apprears that setting

OWC10_SS.Calculation = OWC10.XlCalculation.xlCalculationManual

is ignored, since there is a noticeable delay when we

set
each input value

using this code:

With OWC10_SS
.ActiveSheet.Range("B11").Select()
.Selection.Value = stringVariable

.ActiveSheet.Range("B11").Select()
.Selection.Value = stringVariable

....
....
End With

and later reading the outputs...

With OWC10_SS
.ActiveSheet.Range("F5").Select()
lblControl1.Text = .Selection.Value()

.ActiveSheet.Range("F11").Select()
lblControl2.Text = .Selection.Value()

End With

Is there a way to "batch" assign/read more values at once?

3. Since we are talking about using it from ASP.NET, we would be interested
to know how best to provide for multi-user environment. Our concern is
about processing >1 request at the sane time and mixing up inputs/outputs
from different users. Any ideas?

Thanks in advance,

Vitaliy

OWC can not open *.xls files however:

1) Excel can save files as XML ([File].[Save As...] dialog
2) OWC10 SpreadSheet can consume this data in two ways:
a) SpreadSheet.XMLData = StringValue
b) SpreadSheet.XMLURL = PathToFile

1) Within the ASPX page, retrieve the path to the
ExcelWorkbook.XML file.
2) Set XMLURL to the complete pathname for the file
Or
2) Load the ExcelWorkbook.XML file into a variable and set
XMLData to the variable
3) Change the data as needed in the OWC10 SpreadSheet
control.

At this point, the data could be displayed on the client,
or a specific value from the SpreadSheet control used to
send some other information to the client.
__________________
-----
Thx
Dan

Quality is not just the absence of defects, but the
presence of value throughout the entire transaction.



-----Original Message-----
Hello there,

Can anyone tell me if it is possible with OWC10 used on
the server side
(under ASP.NET) to open an existing .xls file, update a
number of cells
(individually, or using some group method), recalculate
embedded formulas
and get updated values from formula fields to use within
our application?
If in the process we don't need to save changed .xls file
(as a result of
recalculation) in order to get updated results, then it
would be even
better.

Thanks for any input,

Vitaliy


.

.

.

 

[Vitalik]

Dan, Cellbert - thanks for your answers.
I have concluded, that keeping the Calculation property at Auto helps to
reduce time, since when assigning new "input" values there is a delay which
is the same either way. Calling Calculate method forces another unnecessary
delay.

So, now when processing time is comparable with other tools, I'm looking for
an answer to a simple question that I raised in my other posting (OWC10
server side use in multi-user environment.) Your (or anybody else) opinion
is welcome.

Thanks,
Vitaliy

I "believe" that when you load the XML (either way) that
the Calculation property is reset to whatever is held in
the XML (I actually hope so anyway...).

I'm not sure if Excel writes out this property as part of
the XML file on save...

If Excel does write out the property, the save the file
with the property set to manual in Excel.

If Excel does not write out the property, then set the
property in OWC. Read OWC's XML to see where/how the
property is stored. Add the XML Tag to the Excel Saved XML

-----Original Message-----
There are definitely performance issues loading XML.
Regarding setting and getting cell values, never do
Select -
do:
With OWC10_SS
.ActiveSheet.Range("B11").Value = stringVariable
....

Also a trick for setting multiple cells in one call:
let's say we're setting b3 and d6

Dim v as Variant

v = .Range("b3:d6").Value
v(1,1) = 12 ' b3
v(4,3) = 19 ' d6
..Range("b3:d6").Value = v
Looks like more data sloshing around but often faster.
-- hk

-----Original Message-----
Thank you Dan.

Using the info from the cellbert's post, I figured out the steps that you
described in detail and currently use XMLURL to load the .xml file.
Unfortunately, now I face more obstacles. First of all, let me say that our
objective is fast response time and it may turn out that OWC10 or up is not
up to the task. So, here are the issues that we face:

1. Our .xls files have many work sheets with formula and data fields in
tables. So, the size of the .xls file is not small (~ 2 MB for simple
files). When saved as XML file the size goes up by ~ 5 times. For the file
of this size (~10 MB) it takes ~ 25 sec to initially

load
and parse the .xml

file (Excel itself is much faster to load .xml file). This we kind of
solved by caching the OWC10.Spreadsheet object (1st user will wait longer or
we need to do it on app start up), but still it is sad that smaller .xls
file cannot be used and we are forced to use less efficient file format. I
tried both XMLData and XMLURL and the timing was pretty much identical.
2. Our spreadsheets accept large number of input

values,
so we are

interested in delayed recalculation (we also have many "output" fields.) But
it apprears that setting

OWC10_SS.Calculation = OWC10.XlCalculation.xlCalculationManual

is ignored, since there is a noticeable delay when we

set
each input value

using this code:

With OWC10_SS
.ActiveSheet.Range("B11").Select()
.Selection.Value = stringVariable

.ActiveSheet.Range("B11").Select()
.Selection.Value = stringVariable

....
....
End With

and later reading the outputs...

With OWC10_SS
.ActiveSheet.Range("F5").Select()
lblControl1.Text = .Selection.Value()

.ActiveSheet.Range("F11").Select()
lblControl2.Text = .Selection.Value()

End With

Is there a way to "batch" assign/read more values at once?

3. Since we are talking about using it from ASP.NET, we would be interested
to know how best to provide for multi-user environment. Our concern is
about processing >1 request at the sane time and mixing up inputs/outputs
from different users. Any ideas?

Thanks in advance,

Vitaliy


OWC can not open *.xls files however:

1) Excel can save files as XML ([File].[Save As...] dialog
2) OWC10 SpreadSheet can consume this data in two ways:
a) SpreadSheet.XMLData = StringValue
b) SpreadSheet.XMLURL = PathToFile

1) Within the ASPX page, retrieve the path to the
ExcelWorkbook.XML file.
2) Set XMLURL to the complete pathname for the file
Or
2) Load the ExcelWorkbook.XML file into a variable and set
XMLData to the variable
3) Change the data as needed in the OWC10 SpreadSheet
control.

At this point, the data could be displayed on the client,
or a specific value from the SpreadSheet control used to
send some other information to the client.
__________________
-----
Thx
Dan

Quality is not just the absence of defects, but the
presence of value throughout the entire transaction.



-----Original Message-----
Hello there,

Can anyone tell me if it is possible with OWC10 used on
the server side
(under ASP.NET) to open an existing .xls file, update a
number of cells
(individually, or using some group method), recalculate
embedded formulas
and get updated values from formula fields to use within
our application?
If in the process we don't need to save changed .xls file
(as a result of
recalculation) in order to get updated results, then it
would be even
better.

Thanks for any input,

Vitaliy


.



.

.