c# 操作excel文件（csv、数据库）

1、把csv文件中的数据读到datatable中       

       public static DataTable ImportCSV(string filePath)
        {
              DataTable ds = new DataTable();
                using (StreamReader sw = new StreamReader(filePath,Encoding.Default))
                {

                    string str = sw.ReadLine();
                    string[] columns = str.Split(',');
                    foreach (string name in columns)
                    {
                        ds.Columns.Add(name);
                    }

                    string line = sw.ReadLine();
                    while (line != null)
                    {
                        string[] data = line.Split(',');
                        ds.Rows.Add(data);
                        line = sw.ReadLine();
                    }

                    sw.Close();
                }

                return ds;
        }

 2、把csv的数据导入到数据库中

        public static void ImportCSVToDB(string filePath, string type, string dbTable)
        {

             string sqlString = "BULT insert " + dbTable + " from '" + filePath + "' WITH(FILEDTERMINATOR=',',ROWTERMINATOR='\n')";           

              using (SqlConnection conn = GetConn(conStr))
                {
                    conn.Open();
                    SqlCommand command = new SqlCommand(sqlString, conn);
                    return command.ExecuteNonQuery();
                }
        }

 

       3、excel的数据读入datatable中

        public static DataTable ImportXLS(string filePath)
        {
             string fileName = System.IO.Path.GetFileName(filePath);
            string path = System.IO.Path.GetDirectoryName(filePath);
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties = 'Excel 8.0;HDR=YES;IMEX=1;'";        
            DataTable ds = new DataTable();

            using (OleDbConnection conn = new OleDbConnection(strCon))
            {
                    conn.Open();
                    OleDbDataAdapter adpt = new OleDbDataAdapter("select * from [Sheet1$]", conn);
                    adpt.Fill(ds);
             }

             return ds;
        }

4、导出数据到csv文件中。（这种方式速度快）   

        public static void ExportToCSV(DataTable ds, string filePath)
        {
             using (StreamWriter sw = new StreamWriter(filePath, false, Encoding.Unicode))
            {
                for (int i = 0; i < ds.Columns.Count; i++)
                {
                    sw.Write(ds.Columns[i].ColumnName + "\t");
                }

                sw.Write("\n");
                for (int i = 0; i < ds.Rows.Count; i++)
                {
                    for (int j = 0; j < ds.Columns.Count; j++)
                    {
                        sw.Write(ds.Rows[i][j].ToString() + "\t");
                    }

                    sw.Write("\n");
                }

                sw.Close();
            }

                     return ;
        }