使用Apache POI读取/写入不同的Microsoft Office文件格式 [英] Read / Write different Microsoft Office file formats using Apache POI

问题描述

如何使用Apache POI在Microsoft Excel电子表格上执行不同的功能?

How to perform different functions on Microsoft Excel spreadsheet using Apache POI ?

我正在尝试从Spring MVC应用程序生成和更新Excel文件(来自DB的数据).

I'm trying to generate and update an Excel file ( data from DB ) from my Spring MVC App..

谢谢

推荐答案

包含apache poi jar文件

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.15</version>
</dependency>

要读取Excel文件

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//..
FileInputStream file = new FileInputStream(new File("C:\\test.xls"));

//Get the workbook instance for XLS file 
HSSFWorkbook workbook = new HSSFWorkbook(file);

//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);

//Get iterator to all the rows in current sheet
Iterator<Row> rowIterator = sheet.iterator();

//Get iterator to all cells of current row
Iterator<Cell> cellIterator = row.cellIterator();

我们在上述代码段中使用的类 HSSFWorkbook 和 HSSFSheet 适用于.xls格式.为了使用.xlsx，请使用 XSSFWorkbook 和 XSSFSheet 类.

The classes we used in above code snippet, HSSFWorkbook and HSSFSheet works for .xls format. In order to work with .xlsx use XSSFWorkbook and XSSFSheet class.

类似于HSSF，POI的其他文件格式前缀也不同:

Similar to HSSF, POI has different prefix for other file formats too:

HSSF(可怕的SpreadSheet格式) –读写Microsoft Excel(XLS)格式的文件.

HSSF (Horrible SpreadSheet Format) – reads and writes Microsoft Excel (XLS) format files.

XSSF(XML SpreadSheet格式) –读写Office Open XML(XLSX)格式的文件.

XSSF (XML SpreadSheet Format) – reads and writes Office Open XML (XLSX) format files.

HPSF(可怕的属性集格式) –从Microsoft Office文件中读取文档摘要"格式.

HPSF (Horrible Property Set Format) – reads "Document Summary" formation from Microsoft Office files.

HWPF(可怕的文字处理器格式) –旨在读取和写入Microsoft Word 97(DOC)格式的文件.

HWPF (Horrible Word Processor Format) – aims to read and write Microsoft Word 97 (DOC) format files.

HSLF(可怕的幻灯片布局格式) –用于Microsoft PowerPoint文件的纯Java实现.

HSLF (Horrible Slide Layout Format) – a pure Java implementation for Microsoft PowerPoint files.

HDGF(可怕的DiaGram格式) – Microsoft Visio二进制文件的初始纯Java实现.

HDGF (Horrible DiaGram Format) – an initial pure Java implementation for Microsoft Visio binary files.

HPBF(可怕的PuBlisher格式) –用于Microsoft Publisher文件的纯Java实现.

HPBF (Horrible PuBlisher Format) – a pure Java implementation for Microsoft Publisher files.

HSMF(可怕的愚蠢邮件格式) –用于Microsoft Outlook MSG文件的纯Java实现.

HSMF (Horrible Stupid Mail Format) – a pure Java implementation for Microsoft Outlook MSG files.

DDF(可怕的绘图格式) –用于解码Microsoft Office绘图格式的软件包.

DDF (Dreadful Drawing Format) – a package for decoding the Microsoft Office Drawing format.

创建新的Excel文件

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//..
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("FuSsA sheet");
//Create a new row in current sheet
Row row = sheet.createRow(0);
//Create a new cell in current row
Cell cell = row.createCell(0);
//Set value to new value
cell.setCellValue("Slim Shady");
    try {
        FileOutputStream out = 
                new FileOutputStream(new File("C:\\new.xls"));
        workbook.write(out);
        out.close();
        System.out.println("Excel written successfully..");

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

更新现有的Excel文件

try {
            FileInputStream file = new FileInputStream(new File("C:\\update.xls"));

            HSSFWorkbook workbook = new HSSFWorkbook(file);
            HSSFSheet sheet = workbook.getSheetAt(0);
            Cell cell = null;

            //Update the value of cell
            cell = sheet.getRow(1).getCell(2);
            cell.setCellValue(cell.getNumericCellValue() * 2);
            cell = sheet.getRow(2).getCell(2);
            cell.setCellValue(cell.getNumericCellValue() * 2);
            cell = sheet.getRow(3).getCell(2);
            cell.setCellValue(cell.getNumericCellValue() * 2);

            file.close();

            FileOutputStream outFile =new FileOutputStream(new File("C:\\update.xls"));
            workbook.write(outFile);
            outFile.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

有关更多详细信息，向单元格添加公式和样式可以查看以下链接:使用Apache POI在Java中读取/写入Excel文件

For more details, Adding Formulas and Adding Styles to Cell you can check this link: Read / Write Excel file in Java using Apache POI

这篇关于使用Apache POI读取/写入不同的Microsoft Office文件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！