【原創】POI匯入匯出工具類

你好未來發表於2018-12-22

對於可能存在的問題，歡迎指正，萬分感謝。

關於本類執行緒安全性的解釋：多數工具方法不涉及共享變數問題，至於新增合併單元格方法addMergeArea，使用ThreadLocal變數儲存合併資料，ThreadLocal內部借用Thread.ThreadLocalMap以當前ThreadLocal為key進行儲存，設定一次變數，則其他執行緒也會有上次資料的殘留，因此在addMergeArea方法中進行清空的操作。為了保證原子性, 採用ReentrantLock確保一次只有一個執行緒可以進行新增合併資料的操作。執行緒安全性從以上兩個方面保證。水平有限，難免會有疏漏，敬請諒解。主要使用Apache POI進行Excel的匯入、匯出

使用

讀取Excel中的資料

原始資料如下：

方法：public static List<List<String>> readFile(InputStream ins, int headRowNum) throws Exception
使用方式：

        String filePath = "excel檔案路徑";
        File file = new File(filePath);
        InputStream ins = new FileInputStream(file);
        List<List<String>> lists = ExcelUtil.readFile(ins,  2);
        System.out.println(lists);
複製程式碼

返回結果：
[[序號, 部門, 姓名, 上崗證號, 崗職, 入職時間], [, , , , , ], [1, 財務部, 趙六, 001, 出納, 1540915200000], [1, 財務部, 張三, 002, 會計, 1517328000000]] 注：對於Date型別，讀取時讀的是long型別引數

將Excel中的資料轉換為對應的實體Entity

public static <T> List<T> getListFromExcel(InputStream ins, int headRowNum, Class<T> cls, int parseIndex, String... excludeAttr) throws Exception
public static <T> List<T> getListFromExcel(MultipartFile file, int headRowNum, Class<T> cls, int parseIndex, String... excludeAttr) throws Exception
兩種方法本質上沒有什麼區別，可變引數excludeAttr配置實體不匹配的屬性，例如：id 注：轉換的時候，需要保證excel中屬性的順序與實體中屬性的順序對應，例如excel中部門-姓名-上崗證號...這樣，則實體也應該按照這樣的順序定義屬性。

此方法通常用於獲取對應的excel資料，並批量插入資料庫中。

匯出Excel

根據List資料匯出excel

public static void exportExcel(String title, String[] headers, List<?> list, HttpServletResponse response, boolean useXSSF, String sheetName, List<String> includeAttr)
title:匯出名字 headers：表頭陣列，list:資料， useXSSF：是否使用2007Excel, sheetName：建立sheet名字，includeAttr：展示實體的哪些屬性

public static void exportExcel(String title, String[] headers, List<?> list, HttpServletResponse response, boolean useXSSF, String sheetName, String... excludeAttr)主要差別在最後一個，不展示哪些屬性

如果在匯出時需要合併單元格，先呼叫`public static void setMergeAreaList(List list)` 例如：

   List<ExcelUtil.RectangleArea> list = new ArrayList<>();
   ExcelUtil.RectangleArea area = new ExcelUtil.RectangleArea(2,2,0,4);
   list.add(area);
   ExcelUtil.setMergeAreaList(list);
複製程式碼

根據字串列表匯出excel

public static void exportExcel(String title, List<List<String>> list, boolean useXSSF, HttpServletResponse response)

根據字串列表自定義匯出實現

呼叫 public static Workbook getExcelWorkBook(List<List<String>> list, boolean useXSSF)獲取workbook， public static void addMergeArea(Workbook workbook, int sheetIndex, List<RectangleArea> areas)新增合併單元格資料，然後workboo.write(輸出流)即可。其餘依賴工具類，請參見github地址： github.com/studentytj/…

package com.test.sth_useful.common.util;


import com.test.sth_useful.common.util.ReflectionUtils;
import com.test.sth_useful.common.util.StreamUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.concurrent.locks.ReentrantLock;

import static org.apache.poi.ss.usermodel.Cell.*;

