Java导出Excel 复杂表头_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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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();
            }
        }
    }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232

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解析不了");
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

我的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 "";
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23