Java读取excel的示例

一、引用的jar包，apache的POI

// https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.17'
// https://mvnrepository.com/artifact/org.apache.poi/poi
compile group: 'org.apache.poi', name: 'poi', version: '3.17'

 

二、完整的类示例

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelUtil {

    public static void main(String[] args) {
        long t = System.currentTimeMillis();
        // ArrayList<ArrayList<Object>> result = new
        // ExcelUtil().readExcel("D://工作表.xlsx");
        ArrayList<ArrayList<Object>> result = new ExcelUtil().readExcelByColumn("D://工作表.xlsx", new int[] { 1, 2, 9 });
        System.out.println(System.currentTimeMillis() - t);
        for (int i = 0; i < result.size(); i++) {
            for (int j = 0; j < result.get(i).size(); j++) {
                System.out.print("[" + result.get(i).get(j).toString() + "]\t");
            }
            System.out.println("");
        }
    }

    public ArrayList<ArrayList<Object>> readExcel(String fileName) {
        ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
        ArrayList<Object> colList;
        try {
            Workbook wb = initWorkBook(fileName);
            Sheet sheet = wb.getSheetAt(0);
            Row row = null;
            Cell cell = null;
            for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                colList = new ArrayList<Object>();
                if (row == null) {
                    if (i != sheet.getPhysicalNumberOfRows()) {
                        rowList.add(colList);
                    }
                    continue;
                } else {
                    rowCount++;
                }
                for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                    cell = row.getCell(j);
                    if (cell == null) {
                        if (j != row.getLastCellNum()) {
                            colList.add("");
                        }
                        continue;
                    }
                    colList.add(readColValue(cell));
                }
                rowList.add(colList);
            }
            return rowList;
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return null;
    }

    public ArrayList<ArrayList<Object>> readExcelByColumn(String fileName, int[] colId) {
        ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
        ArrayList<Object> colList;
        try {
            Workbook wb = initWorkBook(fileName);
            Sheet sheet = wb.getSheetAt(0);
            Row row = null;
            Cell cell = null;
            for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                colList = new ArrayList<Object>();
                if (row == null) {
                    if (i != sheet.getPhysicalNumberOfRows()) {
                        rowList.add(colList);
                    }
                    continue;
                } else {
                    rowCount++;
                }
                for (int j = 0; j < colId.length; j++) {
                    cell = row.getCell(colId[j]);
                    // here different from read all ! as no end limit.
                    if (cell == null) {
                        colList.add("");
                        continue;
                    }
                    colList.add(readColValue(cell));
                }
                rowList.add(colList);
            }
            return rowList;
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return null;

    }

    private String readColValue(Cell cell) {
        String value = "";
        switch (cell.getCellTypeEnum()) {
        case STRING:
            value = cell.getStringCellValue();
            break;
        case NUMERIC:
            short format = cell.getCellStyle().getDataFormat();
            if (format == 0) {
                DecimalFormat df = new DecimalFormat("#");
                value = df.format(cell.getNumericCellValue());
            } else {
                SimpleDateFormat sdf = null;
                if (format == 14 || format == 31 || format == 57 || format == 58) {
                    sdf = new SimpleDateFormat("yyyy-MM-dd");
                } else if (format == 20 || format == 32) {
                    sdf = new SimpleDateFormat("HH:mm");
                }
                Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                value = sdf.format(date);
            }
            break;
        case BOOLEAN:
            value = String.valueOf(Boolean.valueOf(cell.getBooleanCellValue()));
            break;
        case BLANK:
            value = "";
            break;
        default:
            value = cell.toString();
        }
        return value;
    }

    private Workbook initWorkBook(String fileName) throws IOException {
        File file = new File(fileName);
        InputStream is = new FileInputStream(file);
        Workbook workbook = null;
        if (fileName.endsWith(".xls")) {
            workbook = new HSSFWorkbook(is);
        } else if (fileName.endsWith(".xlsx")) {
            workbook = new XSSFWorkbook(is);
        }
        return workbook;
    }
}