Exemplo n.º 1
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);
        }
Exemplo n.º 2
0
        internal void addSumColumn(params string[] prepend)
        {
            string colName = Globals.ThisAddIn.rsc.GetString("unadjustedTotalColName");
            string formula = "=";
            string format  = "General";
            string prefix  = "";

            if (prepend.Length > 0)
            {
                prefix  = prepend[0];
                colName = Globals.ThisAddIn.rsc.GetString("totalAdjValuesColName");
            }

            foreach (Utilities.EnergySource es in DS.EnergySources)
            {
                if (es.Name != Globals.ThisAddIn.rsc.GetString("unadjustedTotalColName"))
                {
                    formula += Utilities.ExcelHelpers.CreateValidFormulaName(prefix + es.Name) + "+";

                    if (Utilities.ExcelHelpers.GetListColumn(SourceObject, es.Name) != null)
                    {
                        format = Utilities.ExcelHelpers.GetListColumn(SourceObject, es.Name).DataBodyRange.NumberFormat.ToString();
                    }
                }
            }

            if (formula.LastIndexOf("+") > 0)
            {
                formula = formula.Substring(0, formula.LastIndexOf("+"));
            }

            Excel.ListColumn newcol = Utilities.ExcelHelpers.AddListColumn(AdjustedData, colName);
            newcol.Range.get_Offset(1, 0).get_Resize(newcol.Range.Rows.Count - 1, 1).Value2 = formula;
            newcol.Range.get_Offset(1, 0).NumberFormat = format ?? "General";
        }
Exemplo n.º 3
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 = "@";
        }
Exemplo n.º 4
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);
        }
Exemplo n.º 5
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);
            }
        }
Exemplo n.º 6
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);
        }
Exemplo n.º 7
0
        private void AddTableColumn(System.Data.DataTable dtt, Excel.ListColumn listColumn)
        {
            Type columnType = typeof(string);

            switch (listColumn.ListDataFormat.Type)
            {
            case Excel.XlListDataType.xlListDataTypeNumber:
                columnType = typeof(decimal);
                break;

            case Excel.XlListDataType.xlListDataTypeCurrency:
                columnType = typeof(decimal);
                break;

            case Excel.XlListDataType.xlListDataTypeCheckbox:
                columnType = typeof(bool);
                break;

            case Excel.XlListDataType.xlListDataTypeDateTime:
                columnType = typeof(DateTime);
                break;

            default:
                break;
            }
            dtt.Columns.Add(listColumn.Name, columnType);
        }
Exemplo n.º 8
0
        private void updateRowFYFormula(int Intervals, Excel.ListColumn LC, int rowStart)
        {
            int j = 1, i = 0;

            foreach (Excel.Range row in LC.Range.Rows)
            {
                if (i > rowStart)
                {
                    row.Formula = "=" + "\"" + "FY" + j + "\"";
                }
                if (i > 0 && i < rowStart)
                {
                    row.Formula = "";
                }

                if (i == Intervals + rowStart)
                {
                    j       += 1;
                    i        = 0;
                    rowStart = 0;
                }

                i += 1;
            }
        }
Exemplo n.º 9
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
            }
        }
Exemplo n.º 10
0
        public ProtoContact(Excel.ListColumns columns, Excel.ListRow data)
        {
            IEnumerator   columnEnumerator = columns.GetEnumerator();
            StringBuilder address          = new StringBuilder();

            foreach (Excel.Range c in data.Range)
            {
                columnEnumerator.MoveNext();
                Excel.ListColumn col = (Excel.ListColumn)columnEnumerator.Current;
                switch (col.Name)
                {
                case "First":
                    First = ((String)c.Value ?? "").Trim();
                    break;

                case "Last":
                    Last = ((String)c.Value ?? "").Trim();
                    break;

                case "Birthday":
                    Birthday = c.Value;
                    break;

                case "Home":
                    HomePhone = ((String)c.Value ?? "").Trim();
                    break;

                case "Cell":
                    CellPhone = ((String)c.Value ?? "").Trim();
                    break;

                case "Email":
                    Email = ((String)c.Value ?? "").Trim();
                    break;

                case "Work Email":
                    WorkEmail = ((String)c.Value ?? "").Trim();
                    break;

                case "Address 1":
                    Address1 = ((String)c.Value ?? "").Trim();
                    address.Append(Address1);
                    break;

                case "Address 2":
                    Address2 = ((String)c.Value ?? "").Trim();
                    if (Address2 != "")
                    {
                        address.Append('\n');
                        address.Append(Address2);
                    }
                    break;

                default:
                    throw new Exception("Unknown Table Column " + col.Name);
                }
            }
            Address = address.ToString();
        }
