Knowledgebase (2345)
Children categories
Copying worksheet involves duplicating an existing worksheet within the same workbook or across different workbooks. This valuable feature enables developers to create an exact replica of the original worksheet effortlessly, including its structure, formatting, data, formulas, charts, and other objects without any mistake. It proves especially beneficial when dealing with extensive data files, as it significantly reduces time and effort required for backing up files and creating templates. In this article, we will introduce how to copy worksheets in Excel using Spire.XLS for Java. With this method, all the cell formats in the original Excel worksheets will be completely remained.
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.6.5</version>
</dependency>
</dependencies>
Copy Worksheets between Workbooks
Spire.XLS for Java library allows you copy worksheets from one workbook to another file easily by using Worksheet.copyFrom() method. The following are detailed steps.
- Create a new Workbook object.
- Load the source Excel file from disk using Workbook.loadFromFile() method.
- Get the first worksheet of the source file by using Workbook.getWorksheets().get() method.
- Create an another Workbook object.
- Load the target file from disk using Workbook.loadFromFile() method.
- Add a new sheet to the target file using Workbook.getWorksheets().add() method.
- Copy the first worksheet of the source file to the new added sheet of the target file through Worksheet.copyFrom() method.
- Finally, specify the output path and save the target file using Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
public class copyWorksheet {
public static void main(String[] args) {
//Create a Workbook
Workbook sourceWorkbook = new Workbook();
//Load the source Excel file from disk
sourceWorkbook.loadFromFile("sample1.xlsx");
//Get the first worksheet
Worksheet srcWorksheet = sourceWorkbook.getWorksheets().get(0);
//Create a another Workbook
Workbook targetWorkbook = new Workbook();
//Load the target Excel file from disk
targetWorkbook.loadFromFile("sample2.xlsx");
//Add a new worksheet
Worksheet targetWorksheet = targetWorkbook.getWorksheets().add("added");
//Copy the first worksheet of sample1 to the new added sheet of sample2
targetWorksheet.copyFrom(srcWorksheet);
//String for output file
String outputFile = "output/CopyWorksheet.xlsx";
//Save the result file
targetWorkbook.saveToFile(outputFile, ExcelVersion.Version2013);
sourceWorkbook.dispose();
targetWorkbook.dispose();
}
}
Copy Worksheets within Workbooks
You can also copy a worksheet within the same workbook by adding a new worksheet to this workbook and then copying the desired sheet to the new one. The following are the steps to duplicate worksheets within an Excel workbook.
- Create a new Workbook object.
- Load the source Excel file from disk using Workbook.loadFromFile() method.
- Get the first worksheet by using Workbook.getWorksheets().get() method and add a new sheet called "MySheet" using Workbook.getWorksheets().add() method.
- Copy the first worksheet to the second one through Worksheet.copyFrom() method;
- Finally, specify the output path and save the result file using Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
public class copySheetWithinWorkbook {
public static void main(String[] args) {
//Create a Workbook
Workbook workbook = new Workbook();
//Load the sample file from disk
workbook.loadFromFile("sample1.xlsx");
//Get the first sheet and add a new worksheet to this file
Worksheet sheet = workbook.getWorksheets().get(0);
Worksheet sheet1 = workbook.getWorksheets().add("MySheet");
//Copy the first worksheet to the second one
sheet1.copyFrom(sheet);
//String for output file
String result = "output/CopySheetWithinWorkbook.xlsx";
//Save to file
workbook.saveToFile(result, 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.
Published in
Worksheet
Tagged under
This article demonstrates how to repeat table header rows across pages in PDF using Spire.PDF for Java.
import com.spire.pdf.*;
import com.spire.pdf.graphics.*;
import com.spire.pdf.grid.PdfGrid;
import com.spire.pdf.grid.PdfGridRow;
import java.awt.*;
public class RepeatTableHeaderRow {
public static void main(String[] args) {
//Create a new PDF document
PdfDocument pdf = new PdfDocument();
//Add a page
PdfPageBase page = pdf.getPages().add();
//Instantiate a PdfGrid class object
PdfGrid grid = new PdfGrid();
//Set cell padding
grid.getStyle().setCellPadding(new PdfPaddings(1,1,1,1));
//Add columns
grid.getColumns().add(3);
//Add header rows and table data
PdfGridRow[] pdfGridRows = grid.getHeaders().add(1);
for (int i = 0; i < pdfGridRows.length; i++)
{
pdfGridRows[i].getStyle().setFont(new PdfTrueTypeFont(new Font("Arial", Font.PLAIN,12), true));//Designate a font
pdfGridRows[i].getCells().get(0).setValue("NAME");
pdfGridRows[i].getCells().get(1).setValue("SUBJECT");
pdfGridRows[i].getCells().get(2).setValue("SCORES");
pdfGridRows[i].getStyle().setTextBrush(PdfBrushes.getRed());
}
//Repeat header rows (when across pages)
grid.setRepeatHeader(true);
//Add values to the table
for (int i = 0; i < 60; i++)
{
PdfGridRow row = grid.getRows().add();
for (int j = 0; j < grid.getColumns().getCount();j++)
{
row.getCells().get(j).setValue("(Row " + (i+1) + ", column " + (j+1) + ")");
}
}
// Draw a table in PDF
grid.draw(page,0,40);
//Save the document
pdf.saveToFile("Result.pdf");
pdf.dispose();
}
}
Output
Published in
Document Operation
Tagged under
This article will demonstrate how to repeat the table’s header row in C#/VB.NET by using Spire.PDF for .NET.
C#
using Spire.Pdf;
using Spire.Pdf.Graphics;
using Spire.Pdf.Grid;
using System.Drawing;
namespace PDFGrid
{
class Program
{
static void Main(string[] args)
{
//Create a pdf document
PdfDocument doc = new PdfDocument();
//Add a page to pdf
PdfPageBase page = doc.Pages.Add();
//Create a PdfGrid object
PdfGrid grid = new PdfGrid();
//Set the cell padding of the grid
grid.Style.CellPadding = new PdfPaddings(1, 1, 1, 1);
//Set the Columns of the grid
grid.Columns.Add(3);
//Set the header rows and define the data
PdfGridRow[] pdfGridRows = grid.Headers.Add(2);
for (int i = 0; i < pdfGridRows.Length; i++)
{
pdfGridRows[i].Style.Font = new PdfTrueTypeFont(new Font("Arial", 11f, FontStyle.Regular), true);
pdfGridRows[i].Cells[0].Value = "Vendor Name";
pdfGridRows[i].Cells[1].Value = "Address";
pdfGridRows[i].Cells[2].Value = "City";
}
//Repeat the table header rows if the grid exceed one page
grid.RepeatHeader = true;
for (int i = 0; i < 60; i++)
{
PdfGridRow row = grid.Rows.Add();
//Add the data to the table
for (int j = 0; j < grid.Columns.Count; j++)
{
row.Cells[j].Value = "(Row " + i + ", column " + j + ")";
}
}
//draw grid on the pdf page
PdfLayoutResult pdfLayoutResult = grid.Draw(page, new PointF(0, 20));
float y = pdfLayoutResult.Bounds.Y + pdfLayoutResult.Bounds.Height;
PdfPageBase currentPage = pdfLayoutResult.Page;
//Save the doucment to file
doc.SaveToFile("PDFGrid.pdf");
}
}
}
VB.NET
Imports Spire.Pdf
Imports Spire.Pdf.Graphics
Imports Spire.Pdf.Grid
Imports System.Drawing
Namespace PDFGrid
Class Program
Private Shared Sub Main(ByVal args() As String)
'Create a pdf document
Dim doc As PdfDocument = New PdfDocument
'Add a page to pdf
Dim page As PdfPageBase = doc.Pages.Add
'Create a PdfGrid object
Dim grid As PdfGrid = New PdfGrid
'Set the cell padding of the grid
grid.Style.CellPadding = New PdfPaddings(1, 1, 1, 1)
'Set the Columns of the grid
grid.Columns.Add(3)
'Set the header rows and define the data
Dim pdfGridRows() As PdfGridRow = grid.Headers.Add(2)
Dim i As Integer = 0
Do While (i < pdfGridRows.Length)
pdfGridRows(i).Style.Font = New PdfTrueTypeFont(New Font("Arial", 11!, FontStyle.Regular), true)
pdfGridRows(i).Cells(0).Value = "Vendor Name"
pdfGridRows(i).Cells(1).Value = "Address"
pdfGridRows(i).Cells(2).Value = "City"
i = (i + 1)
Loop
'Repeat the table header rows if the grid exceed one page
grid.RepeatHeader = true
Dim i As Integer = 0
Do While (i < 60)
Dim row As PdfGridRow = grid.Rows.Add
'Add the data to the table
Dim j As Integer = 0
Do While (j < grid.Columns.Count)
row.Cells(j).Value = ("(Row " _
+ (i + (", column " _
+ (j + ")"))))
j = (j + 1)
Loop
i = (i + 1)
Loop
'draw grid on the pdf page
Dim pdfLayoutResult As PdfLayoutResult = grid.Draw(page, New PointF(0, 20))
Dim y As Single = (pdfLayoutResult.Bounds.Y + pdfLayoutResult.Bounds.Height)
Dim currentPage As PdfPageBase = pdfLayoutResult.Page
'Save the doucment to file
doc.SaveToFile("PDFGrid.pdf")
End Sub
End Class
End Namespace
Effective screenshot of repeating the table's header row:
Published in
Table
Tagged under
