SpringBoot导出excel文件

一：添加POI依赖

1 2 3 4 5

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

　　

二：创建Excel实体类

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

package com.example.demo.model;   import java.io.Serializable; import java.util.List;   public class ExcelData implements Serializable {       private static final long serialVersionUID = 6133772627258154184L;     /**      * 表头      */     private List<String> titles;       /**      * 数据      */     private List<List<Object>> rows;       /**      * 页签名称      */     private String name;       public List<String> getTitles() {         return titles;     }       public void setTitles(List<String> titles) {         this.titles = titles;     }       public List<List<Object>> getRows() {         return rows;     }       public void setRows(List<List<Object>> rows) {         this.rows = rows;     }       public String getName() {         return name;     }       public void setName(String name) {         this.name = name;     } }

　　

三：创建表格工具类

创建core→utils→ExcelUtils

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

package com.example.demo.core.utils;   import com.example.demo.model.ExcelData; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide;   import javax.servlet.http.HttpServletResponse; import java.awt.Color; import java.io.File; import java.io.FileOutputStream; import java.io.OutputStream; import java.net.URLEncoder; import java.util.List;   public class ExcelUtils {       /**      * 使用浏览器选择路径下载      * @param response      * @param fileName      * @param data      * @throws Exception      */     public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {         // 告诉浏览器用什么软件可以打开此文件         response.setHeader("content-Type", "application/vnd.ms-excel");         // 下载文件的默认名称         response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "utf-8"));         exportExcel(data, response.getOutputStream());     }       public static int generateExcel(ExcelData excelData, String path) throws Exception {         File f = new File(path);         FileOutputStream out = new FileOutputStream(f);         return exportExcel(excelData, out);     }       private static int exportExcel(ExcelData data, OutputStream out) throws Exception {         XSSFWorkbook wb = new XSSFWorkbook();         int rowIndex = 0;         try {             String sheetName = data.getName();             if (null == sheetName) {                 sheetName = "Sheet1";             }             XSSFSheet sheet = wb.createSheet(sheetName);             rowIndex = writeExcel(wb, sheet, data);             wb.write(out);         } catch (Exception e) {             e.printStackTrace();         } finally {             //此处需要关闭 wb 变量             out.close();         }         return rowIndex;     }       /**      * 表不显示字段      * @param wb      * @param sheet      * @param data      * @return      */ //    private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) { //        int rowIndex = 0; //        writeTitlesToExcel(wb, sheet, data.getTitles()); //        rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex); //        autoSizeColumns(sheet, data.getTitles().size() + 1); //        return rowIndex; //    }       /**      * 表显示字段      * @param wb      * @param sheet      * @param data      * @return      */     private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {         int rowIndex = 0;         rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());         rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);         autoSizeColumns(sheet, data.getTitles().size() + 1);         return rowIndex;     }     /**      * 设置表头      *      * @param wb      * @param sheet      * @param titles      * @return      */     private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {         int rowIndex = 0;         int colIndex = 0;         Font titleFont = wb.createFont();         //设置字体         titleFont.setFontName("simsun");         //设置粗体         titleFont.setBoldweight(Short.MAX_VALUE);         //设置字号         titleFont.setFontHeightInPoints((short) 14);         //设置颜色         titleFont.setColor(IndexedColors.BLACK.index);         XSSFCellStyle titleStyle = wb.createCellStyle();         //水平居中         titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);         //垂直居中         titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);         //设置图案颜色         titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));         //设置图案样式         titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);         titleStyle.setFont(titleFont);         setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));         Row titleRow = sheet.createRow(rowIndex);         titleRow.setHeightInPoints(25);         colIndex = 0;         for (String field : titles) {             Cell cell = titleRow.createCell(colIndex);             cell.setCellValue(field);             cell.setCellStyle(titleStyle);             colIndex++;         }         rowIndex++;         return rowIndex;     }       /**      * 设置内容      *      * @param wb      * @param sheet      * @param rows      * @param rowIndex      * @return      */     private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {         int colIndex;         Font dataFont = wb.createFont();         dataFont.setFontName("simsun");         dataFont.setFontHeightInPoints((short) 14);         dataFont.setColor(IndexedColors.BLACK.index);           XSSFCellStyle dataStyle = wb.createCellStyle();         dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);         dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);         dataStyle.setFont(dataFont);         setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));         for (List<Object> rowData : rows) {             Row dataRow = sheet.createRow(rowIndex);             dataRow.setHeightInPoints(25);             colIndex = 0;             for (Object cellData : rowData) {                 Cell cell = dataRow.createCell(colIndex);                 if (cellData != null) {                     cell.setCellValue(cellData.toString());                 } else {                     cell.setCellValue("");                 }                 cell.setCellStyle(dataStyle);                 colIndex++;             }             rowIndex++;         }         return rowIndex;     }       /**      * 自动调整列宽      *      * @param sheet      * @param columnNumber      */     private static void autoSizeColumns(Sheet sheet, int columnNumber) {         for (int i = 0; i < columnNumber; i++) {             int orgWidth = sheet.getColumnWidth(i);             sheet.autoSizeColumn(i, true);             int newWidth = (int) (sheet.getColumnWidth(i) + 100);             if (newWidth > orgWidth) {                 sheet.setColumnWidth(i, newWidth);             } else {                 sheet.setColumnWidth(i, orgWidth);             }         }     }       /**      * 设置边框      *      * @param style      * @param border      * @param color      */     private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {         style.setBorderTop(border);         style.setBorderLeft(border);         style.setBorderRight(border);         style.setBorderBottom(border);         style.setBorderColor(BorderSide.TOP, color);         style.setBorderColor(BorderSide.LEFT, color);         style.setBorderColor(BorderSide.RIGHT, color);         style.setBorderColor(BorderSide.BOTTOM, color);     } }

　　

四：创建ExcelConstant

创建core→constant→ExcelConstant

1 2 3 4 5 6 7 8 9 10 11 12 13 14

package com.example.demo.core.constant;   public class ExcelConstant {       /**      * 生成文件存放路径      */     public static final String FILE_PATH = "C:\\Users\\Administrator\\Desktop\\";       /**      * 表格默认名称      */     public static final String FILE_NAME = "TEST.xls"; }

　　

五：创建ExcelController

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

package com.example.demo.controller;     import com.example.demo.core.constant.ExcelConstant; import com.example.demo.core.ret.RetResponse; import com.example.demo.core.ret.RetResult; import com.example.demo.core.ret.ServiceException; import com.example.demo.core.utils.ExcelUtils; import com.example.demo.model.ExcelData; import com.example.demo.model.UserInfo; import com.example.demo.service.UserInfoService; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; import io.swagger.annotations.Api; import io.swagger.annotations.ApiImplicitParam; import io.swagger.annotations.ApiImplicitParams; import io.swagger.annotations.ApiOperation; import org.springframework.web.bind.annotation.*;   import javax.annotation.Resource; import javax.servlet.http.HttpServletResponse; import java.util.ArrayList; import java.util.List;   @RestController @RequestMapping("excel") public class ExcelController {       @Resource     private UserInfoService userInfoService;       @RequestMapping("/test")     public  RetResult<Integer> test(){         int rowIndex = 0;         List<UserInfo> list = userInfoService.selectAlla(0, 0);         ExcelData data = new ExcelData();         data.setName("hello");         List<String> titles = new ArrayList();         titles.add("ID");         titles.add("userName");         titles.add("password");         data.setTitles(titles);           List<List<Object>> rows = new ArrayList();         for(int i = 0, length = list.size();i<length;i++){             UserInfo userInfo = list.get(i);             List<Object> row = new ArrayList();             row.add(userInfo.getId());             row.add(userInfo.getUserName());             row.add(userInfo.getPassword());             rows.add(row);         }         data.setRows(rows);         try{             rowIndex = ExcelUtils.generateExcel(data, ExcelConstant.FILE_PATH + ExcelConstant.FILE_NAME);         }catch (Exception e){             e.printStackTrace();         }         return RetResponse.makeOKRsp(Integer.valueOf(rowIndex));     }       @RequestMapping("/test2")     public void test2(HttpServletResponse response){         int rowIndex = 0;         List<UserInfo> list = userInfoService.selectAlla(0, 0);         ExcelData data = new ExcelData();         data.setName("hello");         List<String> titles = new ArrayList();         titles.add("ID");         titles.add("userName");         titles.add("password");         data.setTitles(titles);           List<List<Object>> rows = new ArrayList();         for(int i = 0, length = list.size();i<length;i++){             UserInfo userInfo = list.get(i);             List<Object> row = new ArrayList();             row.add(userInfo.getId());             row.add(userInfo.getUserName());             row.add(userInfo.getPassword());             rows.add(row);         }         data.setRows(rows);         try{             ExcelUtils.exportExcel(response,"test2",data);         }catch (Exception e){             e.printStackTrace();         }     } }

　　

七：测试

输入localhost:8080/excel/test