Hi
I have a report that I have to run it monthly in my machine.
My code in VB.NET and I access AS400 to get data, anaysie it and send into
pre formated Excel sheet. The data consist of 9000 rows.
I use data table and with for loop I send the data row by row in
pre-formated Excel sheet.
My machine is:
P4 3.0GHz with Hyperthreading
1 GB of memory
I also use thread technology on higest value to run the report.
If I run the report and not doing anything else with my PC the report will
created aproximetly in 4 hours.
Last weekend I put my application to another machine wich I recognize that
it has less configuration then my PC. P4 2.8Ghz + 996MB Ram with no
hyperthreading technology and report created in 8 hours time.
I know I did good programming but I still wonder why this reports takes long
time run. It looks like it writes each row in 1 sec to excel sheet. By the
way Excel sheet is not open as visualy but I opened it programaticaly to
write.
Here is my code:
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++
Imports KR105U
Imports CLS_MAIN
Imports Microsoft.Offic e.Core
Imports Excel.XlHAlign
Imports Excel.XlLineSty le
Imports Excel.XlBorders Index
Imports Excel.XlPattern
Imports Excel.XlDirecti on
Imports Excel.XlWindowV iew
Imports Excel.XlPageBre ak
Imports System.IO
Imports System.Globaliz ation
Imports System.Threadin g
Public Class clsWINEXCEL
'PRIVATE
Private UretimDate As String
Private row As DataRow
Private tmpTABLE As DataTable
Private myXRow As DataRow
'FRIEND
Friend WithEvents SaveFileDialog1 As System.Windows. Forms.SaveFileD ialog
Public Sub New()
mWriteExcel()
End Sub
#Region " WRITE EXCEL w/DIVISION 1000 "
Private Sub mWriteExcel()
'Save the current Regional Settings
Dim thisThread As System.Threadin g.Thread =
System.Threadin g.Thread.Curren tThread
Dim originalCulture As System.Globaliz ation.CultureIn fo =
thisThread.Curr entUICulture
'Create the New EXCEL Process
Dim oXL As Object = CreateObject("E xcel.Applicatio n") 'Excel.Applicat ion
Dim oWB As Object 'Excel.Workbook
Dim oST As Object 'Excel.Sheet
Dim oRng As Excel.Range 'Excel.Range
Try
thisThread.Curr entCulture = New System.Globaliz ation.CultureIn fo("en-US")
oWB = oXL.Workbooks.O pen(FileName:=c lsConst.m_Excel File,
UpdateLinks:=Fa lse, ReadOnly:=False )
Dim oSheet As Excel.Worksheet
With oWB.ActiveSheet
.Cells(3, 4).value = "DÖNEM: " & clsConst.m_DONE M_TARIHI
.Cells(4, 4).value = "ÃœRETIM: " & UretimDate
oSheet = oWB.ActiveSheet
Dim myRow As Integer = 13
Dim mySIRANO As Integer = 1
Dim mySIRAWORD As String = ""
For Each row In tmpTABLE.Rows
'ID NO
.Cells(myRow, 1).value = row(0)
'RG CODE
.Cells(myRow, 2).value = row(1)
'RG NAME
.Cells(myRow, 3).value = row(2)
'FK NAME
.Cells(myRow, 4).value = row(3)
'KSID NO
.Cells(myRow, 5).value = row(4)
'TYPE CODE
.Cells(myRow, 6).value = row(5)
myRow = myRow 1
Next
In the For loop it takes 1 second to write each row.
I ask my question before and it seems there is noone to help me.
I am also wondering to buy new PC with dual CPU. But is this new PC with
DUAL CPU make it to run my report much fatser or not.
I thank you in advance for reading my post.
Rgds,
Niyazi 13 2551
COM interop is costly. I would minimize the number of COM interop calls.
You could try to load all the values in an array and affect this array to a
range. It will copy all the values in a single interop call instead of
making one interop call per cell...
Though it doesn't seem applicable in this case, along the same lines, it's
quicker to run a presentation macro in Excel (possibly created from a .NET
application using few interop calls) rather than to run the same code from a
..NET application with a COM interop call each time you hit Excel...
As a side note, marking a message as URGENT is considered as a bad practice
as you have no way to know other problems are not as urgent as yours...
--
Patrice
"Niyazi" <Ni****@discuss ions.microsoft. com> a écrit dans le message de news: 8D************* *************** **...icrosof t.com... Hi
I have a report that I have to run it monthly in my machine. My code in VB.NET and I access AS400 to get data, anaysie it and send into pre formated Excel sheet. The data consist of 9000 rows.
I use data table and with for loop I send the data row by row in pre-formated Excel sheet.
My machine is: P4 3.0GHz with Hyperthreading 1 GB of memory
I also use thread technology on higest value to run the report. If I run the report and not doing anything else with my PC the report will created aproximetly in 4 hours.
Last weekend I put my application to another machine wich I recognize that it has less configuration then my PC. P4 2.8Ghz + 996MB Ram with no hyperthreading technology and report created in 8 hours time.
I know I did good programming but I still wonder why this reports takes long time run. It looks like it writes each row in 1 sec to excel sheet. By the way Excel sheet is not open as visualy but I opened it programaticaly to write.
Here is my code: +++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++
Imports KR105U Imports CLS_MAIN Imports Microsoft.Offic e.Core Imports Excel.XlHAlign Imports Excel.XlLineSty le Imports Excel.XlBorders Index Imports Excel.XlPattern Imports Excel.XlDirecti on Imports Excel.XlWindowV iew Imports Excel.XlPageBre ak Imports System.IO Imports System.Globaliz ation Imports System.Threadin g
Public Class clsWINEXCEL
'PRIVATE Private UretimDate As String Private row As DataRow Private tmpTABLE As DataTable Private myXRow As DataRow
'FRIEND Friend WithEvents SaveFileDialog1 As System.Windows. Forms.SaveFileD ialog
Public Sub New() mWriteExcel() End Sub
#Region " WRITE EXCEL w/DIVISION 1000 "
Private Sub mWriteExcel()
'Save the current Regional Settings Dim thisThread As System.Threadin g.Thread = System.Threadin g.Thread.Curren tThread Dim originalCulture As System.Globaliz ation.CultureIn fo = thisThread.Curr entUICulture
'Create the New EXCEL Process Dim oXL As Object = CreateObject("E xcel.Applicatio n") 'Excel.Applicat ion Dim oWB As Object 'Excel.Workbook Dim oST As Object 'Excel.Sheet Dim oRng As Excel.Range 'Excel.Range
Try thisThread.Curr entCulture = New System.Globaliz ation.CultureIn fo("en-US") oWB = oXL.Workbooks.O pen(FileName:=c lsConst.m_Excel File, UpdateLinks:=Fa lse, ReadOnly:=False ) Dim oSheet As Excel.Worksheet
With oWB.ActiveSheet .Cells(3, 4).value = "DÖNEM: " & clsConst.m_DONE M_TARIHI .Cells(4, 4).value = "ÜRETIM: " & UretimDate
oSheet = oWB.ActiveSheet
Dim myRow As Integer = 13 Dim mySIRANO As Integer = 1 Dim mySIRAWORD As String = ""
For Each row In tmpTABLE.Rows 'ID NO .Cells(myRow, 1).value = row(0)
'RG CODE .Cells(myRow, 2).value = row(1)
'RG NAME .Cells(myRow, 3).value = row(2)
'FK NAME .Cells(myRow, 4).value = row(3)
'KSID NO .Cells(myRow, 5).value = row(4)
'TYPE CODE .Cells(myRow, 6).value = row(5)
myRow = myRow 1 Next In the For loop it takes 1 second to write each row.
I ask my question before and it seems there is noone to help me.
I am also wondering to buy new PC with dual CPU. But is this new PC with DUAL CPU make it to run my report much fatser or not.
I thank you in advance for reading my post.
Rgds, Niyazi
Just a small thought, have you considered creating a CSV file that could be
imported into Excel?
"Patrice" <sc****@chez.co m> wrote in message
news:%2******** ********@TK2MSF TNGP02.phx.gbl. .. COM interop is costly. I would minimize the number of COM interop calls.
You could try to load all the values in an array and affect this array to a range. It will copy all the values in a single interop call instead of making one interop call per cell...
Though it doesn't seem applicable in this case, along the same lines, it's quicker to run a presentation macro in Excel (possibly created from a .NET application using few interop calls) rather than to run the same code from a .NET application with a COM interop call each time you hit Excel...
As a side note, marking a message as URGENT is considered as a bad practice as you have no way to know other problems are not as urgent as yours...
-- Patrice
"Niyazi" <Ni****@discuss ions.microsoft. com> a écrit dans le message de news: 8D************* *************** **...icrosof t.com... Hi
I have a report that I have to run it monthly in my machine. My code in VB.NET and I access AS400 to get data, anaysie it and send into pre formated Excel sheet. The data consist of 9000 rows.
I use data table and with for loop I send the data row by row in pre-formated Excel sheet.
My machine is: P4 3.0GHz with Hyperthreading 1 GB of memory
I also use thread technology on higest value to run the report. If I run the report and not doing anything else with my PC the report will created aproximetly in 4 hours.
Last weekend I put my application to another machine wich I recognize that it has less configuration then my PC. P4 2.8Ghz + 996MB Ram with no hyperthreading technology and report created in 8 hours time.
I know I did good programming but I still wonder why this reports takes long time run. It looks like it writes each row in 1 sec to excel sheet. By the way Excel sheet is not open as visualy but I opened it programaticaly to write.
Here is my code: +++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++
Imports KR105U Imports CLS_MAIN Imports Microsoft.Offic e.Core Imports Excel.XlHAlign Imports Excel.XlLineSty le Imports Excel.XlBorders Index Imports Excel.XlPattern Imports Excel.XlDirecti on Imports Excel.XlWindowV iew Imports Excel.XlPageBre ak Imports System.IO Imports System.Globaliz ation Imports System.Threadin g
Public Class clsWINEXCEL
'PRIVATE Private UretimDate As String Private row As DataRow Private tmpTABLE As DataTable Private myXRow As DataRow
'FRIEND Friend WithEvents SaveFileDialog1 As System.Windows. Forms.SaveFileD ialog
Public Sub New() mWriteExcel() End Sub
#Region " WRITE EXCEL w/DIVISION 1000 "
Private Sub mWriteExcel()
'Save the current Regional Settings Dim thisThread As System.Threadin g.Thread = System.Threadin g.Thread.Curren tThread Dim originalCulture As System.Globaliz ation.CultureIn fo = thisThread.Curr entUICulture
'Create the New EXCEL Process Dim oXL As Object = CreateObject("E xcel.Applicatio n") 'Excel.Applicat ion Dim oWB As Object 'Excel.Workbook Dim oST As Object 'Excel.Sheet Dim oRng As Excel.Range 'Excel.Range
Try thisThread.Curr entCulture = New System.Globaliz ation.CultureIn fo("en-US") oWB = oXL.Workbooks.O pen(FileName:=c lsConst.m_Excel File, UpdateLinks:=Fa lse, ReadOnly:=False ) Dim oSheet As Excel.Worksheet
With oWB.ActiveSheet .Cells(3, 4).value = "DÖNEM: " & clsConst.m_DONE M_TARIHI .Cells(4, 4).value = "ÜRETIM: " & UretimDate
oSheet = oWB.ActiveSheet
Dim myRow As Integer = 13 Dim mySIRANO As Integer = 1 Dim mySIRAWORD As String = ""
For Each row In tmpTABLE.Rows 'ID NO .Cells(myRow, 1).value = row(0)
'RG CODE .Cells(myRow, 2).value = row(1)
'RG NAME .Cells(myRow, 3).value = row(2)
'FK NAME .Cells(myRow, 4).value = row(3)
'KSID NO .Cells(myRow, 5).value = row(4)
'TYPE CODE .Cells(myRow, 6).value = row(5)
myRow = myRow 1 Next In the For loop it takes 1 second to write each row.
I ask my question before and it seems there is noone to help me.
I am also wondering to buy new PC with dual CPU. But is this new PC with DUAL CPU make it to run my report much fatser or not.
I thank you in advance for reading my post.
Rgds, Niyazi
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
"Niyazi" wrote: Hi
I have a report that I have to run it monthly in my machine. My code in VB.NET and I access AS400 to get data, anaysie it and send into pre formated Excel sheet. The data consist of 9000 rows.
I use data table and with for loop I send the data row by row in pre-formated Excel sheet.
My machine is: P4 3.0GHz with Hyperthreading 1 GB of memory
I also use thread technology on higest value to run the report. If I run the report and not doing anything else with my PC the report will created aproximetly in 4 hours.
Last weekend I put my application to another machine wich I recognize that it has less configuration then my PC. P4 2.8Ghz + 996MB Ram with no hyperthreading technology and report created in 8 hours time.
I know I did good programming but I still wonder why this reports takes long time run. It looks like it writes each row in 1 sec to excel sheet. By the way Excel sheet is not open as visualy but I opened it programaticaly to write.
Here is my code: +++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++
Imports KR105U Imports CLS_MAIN Imports Microsoft.Offic e.Core Imports Excel.XlHAlign Imports Excel.XlLineSty le Imports Excel.XlBorders Index Imports Excel.XlPattern Imports Excel.XlDirecti on Imports Excel.XlWindowV iew Imports Excel.XlPageBre ak Imports System.IO Imports System.Globaliz ation Imports System.Threadin g
Public Class clsWINEXCEL
'PRIVATE Private UretimDate As String Private row As DataRow Private tmpTABLE As DataTable Private myXRow As DataRow
'FRIEND Friend WithEvents SaveFileDialog1 As System.Windows. Forms.SaveFileD ialog
Public Sub New() mWriteExcel() End Sub
#Region " WRITE EXCEL w/DIVISION 1000 "
Private Sub mWriteExcel()
'Save the current Regional Settings Dim thisThread As System.Threadin g.Thread = System.Threadin g.Thread.Curren tThread Dim originalCulture As System.Globaliz ation.CultureIn fo = thisThread.Curr entUICulture
'Create the New EXCEL Process Dim oXL As Object = CreateObject("E xcel.Applicatio n") 'Excel.Applicat ion Dim oWB As Object 'Excel.Workbook Dim oST As Object 'Excel.Sheet Dim oRng As Excel.Range 'Excel.Range
Try thisThread.Curr entCulture = New System.Globaliz ation.CultureIn fo("en-US") oWB = oXL.Workbooks.O pen(FileName:=c lsConst.m_Excel File, UpdateLinks:=Fa lse, ReadOnly:=False ) Dim oSheet As Excel.Worksheet
With oWB.ActiveSheet .Cells(3, 4).value = "DÖNEM: " & clsConst.m_DONE M_TARIHI .Cells(4, 4).value = "ÜRETIM: " & UretimDate
oSheet = oWB.ActiveSheet
Dim myRow As Integer = 13 Dim mySIRANO As Integer = 1 Dim mySIRAWORD As String = ""
For Each row In tmpTABLE.Rows 'ID NO .Cells(myRow, 1).value = row(0)
'RG CODE .Cells(myRow, 2).value = row(1)
'RG NAME .Cells(myRow, 3).value = row(2)
'FK NAME .Cells(myRow, 4).value = row(3)
'KSID NO .Cells(myRow, 5).value = row(4)
'TYPE CODE .Cells(myRow, 6).value = row(5)
myRow = myRow 1 Next In the For loop it takes 1 second to write each row.
I ask my question before and it seems there is noone to help me.
I am also wondering to buy new PC with dual CPU. But is this new PC with DUAL CPU make it to run my report much fatser or not.
I thank you in advance for reading my post.
Rgds, Niyazi
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
"Michel" wrote: 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
"Niyazi" wrote:
Hi
I have a report that I have to run it monthly in my machine. My code in VB.NET and I access AS400 to get data, anaysie it and send into pre formated Excel sheet. The data consist of 9000 rows.
I use data table and with for loop I send the data row by row in pre-formated Excel sheet.
My machine is: P4 3.0GHz with Hyperthreading 1 GB of memory
I also use thread technology on higest value to run the report. If I run the report and not doing anything else with my PC the report will created aproximetly in 4 hours.
Last weekend I put my application to another machine wich I recognize that it has less configuration then my PC. P4 2.8Ghz + 996MB Ram with no hyperthreading technology and report created in 8 hours time.
I know I did good programming but I still wonder why this reports takes long time run. It looks like it writes each row in 1 sec to excel sheet. By the way Excel sheet is not open as visualy but I opened it programaticaly to write.
Here is my code: +++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++
Imports KR105U Imports CLS_MAIN Imports Microsoft.Offic e.Core Imports Excel.XlHAlign Imports Excel.XlLineSty le Imports Excel.XlBorders Index Imports Excel.XlPattern Imports Excel.XlDirecti on Imports Excel.XlWindowV iew Imports Excel.XlPageBre ak Imports System.IO Imports System.Globaliz ation Imports System.Threadin g
Public Class clsWINEXCEL
'PRIVATE Private UretimDate As String Private row As DataRow Private tmpTABLE As DataTable Private myXRow As DataRow
'FRIEND Friend WithEvents SaveFileDialog1 As System.Windows. Forms.SaveFileD ialog
Public Sub New() mWriteExcel() End Sub
#Region " WRITE EXCEL w/DIVISION 1000 "
Private Sub mWriteExcel()
'Save the current Regional Settings Dim thisThread As System.Threadin g.Thread = System.Threadin g.Thread.Curren tThread Dim originalCulture As System.Globaliz ation.CultureIn fo = thisThread.Curr entUICulture
'Create the New EXCEL Process Dim oXL As Object = CreateObject("E xcel.Applicatio n") 'Excel.Applicat ion Dim oWB As Object 'Excel.Workbook Dim oST As Object 'Excel.Sheet Dim oRng As Excel.Range 'Excel.Range
Try thisThread.Curr entCulture = New System.Globaliz ation.CultureIn fo("en-US") oWB = oXL.Workbooks.O pen(FileName:=c lsConst.m_Excel File, UpdateLinks:=Fa lse, ReadOnly:=False ) Dim oSheet As Excel.Worksheet
With oWB.ActiveSheet .Cells(3, 4).value = "DÖNEM: " & clsConst.m_DONE M_TARIHI .Cells(4, 4).value = "ÜRETIM: " & UretimDate
oSheet = oWB.ActiveSheet
Dim myRow As Integer = 13 Dim mySIRANO As Integer = 1 Dim mySIRAWORD As String = ""
For Each row In tmpTABLE.Rows 'ID NO .Cells(myRow, 1).value = row(0)
'RG CODE .Cells(myRow, 2).value = row(1)
'RG NAME .Cells(myRow, 3).value = row(2)
'FK NAME .Cells(myRow, 4).value = row(3)
'KSID NO .Cells(myRow, 5).value = row(4)
'TYPE CODE .Cells(myRow, 6).value = row(5)
myRow = myRow 1 Next In the For loop it takes 1 second to write each row.
I ask my question before and it seems there is noone to help me.
I am also wondering to buy new PC with dual CPU. But is this new PC with DUAL CPU make it to run my report much fatser or not.
I thank you in advance for reading my post.
Rgds, Niyazi
Hi,
Niyazi wrote: Hi
I have a report that I have to run it monthly in my machine. My code in VB.NET and I access AS400 to get data, anaysie it and send into pre formated Excel sheet. The data consist of 9000 rows.
<snip> Here is my code: .Cells(myRow, 1).value = row(0)
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.
I am also wondering to buy new PC with dual CPU. But is this new PC with DUAL CPU make it to run my report much fatser or not.
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,
Adrian.
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.Worksheet s(1).Range("A1" ).Select()
oBook.Worksheet s(1)..PasteSpec ial(Excel.XlPas teType.xlPasteA ll)
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
"Adrian Gallero" wrote: Hi,
Niyazi wrote:
Hi
I have a report that I have to run it monthly in my machine. My code in VB.NET and I access AS400 to get data, anaysie it and send into pre formated Excel sheet. The data consist of 9000 rows. <snip> Here is my code: .Cells(myRow, 1).value = row(0)
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.
I am also wondering to buy new PC with dual CPU. But is this new PC with DUAL CPU make it to run my report much fatser or not.
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, Adrian.
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) Then use the code below to copy into clipboard: System.Windows. Forms.Clipboard .SetDataObject( mDataHolder)
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,
Adrian.
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
"Adrian Gallero" wrote: 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)
Then use the code below to copy into clipboard: System.Windows. Forms.Clipboard .SetDataObject( mDataHolder)
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, Adrian.
Hi,
Thanks for the detailed comments, I always like to know what other
people think and does, and the reasons why.
On this case: 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.
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.
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.
Just a quick note. Make sure you use a StirngBuilder and not a real
string, it will be much faster too. 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.
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.SetCellValu e(row +1, col +1, Data[row][Col]);
}
xls.Save("myfil e");
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 ag***********@S PAMnetscape.net and I will send you a full registered
version for free.
Best regards,
Adrian. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Rob |
last post by:
I have a form - when you click the submit button, it appends a
variable to the URL (e.g. xyz.cgi?inputID=some_dynamic_variable)
It also opens a new page. Now, that some_dynamic_variable is actually
the name of a form element on the parent window.
On the new page, I have this javascript:
----
var var2 = location.search.substring(9);
document.write(var2)
|
by: Stefan Bauer |
last post by:
Hi NG,
we've got a very urgent problem... :(
We are importing data with the LOAD utility. The input DATE field data
is in the format DDMMYYYY (for days) and MMYYYY (for months).
The target data format is european DD.MM.YYYY (for days) and
01.MM.YYYY (for months).
The input format is not recognizable as a DATE input to a DB2 LOAD for
|
by: Tamir Khason |
last post by:
Follwing the struct:
public struct TpSomeMsgRep
{
public uint SomeId;
|
by: |
last post by:
Hi all, I have a website running on beta 2.0 on server 2003 web sp1 and I
keep getting the following error:-
Error In:
http://www.mywebsite.org/WebResource.axd?d=5WvLfhnJp5Lc8WhQSD4gdA2&t=632614619884218750
--------------------------------------------------------------------------------
System.Security.Cryptography.CryptographicException: Padding is invalid and
cannot be removed. at...
|
by: zeyais |
last post by:
Here is my HTML:
<style>
..leftcolumn{float:left;width:300px;border: 1px solid #ccc}
..rtcolumn{float:left;width:600px;border: 1px solid #ccc}
</style>
<body>
<div class="leftcolumn" id="d_links">
multiple <a href="hello.aspx?q=something">something</a><a
href="hello.aspx?q=something1">something1</a><a
| |
by: dembla |
last post by:
Hey Frnds can anyone help me in this
i need a program in 'c'
PROGRAM to print NxN Matrix
9 1 8 1 2 3
2 7 3 as 4 5 6
6 4 5 7 8 9
in sorted form
|
by: ginnisharma1 |
last post by:
Hi All,
I am very new to C language and I got really big assignment in my
work.I am wondering if anyone can help me.........I need to port
compiler from unix to windows and compiler is written partially in c
and partially in fortran. I guess i need to change host specific files
to make it working. I wonder if standard header files are going to
change in this case.my current windows compiler doesn't have
sys/resource.h but unix compiler...
|
by: alok sengar |
last post by:
hi,
I have already tried this URL's code
"http://www.java2s.com/Code/CSharp/Network/SimpleSNMP.htm"
but I am getting error when i am creating a UDP type Socket and recieving
packet from this socket.
some time error is---"An existing connection was forcibly closed by the
remote host"
and some time error is---
"A connection attempt failed because the connected party did not properly
respond after a period of time, or established...
|
by: N. Spiker |
last post by:
I am attempting to receive a single TCP packet with some text ending with
carriage return and line feed characters. When the text is send and the
packet has the urgent flag set, the text read from the socket is missing the
last character (line feed). When the same text is sent without the urgent
flag set, all of the characters are read.
I'm reading the data using the blocking read call of the network stream
class. The .NET...
|
by: Cirene |
last post by:
I used to use the Web Deployment Project with my VS2005 projects.
Now I've fully upgraded to VS2008. Do I have to download a new version of
the Web Deployment Project? If so where can I find it?
If not, how do I have to change my deployment strategy?
Thanks!
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |