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

Thursday, 08 December 2022 07:16

Java: Protect or Unprotect Excel Documents

While sharing your spreadsheets with others, you may do not want the receiver to alter the content or may want them to change only specific content and leave the rest unchanged. To protect your worksheet from being edited by other people, Excel offers a protection feature. In this article, you will learn how to programmatically protect and unprotect a workbook or a worksheet in Java by 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.3.2</version>
    </dependency>
</dependencies>
    

Password Protect an Entire Workbook in Java

By encrypting an Excel document with a password, you ensure that only you and authorized individuals can read or edit it. The following are the steps to password protect a workbook using Spire.XLS for Java.

  • Create a Workbook object.
  • Load an Excel file using Workbook.loadFromFile() method.
  • Protect the workbook using a password using Workbook.protect() method.
  • Save the workbook to another Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;

public class PasswordProtectWorkbook {

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

        //Protect workbook with a password
        workbook.protect("psd-123");

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

Java: Protect or Unprotect Excel Documents

Protect a Worksheet with a Specific Protection Type in Java

If you wish to grant people permission to read your Excel document but restrict the types of modifications they are allowed to make on a worksheet, you can protect the worksheet with a specific protection type. The table below lists a variety of pre-defined protection types under the SheetProtectionType enumeration.

Protection Type Allow users to
Content Modify or insert content.
DeletingColumns Delete columns.
DeletingRows Delete rows.
Filtering Set filters.
FormattingCells Format cells.
FormattingColumns Format columns.
FormattingRows Format rows.
InsertingColumns Insert columns.
InsertingRows Insert rows.
InsertingHyperlinks Insert hyperlinks .
LockedCells Select locked cells.
UnlockedCells Select unlocked cells.
Objects Modify drawing objects.
Scenarios Modify saved scenarios.
Sorting Sort data.
UsingPivotTables Use pivot table and pivot chart.
All Do any operations listed above on the protected worksheet.
None Do nothing on the protected worksheet.

The following are the steps to protect a worksheet with a specific protection type 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(index) method.
  • Protect the worksheet with a protection type using Worksheet.protect(String password, EnumSet.of <SheetProtectionType> options) method.
  • Save the workbook to another Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.SheetProtectionType;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

import java.util.EnumSet;

public class ProtectWorksheet {

    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 a specific worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //Protect the worksheet with the permission password and the specific protect type
        worksheet.protect("psd-permission", EnumSet.of(SheetProtectionType.None));

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

Java: Protect or Unprotect Excel Documents

Allow Users to Edit Ranges in a Protected Worksheet in Java

In certain cases, you may need to allow users to be able to edit selected ranges in a protected worksheet. The following steps demonstrate how to.

  • Create a Workbook object.
  • Load an Excel file using Workbook.loadFromFile() method.
  • Get a specific worksheet using Workbook.getWorksheets().get(index) method.
  • Specify editable cell ranges using Worksheet.addAllowEditRange() method.
  • Protect the worksheet with a protection type using Worksheet.protect(String password, EnumSet.of <SheetProtectionType> options) method.
  • Save the workbook to another Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.SheetProtectionType;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

import java.util.EnumSet;

public class AllowEditRanges {

    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 ranges that allow editing
        sheet.addAllowEditRange("Range One", sheet.getRange().get("A5:A6"));
        sheet.addAllowEditRange("Range Two", sheet.getRange().get("A8:B11"));

        //Protect the worksheet with a password and a protection type
        sheet.protect("psd-permission", EnumSet.of(SheetProtectionType.All));

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

Java: Protect or Unprotect Excel Documents

Unprotect a Password Protected Worksheet in Java

To remove the protection of a password-protected worksheet, invoke the Worksheet.unprotect() method and pass in the original password as a parameter. The detailed steps are as follows.

