导出功能

1、Jsp(一个按钮)

<button class="btn btn-sm no-border btn-info"  id="export">导出</button>

2、Js

$("#export").click(function(){
    window.location.href =path+"/gc/riskBadAssets/exportExcel?filter=" + encodeURIComponent(JSON.stringify(filter));
});

3、Controller

package com.ctbr.applet.controller.manage;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.ctbr.applet.service.IMpRewardListService;
import com.ctbr.baseFrame.core.BaseController;
import com.ctbr.baseFrame.model.AjaxResult;
import com.ctbr.baseFrame.model.PageResult;
import com.ctbr.baseFrame.service.IExcelService;
import com.ctbr.baseFrame.util.UserContext;

import net.sf.json.JSONArray;


/**
 * 业绩管理
 */
@Controller
@RequestMapping(value = "/appletManage/mpReward")
public class MpRewardListController extends BaseController {

    private static Logger logger = Logger.getLogger(MpRewardListController.class);

    @Resource
    private IMpRewardListService service;


    @ResponseBody
    @RequestMapping(value="/exportExcel")
    public void exportExcel(HttpServletResponse response,HttpServletRequest request){
        String filter = request.getParameter("filter");
        Map<String,Object> map = new HashMap<String,Object>();
        String vague = null;
        try {
            if(getFilter(JSONArray.fromObject(filter)) == null || getFilter(JSONArray.fromObject(filter)).equals("")) {
                vague = getFilter(JSONArray.fromObject(filter));
            } else {
                vague = new String(getFilter(JSONArray.fromObject(filter)).getBytes("ISO8859-1"), "UTF-8");
            }
        } catch (Exception e) {
            logger.info("根据模糊匹配时导出excel表格时发生异常", e);
        }
        map.put("filter", vague);
        String titleList[] = {"奖励明细编号" , "业务编号" , "客户名称" , "业务日期" , "发生时间" , "业务金额" , "原始奖励金额" , "实际奖励金额" , "奖励来源" , "奖励规则描述" , "代理人名称" , "状态" , "审核人", "审核时间"  };
        String dataListKey[] = {"bsn_id" , "loan_id" , "customer_name" , "bsn_date" , "generate_date" , "bsn_amount" , "origin_amount" , "act_amount" , "reward_origin_nm" , "reward_desc" , "agent_nm" , "status_flag_nm" , "audit_user", "audit_date" };

        List<Map<String,Object>> dataList =  service.getRewardListExcelData(map);
        excelService.exportExcel("奖励列表", "奖励列表", titleList, dataListKey, dataList, response);
    }

}

4、IMpRewardListService

package com.ctbr.applet.service;

import java.util.List;
import java.util.Map;

import com.ctbr.baseFrame.core.IBaseService;

public interface IMpRewardListService extends IBaseService {
    
    /**业绩管理导出excel所需数据
     * */
    public List<Map<String, Object>> getRewardListExcelData(Map<String, Object> paramsMap);

}

5、MpRewardListServiceImpl

package com.ctbr.applet.service.impl;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.ctbr.applet.service.IMpRewardListService;
import com.ctbr.baseFrame.core.AbstractBaseService;

@Service("mpRewardListService")
@Transactional
public class MpRewardListServiceImpl extends AbstractBaseService implements IMpRewardListService {

    /**操作对应的表
     * */
    @Override
    public String getTableMapping() {
        return "mp_reward_list";
    }

    /**建立列表导出
     * */
    @Override
    public List<Map<String, Object>> getRewardListExcelData(Map<String, Object> paramsMap) {
        List<Map<String, Object>> data = this.baseDao.selectList("mp_reward_list.selectRewardExcel", paramsMap);
        return data;
    }
}

6、IExcelService

package com.ctbr.baseFrame.service;

import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

public interface IExcelService {

    /**
     * 导出Excel
     * @Title: exportExcel   
     * @Description: TODO(这里用一句话描述这个方法的作用)   
     * @param: @param workName        工作簿名称
     * @param: @param sheetName        sheeet名称
     * @param: @param titleList        列名集合
     * @param: @param dataListKey   数据集合key
     * @param: @param dataList         数据
     * @param: @param response         HttpServletResponse
     * @return: void      
     * @throws
     */
    public void exportExcel(String workName, String sheetName,
            String[] titleList, String[] dataListKey, List<Map<String,Object>> dataList, 
            String[] foamatTitle,HttpServletResponse response);
}

