Java - How to Apply Gridlines or Borders to Excel Cells

Borders and Gridlines are not the same. Gridlines are 1-pixel gray lines that run through an entire worksheet. Their main purpose is that you can see the cell boundaries when working on the worksheet. Gridlines are invisible when you color a cell or a border. Borders can have various width, or doubling of lines, and can be colored. Borders will always print with normal printing.

In this article, I am going to introduce how to apply gridlines or borders to Excel cells in Java using Spire.XLS for Java.

• Display Gridlines on a Worksheet in Java
• Apply Borders to the Cell Range Containing Data in Java
• Apply Borders to Selected Cell Ranges in Java

Add Spire.Xls jar as dependency

If you are working on a maven project, you can include the dependency in pom.xml file using this:

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>12.11.8</version>
    </dependency>
</dependencies>

If you are not using maven, then you can find the required jar files from the zip file available in this location. Include all the jar files into the application lib folder to run the sample code given in this tutorial.

Display Gridlines on a Worksheet in Java

Gridlines are always applied to the whole worksheet or workbook, and can't be applied to specific cells or ranges. The Worksheet.setGridLinesVisible() method controls whether or not to display gridlines on a worksheet. The following are the steps to make gridlines visible using Spire.XLS for Java.

• Create a Workbook object.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a specific worksheet using Workbook.getWorksheets().get() method.
• Show gridlines on the worksheet by passing true to Worksheet.setGridLinesVisible() method as a parameter.
• Save the workbook to another Excel file using Workbook.saveToFile() method.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class ShowGridlines {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //Show gridlines on the specified worksheet
        worksheet.setGridLinesVisible(true);

        //Save the document
        workbook.saveToFile("ShowGridlines.xlsx", ExcelVersion.Version2016);
    }
}

Apply Borders to the Cell Range Containing Data in Java

Sometimes, you may only want to apply borders to the cell range where data is located. You can get the cell range containing data using Worksheet.getAllocatedRange() method and set the outside or inside borders using CellRange.borderAround() method or CellRange.borderInside() method.

• Create a Workbook object.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a specific worksheet using Workbook.getWorksheets().get() method.
• Get the cell range containing data through Worksheet.getAllocatedRange() method.
• Apply borders to the cell range using CellRange.borderAround() method and CellRange.borderInside() method.
• Save the workbook to another Excel file using Workbook.saveToFile() method.

import com.spire.xls.*;

import java.awt.*;

public class ApplyBordersToLocatedRange {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //Get the cell range containing data
        CellRange cellRange = worksheet.getAllocatedRange();

        //Apply outside borders to the range
        cellRange.borderAround(LineStyleType.MediumDashed, Color.black);

        //Apply inside borders to the range
        cellRange.borderInside(LineStyleType.Thin, Color.black);

        //Save the document
        workbook.saveToFile("ApplyBorders.xlsx", ExcelVersion.Version2016);
    }
}

Apply Borders to Selected Cell Ranges in Java

To distinguish the data of one region from that of another, you can apply different border styles to different cell ranges. Apart from the inside and outside borders, you can also set the top, bottom, left and right border of a cell or a cell range. The following steps demonstrate how to.

• Create a Workbook object.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a specific worksheet using Workbook.getWorksheets().get() method.
• Get a cell range using Worksheet.getRange().get() method.
• Get IBorder object from the cell range using CellRange.getBorders().getByBordersLineType() method.
• Set the border style and color using setLineStyle() and setColor method of the IBorder object.
• Save the workbook to another Excel file using Workbook.saveToFile() method.

import com.spire.xls.*;
import com.spire.xls.core.IBorder;

import java.awt.*;

public class ApplyBordersToSelectedRanges {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //Get cell range one
        CellRange range_one = worksheet.getRange().get("B2:J2");

        //Set borders of the range
        IBorder topBorder = range_one.getBorders().getByBordersLineType(BordersLineType.EdgeTop);
        topBorder.setLineStyle(LineStyleType.Medium);
        topBorder.setColor(Color.red);
        IBorder bottomBorder = range_one.getBorders().getByBordersLineType(BordersLineType.EdgeBottom);
        bottomBorder.setLineStyle(LineStyleType.Thin);
        bottomBorder.setColor(Color.red);

        //Get cell range two and set borders of the range
        CellRange range_two = worksheet.getRange().get("B3:B10");
        IBorder rightBorder = range_two.getBorders().getByBordersLineType(BordersLineType.EdgeRight);
        rightBorder.setLineStyle(LineStyleType.Thin);
        rightBorder.setColor(Color.red);

        //Get cell range three and set borders of the range
        CellRange range_three = worksheet.getRange().get("B11:J11");
        topBorder = range_three.getBorders().getByBordersLineType(BordersLineType.EdgeTop);
        topBorder.setLineStyle(LineStyleType.Thin);
        topBorder.setColor(Color.red);
        bottomBorder = range_three.getBorders().getByBordersLineType(BordersLineType.EdgeBottom);
        bottomBorder.setLineStyle(LineStyleType.Medium);
        bottomBorder.setColor(Color.red);

        //Save the document
        workbook.saveToFile("SetBorders.xlsx", ExcelVersion.Version2016);
    }
}