By default, an animation plays only one time and does not repeat. However, we can make the animation to play more than once by setting the repeat type of it. This article demonstrates how to accomplish this function using Spire.Presentation for Java.

import com.spire.presentation.*;
import com.spire.presentation.drawing.animation.AnimationEffect;

public class RepeatAnimation {
    public static void main(String[] args) throws Exception {
        //Create a Presentation instance
        Presentation ppt = new Presentation();
        //Load a PowerPoint document
        ppt.loadFromFile("Animation.pptx");
        
        //Get the first slide
        ISlide slide = ppt.getSlides().get(0);
        
        //Get the first animation effect on the slide
        AnimationEffect animation = slide.getTimeline().getMainSequence().get(0);
        //Set the animation effect to repeat forever until the end of slide.
        animation.getTiming().setAnimationRepeatType(AnimationRepeatType.UtilEndOfSlide);

        //Save the result document
        ppt.saveToFile("RepeatAnimation.pptx", FileFormat.PPTX_2013);
    }
}

Output:

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);
    }
}

This article will demonstrate how to set the layout of the slide via Spire.Presentation in Java applications. There are 11 kinds of layout on Microsoft PowerPoint and Spire.Presentation supports all of them.

Set one layout of the slide

import com.spire.presentation.*;

public class setSlideLayout {
    public static void main(String[] args) throws Exception{

        //Create an instance of presentation document
        Presentation ppt = new Presentation();

        //Remove the default slide
        ppt.getSlides().removeAt(0);

        //Append a slide and set the layout for slide
        ISlide slide = ppt.getSlides().append(SlideLayoutType.TITLE);

        //Add content for Title and Text
        IAutoShape shape = (IAutoShape)slide.getShapes().get(0);
        shape.getTextFrame().setText("Spire.Presentation");

        shape = (IAutoShape)slide.getShapes().get(1);
        shape.getTextFrame().setText("Set the Layout of Slide as Title");

        //Save the document
         ppt.saveToFile("SlideLayout.pptx", FileFormat.PPTX_2013);
        ppt.dispose();
    }
}

Set the different layout of the slides

import com.spire.presentation.*;

public class setDifferentSlideLayout {
    public static void main(String[] args) throws Exception{

        //Create a PPT document
        Presentation presentation = new Presentation();

        //Remove the default slide
        presentation.getSlides().removeAt(0);

        //Loop through slide layouts
        for (SlideLayoutType type : SlideLayoutType.values())
        {
            //Append slide by specified slide layout
            presentation.getSlides().append(type);
        }

        //Save the document
        presentation.saveToFile("Result.pptx", FileFormat.PPTX_2013);
        presentation.dispose();
    }
}

Effective screenshot:

Spire.XLS for Java enables developers to add and manipulate multiple types of form controls, e.g. text box, option button, check box and combo box in Excel files in Java applications.

The following examples will show you how to add and remove text box, option button, check box and combo box form controls in an Excel file using Spire.XLS for Java.

Add Form Controls

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

public class AddFormControls {
    public static void main(String[] args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        sheet.getCellRange("A2").setText("Name: ");
        //Add a text box
        ITextBoxShape textbox = sheet.getTextBoxes().addTextBox(2, 2, 18, 65);
        textbox.setText("Shaun");
        textbox.getFill().setForeColor(Color.PINK);
        textbox.setHAlignment(CommentHAlignType.Center);
        textbox.setVAlignment(CommentVAlignType.Center);

        sheet.getCellRange("A4").setText("Gender: ");
        //Add an option button
        IRadioButton radiobutton1 = sheet.getRadioButtons().add(4, 2, 18, 65);
        radiobutton1.setText("Male");
        //Add an option button
        IRadioButton radiobutton2 = sheet.getRadioButtons().add(4, 4, 18, 65);
        radiobutton2.setText("Female");

        sheet.getCellRange("A6").setText("Hobby: ");
        //Add a check box
        ICheckBox checkbox1 = sheet.getCheckBoxes().addCheckBox(6, 2, 18, 100);
        checkbox1.setCheckState(CheckState.Checked);
        checkbox1.setText("Photography");
        //Add a check box
        ICheckBox checkbox2 = sheet.getCheckBoxes().addCheckBox(6, 4, 18, 65);
        checkbox2.setCheckState(CheckState.Checked);
        checkbox2.setText("Travel");

        sheet.getCellRange("A8").setText("Profession: ");
        sheet.getCellRange("A20").setText("Student");
        sheet.getCellRange("A21").setText("Teacher");
        sheet.getCellRange("A22").setText("Doctor");
        //Add a combo box
        IComboBoxShape combobox = sheet.getComboBoxes().addComboBox(8, 2, 18, 65);
        combobox.setListFillRange(sheet.getCellRange("A20:A22"));
        combobox.setSelectedIndex(1);

        for (int column = 1; column < 5; column ++)
        {
            sheet.setColumnWidth(column, 15f);
        }

        //Save the file
        workbook.saveToFile("AddControls.xlsx", ExcelVersion.Version2013);
    }
}

