java导入导出Excel工具类

在写系统中，常常用到Excel导入导出数据跟数据库进行操作，给大家提供一个公共类，直接上代码：

package com.yss.db.util;

import com.yss.base.common.exception.BaseException;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
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.math.BigDecimal;
import java.util.*;

public class ExportUtils {
    /**
     * 导入读取单元个内容
     * 返回如果需要转换类型，请判断是否为空，或者空字符串
     *
     * @return
     */
    // 读Excel数据
    public static List<ArrayList<String>> readExcel(MultipartFile file)
            throws BaseException {

        //获取文件名
        String fileName = file.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new BaseException("上传文件格式不正确");
        }
        Workbook workbook = null;
        InputStream is = null;
        try {
            is = file.getInputStream();
            if (fileName.endsWith("xlsx")) {
                workbook = new XSSFWorkbook(is);
            }
            if (fileName.endsWith("xls")) {
                workbook = new HSSFWorkbook(is);
            }
        } catch (Exception e) {
            throw new BaseException("导入失败！");
        }

        List<ArrayList<String>> excelData = null;
        try {

            // 读取第一个工作簿
            Sheet sheet = workbook.getSheetAt(0);
            Row titleRow = sheet.getRow(0);
            // 获取sheet的行数
            int rowCount = sheet.getPhysicalNumberOfRows();
            // 循环行
            excelData = new ArrayList<ArrayList<String>>();
            int sumValue = 0;
            for (int i = 0; i < rowCount; i++) {
                Row row = sheet.getRow(i);
                if (i == 0) {
                    sumValue = row.getLastCellNum();
                }
//            int cells = row.getLastCellNum();
//            int cells = row.getPhysicalNumberOfCells();

                ArrayList<String> rowDataArr = new ArrayList<String>();
                for (int j = 0; j < sumValue; j++) {
                    Cell cell = row.getCell(j);
                    String stringCellValue = getStringCellValue(cell);
                    rowDataArr.add(stringCellValue);
                }
                excelData.add(rowDataArr);
            }
        } catch (Exception e) {
            throw new BaseException("导入失败,请检查文件是否有误！");
        }
        return excelData;
    }


    /**
     * 读取单元格内容，支持获取函数内容
     *
     * @param cell
     * @return
     */
    public static String getStringCellValue(Cell cell) {
        String strCell = "";
        if (cell == null) {
            return strCell;
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                strCell = cell.getRichStringCellValue().getString().trim();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    strCell = DateUtils.format(cell.getDateCellValue(), "yyyy-MM-dd HH:mm:ss");
                } else {
                    BigDecimal numberic = new BigDecimal(cell.getNumericCellValue());
                    strCell = numberic.toPlainString();
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                evaluator.evaluateFormulaCell(cell);
                CellValue cellValue = evaluator.evaluate(cell);
                if (StringUtils.isBlank(cellValue.getStringValue())) {
                    strCell = String.valueOf(cellValue.getStringValue());
                } else {
                    strCell = String.valueOf(cellValue.getNumberValue());
                }
                strCell = String.valueOf(cellValue.getNumberValue());
                break;
            default:
                strCell = "";
        }
        return strCell;
    }

    /**
     * @headerList 所要导出excel表头
     * @注释 请按照顺序传入相关信息
     */
    public static List excelHeaderList(String strHeader) {
        //设置表头集为list<map<String,String>>
        List headerList = new ArrayList();
        int j = 8;
//		String strHeader = "订单编号,收款方,发放账户,充值金额,服务费率,服务费,订单总金额,订单状态,下单时间,";
        String[] split = strHeader.split(",");
        for (int i = 0; i < split.length; i++) {
            Map<String, String> header = new LinkedHashMap<String, String>();
            header.put("header" + i, split[i]);
            headerList.add(header);
        }
        return headerList;
    }


    /**
     * @allList 导出数据集map
     * @headerList 所要导出excel表头
     * @fileName 导出文件名
     * @注释 请按照顺序传入相关信息
     */
    @SuppressWarnings({"unused", "resource"})
    public static void exportingReport(List<Map<String, String>> allList,
                                       List<Map<String, String>> headerList,
                                       String fileName,
                                       HttpServletResponse response) {
        String cnumber = "";
        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        String title = "";
        HSSFSheet sheet = wb.createSheet();
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        // HSSFCellStyle style = wb.createCellStyle();
        // style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        Font font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        CellStyle createCellStyle = wb.createCellStyle();
        createCellStyle.setAlignment(CellStyle.ALIGN_CENTER);// 水平居中
        createCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
        createCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        createCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        createCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        createCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        createCellStyle.setFont(font);
        // 只要在已有的用户管理界面输出个 包含“员工编号” 、“姓名”、“开户行”、“卡号 ”
        // 这四个字段的excel表就行。然后我填好后导入。
        row.setHeightInPoints(25);
        createCellStyle.setFillForegroundColor(IndexedColors.TEAL.getIndex());
        createCellStyle.setFillForegroundColor(HSSFColor.LIME.index);

        //createCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//背景色
        HSSFCell cell = null;
        //获取表头
        for (int i = 0; i < headerList.size(); i++) {
            Map<String, String> map = headerList.get(i);
            cell = row.createCell(i);
            cell.setCellValue(map.get("header" + i));
            cell.setCellStyle(createCellStyle);
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }
        // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
        Font font1 = wb.createFont();
        CellStyle createCellStyle1 = wb.createCellStyle();
        createCellStyle1.setAlignment(CellStyle.ALIGN_CENTER);// 水平居中
        createCellStyle1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
        createCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        createCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        createCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        createCellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        createCellStyle1.setFont(font1);
        //判断数据集是否为空
        if (null != allList) {
            for (int i = 0; i < allList.size(); i++) {
                row = sheet.createRow((int) i + 1);
                Map<String, String> map = allList.get(i);
                int j = 0;
                for (Map.Entry<String, String> entry : map.entrySet()) {
                    // 第四步，创建单元格，并设置值
                    // 订单号
                    HSSFCell cells = row.createCell(j);
                    cells.setCellStyle(createCellStyle1);
                    cells.setCellValue(entry.getValue());
                    row.setHeightInPoints(25);
                    sheet.autoSizeColumn(j);
                    sheet.setColumnWidth(j, sheet.getColumnWidth(j) * 17 / 10);
//					sheet.setColumnWidth(j, 10 * 512);
                    j++;
                }
            }
        }
        try {
            // 设置response的Header
            response.setContentType("application/-excel;charset=utf-8");
            String name = java.net.URLEncoder.encode(fileName + ".xls", "utf-8");
            response.addHeader("Content-Disposition", "attachment; filename="
                    + name + "; filename*=utf-8''" + name);
            // response.addHeader("Content-Length", "" + response.);
            OutputStream toClient = new BufferedOutputStream(
                    response.getOutputStream());
            wb.write(toClient);
            toClient.flush();
            toClient.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


}

pom文件里加依赖：

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.8</version>
        </dependency>

知识拓展：

1.设置Excel的自动筛选
//创建sheet页 Sheet sheet = workbook.createSheet(); CellRangeAddress c = CellRangeAddress.valueOf("A1:B1"); sheet.setAutoFilter(c); sheet.protectSheet("edit"); sheet.protectSheet("111111");

2.设置保护工作表密码

sheet.protectSheet("edit");//先设置edit，再设置密码
sheet.protectSheet("111111");