7、IExcelServiceImpl

package com.ctbr.baseFrame.service.impl;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.regex.Pattern;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Service;

import com.ctbr.baseFrame.service.IExcelExportService;
import com.ctbr.baseFrame.util.StringUtil;
import com.ctbr.baseFrame.util.TimestampTool;

@Service
public class ExcelExportServiceImpl implements IExcelExportService {

    //日期
    private String p1 ="^[0-9]{4}-(((0[13578]|(10|12))-(0[1-9]|[1-2][0-9]|3[0-1]))|(02-(0[1-9]|[1-2][0-9]))|((0[469]|11)-(0[1-9]|[1-2][0-9]|30)))$";
    //日期：时间
//    private String p2 = "^(\\d{2}|\\d{4})(?:\\-)?([0]{1}\\d{1}|[1]{1}[0-2]{1})(?:\\-)?([0-2]{1}\\d{1}|[3]{1}[0-1]{1})(?:\\s)?([0-1]{1}\\d{1}|[2]{1}[0-3]{1})(?::)?([0-5]{1}\\d{1})(?::)?([0-5]{1}\\d{1})$";
    //千位分割
    private String p3 ="^((-?[1-9][0-9]{0,2}(,\\d{3})*)|0)(\\.\\d{1,2})?$";
    //数字
    private String p4 ="^(-?\\d+)(\\.\\d+)?$";

    
    private CellStyle cellStyleDate = null;
    private CellStyle cellStyleBigDecimal = null;

    private List<Integer>  formatIndexList = null;
    
    /**
     * 不需要格式化的title(字符串转日期、字符串转数值)
     * @param titleList
     */
    public void initFormatIndexList(String[] titleList,String[] foamatTitle) {
        formatIndexList = new ArrayList<Integer>();
        List<String> titleListTemp = Arrays.asList(titleList);
        
        if(foamatTitle != null && foamatTitle.length > 0) {
            for(String title : foamatTitle) {
                formatIndexList.add(titleListTemp.indexOf(title));
            }
        }
    }

