News Category

Data Import/Export

Data Import/Export (2)

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>14.3.2</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.

This article demonstrates how to insert arrays, including one-dimensional and two-dimensional arrays, into Excel cells using Spire.XLS for Java.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class InsertArray {

    public static void main(String[] args) {

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

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

        //Define a one-dimensional array 
        String[] oneDimensionalArray = new String[]{"Apple", "Pear", "Grape", "Banana"};

        // Write the array to the worksheet from the specified cell (true means vertically insert)
        sheet.insertArray(oneDimensionalArray, 1, 1, true);

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

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

        //Save the file 
        wb.saveToFile("InsertArrays.xlsx", ExcelVersion.Version2016);
    }
}

Insert Arrays into Excel in Java