利用Try-with-resources（TWR）读取Excel文件

使用原因： 当我们在读取字节流时，将取到的字节流写入到文件中，首先我们要打开流，用完之后将流关闭，在关闭的操作过程中很容易产生错误，比如，我们在进行try，catch的时候，即使遇到异常，但是并做不了什么。

TWR的优势： 改变了语法，减少了错误发生的可能行。

Java7资源管理语法

try (OutputStream out = new FileOutputStream(file);
     InputStream is = url.openStream() ) {
  byte[] buf = new byte[4096];
  int  len;
  while ((len = is.read(buf)) > 0) {
    out.write(buf, 0, len);
  }
}

具体实现代码

import com.yinglian.java.core.entities.ResultMessage;
import lombok.extern.apachecommons.CommonsLog;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.util.StringUtils;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author gmh
 * @ClassName: ExcelUtil
 * @Description: Ｅxcel表格数据获取工具类
 * @date 2017年3月31日 下午6:18:08
 */
@CommonsLog
public class ExcelUtil {

    private String filPath;

    private String sheetName;

    private List<String> headlist;

    private List<Map<String, String>> maplist;

    public ExcelUtil(String filPath) {
        this.filPath = filPath;
    }

    /**
     * 获取excel数据信息
     *
     * @return
     */
    public List<Map<String, String>> getMaplist() {
        return maplist;
    }

    /**
     * 获取表头信息
     *
     * @return
     */
    public List<String> getHead() {
        return headlist;
    }


