473,287 Members | 1,418 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Reading and working with Excel Document


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
4 2541
"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

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
"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

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: David Krmpotic | last post by:
hi!! I use the following code to operate excel document: Excel.Application excelApp = new Excel.ApplicationClass(); //excelApp.Visible = true; string workbookPath = "c:\\eltina.xls";...
0
by: Alex Shirley | last post by:
Hi I'm still banging my head on how to import an Excel XML spreadsheet into a dataset. The code will work for standard XML files, but not XML files made with Excel (I get 'Specified cast is not...
1
by: Turner, GS \(Geoff\) | last post by:
> -----Original Message----- > From: siliconwafer > Posted At: 19 August 2005 15:20 > Posted To: c > Conversation: reading an excel file in C? > Subject: reading an excel file in C? > > >...
5
by: Scott M. Lyon | last post by:
I've just discovered a bug in some code I wrote a little while ago, and I need you guys' help to fix it. My program imports data from a standard Excel Spreadsheet (just with specific column...
6
by: Michael Groeger | last post by:
Hi, I have an aspx page which generates an excel document and transfers it to the browser as attachment. Normally, once the document is transferred the open save dialog prompts to open or save...
3
by: jeremy.bird | last post by:
I am looking for a possible solution or bump in the right direction when working with excel on the web. Currently I am using and developing in visual studio 2005. Here is the situation I have a lot...
1
by: Chris Yan | last post by:
Hi All I have an excel sheet with Chinese characters in them. I'm using the OLEBE 4.0 Jet Driver in C# Microsoft Visual Studio 2005 to read from the Excel sheet and then enter into mySQL...
0
by: Eric | last post by:
Hi all, First i appoligise for my bad english. For a friend i need to create a script that can read XLS documents. Well that's not the problem anymore... with the code (down here) i've already...
8
by: msghaleb | last post by:
Hi all, I've created an asp page which is reading an Excel file, the code is working ok only not on IIS !! here is the code: oConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _ ...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.