Displaying items by tag: xls java Cells

Tuesday, 24 March 2020 10:18

Insert Subscript and Superscript in Excel in Java

This article demonstrates how to insert subscript and superscript in an Excel document using Spire.XLS for Java.

import com.spire.xls.*;

import java.awt.*;

public class InsertSubscriptSuperscript {

    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();
        
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Insert text to B2 and D2
        sheet.getCellRange("B2").setText("This is an example of Subscript:");
        sheet.getCellRange("D2").setText("This is an example of Superscript:");

        //Insert text to B3 and apply subscript effect
        CellRange range = sheet.getCellRange("B3");
        range.getRichText().setText("R100-0.06");
        ExcelFont font = workbook.createFont();
        font.isSubscript(true);
        font.setColor(Color.red);
        range.getRichText().setFont(4, 8, font);

        //Insert text to D3 and apply superscript effect
        range = sheet.getCellRange("D3");
        range.getRichText().setText("a2 + b2 = c2");
        font = workbook.createFont();
        font.isSuperscript(true);
        range.getRichText().setFont(1, 1, font);
        range.getRichText().setFont(6, 6, font);
        range.getRichText().setFont(11, 11, font);

        //Auto fit column width
        sheet.getAllocatedRange().autoFitColumns();
        
        //Save the docuemnt
        workbook.saveToFile("output/SubSuperScript.xlsx", ExcelVersion.Version2016);
    }
}

Insert Subscript and Superscript in Excel in Java

Published in Cells

Monday, 16 March 2020 08:19

Freeze or Unfreeze Excel Rows and Columns in Java

This article demonstrates how to freeze or unfreeze rows and columns in Excel using Spire.XLS for Java.

Freeze top row

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

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

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

//Freeze top row
sheet.freezePanes(2,1);

//Save to file
workbook.saveToFile("FreezeTopRow.xlsx", ExcelVersion.Version2016);

Freeze or Unfreeze Excel Rows and Columns in Java

Freeze fisrt column

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

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

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

//Freeze frist column
sheet.freezePanes(1,2);

//Save to file
workbook.saveToFile("FreezeFirstColumn.xlsx", ExcelVersion.Version2016);

Freeze or Unfreeze Excel Rows and Columns in Java

Freeze few rows and columns

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

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

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

//Freeze the second row and the second column
sheet.freezePanes(3,3);

//Save to file
workbook.saveToFile("FreezeFewRowsAndColumns.xlsx", ExcelVersion.Version2016);

Freeze or Unfreeze Excel Rows and Columns in Java

Unfreeze panes

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

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

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

//Unfreeze panes
sheet.removePanes();

//Save to file
workbook.saveToFile("UnfreezePanes.xlsx", ExcelVersion.Version2016);

Published in Cells

Wednesday, 12 July 2023 07:31

Java: Copy Cell Range in Excel

Copying cell ranges is a fundamental and highly beneficial function in spreadsheet management, which empowers users to effortlessly duplicate a range of cells, including data, formatting, and formulas, to a specified position. With it, users can efficiently copy identical content throughout their spreadsheets, while mitigating the potential for input errors. Importantly, the relative relationships between cells are preserved when copying cell ranges, ensuring the consistency of the copied data with the original. As a result, this feature holds immense value for tasks such as file backups and template creation, making it an indispensable tool in spreadsheet. This article will demonstrate how to copy a cell range within a worksheet or between two worksheets in a single Excel file by using Spire.XLS for Java.

Copy a Specific Cell Range within a Worksheet
Copy a Specific Cell Range between Worksheets

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

Package Manager

<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>14.11.0</version>
    </dependency>
</dependencies>

Copy a Specific Cell Range within a Worksheet

Spire.XLS for Java provides Worksheet.copy() method, which supports copying a specific cell range in the same worksheet. The following are detailed steps.

Create a new Workbook object.
Load an Excel file from disk using Workbook.loadFromFile() method.
Get the first worksheet of this file by using Workbook.getWorksheets().get() method.
Get the source range and target range of the first sheet using Worksheet.getCellRange() method.
Copy the specific cell range within a worksheet by calling Worksheet.copy() method.
Finally, specify the output path and save the result file using Workbook.saveToFile() method.

Java

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

public class CopyRow {
    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook wb = new Workbook();

        //Load a sample Excel file from disk
        wb.loadFromFile("sample.xlsx", ExcelVersion.Version2013);

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

        //Get the source range and target range
        CellRange sourceRange = sheet.getCellRange("A1:E1");
        CellRange destRange = sheet.getCellRange("A10:E10");

        //Copy a specific cell range within a worksheet
        sheet.copy (sourceRange,destRange,true);

        //Save the result file
        wb.saveToFile("CopyRangeWithinSheet.xlsx", ExcelVersion.Version2013);
        wb.dispose();
    }
}

Java: Copy Cell Range in Excel

Copy a Specific Cell Range between Worksheets

Spire.XLS for Java library also allows you to copy cell range from one sheet to another sheet effortlessly. The following are the steps to duplicate cell range between different worksheets.

Create a new Workbook object.
Load an Excel file from disk using Workbook.loadFromFile() method.
Get the first and second worksheets of this file by using Workbook.getWorksheets().get() method.
Get the source range and target range using Worksheet.getCellRange() method.
Copy the specific cell range from sheet1 to sheet2 by calling Worksheet.copy() method.
Auto fit the column width in sheet2 by using Worksheet.autoFitColumn() method
Finally, specify the output path and save the result file using Workbook.saveToFile() method.

Java

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

public class CopyRow {
    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook wb = new Workbook();

        //Load a sample Excel file from disk
        wb.loadFromFile("sample.xlsx", ExcelVersion.Version2013);

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

        //Get the second worksheet 
        Worksheet sheet2 = wb.getWorksheets().get(1);

        //Get the source range and target range
        CellRange sourceRange = sheet1.getCellRange("A1:E1");
        CellRange destRange = sheet2.getCellRange("A1:E1");

        //Copy a specific cell range from sheet1 to sheet2
        sheet1.copy (sourceRange,destRange,true);

        //Auto fit column width in sheet 2
        for (int i = 0; i < 8; i++) {
            sheet2.autoFitColumn(i+1);
        }

        //Save the result file
        wb.saveToFile("CopyRangeBetweenSheets.xlsx", ExcelVersion.Version2013);
        wb.dispose();
    }
}

Java: Copy Cell Range in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Cells

Friday, 13 May 2022 07:13

Java: Add Cell Borders in Excel

In Excel, the border is a line around a cell or a range of cells. Adding cell borders can help distinguish different sections, highlight summarized values, or separate specific data in a worksheet. In this article, you will learn how to add borders with different line styles and colors to Excel cells using Spire.XLS for Java.

Install Spire.XLS for Java

First, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

Package Manager

<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>14.11.0</version>
    </dependency>
</dependencies>

Add Cell Borders in Excel

Spire.XLS for Java provides the CellRange.borderInside() and CellRange.borderAround() methods to add inside and around borders to a specified cell range. To add top, bottom, left, right and diagonal borders, you can use the BordersCollection.getByBordersLineType(BordersLineType index).setLineStyle (LineStyleType lineStyleType) method. The detailed steps are as follows.

Create a Workbook instance.
Load a sample Excel document using Workbook.loadFromFile() method.
Get a specified worksheet using Workbook.getWorksheets().get() method.
Get a specified cell range using Worksheet.getCellRange() method.
Add inside and around borders with specific line styles and colors to the specified range using CellRange.borderInside(LineStyleType borderLine, Color borderColor) and CellRange.borderAround (LineStyleType borderLine, Color borderColor) methods.
Get a borders collection that represents the borders of the specified cell range using CellRange.getBorders() method.
Set the border line style and color for the specified range using BordersCollection.setLineStyle() and BordersCollection.setColor() methods.
Set the diagonal border line style for the specified range using BordersCollection.getByBordersLineType(BordersLineType index).setLineStyle (LineStyleType lineStyleType) method.
Save the document to another file using Workbook.saveToFile() method.

