Java (486)
When creating reports in Word, we often encounter the situation where we need to copy and paste data from Excel to Word, so that readers can browse data directly in Word without opening Excel documents. In this article, you will learn how to convert Excel data into Word tables and preserve the formatting using Spire.Office for Java.
Install Spire.Office for Java
First, you're required to add the Spire.Office.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.office</artifactId>
<version>11.10.0</version>
</dependency>
</dependencies>
Export Excel Data to a Word Table with Formatting
The following are the steps to convert Excel data to a Word table maintaining formatting using Spire.Office for Java.
- Create a Workbook object and load a sample Excel file using Workbook.loadFromFile() method.
- Get a specific worksheet using Workbook.getWorksheets().get() method.
- Create a Document object, and add a section to it.
- Add a table using Section.addTable() method.
- Detect the merged cells in the worksheet and merge the corresponding cells of the Word tale using the custom method mergeCells().
- Get value of a specific Excel cell using CellRange.getValue() method and add it to a cell of the Word table using TableCell.addParagraph().appendText() method.
- Copy the font style and cell style from Excel to the Word table using the custom method copyStyle().
- Save the document to a Word file using Document.saveToFile() method.
- Java
import com.spire.doc.*;
import com.spire.doc.FileFormat;
import com.spire.doc.documents.HorizontalAlignment;
import com.spire.doc.documents.PageOrientation;
import com.spire.doc.documents.VerticalAlignment;
import com.spire.doc.fields.TextRange;
import com.spire.xls.*;
public class ExportExcelToWord {
public static void main(String[] args) {
//Load an Excel file
Workbook workbook = new Workbook();
workbook.loadFromFile("C:/Users/Administrator/Desktop/sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Create a Word document
Document doc = new Document();
Section section = doc.addSection();
section.getPageSetup().setOrientation(PageOrientation.Landscape);
//Add a table
Table table = section.addTable(true);
table.resetCells(sheet.getLastRow(), sheet.getLastColumn());
//Merge cells
mergeCells(sheet, table);
for (int r = 1; r <= sheet.getLastRow(); r++) {
//Set row Height
table.getRows().get(r - 1).setHeight((float) sheet.getRowHeight(r));
for (int c = 1; c <= sheet.getLastColumn(); c++) {
CellRange xCell = sheet.getCellRange(r, c);
TableCell wCell = table.get(r - 1, c - 1);
//Get value of a specific Excel cell and add it to a cell of Word table
TextRange textRange = wCell.addParagraph().appendText(xCell.getValue());
//Copy font and cell style from Excel to Word
copyStyle(textRange, xCell, wCell);
}
}
//Save the document to a Word file
doc.saveToFile("ExportToWord.docx", FileFormat.Docx);
}
//Merge cells if any
private static void mergeCells(Worksheet sheet, Table table) {
if (sheet.hasMergedCells()) {
//Get merged cell ranges from Excel
CellRange[] ranges = sheet.getMergedCells();
for (int i = 0; i < ranges.length; i++) {
int startRow = ranges[i].getRow();
int startColumn = ranges[i].getColumn();
int rowCount = ranges[i].getRowCount();
int columnCount = ranges[i].getColumnCount();
//Merge corresponding cells in Word table
if (rowCount > 1 && columnCount > 1) {
for (int j = startRow; j <= startRow + rowCount ; j++) {
table.applyHorizontalMerge(j - 1, startColumn - 1, startColumn - 1 + columnCount - 1);
}
table.applyVerticalMerge(startColumn - 1, startRow - 1, startRow - 1 + rowCount -1);
}
if (rowCount > 1 && columnCount == 1 ) {
table.applyVerticalMerge(startColumn - 1, startRow - 1, startRow - 1 + rowCount -1);
}
if (columnCount > 1 && rowCount == 1 ) {
table.applyHorizontalMerge(startRow - 1, startColumn - 1, startColumn - 1 + columnCount-1);
}
}
}
}
//Copy cell style of Excel to Word table
private static void copyStyle(TextRange wTextRange, CellRange xCell, TableCell wCell) {
//Copy font style
wTextRange.getCharacterFormat().setTextColor(xCell.getStyle().getFont().getColor());
wTextRange.getCharacterFormat().setFontSize((float) xCell.getStyle().getFont().getSize());
wTextRange.getCharacterFormat().setFontName(xCell.getStyle().getFont().getFontName());
wTextRange.getCharacterFormat().setBold(xCell.getStyle().getFont().isBold());
wTextRange.getCharacterFormat().setItalic(xCell.getStyle().getFont().isItalic());
//Copy backcolor
wCell.getCellFormat().setBackColor(xCell.getStyle().getColor());
//Copy horizontal alignment
switch (xCell.getHorizontalAlignment()) {
case Left:
wTextRange.getOwnerParagraph().getFormat().setHorizontalAlignment(HorizontalAlignment.Left);
break;
case Center:
wTextRange.getOwnerParagraph().getFormat().setHorizontalAlignment(HorizontalAlignment.Center);
break;
case Right:
wTextRange.getOwnerParagraph().getFormat().setHorizontalAlignment(HorizontalAlignment.Right);
break;
}
//Copy vertical alignment
switch (xCell.getVerticalAlignment()) {
case Bottom:
wCell.getCellFormat().setVerticalAlignment(VerticalAlignment.Bottom);
break;
case Center:
wCell.getCellFormat().setVerticalAlignment(VerticalAlignment.Middle);
break;
case Top:
wCell.getCellFormat().setVerticalAlignment(VerticalAlignment.Top);
break;
}
}
}
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.
ODS (OpenDocument Spreadsheet) is an XML-based file format created by the Calc program. Similar to MS Excel files, ODS files store data in cells organized into rows and columns, and can contain text, mathematical functions, formatting, and more. Sometimes, you may need to convert an Excel file to an ODS file to ensure that the file can be viewed by more applications in different operating systems. This article will demonstrate how to accomplish this task programmatically 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.10.1</version>
</dependency>
</dependencies>
Convert Excel to ODS
The detailed steps are as follows:
- Create a Workbook instance.
- Load a sample Excel file using Workbook.loadFromFile() method.
- Convert the Excel file to ODS using Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
public class toODS {
public static void main(String[] args) {
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load a sample Excel document
workbook.loadFromFile("C:\\Files\\sample.xlsx");
//Convert to ODS file
workbook.saveToFile("ExcelToODS.ods", FileFormat.ODS);
}
}
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.
When you open an XLS file in a newer version of Microsoft Excel, such as Excel 2016 or 2019, you'll see "Compatibility Mode" in the title bar after the file name. If you want to change from Compatibility Mode to Normal Mode, you can save the XLS file as a newer Excel file format like XLSX. In this article, you will learn how to convert XLS to XLSX or XLSX to XLS in Java 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.10.1</version>
</dependency>
</dependencies>
Convert XLS to XLSX in Java
The following are the steps to convert an XLS file to XLSX format using Spire.XLS for Java:
- Create a Workbook instance.
- Load the XLS file using Workbook.loadFromFile() method.
- Save the XLS file to XLSX format using Workbook.saveToFile(String, ExcelVersion) method.
- Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
public class ConvertXlsToXlsx {
public static void main(String[] args){
//Initialize an instance of Workbook class
Workbook workbook = new Workbook();
//Load the XLS file
workbook.loadFromFile("Input.xls");
//Save the XLS file to XLSX format
workbook.saveToFile("ToXlsx.xlsx", ExcelVersion.Version2016);
}
}
Convert XLSX to XLS in Java
The following are the steps to convert an XLSX file to XLS format using Spire.XLS for Java:
- Create a Workbook instance.
- Load the XLSX file using Workbook.loadFromFile() method.
- Save the XLSX file to XLS format using Workbook.saveToFile(String, ExcelVersion) method.
- Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
public class ConvertXlsxToXls {
public static void main(String[] args){
//Initialize an instance of Workbook class
Workbook workbook = new Workbook();
//Load the XLSX file
workbook.loadFromFile("Input.xlsx");
//Save the XLSX file to XLS format
workbook.saveToFile("ToXls.xls", ExcelVersion.Version97to2003);
}
}
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.
