 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / General 2 / May 2004Loading an excel file into a flexgrid is too slow
because of the enormous increase to my installation package that using ADO entails I am thinking of using an excel spreasheet for data retrieval instead of access 2002. I have tried the code below which works ok except it is incredibly slow. Is there a faster way of dong it ? I just want to load a flexgrid. Dim x As Integer Dim xlsApp As excel.Application Dim book As excel.Workbook Dim sheet As excel.Worksheet Set xlsApp = New excel.Application xlsApp.Visible = False Set book = xlsApp.Workbooks.Open("C:\Temp\misc.xls") Set sheet = book.Worksheets(1) 'Sheet 1 'Loop through cells to get data and modify data if you want to For x = 1 To 600 MSHFlexGrid1.TextMatrix(x, 1) = sheet.Cells(x, 1) MSHFlexGrid1.TextMatrix(x, 2) = sheet.Cells(x, 2) MSHFlexGrid1.TextMatrix(x, 3) = sheet.Cells(x, 3) MSHFlexGrid1.TextMatrix(x, 4) = sheet.Cells(x, 4) Next x book.Close False 'Or True if you want to save changes xlsApp.Quit Thanks - Del > because of the enormous increase to my installation package that using > ADO entails I am thinking of using an excel spreasheet for data [quoted text clipped - 27 lines] > book.Close False 'Or True if you want to save changes > xlsApp.Quit How much data are you talking about in a worst-case scenario? I made an xml-file with the following structure and named it "data.xml", containing 600 <row> nodes... <?xml version="1.0" encoding="iso-8859-1"?> <rows> <row> <col1>Row1Col1</col1> <col2>Row1Col2</col2> <col3>Row1Col3</col3> <col4>Row1Col4</col4> </row> ... <row> <col1>Row600Col1</col1> <col2>Row600Col2</col2> <col3>Row600Col3</col3> <col4>Row600Col4</col4> </row> </rows> The file became 80 Kb. Then I put a Flexgrid named "flxGrid", a Label named "Label1", and a button named "cmdLoad" on a form. I also added a reference to MsXML 3.0 in references. Then I put the following code in cmdLoad's Click event: Private Sub cmdLoad_Click() Dim oDOM As MSXML2.DOMDocument Dim oRoot As MSXML2.IXMLDOMNode Dim oRows As MSXML2.IXMLDOMNodeList Dim oRow As MSXML2.IXMLDOMNode Set oDOM = New MSXML2.DOMDocument If Not oDOM.Load(App.Path & "\data.xml") Then MsgBox oDOM.parseError.reason, vbCritical + vbOKOnly, "Parse Error" Else Set oRoot = oDOM.documentElement Set oRows = oRoot.selectNodes("/rows/row") Dim r As Long Dim start As Single start = Timer r = 0 For Each oRow In oRows flxGrid.TextMatrix(r, 0) = oRow.selectSingleNode("col1").Text flxGrid.TextMatrix(r, 1) = oRow.selectSingleNode("col2").Text flxGrid.TextMatrix(r, 2) = oRow.selectSingleNode("col3").Text flxGrid.TextMatrix(r, 3) = oRow.selectSingleNode("col4").Text r = r + 1 Next oRow End If Label1.Caption = "Time elapsed: " & Format$(Timer - start, "#.000") & " seconds" Set oRow = Nothing Set oRows = Nothing Set oRoot = Nothing Set oDOM = Nothing End Sub Running this code on my PC takes 0.1 seconds on average... -- Dag 58°26'15.9" N 008°46'45.5" E This looks interesting but there seems to be something wrong as nothing happens. I saved an excel as an xml file which I assume is ok. This is all new to me but I assumed you left out the line that loads the file . I tried adding oDOM.Load(App.Path & "\data.xml") but it made no difference. I have a maximium of 600 rows and a constant 13 columns. >> because of the enormous increase to my installation package that using >> ADO entails I am thinking of using an excel spreasheet for data [quoted text clipped - 101 lines] >Dag >58°26'15.9" N 008°46'45.5" E Thanks - Del > This looks interesting but there seems to be something wrong as > nothing happens. I saved an excel as an xml file which I assume is ok. The xml-format you get when you save an excel-file as xml is quite different from the xml-format I gave you! (How did you get Excel to save as xml by the way)? > This is all new to me but I assumed you left out the line that loads > the file . I tried adding oDOM.Load(App.Path & "\data.xml") > but it made no difference. No, i didn't leave it out... ... Set oDOM = New MSXML2.DOMDocument --> If Not oDOM.Load(App.Path & "\data.xml") Then MsgBox oDOM.parseError.reason, vbCritical + vbOKOnly, "Parse Error" Else > I have a maximium of 600 rows and a constant 13 columns. I extended my test file to contain 600 rows, each with 13 cols, and the time it took increased to 0.4 seconds... The code I use in my sample for extracting data in my sample is hand-crafted to the format i showed in my sample: <?xml version="1.0" encoding="iso-8859-1"?> <rows> <row> <col1>Row1Col1</col1> <col2>Row1Col2</col2> <col3>Row1Col3</col3> <col4>Row1Col4</col4> ... <colN>Row1Col4</colN> </row> ... If you want to use another xml-format, you must change the code accordingly: > > Set oRows = oRoot.selectNodes("/rows/row") ... > > flxGrid.TextMatrix(r, 0) = oRow.selectSingleNode("col1").Text Note the "/rows/row" and the "col1"... Those are known as XPath strings, and can be compared to SQL query statements against an xml-file. -- Dag 58°26'15.9" N 008°46'45.5" E Here's a commented version of the code... I sendt you the VB project & data file in mail, so you can experiment with that... Private Sub cmdLoad_Click() Dim oDOM As MSXML2.DOMDocument Dim oRoot As MSXML2.IXMLDOMNode Dim oRows As MSXML2.IXMLDOMNodeList Dim oRow As MSXML2.IXMLDOMNode ' Create a Document Object Model instance Set oDOM = New MSXML2.DOMDocument ' Try to parse the xml-file If Not oDOM.Load(App.Path & "\data.xml") Then ' Show the reason we failed, if it didn't work MsgBox oDOM.parseError.reason, vbCritical + vbOKOnly, "Parse Error" Else ' We have loaded/parsed the data successfully, so now we can query ' the data we need ' Get the root node of the DOM three Set oRoot = oDOM.documentElement ' Query for a collection (nodeList) of all available <row> tags that ' is a child of a <rows> tag: Set oRows = oRoot.selectNodes("/rows/row") Dim r As Long Dim c As Long Dim start As Single start = Timer r = 0 ' For each <row> tag we found... For Each oRow In oRows For c = 1 To MAX_COLS ' select the tag <colC>, and assign its .Text attribute to ' the corresponding cell in the grid flxGrid.TextMatrix(r, c - 1) = oRow.selectSingleNode("col" & c).Text Next c r = r + 1 Next oRow End If Label1.Caption = "Time elapsed: " & Format$(Timer - start, "#.000") & " seconds" Set oRow = Nothing Set oRows = Nothing Set oRoot = Nothing Set oDOM = Nothing End Sub -- Dag 58°26'15.9" N 008°46'45.5" E thanks. Very kind of you. I should have mentioned though that I need to be able to write back to this XML file though I can just dump everything from the flexgrid back into it. As far a saving the excel 2002 file I literally just went File>save as > and then chose save as xml file. I will give that a try in a minute when I have given my brain a rest. >Here's a commented version of the code... > [quoted text clipped - 60 lines] >Dag >58°26'15.9" N 008°46'45.5" E Thanks - Del From the original post: > I have tried the code below which works ok except it is incredibly > slow. Is there a faster way of dong it ? I just want to load a > flexgrid. .... > For x = 1 To 600 > MSHFlexGrid1.TextMatrix(x, 1) = sheet.Cells(x, 1) > MSHFlexGrid1.TextMatrix(x, 2) = sheet.Cells(x, 2) .... Working through an excel spreadsheet cell by cell is slow, because each cell reference is a separate call across processes, with tons of overhead. Using the clipboard can get you a much faster transfer of data in both directions. First you copy all of the data from the excel sheet to the clipboard, and retrieve it as a string: Private mData As String Private Sub Command1_Click() Dim oXL As Object Set oXL = CreateObject("Excel.Application") Call oXL.Workbooks.Open("C:\Test.xls") Call oXL.ActiveSheet.UsedRange.Copy mData = Clipboard.GetText Call oXL.Quit Set oXL = Nothing End Sub Then you go through some steps to split it up by line, then split each row into cells. The text is in standard Tab delimited format with line breaks for each row: Private mGrid() As String Private Sub Command2_Click() Const ColCnt = 13 Dim strRows() As String Dim strCells() As String Dim n As Long Dim j As Long strRows = Split(mData, vbCrLf) ReDim mGrid(0 To UBound(strRows), 0 To ColCnt - 1) As String For n = 0 To UBound(strRows) strCells = Split(strRows(n), vbTab, ColCnt) For j = 0 To UBound(strCells) mGrid(n, j) = strCells(j) Next j Next n End Sub To update the excel file, you can use the reverse process to construct a single large string, copy it to the clipboard, and paste it back into Excel in one operation. No sign of the email. You did remove the XXX ? THe other thing I thought of is can you password protect an xml file like you can an excel file ? >thanks. Very kind of you. > [quoted text clipped - 73 lines] > >Thanks - Del Thanks - Del > No sign of the email. You did remove the XXX ? > > THe other thing I thought of is can you password protect an xml file > like you can an excel file ? Yes, I did remove the X'es... I'll resend it. No, an XML-file is a plain text file editable with NotePad, etc... If you need that, take a look at Steve Gerrard's post... -- Dag 58°26'15.9" N 008°46'45.5" E There is always something isn't there. My biggest problem with all this project is that I need to retrieve strings in Russian and chinese etc. The clipboard thing was a good idea but surprisingly I ended up with the familiar ????????? you get for non unicode strings. TO be honest it wasn't that much faster and it seemed a little unstable as a couple of times I had clipboard error messages. Thanks for trying . I think I will have to put up with it and grin and bear the extra 10 MB download size. >> No sign of the email. You did remove the XXX ? >> [quoted text clipped - 10 lines] >Dag >58°26'15.9" N 008°46'45.5" E Thanks - Del > There is always something isn't there. My biggest problem with all > this project is that I need to retrieve strings in Russian and chinese [quoted text clipped - 5 lines] > Thanks for trying . I think I will have to put up with it and grin and > bear the extra 10 MB download size. Xml files (and notepad too for that sake) can percefly well handle unicode characters. See the encoding='iso-8859-1' in the <?xml... tag. Can't remember what the iso code for the cyrillic character set is, but do a search. The only problem I see, is your need to password protect the file. But as a (Unicode) textfile, its easy to scramble or encrypt it. -- Dag 58°26'15.9" N 008°46'45.5" E > There is always something isn't there. My biggest problem with all > this project is that I need to retrieve strings in Russian and chinese [quoted text clipped - 5 lines] > Thanks for trying . I think I will have to put up with it and grin and > bear the extra 10 MB download size. I hadn't tested unicode characters, and sure enough, you get ? marks. Too bad. The odd thing is if you copy cyrillic from Excel and paste in Notepad, it comes across fine. I guess VB's Clipboard.GetText is filtering out the unicode. I suspect that the errors you mentioned are related to the unicode text, because I have used this technique on grids of ordinary text and numbers (500 x 20) with solid results. It would be interesting to know if you could do this with the XML approach Dag is describing. As for the setup, you can always make two setups: one for all the system components, including MDAC, Jet, etc. for those users who need it, and one for just your program, for those users who already have all the system stuff. This is especially useful if you do more than one program. <snipped> > I suspect that the errors you mentioned are related to the unicode text, > because I have used this technique on grids of ordinary text and numbers > (500 x 20) with solid results. It would be interesting to know if you > could do this with the XML approach Dag is describing. Just as a test... <?xml version="1.0" encoding="ISO-8859-5"?> <!-- edited with XML Spy v2.0 NT - http://www.xmlspy.com--> <RUSSIAN> <TITLE>????????? ???????? ? ????? ???????!</TITLE> <P>? ?????? ??? ? ?????? ??????? ?????????? ?????? ???????? ?????? ?????. ????? ??????????? ?????? ???????????? ????????: ?????????? ????????????? ????? ???????, ?????????????????, ?????????????? ?????????????? ????????? ????????, ???????????, ??????? ??????? ????? ???????, ?????? ???????????, ???????? ? ?? ?????? ???? ??????????? ???????? ????? ????. </P> <TITLE>????? 3 ????? ??????????? ???????? "?? ????? ????? ????????".</TITLE> <P>???????????????? ???????, ? ??????? ??????? ??????? ????? 13 ????? ???????, ????????? ?? ???????? ???????. ? ??????? ? ?????????????? ????? ???? ???????? ??????? ??????. ????? ????- ??? ??????? ??????? ?????? ? ????? ????????????? ?????? ?? ??????, ????????????? ?? ??????????? "???", ?????? ???????? ?? ??????????? ? ????? ????? ?????. ? ?????? ???????? ??????? ? ??????????? ???????? ????????????? ??? ?????? ???? ??????, ????????????? ? ?????? ?? ????????? ??????????? ???? ?????????? ?????????? ????????. </P> </RUSSIAN> -- Dag. > <snipped> > [quoted text clipped - 9 lines] > <RUSSIAN> > <TITLE>????????? ???????? ? ????? ???????!</TITLE> Of course tat didn't work... My Newsreader is set to send all as plain ASCII... :-\ But I'll put a iso-8859-5 encoded xml-file on my web-server later, so somebody can try to open it in IE... http://www.dagsunde.com/russian.xml -- Dag 58°26'15.9" N 008°46'45.5" E > Of course tat didn't work... > [quoted text clipped - 10 lines] > Dag > 58°26'15.9" N 008°46'45.5" E That opened in IE for me with the Russian characters displayed. I also copied the file for further tinkering... <snipped /> > That opened in IE for me with the Russian characters displayed. I also > copied the file for further tinkering... Xml *is* fun! :-) Want some more? http://www.dagsunde.com/Japanese_UTF-16.xml http://www.dagsunde.com/Big5.xml (Just to prove that unicode is absolutely *no* prblem for xml). -- Dag 58°26'15.9" N 008°46'45.5" E It's ironical but my program is very simple but the whole point of it is it is accessible in any language so I have had to get involved in unicode and what a peformance that has been. I had to pay a lot of money for Unitoolbox controls and although they work they have slowed my program dramatically. Then someone suggested I used DAO 3.51 instead of ADO and I was overjoyed to see it shave 10 MB off my installation package. Then guess what .. I found out that only 3.6 supports Unicode which meant I had so use Jet4 which is twice as big as using ADO. Then I was prepared to pay even more money for Codebase database. I finally got that working and guess what.. although they claim that Codebase supports unicode the databases they work with don't. I have tried everything. Yes I would be quite happy to use 2 setups, one for European and one for others but I would have thought that would make life very difficult ? I tried it for a while but found that every time I had to make a change, doing it to both setups was very hard to control. At the moment I use the same program for every different version. Is it wise to have more than one setup or is there a trick to it ? >> There is always something isn't there. My biggest problem with all >> this project is that I need to retrieve strings in Russian and chinese [quoted text clipped - 20 lines] >one for just your program, for those users who already have all the >system stuff. This is especially useful if you do more than one program. Thanks - Del > Yes I would be quite happy to use 2 setups, one for European and one > for others but I would have thought that would make life very > difficult ? I tried it for a while but found that every time I had to > make a change, doing it to both setups was very hard to control. At > the moment I use the same program for every different version. Is it > wise to have more than one setup or is there a trick to it ? What I meant was: Setup 1: The VB runtime Your program.exe Any dll or ocx that is particular to just your program Icons, etc. Setup 2: All the database stuff: MDAC, Jet, whatever Standard DLLs and OCXs: common controls, etc Some users would need to run both setups the first time. Others who already had all the standard stuff would only need the first one. Then if you do a second program, or an updated first program, they only need to run the first setup again. If you call the second setup something like "System Update", you can explain what it is for and when to use it. Sorry. What I meant was if I have 2 different exe's then every time I made a change in one of them I would have to make it in the other. I suppose if it is just the odd line of code here and there it is not too bad but any major changes I would ahve thought make it difficult to keep both in sync. >> Yes I would be quite happy to use 2 setups, one for European and one >> for others but I would have thought that would make life very [quoted text clipped - 23 lines] >If you call the second setup something like "System Update", you can >explain what it is for and when to use it. Thanks - Del > Sorry. What I meant was if I have 2 different exe's then every time I > made a change in one of them I would have to make it in the other. I > suppose if it is just the odd line of code here and there it is not > too bad but any major changes I would ahve thought make it difficult > to keep both in sync. Right. I was not suggesting that you have two different executables, only that you split up the required setup files into two groups - the program specific ones, and the common used general purpose ones. Sorry again. I didn't read the first post properly. Yes that is what I would like to do except download sites only seem to allow one download link which seems a bit unreasonable when like download.com you have to pay for them. My experience with them is they are only interested in taking money from me without even the slightest consideration of acoomdating me. >> Sorry. What I meant was if I have 2 different exe's then every time I >> made a change in one of them I would have to make it in the other. I [quoted text clipped - 5 lines] >only that you split up the required setup files into two groups - the >program specific ones, and the common used general purpose ones. Thanks - Del |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.