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++;
}
}
}
}