Пример #1
0
 internal WorkBookClass(string production)
 {
     pidCheck = new PidChecker(production);  //instantiation for later use in checking keys
     try
     {
         xlApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Excel.Application;
     }
     catch (Exception)
     {
         xlApp = new Excel.Application();
     }
     Microsoft.Office.Core.FileDialog fd = this.xlApp.get_FileDialog(Microsoft.Office.Core.MsoFileDialogType.msoFileDialogOpen);
     fd.AllowMultiSelect = false;
     fd.Filters.Clear();
     fd.Filters.Add("Excel Files", "*.xls;*.xlsx");
     fd.Filters.Add("All Files", "*.*");
     fd.Title = "Tax-Aide Update Product Keys";
     if (fd.Show() == -1)
         try
         {
             fd.Execute();
         }
         catch (Exception)
         {
             MessageBox.Show("There is a problem opening the excel file.\r\nPlease close any open Excel applications and start the program again.");
             Environment.Exit(1);
         }
     else
         Environment.Exit(1);
     xlApp.Visible = true;
     xlWBook = xlApp.ActiveWorkbook;
     xlWsheet = xlWBook.ActiveSheet;
     Excel.Range colHdrsRng = xlWsheet.Range["A1:E1"];
     object[,] colHdrsObj = new object[5, 1];
     colHdrsObj = colHdrsRng.Value2;
     var colHdrsStr = colHdrsObj.Cast<string>();
     //Debug.WriteLine(string.Join(Environment.NewLine + "\t",colHdrsStr.Select(x => x.ToString())));
     var result = colHdrsStr.SequenceEqual(colHdrsExpected);
     if (!result)
     {
         MessageBox.Show("The Column Headers in this spreadsheet do not conform to specification.\r\nIs the correct spreadsheet open?\r\n\r\nExiting", "Tax-Aide Update Product Keys");
         DisposeX();
     }
     //First make sure that working range is one area and has no blank rows at the end.
     if (xlWsheet.UsedRange.Areas.Count != 1)
     {
         MessageBox.Show("The used range on this spreadsheet is not contiguous, something is wrong.\r\nIs the correct spreadsheet open?\r\n\r\nExiting", "Tax-Aide Update Product Keys");
         DisposeX();
     }
     //xlApp.ScreenUpdating = true;
     xlRangeWorking = xlWsheet.UsedRange;
     while (xlApp.WorksheetFunction.CountA(xlRangeWorking.Offset[xlRangeWorking.Rows.Count - 1, 0].Resize[1, Type.Missing]) == 0)
     {//eliminates blank rows at end of working range from the working range
         xlRangeWorking = xlRangeWorking.Resize[xlRangeWorking.Rows.Count - 1, Type.Missing];
     }
     resultMessageCell = xlWsheet.Range[xlRangeWorking.Cells[(1 + xlRangeWorking.Rows.Count), 1], xlRangeWorking.Cells[(1 + xlRangeWorking.Rows.Count), 1]];
     //Console.WriteLine(xlRangeWorking.Row.ToString() + "  rowCnt= " + xlRangeWorking.Rows.Count.ToString());
     //pull spreadsheet data across for faster performance
     object[,] sysObj = new object[5, xlRangeWorking.Rows.Count];
     sysObj = xlRangeWorking.Value2;
     rowList = (from idx1 in Enumerable.Range(1, sysObj.GetLength(0))
                select new RowData { mMfr = (string)sysObj[idx1, 1], mSerNo = (sysObj[idx1, 2] != null) ? sysObj[idx1, 2].ToString() : "", mOsPK = (string)sysObj[idx1, 3], mStatus = (string)sysObj[idx1, 4], mRsult = (string)sysObj[idx1, 5] }).ToList<RowData>();
     rowList.RemoveAt(0);    //Remove Column headers from list
     //Debug.WriteLine(sysData.GetType().ToString() + "  count elements " + sysData.Count());
     //Debug.WriteLine(sysData.ElementAt(1).mMfr);
     Debug.WriteLine(string.Join(Environment.NewLine + "\t", from row in rowList select row.mMfr + "  " + row.mSerNo));
     //Change topic setup status column to receive text strings
     try
     {
         xlWsheet.Range["D1:E1"].ColumnWidth = 40;
     }
     catch (Exception e)
     {
         MessageBox.Show("Excel appears to be in a mode of not taking programmatic input. Please close all open Excel copies and restart\r\nThe error message was " + e.Message);
         DisposeX();
     }
     userMessageCell = xlWsheet.Range["D1"];
     userMessageCell.Value2 = "Querying the database for these systems";
     userMessageCell.Font.Italic = true;
     userMessageCell.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.BlueViolet);
 }
