public static void Run(string fileName) { ExcelApplicationExtended excelApplicationExtended = new ExcelApplicationExtended(); String3Dim data = ExcelUtility.loadFromExcel(excelApplicationExtended.excelApp, fileName); for (int w = 0; w < data.Count; w++) { string csvFileName = fileName + w.ToString("000") + ".csv"; //saveCSV File ExcelUtility.saveToCSV(csvFileName, data[w]); } excelApplicationExtended.close(); }
public static void doHUK() { String2Dim dataTarget = new String2Dim(); StringList newRecord = new StringList(); newRecord.Add(" "); newRecord.Add("N:"); newRecord.Add("BAP:"); newRecord.Add("G:"); newRecord.Add("ALTER:"); // Alter newRecord.Add("BTR:"); // Beitrag newRecord.Add("KEA:"); // KEA newRecord.Add("KMA:"); // KMA newRecord.Add("KMV:"); // KMV dataTarget.Add(newRecord); string pathName = @"C:\Projekte\Dokumente\Kv\HUK\Eigene\Test"; if (Directory.Exists(pathName)) { ExcelApplicationExtended excelApplicationExtended = new ExcelApplicationExtended(); String[] filesXLS = Directory.GetFiles(pathName, "*.xls"); for (int f = 0; f < filesXLS.Length; f++) { try { // String3Dim fileData = loadFromExcel(filesXLS[f]); String3Dim fileData = loadFromExcelSlow(filesXLS[f]); for (int w = 0; w < fileData.Count; w++) { String2Dim ws0Data = fileData[w]; string tarifName = ws0Data[1][1]; string stand = ""; // ws0Data[3][1].ToString().Substring(7); for (int r = 12; r < 100; r++) { if (r < ws0Data.Count) { StringList orgRecord = ws0Data[r]; StringList oldRecord = new StringList(); for (int s = 0; s < 10; s++) { oldRecord.Add(""); if (s < orgRecord.Count) { oldRecord[s] = orgRecord[s]; } else { oldRecord[s] = ""; } } if (oldRecord[1].Trim() != "") // Alter { newRecord = new StringList(); newRecord.Add(" "); newRecord.Add(tarifName); newRecord.Add(stand); newRecord.Add("M"); newRecord.Add(oldRecord[1]); // Alter newRecord.Add(oldRecord[2]); // Beitrag newRecord.Add(oldRecord[4]); // KEA newRecord.Add(oldRecord[6]); // KMA newRecord.Add(oldRecord[8]); // KMV dataTarget.Add(newRecord); newRecord = new StringList(); newRecord.Add(" "); newRecord.Add(tarifName); newRecord.Add(stand); newRecord.Add("W"); newRecord.Add(oldRecord[1]); // Alter newRecord.Add(oldRecord[3]); // Beitrag newRecord.Add(oldRecord[5]); // KEA newRecord.Add(oldRecord[7]); // KMA newRecord.Add(oldRecord[9]); // KMV dataTarget.Add(newRecord); } } } } } catch (Exception se) { Debug.Write("fehler " + filesXLS[f] + " " + se.Message); } Debug.Write(f.ToString()); } excelApplicationExtended.close(); ExcelUtility.saveToCSV(pathName + "\\output.txt", dataTarget); } }
// public static String3Dim loadFromExcelViaCSV( Excel.Application excelApp, string pathFile ) // { // String3Dim data = new String3Dim();; // // StringList fileList = new StringList(); // // Excel.Workbook wb = excelApp.Workbooks.Open( pathFile, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value // ); // // for ( int w = 0; w < wb.Sheets.Count; w++ ) // { // Excel.Worksheet ws = (Excel.Worksheet)wb.Sheets[w+1]; // string pathFileCSV = pathFile+"_"+w.ToString("000")+".csv"; // // object[,] values = (object[,])ws.UsedRange.Value2; // if ( values != null ) // { // ws.SaveAs( // pathFileCSV, // Excel.XlFileFormat.xlCSVWindows, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value // ); // fileList.Add( pathFileCSV ); // } // } // // wb.Close( // System.Reflection.Missing.Value, // System.Reflection.Missing.Value, // System.Reflection.Missing.Value // ); // // for ( int w = 0; w < fileList.Count; w++ ) // { // data.Add( loadFromCSV( fileList[w] ) ); // } // return data; // } public static void doMannheimer() { String2Dim dataTarget = new String2Dim(); StringList newRecord = new StringList(); newRecord.Add(" "); newRecord.Add("N:"); newRecord.Add("BAP:"); newRecord.Add("G:"); newRecord.Add("ALTER:"); // Alter newRecord.Add("BTR:"); // Beitrag newRecord.Add("KEA:"); // KEA newRecord.Add("KMA:"); // KMA newRecord.Add("KMV:"); // KMV dataTarget.Add(newRecord); string pathName = @"C:\Projekte\Dokumente\Kv\Mannheimer\Eigene\Test"; if (Directory.Exists(pathName)) { ExcelApplicationExtended excelApplicationExtended = new ExcelApplicationExtended(); String[] filesXLS = Directory.GetFiles(pathName, "*.xls"); for (int f = 0; f < filesXLS.Length; f++) { try { // String3Dim fileData = loadFromExcel(filesXLS[f]); String3Dim fileData = loadFromExcelSlow(filesXLS[f]); String2Dim ws0Data = fileData[0]; string tarifName = ws0Data[1][1]; string stand = ws0Data[3][1].ToString().Substring(7); for (int r = 7; r < 200; r++) { StringList oldRecord = ws0Data[r]; if (oldRecord[0].Trim() == "") { break; } newRecord = new StringList(); newRecord.Add(" "); newRecord.Add(tarifName); newRecord.Add(stand); newRecord.Add("M"); newRecord.Add(oldRecord[0]); // Alter newRecord.Add(oldRecord[1]); // Beitrag newRecord.Add(oldRecord[3]); // KEA newRecord.Add(oldRecord[4]); // KMA newRecord.Add(oldRecord[5]); // KMV dataTarget.Add(newRecord); newRecord = new StringList(); newRecord.Add(" "); newRecord.Add(tarifName); newRecord.Add(stand); newRecord.Add("W"); newRecord.Add(oldRecord[0]); // Alter newRecord.Add(oldRecord[7]); // Beitrag newRecord.Add(oldRecord[9]); // KEA newRecord.Add(oldRecord[10]); // KMA newRecord.Add(oldRecord[11]); // KMV dataTarget.Add(newRecord); } } catch { Debug.Write("fehler " + filesXLS[f]); } Debug.Write(f.ToString()); } excelApplicationExtended.close(); ExcelUtility.saveToCSV(pathName + "\\output.txt", dataTarget); } }
public static String3Dim loadFromExcel(Microsoft.Office.Interop.Excel._Application excelApp, string pathFileExt) { string path = Path.GetPathRoot(pathFileExt); String3Dim data = new String3Dim(); // OK wird geladen Microsoft.Office.Interop.Excel.Workbook wb = excelApp.Workbooks.Open( pathFileExt, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, true, System.Reflection.Missing.Value ); // Excel 2003 macht Probleme: wenn "xls" im tmp-File enthalten, dann werden die Umlaute nicht korrekt in tmp_File geschrieben (Fehler im Excel???) string tmpFile = pathFileExt.Replace(".xls", ".txt"); // die copy Datei wg. Flush?) string tmpFileCopy = pathFileExt.Replace(".xls", "copy.txt"); for (int w = 0; w < wb.Sheets.Count; w++) { Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[w + 1]; #region Methode SaveAS macht Probleme mit Formaten ( local -Parameter geht nicht ) // Probleme probleme Komma /Semikolon ws.SaveAs( tmpFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVWindows, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, true // ( wird ignoriert: Excel verwendet US-Formate, immerhin gehen die Umlaute... ); File.Copy(tmpFile, tmpFileCopy, true); // wg. Flush? // String2Dim data1 = ExcelUtility.loadFromCSV_USA(tmpFileCopy); String2Dim data1 = ExcelUtility.loadFromCSV(tmpFileCopy); data.Add(data1); #endregion SaveAS #region Cell by Cell / Methode funktioniert ist aber 10 X langsamer /* * // values helfen nur der Optimierung...... * object[,] values = (object[,])ws.UsedRange.Value2; // Datumsformate gehen verloren * if (values != null) * { * int intRows = ws.UsedRange.Rows.Count; * int intCols = ws.UsedRange.Columns.Count; * String2Dim data1 = new String2Dim(intRows, intCols); * for (int r = 0; r < intRows; r++) * { * for (int c = 0; c < intCols; c++) * { * if (values[r + 1, c + 1] != null) * { * string text = (string)((Microsoft.Office.Interop.Excel.Range)ws.Cells[r + 1, c + 1]).Text; * if (text.Contains(";")) * { * Debug.Fail("Warnung: Excel - Zelle enthält Semikolons (;) " + text); * } * data1[r][c] = text.Trim(); * } * } * // Nur Test: Console.WriteLine(r); * } * data.Add(data1); * } */ #endregion Cell by Cell } wb.Close( System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value ); //File.Delete( tmpFileCopy); //File.Delete( tmpFile ); return(data); }