首页 > itarticle > java read and write microsoft excel

java read and write microsoft excel

admin 11月 13, 2020 0

Java Read And Write Microsoft Excel

In Java, Apache POI can be used to read and export Excel, the document of Apache POI
seems not straightforward at all, and here’s detailed example.

Maven dependency

Add poi dependency in pom.xml

    <dependencies>
        // for xls(Excel 2003-2007)
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        // for xlsx(Excel 2007 and after)
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>
    </dependencies>

Excel support

Apache POI supports both xlsx(2007-now) and xls(Excel 97-2007) format.
To work with xlsx, use XSSFWorkbook, XSSFSheet, XSSFRow, XSSFcell etc.
And to work with xls, use HSSFWorkbook, HSSFSheet, HSSFRow, HSSFRow etc.

Read Excel with Apache POI Example

Suppose we have an excel like this:

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

public class ExcelRead {
    public static void main(String[] args) throws IOException {
        String filename = "/path/to/you/excel/filename.xlsx";
        FileInputStream inputStream = new FileInputStream(new File(filename));

        // create workbook object from existing excel file
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

        // get sheet by index
        XSSFSheet sheet = workbook.getSheetAt(0);

        // iterate rows
        for (Row currentRow : sheet) {
            // iterate cells in a row
            for (Cell currentCell : currentRow) {
                System.out.print(currentCell.getStringCellValue() + "     ");
            }
            System.out.println("");
        }
    }
}

The output of the code above should be like this:

A1     B1     C1
A2     B2     C2
A3     B3     C3
A4     B4     C4
A5     B5     C5
A6     B6     C6

Export Excel with Apache POI Example

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;


public class ExcelExport {
    public static void main(String[] args) throws IOException {

        String filename = "/Users/xuhangyi/Desktop/wtf.xlsx";

        // create excel workbook object
        XSSFWorkbook workbook = new XSSFWorkbook();
        // add a sheet to the workbook
        XSSFSheet sheet = workbook.createSheet("newSheet");

        String[][] data = {{"Head1", "Head2", "head3"},
        {"Data1", "Data2", "Data3"}, {"Data4", "Data5", "Data6"}};

        int rowCount = 0;

        for (String[] dataRow : data) {
            // create row for sheet
            Row row = sheet.createRow(rowCount++);
            int columnCount = 0;
            for (String str : dataRow) {

                // create cell in row
                Cell cell = row.createCell(columnCount++);
                cell.setCellValue(str);
            }
        }
        FileOutputStream outputStream = new FileOutputStream(filename);
        workbook.write(outputStream);
        workbook.close();
    }
}

Download Excel Example in Spring MVC

@Controller
public class TradeController {
    @RequestMapping("/export", method=RequestMethod.GET)
    public Object exportController(HttpServletResponse response) throws IOEXception{
                // create the excel
                XSSFWorkbook workbook = new XSSFWorkbook();
                // add a sheet to the workbook
                XSSFSheet sheet = workbook.createSheet("newSheet");
                String[][] data = {{"Head1", "Head2", "head3"},
                {"Data1", "Data2", "Data3"}, {"Data4", "Data5", "Data6"}};
                int rowCount = 0;
                for (String[] dataRow : data) {
                    // create row for sheet
                    Row row = sheet.createRow(rowCount++);
                    int columnCount = 0;
                    for (String str : dataRow) {

                        // create cell in row
                        Cell cell = row.createCell(columnCount++);
                        cell.setCellValue(str);
                    }
                }
                // filename of the downloaded file
                String filename="filename.xlsx";
                OutputStream outputStream = response.getOutputStream()
                outputStream.setHeader("Content-Disposition", "attachment; filename="+filename);
                outputStream.setContentType("application/octet-stream;charset=utf-8");

                workbook.write(outputStream);
                outputStream.flush();
                outputStream.close();
    }
}