/**
 * 關於本類執行緒安全性的解釋：
 * 多數工具方法不涉及共享變數問題，至於新增合併單元格方法addMergeArea，使用ThreadLocal變數儲存合併資料，ThreadLocal
 * 內部借用Thread.ThreadLocalMap以當前ThreadLocal為key進行儲存，設定一次變數，則其他執行緒也會有上次資料的殘留，因此在
 * addMergeArea方法中進行清空的操作。為了保證原子性, 採用ReentrantLock確保一次只有一個執行緒可以進行新增合併資料的操作。
 * 執行緒安全性從以上兩個方面保證。
 * 水平有限，難免會有疏漏，敬請諒解。
 *
 *
 * @date 2018/10/15
 */
public class ExcelUtil {
    /**
     * logger
     */
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);

    private static ThreadLocal<List<RectangleArea>> mergeAreaList = new ThreadLocal<>();

    public static List<RectangleArea> getMergeAreaList() {
        return mergeAreaList.get();
    }

    public static void setMergeAreaList(List<RectangleArea> list) {
        mergeAreaList.set(list);
    }

    /**
     * 獲取單元格的值
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        int cellType = cell.getCellType();
        if (cellType == CELL_TYPE_STRING) {
            cellValue = cell.getStringCellValue();
        } else if (cellType == CELL_TYPE_NUMERIC) {
            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                LOGGER.info("date: {}", cell.getDateCellValue());
                cellValue = String.valueOf(cell.getDateCellValue().getTime());
            } else {
                if (cell instanceof XSSFCell) {
                    cellValue = ((XSSFCell) cell).getRawValue();
                } else {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
//                cellValue = String.valueOf(df.format(cell.getNumericCellValue()));
            }
        } else if (cellType == CELL_TYPE_FORMULA) {
            switch (cell.getCachedFormulaResultType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    // 保留兩位小數
                    cellValue = String.valueOf(new DecimalFormat("#.00").format(cell.getNumericCellValue()));
                    break;
                case Cell.CELL_TYPE_STRING:
                    cellValue = String.valueOf(cell.getRichStringCellValue());
                    break;
            }
        } else if (cellType == CELL_TYPE_BLANK) {
            cellValue = cell.getStringCellValue();
        }
        return cellValue;
    }

    /**
     * 讀取excel
     * 注意： 預設讀取的資料是在第一個sheet中的，暫不支援多個sheet的讀取
     *
     * @param ins        輸入流
     * @param headRowNum 表頭所在行數
     * @return 返回excel中的字串資料
     * @throws Exception
     */
    public static List<List<String>> readFile(InputStream ins, int headRowNum) throws Exception {
        Workbook workbook = WorkbookFactory.create(ins);
        Sheet sheet = workbook.getSheetAt(0);
        // 獲取excel總行數
        int rownum = sheet.getLastRowNum();
        List<List<String>> result = new ArrayList<>();
        LOGGER.info("excel 總行數：{}", rownum);
        // 獲取表頭那一行的資料長度
        short allColumnNum = sheet.getRow(headRowNum).getLastCellNum();

        for (int i = 0; i <= rownum; i++) {
            Row row = sheet.getRow(i);
            if (row == null || row.getPhysicalNumberOfCells() == 0) {
                LOGGER.info("第{}行資料為空.", i);
                continue;
            }
            List<String> oneRecord = new ArrayList<>();
            for (int j = 0; j < allColumnNum; j++) {
                Cell cell = row.getCell(j);
//                cell.setCellStyle(cellStyle);
                if (cell == null) {
                    oneRecord.add("");
                    continue;
                }
                String cellValue = getCellValue(cell);
                oneRecord.add(cellValue);
            }
            result.add(oneRecord);
        }
        LOGGER.info("讀取檔案完成!");
        return result;
    }

    /**
     * 將讀取到的excel資料存入List<cls>列表中
     *
     * @param list        字串
     * @param cls         解析的類物件
     * @param parseIndex  開始解析的行數
     * @param excludeAttr 哪些屬性不解析
     * @return 對應的物件列表
     * @throws Exception
     */
    public static <T> List<T> getEntityList(List<List<String>> list, Class<T> cls, int parseIndex, String... excludeAttr) throws Exception {
        List<T> result = new ArrayList<>();
        for (int i = parseIndex; i < list.size(); i++) {
            List<String> oneRecord = list.get(i);
            T instance = cls.newInstance();
            for (int j = 0, tmp = 0; j < oneRecord.size(); j++) {
                String cellValue = oneRecord.get(j);
                if (StringUtils.isEmpty(cellValue)) continue;
                Field[] fields = cls.getDeclaredFields();
                Field field = fields[j];
                for (String s : excludeAttr) {
                    if (field.getName().equals(s)) {
                        tmp++;
                        break;
                    }
                }
                field = fields[j + tmp];
                Object cellVal = field.getType().equals(Date.class) ? new Date(Long.parseLong(cellValue)) : cellValue;
                ReflectionUtils.setFieldValue(instance, field.getName(), cellVal);
            }
            result.add(instance);
        }
        return result;
    }

    /**
     * 獲取excel表中對應的實體資料
     *
     * @param ins
     * @param headRowNum
     * @param cls
     * @param parseIndex
     * @param excludeAttr
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> getListFromExcel(InputStream ins, int headRowNum, Class<T> cls, int parseIndex, String... excludeAttr) throws Exception {
        List<List<String>> strLists = readFile(ins, headRowNum);
        return getEntityList(strLists, cls, parseIndex, excludeAttr);
    }

    /**
     * 獲取excel對應的實體列表
     *
     * @param file        匯入的excel檔案
     * @param headRowNum  表頭行數，記住：從0開始
     * @param cls         實體類
     * @param parseIndex  從哪一行開始解析
     * @param excludeAttr 哪些屬性不對映
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> getListFromExcel(MultipartFile file, int headRowNum, Class<T> cls, int parseIndex, String... excludeAttr) throws Exception {
        // try(流)是Java7中的try-with-resource語法。當try語句塊執行結束時，InputStream 會被自動關閉，只要實現了AutoCloseable介面的類都可以寫在try括號裡
        try (InputStream ins = file.getInputStream()) {
            List<List<String>> strLists = readFile(ins, headRowNum);
            return getEntityList(strLists, cls, parseIndex, excludeAttr);
        }
    }


    /**
     * 展示某些屬性的方法
     *
     * @param headers
     * @param list
     * @param useXSSF     是否使用2007
     * @param sheetName
     * @param includeAttr
     * @param isInclude
     * @return
     */
    private static <T> Workbook getExcelWorkBook(String[] headers, List<T> list, boolean useXSSF, String sheetName, List<String> includeAttr, boolean isInclude) {
        Workbook workbook = getWorkbook(useXSSF);
        // 建立sheet
        Sheet sheet = workbook.createSheet(sheetName);
        CellStyle cellStyle = getCellStyle(workbook);
        // 建立表頭
        createHeader(headers, sheet, useXSSF, cellStyle);
        Field[] fields = null;
        // 確定行數
        for (int i = 0; i < list.size(); i++) {
            // 建立一行
            Row dataRow = sheet.createRow(i + 1);
            T t = list.get(i);
            if (isInclude) {
                // 為每個單元格設值
                for (int j = 0; j < includeAttr.size(); j++) {
                    String fieldName = includeAttr.get(j);
                    formatCell(sheet, cellStyle, dataRow, t, j, fieldName);
                }
            } else {
                fields = fields == null ? t.getClass().getDeclaredFields() : fields;
                // k用來解決某些欄位並不在excel中展示帶來的資料和列名不匹配問題
                for (int j = 0, k = 0; j < fields.length - 1; j++) {
                    Field field = fields[j];
                    String fieldName = field.getName();
                    // flag標誌代表是否不展示該項的值
                    boolean flag = false;
                    if (includeAttr.contains(fieldName)) {
                        // 解決生成資料不匹配問題
                        k++;
                        flag = true;
                        break;
                    }
                    if (flag) continue;

                    formatCell(sheet, cellStyle, dataRow, t, j - k, fieldName);
                }
            }
        }
        return workbook;
    }

    /**
     * 格式化單元格
     * @param sheet
     * @param cellStyle
     * @param dataRow
     * @param t
     * @param j
     * @param fieldName
     * @param <T>
     */
    private static <T> void formatCell(Sheet sheet, CellStyle cellStyle, Row dataRow, T t, int j, String fieldName) {
        Cell dataCell = dataRow.createCell(j);
        dataCell.setCellStyle(cellStyle);
        // 設定固定寬度, 每個單元格可存放16個字元
        sheet.setColumnWidth(j, 16 * 256);
        setCellValue(dataCell, ReflectionUtils.getFieldValue(t, fieldName));
    }

    /**
     * 獲取通用style
     *
     * @param workbook
     * @return
     */
    private static CellStyle getCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        return cellStyle;
    }

    private static Workbook getWorkbook(boolean useXSSF) {
        return useXSSF ? new XSSFWorkbook() : new HSSFWorkbook();
    }

    /**
     * 給出字串的情況下獲取WorkBook
     *
     * @param list
     * @return
     */
    public static Workbook getExcelWorkBook(List<List<String>> list, boolean useXSSF) {
        Workbook workbook = getWorkbook(useXSSF);
        // 建立sheet, 名字預設為sheet0
        Sheet sheet = workbook.createSheet("sheet0");
        // 確定行數
        for (int i = 0; i < list.size(); i++) {
            // 建立一行
            Row dataRow = sheet.createRow(i);
            List<String> t = list.get(i);
            // 為每個單元格設值
            for (int j = 0; j < t.size(); j++) {
                Cell dataCell = dataRow.createCell(j);
                // 設定固定寬度, 每個單元格可存放16個字元
                setCellValue(dataCell, t.get(j));
            }
        }
        return workbook;
    }

    /**
     * 建立表頭
     *
     * @param headers
     * @param sheet
     */
    private static void createHeader(String[] headers, Sheet sheet, boolean useXSSF, CellStyle commonStyle) {
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell headerRowCell = headerRow.createCell(i);
            headerRowCell.setCellStyle(commonStyle);
            RichTextString text = useXSSF ? new XSSFRichTextString(headers[i]) : new HSSFRichTextString(headers[i]);
            headerRowCell.setCellValue(text);
        }
    }

    /**
     * 為單元格設定值
     *
     * @param dataCell
     * @param value
     */
    public static void setCellValue(Cell dataCell, Object value) {
        if (value instanceof Date) {
            DateFormat sdf = DateUtil.secondFormat.get();
            String zero = "00:00:00";
            if (zero.equals(sdf.format(value).split(" ")[1])) {
                sdf = DateUtil.dayFormat.get();
            }
            dataCell.setCellValue(sdf.format(value));
        } else if (value instanceof Number) {
            dataCell.setCellValue(Double.parseDouble(String.valueOf(value)));
        } else if (value == null) {
            dataCell.setCellValue("");
        } else {
            dataCell.setCellValue(value.toString());
        }
    }


    /**
     * 匯出excel ——展示某些屬性
     *
     * @param title
     * @param headers
     * @param list
     * @param response
     * @param useXSSF
     * @param sheetName
     * @param includeAttr
     */
    public static void exportExcel(String title, String[] headers, List<?> list, HttpServletResponse response, boolean useXSSF, String sheetName, List<String> includeAttr, boolean isInclude) {
        try {
            Workbook workbook = getExcelWorkBook(headers, list, useXSSF, sheetName, includeAttr, isInclude);
            addMergeArea(workbook, 0, getMergeAreaList());
            writeToResp(title, workbook, response, useXSSF);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 給出匯出字串的情況下的匯出方法
     *
     * @param title
     * @param list
     * @param response
     */
    public static void exportExcel(String title, List<List<String>> list, boolean useXSSF, HttpServletResponse response) {
        try {
            Workbook workbook = getExcelWorkBook(list, useXSSF);
            writeToResp(title, workbook, response, useXSSF);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 將excel流寫入response
     *
     * @param title
     * @param workbook
     * @param response
     * @param useXSSF
     */
    private static void writeToResp(String title, Workbook workbook, HttpServletResponse response, boolean useXSSF) {
        ServletOutputStream outputStream = null;
        try {
            LOGGER.info("" + workbook);
            outputStream = response.getOutputStream();
            // 解決瀏覽器及中文亂碼問題https://tools.ietf.org/html/rfc2231
            response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(title, "UTF-8") + (useXSSF ? ".xlsx" : ".xls"));
            response.setContentType("application/msexcel");
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 關閉流資源
            StreamUtil.close(outputStream, workbook);
        }
    }

    /**
     * 合併單元格並水平垂直居中實現
     *
     * @param areas
     */
    public static void addMergeArea(Workbook workbook, int sheetIndex, List<RectangleArea> areas) {
        ReentrantLock lock = new ReentrantLock();
        try {
            lock.lock();
            LOGGER.info("Thread: {}, threadLocal is: {}.", Thread.currentThread().getName(), String.valueOf(getMergeAreaList()));
            if (areas != null && areas.size() > 0) {
                for (RectangleArea area : areas) {
                    workbook.getSheetAt(sheetIndex)
                            .addMergedRegion(new CellRangeAddress(area.getFirstRow(), area.getLastRow(), area.getFirstCol(), area.getLastCol()));
                    // 合併單元格時預設居中（水平、垂直居中）
                    CellStyle cellStyle = getCellStyle(workbook);
                    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
                    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                    workbook.getSheetAt(sheetIndex).getRow(area.getFirstRow()).getCell(area.getFirstCol()).setCellStyle(cellStyle);
                }
                mergeAreaList.set(null);
            }
        } finally {
            lock.unlock();
        }
    }

    public static class RectangleArea {
        private int firstRow, lastRow, firstCol, lastCol;

        public RectangleArea(int firstRow, int lastRow, int firstCol, int lastCol) {
            this.firstRow = firstRow;
            this.lastRow = lastRow;
            this.firstCol = firstCol;
            this.lastCol = lastCol;
        }

        public int getFirstRow() {
            return firstRow;
        }

        public int getLastRow() {
            return lastRow;
        }

        public int getFirstCol() {
            return firstCol;
        }

        public int getLastCol() {
            return lastCol;
        }
    }

}

複製程式碼

使用工具類使用poi匯入匯出excel報表
2013-09-07
Excel
java 匯入匯出Excel工具類ExcelUtil
2016-04-27
JavaExcel
Java之POI操作Excel表-匯入匯出
2020-09-23
JavaExcel
POI 匯出Excel
2015-05-05
Excel
【匯入匯出】Oracle 常用匯入匯出工具集錦
2015-03-27
Oracle
不想用POI？幾行程式碼完成Excel匯出匯入
2019-03-03
行程Excel
比 poi匯入匯出更好用的 EasyExcel使用小結
2023-03-09
Excel
java實現Excel定製匯出(基於POI的工具類)
2021-01-27
JavaExcel
Java之POI操作，封裝ExcelUtil實現Excel匯入匯出
2018-04-02
Java封裝Excel
HExcel，一個簡單通用的匯入匯出Excel工具類
2023-10-30
Excel
【匯出匯入】匯出匯入大物件
2010-08-16
物件
Excel匯入匯出-(poi)簡單封裝兩個類,拿來就可以用
2018-10-04
Excel封裝
java poi 匯出excel加密
2017-03-27
JavaExcel加密
Poi 匯入格式轉換
2018-09-13
Java 通過Xml匯出Excel檔案，Java Excel 匯出工具類，Java匯出Excel工具類
2017-09-13
JavaXMLExcel
exp/imp匯出匯入工具的使用
2006-05-19
poi 匯出Excel java程式碼
2018-11-26
ExcelJava
poi--excel --匯出例項
2014-08-01
Excel
Java POI匯入Excel檔案
2020-04-05
JavaExcel
POI匯入Excel中文API文件
2018-03-02
ExcelAPI
Oracle exp/imp匯出匯入工具的使用
2008-04-30
Oracle
【匯入匯出】sqlldr 匯入案例
2011-03-09
SQL
【匯出匯入】% 在匯入匯出中的應用。
2011-03-16
[Mysql]——備份、還原、表的匯入匯出
2017-03-18
MySql
【原創】【譯】匯入資料集
2015-10-03
SpringBoot實現Excel匯入匯出，好用到爆，POI可以扔掉了！
2021-10-27
Spring BootExcel
Activity 流程模型匯入匯出-activity流程模型匯入匯出
2024-03-08
模型
Mysql匯入&匯出
2018-12-12
MySql
Mysql匯入匯出
2014-12-16
MySql
doris匯入匯出
2024-03-14
springboot poi匯出excel表格
2018-07-12
Spring BootExcel
一文搞定POI，再也不怕excel匯入匯出了
2024-03-11
Excel
mongodb使用自帶命令工具匯出匯入資料
2023-04-24
MongoDB
【EXP/IMP】使用EXP /IMP工具“模糊”匯出和匯入
2014-08-14
【EXPDP/IMPDP】使用 EXPDP/IMPDP工具“模糊”匯出和匯入
2010-04-09
MySQL資料匯入匯出方法與工具介紹
2007-11-06
MySql
vue excel匯入匯出
2019-03-07
VueExcel
navlicat 匯入匯出SQL
2022-11-05
SQL