Remove Form Controls

import com.spire.xls.*;

public class RemoveFormControls {
    public static void main(String[] args){
        //Load an Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("AddControls.xlsx");
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Remove option buttons from the worksheet
        for(int j = 0; j < sheet.getRadioButtons().getCount(); j ++){
            sheet.getRadioButtons().get(j).remove();
        }
        
        //Remove check boxes from the worksheet
        for(int i = 0; i < sheet.getCheckBoxes().getCount(); i ++){
            sheet.getCheckBoxes().get(i).remove();
        }

        //Save the file
        workbook.saveToFile("RemoveControls.xlsx", ExcelVersion.Version2013);
    }
}

When working with a large workbook containing dozens of columns and rows, it may often be necessary to use the "Find" function to quickly locate specific values. This article will demonstrate how to programmatically find all cells with a specific value and highlight them with a background color using Spire.XLS for Java.

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.

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

Find and Highlight Data in Excel

The detailed steps are as follows.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Find all cells with matching text using Worksheet.findAllString(java.lang.String stringValue, boolean formula, boolean formulaValue) method.
• Set color to highlight the cells using CellRange.getCellStyle().setColor() method.
• Save the result file using Workbook.saveToFile() method.

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

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

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

        //Load the sample document
        workbook.loadFromFile("Test.xlsx");

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

        //Find all cells with the text "Regulator System"
        CellRange[] ranges = worksheet.findAllString("Regulator System", true, true);
        for (CellRange range : ranges)
        {

            //Set color to highlight the cells
            range.getCellStyle().setColor(Color.yellow);
        }

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

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.

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.

<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.4.1</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.

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();
    }
}

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.

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();
    }
}

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.

When you work with Excel worksheets on a daily basis, you sometimes need to convert them to images for storage or preventing data from being changed when shared with others. This article will show you how to convert a whole Excel worksheet or a specific cell range to an image using Spire.XLS for Java.

• Convert a Whole Excel Worksheet to Image
• Convert a Specific Cell Range to Image

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.

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

Convert a Whole Excel Worksheet to Image

The following steps will demonstrate how to convert a whole Excel worksheet to an image.

• Create a Workbook instance.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a specific worksheet of the document using Workbook.getWorksheets().get() method.
• Convert the worksheet to an image using Worksheet.saveToImage() method.

import com.spire.xls.*;

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

        //Create a workbook instance
        Workbook workbook = new Workbook();
        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\sample.xlsx");

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

        //Save the sheet to an image
        sheet.saveToImage("output/SheetToImage.png");
    }
}

Convert a Specific Cell Range to Image

Spire.XLS for Java supports converting a specific cell range to an image by using the Worksheet.toImage (int firstRow,int firstColumn,int lastRow,int lastColumn) method. Detailed steps are listed below.

• Create a Workbook instance.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a specific worksheet of the document using Workbook.getWorksheets().get() method.
• Create a BufferedImage instance.
• Convert a specific cell range to the ButteredImage object using Worksheet.toImage () method.
• Save the BufferedImage object to a .png image.

import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;

public class SpecificCellsToImage {
    public static void main(String[] args) throws IOException {

        //Create a workbook instance
        Workbook workbook = new Workbook();
        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\sample.xlsx");

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

        //Convert a specific cell range to the BufferedImage object  
        BufferedImage bufferedImage = sheet.toImage(1, 1, 7, 4);
        //Save as a .png image
ImageIO.write(bufferedImage,"PNG",new File("output/specificCellsToImage.png"));
    }
}

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.

