Spire.XLS for Java (129)
Children categories
Worksheets are essential components of spreadsheets, primarily used for storing and managing data. In daily work, we often need to perform certain operations on worksheets in Excel. For instance, we may need to add new worksheets to an existing Excel file to append related contents to the file or divide existing data into different types or labels, which is conducive to organizing and managing the spreadsheet well. Likewise, we can also delete unnecessary or invalid worksheets to save storage space and make the file clearer. Adding or removing worksheets programmatically can avoid the inconvenience of manual operations and allow for efficient handling of large workbooks in a short time. This article introduces how to add or remove worksheets 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>15.12.15</version>
</dependency>
</dependencies>
Add a Worksheet to an Existing Workbook in Java
Spire.XLS for Java provides Workbook.getWorksheets().add() method to add new worksheets to an existing workbook. The following are the detailed steps for this operation.
- Specify input and output paths.
- Create a Workbook object.
- Load a sample Excel file using Workbook.loadFromFile() method.
- Add a new worksheet named "AddNewSheet" to the Excel file using Workbook.getWorksheets().add() method.
- Call Worksheet.getCellRange().setText() method to write text to the specific cell of the new sheet.
- Save the result file using Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
public class AddWorksheet {
public static void main(String[] args) throws Exception {
//Specify input and output paths
String inputFile = "sample.xlsx";
String outputFile = "output/AddWorksheet.xlsx";
//Create a workbook and load a file
Workbook workbook = new Workbook();
//Load a sample Excel file
workbook.loadFromFile(inputFile);
//Add a new worksheet named “AddNewSheet”
Worksheet sheet = workbook.getWorksheets().add("AddNewSheet");
//Write text to the cell C5 of the new worksheet
sheet.getCellRange("C5").setText("This is a new sheet.");
//Save the Excel file
workbook.saveToFile(outputFile, ExcelVersion.Version2010);
workbook.dispose();
}
}
Remove a Worksheet from the Workbook in Java
Removing unnecessary worksheets from an Excel file can reduce the file size. Spire.XLS for Java provides Worksheet.remove() method to remove worksheets from an existing workbook. The following are the detailed steps.
- Specify input and output paths.
- Create a Workbook object.
- Load a sample Excel file using Workbook.loadFromFile() method.
- Get the second worksheet of this file using Workbook.getWorksheets().get() method.
- Remove it from the file using Worksheet.remove() method.
- Save the result file using Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
public class RemoveWorksheet {
public static void main(String[] args) throws Exception {
//Specify input and output paths
String inputFile = "sample.xlsx";
String outputFile = "output/RemoveWorksheet.xlsx";
//Create a workbook
Workbook workbook = new Workbook();
//Load a sample Excel file
workbook.loadFromFile(inputFile);
//Get the second worksheet and remove it
Worksheet sheet1 = workbook.getWorksheets().get(1);
sheet1.remove();
//Save the Excel file
workbook.saveToFile(outputFile, ExcelVersion.Version2010);
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.
Sometimes, you may find that a well-formatted and organized sheet looks messy on a printed page. This is because Excel worksheets are designed for comfortable viewing and editing on screen, not to fit on a sheet of paper. To get perfect hard copies of your Excel documents, you'll need to configure the print settings. In this article, you will learn how to set Excel print options and how to send Excel documents to a specified printer in Java using Spire.XLS for Java.
- Set Excel Print Options via Page Setup in Java
- Specify Printer Settings when Printing Excel Documents in 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>
Set Excel Print Options via Page Setup in Java
Excel Page Setup provides options to control how a worksheet will be printed, such as whether to print comments, whether to print gridlines and whether to fit worksheet on one page.
Spire.XLS offers the PageSetup object to deal with all these things. The following are the steps to set Excel print options through PageSetup using Spire.XLS for Java.
- Create a Workbook object.
- Load an Excel file using Workbook.loadFromFile() method.
- Get a specific worksheet using Workbook.getWorksheets().get() method.
- Get PageSetup object using Worksheet.getPageSetup() method.
- Set page margins, print area, pint title row, print quality, etc. using the methods under PageSetup object.
- Print the workbook by using PrinterJob class.
- Java
import com.spire.xls.*;
import java.awt.print.PageFormat;
import java.awt.print.Paper;
import java.awt.print.PrinterException;
import java.awt.print.PrinterJob;
public class SetPrintOptions {
public static void main(String[] args) {
//Create a workbook
Workbook workbook = new Workbook();
//Load an Excel document
workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");
//Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
//Get the PageSetup object of the first worksheet
PageSetup pageSetup = worksheet.getPageSetup();
//Set page margins
pageSetup.setTopMargin(0.3);
pageSetup.setBottomMargin(0.3);
pageSetup.setLeftMargin(0.3);
pageSetup.setRightMargin(0.3);
//Specify print area
pageSetup.setPrintArea("A1:D10");
//Specify title row
pageSetup.setPrintTitleRows("$1:$2");
//Allow to print with row/column headings
pageSetup.isPrintHeadings(true);
//Allow to print with gridlines
pageSetup.isPrintGridlines(true);
//Allow to print comments as displayed on worksheet
pageSetup.setPrintComments(PrintCommentType.InPlace);
//Set printing quality (dpi)
pageSetup.setPrintQuality(300);
//Allow to print worksheet in black & white mode
pageSetup.setBlackAndWhite(true);
//Set the printing order
pageSetup.setOrder(OrderType.OverThenDown);
//Fit worksheet on one page
pageSetup.isFitToPage(true);
//Create a PrinterJob object
PrinterJob printerJob = PrinterJob.getPrinterJob();
PageFormat pageFormat = printerJob.defaultPage();
Paper paper = pageFormat.getPaper();
//Set the imageable area of this paper
paper.setImageableArea(0, 0, pageFormat.getWidth(), pageFormat.getHeight());
//Set the number of copies
printerJob.setCopies(1);
pageFormat.setPaper(paper);
//Call painter to render the workbook in the specified format
printerJob.setPrintable(workbook, pageFormat);
//Execute print
try {
printerJob.print();
} catch (PrinterException e) {
e.printStackTrace();
}
}
}
Specify Printer Settings when Printing Excel Documents in Java
In addition to print options, it is important to know how to specify a network connected printer and how to specify other printer settings. The following steps demonstrate how to printer Excel documents with the specified printer settings by using Spire.XLS for Java and PrinterJob class.
- Create a Workbook object.
- Load an Excel file using Workbook.loadFromFile() method.
- Create a PrinterJob object using PrinterJob.getPrinterJob() method.
- Specify printer name using PrinterJob.setPrintService() method.
- Sets the number of copies to be printed using PrinterJob .setCopies() method.
- Calls painter to render the workbook using PrinterJob .setPrintable() method.
- Print the workbook using PrinterJob.print() method.
- Java
import com.spire.xls.Workbook;
import javax.print.PrintService;
import java.awt.print.PageFormat;
import java.awt.print.Paper;
import java.awt.print.PrinterException;
import java.awt.print.PrinterJob;
public class SpecifyPrinterSettings {
public static void main(String[] args) throws PrinterException {
//Create a Workbook object
Workbook workbook = new Workbook();
//Load an Excel document
workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\Sample.xlsx");
//Create a PrinterJob object
PrinterJob printerJob = PrinterJob.getPrinterJob();
//Specify printer name
PrintService myPrintService = findPrintService("\\\\192.168.1.104\\HP LaserJet P1007");
printerJob.setPrintService(myPrintService);
//Create a PageFormat object and set it to the default size and orientation
PageFormat pageFormat = printerJob.defaultPage();
//Return a copy of the Paper object associated with this PageFormat.
Paper paper = pageFormat .getPaper();
//Set the imageable area of this Paper
paper.setImageableArea(0,0,pageFormat .getWidth(),pageFormat .getHeight());
//Set the Paper object for this PageFormat
pageFormat .setPaper(paper);
//Set the number of copies
printerJob .setCopies(1);
//Call painter to render the workbook in the specified format
printerJob .setPrintable(workbook,pageFormat);
//Execute print
try {
printerJob.print();
} catch (PrinterException e) {
e.printStackTrace();
}
}
//Get print service by printer name
private static PrintService findPrintService(String printerName) {
PrintService[] printServices = PrinterJob.lookupPrintServices();
for (PrintService printService : printServices) {
if (printService.getName().equals(printerName)) {
return printService;
}
}
return null;
}
}
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.
Spire.XLS for Java is a professional Java Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets without using Microsoft Office or Microsoft Excel.
Spire.XLS for Java offers a wide range of features of operating Excel worksheets on Java applications, such as creating, reading, editing, converting and printing Excel worksheets, finding/replacing data, creating charts, inserting/extracting/deleting textboxes, creating auto filters, reading/writing hyperlinks, creating/modifying/removing tables, merging/unmerging cells and files, grouping/ungrouping rows and columns, freezing/unfreezing panes, adding digital signatures, encrypting/decrypting Excel workbooks etc.