Java

import com.spire.xls.*;
import java.awt.*;

public class AddBorders {
    public static void main(String[] args){
        //Create a workbook instance
        Workbook workbook = new Workbook();

        //Load a sample Excel document
        workbook.loadFromFile("D:\\Files\\Input.xlsx");

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

        //Add border to range "B2:F9"
        CellRange range1= sheet.getCellRange("B2:F9");
        range1.borderInside(LineStyleType.Thin, Color.BLUE);
        range1.borderAround(LineStyleType.Medium, Color.BLUE);

        //Add border to range "B14:F14"
        CellRange range2= sheet.getCellRange("B14:F14");
        range2.getBorders().setLineStyle(LineStyleType.Double);
        range2.getBorders().setColor(Color.RED);

        //Set the diagonal border line style for range "B14:F14"
        range2.getBorders().getByBordersLineType(BordersLineType.DiagonalDown).setLineStyle(LineStyleType.None);
        range2.getBorders().getByBordersLineType(BordersLineType.DiagonalUp).setLineStyle(LineStyleType.None);
        
        //Save the file
        workbook.saveToFile("SetBorder.xlsx", ExcelVersion.Version2010);
    }
}

Java: Add Cell Borders in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Cells

Wednesday, 19 February 2020 08:35

Align Text in Excel Cells in Java

This article demonstrates how to align text in Excel cells using Spire.XLS for Java.

import com.spire.xls.*;

public class AlignText {
    public static void main(String[] args){
        //Create a workbook
        Workbook workbook = new Workbook();

        //Load an Excel file
        workbook.loadFromFile("Sample.xlsx");

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

        //Set the vertical alignment to Top
        sheet.getCellRange("B1").getCellStyle().setVerticalAlignment(VerticalAlignType.Top);
        //Set the vertical alignment to Center
        sheet.getCellRange("B2").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        //Set the vertical alignment to Bottom
        sheet.getCellRange("B3").getCellStyle().setVerticalAlignment(VerticalAlignType.Bottom);

        //Set the horizontal alignment to General
        sheet.getCellRange("B4").getCellStyle().setHorizontalAlignment(HorizontalAlignType.General);
        //Set the horizontal alignment to Left
        sheet.getCellRange("B5").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Left);
        //Set the horizontal alignment to Center
        sheet.getCellRange("B6").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
        //Set the horizontal alignment to Right
        sheet.getCellRange("B7").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Right);

        //Set the text orientation by using setRotation method
        sheet.getCellRange("B8").getCellStyle().setRotation(45);
        sheet.getCellRange("B9").getCellStyle().setRotation(90);

        //Set the text indentation
        sheet.getCellRange("B10").getCellStyle().setIndentLevel(6);

        //Set the text direction
        sheet.getCellRange("B11").getCellStyle().setReadingOrder(ReadingOrderType.LeftToRight);

        //Set the row height 
        sheet.getCellRange("B8").setRowHeight(60);
        sheet.getCellRange("B9").setRowHeight(60);

        //Save the result file
        workbook.saveToFile("AlignText.xlsx", ExcelVersion.Version2010);
    }
}

Output:

Align Text in Excel Cells in Java

Published in Cells

Friday, 25 March 2022 09:24

Java: Appy Fonts to Excel Cells

As you create or review a worksheet, you may want to change the font style in some cells to make them stand out. For example, you can apply a different font type, font color, and font size to text, as well as make text bold. In this article, you will learn how to apply font styles to Excel cells using Spire.XLS for Java.

Apply Different Fonts to Different Cells
Apply Multiple Fonts to a Single Cell

Install Spire.XLS for Java