Exemplo n.º 11
0
 private void updateCalenderYear(Excel.ListObject LO, Excel.ListColumn LC, int rowStart)
 {
     Excel.Range LCDate = ExcelHelpers.GetListColumn(LO, "Date").Range;
     if (LCDate == null)
     {
         LCDate = ExcelHelpers.AddListColumn(LO, "Date", 1).Range;
     }
     updateRowCYFormula(LO, LC, rowStart);
 }
Exemplo n.º 12
0
 /// <summary>
 /// Check to see if there is a column in the table
 /// </summary>
 /// <param name="tbl"></param>
 /// <param name="columnName"></param>
 /// <returns></returns>
 public static bool IsValidListColumn(Excel.ListObject tbl, string columnName)
 {
     try
     {
         Excel.ListColumn col = tbl.ListColumns[columnName];
         return(true);
     }
     catch (Exception)
     {
         return(false);
     }
 }
Exemplo n.º 13
0
        public static string GetListColumnName(Excel.ListObject LO, String ColName)
        {
            Excel.ListColumn thisColumn = null;

            thisColumn = GetListColumn(LO, ColName);

            if (thisColumn == null)
            {
                return(null);
            }

            return(thisColumn.Name);
        }
Exemplo n.º 14
0
        public static Excel.ListColumn AddColumn(Excel.ListObject listObject, string columnName)
        {
            if (null == listObject)
            {
                throw new ArgumentNullException("listObject");
            }

            if (string.IsNullOrWhiteSpace(columnName))
            {
                throw new ArgumentNullException("columnName");
            }

            Excel.ListColumn listColumn = listObject.ListColumns.Add();
            listColumn.Name = columnName;
            listColumn.Range.EntireColumn.AutoFit();

            return(listColumn);
        }
Exemplo n.º 15
0
 internal void addAdjustedEnergyIntensity()
 {
     if (DS.ProductionVariables != null)
     {
         string formula = "";
         for (int j = 0; j < DS.ProductionVariables.Count(); j++)
         {
             formula += SubtotalColumnFormula("SUM", DS.ProductionVariables[j].ToString(), yr(), AdjustedDataSheet.Name) + "+";
         }
         if (formula != "")
         {
             Excel.ListColumn newCol = SummaryData.ListColumns.Add(2);
             newCol.Name = Globals.ThisAddIn.rsc.GetString("adjustedEnergyIntensName");;
             newCol.DataBodyRange.Value2 = "=" + that() + "/" + formula.Substring(0, formula.Length - 1);
             newCol.DataBodyRange.Style  = "Comma [0]";
         }
     }
 }
Exemplo n.º 16
0
        private void updateRowCYFormula(Excel.ListObject LO, Excel.ListColumn LC, int rowStart)
        {
            int i = 0;

            foreach (Excel.Range row in LC.Range.Rows)
            {
                if (i > rowStart)
                {
                    string addrStr = dateaddr.ToString().Substring(1, dateaddr.ToString().IndexOf("$", 1) - 1).ToString() + (i + 1);
                    row.Formula = "=YEAR([Date])";
                }
                if (i > 0 && i <= rowStart)
                {
                    row.Formula = "";
                }
                i += 1;
            }
        }
Exemplo n.º 17
0
        static public Excel.ListObject AddSourceSumColumn(Excel.ListObject LO)
        {
            Excel.ListColumn sum = GetListColumn(LO, EnPIResources.totalSourceColName);
            if (sum == null)
            {
                sum = AddListColumn(LO, EnPIResources.totalSourceColName, 0);
            }

            string strTotal = "";

            foreach (Excel.ListColumn col in LO.ListColumns)
            {
                string colNameclean = ExcelHelpers.CreateValidFormulaName(col.Name);
                if (DataHelper.targetTable((object[, ])col.Range.Value2) == Constants.COLUMNTAG_DVS)
                {
                    strTotal += colNameclean + "+";
                }
            }
            sum.Range.Formula = "= " + strTotal.Substring(0, strTotal.Length - 1);
            return(LO);
        }
