Program Guide (129)
Children categories
Splitting a worksheet can be beneficial when you have a large amount of data and want to organize it into separate files for easier management and sharing. By using this approach, you can organize and distribute your data in a more organized and structured manner. In this tutorial, we will demonstrate how to split a worksheet into multiple Excel documents 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>15.12.15</version>
</dependency>
</dependencies>
Split a Worksheet into Several Excel Files
Spire.XLS for Java provides powerful features that enable us to achieve this task efficiently. The specific steps are as follows.
- Create a Workbook object.
- Load a sample Excel file using Workbook.loadFromFile() method.
- Get the specific sheet using Workbook.getWorksheets().get() method.
- Get the header row and cell ranges using Worksheet.getCellRange() method.
- Create a new workbook and copy the header row and range 1 to the new workbook using Worksheet.copy(CellRange sourceRange, Worksheet worksheet, int destRow, int destColumn, boolean copyStyle, boolean updateRerence) method.
- Copy the column width from the original workbook to the new workbook using Workbook.getWorksheets().get(0).setColumnWidth() method.
- Save the new workbook to an Excel file using Workbook.saveToFile() method.
- Repeat the above operation to copy the header row and range 2 to another new workbook, and save it to another Excel file.
- Java
import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class SplitWorksheet {
public static void main(String[] args) {
//Create a Workbook object to load the original Excel document
Workbook bookOriginal = new Workbook();
bookOriginal.loadFromFile("C:\\Users\\Administrator\\Desktop\\Emplyees.xlsx");
//Get the first worksheet
Worksheet sheet = bookOriginal.getWorksheets().get(0);
//Get the header row
CellRange headerRow = sheet.getCellRange(1, 1, 1, 5);
//Get two cell ranges
CellRange range1 = sheet.getCellRange(2, 1, 6, 5);
CellRange range2 = sheet.getCellRange(7, 1, 11, 5);
//Create a new workbook
Workbook newBook1 = new Workbook();
//Copy the header row and range 1 to the new workbook
sheet.copy(headerRow, newBook1.getWorksheets().get(0), 1, 1, true, false);
sheet.copy(range1, newBook1.getWorksheets().get(0), 2, 1, true, false);
//Copy the column width from the original workbook to the new workbook
for (int i = 0; i < sheet.getLastColumn(); i++) {
newBook1.getWorksheets().get(0).setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
}
//Save the new workbook to an Excel file
newBook1.saveToFile("Sales.xlsx", ExcelVersion.Version2016);
//Create another new workbook
Workbook newBook2 = new Workbook();
//Copy the header row and range 2 to the new workbook
sheet.copy(headerRow, newBook2.getWorksheets().get(0), 1, 1, true, false);
sheet.copy(range2, newBook2.getWorksheets().get(0), 2, 1, true, false);
//Copy the column width from the original workbook to another new workbook
for (int i = 0; i < sheet.getLastColumn(); i++) {
newBook2.getWorksheets().get(0).setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
}
//Save it to another new Excel file
newBook2.saveToFile("Technicians.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.
This article demonstrates how to split a worksheet into several Excel documents by using Spire.XLS for Java.
import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class SplitWorksheet {
public static void main(String[] args) {
//Create a Workbook object to load the original Excel document
Workbook bookOriginal = new Workbook();
bookOriginal.loadFromFile("C:\\Users\\Administrator\\Desktop\\Emplyees.xlsx");
//Get the first worksheet
Worksheet sheet = bookOriginal.getWorksheets().get(0);
//Get the header row
CellRange headerRow = sheet.getCellRange(1, 1, 1, 5);
//Get two cell ranges
CellRange range1 = sheet.getCellRange(2, 1, 6, 5);
CellRange range2 = sheet.getCellRange(7, 1, 11, 5);
//Create a new workbook
Workbook newBook1 = new Workbook();
//Copy the header row and range 1 to the new workbook
sheet.copy(headerRow, newBook1.getWorksheets().get(0), 1, 1, true, false);
sheet.copy(range1, newBook1.getWorksheets().get(0), 2, 1, true, false);
//Copy the column width from the original workbook to the new workbook
for (int i = 0; i < sheet.getLastColumn(); i++) {
newBook1.getWorksheets().get(0).setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
}
//Save the new workbook to an Excel file
newBook1.saveToFile("Sales.xlsx", ExcelVersion.Version2016);
//Copy the header row and range 2 to another workbook, and save it to another Excel file
Workbook newBook2 = new Workbook();
sheet.copy(headerRow, newBook2.getWorksheets().get(0), 1, 1, true, false);
sheet.copy(range2, newBook2.getWorksheets().get(0), 2, 1, true, false);
for (int i = 0; i < sheet.getLastColumn(); i++) {
newBook2.getWorksheets().get(0).setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
}
newBook2.saveToFile("Technicians.xlsx", ExcelVersion.Version2016);
}
}
Add Trendline to Chart and Read Trendline Equation in Excel in Java
2020-11-17 08:12:33 Written by KoohjiThis article demonstrates how to add Trendline to an Excel chart and read the equation of the Trendline using Spire.XLS for Java.
Add Trendline
import com.spire.xls.*;
import com.spire.xls.core.IChartTrendLine;
import java.awt.*;
public class AddTrendline {
public static void main(String[] args){
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load the Excel file
workbook.loadFromFile("test.xlsx");
//Get the first chart in the first worksheet
Chart chart = workbook.getWorksheets().get(0).getCharts().get(0);
//Add a Trendline to the first series of the chart
IChartTrendLine trendLine = chart.getSeries().get(0).getTrendLines().add(TrendLineType.Linear);
//Set Trendline name
trendLine.setName("Linear(Series1)");
//Set line type and color
trendLine.getBorder().setPattern(ChartLinePatternType.DashDot);
trendLine.getBorder().setColor(Color.blue);
//Set forward and backward value
trendLine.setForward(0.5);
trendLine.setBackward(0.5);
//Set intercept value
trendLine.setIntercept(5);
//Display equation on chart
trendLine.setDisplayEquation(true);
//Display R-Squared value on chart
trendLine.setDisplayRSquared(true);
//Save the result file
workbook.saveToFile("AddTrendline.xlsx", ExcelVersion.Version2013);
}
}
Read Trendline equation
import com.spire.xls.Chart;
import com.spire.xls.Workbook;
import com.spire.xls.core.IChartTrendLine;
public class ReadEquationOfTrendline {
public static void main(String[] args){
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load the Excel file
workbook.loadFromFile("AddTrendline.xlsx");
//Get the first chart in the first worksheet
Chart chart = workbook.getWorksheets().get(0).getCharts().get(0);
//Read the equation of the first series of the chart
IChartTrendLine trendLine = chart.getSeries().get(0).getTrendLines().get(0);
String equation = trendLine.getFormula();
System.out.println("The equation is: " + equation);
}
}
This article demonstrates how to set Excel page margins before printing the Excel worksheets in Java applications. By using Spire.XLS for Java, we could set top margin, bottom margin, left margin, right margin, header margin, and footer margin. Please note that the unit for margin is inch on Spire.XLS for Java while On Microsoft Excel, it is cm (1 inch=2.54 cm).
import com.spire.xls.*;
public class setMargins {
public static void main(String[] args) {
String outputFile="output/setMarginsOfExcel.xlsx";
//Load the sample document from file
Workbook workbook = new Workbook();
workbook.loadFromFile("Sample.xlsx";);
//Get the first worksheet.
Worksheet sheet = workbook.getWorksheets().get(0);
//Get the PageSetup object of the first worksheet.
PageSetup pageSetup = sheet.getPageSetup();
//Set the page margins of bottom, left, right and top.
pageSetup.setBottomMargin(2);
pageSetup.setLeftMargin(1);
pageSetup.setRightMargin(1);
pageSetup.setTopMargin(3);
//Set the margins of header and footer.
pageSetup.setHeaderMarginInch(2);
pageSetup.setFooterMarginInch(2);
//Save to file.
workbook.saveToFile(outputFile, ExcelVersion.Version2013);
}
}
Output:
This article demonstrates how to create a scatter chart and add a trendline to it in an Excel document by using Spire.XLS for Java.
import com.spire.xls.*;
import com.spire.xls.core.IChartTrendLine;
import java.awt.*;
public class ScatterChart {
public static void main(String[] args) {
//Create a a Workbook object and get the first worksheet
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
//Rename the first worksheet and set the column width
sheet.getCellRange("A1:B1").setColumnWidth(22f);;
sheet.setName("Scatter Chart");
//Insert data
sheet.getCellRange("A1").setValue("Advertising Expenditure");
sheet.getCellRange("A2").setValue("10429");
sheet.getCellRange("A3").setValue("95365");
sheet.getCellRange("A4").setValue("24085");
sheet.getCellRange("A5").setValue("109154");
sheet.getCellRange("A6").setValue("34006");
sheet.getCellRange("A7").setValue("84687");
sheet.getCellRange("A8").setValue("17560");
sheet.getCellRange("A9").setValue ("61408");
sheet.getCellRange("A10").setValue ("29402");
sheet.getCellRange("B1").setValue("Sales Revenue");
sheet.getCellRange("B2").setValue ("42519");
sheet.getCellRange("B3").setValue("184357");
sheet.getCellRange("B4").setValue ("38491");
sheet.getCellRange("B5").setValue ("214956");
sheet.getCellRange("B6").setValue ("75469");
sheet.getCellRange("B7").setValue ("134735");
sheet.getCellRange("B8").setValue("47935");
sheet.getCellRange("B9").setValue ("151832");
sheet.getCellRange("B10").setValue ("65424");
//Set cell style
sheet.getCellRange("A1:B1").getStyle().getFont().isBold(true);
sheet.getCellRange("A1:B1").getStyle().setColor(Color.darkGray);
sheet.getCellRange("A1:B1").getCellStyle().getExcelFont().setColor(Color.white);
sheet.getCellRange("A1:B10").getStyle().setHorizontalAlignment(HorizontalAlignType.Center);
sheet.getCellRange("A2:B10").getCellStyle().setNumberFormat("\"$\"#,##0") ;
//Create a scatter chart and set its data range
Chart chart = sheet.getCharts().add(ExcelChartType.ScatterMarkers);
chart.setDataRange(sheet.getCellRange("B2:B10"));
chart.setSeriesDataFromRange(false);
//Set position of the chart.
chart.setLeftColumn(4);
chart.setTopRow(1);
chart.setRightColumn(13);
chart.setBottomRow(22);
//Set chart title and series data label
chart.setChartTitle("Advertising & Sales Relationship");
chart.getChartTitleArea().isBold(true);
chart.getChartTitleArea().setSize(12);
chart.getSeries().get(0).setCategoryLabels(sheet.getCellRange("B2:B10"));
chart.getSeries().get(0).setValues(sheet.getCellRange("A2:A10"));
//Add a trendline
IChartTrendLine trendLine = chart.getSeries().get(0).getTrendLines().add(TrendLineType.Exponential);
trendLine.setName("Trendline");
//Set title of the x and y axis
chart.getPrimaryValueAxis().setTitle("Advertising Expenditure ($)");
chart.getPrimaryCategoryAxis().setTitle("Sales Revenue ($)");
//Save the document
workbook.saveToFile("ScatterChart.xlsx",ExcelVersion.Version2010);
workbook.dispose();
}
}
When manipulating Excel data, the "Text to Columns" feature in MS Excel is a handy tool that allows users to separate text in a single cell into multiple columns. This functionality is extremely useful when dealing with data that imported in a less structured format. For Java developers, being able to replicate this operation programmatically can significantly enhance the automation of data processing tasks involving Excel spreadsheets.
This guide will walk you through how to utilize the Spire.XLS for Java library to split text into multiple columns in Excel in Java.
- Java Library for Working with Excel
- Step-by-Step Guide to Splitting Excel Text to Columns
- Handling Different Delimiters
Java Library for Working with Excel
The Spire.XLS for Java library is a practical solution for reading, writing and converting Excel XLS or XLSX files in Java. To start using it, you need to add the appropriate dependency. There are two common ways to import:
Method 1: Install via Maven
If you are using Maven, add the following to your 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>15.12.15</version>
</dependency>
</dependencies>
Method 2: Manual Installation
- Download Spire.XLS for Java package through the official website.
- Unzip it to get the Spire.Xls.jar file and then add the JAR file to your project.
Step-by-Step Guide to Splitting Excel Text to Columns
- Load Excel File and Access a Worksheet
Use the Workbook.loadFromFile() method to load the input Excel file, and then access the specific worksheet in it.
- Access Cells and Get Cell Data
Iterate through each row in the sheet. Access a specified cell and then get its text through the CellRange.getText() method.
- Split Text in Excel Cells
Use the String.split(String regex) method to split the cell text based on the specified delimiter (e.g., ",").
- Write the Split Text to Columns
Iterate through each split data and then write it into different columns.
- Save the Modified Excel File
Use the Workbook.saveToFile() method to save the modified Excel file.
Sample Java Code:
- Java
import com.spire.xls.*;
public class splitDataIntoMultipleColumns {
public static void main(String[] args) {
// Create a Workbook object
Workbook workbook = new Workbook();
// Load an Excel file
workbook.loadFromFile("Data.xlsx");
// Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
// Iterate through each row in the worksheet
for (int i = 0; i < sheet.getLastRow(); i++)
{
// Get the text of the first cell in the current row
String text = sheet.getRange().get(i + 1, 1).getText();
// Split the text by comma
String[] splitText = text.split(",");
// Iterate through each split data
for (int j = 0; j < splitText.length; j++)
{
// Write the split data to different columns
sheet.getRange().get(i + 1, j + 3).setText(splitText[j]);
}
}
// Autofit column widths
sheet.getAllocatedRange().autoFitColumns();
// Save the result file
workbook.saveToFile("SplitExcelData.xlsx", ExcelVersion.Version2016);
}
}
Result File:
Handling Different Delimiters
To split text by other delimiters such as spaces, semicolons, or tabs, modify the regex in the split() method. Examples:
- Spaces: String.split(" ")
- Semicolons: String.split(";")
- Tabs: String.split("\t")
Conclusion
Splitting text into columns in Excel using Java is effortless with Spire.XLS for Java API. By automating this task, you can enhance productivity and ensure data consistency. Whether you’re processing user inputs, logs, or reports, this approach adapts to various delimiters and use cases.
Get a Free License
To fully experience the capabilities of Spire.XLS for Java without any evaluation limitations, you can request a free 30-day trial license.
This article demonstrates how to create pivot chart in an Excel file in Java using Spire.XLS for Java.
The input Excel file:
import com.spire.xls.*;
import com.spire.xls.core.IPivotTable;
public class CreatePivotChart {
public static void main(String[] args) {
//Load the Excel file
Workbook workbook = new Workbook();
workbook.loadFromFile("Sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//get the first pivot table in the worksheet
IPivotTable pivotTable = sheet.getPivotTables().get(0);
//Add a clustered column chart based on the pivot table data to the second worksheet
Chart chart = workbook.getWorksheets().get(1).getCharts().add(ExcelChartType.ColumnClustered, pivotTable);
//Set chart position
chart.setTopRow(2);
chart.setBottomRow(15);
//Set chart title
chart.setChartTitle("Total");
//Save the result file
workbook.saveToFile("CreatPivotChart.xlsx", ExcelVersion.Version2013);
}
}
Output:
This article demonstrates how to set dpi on x and y axis when converting an Excel worksheet to an image using Spire.XLS for Java.
import com.spire.xls.*;
public class ConvertExcelToImage {
public static void main(String[] args) {
//Create a Workbook object
Workbook wb = new Workbook();
//Load an Excel file
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx");
//Set dpi on x and y axis
wb.getConverterSetting().setXDpi(300);
wb.getConverterSetting().setYDpi(300);
//Declare a Worksheet variable
Worksheet sheet;
//Loop through the worksheets
for (int i = 0; i < wb.getWorksheets().size(); i++) {
//Get the specific worksheet
sheet = wb.getWorksheets().get(i);
//Convert worksheet to image
sheet.saveToImage("C:\\Users\\Administrator\\Desktop\\Output\\image-" + i + ".png");
}
}
}
This article demonstrates how to detect merged cells in an Excel worksheet and unmerge the merged cells using Spire.XLS for Java.
The input Excel file:
import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class DetectMergedCells {
public static void main(String[] args) throws Exception {
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load the Excel file
workbook.loadFromFile( "Input.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Get the merged cell ranges in the first worksheet and put them into a CellRange array
CellRange[] range = sheet.getMergedCells();
//Traverse through the array and unmerge the merged cells
for(CellRange cell : range){
cell.unMerge();
}
//Save the result file
workbook.saveToFile("DetectMergedCells.xlsx", ExcelVersion.Version2013);
}
}
The output Excel file:
Hyperlinks are useful features in Excel documents, providing quick access to other relevant resources such as websites, email addresses, or specific cells within the same workbook. However, sometimes you may want to modify or delete existing hyperlinks for various reasons, such as updating broken links, correcting typos, or removing outdated information. In this article, we will demonstrate how to modify or delete hyperlinks in Excel in Java using Spire.XLS for Java library.
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>15.12.15</version>
</dependency>
</dependencies>
Modify Hyperlinks in Excel in Java
If there are issues with the functionality of a hyperlink caused by damage or spelling errors, you may need to modify it. The following steps demonstrate how to modify an existing hyperlink in an Excel file:
- Create an instance of Workbook class.
- Load an Excel file using the Workbook.loadFromFile() method.
- Get a specific worksheet using the Workbook.getWorksheets().get() method.
- Get the collection of all hyperlinks in the worksheet using the Worksheet.getHyperLinks() method.
- Change the values of TextToDisplay and Address property using the HyperLinksCollection.get().setTextToDisplay() and HyperLinksCollection.get().setAddress method.
- Save the result file using the Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
import com.spire.xls.collections.HyperLinksCollection;
public class ModifyHyperlink {
public static void main(String[] args) {
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.loadFromFile("Sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Get the collection of all hyperlinks in the worksheet
HyperLinksCollection links = sheet.getHyperLinks();
//Change the values of TextToDisplay and Address property
links.get(0).setTextToDisplay("Republic of Indonesia");
links.get(0).setAddress("https://www.indonesia.travel/gb/en/home");
//Save the document
workbook.saveToFile("ModifyHyperlink.xlsx", ExcelVersion.Version2013);
workbook.dispose();
}
}
Delete Hyperlinks from Excel in Java
Spire.XLS for Java also offers the Worksheet.getHyperLinks().removeAt() method to remove hyperlinks. The following are the steps to delete hyperlink from Excel in Java.
- Create an instance of Workbook class.
- Load an Excel file using the Workbook.loadFromFile() method.
- Get a specific worksheet using the Workbook.getWorksheets().get() method.
- Get the collection of all hyperlinks in the worksheet using the Worksheet.getHyperLinks() method.
- Remove a specific hyperlink and keep link text using the Worksheet.getHyperLinks().removeAt() method.
- Save the result file using the Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
import com.spire.xls.collections.HyperLinksCollection;
public class RemoveHyperlink {
public static void main(String[] args) {
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.loadFromFile("Sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Get the collection of all hyperlinks in the worksheet
HyperLinksCollection links = sheet.getHyperLinks();
//Remove the first hyperlink and keep link text
sheet.getHyperLinks().removeAt(0);
//Remove all content from the cell
//sheet.getCellRange("A7").clearAll();
//Save the document
String output = "RemoveHyperlink.xlsx";
workbook.saveToFile(output, ExcelVersion.Version2013);
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.
