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>10.6.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;
        }
    }
}

Java: Export Excel Data to Word Tables with Formatting

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.

Java: Convert Excel to ODS

2022-09-13 01:04:56 Written by Koohji

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>15.6.3</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);
    }
}

Java: Convert Excel to 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.

Java: Convert XLS to XLSX and Vice versa

2022-08-25 03:55:37 Written by Koohji

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>15.6.3</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);
    }
}

Java: Convert XLS to XLSX and Vice versa

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);
    }
}

Java: Convert XLS to XLSX and Vice versa

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.

Java: Change the Column Order in Excel

2022-08-19 05:46:03 Written by Koohji

When you are dealing with the data in a worksheet, you may need to rearrange the columns so as to make it easier to find and read the specific data. It is easy to move columns in MS Excel by using Shift and Drag. This article introduces how to programmatically reorder columns 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.6.3</version>
    </dependency>
</dependencies>

Rearrange Columns in Excel in Java

The following are the steps to reorder columns in Excel using Spire.XLS for Java.

  • Create a Workbook object, and load a sample Excel file using Workbook.loadFromFile() method.
  • Get the target worksheet where you’d like to adjust the order using Workbook.getWorksheets().get() method.
  • Specify the new column order in an int array.
  • Create a temporary sheet and copy the data from the target sheet into it.
  • Copy the columns from the temporary sheet to the target sheet and store them in the new order.
  • Remove the temporary sheet.
  • Save the workbook to another Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class RearrangeColumns {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load an Excel file
        workbook.loadFromFile( "C:\\Users\\Jack\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //Set the new column order (the column index starts from 0)
        int[] newColumnOrder = new int[]{3, 0, 1, 2, 4, 5};

        //Add a temporary worksheet
        Worksheet newSheet = workbook.getWorksheets().add("temp");

        //Copy data from the first worksheet to the temporary sheet
        newSheet.copyFrom(worksheet);

        //Loop through the newColumnOrder array
        for (int i = 0; i < newColumnOrder.length; i++) {

            //Copy the column from the temporary sheet to the first sheet
            newSheet.getColumns()[newColumnOrder[i]].copy(worksheet.getColumns()[i],true,true);

            //Set the width of a certain column the first sheet to that of the temporary sheet
            worksheet.getColumns()[i].setColumnWidth(newSheet.getColumns()[newColumnOrder[i]].getColumnWidth());
        }

        //Remove temporary sheet
        workbook.getWorksheets().remove(newSheet);

        //Save the workbook to another Excel file
        workbook.saveToFile("output/MoveColumn.xlsx", FileFormat.Version2016);
    }
}

Java: Change the Column Order in Excel

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.

Java: Write Data to Excel Worksheets

2022-08-12 08:09:03 Written by Koohji

Excel has the ability to store and organize large amounts of data into orderly spreadsheets quickly. But it is time-consuming if we manually enter data into cells one after another. Automating the creation of Excel files by programming can save us a lot of time and energy. This article introduces how to write data into Excel worksheets in Java by using Spire.XLS for Java.

Install Spire.XLS for Java

First, 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.6.3</version>
    </dependency>
</dependencies>

Write Text or Number Values to Specific Cells

A certain cell in a worksheet can be accessed by Worksheet.get(int row, int column) method. Then, you can add a text value or a number value to the cell using the IXLSRange.setValue() or IXLSRange.setNumberValue() method. The following are the detailed steps.

  • Create a Workbook object.
  • Get the first worksheet using Workbook.getWorksheets().get() method.
  • Get a specific cell using Workhseet.get() method.
  • Add a text value or a number value to the cell using IXLSRange.setValue() or IXLSRange.setNumberValue() method.
  • Save the workbook to an Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

public class WriteToCells {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Get the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //Write data to specific cells
        worksheet.get(1,1).setValue("Name");
        worksheet.get(1,2).setValue("Age");
        worksheet.get(1,3).setValue("Department");
        worksheet.get(1,4).setValue("Hiredate");
        worksheet.get(2,1).setValue("Hazel");
        worksheet.get(2,2).setNumberValue(29);
        worksheet.get(2,3).setValue("Marketing");
        worksheet.get(2,4).setValue("2019-07-01");
        worksheet.get(3,1).setValue("Tina");
        worksheet.get(3,2).setNumberValue(31);
        worksheet.get(3,3).setValue("Technical Support");
        worksheet.get(3,4).setValue("2015-04-27");

