How to only print only the specific column of data in excel in java using apace poi?

Question

Actually i have many column and rows rumber in excel which are hard to count.But in my program i have tried to print for column 0,1,2,3.I am able to print upto the column i can count.But when i move further i cannot get the required information about that column to print the data. Suppose the datas are located in GQ column then how can i only print the datas of GQ column??The code i have posted here prints all the columns and rows.But i need only specific of GQ column.

public class Program {
    public static final String SAMPLE_XLSX_FILE_PATH = "D:\\Book1.xlsx";
    public static void main(String[] args) throws IOException, InvalidFormatException {

      // Creating a Workbook from an Excel file (.xls or .xlsx)
        Workbook workbook = WorkbookFactory.create(new File(SAMPLE_XLSX_FILE_PATH));

        // Retrieving the number of sheets in the Workbook
        System.out.println("Workbook has " + workbook.getNumberOfSheets() + " Sheets : ");




        // 2. Or you can use a for-each loop
        System.out.println("Retrieving Sheets using for-each loop");
        for(Sheet sheet: workbook) {
            System.out.println("=> " + sheet.getSheetName());
        }


        // Getting the Sheet at index zero
        Sheet sheet = workbook.getSheetAt(0);

        // Create a DataFormatter to format and get each cell's value as String
        DataFormatter dataFormatter = new DataFormatter();



        //   using for each loop to iterate over rows and colums
        System.out.println("\n\nIterating over Rows and Columns using for-each loop\n");
        for (Row row: sheet) {
            //System.out.println(row.getCell(0));
            for(Cell cell: row) {

                String cellValue = dataFormatter.formatCellValue(cell);

                    System.out.print(cellValue + "\t");


            }
            System.out.println();
        }


        // Closing the workbook
        workbook.close();
    }
}

You just have to skip the cells until you reach column `GQ` or whichever column you want. — Antho Christen, May 24 '18 at 06:31
yes but i only get the colums when i write System.out.println(row.getCell(2)); and i need to convert the GQ column into number which is inefficient.i need to retrieve by column name — Random guy, May 24 '18 at 08:40
Do need to format it (i.e change "0" to $0.00) or merely change the type of the column to Number? — Antho Christen, May 24 '18 at 10:14

score 1 · Accepted Answer · answered May 24 '18 at 10:27

To convert the column type to numeric, try the following code

 CellStyle numericStyle = workbook.createCellStyle(); 
 numericStyle.setDataFormat(BuiltinFormats.getBuiltinFormat(2)); // 2 For Number 
 worksheet.setDefaultColumnStyle(<id_for_GQ>, numericStyle);

The complete list of BuiltinFormats formats can be seen, here

How to only print only the specific column of data in excel in java using apace poi?

1 Answers1