Пример #1
0
        public void TestShiftRows()
        {
            // Read initial file in
            HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleMultiCell.xls");

            NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(0);

            // Shift the second row down 1 and Write to temp file
            s.ShiftRows(1, 1, 1);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);

            // Read from temp file and Check the number of cells in each
            // row (in original file each row was unique)
            s = wb.GetSheetAt(0);

            Assert.AreEqual(1, s.GetRow(0).PhysicalNumberOfCells);
            ConfirmEmptyRow(s, 1);
            Assert.AreEqual(2, s.GetRow(2).PhysicalNumberOfCells);
            Assert.AreEqual(4, s.GetRow(3).PhysicalNumberOfCells);
            Assert.AreEqual(5, s.GetRow(4).PhysicalNumberOfCells);

            // Shift rows 1-3 down 3 in the current one.  This Tests when
            // 1 row is blank.  Write to a another temp file
            s.ShiftRows(0, 2, 3);
            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);

            // Read and ensure things are where they should be
            s = wb.GetSheetAt(0);
            ConfirmEmptyRow(s, 0);
            ConfirmEmptyRow(s, 1);
            ConfirmEmptyRow(s, 2);
            Assert.AreEqual(1, s.GetRow(3).PhysicalNumberOfCells);
            ConfirmEmptyRow(s, 4);
            Assert.AreEqual(2, s.GetRow(5).PhysicalNumberOfCells);

            // Read the first file again
            wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleMultiCell.xls");
            s  = wb.GetSheetAt(0);

            // Shift rows 3 and 4 up and Write to temp file
            s.ShiftRows(2, 3, -2);
            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            s  = wb.GetSheetAt(0);
            Assert.AreEqual(s.GetRow(0).PhysicalNumberOfCells, 3);
            Assert.AreEqual(s.GetRow(1).PhysicalNumberOfCells, 4);
            ConfirmEmptyRow(s, 2);
            ConfirmEmptyRow(s, 3);
            Assert.AreEqual(s.GetRow(4).PhysicalNumberOfCells, 5);
        }
Пример #2
0
        public void TestShiftRow0()
        {
            HSSFWorkbook b = new HSSFWorkbook();

            NPOI.SS.UserModel.ISheet s = b.CreateSheet();
            s.CreateRow(0).CreateCell(0).SetCellValue("TEST1");
            s.CreateRow(3).CreateCell(0).SetCellValue("TEST2");
            s.ShiftRows(0, 4, 1);
        }
Пример #3
0
        public void TestShiftWithFormulas()
        {
            HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("ForShifting.xls");

            NPOI.SS.UserModel.ISheet sheet = wb.GetSheet("Sheet1");
            Assert.AreEqual(20, sheet.LastRowNum);

            ConfirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
            ConfirmRow(sheet, 1, 2, 172, 1, "ROW(D2)", "100+B2", "COUNT(D2:E2)");
            ConfirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");

            ConfirmCell(sheet, 6, 1, 271, "200+B1");
            ConfirmCell(sheet, 7, 1, 272, "200+B2");
            ConfirmCell(sheet, 8, 1, 273, "200+B3");

            ConfirmCell(sheet, 14, 0, 0.0, "A12"); // the cell referred to by this formula will be replaced

            // -----------
            // Row index 1 -> 11 (row "2" -> row "12")
            sheet.ShiftRows(1, 1, 10);

            // Now Check what sheet looks like after move

            // no changes on row "1"
            ConfirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");

            // row "2" is now empty
            Assert.AreEqual(0, sheet.GetRow(1).PhysicalNumberOfCells);

            // Row "2" moved to row "12", and the formula has been updated.
            // note however that the cached formula result (2) has not been updated. (POI differs from Excel here)
            ConfirmRow(sheet, 11, 2, 172, 1, "ROW(D12)", "100+B12", "COUNT(D12:E12)");

            // no changes on row "3"
            ConfirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");


            ConfirmCell(sheet, 14, 0, 0.0, "#REF!");


            // Formulas on rows that weren't shifted:
            ConfirmCell(sheet, 6, 1, 271, "200+B1");
            ConfirmCell(sheet, 7, 1, 272, "200+B12"); // this one moved
            ConfirmCell(sheet, 8, 1, 273, "200+B3");

            // Check formulas on other sheets
            NPOI.SS.UserModel.ISheet sheet2 = wb.GetSheet("Sheet2");
            ConfirmCell(sheet2, 0, 0, 371, "300+Sheet1!B1");
            ConfirmCell(sheet2, 1, 0, 372, "300+Sheet1!B12");
            ConfirmCell(sheet2, 2, 0, 373, "300+Sheet1!B3");

            ConfirmCell(sheet2, 11, 0, 300, "300+Sheet1!#REF!");


            // Note - named ranges formulas have not been updated
        }
