class DataTableAndCSV
    {
        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=" + file + ";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;'"; // csv file:HDR=Yes-- first line is header  
            //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];
        }

        public static void dataTableToCsv(DataTable table, string file)
        {
            FileInfo fi = new FileInfo(file);
            string path = fi.DirectoryName;
            string name = fi.Name;
            //\/:*?"<>|
            //把文件名和路径分别取出来处理
            name = name.Replace(@"\", "＼");
            name = name.Replace(@"/", "／");
            name = name.Replace(@":", "：");
            name = name.Replace(@"*", "＊");
            name = name.Replace(@"?", "？");
            name = name.Replace(@"<", "＜");
            name = name.Replace(@">", "＞");
            name = name.Replace(@"|", "｜");
            string title = "";

            FileStream fs = new FileStream(path + "\\" + name, 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)
            {
                if (row.RowState == DataRowState.Deleted) continue;
                string line = "";
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    line += row[i].ToString().Replace(",", "") + ",";
                }
                line = line.Substring(0, line.Length - 1) + "\n";

                sw.Write(line);
            }

            sw.Close();
            fs.Close();
        }

        public static void dataTableToCsv(DataTable table, string file, string Title)
        {
            FileInfo fi = new FileInfo(file);
            string path = fi.DirectoryName;
            string name = fi.Name;
            //\/:*?"<>|
            //把文件名和路径分别取出来处理
            name = name.Replace(@"\", "＼");
            name = name.Replace(@"/", "／");
            name = name.Replace(@":", "：");
            name = name.Replace(@"*", "＊");
            name = name.Replace(@"?", "？");
            name = name.Replace(@"<", "＜");
            name = name.Replace(@">", "＞");
            name = name.Replace(@"|", "｜");
            string title = "";

            FileStream fs = new FileStream(path + "\\" + name, FileMode.Create);
            StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);

            title += Title + ",";
            for (int i = 1; i < table.Columns.Count; i++)
            {
                title += ",";
            }
            title = title.Substring(0, title.Length - 1) + "\n";
            sw.Write(title);
            title = "";

            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)
            {
                if (row.RowState == DataRowState.Deleted) continue;
                string line = "";
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    line += row[i].ToString().Replace(",", "") + ",";
                }
                line = line.Substring(0, line.Length - 1) + "\n";

                sw.Write(line);
            }

            sw.Close();
            fs.Close();
        }

        //public static void ExportToSvc(DataTable dt, string strFileName)
        //{
        //    string strPath = strFileName;
        //    if (File.Exists(strPath))
        //    {
        //        File.Delete(strPath);
        //    }
        //    //先打印标头
        //    StringBuilder strColu = new StringBuilder();
        //    StringBuilder strValue = new StringBuilder();
        //    int i = 0;
        //    try
        //    {
        //        StreamWriter sw = new StreamWriter(new FileStream(strPath, FileMode.CreateNew), Encoding.GetEncoding("GB2312"));
        //        for (i = 0; i <= dt.Columns.Count - 1; i++)
        //        {
        //            strColu.Append(dt.Columns[i].ColumnName);
        //            strColu.Append(",");
        //        }
        //        strColu.Remove(strColu.Length - 1, 1);//移出掉最后一个,字符
        //        sw.WriteLine(strColu);
        //        foreach (DataRow dr in dt.Rows)
        //        {
        //            strValue.Remove(0, strValue.Length);//移出
        //            for (i = 0; i <= dt.Columns.Count - 1; i++)
        //            {
        //                strValue.Append(dr[i].ToString());
        //                strValue.Append(",");
        //            }
        //            strValue.Remove(strValue.Length - 1, 1);//移出掉最后一个,字符
        //            sw.WriteLine(strValue);
        //        }
        //        sw.Close();
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    ////System.Diagnostics.Process.Start(strPath);
        //}
    }