Formatting Excel Spreadsheet using Apache POI in JAVA

0 votes

My goal is to set the border for the spreadsheet data's contents while leaving the cell's other characteristics alone.

Instead of merely formatting the area of the spreadsheet that contains actual data, the code below formats(sets the border) the entire spreadsheet.

Is there a purpose behind using the formatting throughout the spreadsheet? And can this be overcome in any way?

package learning.selenium.self.begining;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class testing {

    public static void main(String[] args) throws Exception {
        File myFile = new File("TestFile.xlsx");
        Workbook myWorkbook = WorkbookFactory.create(new FileInputStream(myFile));
        Sheet mySheet = myWorkbook.getSheetAt(0);

        Iterator<Row> r = mySheet.rowIterator();
        while (r.hasNext()) {
            Row myR = r.next();
            Iterator<Cell> c = myR.cellIterator();
            while (c.hasNext()) {
                Cell myC = c.next();
                System.out.println("precessing (" + myR.getRowNum() + "," + myC.getColumnIndex() + ")");
                CellStyle s = myC.getCellStyle();
                s = myC.getCellStyle();
                s.setBorderBottom(BorderStyle.THIN);
                s.setBorderTop(BorderStyle.THIN);
                s.setBorderLeft(BorderStyle.THIN);
                s.setBorderRight(BorderStyle.THIN);
                myC.setCellStyle(s);
            }
        }

        FileOutputStream fos = new FileOutputStream(myFile);
        myWorkbook.write(fos);
        fos.close();
    }
}
Oct 27 in Others by Kithuzzz
• 20,660 points
74 views

1 answer to this question.

0 votes

Try this:

CellRangeAddress range= new CellRangeAddress(firstrow, lastrow, firstcol, lastcol); 

RegionUtil.setBorderBottom(BorderStyle.THIN, range, sheet);

I hope this helps you.

answered Oct 27 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Apache POI - watermark in Excel - different appearance in Excel and LibreOffice

There is nothing that apache poi could ...READ MORE

answered Oct 9 in Others by narikkadan
• 37,660 points
133 views
0 votes
1 answer

Is there a way to hide excel formula using Apache POI

In Excel, hiding formulae is a feature ...READ MORE

answered Oct 11 in Others by narikkadan
• 37,660 points
81 views
0 votes
1 answer

Get number of columns of a particular row in given excel using Java

Use: int noOfColumns = sh.getRow(0).getPhysicalNumberOfCells(); Or int noOfColumns = sh.getRow(0).getLastCellNum(); There ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
144 views
0 votes
0 answers
0 votes
1 answer

How to get columns from Excel files using Apache POI?

The only way to see all the ...READ MORE

answered Oct 18 in Others by narikkadan
• 37,660 points
344 views
0 votes
1 answer

Apache POI. Setup data filters in Excel

It's already enabled in Apache POI 3.7. ...READ MORE

answered Oct 21 in Others by narikkadan
• 37,660 points
79 views
0 votes
1 answer

Excel formula gives error when write using Apache-poi library in Java

I tested some sample code. The cell ...READ MORE

answered Nov 5 in Others by narikkadan
• 37,660 points
56 views
0 votes
1 answer

What is the better API to Reading Excel sheets in java - JXL or Apache POI

Here are the things where both APIs ...READ MORE

answered Oct 9 in Others by narikkadan
• 37,660 points
196 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP