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"; }
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); }
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); }
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> >(); } }
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); } }
} // 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
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; } } }
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(); }
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); }
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); }
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> }
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; } } } }
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; } }
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 = "@"; }
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; }
public static int DataPt(Excel.ListObject LO, string year) { int TotDataPt = 0; TotDataPt = Analytics.getDataPt(rangeTable(LO), year, EnPIResources.yearColName); return(TotDataPt); }
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) + ")"); } } }
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); }
/// <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(); } } }
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); }
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 } }
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; }
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; }
/// <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); } } }
/// <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); } }
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); } }
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; } }