POI读取Excle模板中的数据

声明：该Excle模板前两行是固定格式，所以数据是从第3行开始读取。将读取的数据保存在一个字符串二维数组中。

数组的大小是根据Excle中的数据的行数与列数定义的。本demo去除了Excle前两行数据。所以数组的行数是Excle的行数-2。

具体情况具体对待。

package com.im.common.utils;

import java.io.BufferedInputStream;
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 java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

public class ExcleUtil {

    public static  String[][] readExcle(HttpServletRequest request) {
        // TODO Auto-generated method stub
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        CommonsMultipartFile file = (CommonsMultipartFile) multipartRequest.getFile("filename");
        String [][] temp = null;
        if (file != null) {
            try {
                temp = readXls(file.getInputStream(),request);
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return temp;
    }

    /**
     * 逐行遍历其Excel
     */
    private static String [][] readXls(InputStream inputStream,  HttpServletRequest request)
            throws IOException {
        InputStream is = new BufferedInputStream(inputStream);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0); //获得第一sheet
        int column = hssfSheet.getRow(2).getLastCellNum();//获得第3行的列数
        int row = hssfSheet.getLastRowNum()+1;//获取行数
        String [][] arr  = new String [row-2][column]; //创建一个字符串二维数组
        for (int rowNum = 2; rowNum < row; rowNum++) {
            //synchroImport car = new synchroImport();
            HSSFRow hssfRow = hssfSheet.getRow(rowNum);
            for(int colNum = 0;colNum < hssfRow.getLastCellNum(); colNum++) {
                HSSFCell cell = hssfRow.getCell(colNum);
                arr[rowNum-2][colNum] = getCellValue(cell);
            }

        }
        hssfWorkbook.close();
        return arr;
    }

    /**
     * 对Excel的各个单元格的格式进行判断并转换
     */
    private static String getCellValue(HSSFCell cell) {
        String cellValue = "";
        DecimalFormat df = new DecimalFormat("#.###");
        if (cell == null) {
            return "";
        }
        if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC&&HSSFDateUtil.isCellDateFormatted(cell)) {
            return DateUtils.formatDate(
            HSSFDateUtil.getJavaDate(cell.getNumericCellValue()), "yyyy-MM-dd");
        }
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            cellValue = cell.getRichStringCellValue().getString().trim();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            cellValue = df.format(cell.getNumericCellValue()).toString();
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            cellValue = cell.getCellFormula();
            break;
        default:
            cellValue = "";
        }
        return cellValue;
    }
}