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); } }
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.
- Password Protect an Entire Workbook
- Protect a Worksheet with a Specific Protection Type
- Allow Users to Edit Ranges in a Protected Worksheet
- Lock Specific Cells in a Worksheet
- Unprotect a Password Protected Worksheet
- Remove or Reset Password of an Encrypted Workbook
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); } }
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); } }
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); } }
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.
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); } }
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); } }
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); } }
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.
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); } }
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"); } }
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.
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.
- Find and Replace Data in a Worksheet in Excel
- Find and Replace Data in a Specific Cell Range in Excel
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(); } }
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(); } }
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.
Java: Create a Pie Chart or a Doughnut Chart in Excel
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); } }
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); } }
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.
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.
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:
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.
- Add Comments in an Excel Worksheet
- Apply Formatting to Comments in an Excel Worksheet
- Add Comment with Author in an Excel Worksheet
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); } }
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); } }
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); } }
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.
Java: Insert or Delete Rows and Columns in Excel
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.
- Insert a Row and a Column in Excel
- Insert Multiple Rows and Columns in Excel
- Delete a Specific Row and Column in Excel
- Delete Multiple Rows and Columns in Excel
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); } }
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); } }
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); } }
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); } }
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.