poi 导出excel工具类包含导出内容为List＜Map＜String,Object＞＞,List＜List＜Object＞＞

导入jar

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

工具类ExportExcel

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
//这里所需要的参数是  表格文件名  表格的sheet的名称   与表格的头部内容 和数据集合，以及response
/**
 * Created by Bob on 2021/4/7.
 */
public class ExportExcel
{
    public static boolean exportExcel(String excelName, String title, String[] headers, List<Map> dataset, String pattern, HttpServletResponse response) throws IOException {
//        Long milliSecond = LocalDateTime.now().toInstant(ZoneOffset.of("+8")).toEpochMilli();
        long milliSecond = System.currentTimeMillis();
        String fileName = excelName + "-" + milliSecond + ".xls";
        ServletOutputStream outputStream = response.getOutputStream();
        response.setContentType("application/x-xls;charset=UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);

        boolean flag = false;
        Workbook workbook = null;
        if (fileName.endsWith("xlsx"))
        {
            workbook = new XSSFWorkbook();
        } else if (fileName.endsWith("xls"))
        {
            workbook = new HSSFWorkbook();
        } else
        {
            try
            {
                throw new Exception("invalid file name, should be xls or xlsx");
            } catch (Exception e)
            {
                e.printStackTrace();
            }

        }

        Sheet sheet = workbook.createSheet(title);
        CellStyle style = workbook.createCellStyle();

        // 列名
        Row row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++)
        {
            Cell cell = row.createCell(i);
            sheet.setColumnWidth(i, 5000);
//            style.setAlignment(CellStyle.ALIGN_CENTER);
            cell.setCellValue(headers[i]);
        }

        Iterator<Map> it = dataset.iterator();
        int index = 0;
        while (it.hasNext())
        {
            index++;
            row = sheet.createRow(index);

            Map map = it.next();//当前这个list集合中的map
//            logger.info(map.toString());

            Set<String> mapKey = (Set<String>)map.keySet();//获取当前这个map的键的set集合
//            logger.info(mapKey.toString());
            Iterator<String> iterator = mapKey.iterator();
//            logger.info(iterator.toString());
            int num  = 0;
            while(iterator.hasNext()){
                Cell cell = row.createCell(num);
                num++;
                String key = iterator.next();//这个iterator.next就是这个map中的key
                //为保证map有序用linkedhashmap
//                LinkedHashMap<String, Integer> map = new LinkedHashMap<>();
//                logger.info(key);
                Object obj = map.get(key);
                if (obj instanceof Date)
                {
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    cell.setCellValue(sdf.format(obj));
                } else if (obj instanceof Integer)
                {
                    cell.setCellValue((Integer) obj);
                } else if (obj instanceof Double)
                {
                    cell.setCellValue((Double) obj);
                } else
                {
                    cell.setCellValue((String) obj);
                }
            }
        }
        FileOutputStream fos;
        try
        {
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            flag = true;
        } catch (FileNotFoundException e)
        {
//            logger.info("文件不存在");
            flag = false;
            e.printStackTrace();
        } catch (IOException e)
        {
//            logger.info("文件写入错误");
            flag = false;
            e.printStackTrace();

        }
        return flag;
    }


    /**
     * 改造后的方法
     *
     */


    public static boolean exportMap(String excelName, String title, String[] headers, List<LinkedHashMap<String,Object>> dataset, String pattern, HttpServletResponse response) throws IOException {
//        Long milliSecond = LocalDateTime.now().toInstant(ZoneOffset.of("+8")).toEpochMilli();
        long milliSecond = System.currentTimeMillis();
        String fileName = excelName + "-" + milliSecond + ".xls";
        ServletOutputStream outputStream = response.getOutputStream();
        response.setContentType("application/x-xls;charset=UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        boolean flag = false;
        Workbook workbook = null;
        if (fileName.endsWith("xlsx"))
        {
            workbook = new XSSFWorkbook();
        } else if (fileName.endsWith("xls"))
        {
            workbook = new HSSFWorkbook();
        } else
        {
            try
            {
                throw new Exception("invalid file name, should be xls or xlsx");
            } catch (Exception e)
            {
                e.printStackTrace();
            }

        }

        Sheet sheet = workbook.createSheet(title);
        CellStyle style = workbook.createCellStyle();

        // 列名
        Row row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++)
        {
            Cell cell = row.createCell(i);
            sheet.setColumnWidth(i, 5000);
//            style.setAlignment(CellStyle.ALIGN_CENTER);
            cell.setCellValue(headers[i]);
        }

//        Iterator<Map> it = dataset.iterator();quchu
        Iterator<LinkedHashMap<String, Object>> it = dataset.iterator();

        int index = 0;
        int judgecount = 0;
        while (it.hasNext())
        {
            index++;
            judgecount++;
            row = sheet.createRow(index);
            LinkedHashMap<String, Object> linkedHashMap = it.next();
            Set<String> linkkey = linkedHashMap.keySet();

            Iterator<String> keyiterator = linkkey.iterator();//每一行key的集合
            int a = 0;
            Cell cell1 = row .createCell(a);
            a++;
            if (judgecount == 1){
                cell1.setCellValue(index);//新增添加序号
            }
            while (keyiterator.hasNext()){
                Cell cell = row.createCell(a);
                a++;
                if (judgecount == 1){
                    judgecount--;
                }

                String link_key = keyiterator.next();
                Object obj = linkedHashMap.get(link_key);
                if (obj instanceof Date)
                {
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    cell.setCellValue(sdf.format(obj));
                } else if (obj instanceof Integer)
                {
                    cell.setCellValue((Integer) obj);
                } else if (obj instanceof Double)
                {
                    cell.setCellValue((Double) obj);
                } else if (obj instanceof BigDecimal){
                    int i = ((BigDecimal) obj).intValue();
                    cell.setCellValue(i);
                } else
                {
                    cell.setCellValue((String) obj);
                }

            }
        }
        FileOutputStream fos;
        try
        {
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            flag = true;
        } catch (FileNotFoundException e)
        {
//            logger.info("文件不存在");
            flag = false;
            e.printStackTrace();
        } catch (IOException e)
        {
//            logger.info("文件写入错误");
            flag = false;
            e.printStackTrace();

        }
        return flag;
    }
}