exce工具类

package com.xx.xx.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;


public class ExcelUtils {
    private final static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 创建单个表单
     *
     * @param sheetName 表单名称(左下角的 sheet1)
     * @param list      两层数据包装,第一层是row 第二层是col
     * @return 封装好的 workbook对象
     */
    public static XSSFWorkbook createSingleSheetExcel(String sheetName, List<List<String>> list) {
        //创建表头等
        XSSFWorkbook wb = new XSSFWorkbook();
        //创建行
        XSSFSheet sheet = wb.createSheet(sheetName);
        for (int i = 0; i < list.size(); i++) {
            //创建列
            XSSFRow row = sheet.createRow(i);
            for (int j = 0; j < list.get(i).size(); j++) {
                row.createCell(j).setCellValue(list.get(i).get(j));
            }
        }
        return wb;
    }

    public static List<List<String>> importSingleSheet(File file) throws IOException {
        return importSingleSheet(file.getName(), new FileInputStream(file));
    }

    public static List<List<String>> importSingleSheet(String fileName, InputStream inputStream) throws IOException {
        Workbook wb;
        List<List<String>> data = new ArrayList<>();
        if (ExcelUtils.isExcel2007(fileName)) {
            wb = new XSSFWorkbook(inputStream);
        } else {
            wb = new HSSFWorkbook(inputStream);
        }
        DataFormatter formatter = new DataFormatter();

        Sheet sheet = wb.getSheetAt(0);
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            List<String> oneRow = new ArrayList<>();
            Row row = sheet.getRow(i);
            for (int j = 0; j < row.getLastCellNum(); j++) {
                oneRow.add(formatter.formatCellValue(row.getCell(j)));
            }
            data.add(oneRow);
        }
        try {
            wb.close();
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
        return data;
    }

    public static void main(String... args) throws IOException {
        ExcelUtils.createSingleSheetExcel("表单一", Arrays.asList(Arrays.asList("123", "12344", "阿西吧")));
    }
}

results matching ""

    No results matching ""