dianwangAdm/src/main/java/com/minpay/common/util/ExcelTemplateUtil.java

package com.minpay.common.util;

import org.apache.commons.fileupload.FileItem;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel导入模板校验
 */
public class ExcelTemplateUtil {
    /**
     * 验证表头数据
     * @param excel
     * @param header
     * @return
     */
    public static boolean verificationExcelHeadLine(FileItem excel, Map<String,Object> header) throws IOException, InvalidFormatException {
        boolean flag = true;

        List<String[]> list = (List<String[]>) header.get("header");
        Workbook wb = WorkbookFactory.create(excel.getInputStream());
        String fileName = excel.getName();
        Workbook hssfWorkbook = null;
        if (fileName.endsWith(".xlsx") ){
            hssfWorkbook = new XSSFWorkbook(excel.getInputStream());//Excel 2007
        }else if(fileName.endsWith(".xls")){
            hssfWorkbook = new HSSFWorkbook(excel.getInputStream());//Excel 2003
        }
        int numSheets = 0;//hssfWorkbook.getNumberOfSheets();
        List<String[]> listWK = new ArrayList<String[]>();
        //取所有sheet页的表头
        if ( numSheets == 0) {
            flag = false;
        } else {
            Map<String,List<String[]>> temp = new HashMap<String,List<String[]>>();
            for (int numSheet = 0; numSheet < numSheets; numSheet++) {//遍历Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                int rows = StringUtil.ObjToInt(header.get("headerRows"));
                // 循环行Row
                for (int rowNum = 0; rowNum <= rows; rowNum++) {//遍历行
                    int cols = StringUtil.ObjToInt(header.get("cols"));

                    String[] colsValue = new String[cols];
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow != null) {
                        for (int cloNum = 0; cloNum < cols; cloNum++){//遍历列
                            Cell value = hssfRow.getCell(cloNum);
                            colsValue[cloNum] = StringUtil.ObjectToString(value);
                        }
                    }
                    listWK.add(colsValue);

                }
            }
        }
        //比对表头，如果不一致则表示模板不一致，校验失败
        String[] excelheader = list.get(list.size() - 1);
        String[] excelheader2 = listWK.get(listWK.size() - 1);
        if(excelheader.length != excelheader2.length){
            flag = false;
        } else {
            for (int i = 0; i < excelheader.length; i++) {
                if(!excelheader[i].equals(excelheader2[i])){
                    flag = false;
                    break;
                }
            }
        }
        return flag;
    }
}