public static int getEnd(string cell, ComObject worksheet)
{
ComObject range = getRange(cell, "", worksheet);
ComObject end = range.GetObjectReturningProperty("End", new object[] { -4162 });
int rangeInt = (int)end.GetProperty("Row");
return rangeInt;
}
public static string getValue2ToString(string cell, ComObject worksheet)
{
try
{
ComObject nameRange = getRange(cell, "", worksheet);
object v2 = nameRange.GetProperty("Value2");
return v2.ToString();
}
catch
{
return "";
}
}
public static ComObject getRange(string cell, string cell2, ComObject worksheet)
{
try
{
if (cell2 == "")
return worksheet.InvokeObjectReturningFunction("get_Range", new object[] { cell, n });
else
return worksheet.InvokeObjectReturningFunction("get_Range", new object[] { cell, cell2 });
}
catch
{
if (cell2 == "")
return worksheet.GetObjectReturningProperty("Range", new object[] { cell, n });
else
return worksheet.GetObjectReturningProperty("Range", new object[] { cell, cell2 });
}
}
public static void setStringToValue2(string cell, string value, ComObject worksheet)
{
ComObject nameRange = getRange(cell, "", worksheet);
nameRange.SetProperty("Value2", value);
}
public static void setArrayToRangeValue2(string cell1, string cell2, object[,] value, ComObject worksheet)
{
ComObject nameRange = getRange(cell1, cell2, worksheet);
nameRange.SetProperty("Value", value);
}
public static void insertRow(string cell, ComObject worksheet)
{
ComObject range = getRange(cell, "", worksheet);
ComObject entireRow = range.GetObjectReturningProperty("EntireRow");
entireRow.InvokeFunction("Insert", -4121);
}
public static void deleteRow(string cell1, ComObject worksheet)
{
ComObject r1 = getRange(cell1, "", worksheet);
ComObject er1 = r1.GetObjectReturningProperty("EntireRow");
er1.InvokeFunction("Delete", new object[] { n });
}
public static void killExcelInstanceById(ref ComObject myExcelApp)
{
try
{
IntPtr processID = new IntPtr();
//API Funktion, out val: processId
GetWindowThreadProcessId((int)myExcelApp.GetProperty("Hwnd"), ref processID);
System.Diagnostics.Process myExcelProcess = System.Diagnostics.Process.GetProcessById(processID.ToInt32());
// kill it!
myExcelProcess.Kill();
}
catch
{
//TODO: Error?
}
}
private void writeDataInExcelSheet(string oldExcelFilePath, string newExcelFilePath, List<EntryWithFields> listEntries)
{
try
{
myWordApp = new ComObject("Excel.Application");
}
catch
{
MessageBox.Show(CnfLocalSettings.Instance.GetString("noOfficeInstalled"));
this.Close();
}
backgroundWorker1.ReportProgress(75);
string pdfPath = "";
try
{
myWordApp.SetProperty("Visible", false);
ComObject obj = myWordApp.GetObjectReturningProperty("Workbooks");
ComObject workbook = obj.InvokeObjectReturningFunction("Open", oldExcelFilePath);
ComObject objN = myWordApp.GetObjectReturningProperty("ActiveWorkbook");
ComObject worksheet = objN.GetObjectReturningProperty("ActiveSheet");
List<string> left = new List<string>();
List<string> came = new List<string>();
int rowNr = 7;
if (oldExcelFilePath.EndsWith("Muster.xlsx"))
{
listEntries.Sort();
object[,] values = new object[listEntries.Count, 22];
int maxNr = listEntries.Count + rowNr - 1;
string maxNrString = maxNr.ToString();
for (int i = 0, j = listEntries.Count; i < j; i++)
{
if (i != 0)
ExcelViaComWrapper.insertRow("B7", worksheet);
values[i, 0] = "'" + listEntries[i].Name;
values[i, 1] = Int32.Parse(listEntries[i].OldS);
values[i, 2] = "=RANG(C" + rowNr + ";C$6:C$" + maxNrString + ";0)";
values[i, 3] = listEntries[i].Points;
values[i, 4] = "=(G" + rowNr + "-D" + rowNr + ")*(-1)";
values[i, 5] = "=RANG(E" + rowNr + ";E$6:E$" + maxNrString + ";0)";
values[i, 6] = "=RUNDEN((E" + rowNr + "-C" + rowNr + ")/C" + rowNr + "*100; 2)";
values[i, 7] = "=RANG(H" + rowNr + ";H$6:H$" + maxNrString + ";0)";
values[i, 8] = "=E" + rowNr + "-C" + rowNr;
values[i, 9] = "=RANG(J" + rowNr + ";J$6:J$" + maxNrString + ";0)";
values[i, 10] = listEntries[i].OldBdE;
values[i, 11] = "=RANG(L" + rowNr + ";L$6:L$" + maxNrString + ";0)";
values[i, 12] = listEntries[i].VBases;
values[i, 13] = "=(P" + rowNr + "-M" + rowNr + ")*(-1)";
values[i, 14] = "=RANG(N" + rowNr + ";N$6:N$" + maxNrString + ";0)";
values[i, 15] = "=WENN(N" + rowNr + "=0;0;RUNDEN((N" + rowNr + "-L" + rowNr + ")/L" + rowNr + "*100; 2))";
values[i, 16] = "=RANG(Q" + rowNr + ";Q$6:Q$" + maxNrString + ";0)";
values[i, 17] = "=N" + rowNr + "-L" + rowNr;
values[i, 18] = "=RANG(S" + rowNr + ";S$6:S$" + maxNrString + ";0)";
values[i, 19] = "";
values[i, 20] = "=RANG(W" + rowNr + ";W$6:W$" + maxNrString + ";0)";
values[i, 21] = "=((E" + rowNr + "-$X$4)/$X$5+(H" + rowNr + "-$Y$4)/$Y$5*4+(S" + rowNr + "-$Z$4)/$Z$5*4)/9";
rowNr++;
}
if (CnfLocalSettings.Instance.Language == "1")
{
ExcelViaComWrapper.setArrayToRangeValue2("C2", "J2", new object[1, 1] { { CnfLocalSettings.Instance.GetString("score") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("L2", "S2", new object[1, 1] { { CnfLocalSettings.Instance.GetString("dfb") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("C3", "J3", new object[1, 1] { { CnfLocalSettings.Instance.GetString("before") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("L3", "L3", new object[1, 1] { { CnfLocalSettings.Instance.GetString("before") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("E3", "F3", new object[1, 1] { { CnfLocalSettings.Instance.GetString("now") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("N3", "P3", new object[1, 1] { { CnfLocalSettings.Instance.GetString("now") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("P5", "P5", new object[1, 1] { { CnfLocalSettings.Instance.GetString("now") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("G5", "G5", new object[1, 1] { { CnfLocalSettings.Instance.GetString("now") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("H3", "J3", new object[1, 1] { { CnfLocalSettings.Instance.GetString("inc") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("Q3", "S3", new object[1, 1] { { CnfLocalSettings.Instance.GetString("inc") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("C4", "C5", new object[1, 1] { { CnfLocalSettings.Instance.GetString("value") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("E4", "E5", new object[1, 1] { { CnfLocalSettings.Instance.GetString("value") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("L4", "L5", new object[1, 1] { { CnfLocalSettings.Instance.GetString("value") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("N4", "N5", new object[1, 1] { { CnfLocalSettings.Instance.GetString("value") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("F4", "G4", new object[1, 1] { { CnfLocalSettings.Instance.GetString("pos") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("O4", "P4", new object[1, 1] { { CnfLocalSettings.Instance.GetString("pos") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("V4", "V5", new object[1, 1] { { CnfLocalSettings.Instance.GetString("pos") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("V2", "V2", new object[1, 1] { { CnfLocalSettings.Instance.GetString("sum") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("V3", "V3", new object[1, 1] { { CnfLocalSettings.Instance.GetString("weighted") } }, worksheet);
}
string sumColNrString = (maxNr + 1).ToString();
ExcelViaComWrapper.setArrayToRangeValue2("P" + sumColNrString, "Q" + sumColNrString, new object[1, 1] { { CnfLocalSettings.Instance.GetString("sum") } }, worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("G" + sumColNrString, "H" + sumColNrString, new object[1, 1] { { CnfLocalSettings.Instance.GetString("sum") } }, worksheet);
ExcelViaComWrapper.setStringToValue2("J" + sumColNrString, "=SUMME(J6:J" + maxNr + ")", worksheet);
ExcelViaComWrapper.setStringToValue2("S" + sumColNrString, "=SUMME(S6:S" + maxNr + ")", worksheet);
ExcelViaComWrapper.setStringToValue2("X4", "=MIN(E6:E" + maxNr, worksheet);
ExcelViaComWrapper.setStringToValue2("X5", "=(MAX(E6:E" + maxNr + ")-MIN(E6:E" + maxNr + "))/100", worksheet);
ExcelViaComWrapper.setStringToValue2("Y4", "=MIN(H6:H" + maxNr, worksheet);
ExcelViaComWrapper.setStringToValue2("Y5", "=(MAX(H6:H" + maxNr + ")-MIN(H6:H" + maxNr + "))/100", worksheet);
ExcelViaComWrapper.setStringToValue2("Z4", "=MIN(S6:S" + maxNr, worksheet);
ExcelViaComWrapper.setStringToValue2("Z5", "=(MAX(S6:S" + maxNr + ")-MIN(S6:S" + maxNr + "))/100", worksheet);
ExcelViaComWrapper.setArrayToRangeValue2("B7", "W" + (rowNr - 1).ToString(), values, worksheet);
ExcelViaComWrapper.deleteRow("B6", worksheet);
}
ExcelViaComWrapper.setStringToValue2("B" + (rowNr + 1).ToString(), CnfLocalSettings.Instance.GetString("gone"), worksheet);
ExcelViaComWrapper.setStringToValue2("C" + (rowNr + 1).ToString(), CnfLocalSettings.Instance.GetString("new"), worksheet);
ComObject r = ExcelViaComWrapper.getRange("B" + (rowNr + 2), "", worksheet);
ComObject font = r.GetObjectReturningProperty("Font");
font.SetProperty("Bold", true);
ComObject r2 = ExcelViaComWrapper.getRange("C" + (rowNr + 2), "", worksheet);
ComObject font2 = r2.GetObjectReturningProperty("Font");
font2.SetProperty("Bold", true);
backgroundWorker1.ReportProgress(98);
pdfPath = newExcelFilePath.Substring(0, newExcelFilePath.Length - 4) + "pdf";
// DBase-Datei schließen (nicht speichern) und Excel-Datei sichtbar machen
try
{
FileInfo fi = new FileInfo(newExcelFilePath);
if (fi.Exists == true)
fi.Delete();
workbook.InvokeFunction("SaveAs", new object[] { newExcelFilePath, n, n, n, n, n, 3, n, n, n, n, n });
workbook.InvokeFunction("ExportAsFixedFormat", new object[] { 0, pdfPath, n, n, n, n, n, n, n });
MessageBox.Show(CnfLocalSettings.Instance.GetString("createdSuccessful"));
}
catch (Exception ex)
{
MessageBox.Show(CnfLocalSettings.Instance.GetString("errorSaveFile") + ex.Message);
}
backgroundWorker1.ReportProgress(100);
workbook.InvokeFunction("Close", new object[] { false, n, n });
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
}
catch
{
}
GC.Collect();
}
catch (Exception ex)
{
MessageBox.Show(CnfLocalSettings.Instance.GetString("unknown") + ex.Message);
}
killExcelInstanceById(ref myWordApp);
GC.Collect();
if (checkBoxShowPDF.Checked)
System.Diagnostics.Process.Start(pdfPath);
}
