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, 2022 in Others by Kithuzzz
• 38,020 points
2,127 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, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Writing large Excel in Java causing high CPU usage using apache-poi

Giving SXSSFWorkbook a window size appears to ...READ MORE

answered Jan 29, 2023 in Others by narikkadan
• 63,720 points
1,538 views
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, 2022 in Others by narikkadan
• 63,720 points
2,047 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, 2022 in Others by narikkadan
• 63,720 points
1,125 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, 2022 in Others by narikkadan
• 63,720 points
1,951 views
0 votes
1 answer
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, 2022 in Others by narikkadan
• 63,720 points
3,299 views
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, 2022 in Others by narikkadan
• 63,720 points
5,078 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, 2022 in Others by narikkadan
• 63,720 points
1,852 views
0 votes
1 answer

How to create page borders using Apache POI in excel files with Java?

Microsoft Excel cannot do this. Libreoffice Calc ...READ MORE

answered Dec 13, 2022 in Others by narikkadan
• 63,720 points
944 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