Пример #4
0
        public void TestShiftRowBreaks()
        {
            HSSFWorkbook b = new HSSFWorkbook();

            NPOI.SS.UserModel.ISheet s = b.CreateSheet();
            IRow row = s.CreateRow(4);

            row.CreateCell(0).SetCellValue("Test");
            s.SetRowBreak(4);

            s.ShiftRows(4, 4, 2);
            Assert.IsTrue(s.IsRowBroken(6), "Row number 6 should have a pagebreak");
        }
Пример #5
0
        /// <summary>
        /// 批量插入行
        /// </summary>
        /// <param name="sheet">NPOI工作表</param>
        /// <param name="rowIndex">行索引</param>
        /// <param name="rowsCount">插入行数量</param>
        /// <returns>NPOI单元行数组</returns>
        public static NPOI.SS.UserModel.IRow[] InsertRows(this NPOI.SS.UserModel.ISheet sheet, int rowIndex,
                                                          int rowsCount)
        {
            if (rowIndex <= sheet.LastRowNum)
            {
                sheet.ShiftRows(rowIndex, sheet.LastRowNum, rowsCount, true, false);
            }
            var rows = new List <NPOI.SS.UserModel.IRow>();

            for (var i = 0; i < rowsCount; i++)
            {
                var row = sheet.CreateRow(rowIndex + i);
                rows.Add(row);
            }
            return(rows.ToArray());
        }
Пример #6
0
        public void TestShiftRows()
        {
            HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("46445.xls");


            NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);

            //verify existing hyperlink in A3
            ICell      cell1 = sheet.GetRow(2).GetCell(0);
            IHyperlink link1 = cell1.Hyperlink;

            Assert.IsNotNull(link1);
            Assert.AreEqual(2, link1.FirstRow);
            Assert.AreEqual(2, link1.LastRow);

            //assign a hyperlink to A4
            HSSFHyperlink link2 = new HSSFHyperlink(HyperlinkType.Document);

            link2.Address = ("Sheet2!A2");
            ICell cell2 = sheet.GetRow(3).GetCell(0);

            cell2.Hyperlink = (link2);
            Assert.AreEqual(3, link2.FirstRow);
            Assert.AreEqual(3, link2.LastRow);

            //move the 3rd row two rows down
            sheet.ShiftRows(sheet.FirstRowNum, sheet.LastRowNum, 2);

            //cells A3 and A4 don't contain hyperlinks anymore
            Assert.IsNull(sheet.GetRow(2).GetCell(0).Hyperlink);
            Assert.IsNull(sheet.GetRow(3).GetCell(0).Hyperlink);

            //the first hypelink now belongs to A5
            IHyperlink link1_shifted = sheet.GetRow(2 + 2).GetCell(0).Hyperlink;

            Assert.IsNotNull(link1_shifted);
            Assert.AreEqual(4, link1_shifted.FirstRow);
            Assert.AreEqual(4, link1_shifted.LastRow);

            //the second hypelink now belongs to A6
            IHyperlink link2_shifted = sheet.GetRow(3 + 2).GetCell(0).Hyperlink;

            Assert.IsNotNull(link2_shifted);
            Assert.AreEqual(5, link2_shifted.FirstRow);
            Assert.AreEqual(5, link2_shifted.LastRow);
        }
