//public string[] GetRow(string RangeStart, string RangeEnd) //{ // try // { // if (iRowsRead == Worksheet.UsedRange.Count) // return null; // iRowsRead++; // Range range = Worksheet.get_Range(RangeStart + iRowsRead.ToString(), RangeEnd + iRowsRead.ToString()); // Array myvalues = (Array)range.Cells.Value2; // return saryCurrentRow = ConvertToStringArray(myvalues); // } // catch (COMException ex) // { // throw new COMException("Die Excel-Tabelle ist geschlossen worden!", ex); // } //} //private string[] ConvertToStringArray(Array values) //{ // string[] sArray = new string[values.Length]; // for (int i = 1; i <= values.Length; i++) // { // if (values.GetValue(1, i) == null) // sArray[i - 1] = ""; // else // sArray[i - 1] = values.GetValue(1, i).ToString().Trim(); // } // return sArray; //} public static void saveToExcel(string pathFileExt, String2Dim[] data) { ExcelApplicationExtended excelApplicationExtended = new ExcelApplicationExtended(); saveToExcel(excelApplicationExtended.excelApp, pathFileExt, data); excelApplicationExtended.close(); }
public static String3Dim loadFromExcelSlow(string pathFileExt) { ExcelApplicationExtended excelApplicationExtended = new ExcelApplicationExtended(); String3Dim data = loadFromExcelSlow(excelApplicationExtended.excelApp, pathFileExt); excelApplicationExtended.close(); return(data); }
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 extractCSVFromPath(string pathName) { if (Directory.Exists(pathName)) { ExcelApplicationExtended excelApplicationExtended = new ExcelApplicationExtended(); String[] filesXLS = Directory.GetFiles(pathName, "*.xls"); for (int f = 0; f < filesXLS.Length; f++) { String3Dim data = loadFromExcel(excelApplicationExtended.excelApp, filesXLS[f]); for (int w = 0; w < data.Count; w++) { string csvFileName = filesXLS[f] + w.ToString("000") + ".csv"; //saveCSV File 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); } }