Ejemplo n.º 1
0
        /// <summary>
        /// Adiciona uma nova worksheet a uma workbook existente
        /// </summary>
        /// <param name="xWorkbook">workbook</param>
        /// <param name="xWorksheet">nova worksheet</param>
        private void updateWorkbook(XWorkbook xWorkbook, XWorksheet xWorksheet)
        {
            List <XWorksheet> xWorksheets = xWorkbook.getWorksheets();

            xWorksheets.Add(xWorksheet);
            xWorkbook.setWorksheets(xWorksheets);
        }
Ejemplo n.º 2
0
 private XWorksheet GetXReference(XWorkbook xWorkbook, string xreference, ref XDataTable targettable)
 {
     foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
     {
         targettable = xWorksheet.getDataTables().FirstOrDefault(d => d.getName().Equals(xreference));
         if (targettable != null)
         {
             return(xWorksheet);
         }
     }
     targettable = null;
     return(null);
 }
Ejemplo n.º 3
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);
        }
Ejemplo n.º 4
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);
        }
Ejemplo n.º 5
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);
        }
Ejemplo n.º 6
0
 /// <summary>
 /// Retorna a tabela correspondente a determinado classifier
 /// </summary>
 /// <param name="xWorkbook">workbook onde se encontra tabela</param>
 /// <param name="classifier">classifier correspondente a tabela</param>
 /// <returns>Se encontrar, retorna a referência para a tabela, caso contrário, retorna null</returns>
 private XDataTable getTargetTable(XWorkbook xWorkbook, CoreClassifier classifier)
 {
     return(xWorkbook.getWorksheets()
            .SelectMany(xWorksheet => xWorksheet.getDataTables())
            .FirstOrDefault(xDataTable => xDataTable.getName().Equals(classifier.getName())));
 }