Ejemplo n.º 1
0
 public void Save(string fileName = "")
 {
     if (fileName == string.Empty)
     {
         wb.Save();
     }
     else
     {
         wb.SaveAs(fileName);
     }
 }
Ejemplo n.º 2
0
        private void Tool_Click(object sender, RoutedEventArgs e)
        {
            MSWord.Application EXCL1 = new MSWord.Application(); //新建一个应用程序EXC1
            EXCL1.Visible = true;                                //设置EXC1打开后可见

            MSWord.Workbooks wbs = EXCL1.Workbooks;
            MSWord._Workbook wb  = wbs.Add(GlobalData.gFileName);  //打开EXCEL

            MSWord._Worksheet mySheet;

            mySheet = wb.Sheets[wantUseSheetIndex(GlobalData.gMySheetName, wb)]; //找到我要操作的sheet
            mySheet.Activate();
            //tiaoshi.Text = excelElementRead(mySheet, 1, 5).Interior.Color.ToString();
            System.Data.DataTable dt = readNonredunOneColumn(mySheet, GlobalData.gWantUseColumn);

            MSWord._Worksheet testPointSheet = wb.Sheets[wantUseSheetIndex(GlobalData.gTestPointSheetName, wb)];
            testPointSheet.Activate();
            excelElementWrite(testPointSheet, 1, 1, "测试点序号");
            excelElementWrite(testPointSheet, 1, 2, "测试点");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                excelElementWrite(testPointSheet, i + 2, 1, dt.Rows[i][0].ToString());
                excelElementWrite(testPointSheet, i + 2, 2, dt.Rows[i][1].ToString());
            }

            writeTestPointCount(mySheet, GlobalData.gTestPointRange, GlobalData.gWantUseColumn, dt);

            wb.Save();
        }
Ejemplo n.º 3
0
 private void UpdateExcel(int row, int col, string data)
 {
     // UpdateExcel() is the method used to replace cells inside the specified
     // Excel spreadsheet, using the Interop.Excel library.
     Excel.Application oXL    = null;
     Excel._Workbook   oWB    = null;
     Excel._Worksheet  oSheet = null;
     try
     {
         // This try block opens up your specified document in the File Chooser,
         // and attempts to write to cells with the specified data given (string data).
         oXL    = new Excel.Application();
         oWB    = oXL.Workbooks.Open(excelDocument);
         oSheet = String.IsNullOrEmpty(dropDown.Text) ? (Excel._Worksheet)oWB.ActiveSheet : (Excel._Worksheet)oWB.Worksheets[dropDown.Text];
         oSheet.Cells[row, col] = data;
         oWB.Save();
     }
     catch (Exception ex)
     {
         Debug.WriteLine(ex);
     }
     finally
     {
         if (oWB != null)
         {
             oWB.Close(); // Close down document, to avoid deadlock or R/W issues.
         }
     }
 }
Ejemplo n.º 4
0
 /// <summary>
 /// Değişiklikleri kaydedin.
 /// </summary>
 public void Save()
 {
     if (m_Workbook != null)
     {
         m_Workbook.Save();
     }
 }
Ejemplo n.º 5
0
        public void PrintWskazniki(List <Wskazniki> listaWskaznikow, string nazwaSpolki)
        {
            oWB = (Excel._Workbook)(oXL.Workbooks.Open("E:\\raporty\\wyniki.xlsx"));


            oSheet      = (Excel._Worksheet)oWB.Sheets.Add();
            oSheet.Name = nazwaSpolki;
            //oRng = oSheet.UsedRange;

            oSheet.Activate();

            //   worksheet.get_Range(topLeftLetter, bottomRightLetter).EntireColumn.AutoFit();


            for (int j = 0; j < 5; j++)
            {
                int startRok = 2010;
                oSheet.Cells[1, j + 4] = (startRok + j).ToString();
            }

            for (int i = 0; i < listaWskaznikow.Count(); i++)
            {
                oSheet.Cells[i + 2, 1] = listaWskaznikow[i].category;
                oSheet.Cells[i + 2, 2] = listaWskaznikow[i].name;
                oSheet.Cells[i + 2, 3] = listaWskaznikow[i].wzor;

                for (int j = 0; j < 5; j++)
                {
                    oSheet.Cells[i + 2, j + 4] = listaWskaznikow[i].value[j];
                }
            }

            oWB.Save();
        }
