|
Hi guys
My problem is this . I need to generate a large number of records from SQL server 2000 to and excel spreadsheet. However the durations of time id too long. For about 900 records there's about 40 min.
Any idea or guide to boast up the speed?
i am generating to excel .xls
Basically i have a main details table link to many sub details Table
Then first i select all the main info id and use the for loop to loop through all the sub details table whether the id is match. then insert into the excel spreadsheet. The method i use is Create the XLS file then connect to it and do insert statement to input data. This method work but when inserting 900 records it took about 30 mins or more....
Any suggetion or solution please guide.... Need help man
Thanks a million guys need it urgently
KaKaShi HaTaKe
KaKaShi HaTaKe
|
|
|
|
|
Unless you REALLY need to create an actual .XLS file I would recommend creating a .csv file and letting excel convert it when it is opened. This eliminates all the VBA stuff and you should be able to do 900 rows in under 1 sec.
There are plenty of article/examples on CP
|
|
|
|
|
What's a CP?
KaKaShi HaTaKe
|
|
|
|
|
1. take one of the output excel files and save it as XML
2. open the XML file with your code editor (Visual Studio I suppose?)
3. see what the file's structure looks like
4. adapt the following sub to your file's structure, and content, and to whatever other specific requirements you have:
Public Sub DatasetToExcel(ByVal ds As DataSet, ByVal ExcelPath As String, Optional ByVal Title1 As String = "", Optional ByVal Title2 As String = "")<br />
If File.Exists(ExcelPath) = True Then<br />
If MsgBox("Tiedosto on jo olemassa. Haluatko korvata sen?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then<br />
File.Delete(ExcelPath)<br />
Else<br />
ExcelPath = InputBox("Muuta nimi: ", "Tiedosto olemassa", ExcelPath)<br />
End If<br />
End If<br />
'use .xls even though its an xml file<br />
Dim doc As New StreamWriter(ExcelPath)<br />
Try<br />
Dim startExcelXML As String = ""<br />
startExcelXML &= "<xml version="">"<br />
startExcelXML &= vbNewLine & "<workbook xmlns="" urn:schemas-microsoft-com:office:spreadsheet="" hold=" /"> startExcelXML &= vbNewLine & Microsoft.VisualBasic.Chr(10) & "" + " xmlns:o=""urn:schemas-microsoft-com:office:office"""<br />
startExcelXML &= vbNewLine & "xmlns:x=""urn:schemas- microsoft-com:office:" + "excel"""<br />
startExcelXML &= vbNewLine & "xmlns:ss=""urn:schemas-microsoft-com:" + "office:spreadsheet"">"<br />
<br />
'write the styles tags that format the data and cells properly<br />
startExcelXML &= vbNewLine & "<styles>"<br />
<br />
startExcelXML &= vbNewLine & " <style ss:id="" default="" ss:name="" normal="">"<br />
startExcelXML &= vbNewLine & " <alignment ss:vertical="" bottom="" />"<br />
startExcelXML &= vbNewLine & " <borders />"<br />
startExcelXML &= vbNewLine & " <font />"<br />
startExcelXML &= vbNewLine & " <interior />"<br />
startExcelXML &= vbNewLine & " <numberformat />"<br />
startExcelXML &= vbNewLine & " <protection />"<br />
startExcelXML &= vbNewLine & " </style>"<br />
'Title style<br />
startExcelXML &= vbNewLine & "<style ss:id="" title="">"<br />
startExcelXML &= vbNewLine & "<alignment ss:horizontal="" center="" ss:vertical="" bottom="" />"<br />
startExcelXML &= vbNewLine & "<font x:family="" swiss="" ss:size="" 12="" ss:bold="" 1="" />"<br />
startExcelXML &= vbNewLine & "</style>"<br />
'Column header style<br />
startExcelXML &= vbNewLine & "<style ss:id="" colheader="">"<br />
startExcelXML &= vbNewLine & "<alignment ss:horizontal="" center="" ss:vertical="" bottom="" />"<br />
startExcelXML &= vbNewLine & "<font x:family="" swiss="" ss:color="" #ffffff="" ss:size="" 10="" ss:bold="" 1="" />"<br />
startExcelXML &= vbNewLine & "<interior ss:color="" #969696="" ss:pattern="" solid="" />"<br />
startExcelXML &= vbNewLine & "</style>"<br />
'Row style<br />
startExcelXML &= vbNewLine & " <style ss:id="" reg="">"<br />
startExcelXML &= vbNewLine & " <font x:family="" swiss="" ss:bold="" 0="" />"<br />
startExcelXML &= vbNewLine & " </style>"<br />
<br />
startExcelXML &= vbNewLine & "</styles>"<br />
'write the header to the file<br />
doc.WriteLine(startExcelXML)<br />
'write one sheet per table<br />
For Each tab As Data.DataTable In ds.Tables<br />
Dim sheetname As String = "<worksheet ss:name="<br mode=" hold=" /"> sheetname &= Microsoft.VisualBasic.Chr(34)<br />
sheetname &= tab.TableName.Trim<br />
sheetname &= Microsoft.VisualBasic.Chr(34)<br />
sheetname &= ">"<br />
With doc<br />
.WriteLine(sheetname)<br />
.WriteLine("<table>")<br />
.WriteLine("<row>")<br />
<br />
'write a title line<br />
If Title1 = "" And Title2 = "" Then<br />
Else<br />
.Write("<cell ss:styleid="" title=""><data ss:type="" string="">")<br />
.Write(Title1)<br />
.WriteLine("</data></cell>")<br />
.Write("<cell ss:styleid="" title=""><data ss:type="" string="">")<br />
.Write(Title2)<br />
.WriteLine("</data></cell>")<br />
.WriteLine("<row>")<br />
End If<br />
<br />
'write the column headers<br />
For Each col As DataColumn In tab.Columns<br />
.Write("<cell ss:styleid="" colheader=""><data ss:type="" string="">")<br />
.Write(col.ColumnName)<br />
.WriteLine("</data></cell>")<br />
Next<br />
.WriteLine("</row>")<br />
<br />
'write the table<br />
For Each row As DataRow In tab.Rows<br />
.WriteLine("<row>")<br />
For i As Integer = 0 To tab.Columns.Count - 1<br />
.Write("<cell ss:styleid="" reg=""><data ss:type="" string="">")<br />
.Write(row.Item(i))<br />
.WriteLine("</data></cell>")<br />
Next<br />
.WriteLine("</row>")<br />
Next<br />
.WriteLine("</row></table>")<br />
.WriteLine("</worksheet>")<br />
End With<br />
Next<br />
doc.WriteLine("</workbook>")<br />
doc.Close()<br />
Catch Ex As Exception<br />
doc.Close()<br />
EC(Ex)<br />
End Try<br />
End Sub</xml>
5. Good luck.
Johan
My advice is free, and you may get what you paid for.
|
|
|
|
|
i'm using microsoft visual studio. i want to update my table. how to update a field?
i have a table that have 2 field 1. run_number 2. date. each time i add my input it will update the running no and date.. so i can know that input is the 2nd or 3rd input that i've key-in on that day. and the current running number i will have it in my document number that is in this format <yyyymmdd><custid><run_no>. This doc number is for my reference. i hope i explain it well because my english is not that good. somebody pls help me..
|
|
|
|
|
A table in a database ? You need the unique Id and then just write SQL that will update the record in question, and call it using ADO.NET.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
thank you for ur reply.. i'm sorry if ask a very stupid question. actually i've just got promoted as a programmer. before this i'm just a computer operator (execute program from programmer in main frame). it has been 3 years after my studies in computer science and i've never got a job related to my study.. i'll try to do what u've suggested. thanks again for entertaining my question. 
|
|
|
|
|
No worries - you should find there's plenty of examples online. Ask again if you get stuck.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Hi all,
I would like to create a list of cities that are associated with the country flag that I would use in a combo box or list view. I have tried with the Imagelist but you cannot associate it with a word ?
Thanks, 
|
|
|
|
|
You'd use a dictionary to associate two groups of objects, probably storing the city name as the key and the index into your image list as the value.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Hi all,
I am a beginner in VB 2005  and I am looking for some help.
I am creating a application that will use a small database under the form of a txt file (called *.dat). Here is an example :
ServerName;IP Adress; port_number; etc....
1) How can I update automatically my listview or checkbox with this information as I do not want it to be hard coded?
2) Also I would like to store the parameters (or Options that the user as entered via the application) in an *.INI file and be able to read them at the launch of the application so that I can populate the options fields?
I know that it is an old way of proceeding but easier for me to maintain.
Thanks,
|
|
|
|
|
1 - you'd do better to store your data as XML, or, at least, create a struct that represents one data item, and write code to build a list of items from your data file. Then make that the data source of your control.
2 - Sounds good. Again, XML is probably easier to work with. Then all you need to do, is read the file and set your state from it on startup, and store your state to XML on close.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
I want to insert checkbox in a listbox for selecting purpose
Ahmed hassan
|
|
|
|
|
there is already a control CheckedListBox in VB 2005 & 2008 (not sure about earlier .net versions)
Eslam Afifi
|
|
|
|
|
Does anyone have experience calling a Cobol/DB2 stored procedure from .NET? It is not an easy thing to find on the web as I'm sure most companies have tried to get away from this. We still have them because they must serve as the link between our distributed apps and an IMS Database 
|
|
|
|
|
How do you write a stored proc in Cobol ? If you have a database, then it needs to support ODBC in order to create a link to ADO.NET. Otherwise, it's going to be tough going, I reckon.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
It's pretty stupid. Basically, we have DB2 installed underneath IMS. Programs have to be written in either COBOL or Java and called in order to get to the IMS data from a distributed app written in either .NET or Java. It's certainly not an ideal situation.
|
|
|
|
|
Hi All,
I have encrypted file,
Consider encrypted file is jpeg. Actually what iam doing is, loading the encrypted file on memory and decrypting on-the-fly then iam getting the file content as a byte array.
How to assign the byte array value to appripriate file type to open.
Thanks & Regards
Kumaran
|
|
|
|
|
... well just save the byte array in the right file ... (do open it you might want to use the System.Diagnostics.Process.Start - method)
|
|
|
|
|
hai,
i am using dotnet 2005.i doing my project in vb.net windows application.
Is it possible to generate serial number in datagridview.if possible means,i want to generate serial number in datagridview in windows application,not in web application.
|
|
|
|
|
The easiest way i to add it while u are are adding the rows...
When u add the rows /cells u can have a counter along (as in most of the cases it is done in a loop), u ca just add it to ur serial number col..
Hope this will do the trick for u
Good Luck
|
|
|
|
|
Hi
Please help me out in Progress Bar Control in VB6.0
Progress to show until my 6 functions is completed
Regards
Subash
|
|
|
|
|
Have you tried google ? Progress bars are kind of trivial, you just set the maximum value, and keep incrementing it until the tasks are done.
Also, why are you using VB6, which has been a dead language for some time now ? You plainly are very much a beginner, why start on a language that no-one uses ( and that sucks ) ? VB.NET express edition is free.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Dear All,
please help me how to
Convert Format24bppRgb to Format8bppIndexed in c#
I would really appreciate your help.
Abdul Rahman From Afghanistan.
|
|
|
|
|
Hello,
Look up the contents of the System.Windows.Media.Imaging namespace, namely PixelFormat and FormatConvertedBitmap. That should get you started. You'll need references to System.Windows.Media.Imaging and WindowsBase.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
