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