Read / Write different Microsoft Office file forma

问题:

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

回答1:

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

回答2:

For manipulation of any cells or adding formula , you can use the folowing:

HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Calculate Simple Interest");

        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("Pricipal Amount (P)");
        header.createCell(1).setCellValue("Rate of Interest (r)");
        header.createCell(2).setCellValue("Tenure (t)");
        header.createCell(3).setCellValue("Interest (P r t)");

        Row dataRow = sheet.createRow(1);
        dataRow.createCell(0).setCellValue(14500d);
        dataRow.createCell(1).setCellValue(9.25);
        dataRow.createCell(2).setCellValue(3d);
        dataRow.createCell(3).setCellFormula("A2*B2*C2");

        try {
            FileOutputStream out = 
                    new FileOutputStream(new File("C:\\formula.xls"));
            workbook.write(out);
            out.close();
            System.out.println("Excel written successfully..");

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

For adding styles to the cell,

you can use: cell.setCellStyle(style);

For adding background to cells, you can use the following:

cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);