First of all, you’re required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

Package Manager

<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>14.11.0</version>
    </dependency>
</dependencies>

Apply Different Fonts to Different Cells

Spire.XLS provides the ExcelFont class which you can use to set or change the font name, color, size, and style in a cell easily. The following are the steps to apply a font style to a specific cell using Spire.XLS for Java.

Create a Workbook object.
Get a specific worksheet using Workbook.getWorksheets().get() method.
Get a specific cell using Worksheet.getCellRange() method.
Set the value of the cell using CellRange.setText() method.
Set the font name, color, size and style of the cell value using setFontName(), setFontColor(), setFontSize(), isBlod() methods under the ExcelFont object.
Save the workbook to an Excel file using Workbook.saveToFile() method.

Java

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

import java.awt.*;

public class ApplyFontToCell {

    public static void main(String[] args) {

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

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

        //Set font name
        sheet.getCellRange("B1").setText("Font name: Comic Sans MS");
        sheet.getCellRange("B1").getCellStyle().getExcelFont().setFontName("Comic Sans MS");

        //Set font size
        sheet.getCellRange("B2").setText("Font size: 20");
        sheet.getCellRange("B2").getCellStyle().getExcelFont().setSize(20);

        //Set font color
        sheet.getCellRange("B3").setText("Font color: Blue");
        sheet.getCellRange("B3").getCellStyle().getExcelFont().setColor(Color.blue);

        //Make text bold
        sheet.getCellRange("B4").setText("Font style: Bold");
        sheet.getCellRange("B4").getCellStyle().getExcelFont().isBold(true);

        //Make text italic
        sheet.getCellRange("B5").setText("Font style: Italic");
        sheet.getCellRange("B5").getCellStyle().getExcelFont().isItalic(true);

        //Underline text
        sheet.getCellRange("B6").setText("Font style: Underline");
        sheet.getCellRange("B6").getCellStyle().getExcelFont().setUnderline(FontUnderlineType.Single);

        //Strikethrough text
        sheet.getCellRange("B7").setText("Font style: Strikethrough");
        sheet.getCellRange("B7").getCellStyle().getExcelFont().isStrikethrough(true);

        //Save the result file
        workbook.saveToFile("output/ApplyFontToCell.xlsx", ExcelVersion.Version2016);
    }
}

Java: Appy Fonts to Excel Cells

Apply Multiple Fonts to a Single Cell

Mixing different font styles can help you emphasize some specific characters in a cell. The following are the steps to apply multiple fonts in a cell using Spire.XLS for Java.

Create a Workbook object.
Get a specific worksheet using Workbook.getWorksheets().get() method.
Create two ExcelFont objects using Workbook.createFont() method.
Get a specific cell using Worksheet.getCellRange() method, and set the rich text content of the cell using CellRange.getRichText().setText() method.
Apply the two ExcelFont objects to the rich text using RichText.setFont() method.
Save the workbook to an Excel file using Workbook.saveToFile() method.

Java

import com.spire.xls.*;

import java.awt.*;

public class ApplyMultipleFontsToCell {

    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook wb = new Workbook();

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

        //Create one Excel font
        ExcelFont font1 = wb.createFont();
        font1.setFontName("Arial Black");
        font1.setColor(Color.blue);
        font1.setSize(13f);
        font1.isBold(true);

        //Create another Excel font
        ExcelFont font2 = wb.createFont();
        font2.setFontName("Algerian");
        font2.setColor(Color.red);
        font2.setSize(15f);
        font2.isBold(true);
        font2.isItalic(true);

        //Insert text to cell B5
        RichText richText = sheet.getCellRange("B5").getRichText();
        richText.setText("Buy One, Get One Free");

        //Apply two fonts to the text in the cell B5
        richText.setFont(0, 8, font1);
        richText.setFont(9, 21, font2);

        //Save the document
        wb.saveToFile("output/ApplyMultipleFontsToCell.xlsx", ExcelVersion.Version2016);
    }
}