Пример #7
0
        /// <summary>
        /// 移除行
        /// </summary>
        /// <param name="sheet">NPOI工作表</param>
        /// <param name="startRowIndex">起始行索引</param>
        /// <param name="endRowIndex">结束行索引</param>
        public static int RemoveRows(this NPOI.SS.UserModel.ISheet sheet, int startRowIndex, int endRowIndex)
        {
            var span = endRowIndex - startRowIndex + 1;

            sheet.RemoveMergedRegions(startRowIndex, endRowIndex, null, null);
            sheet.RemovePictures(startRowIndex, endRowIndex, null, null);
            for (var i = endRowIndex; i >= startRowIndex; i--)
            {
                var row = sheet.GetRow(i);
                sheet.RemoveRow(row);
            }
            if (endRowIndex + 1 <= sheet.LastRowNum)
            {
                sheet.ShiftRows(endRowIndex + 1, sheet.LastRowNum, -span, true, false);
                sheet.MovePictures(endRowIndex + 1, null, null, null, moveRowCount: -span);
            }
            return(span);
        }
Пример #8
0
        public void TestShiftWithComments()
        {
            HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("comments.xls");

            NPOI.SS.UserModel.ISheet sheet = wb.GetSheet("Sheet1");
            Assert.AreEqual(3, sheet.LastRowNum);

            // Verify comments are in the position expected
            Assert.IsNotNull(sheet.GetCellComment(0, 0));
            Assert.IsNull(sheet.GetCellComment(1, 0));
            Assert.IsNotNull(sheet.GetCellComment(2, 0));
            Assert.IsNotNull(sheet.GetCellComment(3, 0));

            String comment1 = sheet.GetCellComment(0, 0).String.String;

            Assert.AreEqual(comment1, "comment top row1 (index0)\n");
            String comment3 = sheet.GetCellComment(2, 0).String.String;

            Assert.AreEqual(comment3, "comment top row3 (index2)\n");
            String comment4 = sheet.GetCellComment(3, 0).String.String;

            Assert.AreEqual(comment4, "comment top row4 (index3)\n");

            // Shifting all but first line down to Test comments shifting
            sheet.ShiftRows(1, sheet.LastRowNum, 1, true, true);
            MemoryStream outputStream = new MemoryStream();

            wb.Write(outputStream);

            // Test that comments were shifted as expected
            Assert.AreEqual(4, sheet.LastRowNum);
            Assert.IsNotNull(sheet.GetCellComment(0, 0));
            Assert.IsNull(sheet.GetCellComment(1, 0));
            Assert.IsNull(sheet.GetCellComment(2, 0));
            Assert.IsNotNull(sheet.GetCellComment(3, 0));
            Assert.IsNotNull(sheet.GetCellComment(4, 0));

            String comment1_shifted = sheet.GetCellComment(0, 0).String.String;

            Assert.AreEqual(comment1, comment1_shifted);
            String comment3_shifted = sheet.GetCellComment(3, 0).String.String;

            Assert.AreEqual(comment3, comment3_shifted);
            String comment4_shifted = sheet.GetCellComment(4, 0).String.String;

            Assert.AreEqual(comment4, comment4_shifted);

            // Write out and read back in again
            // Ensure that the changes were persisted
            wb    = new HSSFWorkbook(new MemoryStream(outputStream.ToArray()));
            sheet = wb.GetSheet("Sheet1");
            Assert.AreEqual(4, sheet.LastRowNum);

            // Verify comments are in the position expected after the shift
            Assert.IsNotNull(sheet.GetCellComment(0, 0));
            Assert.IsNull(sheet.GetCellComment(1, 0));
            Assert.IsNull(sheet.GetCellComment(2, 0));
            Assert.IsNotNull(sheet.GetCellComment(3, 0));
            Assert.IsNotNull(sheet.GetCellComment(4, 0));

            comment1_shifted = sheet.GetCellComment(0, 0).String.String;
            Assert.AreEqual(comment1, comment1_shifted);
            comment3_shifted = sheet.GetCellComment(3, 0).String.String;
            Assert.AreEqual(comment3, comment3_shifted);
            comment4_shifted = sheet.GetCellComment(4, 0).String.String;
            Assert.AreEqual(comment4, comment4_shifted);
        }
        /// <summary>
        /// Inserta los valores a exportar de un grupo de hechos relacionados a un miembro de la dimensión de tipo de pasivo
        /// En caso de que sea alguno de los miembros que totalizan entonces no se insertan renglones
        /// </summary>
        /// <param name="hojaAExportar"></param>
        /// <param name="renglonTipoPasivo"></param>
        /// <param name="tipoPasivoActual"></param>
        /// <param name="instancia"></param>
        /// <param name="plantillaDocumento"></param>
        private void ExportarGrupoTipoPasivo(NPOI.SS.UserModel.ISheet hojaAExportar, int renglonTipoPasivo, string tipoPasivoActual, DocumentoInstanciaXbrlDto instancia,
                                             IDefinicionPlantillaXbrl plantillaDocumento)
        {
            var hechosDeTipoPasivo = ObtenerHechosPorDimensionYMiembro(instancia, plantillaDocumento, _idDimensionTipoPasivo, tipoPasivoActual);
            int iCol     = _columnaInicioDatos;
            int iRenglon = renglonTipoPasivo;

            string[] listaPrimarios = null;
            if (_miembrosBancarios.Contains(tipoPasivoActual))
            {
                listaPrimarios = _elementosPrimariosBancarios;
            }
            if (_miembrosBursatiles.Contains(tipoPasivoActual))
            {
                listaPrimarios = _elementosPrimariosBursatiles;
            }
            if (_miembrosOtros.Contains(tipoPasivoActual))
            {
                listaPrimarios = _elementosPrimariosOtros;
            }
            if (_idItemMiembroGranTotal.Equals(tipoPasivoActual))
            {
                listaPrimarios = _elementosPrimariosGranTotal;
            }

            //aplica para renglón primarios
            if (_miembrosTipoPasivoTotales.Contains(tipoPasivoActual))
            {
                int iMiembroSubtabla = 0;
                foreach (var elementoPrimario in listaPrimarios)
                {
                    if (_elementosPrimariosTotal.Contains(elementoPrimario))
                    {
                        IList <HechoDto> listaHechos = null;
                        if (elementoPrimario.Equals(_idConceptoSubtabla))
                        {
                            listaHechos = ObtenerHechosPorElementoPrimarioYSecuencia(instancia, hechosDeTipoPasivo, elementoPrimario, null);
                            listaHechos = FiltrarHechosPorDimensionYMiembro(instancia, listaHechos, _idDimensionIntervalo, _miembrosIntervaloDeTiempo[iMiembroSubtabla++]);
                        }
                        else
                        {
                            listaHechos = ObtenerHechosPorElementoPrimarioYSecuencia(instancia, hechosDeTipoPasivo, elementoPrimario, null);
                        }
                        if (listaHechos.Count > 0)
                        {
                            ExcelUtil.AsignarValorCelda(hojaAExportar, renglonTipoPasivo, iCol,
                                                        listaHechos[0].Valor, CellType.Numeric, null);
                        }
                    }
                    iCol++;
                }
            }
            else
            {
                //Organizar por secuencia
                var secuenciasEnHechos = OrganizarHechosPorSecuencia(instancia, hechosDeTipoPasivo);
                if (secuenciasEnHechos.Count > 0)
                {
                    hojaAExportar.ShiftRows(iRenglon + 1, hojaAExportar.LastRowNum, secuenciasEnHechos.Count);
                    iRenglon++;
                    foreach (var secuencia in secuenciasEnHechos.Keys)
                    {
                        var renglon = hojaAExportar.CreateRow(iRenglon);
                        iCol = _columnaInicioDatos;
                        int iMiembroSubtabla = 0;
                        foreach (var elementoPrimario in listaPrimarios)
                        {
                            IList <HechoDto> listaHechos = null;

                            if (elementoPrimario.Equals(_idConceptoSubtabla))
                            {
                                listaHechos = ObtenerHechosPorElementoPrimarioYSecuencia(instancia, secuenciasEnHechos[secuencia], elementoPrimario, null);
                                listaHechos = FiltrarHechosPorDimensionYMiembro(instancia, listaHechos, _idDimensionIntervalo, _miembrosIntervaloDeTiempo[iMiembroSubtabla++]);
                            }
                            else
                            {
                                listaHechos = ObtenerHechosPorElementoPrimarioYSecuencia(instancia, secuenciasEnHechos[secuencia], elementoPrimario, null);
                            }

                            if (listaHechos.Count > 0)
                            {
                                var cellType = CellType.String;
                                if (listaHechos[0].EsNumerico)
                                {
                                    cellType = CellType.Numeric;
                                }

                                ExcelUtil.AsignarValorCelda(hojaAExportar, iRenglon, iCol,
                                                            listaHechos[0].Valor, cellType, null);
                            }
                            iCol++;
                        }
                        iRenglon++;
                    }
                }
            }
        }