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