Пример #2
0
        internal WorkBookClass(string production)
        {
            pidCheck = new PidChecker(production);  //instantiation for later use in checking keys
            try
            {
                xlApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Excel.Application;
            }
            catch (Exception)
            {
                xlApp = new Excel.Application();
            }
            Microsoft.Office.Core.FileDialog fd = this.xlApp.get_FileDialog(Microsoft.Office.Core.MsoFileDialogType.msoFileDialogOpen);
            fd.AllowMultiSelect = false;
            fd.Filters.Clear();
            fd.Filters.Add("Excel Files", "*.xls;*.xlsx");
            fd.Filters.Add("All Files", "*.*");
            fd.Title = "Tax-Aide Update Product Keys";
            if (fd.Show() == -1)
            {
                try
                {
                    fd.Execute();
                }
                catch (Exception)
                {
                    MessageBox.Show("There is a problem opening the excel file.\r\nPlease close any open Excel applications and start the program again.");
                    Environment.Exit(1);
                }
            }
            else
            {
                Environment.Exit(1);
            }
            xlApp.Visible = true;
            xlWBook       = xlApp.ActiveWorkbook;
            xlWsheet      = xlWBook.ActiveSheet;
            Excel.Range colHdrsRng = xlWsheet.Range["A1:E1"];
            object[,] colHdrsObj = new object[5, 1];
            colHdrsObj           = colHdrsRng.Value2;
            var colHdrsStr = colHdrsObj.Cast <string>();
            //Debug.WriteLine(string.Join(Environment.NewLine + "\t",colHdrsStr.Select(x => x.ToString())));
            var result = colHdrsStr.SequenceEqual(colHdrsExpected);

            if (!result)
            {
                MessageBox.Show("The Column Headers in this spreadsheet do not conform to specification.\r\nIs the correct spreadsheet open?\r\n\r\nExiting", "Tax-Aide Update Product Keys");
                DisposeX();
            }
            //First make sure that working range is one area and has no blank rows at the end.
            if (xlWsheet.UsedRange.Areas.Count != 1)
            {
                MessageBox.Show("The used range on this spreadsheet is not contiguous, something is wrong.\r\nIs the correct spreadsheet open?\r\n\r\nExiting", "Tax-Aide Update Product Keys");
                DisposeX();
            }
            //xlApp.ScreenUpdating = true;
            xlRangeWorking = xlWsheet.UsedRange;
            while (xlApp.WorksheetFunction.CountA(xlRangeWorking.Offset[xlRangeWorking.Rows.Count - 1, 0].Resize[1, Type.Missing]) == 0)
            {//eliminates blank rows at end of working range from the working range
                xlRangeWorking = xlRangeWorking.Resize[xlRangeWorking.Rows.Count - 1, Type.Missing];
            }
            resultMessageCell = xlWsheet.Range[xlRangeWorking.Cells[(1 + xlRangeWorking.Rows.Count), 1], xlRangeWorking.Cells[(1 + xlRangeWorking.Rows.Count), 1]];
            //Console.WriteLine(xlRangeWorking.Row.ToString() + "  rowCnt= " + xlRangeWorking.Rows.Count.ToString());
            //pull spreadsheet data across for faster performance
            object[,] sysObj = new object[5, xlRangeWorking.Rows.Count];
            sysObj           = xlRangeWorking.Value2;
            rowList          = (from idx1 in Enumerable.Range(1, sysObj.GetLength(0))
                                select new RowData {
                mMfr = (string)sysObj[idx1, 1], mSerNo = (sysObj[idx1, 2] != null) ? sysObj[idx1, 2].ToString() : "", mOsPK = (string)sysObj[idx1, 3], mStatus = (string)sysObj[idx1, 4], mRsult = (string)sysObj[idx1, 5]
            }).ToList <RowData>();
            rowList.RemoveAt(0);    //Remove Column headers from list
            //Debug.WriteLine(sysData.GetType().ToString() + "  count elements " + sysData.Count());
            //Debug.WriteLine(sysData.ElementAt(1).mMfr);
            Debug.WriteLine(string.Join(Environment.NewLine + "\t", from row in rowList select row.mMfr + "  " + row.mSerNo));
            //Change topic setup status column to receive text strings
            try
            {
                xlWsheet.Range["D1:E1"].ColumnWidth = 40;
            }
            catch (Exception e)
            {
                MessageBox.Show("Excel appears to be in a mode of not taking programmatic input. Please close all open Excel copies and restart\r\nThe error message was " + e.Message);
                DisposeX();
            }
            userMessageCell             = xlWsheet.Range["D1"];
            userMessageCell.Value2      = "Querying the database for these systems";
            userMessageCell.Font.Italic = true;
            userMessageCell.Font.Color  = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.BlueViolet);
        }