    /**
     * 获取excel所有的数据信息集合
     *
     * @return　 返回excel中的所有数据信息集合
     */
    public List<Map<String, String>> getContents() {
        log.info("开始读取Ｅxcel表中数据信息");

        ArrayList<ArrayList<String>> rows = null;
        Workbook rwb = null;
        Sheet sheet = null;

        File file = new File(this.filPath);

        try (FileInputStream stream = new FileInputStream(file)) {
            if (checkFileFormat(filPath)) {
                rwb = new HSSFWorkbook(stream);
            } else {
                rwb = new XSSFWorkbook(stream);
            }

            sheet = rwb.getSheetAt(0);
            sheetName = sheet.getSheetName();

            int totalRows = sheet.getPhysicalNumberOfRows();

            if (totalRows > 0 && sheet.getRow(0) != null) {
                int totalCells = sheet.getRow(0).getPhysicalNumberOfCells();

                rows = readExcel(sheet, totalRows, totalCells);

                if (rows.size() == 0) {
                    log.info("excel表格中没有数据信息");
                    return null;
                }

                headlist = getPerRowInfo(0, totalCells, sheet);

                //log.info("开始将Ｅxcel表中处理后信息进行封装到Map中");

                maplist = new ArrayList<>();
                //循环行，然后封装进map
                for (int k = 0; k < rows.size(); k++) {

                    //log.info("开始封装第" + (k + 1) + "行．．．．．．．．．．．．．．．．．．．．");
                    HashMap<String, String> map = PerRowInfoToMap(k, headlist, rows);
                    maplist.add(map);
                }
            }
        } catch (FileNotFoundException e) {
            log.error("excel文件不存在", e);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                file.delete();
                log.info("读取Ｅxcel表中数据信息完成");
            } catch (Exception e) {
                log.error("本地文件删除出错",e);
            }
        }
        return maplist;
    }

    /**
     * 写入excel文件
     * @param localPath
     *          本地存储地址
     * @param faildatalist
     *          失败数据集合
     */
    public ResultMessage<String> writeExcel(String localPath, List<Map<String, String>> faildatalist) {
        ResultMessage<String> resultMessage = new ResultMessage<>();

        log.info(String.format("开始向文件:%s写入数据",localPath));
        Row row = null;
        Workbook rwb = null;
        int cellnum = 0;
        try (FileOutputStream outputStream = new FileOutputStream(localPath)) {
            rwb = new XSSFWorkbook();
            Sheet sheet = rwb.createSheet(sheetName);

            //表格列数
            cellnum = headlist.size();

            //填入表头
            row = sheet.createRow(0);
            for (int i = 0; i < cellnum; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(headlist.get(i));
            }

            //填入内容
            for (int i = 0; i < faildatalist.size(); i++) {
                row = sheet.createRow(i + 1);
                for (int j = 0; j < cellnum; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(faildatalist.get(i).get(headlist.get(j)));
                }
            }

            rwb.write(outputStream);

            log.info("写入数据结束");
            resultMessage.setSuccess(true);
        } catch (Exception e) {
            resultMessage.setSuccess(false);
            resultMessage.setMessage("保存错误数据为Excel文件时出错" + e.getMessage());
            log.error("保存错误数据为Excel文件时出错", e);
        }

        return resultMessage;
    }

        /**
         * 获得excel的Sheet表
         *
         * @param filePath excel文件的地址
         *
         * @return 返回判断结果
         */

    private boolean checkFileFormat(String filePath) {
        boolean is = false;
        Pattern p = Pattern.compile("\\.(xls)$");
        Matcher m = p.matcher(filePath);
        if (m.find()) {
            is = true;
        }
        return is;
    }

    /**
     * 获取sheet表中的每一行信息集合
     *
     * @param rownum excel表格信息的行数
     * @param cells  excel表格总共的列数
     * @param sheet  excel第一张Sheet表
     * @return 返回每行数据信息集合
     */
    private ArrayList<String> getPerRowInfo(int rownum, int cells, Sheet sheet) {

        ArrayList<String> rowlist = null;
        int emptynum = 0;

        try {
            rowlist = new ArrayList<>();
            for (int j = 0; j < cells; j++) {
                String rightTypeCell = "";
                Cell cell = sheet.getRow(rownum).getCell(j);
                rightTypeCell = getRightTypeCell(cell);
                if (StringUtils.isEmpty(rightTypeCell)) {
                    emptynum++;
                }
                rowlist.add(rightTypeCell);
            }
            if (emptynum == cells) {
                return null;
            }
        } catch (Exception e) {
            log.error("获取sheet表中第" + (rownum + 1) + "行信息集合失败", e);
        }

        return rowlist;
    }

    /**
     * 读取excel所有数据信息
     *
     * @param sheet      sheet表
     * @param totalRows  总行数
     * @param totalCells 总列数
     * @return 返回excel中的数据集合
     */
    private ArrayList<ArrayList<String>> readExcel(Sheet sheet, int totalRows, int totalCells) {

        ArrayList<ArrayList<String>> conlumlist = new ArrayList<>();

        for (int i = 1; i < totalRows; i++) {
            ArrayList<String> perRowInfo = getPerRowInfo(i, totalCells, sheet);
            if (perRowInfo != null) {
                conlumlist.add(perRowInfo);
            }

        }
        return conlumlist;
    }


    /**
     * 将所读取数据的每行信息封装入map
     *
     * @param rownum   行数
     * @param headList 头信息
     * @param crows    已读取的数据信息
     * @return 返回每行数据的map集合
     */
    private HashMap<String, String> PerRowInfoToMap(int rownum, List<String> headList,
                                                    ArrayList<ArrayList<String>> crows) {
        HashMap<String, String> permap = null;
        try {
            permap = new HashMap<>();
            ArrayList<String> rowStr = crows.get(rownum);
            for (int num = 0; num < headList.size(); num++) {
                permap.put(headList.get(num).trim(), rowStr.get(num));
            }
        } catch (Exception e) {
            log.error("将excel中第" + rownum + "行数据封装进map出错", e);
        }
        return permap;

    }


    /**
     * @param cell 一个单元格的对象
     * @return 返回该单元格相应的类型的值
     */
    private String getRightTypeCell(Cell cell) {
        if (cell == null) {
            return "";
        }
        String object = null;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                } else {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                }
                object = cell.getStringCellValue() + "";
                break;

            case Cell.CELL_TYPE_STRING: // 字符串
                object = cell.getStringCellValue();
                break;

            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                object = cell.getBooleanCellValue() + "";
                break;

            case Cell.CELL_TYPE_FORMULA: // 公式
                object = cell.getCellFormula() + "";
                break;

            case Cell.CELL_TYPE_BLANK: // 空值
                object = "";
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                object = "非法字符";
                break;

            default:
                object = "未知类型";
                break;
        }
        return object;
    }


    public static void main(String[] args) throws IllegalAccessException, InstantiationException {

        String upload = "https://ohle6xur4.qnssl.com/o_1bcjm8tondu3hlpac110e0i2pe.xlsx";
        String filePath = "/home/gmh/test1.xlsx";
        String filePath1 = "/home/gmh/test2.xlsx";
        long starttime = System.currentTimeMillis();
        ExcelUtil excelUtil = new ExcelUtil(filePath);
        List<Map<String, String>> head = excelUtil.getContents();
        excelUtil.writeExcel(filePath1, head);
        long endtime = System.currentTimeMillis();
        log.info("工具类运行共耗时：" + (endtime - starttime) + "毫秒");


    }


}

参考文章：http://www.ituring.com.cn/article/9288