  • Create a Workbook object.
  • Load an Excel file using Workbook.loadFromFile() method.
  • Get a specific worksheet using Workbook.getWorksheets().get(index) method.
  • Remove the protection using Worksheet.unprotect(String password) method.
  • Save the workbook to another Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class UnprotectWorksheet {

    public static void main(String[] args) {

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

        //Load an Excel file containing protected worksheet
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\ProtectedWorksheet.xlsx");

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

        //Unprotect the worksheet using the specified password
        sheet.unprotect("psd-permission");

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

Remove or Reset Password of an Encrypted Workbook in Java

To remove or reset password of an encrypted workbook, you can use the Workbook.unprotect() method and the Workbook.protect() method, respectively. The following steps show you how to load an encrypted Excel document and delete or change the password of it.

  • Create a Workbook object.
  • Specify the open password using Workbook.setOpenPassword() method.
  • Load the encrypted Excel file using Workbook.loadFromFile() method.
  • Remove the encryption using Workbook.unprotect() method. Or change the password using Workbook.protect() method.
  • Save the workbook to another Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;

public class RemoveOrResetPassword {

    public static void main(String[] args) {

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

        //Specify the open password
        workbook.setOpenPassword("psd-123");

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

        //Unprotect workbook
        workbook.unProtect();

        //Reset password
        //workbook.protect("newpassword");

        //Save the workbook to another Excel file
        workbook.saveToFile("output/Unprotect.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.

Wednesday, 15 January 2020 07:55

Insert Arrays into Excel in Java

This article demonstrates how to insert arrays, including one-dimensional and two-dimensional arrays, into Excel cells using Spire.XLS for Java.

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

public class InsertArray {

    public static void main(String[] args) {

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

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

        //Define a one-dimensional array 
        String[] oneDimensionalArray = new String[]{"Apple", "Pear", "Grape", "Banana"};

        // Write the array to the worksheet from the specified cell (true means vertically insert)
        sheet.insertArray(oneDimensionalArray, 1, 1, true);

        //Define a two-dimensional array 
        String[][] twoDimensionalArray = new String[][]{
                {"Name", "Age", "Sex", "Dept."},
                {"John", "25", "Male", "Development"},
                {"Albert", "24", "Male", "Support"},
                {"Amy", "26", "Female", "Sales"}
        };

        //Write the array to the worksheet from the specified cell
        sheet.insertArray(twoDimensionalArray, 1, 3);

        //Save the file 
        wb.saveToFile("InsertArrays.xlsx", ExcelVersion.Version2016);
    }
}

Insert Arrays into Excel in Java

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.

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

Monday, 06 December 2021 07:21

Java: Convert Excel to PDF

Using PDF as a format for sending documents ensures that no formatting changes will occur to the original document. Exporting Excel to PDF is a common practice in many cases. This article introduces how to convert a whole Excel document or a specific worksheet to PDF 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.3.2</version>
    </dependency>
</dependencies>
    

Convert a Whole Excel File to PDF

The following are the steps to convert a whole Excel document to PDF.

  • Create a Workbook object.
  • Load a sample Excel document using Workbook.loadFromFile() method.
  • Set the Excel to PDF conversion options through the methods under the ConverterSetting object, which is returned by Workbook.getConverterSetting() method.
  • Convert the whole Excel document to PDF using Workbook.saveToFile() method.
  • Java
import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;

public class ConvertExcelToPdf {

    public static void main(String[] args) {

        //Create a Workbook instance and load an Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\Sample.xlsx");

        //Set worksheets to fit to page when converting
        workbook.getConverterSetting().setSheetFitToPage(true);

        //Save the resulting document to a specified path
        workbook.saveToFile("output/ExcelToPdf.pdf", FileFormat.PDF);
    }
}

Java: Convert Excel to PDF

Convert a Specific Worksheet to PDF

The following are the steps to convert a specific worksheet to PDF.

  • Create a Workbook object.
  • Load a sample Excel document using Workbook.loadFromFile() method.
  • Set the Excel to PDF conversion options through the methods under the ConverterSetting object, which is returned by Workbook.getConverterSetting() method.
  • Get a specific worksheet using Workbook.getWorksheets().get() method.
  • Convert the worksheet to PDF using Worksheet.saveToPdf() method.
  • Java
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class ConvertWorksheetToPdf {

    public static void main(String[] args) {

        //Create a Workbook instance and load an Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\Sample.xlsx");

        //Set worksheets to fit to width when converting
        workbook.getConverterSetting().setSheetFitToWidth(true);

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

        //Convert to PDF and save the resulting document to a specified path
        worksheet.saveToPdf("output/WorksheetToPdf.pdf");
    }
}

Java: Convert Excel to PDF

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.

Wednesday, 19 July 2023 06:45

Java: Find and Replace Data in Excel

Excel is a versatile tool extensively utilized for data management and analysis. There are occasions when you may require locating specific data within an Excel file and replacing it with updated values. In this article, we will explore how to find and replace data in Excel in Java 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.3.2</version>
    </dependency>
</dependencies>
    

Find and Replace Data in a Worksheet in Excel

The Worksheet.findAllString() method provided by Spire.XLS for Java can help you find the cells containing specific text in Excel documents. Once found, you can conveniently replace these values with new ones using the CellRange.setText() method. The steps are as follows:

  • Create an instance of Workbook class.
  • Load an Excel file using Workbook.loadFromFile() method.
  • Get the desired worksheet using Workbook.getWorksheets().get() method.
  • Find the specific value in the worksheet using Worksheet.findAllString() method and replace the value of the cell with another value using CellRange.setText() method.
  • Set a background for the cell so you can easily find the updated cells using CellRange. getStyle().setColor() method.
  • 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;

import java.awt.Color;

public class ReplaceData {
    public static void main(String[] args) {
        // Initialize an instance of the Workbook class
        Workbook workbook = new Workbook();
        // Load an Excel file
        workbook.loadFromFile("Test.xlsx");

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

        // Find the cells with the specific string value "Total" in the worksheet
        CellRange[] cells = worksheet.findAllString("Total", true, true);

        // Iterate through the found cells
        for (CellRange cell : cells) {
            // Replace the value of the cell with another value
            cell.setText("Sum");
            // Set a background color for the cell
            cell.getStyle().setColor(Color.YELLOW);
        }

        // Save the result file to a specific location
        workbook.saveToFile("ReplaceDataInWorksheet.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

Java: Find and Replace Data in Excel

Find and Replace Data in a Specific Cell Range in Excel

To replace data within a specific range of cells, you can utilize the CellRange.findAllString() method to locate cells within the range that contain the desired values. Then, use the CellRange.setText() method to replace the cell value with a new value. The detailed steps are as follows:

  • Create an instance of Workbook class.
  • Load an Excel file using Workbook.loadFromFile() method.
  • Get the desired worksheet using Workbook.getWorksheets().get() method.
  • Get a specific cell range using Worksheet.getCellRange() method.
  • Find the cells with the specific value in the cell range using CellRange.findAllString() method.
  • Iterate through the found cells
  • Replace the value of the cell with another value using CellRange setText() method.
  • Set a background for the cell so you can easily find the updated cells using the CellRange. getStyle().setColor() method.
  • 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;

import java.awt.Color;

public class ReplaceDataInCellRange {
    public static void main(String[] args) {
        // Initialize an instance of the Workbook class
        Workbook workbook = new Workbook();
        // Load an Excel file
        workbook.loadFromFile("Test.xlsx");

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

        // Get a specific cell range
        CellRange range = worksheet.getCellRange("A1:C12");

        // Find the cells with the specific value "Total" in the cell range
        CellRange[] cells = range.findAllString("Total", true, true);

        // Iterate through the found cells
        for (CellRange cell : cells) {
            // Replace the value of the cell with another value
            cell.setText("Sum");
            // Set a background color for the cell
            cell.getStyle().setColor(Color.YELLOW);
        }

        // Save the result file to a specific location
        workbook.saveToFile("ReplaceDataInCellRange.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

Java: Find and Replace Data 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.

A pie chart is a circular chart divided into sectors, and each sector represents a proportionate part of the whole. It presents data in the form of graphs, which makes it easy for users to analyze and compare data. As for the doughnut chart, it performs the same function as the pie chart, except that it has a "hole" in the center. This article will demonstrate how to programmatically create a pie chart or a doughnut chart in Excel 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.3.2</version>
    </dependency>
</dependencies>
    

Create a Pie Chart in Excel

The detailed steps are as follows:

  • Create a Workbook object.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Add some data to specified cells and set the cell styles.
  • Add a pie chart to the worksheet using Worksheet.getCharts().add(ExcelChartType.Pie) method.
  • Set data range for the chart using Chart.setDataRange() method.
  • Set the position and title of the chart.
  • Get a specified series in the chart and set category labels and values for the series using ChartSerie.setCategoryLabels() and ChartSerie.setValues() methods.
  • Show data labels for data points.
  • Save the result file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;
import com.spire.xls.charts.ChartSerie;

import java.awt.*;

public class CreatePieChart {

    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 data to specified cells
        sheet.getCellRange("A1").setValue("Year");
        sheet.getCellRange("A2").setValue("2002");
        sheet.getCellRange("A3").setValue("2003");
        sheet.getCellRange("A4").setValue("2004");
        sheet.getCellRange("A5").setValue("2005");

        sheet.getCellRange("B1").setValue("Sales");
        sheet.getCellRange("B2").setNumberValue(4000);
        sheet.getCellRange("B3").setNumberValue(6000);
        sheet.getCellRange("B4").setNumberValue(7000);
        sheet.getCellRange("B5").setNumberValue(8500);

        //Set cell styles
        sheet.getCellRange("A1:B1").setRowHeight(15);
        sheet.getCellRange("A1:B1").getCellStyle().setColor(Color.darkGray);
        sheet.getCellRange("A1:B1").getCellStyle().getExcelFont().setColor(Color.white);
        sheet.getCellRange("A1:B5").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getCellRange("A1:B5").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);

        //Set number format
        sheet.getCellRange("B2:C5").getCellStyle().setNumberFormat("\"$\"#,##0");

        //Add a pie chart to the worksheet
        Chart chart = sheet.getCharts().add(ExcelChartType.Pie);

        //Set data range for the chart
        chart.setDataRange(sheet.getCellRange("B2:B5"));
        chart.setSeriesDataFromRange(false);

        //Set position of the chart 
        chart.setLeftColumn(3);
        chart.setTopRow(1);
        chart.setRightColumn(11);
        chart.setBottomRow(20);

        //Set and format chart title
        chart.setChartTitle("Sales by year");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        //Get a specified series in the chart
        ChartSerie cs = chart.getSeries().get(0);

        //Set category labels for the series
        cs.setCategoryLabels(sheet.getCellRange("A2:A5"));

        //Set values for the series
        cs.setValues(sheet.getCellRange("B2:B5"));

        //Show data labels for data points
        cs.getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);

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

Java: Create a Pie Chart or a Doughnut Chart in Excel

Create a Doughnut Chart in Excel

The detailed steps are as follows:

  • Create a Workbook object.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Add some data to specified cells and set the cell styles.
  • Add a doughnut chart to the worksheet using Worksheet.getCharts().add(ExcelChartType.Doughnut) method.
  • Set data range for the chart using Chart.setDataRange() method.
  • Set the position and title of the chart.
  • Show data labels for data points.
  • Set the legend position of the chart using Chart.getLegend().setPosition() method.
  • Save the result file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;
import com.spire.xls.charts.ChartSerie;
import com.spire.xls.charts.ChartSeries;

import java.awt.*;

public class CreateDoughnutChart {

    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 data to specified cells
        sheet.getCellRange("A1").setValue("Country");
        sheet.getCellRange("A2").setValue("Cuba");
        sheet.getCellRange("A3").setValue("Mexico");
        sheet.getCellRange("A4").setValue("German");
        sheet.getCellRange("A5").setValue("Japan");


        sheet.getCellRange("B1").setValue("Sales");
        sheet.getCellRange("B2").setNumberValue(6000);
        sheet.getCellRange("B3").setNumberValue(8000);
        sheet.getCellRange("B4").setNumberValue(9000);
        sheet.getCellRange("B5").setNumberValue(8500);

        //Set cell styles
        sheet.getCellRange("A1:B1").setRowHeight(15);
        sheet.getCellRange("A1:B1").getCellStyle().setColor(Color.darkGray);
        sheet.getCellRange("A1:B1").getCellStyle().getExcelFont().setColor(Color.white);
        sheet.getCellRange("A1:B5").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getCellRange("A1:B5").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);

        //Set number format
        sheet.getCellRange("B2:C5").getCellStyle().setNumberFormat("\"$\"#,##0");

        //Add a doughnut chart to the worksheet
        Chart chart = sheet.getCharts().add(ExcelChartType.Doughnut);

        //Set data range for chart
        chart.setDataRange(sheet.getCellRange("A1:B5"));
        chart.setSeriesDataFromRange(false);

        //Set position of the chart 
        chart.setLeftColumn(3);
        chart.setTopRow(1);
        chart.setRightColumn(11);
        chart.setBottomRow(20);

        //Set chart title 
        chart.setChartTitle("Market share by country");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        //Show data labels for data points
        ChartSeries series = chart.getSeries();
        for (int i = 0 ; i < series.size() ; i++) {
            ChartSerie cs = series.get(i);
            cs.getDataPoints().getDefaultDataPoint().getDataLabels().hasPercentage(true);
        }

        //Set the legend position of the chart
        chart.getLegend().setPosition(LegendPositionType.Top);

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

Java: Create a Pie Chart or a Doughnut Chart 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.

Monday, 06 January 2020 08:10

Create Excel column chart in Java

Spire.XLS for Java supports to generate many kinds of charts in Excel files. This article demonstrates how to create Excel column chart using Spire.XLS for Java. There are two main column charts, ColumnClustered chart and ColumnStacked chart. Here comes to the code snippets.

Java generate Column stacked chart. ColumnClustered represents Clustered Column Chart.

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

public class ColumnChart{
    public static void main(String[] args) {
        executeStackedColumn(true);
    }
    private static void executeStackedColumn(boolean is3D)
    {
        //Create a Workbook
        Workbook workbook = new Workbook();

        //Get the first sheet and set its name
        Worksheet sheet = workbook.getWorksheets().get(0);
        sheet.setName("StackedColumn");

        //Set chart data
        createChartData(sheet);

        //Add a chart
        Chart chart = sheet.getCharts().add();

        //Set region of chart data
        chart.setDataRange(sheet.getCellRange("A1:C5"));
        chart.setSeriesDataFromRange(false);

        //Set position of chart
        chart.setLeftColumn(1);
        chart.setTopRow(6);
        chart.setRightColumn(11);
        chart.setBottomRow(29);

        if (is3D)
        {
            chart.setChartType(ExcelChartType.Column3DStacked);
        }
        else
        {
            chart.setChartType(ExcelChartType.ColumnStacked);
        }

        //Chart title
        chart.setChartTitle("Sales market by country");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        //Chart Axis
        chart.getPrimaryCategoryAxis().setTitle("Country");
        chart.getPrimaryCategoryAxis().getFont().isBold(true);
        chart.getPrimaryCategoryAxis().getTitleArea().isBold(true);

        chart.getPrimaryValueAxis().setTitle("Sales(in Dollars)");
        chart.getPrimaryValueAxis().hasMajorGridLines(false);
        chart.getPrimaryValueAxis().setMinValue(1000);
        chart.getPrimaryValueAxis().getTitleArea().isBold(true);
        chart.getPrimaryValueAxis().getTitleArea().setTextRotationAngle(90);

        ChartSeries series = chart.getSeries();
        for (int i = 0;i < series.size();i++)
        {
            ChartSerie cs = series.get(i);
            cs.getFormat().getOptions().isVaryColor(true);
            cs.getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        }

        //Chart legend
        chart.getLegend().setPosition(LegendPositionType.Top);

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

    }

    private static void createChartData(Worksheet sheet)
    {
        //Set value of specified cell
        sheet.getCellRange("A1").setValue("Country");
        sheet.getCellRange("A2").setValue("Cuba");
        sheet.getCellRange("A3").setValue("Mexico");
        sheet.getCellRange("A4").setValue("France");
        sheet.getCellRange("A5").setValue("German");

        sheet.getCellRange("B1").setValue("Jun");
        sheet.getCellRange("B2").setNumberValue(6000);
        sheet.getCellRange("B3").setNumberValue(8000);
        sheet.getCellRange("B4").setNumberValue(9000);
        sheet.getCellRange("B5").setNumberValue(8500);

        sheet.getCellRange("C1").setValue("Aug");
        sheet.getCellRange("C2").setNumberValue(3000);
        sheet.getCellRange("C3").setNumberValue(2000);
        sheet.getCellRange("C4").setNumberValue(2300);
        sheet.getCellRange("C5").setNumberValue(4200);

        //Style
        sheet.getCellRange("A1:C1").setRowHeight(15);
        sheet.getCellRange("A1:C1").getCellStyle().setColor(Color.darkGray);
        sheet.getCellRange("A1:C1").getCellStyle().getExcelFont().setColor(Color.white);
        sheet.getCellRange("A1:C1").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getCellRange("A1:C1").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);

        sheet.getCellRange("B2:C5").getCellStyle().setNumberFormat("\"$\"#,##0");
    }
}

Effective screenshot of column stacked chart. ColumnStacked represents Stacked Column Chart.

Create Excel column chart in Java

Java generate Column clustered chart:

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

public class ColumnChart {
    public static void main(String[] args) {
        executeClusteredColumn(true);
    }
    private static void executeClusteredColumn(boolean is3D)
    {
        //Create a Workbook
        Workbook workbook = new Workbook();

        //Get the first sheet and set its name
        Worksheet sheet = workbook.getWorksheets().get(0);
        sheet.setName("ClusteredColumn");

        //Set chart data
        createChartData(sheet);

        //Add a chart
        Chart chart = sheet.getCharts().add();

        //Set region of chart data
        chart.setDataRange(sheet.getCellRange("A1:C5"));
        chart.setSeriesDataFromRange(false);

        //Set position of chart
        chart.setLeftColumn(1);
        chart.setTopRow(6);
        chart.setRightColumn(11);
        chart.setBottomRow(29);

        if (is3D)
        {
            chart.setChartType(ExcelChartType.Column3DClustered);
        }
        else
        {
            chart.setChartType(ExcelChartType.ColumnClustered);
        }

        //Chart title
        chart.setChartTitle("Sales market by country");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        //Chart Axis
        chart.getPrimaryCategoryAxis().setTitle("Country");
        chart.getPrimaryCategoryAxis().getFont().isBold(true);
        chart.getPrimaryCategoryAxis().getTitleArea().isBold(true);

        chart.getPrimaryValueAxis().setTitle("Sales(in Dollars)");
        chart.getPrimaryValueAxis().hasMajorGridLines(false);
        chart.getPrimaryValueAxis().setMinValue(1000);
        chart.getPrimaryValueAxis().getTitleArea().isBold(true);
        chart.getPrimaryValueAxis().getTitleArea().setTextRotationAngle(90);

        ChartSeries series = chart.getSeries();
        for (int i = 0;i < series.size();i++)
        {
            ChartSerie cs = series.get(i);
            cs.getFormat().getOptions().isVaryColor(true);
            cs.getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        }

        //Chart legend
        chart.getLegend().setPosition(LegendPositionType.Top);

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

    }

    private static void createChartData(Worksheet sheet)
    {
        //Set value of specified cell
        sheet.getCellRange("A1").setValue("Country");
        sheet.getCellRange("A2").setValue("Cuba");
        sheet.getCellRange("A3").setValue("Mexico");
        sheet.getCellRange("A4").setValue("France");
        sheet.getCellRange("A5").setValue("German");

        sheet.getCellRange("B1").setValue("Jun");
        sheet.getCellRange("B2").setNumberValue(6000);
        sheet.getCellRange("B3").setNumberValue(8000);
        sheet.getCellRange("B4").setNumberValue(9000);
        sheet.getCellRange("B5").setNumberValue(8500);

        sheet.getCellRange("C1").setValue("Aug");
        sheet.getCellRange("C2").setNumberValue(3000);
        sheet.getCellRange("C3").setNumberValue(2000);
        sheet.getCellRange("C4").setNumberValue(2300);
        sheet.getCellRange("C5").setNumberValue(4200);

        //Style
        sheet.getCellRange("A1:C1").setRowHeight(15);
        sheet.getCellRange("A1:C1").getCellStyle().setColor(Color.darkGray);
        sheet.getCellRange("A1:C1").getCellStyle().getExcelFont().setColor(Color.white);
        sheet.getCellRange("A1:C1").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getCellRange("A1:C1").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);

        sheet.getCellRange("B2:C5").getCellStyle().setNumberFormat("\"$\"#,##0");
    }
}

Effective screenshot of column clustered chart:

Create Excel column chart in Java

Tuesday, 01 March 2022 01:45

Java: Insert or Format Comments in Excel

Comments in an Excel cell are commonly used for sharing extra information or reviews about the data inside the cell. Comments make it easy to remember, follow up, or reference the data in your worksheet. With Spire.XLS for Java, you can insert a comment to Excel as well as formatting a comment with easy. In this article, we will show you how to add a comment to your Excel spreadsheet in Java from the following three parts.

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

Add Comments in an Excel Worksheet

Spire.XLS offers the CellRange.addComment() method to insert the regular text comment to Excel worksheets.

  • Create a Workbook instance.
  • Load a sample Excel document using Workbook.loadFromFile() method.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Add a comment in a specific cell range using CellRange.addComment() method and then set the comment text through the ExcelComment.setText() method.
  • Save the document to another file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

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

        //Load the sample document from file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

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

        //Add regular comment to specific cell range C6
        CellRange range = sheet.getCellRange("C6");
        ExcelComment comment = range.addComment();
        comment.setVisible(true);
        comment.setText("Regular comment");

        //Save the document to another file
        workbook.saveToFile("Addcomment.xlsx", ExcelVersion.Version2016);

    }
}

Java: Insert or Format Comments in Excel

Apply Formatting to Comments in an Excel Worksheet

Spire.XLS offers the Comment.getRichText().setFont() method to apply font formatting for comments in Excel worksheets.

  • Initialize an instance of Workbook class and load an Excel file using Workbook.loadFromFile() method.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Add a comment in a specific cell range using CellRange.addComment() method and then set the comment text.
  • Create an ExcelFont object and apply the font to the comment text using ExcelComment.getRichText.setFont() method.
  • Save the document to another file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

import java.awt.*;

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

        //Load the sample document from file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

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

        //Create font
        ExcelFont font = workbook.createFont();
        font.setFontName("Calibri");
        font.setSize(12);
        font.setColor(Color.orange);
        font.isBold(true);

        //Add regular comment to specific cell range C6
        CellRange range = sheet.getCellRange("C6");
        ExcelComment comment = range.addComment();
        comment.setVisible(true);
        comment.setHeight(100);
        comment.setWidth(200);
        comment.getRichText().setText("Spire.XLS for Java Rich Text Comment ");
        comment.getRichText().setFont(0, 40, font);
        comment.setTextRotation(TextRotationType.LeftToRight);

        //Set the alignment of text in Comment
        comment.setVAlignment(CommentVAlignType.Center);
        comment.setHAlignment(CommentHAlignType.Justified);


        //Save the document to another file
        workbook.saveToFile("AddRichTextcomment.xlsx", ExcelVersion.Version2016);

    }
}

Java: Insert or Format Comments in Excel

Add Comment with Author in an Excel Worksheet

Spire.XLS offers the ExcelComment.setText() method to insert the comment with author to Excel worksheets.

  • Create a Workbook instance.
  • Load a sample Excel document using Workbook.loadFromFile() method.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Add a comment in a specific cell range using CellRange.addComment() method.
  • Define the comment text and author and then add them as the comment content through the ExcelComment.setText() method.
  • Save the document to another file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

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

        //Load the sample document from file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

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

        //Add regular comment to specific cell range C6
        CellRange range = sheet.getCellRange("C6");
        ExcelComment comment = range.addComment();
        comment.setVisible(true);

        //Set the author and comment content
        String text = "Add a comment with Author";
        String author = "E-iceblue:";
        comment.setText(author + "\r" + text);

        //Save the document to another file
        workbook.saveToFile("Addcomment.xlsx", ExcelVersion.Version2016);

    }
}

Java: Insert or Format Comments 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.

In MS Excel, rows arrange objects from left to right and are identified by row numbers. While columns, on the contrary, arrange objects from top to bottom and are identified by column headers. When processing data in Excel, sometimes you may need to insert additional columns and rows in the middle of your data table, or delete unwanted columns and rows. In this article, you will learn how to complete the below tasks programmatically 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.3.2</version>
    </dependency>
</dependencies>
    

Insert a Row and a Column in Excel

Below are the steps to insert a blank row and a blank column in an Excel worksheet.

  • Create a Workbook instance.
  • Load a sample Excel file using Workbook.loadFromFile() method.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Insert a row into the worksheet using Worksheet.insertRow(int rowIndex) method.
  • Insert a column into the worksheet using Worksheet.insertColumn(int columnIndex) method.
  • Save the result file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

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

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

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

        //Insert a row into the worksheet
        worksheet.insertRow(4);

        //Insert a column into the worksheet
        worksheet.insertColumn(4);

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

Java: Insert or Delete Rows and Columns in Excel

Insert Multiple Rows and Columns in Excel

Below are the steps to insert multiple blank rows and columns in an Excel worksheet.

  • Create a Workbook instance.
  • Load a sample Excel file using Workbook.loadFromFile() method.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Insert multiple rows into the worksheet using Worksheet.insertRow((int rowIndex, int rowCount) method.
  • Insert multiple columns into the worksheet using Worksheet.insertColumn(int columnIndex, int columnCount) method.
  • Save the result file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

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

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

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

        //Insert multiple rows into the worksheet.
        worksheet.insertRow(5, 3);

        //Insert multiple columns into the worksheet.
        worksheet.insertColumn(4, 2);
        
        //Save the result file
        workbook.saveToFile("InsertRowsAndColumns.xlsx", ExcelVersion.Version2013);
    }
}

Java: Insert or Delete Rows and Columns in Excel

Delete a Specific Row and Column in Excel

Below are the steps to delete a specific row and column in an Excel worksheet.

  • Create a Workbook instance.
  • Load a sample Excel file using Workbook.loadFromFile() method.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Delete a specific row from the worksheet using Worksheet.deleteRow(int index) method.
  • Delete a specific column from the worksheet using Worksheet.deleteColumn(int columnIndex) method.
  • Save the result file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

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

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

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

        //Delete a specific row from the worksheet
        worksheet.deleteRow(4);

        //Delete a specific column from the worksheet
        worksheet.deleteColumn(1);

        //Save to file.
        workbook.saveToFile("DeleteRowAndColumn.xlsx", ExcelVersion.Version2013);

    }
}

Java: Insert or Delete Rows and Columns in Excel

Delete Multiple Rows and Columns in Excel

Below are the steps to delete multiple rows and columns in an Excel worksheet.

  • Create a Workbook instance.
  • Load a sample Excel file using Workbook.loadFromFile() method.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Delete multiple rows from the worksheet using Worksheet.deleteRow(int index, int count) method.
  • Delete multiple columns from the worksheet using Worksheet.deleteColumn(int columnIndex, int count) method.
  • Save the result file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

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

        //Load the sample Excel file
        workbook.loadFromFile("E:\\Files\\Sample.xlsx");

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

        //Delete multiple rows from the worksheet
        worksheet.deleteRow(5, 3);

        //Delete multiple columns from the worksheet
        worksheet.deleteColumn(5, 2);

        //Save to file.
        workbook.saveToFile("DeleteRowsAndColumns.xlsx", ExcelVersion.Version2013);

    }
}

Java: Insert or Delete Rows and Columns 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.