XSSFWorkbook导出Excel

package com.example.demo.job.util;

import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class BeanUtils {


    //******************导出*********************
    public static String generateExcel(List<Map<String,Object>> list, String title, String key){

        try {
            //创建工作薄
            XSSFWorkbook workbook = new XSSFWorkbook();
            //创建表单
            XSSFSheet sheet = genSheet(workbook,"testExcel");
            //创建表单样式
            XSSFCellStyle titleStyle = genTitleStyle(workbook);//创建标题样式
            XSSFCellStyle contextStyle = genContextStyle(workbook);//创建文本样式

            //创建Excel
            genExcel(sheet,titleStyle,contextStyle,list,title,key);

            //将生成的Excel文件保存到本地
            FileOutputStream out = new FileOutputStream(new File("D://"+new Date().getTime()+"test.xlsx"));
            //将工作薄写入文件输出流中
            workbook.write(out);
            //文本文件输出流，释放资源
            out.close();
            return "200";
        } catch (IOException e) {
            e.printStackTrace();
            return "501";
        }
    }

    public static void genExcel(XSSFSheet sheet,XSSFCellStyle titleStyle,XSSFCellStyle contextStyle
            ,List<Map<String,Object>> list,String title,String key) {

        //根据Excel列名长度，指定列名宽度  Excel总共10列
        for (int i = 0; i < list.size(); i++) {
            if (i == 0) {
                sheet.setColumnWidth(i, 1500);
            } else if (i == 2 || i == 3 || i == 4) {
                sheet.setColumnWidth(i, 4000);
            } else if (i == 9) {
                sheet.setColumnWidth(i, 3000);
            } else {
                sheet.setColumnWidth(i, 2000);
            }
        }

        //设置标题位置
        sheet.addMergedRegion(new CellRangeAddress(
                0, //first row
                0, //last row
                0, //first column
                list.get(0).size()-1 //last column(标题占几列的位置)
        ));

        XSSFRow row = sheet.createRow(0);//创建第一行，为标题，index从0开始
        XSSFCell cell;
        cell = row.createCell(0);//创建一列
        cell.setCellValue("方案详情");//标题
        cell.setCellStyle(titleStyle);//设置标题样式
        String[] titles = title.split(",");
        row = sheet.createRow(1);//创建第二行(列名展示)
        for(int i=0;i<titles.length;i++){
            cell = row.createCell(i);//创建第二行第一列
            cell.setCellValue(titles[i]);//第二行第一列内容
            cell.setCellStyle(contextStyle);//设置样式
        }
        String[] keys = key.split(",");
        //从数据库取数据填充到Excel，这步省略，添加模拟数据
        for(int i = 0 ; i<list.size();i++){//
            row=sheet.createRow(i+2);//创建第三行,上边已经创建了两行
            for(int j=0;j<titles.length;j++){
                cell = row.createCell(j);//创建第二行第一列
                cell.setCellValue(list.get(i).get(keys[j]).toString());//第二行第一列内容
                cell.setCellStyle(contextStyle);//设置样式
            }
        }
    }

    //设置表单，并生成表单
    public static XSSFSheet genSheet(XSSFWorkbook workbook, String sheetName){
        //生成表单
        XSSFSheet sheet = workbook.createSheet(sheetName);
        //设置表单文本居中
        sheet.setHorizontallyCenter(true);
        sheet.setFitToPage(false);
        //打印时在底部右边显示文本页信息
        Footer footer = sheet.getFooter();
        footer.setRight( "Page " + HeaderFooter.numPages()+ " Of "+ HeaderFooter.page());
        //打印时在头部右边显示Excel创建日期信息
        Header header = sheet.getHeader();
        header.setRight("Create Date " + HeaderFooter.date() + " " + HeaderFooter.time());
        //设置打印方式
        XSSFPrintSetup ps = sheet.getPrintSetup();
        ps.setLandscape(true); // true：横向打印，false：竖向打印 ，因为列数较多，推荐在打印时横向打印
        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //打印尺寸大小设置为A4纸大小
        return sheet;
    }

    //创建文本样式
    public static XSSFCellStyle genContextStyle(XSSFWorkbook workbook){
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);//文本水平居中显示
        style.setVerticalAlignment(VerticalAlignment.CENTER);//文本竖直居中显示
        style.setWrapText(true);//文本自动换行

        //生成Excel表单，需要给文本添加边框样式和颜色
        /*
             CellStyle.BORDER_DOUBLE      双边线
             CellStyle.BORDER_THIN        细边线
             CellStyle.BORDER_MEDIUM      中等边线
             CellStyle.BORDER_DASHED      虚线边线
             CellStyle.BORDER_HAIR        小圆点虚线边线
             CellStyle.BORDER_THICK       粗边线
         */
        style.setBorderBottom(BorderStyle.THIN);//设置文本边框
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //set border color
        style.setTopBorderColor(new XSSFColor(java.awt.Color.BLACK));//设置文本边框颜色
        style.setBottomBorderColor(new XSSFColor(java.awt.Color.BLACK));
        style.setLeftBorderColor(new XSSFColor(java.awt.Color.BLACK));
        style.setRightBorderColor(new XSSFColor(java.awt.Color.BLACK));

        return style;
    }

    //生成标题样式
    public static XSSFCellStyle genTitleStyle(XSSFWorkbook workbook){

        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);

        //标题居中，没有边框，所以这里没有设置边框，设置标题文字样式
        XSSFFont titleFont = workbook.createFont();
        titleFont.setBold(true);//加粗
        titleFont.setFontHeight((short)15);//文字尺寸
        titleFont.setFontHeightInPoints((short)15);
        style.setFont(titleFont);

        return style;
    }
    //*********************************导出结束***********************************



}

//调用

package com.example.demo.job.controller;

import com.alibaba.fastjson.JSONObject;
import com.example.demo.entity.ReceivePageParams;
import com.example.demo.job.service.PlanService;
import com.example.demo.job.util.BeanUtils;
import com.example.demo.mapper.CellinfoMapper;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletResponse;
import javax.swing.filechooser.FileSystemView;
import java.awt.*;
import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 
 */

@Controller
@RequestMapping("plan")
public class PlanController {





    /**
     * 导出
     */
    @RequestMapping(value = "exportPlanExcel", method = RequestMethod.GET)
    @ResponseBody
    public String exportPlanExcel(){
        Map<String,Object> map = new HashMap<>();
        //准备数据
        List<Map<String,Object>> list=cellinfoMapper.selectDataByPage();//这个是自己数据库查询回来的数据.根据自己情况查询即可
        String title = "城市,数量";    //
        String key = "city,cellNum";  //
        String code = BeanUtils.generateExcel(list,title,key);
        if("200".equals(code)){
            map.put("code",200);
            map.put("msg","导出成功");
            return JSONObject.toJSONString(map);
        }else{
            map.put("msg","系统错误,请联系管理员");
            map.put("code",501);
            return JSONObject.toJSONString(map);
        }
    }





}

//所需jar

<!-- 导出需要用的两个包-->
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>