423,851 Members | 1,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,851 IT Pros & Developers. It's quick & easy.

Reading and working with Excel Document

P: n/a

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
Nov 5 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"stephen" <st********@hotmail.comwrote in message
news:eV**************@TK2MSFTNGP06.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...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
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Nov 5 '08 #2

P: n/a

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]" <ma**@markNOSPAMrae.netwrote in message
news:es**************@TK2MSFTNGP02.phx.gbl...
"stephen" <st********@hotmail.comwrote in message
news:eV**************@TK2MSFTNGP06.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...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
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Nov 11 '08 #3

P: n/a
"stephen" <st********@hotmail.comwrote in message
news:%2****************@TK2MSFTNGP04.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

Nov 11 '08 #4

P: n/a

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]" <ma**@markNOSPAMrae.netwrote in message
news:Of**************@TK2MSFTNGP02.phx.gbl...
"stephen" <st********@hotmail.comwrote in message
news:%2****************@TK2MSFTNGP04.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

Nov 11 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.