public static String2Dim loadFromCSV_USA(string pathFile)
{
String2Dim data = new String2Dim();
//StreamReader sr = new StreamReader(pathFile, System.Text.Encoding.UTF7); // verschluckt "+"
StreamReader sr = new StreamReader(pathFile, System.Text.Encoding.Default); // liest +, umlaute ( möglicherweise ging unter XP, NET 1.1 nicht?)
//StreamReader sr=new StreamReader(@"C:\Test\text1.txt",System.Text.Encoding.GetEncoding(1252));
//StreamReader sr=new StreamReader(@"C:\Test\text1.txt",System.Text.Encoding.GetEncoding(437));
//StreamReader sr=new StreamReader(@"C:\Test\text1.txt",System.Text.Encoding.GetEncoding(850));
while (sr.Peek() >= 0)
{
string line = sr.ReadLine();
string[] tokens = line.Split(','); // Problem mit Dezimalpunkt z.B. 1,200.30
StringList sl = new StringList(tokens.Length);
for (int t = 0; t < tokens.Length; t++)
{
tokens[t] = convertFrom_USA(tokens[t].Trim());
sl.Add(tokens[t]);
}
data.Add(sl);
}
sr.Close();
return(data);
}
public static void saveToExcel(Microsoft.Office.Interop.Excel._Application excelApp, string pathFileExt, String2Dim[] data)
{
excelApp.Workbooks.Close();
Workbook wb = excelApp.Workbooks.Add(System.Reflection.Missing.Value);
for (int s = 0; s < data.Length; s++)
{
String2Dim dataSheet = data[s];
Worksheet ws = null;
if (wb.Sheets.Count < s)
{
ws = (Worksheet)(wb.Worksheets.Add(
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value
));
}
else
{
ws = ((Worksheet)(wb.Worksheets[s + 1]));
}
for (int r = 0; r < dataSheet.Count; r++)
{
StringList dataRow = dataSheet[r];
for (int c = 0; c < dataRow.Count; c++)
{
ws.Cells[r + 1, c + 1] = dataRow[c];
if (c == 3)
{
((Microsoft.Office.Interop.Excel.Range)(ws.Cells[r + 1, c + 1])).NumberFormat = "#0,000";
}
}
}
}
int counter = 0;
FileInfo fi = new FileInfo(pathFileExt);
string ext = fi.Extension;
string newPathFile = pathFileExt;
while (File.Exists(newPathFile))
{
newPathFile = pathFileExt.Substring(0, pathFileExt.Length - (ext.Length)) + "_" + (counter++).ToString() + "." + ext;
}
wb.SaveCopyAs(newPathFile);
}
public static void saveToCSV(string pathFile, String2Dim data)
{
FileInfo fileCSV = new FileInfo(pathFile);
StreamWriter sw = fileCSV.CreateText();
for (int r = 0; r < data.Count; r++)
{
string line = "";
for (int c = 0; c < data[r].Count; c++)
{
line += data[r][c] + (c < data[r].Count - 1 ? ";" : "");
}
sw.WriteLine(line);
}
sw.Close();
return;
}
public static String2Dim loadFromCSV(string pathFile)
{
String2Dim data = new String2Dim();
FileInfo fileCSV = new FileInfo(pathFile);
StreamReader sr = fileCSV.OpenText();
while (sr.Peek() >= 0)
{
string line = sr.ReadLine();
string[] tokens = line.Split(';');
StringList sl = new StringList(tokens.Length);
for (int t = 0; t < tokens.Length; t++)
{
sl.Add(tokens[t]);
}
data.Add(sl);
}
sr.Close();
return(data);
}
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 loadFromExcelSlow(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);
//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);
}
// 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);
}
}