Excel Panes can be frozen to make certain rows or columns visible even when scrolling through the worksheet. This feature is helpful for viewing or editing data in a large worksheet. For example, once the header row is frozen, we can easily find cells in the lower part of the worksheet without having to go back to the top to check the name of the header. This article will introduce how to freeze rows or/and columns in Excel using Spire.XLS for Java.

Spire.XLS for Java provides the Worksheet.freezePanes(int rowIndex, int columnIndex) method to freeze all rows and columns above and left of the selected cell which is specified by the rowIndex and the columnIndex.

This tutorial provides the code examples for the following cases:

• Freeze the Top Row
• Freeze the First Column
• Freeze the First Row and the First Column

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.

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

Freeze the Top Row

In order to freeze the top row, we should select the cell (2, 1) – "A2" and freeze the row above. The following are the steps to freeze the top row using Spire.XLS for Java.

• Create a Workbook object.
• Load an Excel file using Workbook.loadFromFile() method.
• Get a specific worksheet using Workbook.getWorksheets().get() method.
• Freeze the top row by passing (2, 1) to the Worksheet.freezePanes(int rowIndex, int columnIndex) method as the 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 FreezeTopRow {

    public static void main(String[] args) {

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

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

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

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

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

Freeze the First Column

To freeze the first column, we should select the cell (1, 2) – "B1" and freeze the column on the left. The following are the steps to freeze the first column using Spire.XLS for Java.

• Create a Workbook object.
• Load an Excel file using Workbook.loadFromFile() method.
• Get a specific worksheet using Workbook.getWorksheets().get() method.
• Freeze the top row by passing (1, 2) to the Worksheet.freezePanes(int rowIndex, int columnIndex) method as the 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 FreezeFirstColumn {

    public static void main(String[] args) {

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

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

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

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

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

Freeze the First Row and the First Column

To freeze the first row and the first column, the selected cell should be cell (2, 2) – "B2". The following are the detailed steps.

• Create a Workbook object.
• Load an Excel file using Workbook.loadFromFile() method.
• Get a specific worksheet using Workbook.getWorksheets().get() method.
• Freeze the first row and the first column by passing (2, 2) to the Worksheet.freezePanes(int rowIndex, int columnIndex) method as the 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 FreezeFirstRowAndFirstColumn {

    public static void main(String[] args) {

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

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

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

        //Freeze the first row and the first column
        sheet.freezePanes(2, 2);

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

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.

Number formatting is a way to control how a number is displayed using numeric format string. For example, you can use format string '0.00' to format 1234.5678 as 1234.57. Numeric format strings often consist of one or more custom numeric specifiers listed as below:

• "#" - Digit placeholder
• "0" - Zero placeholder
• "," - Decimal point
• "." - Decimal separator
• "[Red]" - Color specifier
• "%" - Percentage placeholder

The following example demonstrates how to set number format in Excel using Spire.XLS for Java.

import com.spire.xls.*;

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

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

        //Add a string to cell “B1”
        sheet.getCellRange("B1").setText("NUMBER FORMATTING");
        sheet.getCellRange("B1").getCellStyle().getExcelFont().isBold(true);
        sheet.getCellRange("B1:C1").merge();
        sheet.getCellRange("B1:C1").setHorizontalAlignment(HorizontalAlignType.Center);

        
        //Add a string to cell “B3”
        sheet.getCellRange("B3").setText("0");
       //Add a number to cell “C3” and set the number format
        sheet.getCellRange("C3").setNumberValue(1234.5678);
        sheet.getCellRange("C3").setNumberFormat("0");

        //Repeat the above step to add string and number to other cells and set the number format
        sheet.getCellRange("B4").setText("0.00");
        sheet.getCellRange("C4").setNumberValue(1234.5678);
        sheet.getCellRange("C4").setNumberFormat("0.00");

        sheet.getCellRange("B5").setText("#,##0.00");
        sheet.getCellRange("C5").setNumberValue(1234.5678);
        sheet.getCellRange("C5").setNumberFormat("#,##0.00");

        sheet.getCellRange("B6").setText("$#,##0.00");
        sheet.getCellRange("C6").setNumberValue(1234.5678);
        sheet.getCellRange("C6").setNumberFormat("$#,##0.00");

        sheet.getCellRange("B7").setText("0;[Red]-0");
        sheet.getCellRange("C7").setNumberValue(-1234.5678);
        sheet.getCellRange("C7").setNumberFormat("0;[Red]-0");

        sheet.getCellRange("B8").setText("0.00;[Red]-0.00");
        sheet.getCellRange("C8").setNumberValue(-1234.5678);
        sheet.getCellRange("C8").setNumberFormat("0.00;[Red]-0.00");

        sheet.getCellRange("B9").setText("#,##0;[Red]-#,##0");
        sheet.getCellRange("C9").setNumberValue(-1234.5678);
        sheet.getCellRange("C9").setNumberFormat("#,##0;[Red]-#,##0");

        sheet.getCellRange("B10").setText("#,##0.00;[Red]-#,##0.000");
        sheet.getCellRange("C10").setNumberValue(-1234.5678);
        sheet.getCellRange("C10").setNumberFormat("#,##0.00;[Red]-#,##0.00");

        sheet.getCellRange("B11").setText("0.00E+00");
        sheet.getCellRange("C11").setNumberValue(1234.5678);
        sheet.getCellRange("C11").setNumberFormat("0.00E+00");

        sheet.getCellRange("B12").setText("0.00%");
        sheet.getCellRange("C12").setNumberValue(1234.5678);
        sheet.getCellRange("C12").setNumberFormat("0.00%");

        //Set background color for specified cells 
        sheet.getCellRange("B3:B12").getCellStyle().setKnownColor(ExcelColors.Gray25Percent);
        sheet.getCellRange("C3:C12").getCellStyle().setKnownColor(ExcelColors.Gray50Percent);

        //Set column width for specified columns
        sheet.setColumnWidth(2, 24);
        sheet.setColumnWidth(3, 24);

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

The following screenshot shows the output Excel files after setting number format:

Applying different background colors to alternate rows of Excel can improve the readability of data and make the spreadsheet appear more professional. There many ways to set row color, among which using conditional formatting is a good choice. It can not only set colors in batches, but also define more flexible rules, such as alternating every three rows. In this article, you will learn how to alternate row color in Excel using conditional formatting 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.

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

Apply Color to Alternate Rows in Excel Using Conditional Formatting

The following are the steps to add color to alternative rows in Excel using Spire.XLS for Java.

• Create a Workbook object.
• Load an Excel file using Workbook.loadFromFile() method.
• Get a specific worksheet from the workbook using Workbook.getWorsheets().get(index) method.
• Add a conditional formatting to the worksheet using Worksheet.getConditionalFormats().add() method and return an object of XlsConditionalFormats class.
• Set the cell range where the conditional formatting will be applied using XlsConditionalFormats.addRange() method.
• Add a condition using XlsConditionalFormats.addCondition() method, then set the conditional formula and the cell color of even rows.
• Add another condition to change the format of the cells of odd rows.
• Save the workbook to an Excel file using Workbook.saveToFile() method.

import com.spire.xls.ConditionalFormatType;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;

import java.awt.*;

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

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

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

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

        //Add a conditional format to the worksheet
        XlsConditionalFormats format = sheet.getConditionalFormats().add();

        //Set the range where the conditional format will be applied
        format.addRange(sheet.getRange().get(2,1,sheet.getLastRow(),sheet.getLastColumn()));

        //Add a condition to change the format of the cells based on formula
        IConditionalFormat condition1 = format.addCondition();
        condition1.setFirstFormula("=MOD(ROW(),2)=0");
        condition1.setFormatType(ConditionalFormatType.Formula);
        condition1.setBackColor(Color.YELLOW);

        //Add another condition to change the format of the cells based on formula
        IConditionalFormat condition2 = format.addCondition();
        condition2.setFirstFormula("=MOD(ROW(),2)=1");
        condition2.setFormatType(ConditionalFormatType.Formula);
        condition2.setBackColor(new Color(32,178, 170));

        //Save the workbook to an Excel file
        workbook.saveToFile("output/AlternateRowColors.xlsx", ExcelVersion.Version2016);
    }
}

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.