Fastert way to work with data from Excel

[Nikolay Petrov]

I have an application, which reads data from MS Excel and store it in text
file.
I use a two dimensional array in which I store the contents of Excell cell,
by using For loop.
After that to store the data from the array in String, so I can use it in
StreamWriter i do another For loop to add a vbCrLf at the end of each row.
I am new to programming, but I am quite sure that this is not the best way
to accomplish this. It works, but it is very slow when the Excel table
contains many data.
Reading from Excel is slow, but I guess I can't change this. Any ideas how
to speed up the other things. Looping arrays is not the fastest method i
guess.

TIA

 

[Cor Ligthert]

Nikolay,

You can read an Excel spreadsheet as dataset using OleDB.
There are a lot of samples. Here is one. I thought that I copied as well
somewhere (I thought MSDN) and added something in the connectionstring.

\\\
Dim ConnectionString As String
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\test1\myExcel.xls;" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim Conn As New System.Data.OleDb.OleDbConnection(ConnectionString)
Dim da As New System.Data.OleDb.OleDbDataAdapter _
("Select * from [Sheet1$]", Conn)
Dim ds As New DataSet
da.Fill(ds, "Sheet1")
///

I hope this helps?

Cor

 

[Nikolay Petrov]

Ok Cor
When I have the data from Excel in Dataset
what is the fastes way to write it to disk as a text file?
Every row in a dataset must me a row in the text file.

 

[Cor Ligthert]

Nikolay,

First of all, of course every dataset can be written to disk with
dataset.WriteXML(path)

That goes very fast

When you want it in another way it is just going throught the rows and
writing it using the streamwriter.

I hope this helps,

Cor