How to hide the following Un-used rows in Excel sheet using Java Apache POI?

I am populating a template Excel sheet, with the data from the database :

for (Map<String, Object> resultRow : dbResults) { if ((currentRow = sheet.getRow(currentDataRow)) == null) { currentRow = sheet.createRow(currentDataRow); // Creates a new row. } //currentRow.getRowStyle().setHidden(false); for (int i = 0; i < resultColumns; i++) { currentCell = currentRow.getCell(i, Row.CREATE_NULL_AS_BLANK); setCellValue(currentCell, resultRow.get(dbcolumnNames[i])); } currentDataRow += 1; } // How to hide all empty/Un-used rows following currentDataRow ?

Aim to Achieve :

  • I want that the Un-Used rows following the populated rows should be hidden ?
  • All Populated rows must be visible.
  • Eg: If 1st 100 data rows are filled, then rows following 101 and onward should be hidden.

Please, help !!

--------------Solutions-------------

POI recognizes the number of logical rows in your sheet when you create it. So as you populate it with 100 rows, it will create 100 records. The rest will appear when you open the XLS in Excel with the default layout - but these are not POI records.

You'll have to create some dummy rows after your last data record like this

for (int i=currentDataRow ;i<65000;i++)
sheet.createRow(i);

Create a cell style, and set it to hidden

CellStyle hiddenstyle = workBook.createCellStyle();
hiddenstyle.setHidden(true);

Set this style for all rows from your last row to end of sheet

while (rows.hasNext()){
Row row1 = rows.next ();
row1.setRowStyle(hiddenstyle);

}

row.setRowStye() and row.getRowStye() are present in poi-3.8-beta4.

Row r = sheet.getRow(indexRow);
if ( r!=null ) {
r.setZeroHeight(true);
}

Category:java Time:2011-08-03 Views:1

Related post

  • how to delete rows in excel sheet using php 2010-01-13

    Is it possible to delete rows in excel sheet using PHP ? Can anyone guide me. Regards, Mithun --------------Solutions------------- Only works if your PHP is hosted under Windows. This should be a good start if PHPExcel* doesn't work. Open $this->e

  • export gridview rows to excel sheet 2010-11-21

    I had task in my project to export grid view rows to excel sheet I serached and I had code and I did it but this class export only current page from grid view so I want also to export all gridview rows. Class: using System; using System.Data; using S

  • how to insert only new row from excel sheet to database 2011-08-17

    i am working on importing data from excel sheet to database.i am able to successfully import the data from excel to database.i want to know how can i add only new row from excel sheet to database without adding previous data. for (int i = 0; i < e

  • How to apply background color for the rows in excel sheet using Apache POI? 2011-09-07

    I am using Apache POI for exporting data into excel sheet. it works fine. the problem is i need apply yellow background color for few rows in the excel sheet while generating the excel sheet. please hellp me how to apply background color for the rows

  • delete rows in excel sheet using javascript 2010-01-29

    I am trying to edit an excel sheet using activex object in Javascript. The function below opens an excel sheet and creates a few entries. function test() { var ExcelApp = new ActiveXObject("Excel.Application"); var ExcelSheet = ExcelApp.Workbooks.Ope

  • how to read data from the 3 row from excel sheet and convert to datatable 2010-07-14

    i am able read the data fine. but now i have an issue. i need to read data staring from 3 row in an excel sheet. and then convert the data to datatable how can i set the row postion to start reading data from an excel sheet[to reading starting from 3

  • How to add colour for the columns and rows in excel file in java? 2011-11-17

    Am working on a application to read the values from database and store it in a excel file . Now i need to add different color to the different columns and rows in the excel sheet . Please help me in this . Thanks in advance . /* * To change this temp

  • Create tables in word for each row in excel sheet 2011-12-19

    I'm creating a word report and all my data is on an excel sheet. The sheet is like this: ID Name1 Name2 Name3 Name4 1 blah blah blah blah 2 blah blah blah blah 3 blah blah blah blah And what I want is in the word document, to have one table for each

  • How do I add rows to Excel sheet on Windows Phone 7 2014-04-21

    Anyone know how to insert rows and/or columns to a worksheet on Windows Phone 7? --------------Solutions------------- I believe you cannot insert rows/columns in between existing rows and columns. Hello Neil, Thank you for your post! Unfortunately Ex

  • Copy Listview items to new row in excel sheet 2014-10-27

    Hi, The following code copies the item in listview to excel sheet, but each time it past the data on the same rows, I wish to copy the items in listview and copy those on excel sheet in a new row, thus the earlier data should remain there: Private Su

  • Solved: Importing selected rows from Excel sheet one to Excel sheet two 2013-11-27

    Hi: Using excel 2010 I have a master list of items,product code, and price. I would like to be able to use a separate worksheet and be able to use a drop down menu/list to select and add individually to my worksheet. Example, there mmight be 1000 ite

  • Insert multiple blank rows in excel sheet using vba macro 2014-03-04

    Okay I know nothing about writing code and am hoping that someone can help me. I have an excel file with 2460 rows of data that needs 29 blank rows between each data value. I can get the program to insert the rows individually using the code listed b

  • .Net Code for deleting rows in excel sheet became very slow after moving to Excel 2010. 2014-07-10

    We have a .Net application that opens an excel file and deletes some rows based on our business logic. this is working fine with excel 2007. When we moved to Excel 2010, the same application became very slow. We double checked by running on a differe

  • How to insert rows in an Excel sheet into SQL Server 2005 or oracle 2010-05-30

    How to insert rows in an Excel sheet into SQL Server 2005 or oracle ? For example if I have 5000 rows in Excel sheet how can I insert these rows into a table in any database --------------Solutions------------- And for Oracle (see JYelton's answers f

  • Is this simple to copy pasting rows with Excel VBA? 2011-12-23

    I have columns in 3 excel sheets like this: Sheet1 ColA ColB 5 4 5 5 45 56 56 56 Sheet2 ColA ColB 53 24 55 55 Sheet3 ColA ColB 45 56 56 56 3 4 I want to copy paste columns from sheet 2 and 3 to sheet 1 and I am not sure of the row numbers as they can

  • How To handle Null Row using Apache POI? 2012-02-07

    I am using Apache POI to read xlsx file,it works well.I have question to you when row is found null,how i'm able to handle it.my file contain 500 row,but it show 105667 row,rest of row found null. used code: import java.io.File; import java.io.FileIn

  • How do I freeze multiple rows at the top of excel sheet? 2012-05-29

    I've been trying to freeze multiple rows in excel sheet without success. Each time I tried, it's only the first row that freezes. Please advise how this can be done. Thanks. --------------Solutions------------- Hello, when you activate the Freeze com

  • To insert each row into ms access database from current excel sheet 2013-02-16

    Hi all Please anybody can help me , to write the macros to insert each row from excel sheet to msaccess database . I have to insert from current rows at the end of the rowcount having data. Some of the cell would be formated text or preceded by by ap

  • datatable plugin - show and hide more information about a row 2011-05-01

    datatable plugin - show and hide more information about a row issue : i want to get that more information by ajax in fnFormatDetails function.but i don't know how do it.i try to put $.ajax in fnFormatDetails function but it seems it have delay to pas

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.753 (s). 13 q(s)