Exemplo n.º 18
0
        private void updateFiscialYear(string interval, Excel.ListColumn LC, int rowStart)
        {
            //string formula = "=";
            //TFS Ticket : 70984
            if (interval == Constants.INTERVAL_TYPE_DAILY)
            {
                //formula = formula + "\"" + "FY1" + "\"";
                //LC.DataBodyRange.Formula = formula;
                updateRowFYFormula(Constants.INTERVAL_TYPE_DAYS_COUNT, LC, rowStart);
            }

            if (interval == Constants.INTERVAL_TYPE_MONTHLY)
            {
                updateRowFYFormula(Constants.INTERVAL_TYPE_MONTH_COUNT, LC, rowStart);
            }

            if (interval == Constants.INTERVAL_TYPE_WEEKLY)
            {
                updateRowFYFormula(Constants.INTERVAL_TYPE_WEEK_COUNT, LC, rowStart);
            }
        }
Exemplo n.º 19
0
        public void AddPingColumn()
        {
            try
            {
                Excel.ListColumn colResults;

                if (ErrorHandler.IsValidListObject())
                {
                    Excel.ListObject tbl = Globals.ThisAddIn.Application.ActiveCell.ListObject;

                    if (ErrorHandler.IsValidListColumn(tbl, Properties.Settings.Default.Ping_Results))
                    {
                        colResults = tbl.ListColumns[Properties.Settings.Default.Ping_Results];
                    }
                    else
                    {
                        colResults      = tbl.ListColumns.Add();
                        colResults.Name = Properties.Settings.Default.Ping_Results;
                    }

                    if (ErrorHandler.IsValidListColumn(tbl, Properties.Settings.Default.Ping_ServerName))
                    {
                        Excel.ListColumn colServer = tbl.ListColumns[Properties.Settings.Default.Ping_ServerName];
                        for (int r = 1; r <= tbl.ListRows.Count; r++)
                        {
                            if (colServer.DataBodyRange.Rows[r].EntireRow.Hidden == false)
                            {
                                Excel.Range cellResult = colResults.DataBodyRange.Cells[1].Offset(r - 1, 0);
                                Excel.Range cellServer = colServer.DataBodyRange.Cells[1].Offset(r - 1, 0);
                                cellResult.Value = Ribbon.GetPingResult(cellServer.Value.ToString());
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorHandler.DisplayMessage(ex);
            }
        }
Exemplo n.º 20
0
        public static object[,] getYearArray(Excel.ListObject LO, ArrayList years)
        {
            Excel.ListColumn LCyear = GetListColumn(LO, EnPIResources.yearColName);
            int j = 0;

            object[,] LOyears = null;

            if (LCyear != null)
            {
                foreach (Excel.Range row in LCyear.Range.Rows)
                {
                    string tmp1 = row.Value2.ToString();
                    if (years.Contains(row.Value2.ToString()))
                    {
                        LOyears = DataHelper.arrayAppend(LOyears, LO.ListRows[j].Range.Value2 as object[, ]);
                    }
                    j++;
                }
            }

            return(LOyears);
        }
Exemplo n.º 21
0
        /// <summary>
        /// Cria as fórmulas do workbook passado como parâmetro
        /// </summary>
        /// <param name="eBook"></param>
        /// <param name="xWorkbook"></param>
        private Excel.Workbook createFormulas(Excel.Workbook eBook, XWorkbook xWorkbook)
        {
            foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
            {
                Excel.Worksheet eWorksheet = eBook.Sheets[xWorksheet.getName()];

                foreach (XDataTable xDataTable in xWorksheet.getDataTables())
                {
                    Excel.ListObjects eListObjects = eWorksheet.ListObjects;
                    Excel.ListObject  eListObject  = eListObjects[xDataTable.getName()];

                    foreach (XDataTableColumn sheetColumn in xDataTable.getDataTableColumns())
                    {
                        Excel.ListColumns eListColumns = eListObject.ListColumns;
                        Excel.ListColumn  eListColumn  = eListColumns[sheetColumn.getName()];

                        if (sheetColumn.getDataContent() is XTextExp)
                        {
                            Excel.Range rng     = eListColumn.DataBodyRange;
                            XTextExp    formula = (XTextExp)sheetColumn.getDataContent();
                            rng.Formula = string.Format("{0}", formula.getTextSymbol());

                            Marshal.ReleaseComObject(rng);
                        }

                        Marshal.ReleaseComObject(eListColumn);
                        Marshal.ReleaseComObject(eListColumns);
                    }

                    Marshal.ReleaseComObject(eListObject);
                    Marshal.ReleaseComObject(eListObjects);
                }

                Marshal.ReleaseComObject(eWorksheet);
            }

            return(eBook);
        }
Exemplo n.º 22
0
        internal void addEIColumn(Excel.Worksheet WS, Excel.ListObject LO, string dataListName)
        {
            string formula = "";
            string newname = rsc.GetString("buildingEnPIColName");
            string tbl     = LO.Name;
            string bsqfcol = rsc.GetString("buildingSQFColName");
            string colhat  = rsc.GetString("totalAdjValuesColName");
            string that;    //adjusted current year energy consumption
            string bsqf;    //building square feet

            that = tbl + "[[#This Row]," + ExcelHelpers.CreateValidFormulaName(colhat) + "]";
            bsqf = tbl + "[[#This Row]," + ExcelHelpers.CreateValidFormulaName(bsqfcol) + "]";

            formula = "=IFERROR(" + that + "/" + bsqf + ",0)";

            if (bsqfcol != "")
            {
                Excel.ListColumn newCol = LO.ListColumns.Add(missing);
                newCol.Name = newname;
                newCol.DataBodyRange.Formula = formula;
                newCol.DataBodyRange.Style   = "Comma";
            }
        }
Exemplo n.º 23
0
        static public Excel.ListColumn AddListColumn(Excel.ListObject LO, String ColName, params Int32[] Pos)
        {
            Excel.ListColumn thisColumn = null;

            if (Pos.Length > 0 && Pos[0] > 0)
            {
                thisColumn = LO.ListColumns.Add(Pos);
            }
            if (Pos.Length > 0 && Pos[0] < 0)
            {
                return(thisColumn);
            }
            if (Pos.Length == 0 || Pos[0] == 0)
            {
                thisColumn = LO.ListColumns.Add(missing);
            }

            if (LO.ListColumns[1].Name == "Column1")
            {
                LO.ListColumns[1].Name = "Date";
            }
            for (int i = 1; i < LO.ListColumns.Count; i++)
            {
                if (LO.ListColumns[i].Name == ColName)
                {
                    thisColumn.Name = ColName + "1";
                }
                else
                {
                    thisColumn.Name = ColName;
                }
            }

            thisColumn.Range.BorderAround(Excel.XlLineStyle.xlLineStyleNone, Excel.XlBorderWeight.xlHairline, Excel.XlColorIndex.xlColorIndexNone, missing);

            return(thisColumn);
        }
Exemplo n.º 24
0
        public void CreateRdgFile()
        {
            try
            {
                string quote         = ((char)34).ToString();
                string previousGroup = string.Empty;
                string currentGroup  = string.Empty;
                if (ErrorHandler.IsValidListObject())
                {
                    var script = new StringBuilder()
                                 .AppendLine("<?xml version=" + quote + "1.0" + quote + " encoding=" + quote + "UTF-8" + quote + "?>")
                                 .AppendLine("<RDCMan programVersion=" + quote + "2.7" + quote + " schemaVersion=" + quote + "3" + quote + ">")
                                 .AppendLine("<file>")
                                 .AppendLine("<credentialsProfiles />")
                                 .AppendLine("<properties>")
                                 .AppendLine("<expanded>True</expanded>")
                                 .AppendLine("<name>" + Scripts.AssemblyInfo.Title + "</name>")
                                 .AppendLine("</properties>");

                    Excel.ListObject tbl            = Globals.ThisAddIn.Application.ActiveCell.ListObject;
                    Excel.ListColumn colServer      = tbl.ListColumns[Properties.Settings.Default.Rdg_ServerName];
                    Excel.ListColumn colDesc        = tbl.ListColumns[Properties.Settings.Default.Rdg_Description];
                    Excel.ListColumn colComment     = tbl.ListColumns[Properties.Settings.Default.Rdg_Comment];
                    Excel.ListColumn colServerGroup = tbl.ListColumns[Properties.Settings.Default.Rdg_ServerGroup];

                    for (int r = 1; r <= tbl.ListRows.Count; r++)
                    {
                        if (colServer.DataBodyRange.Rows[r].EntireRow.Hidden == false)
                        {
                            Excel.Range cellServer      = colServer.DataBodyRange.Cells[1].Offset(r - 1, 0);
                            Excel.Range cellDesc        = colDesc.DataBodyRange.Cells[1].Offset(r - 1, 0);
                            Excel.Range cellComment     = colComment.DataBodyRange.Cells[1].Offset(r - 1, 0);
                            Excel.Range cellServerGroup = colServerGroup.DataBodyRange.Cells[1].Offset(r - 1, 0);
                            currentGroup = cellServerGroup.Value;

                            if (currentGroup != previousGroup)
                            {
                                script.AppendLine("<group>");
                                script.AppendLine("<properties>");
                                script.AppendLine("<expanded>True</expanded>");
                                script.AppendLine("<name>" + currentGroup + "</name>");
                                script.AppendLine("</properties>");
                            }

                            script.AppendLine("<server>");
                            script.AppendLine("<properties>");
                            script.AppendLine("<name>" + cellServer.Value + "</name>");
                            script.AppendLine("<displayName>" + cellServer.Value + " (" + cellDesc.Value + ")</displayName>");
                            script.AppendLine("<comment>" & cellComment.Value & "</comment>");
                            script.AppendLine("</properties>");
                            script.AppendLine("</server>");

                            if (currentGroup != colServerGroup.DataBodyRange.Cells[1].Offset(r, 0))
                            {
                                script.AppendLine("</group>");
                            }
                        }
                        previousGroup = currentGroup;
                    }

                    script.AppendLine("</file>");
                    script.AppendLine("<connected />");
                    script.AppendLine("<favorites />");
                    script.AppendLine("<recentlyUsed />");
                    script.AppendLine("</RDCMan>");

                    System.IO.File.WriteAllText(Properties.Settings.Default.Rdg_FileName, script.ToString());
                }
            }
            catch (Exception ex)
            {
                ErrorHandler.DisplayMessage(ex);
            }
        }
Exemplo n.º 25
0
        /// <summary>
        /// Cria a workbook e todos os seus objetos no formato do Excel (COM)
        /// </summary>
        /// <param name="eApp"></param>
        /// <param name="xWorkbook"></param>
        private Excel.Workbook createWorkbook(Excel.Application eApp, XWorkbook xWorkbook)
        {
            Excel.Workbook eBook = eApp.Workbooks.Add();
            eBook.Title = xWorkbook.getName();
            PrepareWorkbook(eBook);

            int numWorksheets = 1;

            foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
            {
                Excel.Worksheet eWorksheet = numWorksheets == 1
                    ? (Excel.Worksheet)eBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value)
                : (Excel.Worksheet)eBook.Worksheets.Add(Missing.Value, eBook.Worksheets[numWorksheets - 1], Missing.Value, Missing.Value);

                eWorksheet.Name = xWorksheet.getName();

                int i = 1;
                foreach (XDataTable xDataTable in xWorksheet.getDataTables())
                {
                    Excel.Range title = eWorksheet.Cells[i, 1];
                    title.Value = xDataTable.getName();
                    Excel.Font titleFont = title.Font;
                    titleFont.Bold  = true;
                    titleFont.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                    Marshal.ReleaseComObject(titleFont);
                    Marshal.ReleaseComObject(title);

                    Excel.ListObjects eListObjects = eWorksheet.ListObjects;
                    Excel.Range       bBegin       = (Excel.Range)eWorksheet.Cells[++i, 1];
                    Excel.ListObject  eListObject  = eListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, bBegin, Missing.Value, Excel.XlYesNoGuess.xlNo, Missing.Value);
                    eListObject.Name       = xDataTable.getName() ?? "";
                    eListObject.ShowTotals = false;

                    eListObject.ListRows.Add();
                    eListObject.ListRows.Add();

                    int columns = 1;
                    foreach (XDataTableColumn sheetColumn in xDataTable.getDataTableColumns())
                    {
                        Excel.ListColumns eListColumns = eListObject.ListColumns;
                        Excel.ListColumn  eListColumn  = columns == 1 ? eListColumns[1] : eListColumns.Add();
                        eListColumn.Name = sheetColumn.getName();
                        eListColumn.TotalsCalculation = Excel.XlTotalsCalculation.xlTotalsCalculationSum;
                        eListColumn.Range.EntireColumn.ColumnWidth = 14;

                        if (xDataTable.getDataTableColumns().Count == 1 &&
                            xDataTable.getDataTableColumns()[0].getDataContent() != null)
                        {
                            var datacontent = xDataTable.getDataTableColumns()[0].getDataContent();
                            if (datacontent is XDataArray)
                            {
                                var datacontentimpl = (XDataArray)datacontent;
                                var arraysymbol     = datacontentimpl.getArray();
                                int numSymbols      = 0;
                                foreach (var symbol in arraysymbol)
                                {
                                    if (eListColumn.DataBodyRange.Count < numSymbols)
                                    {
                                        eListObject.ListRows.Add();
                                    }

                                    eListColumn.DataBodyRange[numSymbols + 1] = symbol;
                                    numSymbols++;
                                }
                            }
                        }

                        columns++;

                        Marshal.ReleaseComObject(eListColumn);
                        Marshal.ReleaseComObject(eListColumns);
                    }

                    Marshal.ReleaseComObject(eListObject);
                    Marshal.ReleaseComObject(bBegin);
                    Marshal.ReleaseComObject(eListObjects);
                }

                numWorksheets++;
                Marshal.ReleaseComObject(eWorksheet);
            }

            return(eBook);
        }
Exemplo n.º 26
0
        /// <summary>
        /// Cria validações de dados nas colunas de referência a tabelas
        /// </summary>
        /// <param name="eBook">workbook correspondente do excel (COM)</param>
        /// <param name="xWorkbook">xworkbook</param>
        private Excel.Workbook createValidation(Excel.Workbook eBook, XWorkbook xWorkbook)
        {
            foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
            {
                Excel.Worksheet eWorksheet = eBook.Sheets[xWorksheet.getName()];

                foreach (XDataTable xDataTable in xWorksheet.getDataTables())
                {
                    Excel.ListObjects eListObjects = eWorksheet.ListObjects;
                    Excel.ListObject  eListObject  = eListObjects[xDataTable.getName()];

                    foreach (XDataTableColumn sheetColumn in xDataTable.getDataTableColumns())
                    {
                        Excel.ListColumns eListColumns = eListObject.ListColumns;
                        Excel.ListColumn  eListColumn  = eListColumns[sheetColumn.getName()];

                        string xreference = sheetColumn.getXReference();
                        if (xreference != null)
                        {
                            var targettable = new XDataTable();
                            var targetsheet = GetXReference(xWorkbook, xreference, ref targettable);
                            if (targettable != null)
                            {
                                var index = targettable.getKeyIndex();
                                eWorksheet   = eBook.Sheets[targetsheet.getName()];
                                eListObjects = eWorksheet.ListObjects;
                                eListObject  = eListObjects[targettable.getName()];
                                eListColumns = eListObject.ListColumns;
                                eListColumn  = eListColumns[index];

                                string rangename = targettable.getName() + index.ToString(CultureInfo.InvariantCulture);
                                eBook.Names.Add(rangename, eListColumn.DataBodyRange);

                                Excel.Name targetName = eBook.Names.Item(rangename, Type.Missing, Type.Missing);
                                string     nameLocal  = "=" + targetName.NameLocal;

                                eWorksheet   = eBook.Sheets[xWorksheet.getName()];
                                eListObjects = eWorksheet.ListObjects;
                                eListObject  = eListObjects[xDataTable.getName()];
                                eListColumns = eListObject.ListColumns;
                                eListColumn  = eListColumns[sheetColumn.getName()];
                                eListColumn.DataBodyRange.Validation.Add(Excel.XlDVType.xlValidateList,
                                                                         Excel.XlDVAlertStyle.xlValidAlertStop, Missing.Value,
                                                                         nameLocal, Missing.Value);
                            }

                            Marshal.ReleaseComObject(eListColumn);
                        }

                        Marshal.ReleaseComObject(eListColumn);
                        Marshal.ReleaseComObject(eListColumns);
                    }

                    Marshal.ReleaseComObject(eListObject);
                    Marshal.ReleaseComObject(eListObjects);
                }

                Marshal.ReleaseComObject(eWorksheet);
            }

            return(eBook);
        }