Java POI读取excel文件实现-AnyReport报表

Java POI读取excel 支持xls、xlsx

报表存在读取数据源来自excel，系统常常出现读取excel需求，Java POI不仅可以输出excel，也可以读取excel中单元格数据、图片数据，poi也支持excel2007（xlsx）读取，使用poi-3.8-20120326.jar,poi-ooxml-3.8-20120326.jar,poi-scratchpad-3.8-20120326.jar，这些Jar包可以从apache poi获取

poi读取excel

单元格读取

下面是读取excel第一个sheet，多个可以循环读取，然后遍历excel 行Row，再遍历Row的单元格Cell

File excelFile = new File("/Users/mike/table5.xls");
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(excelFile));
HSSFSheet sheet = wb.getSheetAt(0);

for (Row row : sheet) {
    for (Cell cell : row) {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING://字符串
            System.out.print(cell.getRichStringCellValue().getString());
            System.out.print("|");
            break;
        case Cell.CELL_TYPE_NUMERIC://数值与日期
            if (DateUtil.isCellDateFormatted(cell)) {
                System.out.print(String.valueOf(cell.getDateCellValue()));
            } else {
                System.out.print(cell.getNumericCellValue());
            }
            System.out.print("|");
            break;
        case Cell.CELL_TYPE_BOOLEAN://boolean类型
            System.out.print(cell.getBooleanCellValue());
            System.out.print("|");
            break;
        default:
        }
    }
    System.out.println();
}

图片读取

先获取excel所有的图片，再查询pictureIndex 根据pictureIndex获取对应的图片

//读取图片
List<HSSFPictureData> pictures = wb.getAllPictures();  
for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {  
    if (shape instanceof HSSFPicture) {
        HSSFPicture pic = (HSSFPicture) shape;  
        int pictureIndex = pic.getPictureIndex()-1;  
        HSSFPictureData picData = pictures.get(pictureIndex);
        System.out.println("image-size:" + picData.getData().length);
    }  
}

读取sheet名称

wb.getSheetName(0)

整个示例

import java.io.File;
import java.io.FileInputStream;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;

public final class TestImportExcel {

    public static void main(String[] args) throws Exception  {

        File excelFile = new File("/Users/mike/table5.xls");
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(excelFile));
        HSSFSheet sheet = wb.getSheetAt(0);

        for (Row row : sheet) {
            for (Cell cell : row) {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getRichStringCellValue().getString());
                    System.out.print("|");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        System.out.print(String.valueOf(cell.getDateCellValue()));
                    } else {
                        System.out.print(cell.getNumericCellValue());
                    }
                    System.out.print("|");
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue());
                    System.out.print("|");
                    break;
                default:
                }
            }
            System.out.println();
        }
        
        //读取图片
        List<HSSFPictureData> pictures = wb.getAllPictures();  
        for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {  
            if (shape instanceof HSSFPicture) {
                HSSFPicture pic = (HSSFPicture) shape;  
                int pictureIndex = pic.getPictureIndex()-1;  
                HSSFPictureData picData = pictures.get(pictureIndex);
                System.out.println("image-size:" + picData.getData().length);
            }  
        }  
        
        System.out.println(wb.getSheetName(0));
    }
}

xlsx读取单元格

xlsx读取大概与xls读取相类似，xlsx采用是X开头的类，采用的是XSSFWorkbook

File excelFile = new File("/Users/mike/table.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(excelFile));
XSSFSheet sheet = wb.getSheetAt(0);

for (Row row : sheet) {
    for (Cell cell : row) {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            System.out.print(cell.getRichStringCellValue().getString());
            System.out.print("|");
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                System.out.print(String.valueOf(cell.getDateCellValue()));
            } else {
                System.out.print(cell.getNumericCellValue());
            }
            System.out.print("|");
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.print(cell.getBooleanCellValue());
            System.out.print("|");
            break;
        default:
        }
    }
    System.out.println();
}

xlsx读取图片

xlsx读取图片直接获取所有的图片，然后遍历图片获取图片数据

 //读取图片
List<XSSFPictureData> pictures = wb.getAllPictures();  
for (int i = 0; i < pictures.size(); i++) {
    XSSFPictureData pictureData = pictures.get(i);
    byte[] picData = pictureData.getData();
    System.out.println("image-size:" + picData.length);
 }

xlsx示例

import java.io.File;
import java.io.FileInputStream;
import java.util.List;

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

public final class TestImportXlsx {

    public static void main(String[] args) throws Exception  {

        File excelFile = new File("/Users/mike/table.xlsx");
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(excelFile));
        XSSFSheet sheet = wb.getSheetAt(0);

        for (Row row : sheet) {
            for (Cell cell : row) {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getRichStringCellValue().getString());
                    System.out.print("|");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        System.out.print(String.valueOf(cell.getDateCellValue()));
                    } else {
                        System.out.print(cell.getNumericCellValue());
                    }
                    System.out.print("|");
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue());
                    System.out.print("|");
                    break;
                default:
                }
            }
            System.out.println();
        }
        
        //读取图片
        List<XSSFPictureData> pictures = wb.getAllPictures();  
        for (int i = 0; i < pictures.size(); i++) {
            XSSFPictureData pictureData = pictures.get(i);
            byte[] picData = pictureData.getData();
            System.out.println("image-size:" + picData.length);
         }
        System.out.println(wb.getSheetName(0));
    }
}