.Net中通過CSV文件實現DataTable與Excel文件相互轉化

在一個項目中，需要從Excel文件導入數據然後再datagridview上顯示，同時也需要右鍵datagridview時可以將數據另存為excel文件，於是寫了這兩個工具方法。本文提供了兩個方法用於Excel和DataTable之間相互轉化。

1， 從Excel文件、CSV文件導入到DataTable：

public static DataTable csvToDataTable(string file)  
 {  
     string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;'"; // Excel file  
 if(file.EndsWith(".csv"))  
         strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;'"; // csv file:HDR=Yes-- first line is header  
     OleDbConnection oleConn = new OleDbConnection(strConn);  
     oleConn.Open();  
     DataTable sheets = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
     if (sheets == null || sheets.Rows.Count < 1)  
     {  
         return null;  
     }  
     String fileName = sheets.Rows[0]["TABLE_NAME"].ToString(); // sheets.Rows[0] -- first sheet of excel  
 if(file.EndsWith(".csv"))  
  fileName = file.Substring(file.LastIndexOf("/"));  
     string olestr = "select * from [" + fileName + "]";  
     if (file.EndsWith(".csv"))  
         olestr = "select * from [" + fileName + "]";  
     OleDbCommand oleComm = new OleDbCommand(olestr, oleConn);  
     oleComm.Connection = oleConn;  
     OleDbDataAdapter oleDa = new OleDbDataAdapter();  
     oleDa.SelectCommand = oleComm;  
     DataSet ds = new DataSet();  
     oleDa.Fill(ds);  
     oleConn.Close();  
     return ds.Tables[0];  
 }

2，DataTable到出到CSV文件：

public static void dataTableToCsv(DataTable table, string file)  
        {  
            string title = "";  
     
            FileStream fs = new FileStream(file, FileMode.Create);  
            StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);  
     
            for (int i=0; i<table.Columns.Count; i++)  
            {  
                title += table.Columns[i].ColumnName + ",";  
            }  
            title = title.Substring(0, title.Length - 1) + "\n";  
            sw.Write(title);  
     
            foreach (DataRow row in table.Rows)  
            {  
                string line = "";  
                for (int i = 0; i < table.Columns.Count; i++)  
                {  
                    line += row[i].ToString() + ",";  
                }  
                line = line.Substring(0, line.Length - 1) + "\n";  
     
                sw.Write(line);  
            }  
     
            sw.Close();  
            fs.Close();  
        }  
    }