CODEWITHSUNDEEP
excel
maephisto
maephisto

Reputation: 333

Excel to dataTable

I need to fetch a sheet from excel to a datatable. I first tried with LinqToExcel library, but this fetched the large numbers from the excel sheet as exponential numbers. I'm talking about big numbers like "2352143523453452334544". Only if they are formated as text it would work ok. After that i've tried this :

OleDbConnection con = null;
System.Data.DataTable dt = null;
System.Data.DataTable dataTable1 = new System.Data.DataTable();
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + UploadFileName + ";Extended Properties=Excel 8.0;";
string sql_xls;
con = new OleDbConnection(conStr);
con.Open();

//OracleDataAdapter oda = new OracleDataAdapter();
//OracleCommand cmd = new OracleCommand("select * from [Sheet1$]", con);

dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string[] excelSheetNames = new string[dt.Rows.Count];
int i = 0;
foreach (System.Data.DataRow row in dt.Rows)
{
    excelSheetNames[i] = row["TABLE_NAME"].ToString(); i++;

}

sql_xls = "SELECT * FROM [" + excelSheetNames[0] + "]";
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql_xls, conStr);
System.Data.DataSet myDataSet = new System.Data.DataSet();
dataAdapter.Fill(myDataSet, "ExcelInfo");
dataTable1 = myDataSet.Tables["ExcelInfo"];

This one returned the same values in the same conditions as null. Isn't there a simple way to fetch data from a excel file as it is? No conversions, no nothing. Just take it all as a string, and put it into a datatable ?

Upvotes: 0

Views: 3332

Answers (1)

Willem
Willem

Reputation: 9476

This is what i used and it worked for me:

private DataTable LoadXLS(string strFile, String sheetName)
    {
        DataTable dtXLS = new DataTable(sheetName);

        try
        {
            string strConnectionString = "";

            if(strFile.Trim().EndsWith(".xlsx"))
            {
                strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", strFile);
            }
            else if(strFile.Trim().EndsWith(".xls")) 
            {
                strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);
            }

            OleDbConnection SQLConn = new OleDbConnection(strConnectionString);
            SQLConn.Open();

            OleDbDataAdapter SQLAdapter = new OleDbDataAdapter();
            string sql = "SELECT * FROM [" + sheetName + "$]";

            OleDbCommand selectCMD = new OleDbCommand(sql, SQLConn);
            SQLAdapter.SelectCommand = selectCMD;

            SQLAdapter.Fill(dtXLS);
            SQLConn.Close();
        }
        catch (Exception)
        {
            throw;
        }

        return dtXLS;
    }

But you can try to export to CSV as well: LinqToCSV

Upvotes: 1

Related Questions