Ejemplo n.º 6
0
        //public bool dB_Delete(string p_dB, string p_Col, string p_Cond)
        //{
        //    bool ret_val = false;

        //    string sql = "DELETE FROM [Sheet1$] WHERE [" + p_Col + "] LIKE '" + p_Cond + "%';";
        //    ret_val = dB_Update(p_dB, sql);
        //    return ret_val;
        //}


        public bool dB_Delete(string p_dB, string p_Col, string p_Cond)
        {
            bool   ret_val = false;
            int    col_idx = 0, row_idx = 0;
            string filename = DataDir + p_dB + dbFileExt;

            XL.Application xlApp       = new XL.Application();
            XL._Workbook   xlWorkbook  = xlApp.Workbooks.Open(filename);
            XL._Worksheet  xlWorksheet = xlWorkbook.Worksheets["Sheet1"];
            XL.Range       xlRange     = xlWorksheet.UsedRange;

            int row_cnt = xlRange.Rows.Count;
            int col_cnt = xlRange.Columns.Count;

            // First find the column number
            for (int i = 1; i < col_cnt; i++)
            {
                string val = xlRange.Cells[1, i].Value2.ToString();
                if (val == p_Col)
                {
                    col_idx = i;
                    break;
                }
            }

            if (col_idx > 0)
            {
                // Next find the row number to delete
                for (int i = 2; i <= row_cnt; i++)
                {
                    string val = xlRange.Cells[i, col_idx].Value2.ToString();
                    if (val == p_Cond)
                    {
                        row_idx = i;
                        break;
                    }
                }

                if (row_idx > 0)
                {
                    (xlWorksheet.Rows[row_idx, System.Reflection.Missing.Value] as XL.Range).Delete(XL.XlDeleteShiftDirection.xlShiftUp);
                    ret_val = true;
                }
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();
            xlApp.DisplayAlerts = false;
            xlWorkbook.Save();
            xlWorkbook.Close();
            xlApp.Quit();

            // release COM objects
            Marshal.ReleaseComObject(xlWorkbook);
            Marshal.ReleaseComObject(xlWorksheet);
            Marshal.ReleaseComObject(xlApp);

            return(ret_val);
        }
Ejemplo n.º 7
0
        //adds project to SEQR workbook
        private static void AddToWorkbook(string parkName, string projectName, bool newProject)
        {
            string region = albPath.Substring(0, albPath.Length - 1);

            region = region.Substring(region.LastIndexOf("\\") + 1);
            string smRegion = region.Replace(" ", "");
            string workbook = albPath + smRegion + "SEQRWorkbook.xlsx";

            //workbook = "D:\\CentralSEQRWorkbook.xlsx";

            Excel.Application excelApp = new Excel.Application();
            excelApp.Visible = false;

            Excel._Workbook wBk = excelApp.Workbooks.Open(workbook, Type.Missing, Type.Missing,
                                                          Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                          Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            Excel._Worksheet wSht = (Excel.Worksheet)wBk.Worksheets[1];

            int rowNum = 0;

            string searchStr = newProject ? "" : projectName;
            int    searchCol = newProject ? 1 : 3;

            foreach (Excel.Range row in wSht.Rows)
            {
                Excel.Range rng = (Excel.Range)wSht.Cells[row.Row, searchCol];

                if (rng.Text.ToString().Equals(searchStr))
                {
                    rowNum = row.Row;
                    break;
                }
            }

            if (newProject)
            {
                PrincipalContext ctx      = new PrincipalContext(ContextType.Domain);
                UserPrincipal    user     = UserPrincipal.Current;
                string           fullName = user.GivenName + " " + user.Surname;

                wSht.Cells[rowNum, 1] = region;
                wSht.Cells[rowNum, 2] = parkName;
                wSht.Cells[rowNum, 3] = projectName;
                wSht.Cells[rowNum, 5] = DateTime.Today.Year;
                wSht.Cells[rowNum, 7] = fullName;
            }
            else
            {
                wSht.Hyperlinks.Add(wSht.Cells[rowNum, 4], albPath + parkName + "\\" + projectName, Type.Missing, Type.Missing, parkName + "\\" + projectName);
                wSht.Cells[rowNum, 13] = DateTime.Today;
            }

            wBk.Save();
            wBk.Close();
            excelApp.Quit();
            excelApp = null;
            GC.Collect();
        }
Ejemplo n.º 8
0
        /// <summary>
        /// 执行Excel中的宏
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径</param>
        /// <param name="macroName">宏名称</param>
        /// <param name="parameters">宏参数组</param>
        /// <param name="rtnValue">宏返回值</param>
        /// <param name="isShowExcel">执行时是否显示Excel</param>
        //public void RunExcelMacro(string excelFilePath, string macroName, object[] parameters, out object rtnValue, bool isShowExcel)
        public void RunExcelMacro(string excelFilePath, string macroName, object[] parameters, object rtnValue, bool isShowExcel)
        {
            try
            {
                #region 检查入参
                //检查文件是否存在
                if (!File.Exists(excelFilePath))
                {
                    MessageBox.Show(excelFilePath + " 文件不存在");
                    //return;
                }
                // 检查是否输入宏名称
                if (string.IsNullOrEmpty(macroName))
                {
                    MessageBox.Show("请输入宏的名称");
                    //return;
                }
                #endregion
                #region 调用宏处理
                // 准备打开Excel文件时的缺省参数对象
                object oMissing = System.Reflection.Missing.Value;
                // 根据参数组是否为空,准备参数组对象
                object[] paraObjects;
                if (parameters == null)
                {
                    paraObjects = new object[] { macroName };
                }
                else
                {
                    // 宏参数组长度
                    int paraLength = parameters.Length;
                    paraObjects    = new object[paraLength + 1];
                    paraObjects[0] = macroName;
                    for (int i = 0; i < paraLength; i++)
                    {
                        paraObjects[i + 1] = parameters[i];
                    }
                }
                // 创建Excel对象示例
                //Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
                // 判断是否要求执行时Excel可见
                if (isShowExcel)
                {
                    // 使创建的对象可见
                    oExcel.Visible = false;
                }

                rtnValue = this.RunMacro(oExcel, paraObjects);
                // 保存更改
                oBook.Save();
            }
            catch (Exception)
            {
                throw;
            }
        }
Ejemplo n.º 9
0
 private void button1_Click(object sender, System.EventArgs e)
   {
         Excel.Application xlexcel = null;
         Excel._Workbook xlWorkbook = null;
         Excel._Worksheet xlWorkSheet = null;
         Excel.Range oRng;
     try
        {
           //start excel and get application object
           oXL = new Microsoft.Office.Interop.Excel.Application();
           //open existing workbook
             oWB = oXL.Workbooks.Open("C:\\vehicledet.xlsx");
             oSheet = (Excel._Worksheet)oWB.ActiveSheet;
          //Add table headers going cell by cell.
           xlWorkSheet.Cells[1, 1] = "Plate Number";
           xlWorkSheet.Cells[1, 2] = "Car Model";
           xlWorkSheet.Cells[1, 3] = "Car Brand";
           xlWorkSheet.Cells[1, 4] = "Mileage";
          //Format A1:D1 as bold, vertical alignment = center.
           xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
           xlWorkSheet.get_Range("A1", "D1").VerticalAlignment = 
           Excel.XlVAlign.xlVAlignCenter;
           // insert text at every last row
             int _lastRow = xlWorkSheet.Range["A" +    xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1;
             xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text;
             xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text;
             xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text;
             xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text;
             //AutoFit columns A:D.
             oRng = xlWorkSheet.get_Range("A1", "D1");
             oRng.EntireColumn.AutoFit();
             //Make sure Excel is not visible and give the user control of Microsoft Excel's lifetime.
             xlexcel.Visible = false;
             xlexcel.UserControl = true;
             //can save update to same file but wont close unless user press x button
             xlWorkbook.Save();
             MessageBox.Show("Data saved successfully");
             xlWorkbook.Close();
             //allow excel to auto close by itself 
             oXL.Quit();
             Marshal.ReleaseComObject(oXL);
         }
         catch (Exception theException)
         {
             String errorMessage;
             errorMessage = "Error: ";
             errorMessage = String.Concat(errorMessage, theException.Message);
             errorMessage = String.Concat(errorMessage, " Line: ");
             errorMessage = String.Concat(errorMessage, theException.Source);
             MessageBox.Show(errorMessage, "Error");
         }
     }
Ejemplo n.º 10
0
        private void Button1_Click(object sender, RoutedEventArgs e)
        {
            string   cellName = this.DataContext.ToString();
            string   fileName = "C:\\Temp\\" + cellName + ".xlsx";
            FileInfo fi       = new FileInfo(@fileName);

            if (!fi.Exists)
            {
                wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                ws = wb.Worksheets[1];
                ws.Range["A1"].Value = cellName;
                DateTime currentDate = DateTime.Now;
                for (int i = 2; i <= 1000; i++)
                {
                    if (ws.Range["A" + i].Value == null)
                    {
                        ws.Range["A" + i].Value = currentDate;
                        break;
                    }
                }
                app.DisplayAlerts = false;
                wb.SaveAs(fileName);
                wb.Close();
                app.Quit();
                app.DisplayAlerts = true;
            }
            else
            {
                string myPath = (@fileName);
                app.Workbooks.Open(myPath);
                wb = app.ActiveWorkbook;
                ws = wb.ActiveSheet;
                DateTime currentDate = DateTime.Now;
                for (int i = 2; i <= 1000; i++)
                {
                    if (ws.Range["A" + i].Value == null)
                    {
                        ws.Range["A" + i].Value = currentDate;
                        break;
                    }
                }
                app.DisplayAlerts = false;
                wb.Save();
                wb.Close();
                app.Quit();
                app.DisplayAlerts = true;
            }
        }
Ejemplo n.º 11
0
        public int InsertEntry(ref ExcelDataUnit unitData)
        {
            CouplingRetData retData   = unitData.couplingRetData;
            double          totalTime = unitData.timeCost;

            if (index < 0)
            {
                return(index);
            }
            xls_exp  = new Microsoft.Office.Interop.Excel.ApplicationClass();
            xls_book = xls_exp.Workbooks.Open(fName, Missing.Value,
                                              false, Missing.Value, Missing.Value, Missing.Value,
                                              Missing.Value, Missing.Value, Missing.Value, true,
                                              Missing.Value, Missing.Value, Missing.Value,
                                              Missing.Value, Missing.Value);
            xls_sheet = (Microsoft.Office.Interop.Excel._Worksheet)xls_book.Worksheets.get_Item(1);

            xls_sheet.Cells[index, 1]  = Convert.ToString(index - startIndex + 1);
            xls_sheet.Cells[index, 2]  = unitData.prdId;
            xls_sheet.Cells[index, 3]  = retData.vpp.ToString();
            xls_sheet.Cells[index, 4]  = unitData.Vbr.ToString("F2");
            xls_sheet.Cells[index, 5]  = retData.lx1.ToString();
            xls_sheet.Cells[index, 6]  = retData.ly1.ToString();
            xls_sheet.Cells[index, 7]  = retData.lz.ToString();
            xls_sheet.Cells[index, 8]  = retData.lx2.ToString();
            xls_sheet.Cells[index, 9]  = retData.ly2.ToString();
            xls_sheet.Cells[index, 10] = Convert.ToString(((double)retData.duration) / 1000000);
            xls_sheet.Cells[index, 11] = totalTime.ToString();
            xls_sheet.Cells[index, 12] = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
            xls_sheet.Cells[index, 13] = unitData.couplingRetData.vpp1.ToString();
            xls_sheet.Cells[index, 14] = unitData.VAPD_Vbr3V_Iop_Vpp.ToString();

            object misValue = System.Reflection.Missing.Value;

//            if (index == 3)
//            {
            xls_sheet.Columns.AutoFit();
//            }

            xls_book.Saved = true;
            //xls_exp.ActiveWorkbook.SaveCopyAs(fName);
            xls_book.Save();
            //xls_book.SaveAs(fName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            Close();
            return(index++);
        }
Ejemplo n.º 12
0
        /// <summary>
        /// Save file
        /// </summary>

        public static void Save()
        {
            if (LogCalls)
            {
                DebugLog.Message("ExcelOp Save");
            }

            try
            {
                XlBook.Save();
                return;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
Ejemplo n.º 13
0
        private void OnApplicationExit(object sender, EventArgs e)
        {
            Gem();
            Thread.Sleep(100);
            excelApp.Run("Opdater");
            Thread.Sleep(2000);
            excelWorkbook.Save();
            Thread.Sleep(1000);
            excelWorkbook.Close();
            excelApp.Quit();

            Marshal.FinalReleaseComObject(excelWorkbook);
            Marshal.FinalReleaseComObject(excelApp);

            excelApp      = null;
            excelWorkbook = null;

            GC.Collect();
        }
Ejemplo n.º 14
0
        public void RunExcelMacro(Excel::Application excelApp, string macroName, object[] parameters, out object rtnValue)
        {
            object oMissing = System.Reflection.Missing.Value;

            object[] paraObjects;
            if (parameters == null)
            {
                paraObjects = new object[] { macroName };
            }
            else
            {
                // 宏参数组长度
                int paraLength = parameters.Length;

                paraObjects = new object[paraLength + 1];

                paraObjects[0] = macroName;
                for (int i = 0; i < paraLength; i++)
                {
                    paraObjects[i + 1] = parameters[i];
                }
            }

            rtnValue = "";
            try
            {
                rtnValue = excelApp.GetType().InvokeMember(
                    "Run",
                    System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod,
                    null,
                    excelApp,
                    paraObjects
                    );
            }
            catch (Exception e)
            {
                SharedObject.Instance.Output(SharedObject.enOutputType.Error, "宏执行异常,请检查宏名称与参数是否匹配", e.Message);
            }
            Excel._Workbook oBook = excelApp.ActiveWorkbook;
            oBook.Save();
        }
Ejemplo n.º 15
0
 public static void Export(DataTable dt, string filepath)
 {
     ExcelApp.Application myExcel = new ExcelApp.Application();
     //Create a New file
     ExcelApp._Workbook mybook = myExcel.Workbooks.Add();
     //Open the exist file
     //ExcelApp._Workbook mybook = myExcel.Workbooks.Open(filepath,
     //          Type.Missing, Type.Missing, Type.Missing,
     //    Type.Missing,Type.Missing, Type.Missing, Type.Missing,
     //    Type.Missing, Type.Missing, Type.Missing,
     //    Type.Missing, Type.Missing,Type.Missing, Type.Missing);
     //ExcelApp._Workbook mybook = myExcel.Workbooks.Open(Filename: filepath);
     myExcel.Visible = true;
     try
     {
         mybook.Activate();
         ExcelApp._Worksheet mysheet = mybook.Worksheets.Add();
         for (int i = 0; i < dt.Rows.Count; i++)
         {
             for (int j = 0; j < dt.Columns.Count; j++)
             {
                 ExcelApp.Range cell =
                     mysheet.get_Range(((char)(65 + j)).ToString() + (i + 1).ToString());
                 cell.Select();
                 cell.Cells.FormulaR1C1 = dt.Rows[i][j] ?? "";
             }
         }
         //mybook.SaveAs(Filename: filepath);
         mybook.Save();
     }
     catch (Exception ex)
     {
     }
     finally
     {
         mybook.Close();
         myExcel.Quit();
         GC.Collect();
     }
 }
Ejemplo n.º 16
0
 private void countTask(String fileName)
 {
     endTask += 1;
     Console.Clear();
     Console.Write("Started scanning {0}files. {1}/{0}", taskCount, endTask);
     if (endTask == taskCount)
     {
         if (EXCEL_PATH.Exists)
         {
             workBook.Save();
         }
         else
         {
             workBook.SaveAs(EXCEL_PATH.FullName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
                             false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
         }
         workBook.Close();
         excelApp.Quit();
         System.Diagnostics.Process.Start("explorer.exe", Application.StartupPath);
         System.Windows.Forms.Application.Exit();
     }
 }
Ejemplo n.º 17
0
        protected void SaveWorkBook(bool blnSave)
        {
            // save and close the workbook
            if (this.xlWorkBook != null)
            {
                if (blnSave && this.IsWorkBookOpened)
                {
                    xlWorkBook.Save();
                }

                xlWorkBook.Close(true, misValue, misValue);
                this.IsWorkBookOpened  = false;
                this.IsWorkSheetOpened = false;
            }

            if (xlApp != null && this.IsAppOpened)
            {
                xlApp.Quit();
                this.IsAppOpened = false;
            }

            // close the object
            this.ClearObject();
        }
Ejemplo n.º 18
0
 public GenerarExcel(M_Eno m, string titulo)
 {
     this.m      = m;
     this.titulo = titulo;
     excel       = new Excel.Application();
     libro       = (Excel._Workbook)excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
     hoja        = (Excel._Worksheet)libro.Worksheets.Add();
     hoja.Name   = "Resultado Del ENO";
     ((Excel.Worksheet)excel.ActiveWorkbook.Sheets["Hoja1"]).Delete();
     generarDocumento(ref hoja);
     if (System.IO.File.Exists(Environment.CurrentDirectory + @"Result.xlsx"))
     {
         libro.Save();
     }
     else
     {
         libro.SaveAs(Environment.CurrentDirectory + @"\Result.xlsx");
     }
     libro.Close();
     releaseObject(libro);
     excel.UserControl = false;
     excel.Quit();
     releaseObject(excel);
 }
Ejemplo n.º 19
0
        public bool AddToSheet(DataTable dt)
        {
            Excel._Workbook workbook = excelApp.Workbooks.Open(filename);
            excelApp.Visible       = false;
            excelApp.DisplayAlerts = false;
            workbook.Worksheets.Add();
            int count = workbook.Worksheets.Count;

            Excel._Worksheet sheet = workbook.Worksheets.get_Item(1);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sheet.Cells[1, i + 1] = dt.Columns[i].Caption;
            }
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    sheet.Cells[j + 2, i + 1] = dt.Rows[j].Field <object>(i);
                }
            }
            workbook.Save();
            workbook.Close();
            return(true);
        }
Ejemplo n.º 20
0
        static void Main(string[] args)
        {
            // set english culture (for english function names and . decimal)
            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");

            // list seperator is now , Trying to change it to ; doesnt work :(
            // System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator = ";";

            // show manual
            bool showMan = false;

            // check pipe
            String pipedText = "";
            bool isKeyAvailable;
            bool piped = false;
            try { isKeyAvailable = System.Console.KeyAvailable; }
            catch { pipedText = System.Console.In.ReadToEnd(); piped = true; }

            // if no args or pipe, show manual
            if (piped == false && args.Length == 0) showMan = true;

            // set default values
            string infile = "";
            string paste = "";
            List<string> macro = new List<string>();
            int[] cellA = new int[2] { 0, 1 };
            int[] cellB = new int[2] { 0, 1 };
            int[] outCellA = new int[2] { 0, 1 };
            int[] outCellB = new int[2] { 0, 0 };
            string sheet = "";
            string active = "";

            // warnings are off by default, since excel will warn about ANYTHING, which interupts the program and leads to errors.
            // f.eks. log charts will make the program fail, when they are given blank data in the step before new data is provided.
            bool warning = false;

            // save on exit
            bool save = true;

            // convert error codes to text in output
            bool outErr = true;

            // paste as text
            bool asText = false;

            // blehh..
            string errLine = "--------------------------------------------------------------------------------";

            // tab as default output space character
            string cellSpacer = "\t";

            // all charts that should be saved
            List<string> charts = new List<string>();

            // check input arguments
            int iarg = 0;
            for (int i = 0; i < args.Length;i++ )
            {
                if (args[i].StartsWith("-")) {

                    // show manual
                    if (args[i] == "-help" || args[i] == "--help" || args[i] == "-?") showMan = true;

                    // specify macro to run
                    if (args[i] == "-m") {
                      try {
                          macro.Add(args[i + 1]);
                        i++;
                      }
                      catch {
                        Error("No macro name given for -m.",1);
                      }
                    }
                    // paste input as text?
                    if (args[i] == "-t") asText = true;
                    // dont save
                    if (args[i] == "-n") save = false;
                    // hide warnings
                    if (args[i] == "-w") warning = true;
                    // set space character
                    if (args[i] == "-b")
                    {
                        cellSpacer = " ";
                        if (args.Length > i+1)
                            if (args[i + 1].Length == 1)
                            {
                                cellSpacer = args[i + 1];
                                i++;
                            }
                    }
                    // set paste sheet
                    if (args[i] == "-p")
                    {
                        try
                        {
                            active = args[i + 1];
                            i++;
                        }
                        catch
                        {
                            Error("No paste name given for -p.", 1);
                        }
                    }
                    // set output sheet
                    if (args[i] == "-s")
                    {
                        try
                        {
                            sheet = args[i + 1];
                            i++;
                        }
                        catch
                        {
                            Error("No sheet name given for -s.", 1);
                        }
                    }
                    // blank errors
                    if (args[i] == "-#") outErr = false;
                }

                else {
                    // excel file
                    if (iarg == 0) infile = args[i];
                    // paste file
                    else if (iarg == 1 && !piped)
                    {
                        paste = args[i];
                        if (paste == "~" || paste == "")
                        {
                            paste = "";
                            iarg++; iarg++;
                        }
                    }
                    // input cell ref
                    else if (iarg == 2)
                    {
                        string[] cellArr = args[i].Split(':');
                        if (cellArr.Length == 1) {
                             cellA = ExcelCellRef(cellArr[0]);
                        }
                        else {
                            cellA = ExcelCellRef(cellArr[0]);
                            cellB = ExcelCellRef(cellArr[1]);
                            iarg++;
                        }
                    }

                    else if (iarg == 3)
                    {
                        cellB = ExcelCellRef(args[i]);
                    }
                    // output cell ref
                    else if (iarg == 4)
                    {
                        string[] cellArr = args[i].Split(':');
                        if (cellArr.Length == 1)
                        {
                           outCellA = ExcelCellRef(cellArr[0]);
                        }
                        else
                        {
                            outCellA = ExcelCellRef(cellArr[0]);
                            outCellB = ExcelCellRef(cellArr[1]);
                            iarg++;
                        }
                    }
                    else if (iarg == 5)
                    {
                        outCellB = ExcelCellRef(args[i]);
                    }

                    // output charts
                    else if (iarg > 5)
                    {
                        charts.Add(args[i]);
                    }

                    iarg++;
                }
            }

            // Print header
            if (showMan)
            {
                Print(@"Usage: excel [OPTIONS] ExcelFile PasteFile Cell1 Cell2 OutCell1 OutCell2 [Chart1 [Chart2 ..]]
               or: excel =FORMULA

            Opens ExcelFile and places the contents of PasteFile from position given.
            Position is the range between Cell1 and Cell2. Unused cells are cleared.
            Echos all filled rows in the range between OutCell1 and OutCell2.
            Any charts named will be saved to <ExcelFile_ChartN>.png.
            'Sheet.ChartN' can be used if multiple charts has the same name.

            -p PasteSheet   Select the sheet that should be pasted to.
            -s OutSheet     Select the sheet that should be outputted.
            -m Macro        Run macro after paste. If -m is used multiples
                    times, more than 1 macro can be executed.
            -n              Do not save workbook
            -w              display Excel dialogs. Default is to surpress.
            -#              Replace errors with blanks in output
            -b [CHAR]       Set cell-spacing character in output to
                    'space' or 'CHAR' (default is 'tab')
            -t              Insert PasteFile as text instead of numbers

            If '~' is specified as PasteFile no file is loaded and Cell1 and Cell2
            should not be specified.

            Version 1.0. Report bugs to <*****@*****.**>");
                Environment.Exit(0);
            }

            if (infile.StartsWith("="))
            {
                string result = "";
                try
                {
                    result = ExcelMath.Calc(infile);
                }
                catch (Exception ex) {

                    Console.Error.WriteLine("Error: " + ex.Message);
            #if DEBUG
                    Console.ReadKey();
            #endif
                    Environment.Exit(1);
                }

                Print(result);
            #if DEBUG
                Console.ReadKey();
            #endif
                Environment.Exit(0);
            }

            // open file
            if (piped == false && infile == "") Error("No file given.", 1);
            System.IO.TextReader stream = new StringReader(pipedText);
            if (!piped && paste.Length > 0)
            {
                try { stream = new StreamReader(paste); }
                catch (Exception e) { Error("Unable to open file: " + paste + "\n\n"+errLine+"\n\n"+e.ToString(), 1); }
            }

            // number of lines and columns
            int N = 0;
            int C = 0;

            // input data, as numbers and text. only one will be used
            double[,] cells = null;
            string[,] sCells = null;

            // if pasted text
            if (paste.Length > 0)
            {
                String line;
                List<string[]> strings = new List<string[]>();

                // trim all lines and split between words
                while ((line = stream.ReadLine()) != null)
                {
                    line = line.Replace(",", " ");
                    line = line.Replace("\t", " ");
                    line = line.Trim();
                    line = System.Text.RegularExpressions.Regex.Replace(line, @"\s+", " ");
                    if (asText)
                    {
                        //if (line.Length > 0)
                            strings.Add(line.Split(' '));
                    }
                    else if (line.Length > 0 && !line.StartsWith("#"))
                        strings.Add(line.Split(' '));
                }

                // set number of rows and columns
                N = strings.Count();
                C = 0;
                foreach (string[] str in strings) if (str.Length > C) C = str.Length;

                // convert input data to a format the Excel-interop understands: var[,]
                if (asText) sCells = new string[N, C];
                else cells = new double[N, C];

                // parse all cells and add to array
                for (int i = 0; i < N; i++)
                {
                    for (int j = 0; j < strings[i].Length; j++)
                    {
                        try
                        {
                            if (asText) sCells[i, j] = strings[i][j];
                            else cells[i, j] = double.Parse(strings[i][j]);
                        }
                        catch (Exception e) { Error("Unable to parse number in paste file, line " + (i + 1) + ", column " + (j + 1) + ":\n" + strings[i][j] + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
                    }
                }
            }

            Excel.Range startCell;
            Excel.Range endCell;

            try
            {
                // open excel app
                oXL = new Excel.Application();

                if (!warning) oXL.DisplayAlerts = false;

                try
                {
                    // try to open the selected excel file
                    // we turn of errors, since excel prompts for macro-enabled files and other things
                    oXL.DisplayAlerts = false;
                    // we need the absolute file path, since excel defaults to the user home dir, not the current working dir :S
                    oWB = oXL.Workbooks.Open(Path.GetFullPath(infile));
                    // turn back on warnings if wanted
                    if (warning) oXL.DisplayAlerts = true;
                }
                catch (Exception e) { throw new System.Exception("Unable to open file: " + Path.GetFullPath(infile) + "\n\n" + errLine + "\n\n" + e.ToString()); }

                // set the active sheet
                if (active.Length > 0)
                {
                    try
                    {
                        oSheet = (Excel._Worksheet)oWB.Sheets[active];
                    }
                    catch (Exception e) { throw new System.Exception("Unable to select worksheet: " + active + "\n\n" + errLine + "\n\n" + e.ToString()); }
                }
                else
                    oSheet = (Excel._Worksheet)oWB.Worksheets[1];

                // insert data
                if (paste.Length > 0)
                {
                    // if only columns are specified, find the amount of rows used
                    if (cellA[0] == 0 && cellB[0] == 0)
                    {
                        string cell = GetExcelColumnName(cellA[1]) + ":" + GetExcelColumnName(cellB[1]);
                        Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
                        cellA[0] = 1;
                        cellB[0] = r.Rows.Count;
                    }

                    // select and paste values
                    try
                    {
                        startCell = (Excel.Range)oSheet.Cells[cellA[0], cellA[1]];
                        endCell = (Excel.Range)oSheet.Cells[cellB[0], cellB[1]];
                        oSheet.get_Range(startCell, endCell).Value = null;

                        endCell = (Excel.Range)oSheet.Cells[cellA[0] + N - 1, cellA[1] + C - 1];

                        if (asText) oSheet.get_Range(startCell, endCell).Value2 = sCells;
                        else oSheet.get_Range(startCell, endCell).Value2 = cells;
                    }
                    catch (Exception e)
                    {
                        string inputCell = GetExcelColumnName(cellA[1]) + cellA[0] + ":" + GetExcelColumnName(cellB[1]) + cellB[0];
                        throw new System.Exception("Unable to select input cells:\n\n         " + inputCell + "\n\n" + errLine + "\n\n" + e.ToString());
                    }
                }

                // run macro
                for (int i = 0; i < macro.Count; i++)
                {
                    try
                    {
                        oXL.Run(macro[i]);
                    }
                    catch (Exception e) { throw new System.Exception("Unable to run macro: " + macro[i] + "\n\n" + errLine + "\n\n" + e.ToString()); }
                }

                // force workbook refresh
                oXL.Calculate();

                // go to result sheet
                if (sheet.Length > 0)
                {
                    try
                    {
                        oSheet = (Excel._Worksheet)oWB.Sheets[sheet];
                    }
                    catch (Exception e) { throw new System.Exception("Unable to select output sheet:" + sheet + "\n\n" + errLine + "\n\n" + e.ToString()); }
                }

                // save charts

                foreach (Excel.Worksheet cSheet in oWB.Worksheets)
                {
                    // loop trough all charts

                    Excel.ChartObjects xlCharts = (Excel.ChartObjects)cSheet.ChartObjects(Type.Missing);
                    for (int i = 1; i <= xlCharts.Count; i++)
                    {

                        Excel.ChartObject oChart = (Excel.ChartObject)xlCharts.Item(i);
                        Excel.Chart chart = oChart.Chart;

                        string chartName = "";
                        if (charts.Contains(cSheet.Name + "." + oChart.Name)) chartName = cSheet.Name + "." + oChart.Name;
                        else if (charts.Contains(oChart.Name)) chartName = oChart.Name;

                        // if chart is specified for output, save it
                        if (chartName.Length > 0)
                        {
                            int id = charts.FindIndex(s => s == chartName);

                            charts.RemoveAt(id);

                            try
                            {
                                // we need full path name again.. excel defaults to user home dir...
                                string saveas = Path.GetFullPath(infile);
                                saveas = Path.GetDirectoryName(saveas) + "\\" + Path.GetFileNameWithoutExtension(saveas);
                                saveas = saveas + "_" + chartName + ".png";
                                chart.Export(saveas, "PNG");
                            }
                            catch (Exception e) { throw new System.Exception("Unable to save chart '" + chartName + "':\n\n" + errLine + "\n\n" + e.ToString()); }
                        }
                    }
                }

                // if any charts was not found; throw an error.
                if (charts.Count > 0)
                {
                    string list = "";
                    foreach (string s in charts) list += s + ", ";
                    throw new Exception("Unable to find chart(s): " + list);
                }

                // if only columns are specified, find amount of rows to use
                if (outCellA[0] == 0 && outCellB[0] == 0)
                {

                    if (outCellB[1] == 0) outCellB[1] = oSheet.UsedRange.Columns.Count;

                    string cell = GetExcelColumnName(outCellA[1]) + ":" + GetExcelColumnName(outCellB[1]);
                    Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
                    outCellA[0] = 1;
                    outCellB[0] = r.Rows.Count;
                }

                // select the output cell range
                try
                {
                    startCell = (Excel.Range)oSheet.Cells[outCellA[0], outCellA[1]];
                    endCell = (Excel.Range)oSheet.Cells[outCellB[0], outCellB[1]];
                }
                catch (Exception e)
                {
                    string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
                    throw new System.Exception("Unable to select output cells:\n            " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
                }

                // get output from selected cells
                object[,] arr = null;
                try
                {
                    Excel.Range r = (Excel.Range)oSheet.get_Range(startCell, endCell);
                    // if only 1 cell is selected, excel will return an object instead of object array!
                    if (r.Cells.Count == 1)
                    {
                        arr = new object[2, 2];
                        arr[1, 1] = r.Cells.Value2;
                    }
                    else arr = r.Cells.Value2 as object[,];
                }
                catch (Exception e)
                {
                    string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
                    throw new System.Exception("Invalid OutCells given. Unable to retrieve data:\n            " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
                }

                List<string> results = new List<string>();
                int last = 0;
                N = outCellB[0] - outCellA[0] + 1;
                C = outCellB[1] - outCellA[1] + 1;

                // loop trough output rows
                for (int i = 1; i <= N; i++)
                {
                    // loop trough output columns
                    string s = "";
                    for (int j = 1; j <= C; j++)
                    {
                        // check if cell contains an error
                        if (arr[i, j] is Int32)
                        {
                            if (outErr)
                            {
                                int eCode = (int)arr[i, j];
                                string e = "";

                                if (eCode == -2146826281) e = "#DIV/0!";
                                else if (eCode == -2146826246) e = "#N/A";
                                else if (eCode == -2146826259) e = "#NAME?";
                                else if (eCode == -2146826288) e = "#NULL!";
                                else if (eCode == -2146826252) e = "#NUM!";
                                else if (eCode == -2146826265) e = "#REF!";
                                else if (eCode == -2146826273) e = "#VALUE!";
                                // no more error codes exists (?) as of 2013.. But to be sure / support future ones:
                                else e = "#ERR" + eCode.ToString();

                                s = s + e + " " + cellSpacer;
                            }
                            else s = s + " " + cellSpacer;
                        }
                        else if (arr[i, j] != null) s = s + arr[i, j].ToString() + cellSpacer;
                        else s = s + " " + cellSpacer;
                    }
                    // remove cellspacer from last column
                    if (C > 0) results.Add(s.Remove(s.Length - 1).TrimEnd());
                    // record last row column with content
                    if (s.Replace(cellSpacer, " ").TrimEnd().Length > 0) last = results.Count();
                }

                // write output to console
                for (int i = 0; i < last; i++)
                    Console.WriteLine(results[i]);

                // save file
                if (save)
                {
                    // if macros are enabled, excel would prompt about saving
                    oXL.DisplayAlerts = false;
                    oWB.Save();
                }

            }

                // catch any exception
            catch (Exception theException)
            {
                Error(errLine + "\n  Error: " + theException.Message, 1);
            }

            finally
            {

                // clean up and exit
                CleanUp();
            }

            #if DEBUG
                Console.ReadKey();
            #endif
        }
Ejemplo n.º 21
0
        static void Main(string[] args)
        {
            // set english culture (for english function names and . decimal)
            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");

            // list seperator is now , Trying to change it to ; doesnt work :(
            // System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator = ";";

            // show manual
            bool showMan = false;

            // check pipe
            String pipedText = "";
            bool   isKeyAvailable;
            bool   piped = false;

            try { isKeyAvailable = System.Console.KeyAvailable; }
            catch { pipedText = System.Console.In.ReadToEnd(); piped = true; }

            // if no args or pipe, show manual
            if (piped == false && args.Length == 0)
            {
                showMan = true;
            }

            // set default values
            string        infile = "";
            string        paste  = "";
            List <string> macro  = new List <string>();

            int[] cellA = new int[2] {
                0, 1
            };
            int[] cellB = new int[2] {
                0, 1
            };
            int[] outCellA = new int[2] {
                0, 1
            };
            int[] outCellB = new int[2] {
                0, 0
            };
            string sheet  = "";
            string active = "";

            // warnings are off by default, since excel will warn about ANYTHING, which interupts the program and leads to errors.
            // f.eks. log charts will make the program fail, when they are given blank data in the step before new data is provided.
            bool warning = false;

            // save on exit
            bool save = true;

            // convert error codes to text in output
            bool outErr = true;

            // paste as text
            bool asText = false;


            // blehh..
            string errLine = "--------------------------------------------------------------------------------";

            // tab as default output space character
            string cellSpacer = "\t";


            // all charts that should be saved
            List <string> charts = new List <string>();


            // check input arguments
            int iarg = 0;

            for (int i = 0; i < args.Length; i++)
            {
                if (args[i].StartsWith("-"))
                {
                    // show manual
                    if (args[i] == "-help" || args[i] == "--help" || args[i] == "-?")
                    {
                        showMan = true;
                    }

                    // specify macro to run
                    if (args[i] == "-m")
                    {
                        try {
                            macro.Add(args[i + 1]);
                            i++;
                        }
                        catch {
                            Error("No macro name given for -m.", 1);
                        }
                    }
                    // paste input as text?
                    if (args[i] == "-t")
                    {
                        asText = true;
                    }
                    // dont save
                    if (args[i] == "-n")
                    {
                        save = false;
                    }
                    // hide warnings
                    if (args[i] == "-w")
                    {
                        warning = true;
                    }
                    // set space character
                    if (args[i] == "-b")
                    {
                        cellSpacer = " ";
                        if (args.Length > i + 1)
                        {
                            if (args[i + 1].Length == 1)
                            {
                                cellSpacer = args[i + 1];
                                i++;
                            }
                        }
                    }
                    // set paste sheet
                    if (args[i] == "-p")
                    {
                        try
                        {
                            active = args[i + 1];
                            i++;
                        }
                        catch
                        {
                            Error("No paste name given for -p.", 1);
                        }
                    }
                    // set output sheet
                    if (args[i] == "-s")
                    {
                        try
                        {
                            sheet = args[i + 1];
                            i++;
                        }
                        catch
                        {
                            Error("No sheet name given for -s.", 1);
                        }
                    }
                    // blank errors
                    if (args[i] == "-#")
                    {
                        outErr = false;
                    }
                }

                else
                {
                    // excel file
                    if (iarg == 0)
                    {
                        infile = args[i];
                    }
                    // paste file
                    else if (iarg == 1 && !piped)
                    {
                        paste = args[i];
                        if (paste == "~" || paste == "")
                        {
                            paste = "";
                            iarg++; iarg++;
                        }
                    }
                    // input cell ref
                    else if (iarg == 2)
                    {
                        string[] cellArr = args[i].Split(':');
                        if (cellArr.Length == 1)
                        {
                            cellA = ExcelCellRef(cellArr[0]);
                        }
                        else
                        {
                            cellA = ExcelCellRef(cellArr[0]);
                            cellB = ExcelCellRef(cellArr[1]);
                            iarg++;
                        }
                    }

                    else if (iarg == 3)
                    {
                        cellB = ExcelCellRef(args[i]);
                    }
                    // output cell ref
                    else if (iarg == 4)
                    {
                        string[] cellArr = args[i].Split(':');
                        if (cellArr.Length == 1)
                        {
                            outCellA = ExcelCellRef(cellArr[0]);
                        }
                        else
                        {
                            outCellA = ExcelCellRef(cellArr[0]);
                            outCellB = ExcelCellRef(cellArr[1]);
                            iarg++;
                        }
                    }
                    else if (iarg == 5)
                    {
                        outCellB = ExcelCellRef(args[i]);
                    }

                    // output charts
                    else if (iarg > 5)
                    {
                        charts.Add(args[i]);
                    }

                    iarg++;
                }
            }

            // Print header
            if (showMan)
            {
                Print(@"Usage: excel [OPTIONS] ExcelFile PasteFile Cell1 Cell2 OutCell1 OutCell2 [Chart1 [Chart2 ..]]
   or: excel =FORMULA

Opens ExcelFile and places the contents of PasteFile from position given.
Position is the range between Cell1 and Cell2. Unused cells are cleared.
Echos all filled rows in the range between OutCell1 and OutCell2.
Any charts named will be saved to <ExcelFile_ChartN>.png.
'Sheet.ChartN' can be used if multiple charts has the same name.

    -p PasteSheet   Select the sheet that should be pasted to.
    -s OutSheet     Select the sheet that should be outputted.
    -m Macro        Run macro after paste. If -m is used multiples
                    times, more than 1 macro can be executed.
    -n              Do not save workbook
    -w              display Excel dialogs. Default is to surpress.
    -#              Replace errors with blanks in output
    -b [CHAR]       Set cell-spacing character in output to 
                    'space' or 'CHAR' (default is 'tab')
    -t              Insert PasteFile as text instead of numbers

If '~' is specified as PasteFile no file is loaded and Cell1 and Cell2 
should not be specified.

Version 1.0. Report bugs to <*****@*****.**>");
                Environment.Exit(0);
            }



            if (infile.StartsWith("="))
            {
                string result = "";
                try
                {
                    result = ExcelMath.Calc(infile);
                }
                catch (Exception ex) {
                    Console.Error.WriteLine("Error: " + ex.Message);
#if DEBUG
                    Console.ReadKey();
#endif
                    Environment.Exit(1);
                }

                Print(result);
#if DEBUG
                Console.ReadKey();
#endif
                Environment.Exit(0);
            }

            // open file
            if (piped == false && infile == "")
            {
                Error("No file given.", 1);
            }
            System.IO.TextReader stream = new StringReader(pipedText);
            if (!piped && paste.Length > 0)
            {
                try { stream = new StreamReader(paste); }
                catch (Exception e) { Error("Unable to open file: " + paste + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
            }

            // number of lines and columns
            int N = 0;
            int C = 0;

            // input data, as numbers and text. only one will be used
            double[,] cells  = null;
            string[,] sCells = null;


            // if pasted text
            if (paste.Length > 0)
            {
                String          line;
                List <string[]> strings = new List <string[]>();

                // trim all lines and split between words
                while ((line = stream.ReadLine()) != null)
                {
                    line = line.Replace(",", " ");
                    line = line.Replace("\t", " ");
                    line = line.Trim();
                    line = System.Text.RegularExpressions.Regex.Replace(line, @"\s+", " ");
                    if (asText)
                    {
                        //if (line.Length > 0)
                        strings.Add(line.Split(' '));
                    }
                    else if (line.Length > 0 && !line.StartsWith("#"))
                    {
                        strings.Add(line.Split(' '));
                    }
                }

                // set number of rows and columns
                N = strings.Count();
                C = 0;
                foreach (string[] str in strings)
                {
                    if (str.Length > C)
                    {
                        C = str.Length;
                    }
                }

                // convert input data to a format the Excel-interop understands: var[,]
                if (asText)
                {
                    sCells = new string[N, C];
                }
                else
                {
                    cells = new double[N, C];
                }

                // parse all cells and add to array
                for (int i = 0; i < N; i++)
                {
                    for (int j = 0; j < strings[i].Length; j++)
                    {
                        try
                        {
                            if (asText)
                            {
                                sCells[i, j] = strings[i][j];
                            }
                            else
                            {
                                cells[i, j] = double.Parse(strings[i][j]);
                            }
                        }
                        catch (Exception e) { Error("Unable to parse number in paste file, line " + (i + 1) + ", column " + (j + 1) + ":\n" + strings[i][j] + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
                    }
                }
            }


            Excel.Range startCell;
            Excel.Range endCell;

            try
            {
                // open excel app
                oXL = new Excel.Application();

                if (!warning)
                {
                    oXL.DisplayAlerts = false;
                }

                try
                {
                    // try to open the selected excel file
                    // we turn of errors, since excel prompts for macro-enabled files and other things
                    oXL.DisplayAlerts = false;
                    // we need the absolute file path, since excel defaults to the user home dir, not the current working dir :S
                    oWB = oXL.Workbooks.Open(Path.GetFullPath(infile));
                    // turn back on warnings if wanted
                    if (warning)
                    {
                        oXL.DisplayAlerts = true;
                    }
                }
                catch (Exception e) { throw new System.Exception("Unable to open file: " + Path.GetFullPath(infile) + "\n\n" + errLine + "\n\n" + e.ToString()); }

                // set the active sheet
                if (active.Length > 0)
                {
                    try
                    {
                        oSheet = (Excel._Worksheet)oWB.Sheets[active];
                    }
                    catch (Exception e) { throw new System.Exception("Unable to select worksheet: " + active + "\n\n" + errLine + "\n\n" + e.ToString()); }
                }
                else
                {
                    oSheet = (Excel._Worksheet)oWB.Worksheets[1];
                }


                // insert data
                if (paste.Length > 0)
                {
                    // if only columns are specified, find the amount of rows used
                    if (cellA[0] == 0 && cellB[0] == 0)
                    {
                        string      cell = GetExcelColumnName(cellA[1]) + ":" + GetExcelColumnName(cellB[1]);
                        Excel.Range r    = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
                        cellA[0] = 1;
                        cellB[0] = r.Rows.Count;
                    }

                    // select and paste values
                    try
                    {
                        startCell = (Excel.Range)oSheet.Cells[cellA[0], cellA[1]];
                        endCell   = (Excel.Range)oSheet.Cells[cellB[0], cellB[1]];
                        oSheet.get_Range(startCell, endCell).Value = null;

                        endCell = (Excel.Range)oSheet.Cells[cellA[0] + N - 1, cellA[1] + C - 1];

                        if (asText)
                        {
                            oSheet.get_Range(startCell, endCell).Value2 = sCells;
                        }
                        else
                        {
                            oSheet.get_Range(startCell, endCell).Value2 = cells;
                        }
                    }
                    catch (Exception e)
                    {
                        string inputCell = GetExcelColumnName(cellA[1]) + cellA[0] + ":" + GetExcelColumnName(cellB[1]) + cellB[0];
                        throw new System.Exception("Unable to select input cells:\n\n         " + inputCell + "\n\n" + errLine + "\n\n" + e.ToString());
                    }
                }

                // run macro
                for (int i = 0; i < macro.Count; i++)
                {
                    try
                    {
                        oXL.Run(macro[i]);
                    }
                    catch (Exception e) { throw new System.Exception("Unable to run macro: " + macro[i] + "\n\n" + errLine + "\n\n" + e.ToString()); }
                }

                // force workbook refresh
                oXL.Calculate();

                // go to result sheet
                if (sheet.Length > 0)
                {
                    try
                    {
                        oSheet = (Excel._Worksheet)oWB.Sheets[sheet];
                    }
                    catch (Exception e) { throw new System.Exception("Unable to select output sheet:" + sheet + "\n\n" + errLine + "\n\n" + e.ToString()); }
                }

                // save charts

                foreach (Excel.Worksheet cSheet in oWB.Worksheets)
                {
                    // loop trough all charts

                    Excel.ChartObjects xlCharts = (Excel.ChartObjects)cSheet.ChartObjects(Type.Missing);
                    for (int i = 1; i <= xlCharts.Count; i++)
                    {
                        Excel.ChartObject oChart = (Excel.ChartObject)xlCharts.Item(i);
                        Excel.Chart       chart  = oChart.Chart;

                        string chartName = "";
                        if (charts.Contains(cSheet.Name + "." + oChart.Name))
                        {
                            chartName = cSheet.Name + "." + oChart.Name;
                        }
                        else if (charts.Contains(oChart.Name))
                        {
                            chartName = oChart.Name;
                        }

                        // if chart is specified for output, save it
                        if (chartName.Length > 0)
                        {
                            int id = charts.FindIndex(s => s == chartName);

                            charts.RemoveAt(id);

                            try
                            {
                                // we need full path name again.. excel defaults to user home dir...
                                string saveas = Path.GetFullPath(infile);
                                saveas = Path.GetDirectoryName(saveas) + "\\" + Path.GetFileNameWithoutExtension(saveas);
                                saveas = saveas + "_" + chartName + ".png";
                                chart.Export(saveas, "PNG");
                            }
                            catch (Exception e) { throw new System.Exception("Unable to save chart '" + chartName + "':\n\n" + errLine + "\n\n" + e.ToString()); }
                        }
                    }
                }

                // if any charts was not found; throw an error.
                if (charts.Count > 0)
                {
                    string list = "";
                    foreach (string s in charts)
                    {
                        list += s + ", ";
                    }
                    throw new Exception("Unable to find chart(s): " + list);
                }

                // if only columns are specified, find amount of rows to use
                if (outCellA[0] == 0 && outCellB[0] == 0)
                {
                    if (outCellB[1] == 0)
                    {
                        outCellB[1] = oSheet.UsedRange.Columns.Count;
                    }

                    string      cell = GetExcelColumnName(outCellA[1]) + ":" + GetExcelColumnName(outCellB[1]);
                    Excel.Range r    = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
                    outCellA[0] = 1;
                    outCellB[0] = r.Rows.Count;
                }


                // select the output cell range
                try
                {
                    startCell = (Excel.Range)oSheet.Cells[outCellA[0], outCellA[1]];
                    endCell   = (Excel.Range)oSheet.Cells[outCellB[0], outCellB[1]];
                }
                catch (Exception e)
                {
                    string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
                    throw new System.Exception("Unable to select output cells:\n            " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
                }


                // get output from selected cells
                object[,] arr = null;
                try
                {
                    Excel.Range r = (Excel.Range)oSheet.get_Range(startCell, endCell);
                    // if only 1 cell is selected, excel will return an object instead of object array!
                    if (r.Cells.Count == 1)
                    {
                        arr       = new object[2, 2];
                        arr[1, 1] = r.Cells.Value2;
                    }
                    else
                    {
                        arr = r.Cells.Value2 as object[, ];
                    }
                }
                catch (Exception e)
                {
                    string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
                    throw new System.Exception("Invalid OutCells given. Unable to retrieve data:\n            " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
                }

                List <string> results = new List <string>();
                int           last    = 0;
                N = outCellB[0] - outCellA[0] + 1;
                C = outCellB[1] - outCellA[1] + 1;

                // loop trough output rows
                for (int i = 1; i <= N; i++)
                {
                    // loop trough output columns
                    string s = "";
                    for (int j = 1; j <= C; j++)
                    {
                        // check if cell contains an error
                        if (arr[i, j] is Int32)
                        {
                            if (outErr)
                            {
                                int    eCode = (int)arr[i, j];
                                string e     = "";

                                if (eCode == -2146826281)
                                {
                                    e = "#DIV/0!";
                                }
                                else if (eCode == -2146826246)
                                {
                                    e = "#N/A";
                                }
                                else if (eCode == -2146826259)
                                {
                                    e = "#NAME?";
                                }
                                else if (eCode == -2146826288)
                                {
                                    e = "#NULL!";
                                }
                                else if (eCode == -2146826252)
                                {
                                    e = "#NUM!";
                                }
                                else if (eCode == -2146826265)
                                {
                                    e = "#REF!";
                                }
                                else if (eCode == -2146826273)
                                {
                                    e = "#VALUE!";
                                }
                                // no more error codes exists (?) as of 2013.. But to be sure / support future ones:
                                else
                                {
                                    e = "#ERR" + eCode.ToString();
                                }

                                s = s + e + " " + cellSpacer;
                            }
                            else
                            {
                                s = s + " " + cellSpacer;
                            }
                        }
                        else if (arr[i, j] != null)
                        {
                            s = s + arr[i, j].ToString() + cellSpacer;
                        }
                        else
                        {
                            s = s + " " + cellSpacer;
                        }
                    }
                    // remove cellspacer from last column
                    if (C > 0)
                    {
                        results.Add(s.Remove(s.Length - 1).TrimEnd());
                    }
                    // record last row column with content
                    if (s.Replace(cellSpacer, " ").TrimEnd().Length > 0)
                    {
                        last = results.Count();
                    }
                }

                // write output to console
                for (int i = 0; i < last; i++)
                {
                    Console.WriteLine(results[i]);
                }

                // save file
                if (save)
                {
                    // if macros are enabled, excel would prompt about saving
                    oXL.DisplayAlerts = false;
                    oWB.Save();
                }
            }

            // catch any exception
            catch (Exception theException)
            {
                Error(errLine + "\n  Error: " + theException.Message, 1);
            }

            finally
            {
                // clean up and exit
                CleanUp();
            }


            #if DEBUG
            Console.ReadKey();
            #endif
        }
Ejemplo n.º 22
0
 public void save()
 {
     m_objBook.Save();
 }
Ejemplo n.º 23
0
        public string AddTeam(DiscordUser user, string teamName, string sheetName)
        {
            Excel.Application oXL       = null;
            Excel._Workbook   oWB       = null;
            Excel.Workbooks   workBooks = null;
            Excel._Worksheet  oSheet    = null;
            Excel.Sheets      sheets    = null;
            Excel.Range       oRng      = null;

            string returnString = null;

            try
            {
                isProcessing = true;

                oXL       = new Excel.Application();
                workBooks = oXL.Workbooks;
                oWB       = workBooks.Open(Program.excelFilePath);
                sheets    = oWB.Worksheets;
                oSheet    = (Excel._Worksheet)sheets[sheetName];

                bool   emptyCellFound = false;
                bool   teamNameFound  = false;
                bool   hasTeam        = false;
                int    count          = 0;
                string ownerTag       = user.Username + " " + user.Mention;
                while (!emptyCellFound && !teamNameFound && !hasTeam)
                {
                    if (string.Equals(oSheet.Cells[startRow + (count * 2), 1].Value, null))
                    {
                        emptyCellFound = true;
                    }
                    else if (string.Equals(oSheet.Cells[startRow + (count * 2), 1].Value, teamName))
                    {
                        teamNameFound = true;
                    }
                    else if (string.Equals(oSheet.Cells[startRow + (count * 2), 2].Value, ownerTag))
                    {
                        hasTeam = true;
                    }
                    else
                    {
                        count++;
                    }
                }

                if (teamNameFound)
                {
                    returnString = teamName + " has already been registered!";
                }
                else if (hasTeam)
                {
                    returnString = user.Mention + " You have already registered a team!";
                }
                else if (emptyCellFound)
                {
                    oSheet.Cells[startRow + (count * 2), 1].Value = teamName;
                    oRng = oSheet.get_Range("A2", "N2");
                    oRng.EntireColumn.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                    oRng.EntireColumn.Font.Bold         = true;
                    oRng.EntireColumn.Font.Size         = 15;
                    oRng.EntireColumn.AutoFit();

                    oRng = oSheet.Rows[startRow + (count * 2)];
                    Excel.Borders borders = oRng.Borders;
                    borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle       = Excel.XlLineStyle.xlContinuous;
                    borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle         = Excel.XlLineStyle.xlLineStyleNone;
                    borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle          = Excel.XlLineStyle.xlLineStyleNone;
                    borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle        = Excel.XlLineStyle.xlLineStyleNone;
                    borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
                    borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle   = Excel.XlLineStyle.xlLineStyleNone;
                    borders[Excel.XlBordersIndex.xlEdgeBottom].Weight          = 4d;

                    oRng = oSheet.Cells[startRow + (count * 2), 1];
                    oRng.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                    oRng.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight    = 3d;

                    oSheet.Cells[startRow + (count * 2), 2].Value = ownerTag;

                    returnString = user.Mention + " " + teamName + " registered!";

                    oWB.Save();
                }
            }
            catch (Exception ex)
            {
                string exceptionString = ex.ToString();
                if (exceptionString.Contains("Microsoft.Office.Interop.Excel.Workbooks.Open"))
                {
                    return(user.Mention + " I am processing another operation, please wait.");
                }
                return("Error: Something went wrong, please contact an admin.");
            }
            finally
            {
                if (oWB != null)
                {
                    foreach (Excel.Workbook _workbook in oXL.Workbooks)
                    {
                        _workbook.Close();
                    }

                    oXL.Quit();
                    oXL = null;
                    var process = System.Diagnostics.Process.GetProcessesByName("Excel");
                    foreach (var p in process)
                    {
                        if (!string.IsNullOrEmpty(p.ProcessName))
                        {
                            try
                            {
                                p.Kill();
                            }
                            catch { }
                        }
                    }
                }
            }

            switch (sheetName)
            {
            case "Overwatch":
                teamCountOverwatch++;
                break;
            }

            isProcessing = false;

            return(returnString);
        }
Ejemplo n.º 24
0
        public string ChangeTeamName(DiscordUser user, string teamName, string sheetName, out string oldName)
        {
            Excel.Application oXL       = null;
            Excel._Workbook   oWB       = null;
            Excel.Workbooks   workBooks = null;
            Excel._Worksheet  oSheet    = null;
            Excel.Sheets      sheets    = null;

            bool   teamNameFound = false;
            int    teamRow       = 0;
            string returnString  = null;
            string ownerTag      = user.Username + " " + user.Mention;
            string oldTeamName   = null;

            try
            {
                isProcessing = true;

                oXL       = new Excel.Application();
                workBooks = oXL.Workbooks;
                oWB       = workBooks.Open(Program.excelFilePath);
                sheets    = oWB.Worksheets;
                oSheet    = (Excel._Worksheet)sheets[sheetName];

                for (int i = 0; i < teamCountOverwatch; i++)
                {
                    if (ownerTag.Equals(oSheet.Cells[startRow + (i * 2), 2].Value))
                    {
                        teamNameFound = true;
                        teamRow       = startRow + (i * 2);
                        break;
                    }
                }

                if (!teamNameFound)
                {
                    returnString = user.Mention + " You do not own a team!";
                }
                else
                {
                    oldTeamName = oSheet.Cells[teamRow, 1].Value;
                    oSheet.Cells[teamRow, 1].Value = teamName;
                    oWB.Save();

                    returnString = user.Mention + " Team's name changed from " + '"' + oldTeamName + '"' + " to " + '"' + teamName + '"';
                }
            }
            catch (Exception ex)
            {
                string exceptionString = ex.ToString();
                if (exceptionString.Contains("Microsoft.Office.Interop.Excel.Workbooks.Open"))
                {
                    oldName = null;
                    return(user.Mention + " I am processing another operation, please wait.");
                }
                oldName = null;
                return("Error: Something went wrong, please contact an admin.");
            }
            finally
            {
                if (oWB != null)
                {
                    foreach (Excel.Workbook _workbook in oXL.Workbooks)
                    {
                        _workbook.Close();
                    }

                    oXL.Quit();
                    oXL = null;
                    var process = System.Diagnostics.Process.GetProcessesByName("Excel");
                    foreach (var p in process)
                    {
                        if (!string.IsNullOrEmpty(p.ProcessName))
                        {
                            try
                            {
                                p.Kill();
                            }
                            catch { }
                        }
                    }
                }

                isProcessing = false;
            }

            oldName = oldTeamName;
            return(returnString);
        }
Ejemplo n.º 25
0
        public string AddPlayerToTeam(DiscordUser user, DiscordUser userToAdd, string sheetName)
        {
            Excel.Application oXL       = null;
            Excel._Workbook   oWB       = null;
            Excel.Workbooks   workBooks = null;
            Excel._Worksheet  oSheet    = null;
            Excel.Sheets      sheets    = null;
            Excel.Range       oRng      = null;

            string returnString = null;

            try
            {
                isProcessing = true;

                oXL       = new Excel.Application();
                workBooks = oXL.Workbooks;
                oWB       = workBooks.Open(Program.excelFilePath);
                sheets    = oWB.Worksheets;
                oSheet    = (Excel._Worksheet)sheets[sheetName];

                bool   emptyCellFound = false;
                bool   teamNameFound  = false;
                bool   userFound      = false;
                int    teamRow        = 0;
                string teamName       = null;
                string ownerTag       = user.Username + " " + user.Mention;
                string addedUserTag   = userToAdd.Username + " " + userToAdd.Mention;

                for (int i = 0; i < teamCountOverwatch; i++)
                {
                    if (ownerTag.Equals(oSheet.Cells[startRow + (i * 2), 2].Value))
                    {
                        teamNameFound = true;
                        teamRow       = startRow + (i * 2);
                        teamName      = oSheet.Cells[startRow + (i * 2), 1].Value;
                        oRng          = oSheet.Rows[teamRow];
                        break;
                    }
                }
                if (!teamNameFound || teamName == null)
                {
                    returnString = "Your team was not found!";
                }
                else if (teamNameFound)
                {
                    int foundColumn = 0;
                    for (int i = 1; i < 100; i++)
                    {
                        if (string.Equals(oSheet.Cells[teamRow, i].Value, null))
                        {
                            emptyCellFound = true;
                            foundColumn    = i;
                            break;
                        }
                        else if (string.Equals(oSheet.Cells[teamRow, i].Value, addedUserTag))
                        {
                            userFound = true;
                            break;
                        }
                    }
                    if (userFound)
                    {
                        returnString = userToAdd.Mention + " is already in this team!";
                    }
                    else if (emptyCellFound)
                    {
                        oSheet.Cells[teamRow, foundColumn].Value = addedUserTag;
                        oWB.Save();
                        returnString = userToAdd.Mention + " added to team " + teamName;
                    }
                }
            }
            catch (Exception ex)
            {
                string exceptionString = ex.ToString();
                if (exceptionString.Contains("Microsoft.Office.Interop.Excel.Workbooks.Open"))
                {
                    return(user.Mention + " I am processing another operation, please wait.");
                }
                return("Error: Something went wrong, please contact an admin.");
            }
            finally
            {
                if (oWB != null)
                {
                    foreach (Excel.Workbook _workbook in oXL.Workbooks)
                    {
                        _workbook.Close();
                    }

                    oXL.Quit();
                    oXL = null;
                    var process = System.Diagnostics.Process.GetProcessesByName("Excel");
                    foreach (var p in process)
                    {
                        if (!string.IsNullOrEmpty(p.ProcessName))
                        {
                            try
                            {
                                p.Kill();
                            }
                            catch { }
                        }
                    }
                }

                isProcessing = false;
            }

            return(returnString);
        }
Ejemplo n.º 26
0
        static void MainTask(string[] args)
        {
            // display usage if no arguments
            if (args.Length == 0)
            {
                Console.WriteLine(Usage());
                return;
            }

            var xlApp = new Microsoft.Office.Interop.Excel.Application();

            try
            {
                Microsoft.Office.Interop.Excel._Workbook wbk = null;
                Microsoft.Office.Interop.Excel.Range     rng = null;

                xlApp.DisplayAlerts = false;
                xlApp.Visible       = true;
                xlApp.Interactive   = true;

                // no-well established parsearg method
                for (int i = 0; i < args.Length; i++)
                {
                    var cmd = args[i];
                    while (cmd[0] == '-' || cmd[0] == '/')
                    {
                        cmd = cmd.Substring(1);
                    }

                    switch (cmd)
                    {
                    case "xlFileOpen":
                    case "xlFilePath":
                        var openPath = MakeFullPath(args[++i]);
                        Console.WriteLine($"> Open Workbook {openPath}");
                        if (wbk != null)
                        {
                            wbk.Close(SaveChanges: false);
                        }
                        wbk = xlApp.Workbooks.Open(openPath);
                        break;

                    case "xlFileNew":
                        Console.WriteLine($"> New Workbook");
                        if (wbk != null)
                        {
                            wbk.Close(SaveChanges: false);
                        }
                        wbk = xlApp.Workbooks.Add();
                        break;

                    case "xlFileSave":
                        Console.WriteLine($"> Save Workbook");
                        wbk.Save();
                        break;

                    case "xlFileSaveAs":
                        var savePath = MakeFullPath(args[++i]);
                        Console.WriteLine($"> Save Workbook as {savePath}");
                        wbk.SaveAs(savePath);
                        break;

                    case "xlEvalMacro":
                        var macro = args[++i];
                        Console.WriteLine($"> Evaluate macro {macro}");
                        xlApp.Evaluate(macro);
                        break;

                    case "xlRefreshLeftToRight":
                        Console.WriteLine($"> Refresh sheets left to right");
                        foreach (Microsoft.Office.Interop.Excel._Worksheet wsh in wbk.Worksheets)
                        {
                            if (wsh.Visible == XlSheetVisibility.xlSheetVisible)
                            {
                                wsh.Calculate();
                            }
                        }
                        break;

                    case "xlRngGet":
                        var rngGetAddr = args[++i];
                        Console.WriteLine($"> Get Range {rngGetAddr}");
                        rng = xlApp.Range[rngGetAddr];
                        Console.WriteLine(rng.Value);
                        break;

                    case "xlRngSet":
                        var rngSetAddr  = args[++i];
                        var rngSetValue = args[++i];
                        Console.WriteLine($"> Set Range {rngSetAddr} {rngSetValue}");
                        rng       = xlApp.Range[rngSetAddr];
                        rng.Value = rngSetValue;
                        break;

                    case "timeout":
                        i++;
                        break;

                    default:
                        throw new Exception($"> Unexpected command {cmd}");
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Environment.Exit(1);
            }
            finally
            {
                foreach (Microsoft.Office.Interop.Excel._Workbook wbk in xlApp.Workbooks)
                {
                    wbk.Close(SaveChanges: false);
                }
                xlApp.Quit();
                Console.WriteLine("Done.");
            }
        }
Ejemplo n.º 27
0
        /// <summary>
        /// 执行Excel中的宏
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径</param>
        /// <param name="macroName">Excel宏名称</param>
        /// <param name="parameters">Excel宏参数组</param>
        /// <param name="rtnValue">Excel宏返回值</param>
        /// <param name="isShowExcel">执行时是否打开并显示Excel</param>
        public void RunExcelMacro(string excelFilePath, string macroName, object[] parameters, out object rtnValue, bool isShowExcel)
        {
            #region 初始化对象
            Excel.ApplicationClass oExcel = null;
            Excel.Workbooks        oBooks = null;
            Excel._Workbook        oBook  = null;
            #endregion

            try
            {
                #region 检查参数

                // 检查文件是否存在
                if (!File.Exists(excelFilePath))
                {
                    throw new System.Exception(excelFilePath + " 文件不存在");
                }

                // 检查是否输入宏名称
                if (string.IsNullOrEmpty(macroName))
                {
                    throw new System.Exception("请输入宏的名称");
                }

                #endregion

                #region 调用宏处理

                // 缺省参数对象
                object oMissing = System.Reflection.Missing.Value;

                // 根据参数组是否为空,准备参数组对象
                object[] paraObjects;

                if (parameters == null)
                {
                    paraObjects = new object[] { macroName };
                }
                else
                {
                    // 宏参数组长度
                    int paraLength = parameters.Length;

                    paraObjects = new object[paraLength + 1];

                    paraObjects[0] = macroName;
                    for (int i = 0; i < paraLength; i++)
                    {
                        paraObjects[i + 1] = parameters[i];
                    }
                }

                // 创建Excel对象
                oExcel = new Excel.ApplicationClass();

                // 判断是否要求执行时Excel可见
                if (isShowExcel)
                {
                    // 使创建的对象可见
                    oExcel.Visible = true;
                }

                // 获取Workbooks对象
                oBooks = oExcel.Workbooks;

                // 打开指定的Excel文件,赋值Workbook对象
                oBook = oBooks.Open(
                    excelFilePath,
                    oMissing,
                    oMissing,
                    oMissing,
                    oMissing,
                    oMissing,
                    oMissing,
                    oMissing,
                    oMissing,
                    oMissing,
                    oMissing,
                    oMissing,
                    oMissing,
                    oMissing,
                    oMissing
                    );

                // 执行Excel中的宏
                rtnValue = this.RunMacro(oExcel, paraObjects);

                // 保存更改
                oBook.Save();

                // 退出Workbook
                oBook.Close(false, oMissing, oMissing);

                #endregion
            }
            catch (Exception ex)
            {
                #region 异常处理

                throw ex;

                #endregion
            }
            finally
            {
                #region 释放对象

                // 释放Workbook对象
                if (oBook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
                    oBook = null;
                }

                // 释放Workbooks对象
                if (oBooks != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
                    oBooks = null;
                }

                // 关闭Excel,并释放Excel对象
                if (oExcel != null)
                {
                    oExcel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
                    oExcel = null;
                }

                // 调用垃圾回收
                GC.Collect();

                #endregion
            }
        }
Ejemplo n.º 28
0
        public void CopyMacro(string path1, string path2)
        {
            Excel.Application app = new Excel.Application();
            app.Visible = true;

            Excel._Workbook wSource = app.Workbooks.Open(path1), wDestination = app.Workbooks.Open(path2);

            bool IsSourceProtected      = Convert.ToBoolean(wSource.VBProject.Protection);
            bool IsDestinationProtected = Convert.ToBoolean(wDestination.VBProject.Protection);

            if (IsSourceProtected)
            {
                if (IsDestinationProtected)
                {
                    KeySendPassword pasike = new KeySendPassword();
                    pasike.Klucze();
                }
                else
                {
                    KeySendPassword pasik = new KeySendPassword();
                    pasik.KluczS(ref app);
                }
            }
            else
            if (IsDestinationProtected)
            {
                KeySendPassword pasiks = new KeySendPassword();
                pasiks.KluczD();
            }
            Boolean found;

            found = false;
            VBIDE.VBComponent dest;

            foreach (VBIDE.VBComponent source in wSource.VBProject.VBComponents)
            {
                //Sprawdzamy czy nasz source ma jakis kod jezeli nie: koniec.
                if (source.CodeModule.CountOfLines > 0)
                {
                    //Sprawdzamy czy istnieje jakies makro w naszym destini pliku jezeli tak sprawdzamy i porwonujemy jego nazwe jezeli nie : dalej.
                    Hashtable pak = new Hashtable();
                    pak = GetMacros(wDestination);
                    if (pak.Count > 0)
                    {
                        //Sprawdzamy czy dany modul istnieje
                        //I czy jego nazwa jest taka sama jak z sourca jezeli tak to jest kasowana jezeli nie to zostaje.

                        foreach (VBIDE.VBComponent destNew in wDestination.VBProject.VBComponents)
                        {
                            if (destNew.Name == source.CodeModule.Name)
                            {
                                wDestination.VBProject.VBComponents.Remove(destNew);
                                found = false;

                                //Usuwamy ten sam module
                            }
                        }
                    }
                    if (found == false)
                    {
                        dest = wDestination.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
                        dest.CodeModule.AddFromString(source.CodeModule.get_Lines(1, source.CodeModule.CountOfLines));

                        dest.Name = source.Name;
                        wDestination.Save();

                        Marshal.FinalReleaseComObject(dest);
                        Marshal.FinalReleaseComObject(source);
                        dest = null;
                    }
                }
            }

            wSource.Close();
            wDestination.Close();
            Marshal.FinalReleaseComObject(wSource);
            Marshal.FinalReleaseComObject(wDestination);
            app.Quit();
        }
Ejemplo n.º 29
0
        /// <summary>  
        /// 执行Excel中的宏  
        /// </summary>  
        /// <param name="excelFilePath">Excel文件路径</param>  
        /// <param name="macroName">宏名称</param>
        public static void RunExcelMacro(string excelFilePath, string macroName)
        {
            try
            {
                #region 检查输入参数
                //检查文件是否存在  
                if (!File.Exists(excelFilePath))
                {
                    LogUtil.LogMessage(excelFilePath + " 文件不存在");
                    //return;
                }

                // 检查是否输入宏名称  
                if (string.IsNullOrEmpty(macroName))
                {
                    LogUtil.LogMessage("请输入宏的名称");
                    //return;
                }
                #endregion

                #region 调用宏处理

                // 准备打开Excel文件时的缺省参数对象  
                object oMissing = System.Reflection.Missing.Value;

                // 创建Excel对象示例  
                //Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
                Excel.Application oExcel = new Excel.Application();
                oExcel.Visible = false;

                // 创建Workbooks对象  
                Excel.Workbooks oBooks = oExcel.Workbooks;

                // 创建Workbook对象  
                Excel._Workbook oBook = null;

                //System.AppDomain.CurrentDomain.BaseDirectory.ToString());
                //System.Console.WriteLine(System.Environment.CurrentDirectory.ToString());

                string appPath = System.Environment.CurrentDirectory;

                excelFilePath = appPath + "\\" + excelFilePath;

                LogUtil.LogMessage("执行Excel文件:" + excelFilePath);

                // 打开指定的Excel文件 
                oBook = oBooks.Open(excelFilePath,
                                    oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                                    oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

                object[] paraObjects = new object[] { macroName };
                object   rtnValue;

                LogUtil.LogMessage("***********运行宏命令!");

                // 执行Excel中的宏  
                rtnValue = RunMacro(oExcel, paraObjects);

                LogUtil.LogMessage("***********结束宏命令!");

                // 保存更改  
                oBook.Save();


                // 退出Workbook  
                oBook.Close(false, oMissing, oMissing);

                #endregion

                #region 释放对象
                // 释放Workbook对象  
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
                oBook = null;

                // 释放Workbooks对象  
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
                oBooks = null;

                // 关闭Excel  
                oExcel.Quit();
                // 释放Excel对象  
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
                oExcel = null;

                // 调用垃圾回收  
                GC.Collect();

                #endregion
            }
            catch (Exception exp)
            {
                LogUtil.LogMessage(exp.Message);
            }
        }
Ejemplo n.º 30
0
        public void RunExcelMacro(string excelFileName, string macro, bool visible)
        {
            //### improve & cleanup code


            // Define Workbooks
            Excel2.Application oExcel   = null;
            Excel2.Workbooks   oBooks   = null;
            Excel2._Workbook   oBook    = null;
            object             oMissing = System.Reflection.Missing.Value;

            try
            {
                //### test thread culture
                System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("fr-FR");

                FileInfo fi = new FileInfo(excelFileName);

                // Create an instance of Microsoft Excel
                //Excel2.ApplicationClass oExcel = new Excel2.ApplicationClass();

                oExcel = new Excel2.Application();

                oExcel.Visible       = visible;
                oExcel.DisplayAlerts = true;

                oBooks = oExcel.Workbooks;

                //Open the file, using the 'path' variable
                oBook = oBooks.Open(excelFileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                                    oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);


                //### activate
                //Object[] oRunArgs = { "'" + fi.Name + "'!FULL.FULL" };

                Object[] oRunArgs = { "'" + fi.Name + "'!" + macro };
                oExcel.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null,
                                              oExcel, oRunArgs);


                //oRunArgs = new object[] { fi.Name + "!exportFile" };
                //oExcel.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null,
                //    oExcel, oRunArgs);

                //save excel file
                oBook.Save();

                // Quit Excel and clean up.
                oBook.Close(false, oMissing, oMissing);

                System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
                oBook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
                oBooks = null;
                oExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
                oExcel = null;

                //Garbage collection
                GC.Collect();
            }
            catch (Exception ex)
            {
                if (oBook != null)
                {
                    try
                    {
                        oBook.Close(false, oMissing, oMissing);
                    }
                    catch (Exception ex2) { }

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
                    oBook = null;
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
                    oBooks = null;
                }

                if (oExcel != null)
                {
                    try
                    {
                        oExcel.Quit();
                    }
                    catch (Exception ex2) { }

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
                    oExcel = null;
                }

                log.Error("Error :: RunExcelMacro : " + ex.Message);
                throw ex;
            }
        }
Ejemplo n.º 31
0
        public void CrearExcel(System.Data.DataTable dt, string path)
        {
            object misvalue = System.Reflection.Missing.Value;

            try
            {
                oXL = new Microsoft.Office.Interop.Excel.Application();

                bool codescreenUpdateState = oXL.ScreenUpdating;
                bool statusBarState        = oXL.DisplayStatusBar;
                bool eventsState           = oXL.EnableEvents;

                string pathDefinitivo = path + ".xlsx";

                oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));

                oWB.SaveCopyAs(pathDefinitivo);

                //oWB.SaveAs(pathDefinitivo, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
                //  false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                // Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);


                oWB             = oXL.Workbooks.Open(pathDefinitivo, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                oXL.Calculation = XlCalculation.xlCalculationManual;

                oXL.ScreenUpdating   = false;
                oXL.DisplayStatusBar = false;
                oXL.Calculation      = XlCalculation.xlCalculationManual;
                oXL.EnableEvents     = false;

                oXL.Visible = false;

                oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

                //Cargar header
                oSheet.Cells[1, 1]  = "Empresa: ";
                oSheet.Cells[1, 2]  = "SURFACTAN S.A.";
                oSheet.Cells[1, 11] = "Fecha: ";
                oSheet.Cells[1, 12] = DateTime.Now.ToShortDateString();
                oSheet.Cells[2, 7]  = "Listado de Muestras";

                oSheet.get_Range("L1", "L1").EntireColumn.NumberFormat = "mm/dd/yyyy";
                oSheet.get_Range("A1", "L2").Font.Bold           = true;
                oSheet.get_Range("A1", "L1").Cells.Font.Size     = 10;
                oSheet.get_Range("A1", "L2").Cells.Font.Name     = "Times New Roman";
                oSheet.get_Range("G2", "G2").Cells.Font.Size     = 16;
                oSheet.get_Range("G2", "G2").HorizontalAlignment =
                    Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                oSheet.get_Range("G2", "G2").Cells.Font.Size   = 16;
                oSheet.get_Range("A1", "L2").VerticalAlignment =
                    Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

                oSheet.get_Range("G2", "G2").Borders.LineStyle =
                    Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                //Agrego los headers
                oSheet.Cells[4, 1]  = "Fecha";
                oSheet.Cells[4, 2]  = "Codigo";
                oSheet.Cells[4, 3]  = "Cantidad";
                oSheet.Cells[4, 4]  = "Nombre para el Cliente";
                oSheet.Cells[4, 6]  = "Cliente";
                oSheet.Cells[4, 8]  = "Observaciones";
                oSheet.Cells[4, 10] = "Lote";
                oSheet.Cells[4, 11] = "Cantidad";
                oSheet.Cells[4, 12] = "Observaciones";

                oSheet.get_Range("A4", "L4").Cells.Font.Size   = 8;
                oSheet.get_Range("A4", "L4").Cells.Font.Name   = "Times New Roman";
                oSheet.get_Range("A4", "L4").VerticalAlignment =
                    Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                oSheet.get_Range("A4", "L4").HorizontalAlignment =
                    Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

                int rango = 6;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string[,] valores = new string[dt.Rows.Count, 12];

                    DataRow dr = dt.Rows[i];

                    valores[0, 0]  = dr["Fecha"].ToString();
                    valores[0, 1]  = dr["Codigo"].ToString();
                    valores[0, 2]  = dr["Cantidad"].ToString();
                    valores[0, 3]  = dr["DescriCliente"].ToString();
                    valores[0, 4]  = "";
                    valores[0, 5]  = dr["Razon"].ToString();
                    valores[0, 6]  = "";
                    valores[0, 7]  = dr["Observaciones"].ToString();
                    valores[0, 8]  = "";
                    valores[0, 9]  = dr["Lote2"].ToString();
                    valores[0, 10] = dr["Cantidad2"].ToString();
                    valores[0, 11] = dr["Observaciones2"].ToString();

                    oSheet.get_Range("A" + rango.ToString(), "L" + rango.ToString()).Value2          = valores;
                    oSheet.get_Range("A" + rango.ToString(), "L" + rango.ToString()).Cells.Font.Size = 8;
                    oSheet.get_Range("A" + rango.ToString(), "L" + rango.ToString()).Cells.Font.Name = "Times New Roman";

                    rango += 1;
                }


                oXL.Visible     = false;
                oXL.UserControl = false;

                oWB.Save();
                oWB.Close();

                oXL.ScreenUpdating   = codescreenUpdateState;
                oXL.DisplayStatusBar = statusBarState;
                oXL.EnableEvents     = eventsState;

                MessageBox.Show("El archivo se genero con exito", "Generacion de Excel",
                                MessageBoxButtons.OK, MessageBoxIcon.None);
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message, "Error al generar archivo",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }