Esempio n. 1
0
        internal void AddTable()
        {
            Excel.Range sumRange = BottomCell().get_Offset(2, 0);

            sumRange.Value2 = yr();

            object[] years = Utilities.ExcelHelpers.getYears(DS.SourceData);
            int      ycols = years.Length;

            sumRange = sumRange.get_Resize(1, ycols + 1);
            for (int i = 0; i < ycols; i++)
            {
                if (false)//i.Equals(0))//
                {
                    sumRange.get_Offset(0, i + 1).get_Resize(1, 1).Value2 = years[i] + " (Baseline)";
                }
                else
                {
                    sumRange.get_Offset(0, i + 1).get_Resize(1, 1).Value2 = years[i];
                }
            }

            SummaryData      = thisSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, sumRange, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing);
            SummaryData.Name = "Annual" + SummaryData.Name;
            ((Excel.Range)SummaryData.Range[1, 1]).Value2 = " ";
            SummaryData.TableStyle = "TableStyleMedium4";
        }
Esempio n. 2
0
 private void dropDownYear_SelectionChanged(object sender, RibbonControlEventArgs e)
 {
     Excel.Range      thisRange = Globals.ThisAddIn.Application.Selection as Excel.Range;
     Excel.Worksheet  thisSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
     Excel.ListObject thisList  = ExcelHelpers.GetListObject(thisSheet);
     ExcelHelpers.SetYear(thisList, thisRange, dropDownYear.SelectedItemIndex - 1);
 }
Esempio n. 3
0
        private void add_ListColumn(object sender, RibbonControlEventArgs e, string ColName)
        {
            Excel.Worksheet  thisSheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
            Excel.ListObject thisList  = ExcelHelpers.GetListObject(thisSheet);

            ExcelHelpers.AddListColumn(thisList, ColName, 0);
        }
Esempio n. 4
0
        public ExcelHandlerModel2()
        {
            SummaryTableTest.Clear();
            SummaryTableTest.Columns.Add("Файл");
            SummaryTableTest.Columns.Add("Статус");
            SummaryTableTest.Columns.Add("Количество контрактов", System.Type.GetType("System.Int32"));

            app      = new Excel.Application();
            workbook = app.Workbooks.Add(Type.Missing);

            worksheet = workbook.ActiveSheet;

            table      = worksheet.ListObjects.Add();
            table.Name = "Результат";
            Excel.Range header = table.HeaderRowRange;

            header[1, 1].Value = "ОСВ";
            header[1, 2].Value = "КФО";
            header[1, 3].Value = "Контрагент";
            header[1, 4].Value = "Договор";
            header[1, 5].Value = "Дебет";
            header[1, 6].Value = "Кредит";

            table.ListColumns.Item[5].Range.NumberFormatLocal = @"# ##0,00";
            table.ListColumns.Item[6].Range.NumberFormatLocal = @"# ##0,00";

            //table.ListColumns.Item[5].Range.NumberFormat = @"@";
        }
        public frmDEMapColumnValues(Interop.ListObject inExcelTable, string columnName,
                                    string newColDTusrKey, string oldColDTkey,
                                    Dictionary <object, List <object> > ExCellValToUsrVal)
        {
            InitializeComponent();

            _intputExTable = inExcelTable;
            _colToInspect  = columnName;

            // store the new data type of the column
            _colToInspectNewDTKey = newColDTusrKey;

            // store the aproximated column value
            _colToInspectOldDTKey = oldColDTkey;

            // Excel cell value to be mapped to a user value dict
            if (ExCellValToUsrVal != null)
            {
                _ExCellValToUserValDict = ExCellValToUsrVal;
            }
            else
            {
                _ExCellValToUserValDict = new Dictionary <object, List <object> >();
            }
        }
Esempio n. 6
0
        public static Excel.Range getYearRange(Excel.ListObject LO, string year)
        {
            Excel.ListColumn LCyear = GetListColumn(LO, EnPIResources.yearColName);
            int first = 0;
            int last  = 0;
            int j     = 0;

            if (LCyear != null)
            {
                foreach (Excel.Range row in LCyear.Range.Rows)
                {
                    j += 1;
                    if (row.Value2.ToString() == year && first == 0)
                    {
                        first = j;
                    }
                    if (row.Value2.ToString() == year)
                    {
                        last = j;
                    }
                }
            }

            if (first != 0)
            {
                return(LO.Range.get_Offset(first - 1, 0).get_Resize(last - (first - 1), LO.ListColumns.Count));
            }
            else
            {
                return(null);
            }
        }
Esempio n. 7
0
        }         // func RefreshTableAsync

        internal async Task RefreshTableAsync(Excel.ListObject _xlList, bool refreshLayout)
        {
            using (var progress = CreateProgress())
            {
                var xlList = Globals.Factory.GetVstoObject(_xlList);

                progress.Report(String.Format("Aktualisiere {0}...", xlList.Name ?? "Tabelle"));

                if (PpsListObject.TryGet(FindEnvironment, xlList, out var ppsList))
                {
                    await ppsList.RefreshAsync(refreshLayout?PpsXlRefreshList.Style : PpsXlRefreshList.None, PpsMenu.IsSingleLineModeToggle(), null);
                }
                else
                {
                    //if (refreshColumnLayout)
                    //	;

                    switch (xlList.SourceType)
                    {
                    case Excel.XlListObjectSourceType.xlSrcQuery:
                    case Excel.XlListObjectSourceType.xlSrcXml:
                        try
                        {
                            xlList.Refresh();
                        }
                        catch (COMException)
                        { }
                        break;
                    }
                }
            }
        }         // func RefreshTableAsync
Esempio n. 8
0
        public void Update()
        {
            this.Name        = this.ListObject.Name;
            this.DisplayName = this.Worksheet.Name;

            if (string.IsNullOrWhiteSpace(this.DataName))
            {
                this.Icon               = Properties.Resources.table;
                this.DataDisplayName    = string.Empty;
                this.ListObject.Comment = string.Empty;
            }
            else
            {
                this.Icon = Properties.Resources.table_link;
                Excel.ListObject dataListObject = ListObjectHelper.GetByName(this.Workbook, this.DataName);

                if (null != dataListObject)
                {
                    this.DataDisplayName = ListObjectHelper.GetWorksheetName(dataListObject);
                }
                else
                {
                    this.DataDisplayName = string.Empty;
                }
            }
        }
Esempio n. 9
0
        public TestCase(Excel.Workbook workbook, Excel.Worksheet worksheet, Excel.ListObject listObject)
        {
            if (null == workbook)
            {
                throw new ArgumentNullException("workbook");
            }

            if (null == worksheet)
            {
                throw new ArgumentNullException("worksheet");
            }

            if (null == listObject)
            {
                throw new ArgumentNullException("listObject");
            }

            this.Workbook   = workbook;
            this.Worksheet  = worksheet;
            this.ListObject = listObject;
            this.DataName   = listObject.Comment;
            this.Result     = TestResult.None;
            this.Load();
            this.Update();
        }
Esempio n. 10
0
        private void ExecuteInternal()
        {
            var workbookContext = App.Context.GetActiveWorkbookContext();

            Excel.Workbook  workbook  = workbookContext.Workbook;
            Excel.Worksheet worksheet = ExcelHelper.WorksheetAdd(workbook);
            ExcelHelper.WorksheetActivate(worksheet);

            string name = ListObjectHelper.NewTestDataName(workbook);

            worksheet.Name = name;

            Excel.ListObject listObject = ListObjectHelper.AddListObject(worksheet);
            listObject.Name = name;

            listObject.ListColumns[1].Name = "Column1";
            listObject.ListColumns[1].Range.EntireColumn.AutoFit();

            for (int i = 2; i < 10; i++)
            {
                Excel.ListColumn listColumn = listObject.ListColumns.Add();

                listColumn.Name = string.Format(
                    CultureInfo.CurrentCulture,
                    "Column{0}",
                    i);

                listColumn.Range.EntireColumn.AutoFit();
            }

            ListObjectHelper.AddRows(listObject, DEFAULT_ROW_COUNT, false);
            ListObjectHelper.SelectCell(listObject, 2, 1);
        }
Esempio n. 11
0
        internal string addProductionColumn(Excel.Worksheet WS, Excel.ListObject LO, string dataListName)
        {
            string colName = "";
            string formula = "";

            Excel.ListObject dataList = ExcelHelpers.GetListObject(WS, dataListName);
            object[,] headers = dataList.HeaderRowRange.Value2 as object[, ];
            string dttbl     = dataListName;
            string dtyearcol = ExcelHelpers.GetListColumn(dataList, EnPIResources.yearColName).Name;
            string yearcol   = ExcelHelpers.GetListColumn(LO, EnPIResources.yearColName).Name;

            int i = headers.GetLowerBound(0);

            for (int j = headers.GetLowerBound(1); j <= headers.GetUpperBound(1); j++)
            {
                if (DataHelper.isProduction(headers[i, j].ToString()))
                {
                    formula += "SUM(" + dttbl + ExcelHelpers.CreateValidFormulaName(headers[i, j].ToString()) + " " +
                               DataHelper.RowRangebyMatch(dttbl, dtyearcol, "[[#This Row]," + yearcol + "]", dataSheetName)
                               + ")" + "+";
                }
            }
            if (formula != "")
            {
                Excel.ListColumn newCol = LO.ListColumns.Add(2);
                colName     = "Production";
                newCol.Name = colName;
                newCol.DataBodyRange.Formula = "=" + formula.Substring(0, formula.Length - 1);
                newCol.DataBodyRange.Style   = "Comma [0]";
            }

            return(colName);
        }
Esempio n. 12
0
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            //<snippet1>
            Microsoft.Office.Interop.Excel.Worksheet nativeWorksheet =
                Globals.ThisAddIn.Application.ActiveSheet;
            if (nativeWorksheet != null)
            {
                Microsoft.Office.Tools.Excel.Worksheet vstoSheet =
                    Globals.Factory.GetVstoObject(nativeWorksheet);
            }
            //</snippet1>

            //<snippet2>
            Microsoft.Office.Interop.Excel.Workbook nativeWorkbook =
                Globals.ThisAddIn.Application.ActiveWorkbook;
            if (nativeWorkbook != null)
            {
                Microsoft.Office.Tools.Excel.Workbook vstoWorkbook =
                    Globals.Factory.GetVstoObject(nativeWorkbook);
            }
            //</snippet2>

            //<snippet3>
            Microsoft.Office.Interop.Excel.Worksheet sheet =
                Globals.ThisAddIn.Application.ActiveSheet;
            if (sheet.ListObjects.Count > 0)
            {
                Excel.ListObject listObject =
                    sheet.ListObjects[1];
                Microsoft.Office.Tools.Excel.ListObject vstoListObject =
                    Globals.Factory.GetVstoObject(listObject);
            }
            //</snippet3>
        }
Esempio n. 13
0
        internal void AddConditionalFormatting(Excel.ListObject LO)
        {
            // for each independent variable
            foreach (Excel.ListColumn LC in LO.ListColumns)
            {
                if (DS.IndependentVariables.Contains(LC.Name))
                {
                    double   low    = 0;
                    double   high   = 0;
                    double[] values = Utilities.DataHelper.objectTOdblArray(LC.Range.Value2 as object[, ]);

                    string  fltr = "VariableName='" + LC.Name.Replace("'", "''") + "'";
                    DataRow row  = DS.PredictorRange().Select(fltr).First();
                    // get the low value
                    low = Double.TryParse(row[1].ToString(), out low) ? Double.Parse(row[1].ToString()) : 0;
                    // get the high value
                    high = Double.TryParse(row[2].ToString(), out high) ? Double.Parse(row[2].ToString()) : 0;

                    // apply conditional formatting
                    if (low != 0 && high != 0)
                    {
                        Excel.Range           rng = Utilities.ExcelHelpers.GetListColumn(LO, LC.Name).DataBodyRange;
                        Excel.FormatCondition fc  = (Excel.FormatCondition)rng.FormatConditions.Add(
                            Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlNotBetween, low, high
                            , System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
                        fc.Font.ColorIndex = 3;
                    }
                }
            }
        }
Esempio n. 14
0
        internal void modelInformation()
        {
            Excel.Range sumRange = BottomCell().get_Offset(2, 0);

            ((Excel.Range)sumRange[1, 1]).Value2 = " ";

            //sumRange.Value2 = "Info";

            ModelData      = thisSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, sumRange, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing);
            ModelData.Name = "Model" + ModelData.Name;
            ((Excel.Range)ModelData.Range[1, 1]).Value2 = " ";
            ModelData.TableStyle = "TableStyleMedium4";

            ModelData.ListColumns.Add(Type.Missing).Name = "Variables";
            ModelData.ListColumns.Add(Type.Missing).Name = "Model is Appropriate for SEP";
            ModelData.ListColumns.Add(Type.Missing).Name = "Model Validity";
            ModelData.ListColumns.Add(Type.Missing).Name = "Model P-Value";
            ModelData.ListColumns.Add(Type.Missing).Name = "Variable P-Value";
            ModelData.ListColumns.Add(Type.Missing).Name = "Adjusted R2";
            ModelData.ListColumns.Add(Type.Missing).Name = "Model Selected";


            //get all the variables from the currently selected model.

            foreach (String esv in DS.EnergySourceVariables)
            {
                ModelData.ListRows.AddEx(Type.Missing, Type.Missing);
                ModelData.ListRows[DS.EnergySourceVariables.IndexOf(esv) + 1].Range.Value2 = esv;
            }
        }
Esempio n. 15
0
        public static void SetYear(Excel.ListObject LO, Excel.Range Sel, int YearNo)
        {
            Excel.ListColumn yearCol = GetListColumn(LO, EnPIResources.yearColName);

            if (yearCol == null)
            {
                yearCol = AddListColumn(LO, EnPIResources.yearColName, 1);
            }

            // the selection
            int firstRow = Sel.Row;

            // if there are header rows in the selection or the selection starts above the header row, return
            // we don't know where we are on the sheet relative to the list object
            if (Sel.ListHeaderRows > 0 || firstRow < LO.HeaderRowRange.Row)
            {
                return;
            }

            // get the corresponding range in the year column
            Excel.Range yearRows = yearCol.Range.get_Offset(firstRow - LO.HeaderRowRange.Row, 0).get_Resize(Sel.Rows.Count, 1);
            yearRows.Value2 = YearNo;

            yearRows.NumberFormat = "@";
        }
Esempio n. 16
0
        public ReportingPeriodControl(bool fromWizard)
        {
            InitializeComponent();
            DataLO = ((Excel.Range)Globals.ThisAddIn.Application.Selection).ListObject;

            if (DataLO == null)
            {
                DataLO = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).ListObjects[1];
            }

            Excel.ListObject thisList   = ExcelHelpers.GetListObject(thisSheet);
            bool             dateColChk = false;

            foreach (Excel.ListColumn cname in thisList.ListColumns)
            {
                string colName = cname.Name.ToUpper();
                if (colName.Equals("DATE"))
                {
                    dateColChk = true;
                    cname.Name = "Date";
                }
            }

            if (!dateColChk)
            {
                this.cbBaselineYear.Enabled     = false;
                this.cbInterval.Enabled         = false;
                this.cbLabel.Enabled            = false;
                this.btnReportingPeriod.Enabled = false;
            }

            this.btnBack.Visible  = fromWizard;
            this.btnClose.Visible = fromWizard;
            this.btnNext.Visible  = fromWizard;
        }
Esempio n. 17
0
        public static int DataPt(Excel.ListObject LO, string year)
        {
            int TotDataPt = 0;

            TotDataPt = Analytics.getDataPt(rangeTable(LO), year, EnPIResources.yearColName);
            return(TotDataPt);
        }
Esempio n. 18
0
 private void PopulateDropdown(Excel.ListObject DataLO)
 {
     this.comboBox1.Items.Clear();
     cmbValues.Clear();
     if (DataLO != null)
     {
         foreach (SwitchModel SM in sModCol)
         {
             string vars  = "";
             int    count = 0;
             foreach (string s in SM.VariableNames)
             {
                 if (s != null && s != "(Intercept)")
                 {
                     if (SM.VariableNames.Length - 1 > count)
                     {
                         vars = vars + s + ", ";
                     }
                     else
                     {
                         vars = vars + s;
                     }
                 }
                 count++;
             }
             cmbValues.Add(vars.Substring(0, vars.Length - 1) + " (Adj R^2=" + Math.Round(SM.R2, 4) + ")", SM.ModelNumber);
             this.comboBox1.Items.Add(vars.Substring(0, vars.Length - 1) + " (Adj R^2=" + Math.Round(SM.R2, 4) + ")");
         }
     }
 }
Esempio n. 19
0
        public static Excel.ListObject writeDataTable(string startCell, ref Excel.Worksheet WS, ref DataTable dt)
        {
            // create list objects for the model year and adjusted year data
            string start = startCell;

            //int rowOffset = 3;
            Excel.Range range1 = WS.get_Range(start, missing)
                                 //.get_Offset(rowOffset, 0)
                                 .get_Resize(dt.Rows.Count + 1, dt.Columns.Count);

            range1.get_Resize(1, dt.Columns.Count).Value2 = DataHelper.dataTableHeaders(dt);

            range1.get_Offset(1, 0).get_Resize(dt.Rows.Count, dt.Columns.Count).Value2 = DataHelper.dataTableArray(dt);

            // add in formulae
            foreach (DataColumn dc in dt.Columns)
            {
                if (dc.ExtendedProperties["Formula"] != null)
                {
                    range1.get_Offset(1, dc.Ordinal).get_Resize(dt.Rows.Count, 1).Value2 = dc.ExtendedProperties["formula"];
                }
            }

            Excel.ListObject newObject = WS.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, range1, missing, Excel.XlYesNoGuess.xlYes, missing);

            string tmp = newObject.Range.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing);

            return(newObject);
        }
Esempio n. 20
0
        /// <summary>
        /// Sets <list type="bullet">
        /// <item>parent <see cref="Excel.Worksheet"/> of <see cref="Excel.ListObject"/></item>
        /// <item><see cref="Excel.ListObject"/> itself</item>
        /// <item><see cref="iClickerQuizPts.ListObjMgmt.XLListObjWrapper.DoesListObjHaveData"/> property</item>
        /// </list>
        /// </summary>
        /// <exception cref="iClickerQuizPts.AppExceptions.MissingWorksheetException"> thrown when the
        /// parent <see cref="Excel.Worksheet"/> has either been renamed or deleted.</exception>
        /// <exception cref="iClickerQuizPts.AppExceptions.MissingListObjectException"> thrown when
        /// the <see cref="Excel.ListObject"/> has either been renamed or deleted.</exception>
        /// <remarks>It would be more efficient to call this method from within the class
        /// constructor.  However, doing some complicates unit testing.</remarks>
        public void SetListObjAndParentWshPpts()
        {
            if (!DoesParentWshExist())
            {
                MissingWorksheetException ex = new MissingWorksheetException();
                ex.WshListObjPair = _wshLoPr;
                throw ex;
            }
            else
            {
                _ws = Globals.ThisWorkbook.Worksheets[_wshLoPr.WshNm];
            }

            if (!DoesListObjExist())
            {
                MissingListObjectException ex = new MissingListObjectException();
                ex.WshListObjPair = _wshLoPr;
                throw ex;
            }
            else
            {
                _lo = _ws.ListObjects[_wshLoPr.ListObjName];
            }

            // Set fields...
            _wshAndListObjIntegrityVerified = true;
            _listObjHasData = DoesListObjHaveData();
        }
 public static List <Contact> ReadExcel(string filename)
 {
     Excel.Application excel    = null;
     Excel.Workbook    workbook = null;
     try {
         excel         = new Excel.Application();
         excel.Visible = false;
         workbook      = excel.Workbooks.Open(filename, ReadOnly: true);
         Excel.Worksheet     sheet    = workbook.Sheets[1];
         Excel.ListObject    table    = sheet.ListObjects["Information_Table"];
         List <ProtoContact> contacts = new List <ProtoContact>();
         foreach (Excel.ListRow row in table.ListRows)
         {
             ProtoContact c = new ProtoContact(table.ListColumns, row);
             contacts.Add(c);
         }
         Dictionary <string, bool> multiple = contacts.GroupBy(c => c.First).ToDictionary(cg => cg.Key, cg => cg.Count() > 1);
         return(contacts.Select(c => {
             string shortName = multiple[c.First] ? c.First + " " + c.Last.Substring(0, 1) + "." : c.First;
             return new Contact(c, shortName);
         }).ToList());
     } finally {
         if (workbook != null)
         {
             workbook.Close();
         }
         if (excel != null)
         {
             excel.Quit();
         }
     }
 }
Esempio n. 22
0
        public static Excel.ListColumn GetListColumn(Excel.ListObject LO, String ColName)
        {
            Excel.ListColumn thisColumn = null;
            if (ColName == null || LO == null)
            {
                return(null);
            }

            string thisName = ColName.Replace("\\", "/");

            foreach (Excel.ListColumn i in LO.ListColumns)
            {
                if (i.Name.ToLower() == thisName.ToLower())
                {
                    thisColumn = i;
                }
            }

            //if (thisColumn == null)
            //{
            //    thisColumn = AddListColumn(LO, thisName, Pos);
            //}

            return(thisColumn);
        }
Esempio n. 23
0
        private void btnRun_Click(object sender, EventArgs e)
        {
            try
            {
                Excel.Worksheet  thisSheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
                Excel.ListObject thisList  = ExcelHelpers.GetListObject(thisSheet);

                int orgCount = thisList.ListColumns.Count;

                foreach (string t in this.checkedListBox1.CheckedItems)
                {
                    int    lcCount   = thisList.ListColumns.Count;
                    int    position  = this.checkedListBox1.Items.IndexOf(t) + (lcCount - orgCount) + 2;
                    string colName   = t + "(" + this.comboBox2.SelectedItem.ToString() + ")";
                    string formula   = "=" + ExcelHelpers.CreateValidFormulaName(t) + "*" + this.textBox1.Text + "*" + this.textBox2.Text;
                    string stylename = "Comma";

                    Excel.ListColumn newColumn = thisList.ListColumns.Add(position);
                    newColumn.Name = colName;
                    newColumn.DataBodyRange.Formula = formula;
                    newColumn.DataBodyRange.Style   = stylename;

                    //set old column property to not show up in regression/actual options
                }

                this.checkedListBox1.Items.Clear();
                AddColumnNames();
            }
            catch
            {
                //raise error
            }
        }
Esempio n. 24
0
        public void DuplicateInExcel()
        {
            Visio.Window win = vApp.ActiveWindow;
            if (win.Type != (short)Visio.VisWinTypes.visDrawing)
            {
                return;
            }
            if (win.Selection.Count < 1)
            {
                return;
            }

            xRulerOrigin = vApp.ActivePage.PageSheet.Cells["XRulerOrigin"].Result[Visio.VisUnitCodes.visMillimeters];
            yRulerOrigin = vApp.ActivePage.PageSheet.Cells["YRulerOrigin"].Result[Visio.VisUnitCodes.visMillimeters];

            Excel.Application xlsApp    = new Excel.Application();
            Excel.Workbook    workbook  = xlsApp.Workbooks.Add();
            Excel.Worksheet   worksheet = workbook.Worksheets[1];
            worksheet.Name        = "Positions";
            worksheet.Cells[1, 1] = "X";
            worksheet.Cells[1, 2] = "Y";
            Excel.ListObject listObject = worksheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 2]], null, Excel.XlYesNoGuess.xlYes);

            xlsApp.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(App_WorkbookBeforeClose);

            worksheet.Columns.AutoFit();
            xlsApp.Visible = true;
        }
Esempio n. 25
0
        public void FormatAsTable(Excel.Range SourceRange, string TableName)
        {
            Excel.ListObject table = SourceRange.Worksheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange,
                                                                           SourceRange, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing);

            table.Name = TableName;
        }
Esempio n. 26
0
 /// <summary>
 /// Check to see if there is an named range selected
 /// </summary>
 /// <param name="showMsg">To show a message </param>
 /// <returns>A method that returns true or false if there is a valid list object </returns>
 /// <remarks></remarks>
 public static bool IsValidListObject(bool showMsg = false)
 {
     Excel.ListObject tbl = null;
     try
     {
         tbl = Globals.ThisAddIn.Application.ActiveCell.ListObject;  // directly after the table is created this is not true
         if ((tbl == null))
         {
             if (showMsg == true)
             {
                 MessageBox.Show("The command could not be completed by using the range specified.  Select a single cell within the range and try the command again. [ListObject]", AssemblyInfo.Description, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
             }
             return(false);
         }
         else
         {
             return(true);
         }
     }
     catch (Exception)
     {
         return(false);
     }
     finally
     {
         if (tbl != null)
         {
             Marshal.ReleaseComObject(tbl);
         }
     }
 }
Esempio n. 27
0
        /// <summary>
        /// Change zero string cell values to string "NULL"
        /// </summary>
        /// <remarks></remarks>
        public static void UpdateZeroStringToNull(Excel.ListObject tbl)
        {
            try
            {
                if (ErrorHandler.IsAvailable(true) == false)
                {
                    return;
                }

                Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
                ErrorHandler.CreateLogRecord();

                Excel.Range cell      = default(Excel.Range);
                Excel.Range usedRange = tbl.Range;

                for (int r = 0; r <= tbl.ListRows.Count; r++)
                {
                    for (int c = 1; c <= tbl.ListColumns.Count; c++)
                    {
                        if (usedRange[r + 1, c].Value2 == null)
                        {
                            cell       = tbl.Range.Cells[r + 1, c];
                            cell.Value = "NULL";
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorHandler.DisplayMessage(ex);
            }
        }
Esempio n. 28
0
        private static void WriteTableHeaderRangeToCSV(Interop.ListObject excelTable, char csvDelimiter)
        {
            Interop.Range tableHeaderRange = excelTable.HeaderRowRange;

            string Headerline         = String.Empty;
            int    columnInTableCount = 1;

            foreach (Interop.Range cell in tableHeaderRange)
            {
                if (columnInTableCount == 1)
                {
                    // assume the header is not null or empty
                    //Headerline = Escape(Convert.ToString(cell.Value2), csvDelimiter);
                    Headerline = EscapeCSVstyle(Convert.ToString(cell.Text), csvDelimiter);
                }
                else
                {
                    // assume the header is not null or empty
                    //Headerline = String.Join(Convert.ToString(csvDelimiter), Headerline, Escape(Convert.ToString(cell.Value2), csvDelimiter));
                    Headerline = String.Join(Convert.ToString(csvDelimiter), Headerline, EscapeCSVstyle(Convert.ToString(cell.Text), csvDelimiter));
                }

                columnInTableCount++;
            }

            Headerline += Environment.NewLine;

            File.WriteAllText(filePath, Headerline);

            if (tableHeaderRange != null)
            {
                Marshal.ReleaseComObject(tableHeaderRange);
            }
        }
Esempio n. 29
0
        private List <ExcelReferenceTable> GetReferenceTables(Excel.Sheets xlWorkSheets)
        {
            List <ExcelReferenceTable> Result = new List <ExcelReferenceTable>();
            string Temp = "";

            Excel.Worksheet   xlWorkSheet   = null;
            Excel.ListObjects xlListObjects = null;
            Excel.ListObject  ThisItem      = null;

            for (int x = 1; x <= xlWorkSheets.Count; x++)
            {
                ExcelReferenceTable Item = new ExcelReferenceTable();

                xlWorkSheet   = (Excel.Worksheet)xlWorkSheets[x];
                xlListObjects = xlWorkSheet.ListObjects;

                Int32 TotalCount = xlListObjects.Count - 1;
                for (int y = 0; y <= TotalCount; y++)
                {
                    ThisItem       = xlListObjects.Item[y + 1];
                    Item.Name      = ThisItem.Name;
                    Item.SheetName = xlWorkSheet.Name;

                    // TODO: Need to tinker with this.
                    try
                    {
                        Excel.QueryTable QT = ThisItem.QueryTable;
                        Item.SourceDataFile = QT.SourceDataFile;
                        ReleaseComObject(QT);
                    }
                    catch (Exception)
                    {
                        Item.SourceDataFile = "";
                    }

                    Excel.Range ThisRange = ThisItem.Range;
                    Temp = ThisRange.Address;

                    Item.Address = Temp.Replace("$", "");

                    Result.Add(Item);

                    Marshal.FinalReleaseComObject(ThisRange);
                    ThisRange = null;

                    Marshal.FinalReleaseComObject(ThisItem);
                    ThisItem = null;

                    Marshal.FinalReleaseComObject(xlListObjects);
                    xlListObjects = null;
                }
            }

            ReleaseComObject(xlWorkSheet);

            mReferenceTables = Result;

            return(Result);
        }
        internal bool Import(Commande cmd)
        {
            string path = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            string file = $"{path}\\{dbName}_{cmd.Piece}.xlsm";

            if (!File.Exists(file))
            {
                throw new Exception($"Fichier {file} non trouvé");
            }
            ExcelApp.Application app      = new ExcelApp.Application();
            ExcelApp.Workbook    workbook = app.Workbooks.Open(file);
            try
            {
                ExcelApp.Worksheet  worksheet  = workbook.Worksheets["Commande"];
                ExcelApp.ListObject listObject = worksheet.ListObjects["tableCommande"];
                foreach (ExcelApp.ListRow row in listObject.ListRows)
                {
                    try
                    {
                        string Ref   = (string)row.Range[2].Value();
                        double?qt    = (double?)row.Range[3].Value();
                        double stock = ((double?)row.Range[10].Value()) ?? 0;
                        Debug.Print($"{Ref} - {qt} - {stock}");

                        LigneCommande ligne = cmd.Lignes.Where(l => l.Ref == Ref).FirstOrDefault();
                        if (ligne == null)
                        {
                            continue;
                        }
                        if (qt != ligne.Qte)
                        {
                            ligne.Qte = qt;
                        }
                        if (stock != ligne.Stock)
                        {
                            ligne.Stock = stock;
                        }
                    }
                    catch (Exception)
                    {
                        // TODO Log
                    }
                }

                return(true);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (workbook != null)
                {
                    workbook.Close(false);
                }
                app.Quit();
            }
        }
        /// <summary>
        /// Da Formato a la Hoja de Excel Creando los
        /// </summary>
        private void RequisitionServiceFormat()
        {
            try
            {
                excelApp = Globals.ThisAddIn.Application;
                var excelBook = excelApp.Workbooks.Add();
                Excel.Worksheet excelSheet = excelBook.ActiveSheet;

                excelSheet.Name = SheetName01;

                cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).Style = cells.GetStyle(StyleConstants.Normal);
                cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).Clear();
                cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).Clear();

                cells.GetCell("A1").Value = "CERREJÓN";
                cells.GetCell("B1").Value = "Requisition Service";

                cells.GetCell(1, TittleRow).Value = "Requested By";
                cells.GetCell(2, TittleRow).Value = "Requested By Position";
                cells.GetCell(3, TittleRow).Value = "Requisition Number";
                cells.GetCell(4, TittleRow).Value = "Requisition Type";
                cells.GetCell(5, TittleRow).Value = "Transaction Type";
                cells.GetCell(6, TittleRow).Value = "Required By Date";
                cells.GetCell(6, TittleRow).AddComment("YYYYMMDD");
                cells.GetCell(7, TittleRow).Value = "Original Warehouse";
                cells.GetCell(8, TittleRow).Value = "Priority Code";
                cells.GetCell(9, TittleRow).Value = "Reference Type";
                cells.GetCell(10, TittleRow).Value = "Reference";
                cells.GetCell(11, TittleRow).Value = "Delivery Instructions";
                cells.GetCell(12, TittleRow).Value = "Return Cause";
                cells.GetCell(13, TittleRow).Value = "Issue Question";
                cells.GetCell(14, TittleRow).Value = "Stock Code";
                cells.GetCell(15, TittleRow).Value = "Unit Of Issue";
                cells.GetCell(16, TittleRow).Value = "Quantity";
                cells.GetCell(ResultColumn, TittleRow).Value = "Result";

                cells.GetCell("E1").Value = "OBLIGATORIO";
                cells.GetCell("E1").Style = cells.GetStyle(StyleConstants.TitleRequired);
                cells.GetCell("E2").Value = "OPCIONAL";
                cells.GetCell("E2").Style = cells.GetStyle(StyleConstants.TitleOptional);
                cells.GetCell("E3").Value = "INFORMATIVO";
                cells.GetCell("E3").Style = cells.GetStyle(StyleConstants.TitleInformation);
                cells.GetCell("E4").Value = "ACCIÓN A REALIZAR";
                cells.GetCell("E4").Style = cells.GetStyle(StyleConstants.TitleAction);
                cells.GetCell("E5").Value = "REQUERIDO ADICIONAL";
                cells.GetCell("E5").Style = cells.GetStyle(StyleConstants.TitleAdditional);

                #region Styles
                cells.GetCell(1, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
                cells.GetCell(2, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
                cells.GetCell(3, TittleRow).Style = cells.GetStyle(StyleConstants.TitleInformation);
                cells.GetCell(4, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
                cells.GetCell(5, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
                cells.GetCell(6, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
                cells.GetCell(7, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
                cells.GetCell(8, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
                cells.GetCell(9, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
                cells.GetCell(10, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
                cells.GetCell(11, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
                cells.GetCell(12, TittleRow).Style = cells.GetStyle(StyleConstants.TitleOptional);
                cells.GetCell(13, TittleRow).Style = cells.GetStyle(StyleConstants.TitleOptional);
                cells.GetCell(14, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
                cells.GetCell(15, TittleRow).Style = cells.GetStyle(StyleConstants.TitleOptional);
                cells.GetCell(16, TittleRow).Style = cells.GetStyle(StyleConstants.TitleRequired);
                cells.GetCell(ResultColumn, TittleRow).Style = cells.GetStyle(StyleConstants.TitleInformation);
                #endregion

                excelSheetItems = excelSheet.ListObjects.AddEx(SourceType: Excel.XlListObjectSourceType.xlSrcRange, Source: cells.GetRange(1, TittleRow, ResultColumn, 10000), XlListObjectHasHeaders: Excel.XlYesNoGuess.xlYes);
                excelSheetItems.Name = "RequisitionsData";

                excelSheet.Cells.Columns.AutoFit();
                excelSheet.Cells.Rows.AutoFit();

                //ExcelSheet.Protection.AllowEditRanges.Add("Editable", worksheet.Range["B2:B3,A5:O100000"]);
                //ExcelSheet.Protect(Type.Missing, true, true, true, true, true, true, true, false, true, false, false, false, true, true, false);

                #region Selection List Options

                var optionList = new List<string> { "Work Order", "Equipment No.", "Project No.", "Account Code" };
                cells.SetValidationList(cells.GetRange(9, TittleRow + 1, 9, MaxRows), optionList);

                optionList = new List<string>
                {
                    "NI - NORMAL REQUISITION",
                    "PR - PURCHASE REQUISITION",
                    "CR - CREDIT REQUISITION",
                    "LN - LOAN REQUISITION"
                };
                cells.SetValidationList(cells.GetRange(4, TittleRow + 1, 4, MaxRows), optionList);

                optionList = new List<string>
                {
                    "DN - DESPACHO NO PLANEADO",
                    "DP - DESPACHO PLANEADO",
                    "CN - DEVOLUCION NO PLANEADA",
                    "CP - DEVOLUCION PLANEADA"
                };
                cells.SetValidationList(cells.GetRange(5, TittleRow + 1, 5, MaxRows), optionList);

                optionList = new List<string> { "", "A - VENTAS", "B - RUBROS" };
                cells.SetValidationList(cells.GetRange(13, TittleRow + 1, 13, MaxRows), optionList);

                optionList = GetPriorityTable();
                cells.SetValidationList(cells.GetRange(8, TittleRow + 1, 8, MaxRows), optionList);
                #endregion

                cells.GetRange(1, TittleRow + 1, ResultColumn, MaxRows).Style.NumberFormat = "@";
            }
            catch (Exception error)
            {
                cells.GetCell(ResultColumn, TittleRow).Value += " Error " + error.Message;
            }
        }