        //Auto fit column widths
        worksheet.getAllocatedRange().autoFitColumns();

        //Apply a style to the first row
        CellStyle style = workbook.getStyles().addStyle("newStyle");
        style.getFont().isBold(true);
        worksheet.getRange().get(1,1,1,4).setStyle(style);

        //Save to an Excel file
        workbook.saveToFile("output/WriteToCells.xlsx", ExcelVersion.Version2016);
    }
}

Java: Write Data to Excel Worksheets

Write Arrays to a Worksheet

Spire.XLS for Java provides the Worksheet.insertArrary() method, allowing programmers to write one-dimensional array or two-dimensional array into the specified cell range of a worksheet. The steps to write arrays to a worksheet are as follows:

  • Create a Workbook object.
  • Get the first worksheet using Workbook.getWorksheets().get() method.
  • Create a one-dimensional array and a two-dimensional array.
  • Insert the arrays to worksheet using Worksheet.insertArray() method.
  • Save the workbook to an Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

public class WriteArrayToWorksheet {

    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Get the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //Create a one-dimensional array
        String[] oneDimensionalArray = new String[]{"January", "February", "March", "April","May", "June"};

        //Write the array to the first row of the worksheet
        worksheet.insertArray(oneDimensionalArray, 1, 1, false);

        //Create a two-dimensional array
        String[][] twoDimensionalArray = new String[][]{
                {"Name", "Age", "Sex", "Dept.", "Tel."},
                {"John", "25", "Male", "Development","654214"},
                {"Albert", "24", "Male", "Support","624847"},
                {"Amy", "26", "Female", "Sales","624758"}
        };

        //Write the array to the worksheet starting from the cell A3
        worksheet.insertArray(twoDimensionalArray, 3, 1);

        //Auto fit column width in the located range
        worksheet.getAllocatedRange().autoFitColumns();

        //Apply a style to the first and the third row
        CellStyle style = workbook.getStyles().addStyle("newStyle");
        style.getFont().isBold(true);
        worksheet.getRange().get(1,1,1,6).setStyle(style);
        worksheet.getRange().get(3,1,3,6).setStyle(style);

        //Save to an Excel file
        workbook.saveToFile("output/WriteArrays.xlsx", ExcelVersion.Version2016);
    }
}

Java: Write Data to Excel Worksheets

Write a DataTable to a Worksheet

To import data from a DataTable to a worksheet, use the Worksheet.insertDataTable() method. The following are the detailed steps.

  • Create a Workbook object.
  • Get the first worksheet using Workbook.getWorksheets().get() method.
  • Create a DataTable, and write the DataTable to the worksheet at the specified location using Worksheet.insertDataTable() method.
  • Save the workbook to an Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.data.table.DataRow;
import com.spire.data.table.DataTable;
import com.spire.xls.*;

public class WriteDataTableToWorksheet {

    public static void main(String[] args) throws Exception {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Get the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //Create a DataTable object
        DataTable dataTable = new DataTable();
        dataTable.getColumns().add("SKU", Integer.class);
        dataTable.getColumns().add("NAME", String.class);
        dataTable.getColumns().add("PRICE", String.class);

        //Create rows and add data
        DataRow dr = dataTable.newRow();
        dr.setInt(0, 512900512);
        dr.setString(1,"Wireless Mouse M200");
        dr.setString(2,"$85");
        dataTable.getRows().add(dr);
        dr = dataTable.newRow();
        dr.setInt(0,512900637);
        dr.setString(1,"B100 Cored Mouse ");
        dr.setString(2,"$99");
        dataTable.getRows().add(dr);
        dr = dataTable.newRow();
        dr.setInt(0,512901829);
        dr.setString(1,"Gaming Mouse");
        dr.setString(2,"$125");
        dataTable.getRows().add(dr);
        dr = dataTable.newRow();
        dr.setInt(0,512900386);
        dr.setString(1,"ZM Optical Mouse");
        dr.setString(2,"$89");
        dataTable.getRows().add(dr);

        //Write datatable to the worksheet
        worksheet.insertDataTable(dataTable,true,1,1,true);

        //Auto fit column width in the located range
        worksheet.getAllocatedRange().autoFitColumns();

        //Apply a style to the first row
        CellStyle style = workbook.getStyles().addStyle("newStyle");
        style.getFont().isBold(true);
        worksheet.getRange().get(1,1,1,3).setStyle(style);

        //Save to an Excel file
        workbook.saveToFile("output/WriteDataTable.xlsx", ExcelVersion.Version2016);
    }
}

