Importing Excelsheet

Paul Witmond

Hello,
I'm trying to import an Excel sheet but it's not working
public void Budget()
{
string filename = @"C:Budget_2008.xls";
ImportZPfromExcel(filename, "Blad1");
}
private System.Data.DataTable GetDataFromXLS(string strFilePath, string sheetName)
{
try
{
string strConnectionString = string.Empty;
strConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
System.Data.OleDb.OleDbConnection cnCSV = new System.Data.OleDb.OleDbConnection(strConnectionString);
cnCSV.Open();
System.Data.OleDb.OleDbCommand cmdSelect = new System.Data.OleDb.OleDbCommand(@"SELECT * FROM [" + sheetName + "$]", cnCSV);
System.Data.OleDb.OleDbDataAdapter daCSV = new System.Data.OleDb.OleDbDataAdapter();
daCSV.SelectCommand = cmdSelect;
System.Data.DataTable dtCSV = new System.Data.DataTable();
daCSV.Fill(dtCSV);
cnCSV.Close();
daCSV = null;
return dtCSV;
}
catch (Exception ex)
{
return null;
}
finally
{
}
}
public void ImportZPfromExcel(string filename,string sheet)
{
System.Data.DataTable dtZPXls = GetDataFromXLS(filename, sheet);
System.Data.DataTable dtZP = new System.Data.DataTable();
dtZP.Columns.Add("Cardcode",typeof(string));
dtZP.Columns.Add("Cardname",typeof(string));
// Parse Excel Sheet
foreach (System.Data.DataRow row in dtZPXls.Rows)
{
System.Data.DataRow r=dtZP.NewRow();
r["Cardcode"]=row["Cardcode"].ToString();
r["Cardname"]=row["Cardname"].ToString();
dtZP.Rows.Add(r);
}
SwissAddonFramework.UI.Dialogs.MessageBox.Show("Fout :"+ err1,"OK");
}
When the script comes to the line r["Cardcode"]=row["Cardcode"].ToString(); it stops
What am I doing wrong ?
Regards,
Paul

Manuel Marhold

Hi Paul,

do you get an errormessage?

Is the CardCode a numeric-value (If excel has formatted it as text, this information is not saved in txt-files (csv, etc.)