Excel到dataTable [英] Excel to dataTable

问题描述

我需要从excel中获取表格到数据表。我首先尝试使用LinqToExcel库，但是从excel表中获取大量数字作为指数数字。我在说大数字，如2352143523453452334544。只有当它们形成文本时，它才能正常工作。
之后，我已经尝试过：

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"];

这个在与null相同的条件下返回相同的值。
是否有一种简单的方式从Excel文件中获取数据？没有转换，没有什么。只要把它全部作为一个字符串，并把它放入一个数据表？

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 ?

推荐答案

这是我使用的，它为我工作：

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;
    }

但是您也可以尝试导出为CSV：
<一个href =http://www.codeproject.com/KB/linq/LINQtoCSV.aspx =nofollow> LinqToCSV

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

这篇关于Excel到dataTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！