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); }
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); }
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 }
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"); }
/// <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()); }
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); }
/// <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); }
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++; } } } }