入门初学实用的 ExcelUtils 工具类

当前工具类归纳了简单方便操作Excel的一些常用方法(仅供参考):

需要的一些 jar 包:

poi-3.17.jar

poi-examples-3.17.jar

poi-ooxml-3.17.jar

poi-ooxml-schemas-3.17.jar

 

 

 

package com.sys.commons.utils;

import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.*;


import org.apache.log4j.Logger;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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


/**
 * @author jiewai
 */
public class ExcelUtils {

    private static final String EXTENSION_XLS = "xls";
    private static final String EXTENSION_XLSX = "xlsx";

    static Logger log = Logger.getLogger(ExcelUtils.class);

    /**
     * 创建新的Excel文件(写出 .xlsx 格式文件)
     *
     * @param outFilePath
     * @param outFileName
     * @param str
     * @param sheetName
     */
    public static void creatExcels(String outFilePath, String outFileName,
                                   Object[] str, String sheetName) {
        // TODO Auto-generated constructor stub
        // 第一步，创建一个webbook，对应一个Excel文件
        Workbook wb = new XSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        Sheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        for (int i = 0; i < 1; i++) {
            Row row = sheet.createRow(i);
            // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
            for (int j = 0; j < str.length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(str[j] == null ? "" : str[j].toString()); // 设置值
            }
        }
        // 第六步，将文件存到指定位置, 写出到本地,如果需要写出到浏览器,只需替换当前步骤
        try {
            FileUtils.creatFileOrPath(outFilePath, "");
            FileOutputStream fout = new FileOutputStream(outFilePath + outFileName);
            wb.write(fout);
            fout.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 在原有文件中追加数据(写入.xlsx 格式文件)
     *
     * @param outFilePath
     * @param outFileName
     * @param str
     */
    public static void addToExcels(String outFilePath, String outFileName, Object[] str) {
        FileInputStream fs;
        try {
            fs = new FileInputStream(outFilePath + outFileName);
            XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(fs);
            XSSFSheet sheet = wb.getSheetAt(0); // 获取到工作表，excel可能有多个工作表(当前只满足追加第一个sheet文件的方法)
            XSSFRow row;
            FileOutputStream out = new FileOutputStream(outFilePath
                    + outFileName); // 向已存在文件中写数据
            row = sheet.createRow((short) (sheet.getLastRowNum() + 1)); // 在现有行号后追加数据
            for (int j = 0; j < str.length; j++) {
                XSSFCell cell = row.createCell(j);
                cell.setCellValue(str[j] == null ? "" : str[j].toString()); // 设置值
            }
            out.flush();
            wb.write(out);
            out.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (EncryptedDocumentException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 获取以表头为 key 的 map 集合 list
     * @param file 上传文件
     * @return
     */
    public static List<Map<String, Object>> loadAllExcelData(File file) {
        // 检查
        preReadCheck(file.getPath());
        Workbook workbook;
        List<Map<String, Object>> source = null;
        try {
            workbook = getWorkbook(file);
            if (workbook.getNumberOfSheets() <= 0)
                return null;
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 获取每个Sheet表
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet.getLastRowNum() <= 0)
                    return null;
                source = loadSheet(sheet);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return source;
    }

    /**
     * 加载当前sheet中的所有数据
     *
     * @param sheet
     * @return
     */
    private static List<Map<String, Object>> loadSheet(Sheet sheet) {
        Iterator<Row> rows = sheet.iterator();
        List<Map<String, Object>> source = new ArrayList<>();
        Row row = rows.next();
        Map<Integer, String> headers = new HashMap<>();
        int index = 0;
        for (int i = 0; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            String value;
            if (cell != null) {
                value = cell.toString();
                headers.put(i, value);
            }
            index++;
        }
        DecimalFormat df = new DecimalFormat("#.#########");
        while (rows.hasNext()) {
            Map<String, Object> lhhead = new HashMap<>();
            Row r = rows.next();
            Cell cell;
            for (int i = 0; i < index; i++) {
                cell = r.getCell(i);
                Object value;
                if (cell != null) {
                    if (headers.get(i) != null && !"".equals(headers.get(i))) {
                        switch (cell.getCellType()) {
                            case XSSFCell.CELL_TYPE_STRING:
                                value = cell.getRichStringCellValue().getString().trim();
                                break;
                            case XSSFCell.CELL_TYPE_NUMERIC:
                                value = df.format(cell.getNumericCellValue());
                                break;
                            case XSSFCell.CELL_TYPE_BOOLEAN:
                                value = String.valueOf(cell.getBooleanCellValue()).trim();
                                break;
                            case XSSFCell.CELL_TYPE_FORMULA:
                                value = cell.getCellFormula();
                                break;
                            default:
                                value = "";
                        }
                    } else {
                        value = null;// 当列为空
                    }
                    lhhead.put(headers.get(i), value);
                }
            }
            source.add(lhhead);
        }
        return source;
    }

    /**
     * 获取文件扩展名
     *
     * @param path
     * @return String
     * @author zhang 2015-08-17 23:26
     */
    private static String getExt(String path) {
        if (path == null || path.equals("") || !path.contains(".")) {
            return null;
        } else {
            return path.substring(path.lastIndexOf(".") + 1, path.length());
        }
    }

    /***
     * <pre>
     * 取得Workbook对象(xls和xlsx对象不同,不过都是Workbook的实现类)
     *   xls:HSSFWorkbook
     *   xlsx：XSSFWorkbook
     * @return
     * @throws IOException
     * </pre>
     */
    private static Workbook getWorkbook(File file) throws IOException {
        Workbook workbook = null;
        InputStream is = new FileInputStream(file);
        if (file.getPath().endsWith(EXTENSION_XLS)) {
            workbook = new HSSFWorkbook(is);
        } else if (file.getPath().endsWith(EXTENSION_XLSX)) {
            workbook = new XSSFWorkbook(is);
        }
        return workbook;
    }

    /**
     * 文件检查
     *
     * @param filePath  文件路径
     */
    private static void preReadCheck(String filePath) {
        // 常规检查
        File file = new File(filePath);
        if (!file.exists()) {
            log.info("");
        }
        if (!(filePath.endsWith(EXTENSION_XLS) || filePath
                .endsWith(EXTENSION_XLSX))) {
            log.info("文件不是excel" + filePath);
        }
    }


    /**
     *  将已写入的 Excel 文件下载导出到浏览器端
     *
     * @param fileName  文件名
     * @param wb    Workbook 对象
     * @param request HttpServletRequest 对象
     * @param response HttpServletResponse 对象
     */
    public static void writeFileToClient(String fileName, Workbook wb, HttpServletRequest request, HttpServletResponse response) {
        try {
            OutputStream fos;
            fos = response.getOutputStream();
            String userAgent = request.getHeader("USER-AGENT");
            if (org.apache.commons.lang.StringUtils.contains(userAgent, "Mozilla")) {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } else {
                fileName = URLEncoder.encode(fileName, "utf8");
            }
            response.setCharacterEncoding("UTF-8");
            response.setContentType("multipart/form-data");
            response.setHeader("Content-Disposition", "Attachment;Filename=" + fileName);
            wb.write(fos);
            fos.flush();
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 输出 Excel 到本地本地磁盘文件夹
     *
     * @param outFilePath 输出路径
     * @param outFileName   输出文件名
     * @param wb    Workbook对象
     */
    public static void printExcelFileToLocal(String outFilePath, String outFileName, Workbook wb) {
        try {
            FileUtils.creatFileOrPath(outFilePath, "");
            FileOutputStream fout = new FileOutputStream(outFilePath + File.separator + outFileName);
            wb.write(fout);
            fout.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}