    @Override
    public synchronized void  exportExcel(String workName, String sheetName, String[] titleList, String[] dataListKey,
            List<Map<String, Object>> dataList, String[] foamatTitle,HttpServletResponse response) {
        initFormatIndexList(titleList,foamatTitle);
        cellStyleDate = null;
        cellStyleBigDecimal = null;
        
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet(sheetName);
        int idx = 0;
        if (titleList != null && titleList.length > 0) {//设置标题
            idx = 1;
            Row row = sheet.createRow((short)0);
            row.setHeightInPoints((short) 20);
            int j = 0;
            //设置字体
            Font font = wb.createFont();
            font.setFontHeightInPoints((short) 12); //字体高度
            font.setColor(HSSFColor.WHITE.index); //字体颜色
            font.setFontName("黑体"); //字体
            font.setBold(true);
            //设置样式
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setFont(font);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
            cellStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            for (String title : titleList) {
                creatCell(wb, row, j++, title, cellStyle);
            }
        }
        if (dataList != null && !dataList.isEmpty()) {//设置数据
            for (int i = 0; i < dataList.size(); i++) {
                Map<String, Object> data = dataList.get(i);
                Row row = sheet.createRow(i + idx);
                if (dataListKey != null && dataListKey.length > 0) {//存在KEY
                    for (int j = 0; j < dataListKey.length; j++) {
                        creatCell(wb, row, j, data.get(dataListKey[j]), null);
                    }
                } else {//不存在KEY
                    Set<Entry<String, Object>> set = data.entrySet();
                    Iterator<Entry<String, Object>> it = set.iterator();
                    int j = 0;
                    while (it.hasNext()) {
                        Entry<String, Object> entry = it.next();
                        creatCell(wb, row, j++, entry.getValue(), null);
                    }
                }
                
            }
        }
        if (sheet.getRow(0) != null) {
            for (int i = 0; i < sheet.getRow(0).getLastCellNum(); i ++) {//自动调整列宽
                sheet.autoSizeColumn(i);
            }
        }
        try {
            //输出excel
             response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename="  
                    + URLEncoder.encode(workName + ".xls", "UTF-8"));  
            ServletOutputStream out = response.getOutputStream();
            wb.write(out);  
            out.flush();
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    
    
    /**
     * excel单元格日期格式
     * @param workbook
     * @return
     */
    public CellStyle getCellStyleByDate(Workbook workbook) {
        if(cellStyleDate == null) {
            cellStyleDate = workbook.createCellStyle();
            CreationHelper createHelper = workbook.getCreationHelper();
            short dateFormat = createHelper.createDataFormat().getFormat("yyyy/MM/dd");
            cellStyleDate.setDataFormat(dateFormat);
            return cellStyleDate;
        }else {
            return cellStyleDate;
        }
    }
    
    
    /**
     * excel单元格千位分割格式
     * @param workbook
     * @return
     */
    public CellStyle getCellStyleByBigDecimal(Workbook workbook) {
        if(cellStyleBigDecimal == null) {
            cellStyleBigDecimal = workbook.createCellStyle();
            cellStyleBigDecimal.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
            return cellStyleBigDecimal;
        }else {
            return cellStyleBigDecimal;
        }
    }
    
    
    /**
     * 创建单行
     * @Title: creatCell   
     * @Description: TODO(这里用一句话描述这个方法的作用)   
     * @param: @param row
     * @param: @param idx
     * @param: @param val      
     * @return: void      
     * @throws
     */
    private void creatCell(Workbook workbook,Row row, int idx, Object val, CellStyle style) {
        Cell cell = row.createCell(idx);
        if (val instanceof String) {
            if(formatIndexList != null && formatIndexList.size() > 0 
                    && formatIndexList.contains(idx)) {
                if(Pattern.matches(p1, (String)val)){//如果是日期格式的字符
                    cell.setCellValue(TimestampTool.parseDate((String)val));
                    cell.setCellStyle(getCellStyleByDate(workbook));
                }else if(Pattern.matches(p3, (String)val) || Pattern.matches(p4, (String)val)){//如果是千位分割格式的字符
                    try {
                        cell.setCellValue(new DecimalFormat().parse((String)val).doubleValue());
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                    cell.setCellStyle(getCellStyleByBigDecimal(workbook));
                }else {
                    cell.setCellValue((String)val);
                }
                
            }else {
                cell.setCellValue((String)val);
            }
            
        } else if (val instanceof Integer) {
            cell.setCellValue((Integer)val);
        } else if (val instanceof Long) {
            cell.setCellValue((Long)val);
        } else if (val instanceof Date) {
            cell.setCellValue((Date)val);
        } else if (val instanceof BigDecimal) {
            cell.setCellValue(new BigDecimal(StringUtil.isNull(val)).doubleValue());
        } else if (val instanceof Double) {
            cell.setCellValue((Double)val);
        } else {
            cell.setCellValue((String)val);
        }
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

}

导入功能

1、Js

$("#file_uploadBtn").click(function() {
    if(!$("#uploadForm").validate().form()){
        return false;
    }
    // if(!$('#file').get(0).files[0]){        // ie9不支持files属性
    if($('#file').get(0).value === ""){
        $.zkbr.alert("提示信息","请选择上传文件");
        return false;
    }
    $("#file_uploadBtn").prop("disabled",true);
    var data = $("#uploadForm").serializeObject();
    data.collateralId = $("#collateralId").val();
    $.ajaxFileUpload({
        url : path+"/base/migration/upload",
        type: "post",
        secureuri : false,//一般设置为false
        data:  data,
        fileElementId : "file",//文件上传控件的id属性  <input type="file" id="upload" name="upload" />
        dataType : "json",//返回值类型 一般设置为json
        success : function(data) {//服务器成功响应处理函数
            $("#file_uploadBtn").prop("disabled",false);
            loadFile();
            if(data.statusCode=='505'){
                $.zkbr.alert("提示",data.errorMsg);
            }else{
                $("#uploadModal").modal('hide');
                $.zkbr.alert("提示","数据导入成功");
            }
            //location.reload();
        }
    });
});

2、Service

package com.ctbr.base.service;

import org.springframework.web.multipart.MultipartFile;

import com.ctbr.baseFrame.core.IBaseService;
import com.ctbr.baseFrame.model.AjaxResult;

public interface IHistoryDateMigrationService extends IBaseService {

    /**
     * TODO(导入客户信息)
     * @param file excel模板文件
     * @return
     */
    AjaxResult importHistoryDate(MultipartFile file, String historyDateType);
}

3、ServiceImpl

package com.ctbr.base.service.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.ctbr.apply.service.IHistoryDataService;
import com.ctbr.base.service.IHistoryDateMigrationDetailService;
import com.ctbr.base.service.IHistoryDateMigrationService;
import com.ctbr.baseFrame.core.AbstractBaseService;
import com.ctbr.baseFrame.model.AjaxResult;
import com.ctbr.baseFrame.util.StringUtil;
import com.ctbr.baseFrame.util.UserContext;
import com.ctbr.collateral.service.ICollateralRelieveApplyService;
import com.ctbr.collateral.service.INeighbourhoodsManagerService;
import com.ctbr.contract.service.IGCContractExcelService;
import com.ctbr.financial.service.IFinancialFundPaymentService;
import com.ctbr.financial.service.ISplitCustomerTempService;
import com.ctbr.gc.service.ICustomerBaseService;
import com.ctbr.gc.service.IRepayExtensionApplyService;
import com.ctbr.gcapply.service.IApplyLoanCustomerRateService;
import com.ctbr.gcapply.service.IRepayReleaseApplyService;
import com.ctbr.gcapply.service.IRepayReleaseCustomerRateService;
import com.ctbr.gcapply.service.IgcApplyLoanService;
import com.ctbr.repay.service.IRepayExemptApplyService;
import com.ctbr.repay.service.IRepayRefundApplyService;
import com.ctbr.util.ExcelUtil;

@Service("historyDateMigrationService")
public class HistoryDateMigrationServiceImpl extends AbstractBaseService implements IHistoryDateMigrationService {


    @Resource(name="gcCustomerBaseService")
    private ICustomerBaseService customerBaseService;

    @Resource
    private IHistoryDataService historyDataService;

    @Override
    public String getTableMapping() {
        return "gc_history_date_migration_log";
    }

    @Override
    public AjaxResult importHistoryDate(MultipartFile file, String historyDateType) {

        AjaxResult ajaxResult = new AjaxResult();
        String pId = UUID.randomUUID().toString();
        //记录日志表
        Map<String,Object> migrationLogMap = new HashMap<String,Object>();
        migrationLogMap.put("id", pId);
        migrationLogMap.put("file_name", file.getOriginalFilename());
        migrationLogMap.put("business_type", historyDateType);
        migrationLogMap.put("oper_user_id", UserContext.getCurrentUser().getRealName());
        migrationLogMap.put("oper_time", new Date());

        try {
            ExcelUtil excelUtil = new ExcelUtil();
            if("customer".equals(historyDateType)) {//客户
                //调用工具类读取指定页数数据，0默认读取第一页
                List<List<String>> excelDataList = excelUtil.readExcel(file, 0);
                System.out.println(excelDataList);
                List<String> errorMsg = customerBaseService.uploadHistoryData(excelDataList);
                if(errorMsg.size() > 0) {
                    return ajaxResult.fail("505", errorMsg.get(0));
                }

            }else if("collateral".equals(historyDateType)) {//抵押物
                List<List<String>> list = excelUtil.readExcel(file, 0);
                //调用增加的方法
                historyDataService.saveCollateral(list);
            }
        } catch (Exception e) {
            e.printStackTrace();  
            ajaxResult.fail("505", "系统异常！");
            migrationLogMap.put("status", "失败");
            this.baseDao.insert("gc_history_date_migration_log.insert",migrationLogMap);
            return ajaxResult;
        }
        migrationLogMap.put("status", "成功");
        this.baseDao.insert("gc_history_date_migration_log.insert",migrationLogMap);
        return ajaxResult.success200();
    }

}

4、对应的导入增加的方法

@Transactional
@Override
public AjaxResult saveCollateral(List<List<String>> data) {

    AjaxResult ajaxResult = new AjaxResult();

    for(int i=1; i<data.size(); i++) {
        Map<String, Object> map = new HashMap<>();

        List<String> row = data.get(i);

        //抵押物编号
        map.put("collateral_no", row.get(0));

        //押品类型
        String collateral_type = row.get(1);
        if("房产".equals(StringUtils.trim(collateral_type))) {
            map.put("collateral_type", "2");
        } else if("股权".equals(StringUtils.trim(collateral_type))) {
            map.put("collateral_type", "5");
        } else {
            map.put("collateral_type", "6");
        }
        //押品名称
        String collateral_name = row.get(2);
        map.put("collateral_name", collateral_name);

        //权属人
        String customer_name = row.get(3);
        //证件号码
        String customer_card_no = row.get(4);

        Map<String, Object> customer = customerBaseService.getCustomerByCardNo(customer_card_no);
        if(customer == null) {
            throw new RuntimeException("客户 "+customer_name+"【"+customer_card_no+"】不存在");
        }
        map.put("customer_id", customer.get("id"));

        map.put("collateral_use_status", "0");
        map.put("delete_status", "0");

        String collateral_id = warrantInfoService.addByUuid(map);

        if("房产".equals(StringUtils.trim(collateral_type))) {
            //共有人姓名
            String co_owner_name = row.get(5);
            String[] names = co_owner_name.split(",");

            //共有人证件号
            String co_owner_card_no = row.get(6);
            String[] card_nos = co_owner_card_no.split(",");

            //共有人权属证号
            String co_certificate = row.get(7);
            String[] co_certificates = co_certificate.split(",");

            if(!(names.length==card_nos.length && card_nos.length==co_certificates.length)) {
                return ajaxResult.fail("500", "共有人姓名、证件号码、权属证信息必须个数相对应！");
            }

            for(int j=0; j<names.length; j++) {
                Map<String, Object> co_owner_map = new HashMap<>();
                Map<String, Object> co_owner = customerBaseService.getCustomerByCardNo(card_nos[j]);
                co_owner_map.put("customer_id", co_owner.get("id"));
                co_owner_map.put("collateral_id", collateral_id);
                String ownership_certificate = co_certificates[j];
                if("无".equals(ownership_certificate)) {
                    co_owner_map.put("have_ownership_certificate", "1");
                } else {
                    co_owner_map.put("have_ownership_certificate", "2");
                    co_owner_map.put("ownership_certificate",ownership_certificate);
                }
                coOwnerService.addByUuid(co_owner_map);

            }

        }

    }

    return ajaxResult.success200();

}

5、ExcelUtil

package com.ctbr.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.poi.hssf.usermodel.HSSFCell;
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;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

public class ExcelUtil {
    
    private String errorInfo;
    private static int readSheet = 0;//默认是0，读取第一个sheet页
    private static boolean readSheetNum = true;//true开启自定义读取sheet页，false读取全部sheet页

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

    /**
     *     读取excel文件内容
     * @param filePath 文件路径
     * @param num 默认为0(读取第一页)，值为1时读取第二页
     * @return 内容集合
     */
    public List<List<String>> readExcel(MultipartFile file, int num) {
        this.readSheet = num;
        List<List<String>> list = readExcel(file);
        return list;
    }

    /**
     *     读取excel文件内容
     * @param filePath 文件路径
     * @return 内容集合
     */
    public List<List<String>> readExcel(MultipartFile multfile) {

        List<List<String>> dataList = new ArrayList<List<String>>();
        InputStream is = null;
        try {
            String fileName = multfile.getOriginalFilename();

            
            if(fileName.endsWith("xls")){  
                is = multfile.getInputStream();
                //2003  
                dataList = readFile(is, true);

            }else if(fileName.endsWith("xlsx")){  
                is = multfile.getInputStream();
                //2007  
                dataList = readFile(is, false);
 
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    is = null;
                    e.printStackTrace();
                }
            }
        }
        // 返回最后读取的结果
        return dataList;

    }

    // 根据流读取Excel文件
    public List<List<String>> readFile(InputStream inputStream, boolean is2003Excel) {

        List<List<String>> dataLists = null;

        try {
            // 根据版本选择创建Workbook的方式
            Workbook wb = null;
            if (is2003Excel) {
                wb = new HSSFWorkbook(inputStream);
            } else {
                wb = new XSSFWorkbook(inputStream);
            }
            // sheet循环
            int sheetNum = sheetCirculation(wb);
            List<List<String>> dataList = new ArrayList<List<String>>();
            if (readSheetNum) {
                dataLists = read(dataList, wb, readSheet);
            } else {
                for (int i = 0; i < sheetNum; i++) {
                    // Sheet sheet = wb.getSheetAt(i);
                    // 显示sheet名称
                    // System.out.println(sheet.getSheetName());
                    dataLists = read(dataList, wb, i);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataLists;

    }

    // 读取数据
    private List<List<String>> read(List<List<String>> dataList, Workbook wb, int sheets) {
        
        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
        // 总行数
        int totalRows = 0;
        // 总列数
        int totalCells = 0;
        // 第一个shell页
        Sheet sheet = wb.getSheetAt(sheets);
        // Excel的行数
        totalRows = sheet.getPhysicalNumberOfRows();
        // Excel的列数
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        DecimalFormat df = new DecimalFormat("#.######");
        // 遍历Excel的行
        for (int r = 0; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            List<String> rowLst = new ArrayList<String>();
            // 遍历Excel的列
            for (int c = 0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                String cellValue = "";
                if (null != cell) {
                    // 以下是判断数据的类型
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                        if(DateUtil.isCellDateFormatted(cell)){//用于转化为日期格式
                            Date d = cell.getDateCellValue();
                            cellValue = formater.format(d);
                        }else{
                            cellValue = df.format(cell.getNumericCellValue()); 
                        }
                        break;
                    case HSSFCell.CELL_TYPE_STRING: // 字符串
                        cellValue = cell.getStringCellValue();
                        break;
                    case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                        cellValue = cell.getBooleanCellValue() + "";
                        break;
                    case HSSFCell.CELL_TYPE_FORMULA: // 公式
                        cellValue = cell.getCellFormula() + "";
                        break;
                    case HSSFCell.CELL_TYPE_BLANK: // 空值
                        cellValue = "";
                        break;
                    case HSSFCell.CELL_TYPE_ERROR: // 故障
                        cellValue = "非法字符";
                        break;
                    default:
                        cellValue = "未知类型";
                        break;
                    }
                }
                rowLst.add(cellValue);
            }
            // 保存第r行的第c列
            dataList.add(rowLst);
        }
        return dataList;

    }
    
    // 验证excel文件
    public boolean validateExcel(String filePath) {
        // 检查文件名是否为空或者是否是Excel格式的文件
        if (filePath == null || !(is2003Excel(filePath) || is2007Excel(filePath))) {
            errorInfo = "文件名不是excel格式";
            return false;
        }
        // 检查文件是否存在
        File file = new File(filePath);
        if (file == null || !file.exists()) {
            errorInfo = "excel文件不存在";
            return false;
        }
        return true;
    }

    private int sheetCirculation(Workbook wb) {
        int sheetCount = -1;
        sheetCount = wb.getNumberOfSheets();
        return sheetCount;
    }

    //是否是2003的excel，返回true是2003
    public static boolean is2003Excel(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    //是否是2007的excel，返回true是2007
    public static boolean is2007Excel(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    //得到错误信息
    public String getErrorInfo() {
        return errorInfo;
    }
    
    /**
     * 文件类型转换，判断excel是2003还是2007
     * @param cmfFile 
     * @return
     */
    public Map<String,Object> getFile(CommonsMultipartFile cmfFile) {
        CommonsMultipartFile commonsMultipartFile = (CommonsMultipartFile)cmfFile;
        DiskFileItem diskFileItem = (DiskFileItem)commonsMultipartFile.getFileItem();
        String name = diskFileItem.getName();//文件名
        System.out.println("Excel文件名："+name);
        // 判断文件的类型，是2003还是2007
        boolean is2003Excel = true;
        if (is2007Excel(name)) {
            is2003Excel = false;
        }
        File file = diskFileItem.getStoreLocation();
        
        Map<String,Object> map = new HashMap<String, Object>();
        map.put("is2003Excel", is2003Excel);
        map.put("file", file);
        return map;
    }


}