java Excel工具类 ExcelUtils

package com.wqh.fsrm.common.util;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel表格操作类
 */
public class ExcelUtils {

    //表格各种列类型
    public static final int CELL_TYPE_NUMERIC = 0;
    public static final int CELL_TYPE_STRING = 1;
    public static final int CELL_TYPE_FORMULA = 2;
    public static final int CELL_TYPE_BLANK = 3;
    public static final int CELL_TYPE_BOOLEAN = 4;
    public static final int CELL_TYPE_ERROR = 5;

    /**
     * 上传文件获取 文件路径
     * @param fileName
     * @param mFile
     * @return
     * @throws IOException
     */
    public static String getFileUrl(String fileName, MultipartFile mFile) throws IOException {
        String tmpPath = "/tmp/upload/excels/";
        File directory = new File(tmpPath);
        if (!directory.exists()) {
            directory.mkdirs();
        }
        String path = directory.getAbsolutePath()+"/"+System.currentTimeMillis() + "_" + mFile.getOriginalFilename() + "_" + UUID.randomUUID() + ".xlsx";
        File file = new File(path);
        if (!file.exists()) {
            file.createNewFile();
        }
        mFile.transferTo(file);
        return path;
    }

    /**
     * 读取excel
     * @param path
     */
    public static void readExcel(String path){
        try {
            File file = new File(path);
            String fileName = file.getName();
            InputStream is = new FileInputStream(path);
            Workbook wb = isExcel2003(fileName) ? new HSSFWorkbook(is) : new XSSFWorkbook(is);
            Sheet sheet = wb.getSheetAt(0);
            for (int i = sheet.getFirstRowNum()+1; i < sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);
                for(int j = row.getFirstCellNum(); j < row.getLastCellNum();j++){
                    Cell cell = row.getCell(j);
                    String value = String.valueOf(getCellValue(cell));
                    // 具体的业务逻辑
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 读取excel
     * @param path
     */
    public static List<Map<String,Object>> readExcelToList(String path){
        List<Map<String,Object>> list = new ArrayList<>();
        try {
            File file = new File(path);
            String fileName = file.getName();
            InputStream is = new FileInputStream(path);
            Workbook wb = isExcel2003(fileName) ? new HSSFWorkbook(is) : new XSSFWorkbook(is);
            Sheet sheet = wb.getSheetAt(0);
            Row rowFirst = sheet.getRow(0);
            for (int i = sheet.getFirstRowNum()+1; i < sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);
                Map<String,Object> map = new HashMap<>();
                for(int j = row.getFirstCellNum(); j < row.getLastCellNum();j++){
                    Cell cell = row.getCell(j);
                    String value = String.valueOf(getCellValue(cell));
                    String key =String.valueOf(getCellValue(rowFirst.getCell(j)));
                    // 具体的业务逻辑
                    map.put(key,value);
                }
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    public static Object getCellValue(Cell cell) throws Exception {
        Object value;
        //判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case NUMERIC:
                value = cell.getNumericCellValue();
                break;
            case STRING:
                value = cell.getStringCellValue();
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case FORMULA:
                throw new Exception("excel cell unSupport expression");
            case ERROR:
                throw new Exception("excel cell type excel");
            case BLANK:
                value = "";
                break;
            default:
                throw new Exception("known excel cellType");
        }
        return value;
    }


    public static String generateTimestampNo() {
        String tradeNo = "";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
        tradeNo += sdf.format(new Date());
        Random rand = new Random(Thread.currentThread().getId() ^ System.nanoTime());
        tradeNo += new DecimalFormat("0000").format(rand.nextInt(10000));
        return tradeNo;
    }

    /**
     * 创建导出excel
     * @param info
     * @return
     */
    public static void createExportExcel(Map<String,Object> info){
        try {
            HttpServletResponse response = (HttpServletResponse) info.get("response");
            String fileName = (String)info.get("fileName");
            List<String> columnTitles = (List<String>)info.get("columnTitles");
            List<List<String>> datas = (List<List<String>>) info.get("datas");
            int columnWidth = 16;
            if(info.containsKey("columnWidth")){
                columnWidth = (Integer)info.get("columnWidth");
            }
            List<Integer> columnWidthList = (List<Integer>)info.get("columnWidthList");//各列列宽
            short rowHeight = 400;
            if(info.containsKey("rowHeight")){//行高
                rowHeight = Short.parseShort(info.get("rowHeight").toString());
            }
            OutputStream os = null;
            os = response.getOutputStream();// 取得输出流
            response.reset();// 清空输出流
            response.setHeader("Content-disposition", "attachment; filename="
                    + new String((fileName +generateTimestampNo()).getBytes("GB2312"), "8859_1")
                    + ".xlsx");// 设定输出文件头
            response.setContentType("application/msexcel");// 定义输出类型

            Workbook workbook = new HSSFWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            Sheet sheet = workbook.createSheet();
            sheet.setDefaultColumnWidth(columnWidth);
            if(columnWidthList != null && columnWidthList.size() == columnTitles.size()){
                for(int i=0;i<columnWidthList.size();i++){
                    sheet.setColumnWidth(i,columnWidthList.get(i)*256);
                }
            }
            int rowIndex = 0;
            Row row = sheet.createRow(rowIndex);
            row.setHeight(rowHeight);
            for(int i=0;i<columnTitles.size();i++){
                row.createCell(i).setCellValue(columnTitles.get(i));
            }
            rowIndex++;
            for(int i=0;i<datas.size();i++){
                List<String> data = datas.get(i);
                row = sheet.createRow(rowIndex);
                row.setHeight(rowHeight);
                for(int j=0;j<data.size();j++){
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(data.get(j));
                }
                rowIndex++;
            }
            workbook.write(os);
            os.flush();
            os.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        List<Map<String,Object>> list = readExcelToList("D:/5.xlsx");
        System.out.println(list);
    }
}