Sign in to follow this  
Nza

VB.NET - Import data from an XLS

Recommended Posts

Hey everyone - I was wondering if anyone knew how to import data from an XLS file into a VB.NET project? I want to be able to load all data from certain columns (to collect first and last names to add to a Listbox), and then I want to load all data from a certain row (to populate textboxes depending on who the user chose in the Listbox). Thanks for any help. I've tried google but can't find anything relevant. Also, while i'm here, I also want to write data to the XLS, so any changes made to the textbox values is written directly to the XLS.

Share this post


Link to post
Share on other sites
XLS is a secured format although it will be available somewhere probably. Perhaps you can find it here. A few years ago I searched and found little. There is a library out there somwhere which imports XLS files for you but for some reason it didn't work out for me (could be that it is an old version of XLS).

An alternative is to work with CSV files -- comma separated files. These store the cell values per line, separating each column by a comma. Such files can be opened and saved in MS Excel.

Greetz,

Illco

Share this post


Link to post
Share on other sites
You can open Excel as a automation server and programmatically access the Excel DOM. If you have Excel installed on your computer that you have VB .NET installed on, then the relevant reference should already be available. First you need to create an instance of the Excel.Application, then use the Application.Workbooks member which you can then use to open an XLS file. From there use the Worksheets member to get at the actual worksheet you're interested in and then play with the Cells member to read and write data.

Alternately, I believe that you can read and write to an XLS file via ODBC if you have the Excel ODBC driver installed.

Share this post


Link to post
Share on other sites
Hrm, can't seem to find much info on importing/exporting CSV in VB.NET code either :(

Exporting should just be as simple as writing a file with each textbox seperated by a comma.

Importing would be tricker I imagine - i'd have to tell VB to load every value before each comma into a multi dimensional array, since there are 2 dimensions to the CSV file.

Right? Not that I know how to do both of these exactly though.

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
Just Convert this to VB .Net
-----------------------------

public static DataSet ImportFromCSV(string strFilePath)
{
try
{
DataSet dsValues = null;
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\;Extended Properties=Text;";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();

string sqlSelect = @"SELECT * FROM " + strFilePath;

OleDbCommand objCmdSelect = new OleDbCommand(sqlSelect, objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;

dsValues = new DataSet();
objAdapter1.Fill(dsValues, "Test");
//DataGrid1.DataSource = objDataset1.Tables(0).DefaultView
objConn.Close();
return dsValues;
}
catch(Exception Ex)
{
throw Ex;
}
}

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this