常用类-CSV---OLEDB

阅读量：5046 次

发布时间：2019-06-12

本文共 9245 字，大约阅读时间需要 30 分钟。

1 using System;  2 using System.Collections.Generic;  3 using System.Linq;  4 using System.Text;  5   6 using System.Data;  7 using System.IO;  8 using System.Data.OleDb;  9 using Aspose.Cells; 10  11 namespace Common 12 { 13     public class CSV 14     { 15         ///  16         /// "HDR=Yes;"声名第一行的数据为域名，并非数据。  17         /// 这种方式读取csv文件前8条为int类型后面为string类型 则后面数据读取不了 18         /// 还存在乱码问题 19         /// 
 20         ///  21         /// 
      22         public static DataTable Read(string fullPath) 23         { 24             FileInfo fileInfo = new FileInfo(fullPath); 25             DataTable table = new DataTable(); 26             string connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileInfo.DirectoryName + ";Extended Properties='Text;HDR=YES;FMT=Delimited;IMEX=1;'"; 27             string cmdstring = String.Format("select * from [{0}]", fileInfo.Name); 28  29             using (OleDbConnection conn = new OleDbConnection(connstring)) 30             { 31                 conn.Open(); 32  33                 OleDbDataAdapter adapter = new OleDbDataAdapter(cmdstring, conn); 34                 adapter.Fill(table); 35  36                 conn.Close(); 37             } 38  39             return table; 40         } 41  42         public static List
     
       Read2(string fullpath) 43         { 44             DataTable table = CSV.Read(fullpath); 45             List
      
        records = new List
       
        (); 46  47             foreach (DataRow row in table.Rows) 48             { 49                 records.Add(row[0].ToString()); 50             } 51  52             return records; 53         } 54  55         public static string ExportToCSV(DataTable table) 56         { 57             StringBuilder sb = new StringBuilder(); 58  59             for (int i = 0; i < table.Columns.Count; i++) 60             { 61                 if (i == table.Columns.Count - 1) 62                 { 63                     sb.Append(table.Columns[i].Caption); 64                 } 65                 else 66                 { 67                     sb.AppendFormat("{0},", table.Columns[i].Caption); 68                 } 69             } 70             sb.Append(Environment.NewLine); 71  72             for (int index = 0; index < table.Rows.Count; index++) 73             { 74                 StringBuilder sb2 = new StringBuilder(); 75                 DataRow row = table.Rows[index]; 76  77                 for (int i = 0; i < table.Columns.Count; i++) 78                 { 79  80                     string input = row[i].ToString(); 81                     string format = "{0}"; 82                     if (input.Contains(",")) 83                     { 84                         format = "\"{0}\""; 85                     } 86  87                     if (i == table.Columns.Count - 1) 88                     { 89                         sb.Append(String.Format(format, ReplaceSpecialChars(input))); 90                     } 91                     else 92                     { 93                         sb.AppendFormat(format + ",", ReplaceSpecialChars(input)); 94                     } 95                 } 96  97                 if (index < table.Rows.Count - 1) 98                     sb.Append(Environment.NewLine); 99             }100 101             return sb.ToString();102         }103 104         public static void ExportToCSVFile(DataTable table, string filename)105         {106             // using (StreamWriter sw = new StreamWriter(filename, false,Encoding.UTF8))107             using (StreamWriter sw = new StreamWriter(filename, false))108             {109                 string text = ExportToCSV(table);110                 sw.WriteLine(text);111             }112         }113 114         public static string ReplaceSpecialChars(string input)115         {116             // space -> _x0020_   特殊字符的替换117             // % -> _x0025_118             // # -> _x0023_119             // & -> _x0026_120             // / -> _x002F_121             if (input == null)122                 return "";123             //input = input.Replace(" ", "_x0020_");124             //input.Replace("%", "_x0025_");125             //input.Replace("#", "_x0023_");126             //input.Replace("&", "_x0026_");127             //input.Replace("/", "_x002F_");128 129             input = input.Replace("\"", "\"\"");130 131             return input;132         }133 134         public static DataTable ReadExcel(string fullpath, string sheetname = "sheet1$")135         {136             DataTable table = new DataTable();137 138             string connectionstring = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;';", fullpath);139             //string cmdstring = "select * from [sheet1$]";140             string cmdstring = "select * from [" + sheetname + "]";141 142             using (OleDbConnection conn = new OleDbConnection(connectionstring))143             {144                 conn.Open();145 146                 OleDbDataAdapter adapter = new OleDbDataAdapter(cmdstring, conn);147                 adapter.Fill(table);148 149                 conn.Close();150             }151 152             return table;153         }154 155         public static DataTable ExcelDs(string filenameurl)156         {157             string strConn = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", filenameurl); ;158             OleDbConnection conn = new OleDbConnection(strConn);159             conn.Open();160             //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　161             DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });162             //包含excel中表名的字符串数组163             string[] strTableNames = new string[dtSheetName.Rows.Count];164             for (int k = 0; k < dtSheetName.Rows.Count; k++)165             {166                 strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();167             }168 169 170             OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + strTableNames[0] + "]", conn);171             DataTable ds = new DataTable(); odda.Fill(ds);172 173             conn.Close();174             conn.Dispose();175             return ds;176         }177 178         public static DataTable ReadExcelTopVersion(string fullpath, string sheetname = "sheet1$")179         {180             DataTable table = new DataTable();181 182             //我测试用下183             //string connectionstring = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;';", fullpath);184 185             //这个是正确的186             string connectionstring = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", fullpath);187             //string cmdstring = "select * from [sheet1$]";188             string cmdstring = "select * from [" + sheetname + "]";189 190             using (OleDbConnection conn = new OleDbConnection(connectionstring))191             {192                 conn.Open();193 194                 OleDbDataAdapter adapter = new OleDbDataAdapter(cmdstring, conn);195                 adapter.Fill(table);196 197                 conn.Close();198             }199 200             return table;201         }202 203         public static List
        
          ReadExcel2(string fullpath)204         {205             List
         
           records = new List
          
           ();206 DataTable table = CSV.ReadExcel(fullpath);207 208 foreach (DataRow row in table.Rows)209 {210 records.Add(row[0].ToString());211 }212 213 return records;214 }215 216 /// 
           217 /// 使用Aspose方法读取csv文件218 /// 当前8条数据为int类型,后面数据为string类型,会报错219 /// 乱码文件正确读取220 /// 
221 /// 
           222 /// 
           223 public static DataTable ReadCSVByAspose(string fullpath)224 {225 Workbook workbook = new Workbook(fullpath);226 Cells cells = workbook.Worksheets[0].Cells; 227 DataTable data = cells.ExportDataTable(0, 0, cells.MaxDataRow, cells.MaxDataColumn + 1, true);228 return data;229 }230 231 /// 
           232 /// sun : use ace.oledb, not use jet.oledb233 /// 
234 /// 
           235 /// 
           236 public static DataTable ReadCSVByACEOLEDB(string excelFilename)237 {238 string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"{0}\";Extended Properties=\"Text\"", Directory.GetParent(excelFilename));239 DataSet ds = new DataSet();240 string fileName = string.Empty;241 using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))242 {243 connection.Open();244 fileName = Path.GetFileName(excelFilename);245 246 string strExcel = "select * from " + "[" + fileName + "]";247 OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, connectionString);248 adapter.Fill(ds, fileName);249 connection.Close();250 //tableNames.Clear();251 }252 return ds.Tables[fileName];253 }254 255 }256 }