Reading and working with Excel Document

[stephen]

Hi all,

I am working with Excel. I read an excel document using ExcelReader and lets
say it has 10 columns. I have to read each record and based on a specified
column peform some activites and then write the result at the end of the
record (so will have 11 columns in total) At the same time, I am performing
other calculations which has to be written at the end of the file (for eg.
Total records, ..., ...).

Currently, I am using ExcelReader and peforming the above operations, it
does the needful but it takes forever (some sheets have like 1000+ records).
Question: If I read the Excel into ExcelReader, am I still connected to the
excel document or have I created an ExcelObject in memory and then working
with it before updating the actual excel document at the end when I say
WorkBook.Save() ?

If this approach is not efficient, Is there any other efficient approach?

I was thinking about using DataSet, read the excel document have DataTables
which = the number of sheets, then perform the above operations and then
write it to the excel sheet after cleaning the Excel. Is this a good
approach?

Thanks for all your help,
Stephen

[Mark Rae [MVP]]

Re: Reading and working with Excel Document

"stephen" <stephen_jn@hot mail.comwrote in message
news:eVEdxW4PJH A.4680@TK2MSFTN GP06.phx.gbl...

I created an ExcelObject in memory
If this approach is not efficient,

Not only is it not efficient, it is not recommended or supported by
Microsoft because Excel (and the rest of Office) wasn't designed to be used
in this way:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2

http://support.microsoft.com/default.aspx/kb/288367

Is there any other efficient approach?

http://www.aspose.com/categories/file-format-components/aspose.cells-for-.net-and-java/default.aspx

--
Mark Rae
ASP.NET MVP

http://www.markrae.net

[stephen]

Re: Reading and working with Excel Document


Hi Mark,

Thanks for the info, I will look into this as suggested. I have a question,
though crude, please help me understand this:
I read the excel object in memory (excel object is a lookup file has 300
records). I have another large file that has (let say 700 records) and when
I need to search for a "search string" in this excel object. I loop through
it and if I get a match, I proceed further.... the approx time it takes is
around 30 minutes (the way I did it was loop through each line again and
again for a possible match for the "search sting" using for each loop) - I
know this method is wrong

Then, I read the entire lookup file into a DataTable and now i search for
the possible "search string" and this time it took me like <5 min for the
entire process. How is this efficient. If i understand, the searching for a
string in a datatable is still in a loop right.

Any advice,
Stephen

"Mark Rae [MVP]" <mark@markNOSPA Mrae.netwrote in message
news:esPx3a4PJH A.4760@TK2MSFTN GP02.phx.gbl...

"stephen" <stephen_jn@hot mail.comwrote in message
news:eVEdxW4PJH A.4680@TK2MSFTN GP06.phx.gbl...
>

>I created an ExcelObject in memory
>If this approach is not efficient,

>
Not only is it not efficient, it is not recommended or supported by
Microsoft because Excel (and the rest of Office) wasn't designed to be
used in this way:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2

http://support.microsoft.com/default.aspx/kb/288367

>

>Is there any other efficient approach?

http://www.aspose.com/categories/file-format-components/aspose.cells-for-.net-and-java/default.aspx

>
>
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Mark Rae [MVP]]

Re: Reading and working with Excel Document

"stephen" <stephen_jn@hot mail.comwrote in message
news:%23F7HpODR JHA.4728@TK2MSF TNGP04.phx.gbl. ..

[top-posting corrected]

>>I created an ExcelObject in memory
>>If this approach is not efficient,

>>
>Not only is it not efficient, it is not recommended or supported by
>Microsoft because Excel (and the rest of Office) wasn't designed to be
>used in this way:
>http://support.microsoft.com/default...US;q257757#kb2
>http://support.microsoft.com/default.aspx/kb/288367
>>

>>Is there any other efficient approach?

>http://www.aspose.com/categories/fil...a/default.aspx

>
Thanks for the info, I will look into this as suggested. I have a
question, though crude, please help me understand this:

Apologies - I have obviously. not explained clearly enough.

I read the Excel object in memory

If you do this server-side, it is only a matter of time before your app
crashes. Office is not designed to be used in this way, which is the reason
that Microsoft don't support it. Please read the links I supplied, paying
particular attention to sections such as:

"Caution. Automation of any Office application from an unattended,
non-interactive user account is risky and unstable. A single error in code
or configuration can result in a dialog box that can cause the client
process to stop responding (hang), that can corrupt data, or that can even
crash the calling process (which could bring down your Web server if the
client is ASP)."

"Warning. Office was not designed, and is not safe, for unattended execution
on a server. Developers who use Office in this manner do so at their own
risk."

I'm really not making this up...


--
Mark Rae
ASP.NET MVP

http://www.markrae.net

[stephen]

Re: Reading and working with Excel Document


Hi Mark,

I am not doubting the answers that you have provided and never meant to. I
appreciate all the help and the valuable info.

Thanks,
Stephen

"Mark Rae [MVP]" <mark@markNOSPA Mrae.netwrote in message
news:OfkLEfDRJH A.3808@TK2MSFTN GP02.phx.gbl...

"stephen" <stephen_jn@hot mail.comwrote in message
news:%23F7HpODR JHA.4728@TK2MSF TNGP04.phx.gbl. ..
>
[top-posting corrected]
>

>>>I created an ExcelObject in memory
>>>If this approach is not efficient,
>>>
>>Not only is it not efficient, it is not recommended or supported by
>>Microsoft because Excel (and the rest of Office) wasn't designed to be
>>used in this way:
>>http://support.microsoft.com/default...US;q257757#kb2
>>http://support.microsoft.com/default.aspx/kb/288367
>>>
>>>Is there any other efficient approach?
>>http://www.aspose.com/categories/fil...a/default.aspx

>>
>Thanks for the info, I will look into this as suggested. I have a
>question, though crude, please help me understand this:

>
Apologies - I have obviously. not explained clearly enough.
>

>I read the Excel object in memory

>
If you do this server-side, it is only a matter of time before your app
crashes. Office is not designed to be used in this way, which is the
reason that Microsoft don't support it. Please read the links I supplied,
paying particular attention to sections such as:
>
"Caution. Automation of any Office application from an unattended,
non-interactive user account is risky and unstable. A single error in code
or configuration can result in a dialog box that can cause the client
process to stop responding (hang), that can corrupt data, or that can even
crash the calling process (which could bring down your Web server if the
client is ASP)."
>
"Warning. Office was not designed, and is not safe, for unattended
execution on a server. Developers who use Office in this manner do so at
their own risk."
>
I'm really not making this up...
>
>
--
Mark Rae
ASP.NET MVP
http://www.markrae.net