Java: Appy Fonts to Excel Cells

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Cells

Tuesday, 09 April 2024 05:29

Java: Change Row Height and Column Width in Excel

Adjusting row heights and column widths allows users to optimize the display of their data in a spreadsheet. Whether you're working with a large dataset or preparing a report, customizing these dimensions can help ensure that your information is presented clearly and concisely. Excel provides several ways to change row height and column width, including manual adjustments and automatic fitting options.

In this article, you will learn how to programmatically change row height and column width in Excel in Java using the Spire.XLS for Java library.

Change Row Height and Column Width for a Specific Row and Column
Change Row Height and Column Width for All Rows and Columns
Automatically Adjust Row Height and Column Width for a Specific Row and Column
Automatically Adjust Row Height and Column Width in a Cell Range

Install Spire.XLS for Java

Package Manager

<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>14.11.0</version>
    </dependency>
</dependencies>

Change Row Height and Column Width for a Specific Row and Column

Spire.XLS for Java provides the Worksheet.setRowHeight() and Worksheet.setColumnWidth() methods for adjusting the height of a specific row and the width of a specific column in a worksheet. Here are the detailed steps to accomplish this task.

Create a Workbook object.
Load an Excel document from a given file path.
Get a specific worksheet from the workbook.
Change the height of a specific row using Worksheet.setRowHeight() method.
Change the width of a specific column using Worksheet.setColumnWidth() method.
Save the workbook to a different Excel file.

Java

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

public class SetRowHeightAndColumnWidth {

    public static void main(String[] args) {

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

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

        // Get a specific worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Set the height of a selected row to 20
        worksheet.setRowHeight(1,20);

        // Set the width of a selected column to 30
        worksheet.setColumnWidth(4, 30);

        //Save to file.
        workbook.saveToFile("output/SetHeightAndWidth.xlsx", ExcelVersion.Version2016);
        
        // Dispose resources
        workbook.dispose();
    }
}

Change Row Height and Column Width for All Rows and Columns

To modify the row height and column width for all rows and columns in a worksheet, you can utilize the Worksheet.setDefaultRowHeight() and Worksheet.setDefaultColumnWidth() methods. The following are the detailed steps.

Create a Workbook object.
Load an Excel document from a given file path.
Get a specific worksheet from the workbook.
Change the height for all rows using Worksheet.setDefaultRowHeight() method.
Change the width for all columns using Worksheet.setDefaultColumnWidth() method.
Save the workbook to a different Excel file.

Java

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

public class SetRowHeightColumnWidthForAll {

    public static void main(String[] args) {

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

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

        // Get a specific worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Set the default row height to 18
        worksheet.setDefaultRowHeight(18);

        // Set the default column width to 15
        worksheet.setDefaultColumnWidth(15);
        
        //Save to file.
        workbook.saveToFile("output/SetHeightAndWidthForAll.xlsx", ExcelVersion.Version2016);

        // Dispose resources
        workbook.dispose();
    }
}

Automatically Adjust Row Height and Column Width for a Specific Row and Column

To automatically adjust the row height and column width to fit the content of a specific row and column in a worksheet, you can use the Worksheet.autoFitRow() and Worksheet.autoFitColumn() methods. The steps to autofit row height and column width are as follows.

Create a Workbook object.
Load an Excel document from a given file path.
Get a specific worksheet from the workbook.
Automatically adjust the height of a specific row using Worksheet.autoFitRow() method.
Automatically adjust the width of a specific column using Worksheet.autoFitColumn() method.
Save the workbook to a different Excel file.

Java

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

public class AutoFitRowHeightAndColumnWidth {

    public static void main(String[] args) {

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

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

        // Get a specific worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Autofit the first row 
        worksheet.autoFitRow(1);

        // Autofit the second column
        worksheet.autoFitColumn(2);

        // Save the document
        workbook.saveToFile("output/AutoFit.xlsx", ExcelVersion.Version2016);

        // Dispose resources
        workbook.dispose();
    }
}

