Java poi的excel操作

Java poi的excel 工具

一、加入依赖

  <!-- svn -->
    <dependency>
      <groupId>org.tmatesoft.svnkit</groupId>
      <artifactId>svnkit</artifactId>
      <version>1.9.3</version>
    </dependency>

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

    <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.2.68</version>
    </dependency>

二、导入导出excel工具类

package com.mmall.exceltest;

import com.alibaba.fastjson.JSON;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.nio.charset.StandardCharsets;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtil {
   
    private static final String APPLICATION_HEADER = "application/vnd.ms-excel;charset=UTF-8";

    private static final String CONTENT_DISPOSITION = "Content-disposition";

    private static final String SUFFIX = ".xlsx";

    private static final String FILE_NAME_EXCHANGE_ORDER = "attachment;filename=";

    private static final String STRING_CONSTANT = "String";

    private static final String INTEGER_CONSTANT = "Integer";

    private static final String INT_CONSTANT = "int";

    private static final String LONG_CONSTANT = "Long";

    private static final String LONG_CONSTANT_SMALL = "long";

    private static final String DOUBLE_CONSTANT = "Double";

    private static final String DOUBLE_CONSTANT_SMALL = "double";

    private static final String DATE_CONSTANT = "Date";

    private static final String LIST_CONSTANT = "List";

    private static final String COLLECTION_CONSTANT = "Collection";

    private static final String SET_CONSTANT = "HashSet";

    private ExcelUtil() {
   

    }

    public static <T> void export(File exportFile,
                                  final String[] rowName,
                                  final List<T> data,
                                  final String sheetName) {
   
        BufferedOutputStream bos = null;
        try {
   
            bos = getBufferedOutputStream(exportFile);
            doExport(rowName, sheetName, data, bos);
        } catch (final Exception e) {
   
//            log.error("Export Excel error : {}", e.getMessage());
        } finally {
   
            try {
   
                if (bos != null) {
   
                    bos.close();
                }
            } catch (IOException e) {
   
//                log.error("Export Excel error : {}", e.getMessage());
            }
        }
    }

    public static <T> void export(final String filenNamePrefix,
                                  final String[] rowName,
                                  final List<T> data,
                                  final String sheetName,
                                  final HttpServletResponse response) {
   
        BufferedOutputStream bos = null;
        try {
   
            final SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            final String fileName = filenNamePrefix + sdf.format(new Date()) + String.format("%04d", new Random().nextInt(10000)) + SUFFIX;
            bos = getBufferedOutputStream(fileName, response);
            doExport(rowName, sheetName, data, bos);
        } catch (final Exception e)