//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);
}
}