URGENT! How to make it to run faster -- dotnet general

Michel
5/4/2006 1:14:01 AM

I agree with Code Rodent, office applications are by definition slow... I
know this first hand as I have written a template automation system in 2005
for Word....

Try not to write to excel directly, create a CSV and import it by code, into
excel when finished, it will run a lot faster!

Remove the imports for excel, when you don't need them...

good luck

[quoted text, click to view]

Niyazi
5/4/2006 3:44:01 AM

Hi all,

To Patrice:
I am realy sorry that I use the URGENT keyword. I learn it now, and I will
not do it again. Thank you for kind understanding.

To Code and Michel:
Thank you very much for reading my post and giving me idea that I never
thought of.
Unfortunatly I don't know how to create a CSV and import it by code, into
excel when finished. Is it possible to show it to me by smal example.

To Michel:
What do you mean by "Remove the imports for excel, when you don't need
them.."?

I thank you all of you for your kind help.

Rgds,
Niyazi

[quoted text, click to view]

Adrian Gallero
5/4/2006 4:35:23 PM

Hi,

[quoted text, click to view]

You can speed up this a lot by not writing one value at a time, but
writing arrays of values instead. There is a big difference there.

But if you really want to make this fast, you might want to try a third
party solution for writing xls files. My company develops one, at
www.tmssoftware.com/go/?flexcelnet
you might want to take a look, but a 9000 rows report should not take
more than a couple of seconds.

[quoted text, click to view]

It will be much cheaper if you but a third party solution, and the
report will run much faster too ;) With a faster CPU you might win
some minutes but I doubt you will get down to minutes.

Hope this helps
Best regards,

Niyazi
5/5/2006 12:52:02 AM

Hi Adrian,

Thank you very much your kind help. First I don't effort to but Third Party
product, but I check the link and it is very good product. Fortunetly help of
some MS KB team member made my report to run in 4 minutes in my PC and 14
minutes in the second PC that I desribe in above.

I use the XSD adapter as well as the I construct my code so I copied on to
the clipborad and pasted whithout loosing any formating from the data.

First I createa string call mDataHolder. In my for loop instead of writing
into excel row by row I add them to the string.

BEGIN TO LOOP
mDataHolder = mDataHolder & row(1) & vbTab
....
....
....
....
mDataHolder = mDataHolder & row(1) & vbCr
NEXT

The string delimited by Tab characters for column and rows are delimited by
carriage returns.

Then use the code below to copy into clipboard:
System.Windows.Forms.Clipboard.SetDataObject(mDataHolder)

After initial interop call I use similar to below code:
oBook.Worksheets(1).Range("A1").Select()
oBook.Worksheets(1)..PasteSpecial(Excel.XlPasteType.xlPasteAll)

Then everythings worked in 4 minute. The report analysis the data nearly 3
min 20 second. And it takes about 30 second to writes into Excel.

Thank you very much and I have to give my special thank to MS Support team.

Thank you MS.

Rgds,
GC

[quoted text, click to view]

Adrian Gallero
5/5/2006 9:51:33 AM

Glad you could solve it! :)
I personally would not go with com operation anyway, but if it works
for you then it is good. Just 2 small comments:

1)

[quoted text, click to view]

I would make sure you clear the clipboard after pasting it into Excel.
If not, if your user by any change presses ctrl-v, it will paste the
whole 9000 rows in its application, and he probably will ont like this.
Probably you can even save the original clipboard contents, and restore
them after pasting in excel.

2)
As said on the previous post, if you really need to go with ole
automation, you can go much faster by setting the Range.Value to an
array, instead of a single value. I imagine it will work as fast as
copying from the clipboard, and you will not need to change your user's
clipboard. I am not sure on the syntax on .net (since luckily it has
been a lot of years since I had to do my last interop), but you can
probably search the interner for it, there should be plenty of
information about this.

Best regards,

Niyazi
5/6/2006 2:50:01 AM

Hi Adrian,
Thank you for comment. After I post my comment I realize that I should clear
the clipboard before inserting.

Simply for a few second I used API and lock the mouse and keyboard,
clear the clipboard and pasted into pre-formataed Excel sheet.

My problem was tha data hat I receive (in real-time) from AS400 DB2. The
database constructed nearly 10 years ago and had many user error. After I
received the data (in 49 sec) I analyz it (4 min) and re-formated as what
they want me to show and copy it into Excel sheet in 3 min.

Example the ID 1 have to be shown in 00001 format. The data I get from AS400
for custmer number nearly 5 characters but report says I have to show it in 4
characters and help nof the ms community I solved the all the problem. Expext
the insertaion into Excel. Now I solve it that via copying. If data was
already ready for Excel via AS400 then it will be perfect to use third party
solution as you describe above. But unfortunetly I have to create 23 reports
each month into Excel and as well as I make small program that converts Excel
sheet into notpad before we send it to destination database.

Once the 23 reports were okay it will goto management than they decide what
I should have to say. It means sometimes they play with Excel data because of
the inconsistent data that I work with. Once the 23 Excel sheet is ready for
send I recivied them by mail and convert then into notpad.

