public static DataTable OpenPriceTable(string filePath, DataTableMarking dataTableMarking, PriceFormat?priceFormat, PriceEncode encode) { DataTable dataTablePrice = null; if (dataTableMarking.Rows.Count > 1) { var fields = new ArrayList(); for (var i = 0; i < dataTableMarking.Rows.Count; i++) { var row = dataTableMarking.Rows[i]; var fieldName = row["MNameField"].ToString(); var beginPosition = Convert.ToInt32(row["MBeginField"]); var endPosition = Convert.ToInt32(row["MEndField"]); fields.Add(new TxtFieldDef(fieldName, beginPosition, endPosition)); } fields.Sort(new TxtFieldDef()); var schemaFile = Path.GetDirectoryName(filePath) + Path.DirectorySeparatorChar + "Schema.ini"; using (var w = new StreamWriter(schemaFile, false, Encoding.GetEncoding(1251))) { w.WriteLine("[" + Path.GetFileName(filePath) + "]"); w.WriteLine(((priceFormat == PriceFormat.FixedWIN) || (priceFormat == PriceFormat.NativeFixed && encode == PriceEncode.Cp1251)) ? "CharacterSet=ANSI" : "CharacterSet=OEM"); w.WriteLine("Format=FixedLength"); w.WriteLine("ColNameHeader=False"); w.WriteLine("MaxScanRows=300"); if (fields.Count > 0) { int j = 1; TxtFieldDef prevTFD, currTFD = (TxtFieldDef)fields[0]; if (1 == currTFD.posBegin) { w.WriteLine(String.Format("Col{0}={1} Text Width {2}", j, currTFD.fieldName, currTFD.posEnd)); j++; } else { w.WriteLine(String.Format("Col{0}={1} Text Width {2}", j, "x", currTFD.posBegin - 1)); j++; w.WriteLine(String.Format("Col{0}={1} Text Width {2}", j, currTFD.fieldName, currTFD.posEnd - currTFD.posBegin + 1)); j++; } for (var i = 1; i <= fields.Count - 1; i++) { prevTFD = (TxtFieldDef)fields[i - 1]; currTFD = (TxtFieldDef)fields[i]; if (currTFD.posBegin == prevTFD.posEnd + 1) { w.WriteLine(String.Format("Col{0}={1} Text Width {2}", j, currTFD.fieldName, currTFD.posEnd - currTFD.posBegin + 1)); j++; } else { w.WriteLine(String.Format("Col{0}={1} Text Width {2}", j, "x", currTFD.posBegin - prevTFD.posEnd - 1)); j++; w.WriteLine(String.Format("Col{0}={1} Text Width {2}", j, currTFD.fieldName, currTFD.posEnd - currTFD.posBegin + 1)); j++; } } } else { w.WriteLine(String.Format("Col{0}=x1 Text Width {1}", 1, 255)); } } var connectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Text\"", Path.GetDirectoryName(filePath)); using (var connection = new OleDbConnection(connectionString)) { connection.Open(); var dataAdapter = new OleDbDataAdapter(String.Format("select * from {0}", Path.GetFileName(filePath).Replace(".", "#")), connection); dataTablePrice = new DataTable(); dataAdapter.SyncFill(dataTablePrice); if (dataTablePrice.Rows.Count == 0) { throw new Exception("При открытии файла таблица с полями прайс-листа оказалась пуста. Предположительно неверный формат или файл поврежден"); } } } return(dataTablePrice); }
private static DataTable OpenTextDelimiterFile(string filePath, PriceFormat?fmt, string delimiter, PriceEncode encode) { filePath = CreateCopyWithoutSpacesAndDots(filePath); var fileName = Path.GetDirectoryName(filePath) + Path.DirectorySeparatorChar + "Schema.ini"; using (var w = new StreamWriter(fileName, false, Encoding.GetEncoding(1251))) { w.WriteLine("[" + Path.GetFileName(filePath) + "]"); w.WriteLine(((fmt == PriceFormat.DelimWIN) || ((fmt == PriceFormat.NativeDelim && encode == PriceEncode.Cp1251))) ? "CharacterSet=ANSI" : "CharacterSet=OEM"); w.WriteLine(("TAB" == delimiter.ToUpper()) ? "Format=TabDelimited" : "Format=Delimited(" + delimiter + ")"); w.WriteLine("ColNameHeader=False"); w.WriteLine("MaxScanRows=300"); } string replaceFile; using (var reader = new StreamReader(filePath, Encoding.GetEncoding(1251))) { replaceFile = reader.ReadToEnd(); } replaceFile = replaceFile.Replace("\"", ""); using (var sw = new StreamWriter(filePath, false, Encoding.GetEncoding(1251))) { sw.Write(replaceFile); } int maxColCount = 0; var tableName = Path.GetFileName(filePath).Replace(".", "#"); var connectionFormatString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Text\""; var connectionString = String.Format(connectionFormatString, Path.GetDirectoryName(filePath)); var connection = new OleDbConnection(connectionString); Application.DoEvents(); connection.Open(); DataTable ColumnNames = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null }); maxColCount = (ColumnNames.Rows.Count >= 256) ? 255 : ColumnNames.Rows.Count; connection.Close(); Application.DoEvents(); using (var w = new StreamWriter(Path.GetDirectoryName(filePath) + Path.DirectorySeparatorChar + "Schema.ini", false, Encoding.GetEncoding(1251))) { w.WriteLine("[" + Path.GetFileName(filePath) + "]"); w.WriteLine(((fmt == PriceFormat.DelimWIN) || ((fmt == PriceFormat.NativeDelim && encode == PriceEncode.Cp1251))) ? "CharacterSet=ANSI" : "CharacterSet=OEM"); w.WriteLine(("TAB" == delimiter.ToUpper()) ? "Format=TabDelimited" : "Format=Delimited(" + delimiter + ")"); w.WriteLine("ColNameHeader=False"); w.WriteLine("MaxScanRows=300"); for (var i = 0; i <= maxColCount; i++) { w.WriteLine("Col{0}=F{0} Text", i); } } Application.DoEvents(); connection.ConnectionString = String.Format(connectionFormatString, Path.GetDirectoryName(filePath)); connection.Open(); var dataAdapter = new OleDbDataAdapter(String.Format( "select * from {0}", Path.GetFileName(filePath).Replace(".", "#")), connection); var dataTablePrice = new DataTable(); dataAdapter.SyncFill(dataTablePrice); if (dataTablePrice.Rows.Count == 0) { throw new Exception("При открытии файла таблица с полями прайс-листа оказалась пуста. Предположительно неверный формат или файл поврежден"); } connection.Close(); Application.DoEvents(); return(dataTablePrice); }