Java: Write Data to Excel Worksheets

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.

The Excel print options (also known as sheet options) allow you to control the print options when printing Excel documents. Spire.XLS for Java offers the PageSetup class to set the print options, such as print area, print titles and print order. This article will demonstrate how to set different printing settings using Spire.XLS for Java from the following aspects:

  • Set the print area in Excel
  • Print titles in Excel
  • Print gridlines in Excel
  • Print comments in Excel
  • Print Excel in black and white mode
  • Set print quality
  • Set the print order of worksheet pages

Install Spire.XLS for Java

First, 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.6.3</version>
    </dependency>
</dependencies>

Setting Excel Print Options via Page Setup

The detailed steps of controlling the Excel printing settings are as follows.

  • Create a Workbook object.
  • Load a sample Excel document using Workbook.loadFromFile() method.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Get the PageSetup object of the first worksheet.
  • Select a specific print area of a worksheet using PageSetup.setPrintArea() method.
  • Set the rows to repeat at top when printing using PageSetup.setPrintTitleRows() method.
  • Set printing with gridlines using PageSetup.isPrintGridlines(true) method.
  • Set printing with comments using PageSetup.setPrintComments() method.
  • Print worksheet in black & white mode using PageSetup.setBlackAndWhite(true) method.
  • Set the printing quality using PageSetup.setPrintQuality() method.
  • Set the printing order using PageSetup.setOrder() method.
  • Save the document to another file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

public class pageSetupForPrinting {

    public static void main(String[] args) throws Exception {

        //Create a workbook
        Workbook workbook = new Workbook();

        //Load the Excel document from disk
        workbook.loadFromFile("Sample.xlsx");

        //Get the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //Get the PageSetup object of the first worksheet
        PageSetup pageSetup = worksheet.getPageSetup();

        //Specifying the print area
        pageSetup.setPrintArea("A1:D10");

        //Define row numbers 1 as title rows
        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
        pageSetup.setPrintQuality(150);
        
        //Allow to print worksheet in black & white mode
        pageSetup.setBlackAndWhite(true);
        //Set the printing order
        pageSetup.setOrder(OrderType.OverThenDown);

        //Save the document to file
        workbook.saveToFile("PagePrintOptions.xlsx", ExcelVersion.Version2016);
    }
}

Java: Set Excel Print Options Through Page Setup

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.

Java: Convert CSV to PDF

2022-07-07 08:07:44 Written by Koohji

A CSV file is essentially a plain text file. It can be easily edited by almost any program that can handle text files, such as Notepad. Converting a CSV file to PDF can help in preventing it from being edited by viewers. In this article, you will learn how to convert CSV to PDF 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>15.6.3</version>
    </dependency>
</dependencies>

Convert CSV to PDF in Java

The following are the steps to convert a CSV file to PDF:

  • Create an instance of Workbook class.
  • Load the CSV file using Workbook.loadFromFile(filePath, separator) method.
  • Set the worksheet to be rendered to one PDF page using Workbook.getConverterSetting().setSheetFitToPage(true) method.
  • Get the first worksheet in the Workbook using Workbook.getWorksheets().get(0) method.
  • Loop through the columns in the worksheet and auto-fit the width of each column using Worksheet.autoFitColumn() method.
  • Save the worksheet to PDF using Worksheet.saveToPdf() method.
  • Java
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class ConvertCsvToPdf {
    public static void main(String []args) {
        //Create a Workbook instance
        Workbook wb = new Workbook();
        //Load a CSV file
        wb.loadFromFile("Sample.csv", ",");

        //Set SheetFitToPage property as true to ensure the worksheet is converted to 1 PDF page
        wb.getConverterSetting().setSheetFitToPage(true);

        //Get the first worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Loop through the columns in the worksheet
        for (int i = 1; i < sheet.getColumns().length; i++)
        {
            //AutoFit columns
            sheet.autoFitColumn(i);
        }

        //Save the worksheet to PDF
        sheet.saveToPdf("toPDF.pdf");
    }
}

Java: Convert CSV to PDF

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.

Java: Create a Line Chart in Excel

2022-06-01 05:36:41 Written by Koohji

Line chart is a fundamental chart type used to display trends or changes in data over a specific time interval. A line chart uses lines to connect data points, it can include a single line for one data set or multiple lines for two or more data sets. This article will demonstrate how to create a line chart in Excel 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>15.6.3</version>
    </dependency>
