Java导出Excel 复杂表头

导出表格

导出表格的方式在我的理解有两种
一种是直接用代码控制表头==== 简单的表头
一种是直接使用模板直接添加数据就可以=== 复杂的表头

依赖

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

<!--下面是我直接测试表格,因为要在浏览器导出，直接创建Springboot 简单访问-->
 <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.3.4.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>4.0.1</version>
        </dependency>

package com.example.demo.test;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
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 javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @program: Ecology1
 * @description: this is a class
 * @author: Mr.zeng
 * @create: 2021-03-12 10:02
 **/

public class TestTemplateExcel {
    /*
     *
     */
    private static final long serialVersionUID = 1L;

    /**
     * 生成excel并下载
     */
    public void exportExcel(HttpServletResponse response, HttpServletRequest request) {
        String userName = request.getParameter("userName");
        File newFile = createNewFile();
        // File newFile = new File("d:/ss.xls");
        // 新文件写入数据，并下载*****************************************************
        InputStream is = null;
        Workbook workbook = null;
        Sheet sheet = null;
        try {
            is = new FileInputStream(newFile);// 将excel文件转为输入流

            workbook=XlsImpUtil.create(is);
//            workbook = new XSSFWorkbook(is);// 创建个workbook，
            // 获取第一个sheet
            sheet = workbook.getSheetAt(0);
        } catch (Exception e1) {
            e1.printStackTrace();
        }

        if (sheet != null) {
            try {
                // 写数据
                FileOutputStream fos = new FileOutputStream(newFile);
                Row row = sheet.getRow(3);
                if (row == null) {
                    row = sheet.createRow(3);
                }
                Cell cell = row.getCell(0);
                if (cell == null) {
                    cell = row.createCell(0);
                }

                // TODO 定义一个list集合假数据
                List<Map<String, Object>> lst = new ArrayList();
                Map<String, Object> map1 = new HashMap<String, Object>();
                // 只能添加11个
                for (int i = 0; i < 11; i++) {
                    map1.put("id" + i, i);
                    lst.add(map1);
                }
                row=sheet.getRow(1);
                String sj="填报日期：2020年03月24日 15:52 星期四";
                cell=row.getCell(0);
                cell.setCellValue(sj);
                for (int m = 0; m < lst.size(); m++) {
                    Map<String, Object> map = lst.get(m);
                    row=sheet.getRow(m+3);
//                    row = sheet.createRow((int) m + 3);
                    for (int i = 0; i < 5; i++) {
                        String value = map.get("id" + m) + "";
                        if (value.equals("null")) {
                            value = "0";
                        }
                        if(row.getRowNum()>= 7 && row.getRowNum()<=12){
                            cell=row.getCell(i+2);
                        }else {
                            cell=row.getCell(i+2);
                        }

                    /*    cell = row.createCell(i);*/
                        cell.setCellValue(value);
                    }

                }
                // 填报人
                row=sheet.getRow(14);
                String tbr="\t\t填报人："+userName;
                cell=row.getCell(0);
                cell.setCellValue(tbr);

                workbook.write(fos);
                fos.flush();
                fos.close();
                // 下载
                InputStream fis = new BufferedInputStream(new FileInputStream(
                        newFile));
//                    HttpServletResponse response = /*ServletActionContext.getResponse();*/null;
                byte[] buffer = new byte[fis.available()];
                fis.read(buffer);
                fis.close();
                response.reset();
                response.setContentType("text/html;charset=UTF-8");
                OutputStream toClient = new BufferedOutputStream(
                        response.getOutputStream());
                response.setContentType("application/x-msdownload");
                String newName = URLEncoder.encode(
                        "活动报表" + System.currentTimeMillis() + ".xlsx",
                        "UTF-8");
                response.addHeader("Content-Disposition",
                        "attachment;filename=\"" + newName + "\"");
                response.addHeader("Content-Length", "" + newFile.length());
                toClient.write(buffer);
                toClient.flush();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (null != is) {
                        is.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        // 删除创建的新文件
         this.deleteFile(newFile);
    }
    /**
     * 复制文件
     *
     * @param s
     *            源文件
     * @param t
     *            复制到的新文件
     */
    public void fileChannelCopy(File s, File t) {
        try {
            InputStream in = null;
            OutputStream out = null;
            try {
                in = new BufferedInputStream(new FileInputStream(s), 1024);
                out = new BufferedOutputStream(new FileOutputStream(t), 1024);
                byte[] buffer = new byte[1024];
                int len;
                while ((len = in.read(buffer)) != -1) {
                    out.write(buffer, 0, len);
                }
            } finally {
                if (null != in) {
                    in.close();
                }
                if (null != out) {
                    out.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private String getSispPath() {
        String classPaths = "C:\\Users\\zp\\IdeaProjects\\Ecology1\\src\\main\\resources";
//        String classPaths="/usr/weaver/ecology/classbean/com/api/zp";
        String[] aa = classPaths.split("/");
        String sispPath = "";
        for (int i = 1; i < aa.length - 2; i++) {
            sispPath += aa[i] + "/";
        }
        return sispPath;
    }
    /**
     * 读取excel模板，并复制到新文件中供写入和下载
     *
     * @return
     */
    public File createNewFile() {
        // 读取模板，并赋值到新文件************************************************************
       final String name="C:\\Users\\zp\\IdeaProjects\\demo\\活动报表";
//       String name="/usr/weaver/ecology/classbean/com/api/zp/活动报表";
        // 文件模板路径
        String path =  name+".xls";
        File file = new File(path);
        // 保存文件的路径
        String realPath =  "";
        // 新的文件名
        String newFileName = name + System.currentTimeMillis() + ".xls";
        // 判断路径是否存在
        File dir = new File(realPath);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        // 写入到新的excel
        File newFile = new File(newFileName);
        try {
            newFile.createNewFile();
            // 复制模板到新文件
            fileChannelCopy(file, newFile);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return newFile;
    }
    /**
     * 下载成功后删除
     *
     * @param files
     */
    private void deleteFile(File... files) {
        for (File file : files) {
            if (file.exists()) {
                file.delete();
            }
        }
    }
    }

import com.fasterxml.jackson.databind.exc.InvalidFormatException;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;

/**
 * @program: Ecology
 * @description: this is a class
 * @author: Mr.zeng
 * @create: 2021-03-12 11:34
 **/

public class XlsImpUtil {
    public static Workbook create(InputStream inp) throws IOException, InvalidFormatException, org.apache.poi.openxml4j.exceptions.InvalidFormatException {
        if (!inp.markSupported()) {
            inp = new PushbackInputStream(inp, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(inp)) {
            return new HSSFWorkbook(inp);
        }
        if (POIXMLDocument.hasOOXMLHeader(inp)) {
            return new XSSFWorkbook(OPCPackage.open(inp));
        }
        throw new IllegalArgumentException("你的excel版本目前poi解析不了");
    }
}

我的Controller

import com.example.demo.test.TestTemplateExcel;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * @program: Ecology1
 * @description: this is a class
 * @author: Mr.zeng
 * @create: 2021-03-12 10:52
 **/
@Controller
@RequestMapping("/excel")
public class ExcelMainController {

    @RequestMapping("/port")
    public String excelport(HttpServletResponse response, HttpServletRequest request){
         new TestTemplateExcel().exportExcel(response,request);
         return "";
    }
}