常用类-CSV---OLEDB
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 /// <summary> 16 /// "HDR=Yes;"声名第一行的数据为域名，并非数据。 17 /// 这种方式读取csv文件前8条为int类型后面为string类型 则后面数据读取不了 18 /// 还存在乱码问题 19 /// </summary> 20 /// <param name="fullPath"></param> 21 /// <returns></returns> 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<string> Read2(string fullpath) 43 { 44 DataTable table = CSV.Read(fullpath); 45 List<string> records = new List<string>(); 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<string> ReadExcel2(string fullpath) 204 { 205 List<string> records = new List<string>(); 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 /// <summary> 217 /// 使用Aspose方法读取csv文件 218 /// 当前8条数据为int类型,后面数据为string类型,会报错 219 /// 乱码文件正确读取 220 /// </summary> 221 /// <param name="fullpath"></param> 222 /// <returns></returns> 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 /// <summary> 232 /// sun : use ace.oledb, not use jet.oledb 233 /// </summary> 234 /// <param name="excelFilename"></param> 235 /// <returns></returns> 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 }
备注: 需要安装引擎
服务器未安装office软件的时候,使用连接字符串,读取excel失败的解决办法,下载 安装即可
Microsoft Access Database Engine 2010 Redistributable
对应的excel连接字符串：
"Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