Part of the world that I am living doesn't have the great IT things anyway.
So I am stuck the garbiage data that was design 25 years ago and updated only
10 years ago.

My job real Job is Software Engineer, but currently I am working as
Programmer and IT Instructor. Things too worst in here comparing when I lived
in Japan nearly 6 years. People doesn't admit wheere the problem is.

So it wasn't too hard to work with but I have to keep change it allways
things I do, until I satisfy them. Till 9000 rows report everythings was
perfect. Because in Japan we used to AS400 to do the job and it does it in
few second. But the data that I work for has many user interface error. So if
the data missing I have to find one way or another. But I was realy upset
that it took 4 houers to complete 1 report.

Then actualy idea came from your third party soulution. All I did is cahne
the for loop to send the data into string and paste it into Excel. I worked
real hard solve this problem last 4 days and suddenly Friday morrning at 2.30
am came the idea that one way or another before the day finishes I have to
make it much fatsrer than what I did before. But I did a lot search and
reading and get many help from ms support kb team.

I realy find your third party product very useful. But I guess it is very
useful to normal programmer. I like the challange so so I combine your xsd
idea with copy-paste option and I did it. Friday I finshed my job at 11am.
And rest I enjoy to read the article in your third party product.

I created most of the class as in your third party product but it was very
time consuming. If I had some money I realy consider to buy the product for
my own use.

Again that me thank you and others for all your kind help.

Rgds,
GC

[quoted text, click to view]

Adrian Gallero
5/6/2006 8:09:06 AM

Hi,

Thanks for the detailed comments, I always like to know what other
people think and does, and the reasons why.

On this case:

[quoted text, click to view]

Believe me, it is the same here. I have been trying to get a dual core
machine for months, and everybody looks at me like I came from another
planet. What's wrong with a pentium 4?? It has hyperthreading! they
tell me.

[quoted text, click to view]

Just a quick note. Make sure you use a StirngBuilder and not a real
string, it will be much faster too.

[quoted text, click to view]

Yes, understanding a new product is time consuming, but I think it does
pay the price many times. I am not sure if it is clear to you from the
demos, but there are actually 2 ways you can create an xls file with
our product:
1) Use it just like ole automation:
To create a simple file you just need code like:

XlsFile xls = new XlsFile(true);
xls.Newfile();
for (int row = 0 ; row < NumberOfRows; row++)
for (int col = 0; col < NumberOfCols; col++)
{
xls.SetCellValue(row +1, col +1, Data[row][Col]);
}

xls.Save("myfile");

2) you can create a template, fill the data in a dataset and run a
report. While this is more time consuming initially than 1), it lets
you change the format very easily once created, and even your customers
can do it. (without touching any code). For pretty output, reports is
normally the way to go, but if you want a quick dump you can do it too.

And about the money, I understand that not everybody has the money to
pay, and this is why we price it on the inexpensive range. We prefer to
sell a lot of products at 100 bucks to a lot of people, than a little
at 2000 bucks.

But I would not want money to be the reason why you don't use it. So if
you like, just send me a private email at
agalleroDONOT@SPAMnetscape.net and I will send you a full registered
version for free.

Best regards,
Adrian.

Niyazi
5/7/2006 3:02:01 AM

Hi Adrian,

The reason that I cannot afford to pay is that I had some bank credit for my
father hospital expense. After 17 years I return my home country and start
everything from 0. Price is realy cheap but as I said it is imposible for now
for me to buy it.

I red the all article but I didn't try it yet. It looks like it doeas many
job very easily and over come time consuming job for many programmers.

Thank you for affer and I wil send you mail.

Thank you for your kind undersatnding.

Rgds,
Niyazi

[quoted text, click to view]

Niyazi
5/7/2006 3:32:01 AM

Hi Adrian,

I also ask you about the TDBPlanner. Do you sell any commponents for VS.NET
2003 for Planning and scheduling.

I realy like the TDBPlanner month and horizontal view. But I couldn't see
any demo version for VS.NET 2003.

Is it there or do you plan to make same TDBPlanner for VS.NET 2003?

Thank you.

Rgds,

Adrian Gallero
5/7/2006 4:28:27 AM

Hi Niyazi,

[quoted text, click to view]

I am not the best man to answer this, since I am only FlexCel's
developer. TMS is a big company and they offer a lot of products, and I
am not always up to date.

But about a planner, I know TMS offers an ASP.NET version (Webplanner),
on partnership with componentscience at http://www.componentscience.net/
I know because I wrote the code to export the planner to excel :)

About winforms, I don't think we are offering anything now, but you
might want to contact TMS support.

Best regards,
Adrian.

Ps: I got your email, I will be sending you a registered version on

Niyazi
5/7/2006 11:14:01 PM

Hi Adrian,

Thank you very much for all your kind help.

Rgds,
Niyazi

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet general

dotnet general : URGENT! How to make it to run faster