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 });
 }
Esempio n. 8
0
        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?
            }
        }
Esempio n. 9
0
        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);
        }