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

查看:44
本文介绍了使用 Apache POI 读取/写入不同的 Microsoft Office 文件格式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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

谢谢

解决方案

包含 apache poi jar 文件

<依赖><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.15</version></依赖>

<块引用>

读取 Excel 文件

import org.apache.poi.hssf.usermodel.HSSFSheet;导入 org.apache.poi.hssf.usermodel.HSSFWorkbook;//..FileInputStream file = new FileInputStream(new File("C:\\test.xls"));//获取XLS文件的工作簿实例HSSFWorkbook 工作簿 = 新的 HSSFWorkbook(file);//从工作簿中获取第一张工作表HSSFSheet 工作表 = workbook.getSheetAt(0);//获取当前工作表中所有行的迭代器迭代器<行>rowIterator = sheet.iterator();//获取当前行所有单元格的迭代器迭代器<Cell>cellIterator = row.cellIterator();

我们在上述代码片段中使用的类 HSSFWorkbookHSSFSheet 适用于 .xls 格式.为了使用 .xlsx,请使用 XSSFWorkbookXSSFSheet 类.

与 HSSF 类似,POI 对于其他文件格式也有不同的前缀:

HSSF(可怕的电子表格格式) – 读取和写入 Microsoft Excel (XLS) 格式的文件.

XSSF(XML 电子表格格式) – 读取和写入 Office Open XML (XLSX) 格式文件.

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

HWPF (Horrible Word Processor Format) – 旨在读写 Microsoft Word 97 (DOC) 格式的文件.

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

HDGF(Horrible DiaGram Format) – Microsoft Visio 二进制文件的初始纯 Java 实现.

HPBF(Horrible PuBlisher 格式) – Microsoft Publisher 文件的纯 Java 实现.

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

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

<块引用>

新建 Excel 文件

import org.apache.poi.hssf.usermodel.HSSFSheet;导入 org.apache.poi.hssf.usermodel.HSSFWorkbook;//..HSSFWorkbook 工作簿 = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet("FuSsA sheet");//在当前工作表中新建一行行行 = sheet.createRow(0);//在当前行新建一个单元格单元格单元格 = row.createCell(0);//将值设置为新值cell.setCellValue("Slim Shady");尝试 {FileOutputStream 输出 =new FileOutputStream(new File("C:\\new.xls"));workbook.write(out);关闭();System.out.println("Excel 写入成功..");} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}

<块引用>

更新现有 Excel 文件

尝试{FileInputStream file = new FileInputStream(new File("C:\\update.xls"));HSSFWorkbook 工作簿 = 新的 HSSFWorkbook(file);HSSFSheet 工作表 = workbook.getSheetAt(0);单元格单元格 = 空;//更新单元格的值单元格 = sheet.getRow(1).getCell(2);cell.setCellValue(cell.getNumericCellValue() * 2);单元格 = sheet.getRow(2).getCell(2);cell.setCellValue(cell.getNumericCellValue() * 2);单元格 = sheet.getRow(3).getCell(2);cell.setCellValue(cell.getNumericCellValue() * 2);文件.关闭();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 文件

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

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

Thanks

解决方案

Include apache poi jar file

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

To read an excel file

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

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.

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

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

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

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

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

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

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

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

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

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

Create New Excel File

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

Update Existing Excel File

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

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