springboot读取excel文件模板写入数据，并下载

今天药忘吃喽~ 2022-02-01 11:45 709阅读 0赞

先定好excel头部内容，样式等，以此为模板，插入表格数据，就不用HSSFCellStyle类设置样式了。  
例如：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3prXzEzMjU1NzI4MDM_size_16_color_FFFFFF_t_70]  
前4行内容是不变的，从第五行开始 从数据库拿出数据 写入表格  
完成后  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3prXzEzMjU1NzI4MDM_size_16_color_FFFFFF_t_70 1]pom.xml，必要两个依赖

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

controller

import com.tyxx.common.enums.HttpStatusEnum;
    import com.tyxx.common.render.makeResult;
    import com.tyxx.model.Jzzhxx;
    import com.tyxx.service.JzzhxxService;
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    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.ss.usermodel.WorkbookFactory;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.core.io.ClassPathResource;
    import org.springframework.core.io.Resource;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.*;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.*;
    import java.net.URLEncoder;
    import java.util.List;
    
    
    @Controller     //controller + ResponseBody
    @CrossOrigin
    @RequestMapping(value = "/download")
    public class DownloadController {
    
        private static final Logger logger = LoggerFactory.getLogger(DownloadController.class);
    
        @Autowired
        private JzzhxxService jzzhxxService;
    
        @RequestMapping(value = "/loadFile/{choose}", produces = {"application/json;charset=UTF-8"})
        @ResponseBody
        public void loadFile(@PathVariable int choose, HttpServletResponse response) throws IOException {
    		Resource resource = new ClassPathResource("templates/reporttemplate.xls");
            boolean isFile = resource.isFile();
            if(!isFile){     //如果不存在返回
                return;
            }
            String path = resource.getFile().getPath();     //获取文件路径
    
            /* 数据写入模板文件中 */
            //更改文件名编码
            String fileName = "基站综合信息.xls";
            String gFileName = URLEncoder.encode(fileName, "UTF-8");
            //如进行下载名为：文件（3）.txt，下载时显示名为：文件+（3）.txt --空格变为了+号
            //解决办法如下
            String dFileName = gFileName.replaceAll("\\+", "%20");
            InputStream in = null;
            Workbook exl = null;
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            try {
                in = new FileInputStream(path);
                exl = WorkbookFactory.create(in);
                Sheet sheet1 = exl.getSheetAt(0);
                int rowNums = sheet1.getLastRowNum();	//模板的行数0开始，返回值比行数小1
                short cellNums = sheet1.getRow(3).getLastCellNum();		//列数
                List<Jzzhxx> lt = jzzhxxService.getAllList();
                for (int i = 0, y = lt.size(); i < y; ++i) {
                    Row row = sheet1.createRow( i + 4 );
                    row.createCell(0).setCellValue(i + 1);  //序号
                    row.createCell(1).setCellValue(lt.get(i).getName().trim());     //站名
                    row.createCell(2).setCellValue(lt.get(i).getYys().trim());      //运营商
                    row.createCell(3).setCellValue(lt.get(i).getJingdu().trim()); //经度
                    row.createCell(4).setCellValue(lt.get(i).getWeidu().trim());  //纬度
                    row.createCell(5).setCellValue(lt.get(i).getGaodu());                //高度
                    row.createCell(6).setCellValue(lt.get(i).getLeibie());                //基站类别
                    row.createCell(7).setCellValue(lt.get(i).getType());                //基站类型
                    row.createCell(8).setCellValue(lt.get(i).getLianxiren());                //区域联系人
                    row.createCell(9).setCellValue(lt.get(i).getFugai());                //覆盖范围
                }
                //激活浏览器弹出窗口
                response.setContentType("application/x-msdownload");
                //浏览器弹出窗口显示的文件名
                response.addHeader("Content-Disposition", "attachment;filename=" + dFileName);
                exl.write(out);
                //in = new ByteArrayInputStream(out.toByteArray());
                response.getOutputStream().write(out.toByteArray());
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (exl != null) {
                        exl.close();
                    }
                    if (out != null) {
                        out.close();
                    }
                    if (in != null) {
                        in.close();
                    }
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
        }       
    }

项目结构  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3prXzEzMjU1NzI4MDM_size_16_color_FFFFFF_t_70 2]前台  
![在这里插入图片描述][20190514180255262.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3prXzEzMjU1NzI4MDM_size_16_color_FFFFFF_t_70]: /images/20220201/7f21e7c91924421895ec75393dd355b2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3prXzEzMjU1NzI4MDM_size_16_color_FFFFFF_t_70 1]: /images/20220201/e2da496c29bf4db091cfff354b233e50.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3prXzEzMjU1NzI4MDM_size_16_color_FFFFFF_t_70 2]: /images/20220201/4a71d1e8f2da4de18aa9b94692696ba4.png
[20190514180255262.png]: /images/20220201/5c826765640c45c6bf0faaa9122b767b.png