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 < )

            {

                return null;

            }

            String fileName = sheets.Rows[]["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[];

        }

        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 = ; i < table.Columns.Count; i++)

            {

                title += table.Columns[i].ColumnName + ",";

            }

            title = title.Substring(, title.Length - ) + "\n";

            sw.Write(title);

            foreach (DataRow row in table.Rows)

            {

                if (row.RowState == DataRowState.Deleted) continue;

                string line = "";

                for (int i = ; i < table.Columns.Count; i++)

                {

                    line += row[i].ToString().Replace(",", "") + ",";

                }

                line = line.Substring(, line.Length - ) + "\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 = ; i < table.Columns.Count; i++)

            {

                title += ",";

            }

            title = title.Substring(, title.Length - ) + "\n";

            sw.Write(title);

            title = "";

            for (int i = ; i < table.Columns.Count; i++)

            {

                title += table.Columns[i].ColumnName + ",";

            }

            title = title.Substring(, title.Length - ) + "\n";

            sw.Write(title);

            foreach (DataRow row in table.Rows)

            {

                if (row.RowState == DataRowState.Deleted) continue;

                string line = "";

                for (int i = ; i < table.Columns.Count; i++)

                {

                    line += row[i].ToString().Replace(",", "") + ",";

                }

                line = line.Substring(, line.Length - ) + "\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);

        //}

    }