Java (482)
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>16.3.2</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.
Published in
Document Operation
Tagged under
This article will demonstrate how to set the zoom factor/percentage (such as default, 100 percent or any other zoom factors as required) and the viewer preference by using Spire.PDF for Java in Java applications.
Set the zoom factor
import com.spire.pdf.*;
import com.spire.pdf.actions.*;
import com.spire.pdf.general.*;
import java.awt.geom.*;
public class setZoomFactor {
public static void main(String[] args) {
//Load the sample document
PdfDocument doc = new PdfDocument();
doc.loadFromFile("Sample.pdf");
//Get the first page of PDF
PdfPageBase page = doc.getPages().get(0);
//Set pdf destination
PdfDestination dest = new PdfDestination(page);
dest.setMode(PdfDestinationMode.Location);
dest.setLocation(new Point2D.Float(-40f, -40f));
//Set zoom factor
dest.setZoom(0.8f);
//Set action
PdfGoToAction gotoAction = new PdfGoToAction(dest);
doc.setAfterOpenAction(gotoAction);
//Save pdf document
String output = "output/setZoomFactor.pdf";
doc.saveToFile(output);
}
}
Output:
Set the viewer preference
import com.spire.pdf.*;
public class viewerPreference {
public static void main(String[] args) {
//Load the sample document
PdfDocument doc = new PdfDocument();
doc.loadFromFile("Sample.pdf");
//Set viewer reference
doc.getViewerPreferences().setCenterWindow(true);
doc.getViewerPreferences().setDisplayTitle(false);
doc.getViewerPreferences().setFitWindow(false);
doc.getViewerPreferences().setHideMenubar(true);
doc.getViewerPreferences().setHideToolbar(true);
doc.getViewerPreferences().setPageLayout(PdfPageLayout.Two_Column_Left);
//Save pdf document
String output = "output/viewerPreference.pdf";
doc.saveToFile(output);
}
}
Output:
Published in
Page Setting
Tagged under
This article will show you how to use Spire.PDF for Java to draw superscript and subscript text to PDF file in Java applications.
Draw Superscript Text
import com.spire.pdf.*;
import com.spire.pdf.graphics.*;
import java.awt.*;
import java.awt.geom.*;
public class PdfSuperscriptText {
public static void main(String[] args) {
//Create a new PdfDocument instance
PdfDocument doc = new PdfDocument();
//Add a page to pdf
PdfPageBase page = doc.getPages().add();
//Set the font
PdfTrueTypeFont font = new PdfTrueTypeFont(new Font("Arial", Font.PLAIN,14),true);
PdfSolidBrush brush = new PdfSolidBrush(new PdfRGBColor(Color.black));
//Set initial (x, y) coordinate
float x = 120f;
float y = 100f;
//Draw text string
String text = "Sample Text";
page.getCanvas().drawString(text, font, brush, new Point2D.Float(x, y));
//Measure the string
Dimension2D size = font.measureString(text);
x += size.getWidth();
//Draw the text string and set the format as Superscript
PdfStringFormat format = new PdfStringFormat();
format.setSubSuperScript(PdfSubSuperScript.Super_Script);
text = "Superscrip";
page.getCanvas().drawString(text, font, brush, new Point2D.Float(x, y), format);
//Save the document to file
String result="output/superScript.pdf";
doc.saveToFile(result);
}
}
Effective screenshot:
Draw Subscript Text
import com.spire.pdf.*;
import com.spire.pdf.graphics.*;
import java.awt.*;
import java.awt.geom.*;
public class PdfSubscriptText {
public static void main(String[] args) {
//Create a new PdfDocument instance
PdfDocument doc = new PdfDocument();
//Add a page to pdf
PdfPageBase page = doc.getPages().add();
//Set the font
PdfTrueTypeFont font = new PdfTrueTypeFont(new Font("Arial", Font.PLAIN,14),true);
PdfSolidBrush brush = new PdfSolidBrush(new PdfRGBColor(Color.black));
//Set initial (x, y) coordinate
float x = 120f;
float y = 100f;
//Draw text string
String text = "Sample Text";
page.getCanvas().drawString(text, font, brush, new Point2D.Float(x, y));
//Measure the string
Dimension2D size = font.measureString(text);
x += size.getWidth();
//Draw the text string and set the format as Subscript
PdfStringFormat format = new PdfStringFormat();
format.setSubSuperScript(PdfSubSuperScript.Sub_Script);
text = "Subscrip";
page.getCanvas().drawString(text, font, brush, new Point2D.Float(x, y), format);
//Save the document to file
String result="output/subScript.pdf";
doc.saveToFile(result);
}
}
Output:
Published in
Text
Tagged under
