java操作EXCEL

引入

<!--poi对excel2007以上版本的支持-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.12</version>
        </dependency>

代码

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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 java.io.*;
import java.nio.file.NoSuchFileException;
import java.util.*;

import static org.apache.poi.ss.usermodel.CellType.STRING;

/**
 * 处理excel读入的工具类
 *
 * @author Jim
 * @date 2020/2/28
 */
public class ExcelUtils {
    /**
     * 要求excel版本在2007以上
     *
     * @param fileInputStream 文件信息
     * @param sheetIndex      工作表索引
     * @return 二维list
     */
    public static List<List<Object>> readExcel(FileInputStream fileInputStream, int sheetIndex) throws IOException {
        List<List<Object>> rowsList = new LinkedList<>();
        XSSFWorkbook xwb = new XSSFWorkbook(fileInputStream);
        XSSFSheet sheet = xwb.getSheetAt(sheetIndex);
        XSSFRow row;
        XSSFCell cell;
        for (int i = sheet.getFirstRowNum(); i <= (sheet.getPhysicalNumberOfRows() - 1); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> rowValue = new LinkedList<>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                Object value = null;
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case STRING:
                        value = cell.getStringCellValue();
                        break;
                    case NUMERIC:
                        if ("yyyy\"年\"m\"月\"d\"日\";@".equals(cell.getCellStyle().getDataFormatString())) {
                            Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                            Calendar calendar = Calendar.getInstance();
                            calendar.setTime(date);
                            value = calendar.getTimeInMillis() / 1000;
                        } else {
                            value = cell.getNumericCellValue();
                        }
                        break;
                    case BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    case BLANK:
                        break;
                    default:
                        value = cell.toString();
                }
                if (value != null && !"".equals(value)) {
                    rowValue.add(value);
                }
            }
            if (rowValue.size() != 0) {
                rowsList.add(rowValue);
            }
        }
        return rowsList;
    }

    /**
     * 要求excel版本在2007以上
     *
     * @param file       文件信息
     * @param sheetIndex 工作表索引
     * @return 二维list
     */
    public static List<List<Object>> readExcel(File file, int sheetIndex) throws IOException {
        if (!file.exists()) {
            throw new NoSuchFileException("找不到文件");
        }
        FileInputStream fileInputStream = new FileInputStream(file);
        return readExcel(fileInputStream, sheetIndex);
    }

    /**
     * 导出excel
     *
     * @param excelName 导出的excel路径（需要带.xlsx)
     * @param titleList 大标题
     * @param fieldList 列名列表（与数据中map中键值对应）
     * @param dataList  数据列表
     * @throws Exception
     */
    public static void createExcel(String excelName, String[] titleList,
                                   String[] fieldList, List<Map<String, Object>> dataList)
            throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < titleList.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellType(STRING);
            cell.setCellValue(titleList[i]);
        }
        for (int n = 0; n < dataList.size(); n++) {
            XSSFRow rowValue = sheet.createRow(n + 1);
            Map<String, Object> dataMap = dataList.get(n);
            for (int i = 0; i < fieldList.length; i++) {
                XSSFCell cell = rowValue.createCell(i);
                cell.setCellType(STRING);
                cell.setCellValue((dataMap.get(fieldList[i])).toString());
            }
        }
        FileOutputStream fos = new FileOutputStream(excelName);
        workbook.write(fos);
        fos.flush();
        fos.close();
    }
}

Refer

https://blog.csdn.net/js_sky/article/details/47334833