Automatically Adjust Row Height and Column Width in a Cell Range

To automatically adjust the row height and column width within a specific cell range in your worksheet, you can utilize the CellRange.autoFitRows() and CellRange.autoFitColumns() methods respectively. Below are the detailed steps.

Create a Workbook object.
Load an Excel document from a given file path.
Get a specific worksheet from the workbook.
Get a cell range using Worksheet.getCellRange() method.
Automatically adjust the row height in the range using CellRange.autoFitRow() method.
Automatically adjust the column width in the range using CellRange.autoFitColumn() method.
Save the workbook to a different Excel file.

Java

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

public class AutoFitInRange {

    public static void main(String[] args) {

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

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

        // Get a specific worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Get the used range
        CellRange cellRange = worksheet.getAllocatedRange();

        // Or, you can get a desired cell range
        // CellRange cellRange = worksheet.getCellRange(1,1,6,4)

        // Autofit rows and columns in the range
        cellRange.autoFitRows();
        cellRange.autoFitColumns();

        // Save the document
        workbook.saveToFile("output/AutoFit.xlsx", ExcelVersion.Version2016);

        // Dispose resources
        workbook.dispose();
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Cells

Thursday, 10 February 2022 01:39

Java: Apply or Remove Data Validation in Excel

The data validation in Excel helps control what kind of data can or should be entered into a worksheet. In other words, any input entered into a particular cell must meet the criteria set for that cell. For example, you can create a validation rule that restricts a cell to accept only whole numbers. In this article, you will learn how to apply or remove data validation in Excel by using Spire.XLS for Java.

Apply Data Validation to Excel Cells
Remove Data Validation from Selected Cell Ranges

Install Spire.XLS for Java

Package Manager

<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>14.11.0</version>
    </dependency>
</dependencies>

Apply Data Validation to Excel Cells

The following are the main steps to add various types of data validation to cells.

Create a Workbook object, and get the first worksheet using Workbook.getWorksheets().get() method.
Get a specific cell range using Worksheet.getCellRange() method to add data validation.
Set the data type allowed in the cell using CellRange.getDataValidation().setAllowType() method. You can choose the data type as Integer, Time, Date, TextLength, Decimal, etc.
Set the comparison operator using CellRange.getDataValiation().setCompareOperator() method. The comparison operators include Between, NotBetween, Less, Greater, Equal, etc.
Set one or two formulas for the data validation using CellRange.getDataValidation().setFormula1() and CellRange.getDataValidation().setFormula2() methods.
Set the input prompt using CellRange.getDataValidation().setInputMessage() method.
Save the workbook to an Excel file using Workbook.saveToFile() method.

Java

import com.spire.xls.*;

public class DataValidation {

    public static void main(String[] args) {

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

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

        //Insert text in cells
        sheet.getCellRange("B2").setText("Number Validation:");
        sheet.getCellRange("B4").setText("Date Validation:");
        sheet.getCellRange("B6").setText("Text Length Validation:");
        sheet.getCellRange("B8").setText("List Validation:");
        sheet.getCellRange("B10").setText("Time Validation:");

        //Add a number validation to C2
        CellRange rangeNumber = sheet.getCellRange("C2");
        rangeNumber.getDataValidation().setAllowType(CellDataType.Integer);
        rangeNumber.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
        rangeNumber.getDataValidation().setFormula1("1");
        rangeNumber.getDataValidation().setFormula2("10");
        rangeNumber.getDataValidation().setInputMessage("Enter a number between 1 and 10");
        rangeNumber.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

        //Add a date validation to C4
        CellRange rangeDate = sheet.getCellRange("C4");
        rangeDate.getDataValidation().setAllowType(CellDataType.Date);
        rangeDate.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
        rangeDate.getDataValidation().setFormula1("1/1/2010");
        rangeDate.getDataValidation().setFormula2("12/31/2020");
        rangeDate.getDataValidation().setInputMessage("Enter a date between 1/1/2010 and 12/31/2020");
        rangeDate.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

        //Add a text length validation to C6
        CellRange rangeTextLength = sheet.getCellRange("C6");
        rangeTextLength.getDataValidation().setAllowType(CellDataType.TextLength);
        rangeTextLength.getDataValidation().setCompareOperator(ValidationComparisonOperator.LessOrEqual);
        rangeTextLength.getDataValidation().setFormula1("5");
        rangeTextLength.getDataValidation().setInputMessage("Enter text lesser than 5 characters");
        rangeTextLength.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

        //Apply a list validation to C8
        CellRange rangeList = sheet.getCellRange("C8");
        rangeList.getDataValidation().setValues(new String[]{ "United States", "Canada", "United Kingdom", "Germany" });
        rangeList.getDataValidation().isSuppressDropDownArrow(false);
        rangeList.getDataValidation().setInputMessage("Choose an item from the list");
        rangeList.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

        //Apply a time validation to C10
        CellRange rangeTime= sheet.getCellRange("C10");
        rangeTime.getDataValidation().setAllowType(CellDataType.Time);
        rangeTime.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
        rangeTime.getDataValidation().setFormula1("9:00");
        rangeTime.getDataValidation().setFormula2("12:00");
        rangeTime.getDataValidation().setInputMessage("Enter a time between 9:00 and 12:00");
        rangeTime.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

        //Auto fit width of column 2
        sheet.autoFitColumn(2);

        //Set the width of column 3
        sheet.setColumnWidth(3, 20);

        //Save to file
        workbook.saveToFile("output/ApplyDataValidation.xlsx", ExcelVersion.Version2016);
    }
}

Java: Apply or Remove Data Validation in Excel

Remove Data Validation from Selected Cell Ranges

The following are the steps to remove data validation from selected cell ranges.

Create a Workbook object.
Load a sample Excel file using Workbook.loadFromFile() method.
Get the first worksheet using Workbook.getWorksheets().get() method.
Create an array of rectangles, which is used to locate the cell ranges where the validation will be removed.
Remove the data validation from the selected cell ranges using Worksheet.getDVTable().remove() method.
Save the workbook to another Excel file using Workbook.saveToFile() method.

Java

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

import java.awt.*;

public class RemoveDataValidation {

    public static void main(String[] args) {

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

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

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

        //Create an array of rectangles, which is used to locate the ranges in worksheet
        Rectangle[] rectangles = new Rectangle[]{

                //One Rectangle(columnIndex, rowIndex) specifies a specific cell，the column or row index starts at 0
                //To specify a cell range, use Rectangle(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex)
                new Rectangle(2,1),
                new Rectangle(2,3),
                new Rectangle(2,5),
                new Rectangle(2,7),
                new Rectangle(2,9)
        };

        //Remove the data validation from the selected cells
        worksheet.getDVTable().remove(rectangles);

        //Save the workbook to an Excel file
        workbook.saveToFile("output/RemoveDataValidation.xlsx");
    }
}

Java: Apply or Remove Data Validation in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Cells

Tuesday, 21 January 2020 08:59

Create Nested Groups in Excel in Java

This article demonstrates how to create a nested group in a worksheet using Spire.XLS for Java.

import com.spire.xls.*;

import java.awt.*;

public class CreateNestedGroup {

    public static void main(String[] args) {

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

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

        //Create a cell style
        CellStyle style = workbook.getStyles().addStyle("style");
        style.getFont().setColor(Color.blue);
        style.getFont().isBold(true);

        //Write data to cells
        sheet.get("A1").setValue("Project plan for project X");
        sheet.get("A1").setCellStyleName(style.getName());
        sheet.get("A3").setValue("Set up");
        sheet.get("A3").setCellStyleName(style.getName());
        sheet.get("A4").setValue("Task 1");
        sheet.get("A5").setValue("Task 2");
        sheet.getCellRange("A4:A5").borderAround(LineStyleType.Thin);
        sheet.getCellRange("A4:A5").borderInside(LineStyleType.Thin);
        sheet.get("A7").setValue("Launch");
        sheet.get("A7").setCellStyleName(style.getName());
        sheet.get("A8").setValue("Task 1");
        sheet.get("A9").setValue("Task 2");
        sheet.getCellRange("A8:A9").borderAround(LineStyleType.Thin);
        sheet.getCellRange("A8:A9").borderInside(LineStyleType.Thin);

        //Pass false to isSummaryRowBelow method , which indicates the summary rows appear above detail rows
        sheet.getPageSetup().isSummaryRowBelow(false);

        //Group the rows using groupByRows method
        sheet.groupByRows(2,9,false);
        sheet.groupByRows(4,5,false);
        sheet.groupByRows(8,9,false);

        //Save to file
        workbook.saveToFile("NestedGroup.xlsx", ExcelVersion.Version2016);
    }
}

Create Nested Groups in Excel in Java

Published in Cells

Friday, 22 April 2022 02:39

Java: Merge or Unmerge Cells in Excel

Merging cells in Excel refers to combining two or more adjacent cells into one large cell that spans multiple rows or columns. This is useful for creating titles or labels that need to be centered over a range of cell. In this article, you will learn how to programmatically merge or unmerge cells in an Excel document using Spire.XLS for Java.

Merge Cells in Excel in Java
Unmerge Cells in Excel in Java

Install Spire.XLS for Java

Package Manager

<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>14.11.0</version>
    </dependency>
</dependencies>

Merge Cells in Excel in Java

The detailed steps are as follows.

Create a Workbook instance.
Load a sample Excel document using Workbook.loadFromFile() method.
Get a specified worksheet using Workbook.getWorksheets().get() method.
Get a specified range using Worksheet.getRange().get() method.
Merge cells in the specified range using XlsRange.merge() method.
Set the horizontal alignment of merged cells to Center using XlsRange.getCellStyle().setHorizontalAlignment() method.
Set the vertical alignment of merged cells to Center using XlsRange.getCellStyle().setVerticalAlignment() method.
Save the result document using Workbook.saveToFile() method.

Java

import com.spire.xls.*;

public class MergeCells {
    public static void main(String[] args){

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

        //Load a sample Excel document
        workbook.loadFromFile("input.xlsx");

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

        //Merge cells by range
        sheet.getRange().get("A2:A4").merge();
        sheet.getRange().get("A5:A7").merge();

        //Set the horizontal alignment of merged cells to Center
        sheet.getRange().get("A2").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
        sheet.getRange().get("A5").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);

        //Set the vertical alignment of merged cells to Center
        sheet.getRange().get("A2").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getRange().get("A5").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);


        //Save the result document
        workbook.saveToFile("MergeCells.xlsx", FileFormat.Version2013);
    }
}

Java: Merge or Unmerge Cells in Excel

Unmerge Cells in Excel in Java

The detailed steps are as follows.

Create a Workbook instance.
Load a sample Excel document using Workbook.loadFromFile() method.
Get a specified worksheet using Workbook.getWorksheets().get() method.
Get a specified range using Worksheet.getRange().get() method.
Unmerge cells in the specified range using XlsRange.unMerge() method.
Save the result document using Workbook.saveToFile() method.

Java

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

public class UnmergeCells {
    public static void main(String[] args){

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

        //Load a sample Excel document
        workbook.loadFromFile("MergeCells.xlsx");

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

        //Unmerge cells by range
        sheet.getRange().get("A2:A4").unMerge();

        //Save the result document
        workbook.saveToFile("UnMergeCells.xlsx", FileFormat.Version2013);
    }
}

Java: Merge or Unmerge Cells in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Cells

Start «123 »End

Page 2 of 3