c# 将csv文件转换datatable的三种方式。

第一种：

string path ="test1.xlsx";
using (OleDbConnection conn = new OleDbConnection())
{
DataTable dt = new DataTable();
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path+ ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
using (OleDbCommand comm = new OleDbCommand())
{
comm.CommandText = "Select * from [" + "Sheet1" + "$]";
comm.Connection = conn;
using (OleDbDataAdapter da = new OleDbDataAdapter())
{
da.SelectCommand = comm;
da.Fill(dt);
dataGridView1.DataSource = dt;

}
}
}

 

第二种:

需要引用    

Microsoft.VisualBasic.dll

private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
DataTable dt = new DataTable();

string contents = File.ReadAllText(mFilepath, System.Text.Encoding.GetEncoding(1252));

TextFieldParser parser = new TextFieldParser(new StringReader(contents));

parser.HasFieldsEnclosedInQuotes = true;
parser.SetDelimiters(",");

string[] fields;

while (!parser.EndOfData)
{
fields = parser.ReadFields();

if (dt.Columns.Count == 0)
{
foreach (string field in fields)
{
dt.Columns.Add(new DataColumn(string.IsNullOrWhiteSpace(field.Trim('\"')) ? null : field.Trim('\"'), typeof(string)));
}
}
else
{
dt.Rows.Add(fields.Select(item => string.IsNullOrWhiteSpace(item.Trim('\"')) ? null : item.Trim('\"')).ToArray());
}

}

parser.Close();

return dt;

}

 第三种 需要引用

Microsoft.Office.Interop.Excel

      public DataTable READExcel(string path)
        {
            Microsoft.Office.Interop.Excel.Application objXL = null;
            Microsoft.Office.Interop.Excel.Workbook objWB = null;
            objXL = new Microsoft.Office.Interop.Excel.Application();
            objWB = objXL.Workbooks.Open(path);
            Microsoft.Office.Interop.Excel.Worksheet objSHT = objWB.Worksheets[1];

            int rows = objSHT.UsedRange.Rows.Count;
            int cols = objSHT.UsedRange.Columns.Count;
            DataTable dt = new DataTable();
            int noofrow = 1;

            for (int c = 1; c <= cols; c++)
            {
                string colname = objSHT.Cells[1, c].Text;
                dt.Columns.Add(colname);
                noofrow = 2;
            }

            for (int r = noofrow; r <= rows; r++)
            {
                DataRow dr = dt.NewRow();
                for (int c = 1; c <= cols; c++)
                {
                    dr[c - 1] = objSHT.Cells[r, c].Text;
                }

                dt.Rows.Add(dr);
            }

            objWB.Close();
            objXL.Quit();
            return dt;
        }