</dependencies>

Create a Line Chart in Excel using Java

The following are the main steps to create a line chart:

  • Create an instance of Workbook class.
  • Get the first worksheet by its index (zero-based) though Workbook.getWorksheets().get(sheetIndex) method.
  • Add some data to the worksheet.
  • Add a line chart to the worksheet using Worksheet.getCharts().add(ExcelChartType.Line) method.
  • Set data range for the chart through Chart.setDataRange() method.
  • Set position, title, category axis title and value axis title for the chart.
  • Loop through the data series of the chart, show data labels for the data points of each data series using ChartSerie.getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true) method.
  • Set the position of chart legend through Chart.getLegend().setPosition() method.
  • Save the result file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;
import com.spire.xls.charts.ChartSerie;

import java.awt.*;

public class CreateLineChart {
    public static void main(String []args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //Set sheet name
        sheet.setName("Line Chart");;
        //Hide gridlines
        sheet.setGridLinesVisible(false);

        //Add some data to the the worksheet
        sheet.getRange().get("A1").setValue("Country");
        sheet.getRange().get("A2").setValue("Cuba");
        sheet.getRange().get("A3").setValue("Mexico");
        sheet.getRange().get("A4").setValue("France");
        sheet.getRange().get("A5").setValue("German");

        sheet.getRange().get("B1").setValue("Jun");
        sheet.getRange().get("B2").setNumberValue(3300);
        sheet.getRange().get("B3").setNumberValue(2300);
        sheet.getRange().get("B4").setNumberValue(4500);
        sheet.getRange().get("B5").setNumberValue(6700);

        sheet.getRange().get("C1").setValue("Jul");
        sheet.getRange().get("C2").setNumberValue(7500);
        sheet.getRange().get("C3").setNumberValue(2900);
        sheet.getRange().get("C4").setNumberValue(2300);
        sheet.getRange().get("C5").setNumberValue(4200);

        sheet.getRange().get("D1").setValue("Aug");
        sheet.getRange().get("D2").setNumberValue(7700);
        sheet.getRange().get("D3").setNumberValue(6900);
        sheet.getRange().get("D4").setNumberValue(8400);
        sheet.getRange().get("D5").setNumberValue(4200);

        sheet.getRange().get("E1").setValue("Sep");
        sheet.getRange().get("E2").setNumberValue(8000);
        sheet.getRange().get("E3").setNumberValue(7200);
        sheet.getRange().get("E4").setNumberValue(8300);
        sheet.getRange().get("E5").setNumberValue(5600);

        //Set font and fill color for specified cells
        sheet.getRange().get("A1:E1").getStyle().getFont().isBold(true);
        sheet.getRange().get("A2:E2").getStyle().setKnownColor(ExcelColors.LightYellow);;
        sheet.getRange().get("A3:E3").getStyle().setKnownColor(ExcelColors.LightGreen1);
        sheet.getRange().get("A4:E4").getStyle().setKnownColor(ExcelColors.LightOrange);
        sheet.getRange().get("A5:E5").getStyle().setKnownColor(ExcelColors.LightTurquoise);

        //Set cell borders
        sheet.getRange().get("A1:E5").getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeTop).setColor(new Color(0, 0, 128));
        sheet.getRange().get("A1:E5").getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeTop).setLineStyle(LineStyleType.Thin);
        sheet.getRange().get("A1:E5").getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setColor(new Color(0, 0, 128));
        sheet.getRange().get("A1:E5").getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Thin);
        sheet.getRange().get("A1:E5").getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeLeft).setColor(new Color(0, 0, 128));
        sheet.getRange().get("A1:E5").getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeLeft).setLineStyle(LineStyleType.Thin);
        sheet.getRange().get("A1:E5").getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeRight).setColor(new Color(0, 0, 128));
        sheet.getRange().get("A1:E5").getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeRight).setLineStyle(LineStyleType.Thin);

        //Set number format
        sheet.getRange().get("B2:D5").getStyle().setNumberFormat("\"$\"#,##0");

        //Add a line chart to the worksheet
        Chart chart = sheet.getCharts().add(ExcelChartType.Line);

        //Set data range for the chart
        chart.setDataRange(sheet.getRange().get("A1:E5"));

        //Set position of the chart
        chart.setLeftColumn(1);
        chart.setTopRow(6);
        chart.setRightColumn(11);
        chart.setBottomRow(29);

        //Set and format chart title
        chart.setChartTitle("Sales Report");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        //Set and format category axis title
        chart.getPrimaryCategoryAxis().setTitle("Month");
        chart.getPrimaryCategoryAxis().getFont().isBold(true);
        chart.getPrimaryCategoryAxis().getTitleArea().isBold(true);

        //Set and format value axis title
        chart.getPrimaryValueAxis().setTitle("Sales (in USD)");
        chart.getPrimaryValueAxis().hasMajorGridLines(false);
        chart.getPrimaryValueAxis().getTitleArea().setTextRotationAngle(-90);
        chart.getPrimaryValueAxis().setMinValue(1000);
        chart.getPrimaryValueAxis().getTitleArea().isBold(true);

        //Loop through the data series of the chart
        for(ChartSerie cs : (Iterable) chart.getSeries())
        {
            cs.getFormat().getOptions().isVaryColor(true);
            //Show data labels for data points
            cs.getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        }

        //Set position of chart legend
        chart.getLegend().setPosition(LegendPositionType.Top);

        //Save the result file
        workbook.saveToFile("LineChart.xlsx", ExcelVersion.Version2016);
    }
}

