导出导入ExcelUtil工具类

package com.czqc.czc.web.utils;

import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtil01 {
    private final static String excel2003L = ".xls";    //2003- 版本的excel
    private final static String excel2007U = ".xlsx";   //2007+ 版本的excel

    /**
     * 导出Excel
     *
     * @param sheetName sheet名称
     * @param title     标题
     * @param values    内容
     * @param wb        HSSFWorkbook对象
     * @return
     */
    public static Workbook getHSSFWorkbook(String sheetName, String[] title, Object[][] values, Workbook wb) throws IOException {

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        Sheet sheet = wb.createSheet(sheetName);
        //设置列的宽度
        sheet.setColumnWidth(2, 256*20);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        CellStyle style = wb.createCellStyle();//大多数使用

        Row row = sheet.createRow(1);


        //声明列对象
        Cell cell = null;

        //创建标题
        Row row1 = sheet.createRow(0);
        for (int i = 0; i < title.length; i++) {
            cell = row1.createCell(i);
            cell.setCellValue(title[i]);
        }

        //创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                //将内容按顺序赋给对应的列对象
                Cell cellc = row.createCell(j);
                if (!org.springframework.util.StringUtils.isEmpty(values[i][j])) {
                    if (NumberUtils.isNumber(values[i][j] + "") && Double.parseDouble(values[i][j] + "") != 0) {
                        cellc.setCellValue(Double.parseDouble(values[i][j] + ""));
                    } else if (NumberUtils.isNumber(values[i][j] + "") && Double.parseDouble(values[i][j] + "") == 0) {
                        cellc.setCellValue("");
                    } else {
                        cellc.setCellValue(values[i][j] + "");
                    }
                } else {
                    cellc.setCellValue("");
                }
                cellc.setCellStyle(style);
            }
        }
        return wb;
    }

    /**
     * 导入Excel
     */
    public static List<List<Object>> setHSSFWorkbook(InputStream in, String fileName) throws IOException {
        List<List<Object>> list = new ArrayList<>();
        try {
            Workbook workbook = getWorkbok(in, fileName);
            //创建Excel工作薄
            if (null == workbook) {
                throw new Exception("创建Excel工作薄为空！");
            }
            System.out.println("导入数据Sheets="+workbook.getNumberOfSheets());
            System.out.println("导入数据RowNum="+workbook.getSheetAt(0).getLastRowNum());
            System.out.println("导入数据CellNum="+workbook.getSheetAt(0).getRow(0).getLastCellNum());
            // 循环工作表Sheet
            for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
                Sheet hssfSheet = workbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                // 循环行Row
                for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row row = hssfSheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }
                    // 循环列Cell
                    List<Object> li = new ArrayList<>();
                    for (int y = 0; y < row.getLastCellNum(); y++) {
                        Cell cell = row.getCell(y);
                        li.add(getCellValue(cell));
                    }
                    list.add(li);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }


    //发送响应流方法
    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            fileName = new String(fileName.getBytes(), "ISO8859-1");
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    //获取单元格各类型值，返回字符串类型
    public static Object getCellValue(Cell cell) {
        Object value = "";
        try {
            DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
            SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
            DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
            if (cell == null) {
                return value;
            }
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getRichStringCellValue().getString();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                        value = df.format(cell.getNumericCellValue());
                    } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                        value = sdf.format(cell.getDateCellValue());
                        value = sdf.parse(value + "");
                    } else {
                        value = df2.format(cell.getNumericCellValue());
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    DecimalFormat dft = new DecimalFormat("#");
                    try {
                        value = cell.getStringCellValue();
                    } catch (IllegalStateException e) {
                        value = String.valueOf(dft.format(cell.getNumericCellValue()));
                    }
                    break;

                default:
                    break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value;
    }


    public static Workbook getWorkbok(InputStream in, String filename) throws IOException {
        Workbook wb = null;
        if (filename.endsWith(excel2003L)) {  //Excel 2003
            wb = new HSSFWorkbook(in);
        } else if (filename.endsWith(excel2007U)) {  // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }
}