Java: Create a Line Chart in Excel

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.

AutoFit is a very practical feature MS Excel offers to automatically resize cells to accommodate different sized data. With a single click, it makes all the data in a cell clearly visible without having to manually adjust the column width or row height. In this article, you will learn how to programmatically AutoFit the column width and row height in an Excel worksheet using Spire.XLS for Java.

Install Spire.XLS for Java

First, 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.6.3</version>
    </dependency>
</dependencies>

AutoFit Column Width and Row Height in Excel

The detailed steps are as follows.

  • Create a Workbook object.
  • Load a sample Excel document using Workbook.loadFromFile() method.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Get the used range on the specified worksheet using Worksheet.getAllocatedRange() method.
  • Autofit column width and row height in the range using CellRange.autoFitColumns() and CellRange.autoFitRows() methods.
  • Save the document to another file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

public class AutoFitColumn {
    public static void main(String[] args) {
        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load a sample Excel document
        workbook.loadFromFile("E:\\Files\\Test.xlsx");

        //Get the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //AutoFit column width and row height
        worksheet.getAllocatedRange().autoFitColumns();
        worksheet.getAllocatedRange().autoFitRows();

        //Save the document
        workbook.saveToFile("AutoFitCell.xlsx", ExcelVersion.Version2013);
    }
}

Java: AutoFit Column Width and Row Height in Excel

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 working with an Excel document that contains a lot of data, setting color or pattern for selected cells can make it very easy for users to locate specific types of information. In Microsoft Excel, you can achieve this function by simply clicking the "Fill Color" button on the formatting toolbar. In this article, you will learn how to programmatically set background color and pattern style for a specified cell or cell range in Excel using Spire.XLS for Java.

Install Spire.XLS for Java

First, 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.6.3</version>
    </dependency>
</dependencies>

Set Background Color and Pattern for Excel Cells

The detailed steps are as follows.

  • Create a Workbook object.
  • Load a sample Excel document using Workbook.loadFromFile() method.
  • Get a specified worksheet using Workbook.getWorksheets().get() method.
  • Get a specified cell range using Worksheet.getRange().get() method.
  • Set background color for the specified cell range using CellRange.getStyle().setColor() method.
  • Set fill pattern style for the specified cell range using CellRange.getStyle().setFillPattern() method.
  • Save the result to another file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;
import java.awt.*;

public class CellBackground {
    public static void main(String[] args) {
        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx");

        //Get the first worksheet
        Worksheet worksheet= workbook.getWorksheets().get(0);

        //Set background color for range "A1:E1" and "A2:A10"
        worksheet.getRange().get("A1:E1").getStyle().setColor(Color.green);
        worksheet.getRange().get("A2:A10").getStyle().setColor(Color.yellow);

        //Set background color for cell E8
        worksheet.getRange().get("E8").getStyle().setColor(Color.red);

        //Set fill pattern style for range "C4:D5"
        worksheet.getRange().get("C4:D5").getStyle().setFillPattern(ExcelPatternType.Percent25Gray);

        //Save the document
        workbook.saveToFile("CellBackground.xlsx", ExcelVersion.Version2013);
    }
}

Java: Set Background Color and Pattern for Excel Cells

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.

Page 2 of 10
page 2