devbox
羊村懒王
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

spring boot导出数据到Excel表格_"@excel(name = \"customer_phone\")"

作者：羊村懒王 | 2024-05-05 01:44:14

赞

踩

"@excel(name = \"customer_phone\")"

所需依赖：

  1. <!-- Excel导入 -->
  2. 		<dependency>
  3. 			<groupId>org.apache.poi</groupId>
  4. 			<artifactId>poi</artifactId>
  5. 			<version>3.6</version>
  6. 		</dependency>
  7.  
  8. 		<dependency>
  9. 			<groupId>org.apache.poi</groupId>
  10. 			<artifactId>poi-ooxml</artifactId>
  11. 			<version>3.6</version>
  12. 		</dependency>
  13.         <dependency>
  14.             <groupId>org.jetbrains</groupId>
  15.             <artifactId>annotations</artifactId>
  16.             <version>RELEASE</version>
  17.             <scope>compile</scope>
  18.         </dependency>

实体类：CustomerInfo

  1. package com.nz.entity;
  2.  
  3. import java.util.Date;
  4.  
  5. /**
  6.  * @Author xxs
  7.  * @Date 2020/4/16 17:29
  8.  */
  9. public class CustomerInfo {
  10.     private Integer id;
  11.     private String customerName;
  12.     private String customerPhone;
  13.     private String text;
  14.     private String type;
  15.     private Date addTime;
  16.  
  17.     public Integer getId() {
  18.         return id;
  19.     }
  20.  
  21.     public void setId(Integer id) {
  22.         this.id = id;
  23.     }
  24.  
  25.     public String getCustomerName() {
  26.         return customerName;
  27.     }
  28.  
  29.     public void setCustomerName(String customerName) {
  30.         this.customerName = customerName;
  31.     }
  32.  
  33.     public String getCustomerPhone() {
  34.         return customerPhone;
  35.     }
  36.  
  37.     public void setCustomerPhone(String customerPhone) {
  38.         this.customerPhone = customerPhone;
  39.     }
  40.  
  41.     public String getText() {
  42.         return text;
  43.     }
  44.  
  45.     public void setText(String text) {
  46.         this.text = text;
  47.     }
  48.  
  49.     public String getType() {
  50.         return type;
  51.     }
  52.  
  53.     public void setType(String type) {
  54.         this.type = type;
  55.     }
  56.  
  57.     public Date getAddTime() {
  58.         return addTime;
  59.     }
  60.  
  61.     public void setAddTime(Date addTime) {
  62.         this.addTime = addTime;
  63.     }
  64. }
导出表格所要显示的信息实体：downloadProfitStatementExcel
  1. package com.nz.entity;
  2.  
  3. import java.text.SimpleDateFormat;
  4. import java.util.Date;
  5.  
  6. /**
  7.  * @Author xxs
  8.  * @Date 2020/4/17 14:58
  9.  */
  10. public class downloadProfitStatementExcel {
  11.     private String customerName;
  12.     private String customerPhone;
  13.     private String text;
  14.     private String addTime;
  15.  
  16.     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  17.  
  18.     public downloadProfitStatementExcel(String customerName, String customerPhone,
  19.                                         String text,String addTime) {
  20.         super();
  21.         this.customerName = customerName;
  22.         this.customerPhone = customerPhone;
  23.         this.text = text;
  24.         this.addTime = addTime;
  25.     }
  26.     public static downloadProfitStatementExcel getInit() {
  27.         return new downloadProfitStatementExcel("用户名", "手机号", "信息","提交时间");
  28.     }
  29.  
  30.     public downloadProfitStatementExcel() {
  31.         super();
  32.     }
  33.     public String getCustomerName() {
  34.         return customerName;
  35.     }
  36.     public void setCustomerName(String customerName) {
  37.         this.customerName = customerName;
  38.     }
  39.     public String getCustomerPhone() {
  40.         return customerPhone;
  41.     }
  42.     public void setCustomerPhone(String customerPhone) {
  43.         this.customerPhone = customerPhone;
  44.     }
  45.     public String getText() {
  46.         return text;
  47.     }
  48.     public void setText(String text) {
  49.         this.text = text;
  50.     }
  51.     public String getAddTime() {
  52.         return addTime;
  53.     }
  54.     public void setAddTime(Date addTime) {
  55.         this.addTime = sdf.format(addTime);
  56.     }
  57.  
  58. }
CustomerInfoService接口：
  1. package com.nz.service;
  2.  
  3. import com.nz.entity.CustomerInfo;
  4.  
  5. import java.util.List;
  6.  
  7. /**
  8.  * @Author xxs
  9.  * @Date 2020/4/17 10:36
  10.  */
  11. public interface CustomerInfoService {
  12.     public List<CustomerInfo> selectAll();
  13.     public List<CustomerInfo> selectAllByType(String type);
  14.     int insert(CustomerInfo customerInfo);
  15. }
CustomerInfoServiceImpl实现类：
  1. package com.nz.service.Impl;
  2.  
  3. import com.nz.dao.CustomerInfoDao;
  4. import com.nz.entity.CustomerInfo;
  5. import com.nz.service.CustomerInfoService;
  6. import org.springframework.stereotype.Service;
  7.  
  8. import javax.annotation.Resource;
  9. import java.util.List;
  10.  
  11. /**
  12.  * @Author xxs
  13.  * @Date 2020/4/17 10:39
  14.  */
  15. @Service
  16. public class CustomerInfoServiceImpl implements CustomerInfoService {
  17.  
  18.     @Resource
  19.     private CustomerInfoDao customerInfoDao;
  20.  
  21.     @Override
  22.     public List<CustomerInfo> selectAll() {
  23.         return customerInfoDao.selectAll();
  24.     }
  25.  
  26.     @Override
  27.     public List<CustomerInfo> selectAllByType(String type) {
  28.         return customerInfoDao.selectAllByType(type);
  29.     }
  30.  
  31.     @Override
  32.     public int insert(CustomerInfo customerInfo) {
  33.         return customerInfoDao.insert(customerInfo);
  34.     }
  35. }
CustomerInfoDao接口：
  1. package com.nz.dao;
  2.  
  3. import com.nz.entity.CustomerInfo;
  4. import org.apache.ibatis.annotations.*;
  5.  
  6. import java.util.List;
  7.  
  8. /**
  9.  * @Author xxs
  10.  * @Date 2020/4/17 10:40
  11.  */
  12. public interface CustomerInfoDao {
  13.  
  14.     @Select("select * from customer_info")
  15.     @Results(id="resultMap", value={
  16.             @Result(column="id", property="id", id=true),
  17.             @Result(column="customer_name", property="customerName"),
  18.             @Result(column="customer_phone", property="customerPhone"),
  19.             @Result(column="text", property="text"),
  20.             @Result(column="type", property="type"),
  21.             @Result(column="addtime", property="addTime")
  22.     })
  23.     public List<CustomerInfo> selectAll();
  24.  
  25.     @Select("select * from customer_info where type =#{type}")
  26.     @ResultMap(value={"resultMap"})
  27.     public List<CustomerInfo> selectAllByType(String type);
  28.  
  29.     @Insert("insert INTO  customer_info (customer_name,customer_phone,text,type,addtime) values(#{customerName},#{customerPhone},#{text},#{type},#{addTime})")
  30.     int insert(CustomerInfo customerInfo);
  31.  
  32. }

controller层导出方法：

  1. package com.nz.controller;
  2.  
  3. import com.nz.entity.CustomerInfo;
  4. import com.nz.entity.downloadProfitStatementExcel;
  5. import com.nz.response.ResponseMess;
  6. import com.nz.service.CustomerInfoService;
  7. import com.nz.util.ExcelUtil;
  8. import org.springframework.beans.BeanUtils;
  9. import org.springframework.web.bind.annotation.RequestMapping;
  10. import org.springframework.web.bind.annotation.ResponseBody;
  11. import org.springframework.web.bind.annotation.RestController;
  12.  
  13. import javax.annotation.Resource;
  14. import javax.servlet.http.HttpServletResponse;
  15. import java.io.IOException;
  16. import java.util.ArrayList;
  17. import java.util.Date;
  18. import java.util.List;
  19.  
  20. /**
  21.  * @Author xxs
  22.  * @Date 2020/4/17 12:36
  23.  */
  24. @RestController
  25. @ResponseBody
  26. @RequestMapping("/customerInfo")
  27. public class CustomerInfoController {
  28.  
  29.     @Resource
  30.     private CustomerInfoService customerInfoService;
  31.  
  32.    
  33.     /**
  34.      * 导出信息表excel
  35.      * @param type 0-，1-
  36.      * @param response
  37.      * @returns
  38.      */
  39.     @RequestMapping("/downloadCustomerInfo")
  40.     @ResponseBody
  41.     public ResponseMess downloadQueryProfitStatementEXCEL(String type, HttpServletResponse response) {
  42.         if(type.equals("")||type==null) {
  43.             return new ResponseMess("请求参数异常");
  44.         }
  45.  
  46.         List<CustomerInfo> qs = customerInfoService.selectAllByType(type);
  47.         List<downloadProfitStatementExcel> list = new ArrayList<>(qs.size()+1);
  48.         list.add(downloadProfitStatementExcel.getInit());
  49.         for(CustomerInfo q:qs) {
  50.             downloadProfitStatementExcel d = new downloadProfitStatementExcel();
  51.             try {
  52.                 BeanUtils.copyProperties(q, d);
  53.             } catch (Exception e) {
  54.                 e.printStackTrace();
  55.             }
  56.             list.add(d);
  57.         }
  58.         ExcelUtil e = new ExcelUtil();
  59.  
  60.         //设置文件输出类型
  61.         response.setHeader("Content-disposition", "attachment; filename="
  62.                 + new Date().getTime() + ".xls");
  63.  
  64.         String s = "";
  65.         if(type.equals("0")){
  66.             s = "1类型提交资料";
  67.         }else{
  68.             s = "2类型提交资料";
  69.         }
  70.         String[] strs = new String[] {s};
  71.         try {
  72.             e.exportDataToExcel(list, strs, s, response.getOutputStream());
  73.         } catch (IOException e1) {
  74.             e1.printStackTrace();
  75.             return new ResponseMess("失败");
  76.         }
  77.         return new ResponseMess(1,"成功");
  78.     }
  79.  
  80.  
  81.  
  82. }

ExcelUtil 表格工具类：

  1. package com.nz.util;
  2.  
  3. import java.io.IOException;
  4. import java.io.InputStream;
  5. import java.io.OutputStream;
  6. import java.lang.reflect.Field;
  7. import java.lang.reflect.Method;
  8. import java.util.ArrayList;
  9. import java.util.List;
  10.  
  11. import javax.servlet.http.HttpServletRequest;
  12.  
  13. import org.apache.poi.hssf.usermodel.HSSFCell;
  14. import org.apache.poi.hssf.usermodel.HSSFCellStyle;
  15. import org.apache.poi.hssf.usermodel.HSSFFont;
  16. import org.apache.poi.hssf.usermodel.HSSFRichTextString;
  17. import org.apache.poi.hssf.usermodel.HSSFRow;
  18. import org.apache.poi.hssf.usermodel.HSSFSheet;
  19. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  20. import org.apache.poi.hssf.util.HSSFColor;
  21. import org.apache.poi.ss.usermodel.Cell;
  22. import org.apache.poi.ss.usermodel.Row;
  23. import org.apache.poi.ss.usermodel.Sheet;
  24. import org.apache.poi.ss.usermodel.Workbook;
  25. import org.apache.poi.ss.util.CellRangeAddress;
  26. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  27.  
  28.  
  29.  
  30.  
  31. /** 
  32. * @ClassName: ExcelUtil 
  33. * @Description: Excel导入导出工具类
  34. */
  35. public class ExcelUtil {
  36.     
  37.     /** 
  38.     * @Title: createWorkbook 
  39.     * @Description: 判断excel文件后缀名，生成不同的workbook 
  40.     * @param @param is
  41.     * @param @param excelFileName
  42.     * @param @return
  43.     * @param @throws IOException
  44.     * @return Workbook
  45.     * @throws 
  46.     */
  47.     public Workbook createWorkbook(InputStream is,String excelFileName) throws IOException{
  48.         if (excelFileName.endsWith(".xls")) {
  49.             return new HSSFWorkbook(is);
  50.         }else if (excelFileName.endsWith(".xlsx")) {
  51.             return new XSSFWorkbook(is);
  52.         }
  53.         return null;
  54.     }
  55.  
  56.     /** 
  57.     * @Title: getSheet 
  58.     * @Description: 根据sheet索引号获取对应的sheet
  59.     * @param @param workbook
  60.     * @param @param sheetIndex
  61.     * @param @return
  62.     * @return Sheet
  63.     * @throws 
  64.     */
  65.     public Sheet getSheet(Workbook workbook,int sheetIndex){
  66.         return workbook.getSheetAt(0);        
  67.     }
  68.     
  69.  
  70.     /** 
  71.     * @Title: isHasValues 
  72.     * @Description: 判断一个对象所有属性是否有值，如果一个属性有值(分空)，则返回true
  73.     * @param @param object
  74.     * @param @return
  75.     * @return boolean
  76.     * @throws 
  77.     */
  78.     public boolean isHasValues(Object object){
  79.         Field[] fields = object.getClass().getDeclaredFields();
  80.         boolean flag = false;
  81.         for (int i = 0; i < fields.length; i++) {
  82.             String fieldName = fields[i].getName();
  83.             String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
  84.             Method getMethod;
  85.             try {
  86.                 getMethod = object.getClass().getMethod(methodName);
  87.                 Object obj = getMethod.invoke(object);
  88.                 if (null != obj || !"".equals(obj)) {
  89.                     flag = true;
  90.                     break;
  91.                 }
  92.             } catch (Exception e) {
  93.  
  94.             }
  95.             
  96.         }
  97.         return flag;
  98.         
  99.     }
  100.     
  101.     public <T> void exportDataToExcel(List<T> list,String[] headers,String title,OutputStream os){
  102.         HSSFWorkbook workbook = new HSSFWorkbook();
  103.         //生成一个表格
  104.         HSSFSheet sheet = workbook.createSheet(title);
  105.         //设置表格默认列宽15个字节
  106.         sheet.setDefaultColumnWidth(15);
  107.         //生成一个样式
  108.         HSSFCellStyle style = this.getCellStyle(workbook);
  109.         //生成一个字体
  110.         HSSFFont font = this.getFont(workbook);
  111.         //把字体应用到当前样式
  112.         style.setFont(font);
  113.         
  114.         //生成表格标题
  115.         HSSFRow row = sheet.createRow(0);
  116.         row.setHeight((short)300);
  117.         HSSFCell cell = null;
  118.         
  119.         for (int i = 0; i < headers.length; i++) {        
  120.             cell = row.createCell(i);
  121.             cell.setCellStyle(style);
  122.             HSSFRichTextString text = new HSSFRichTextString(headers[i]);
  123.             cell.setCellValue(text);
  124.         }
  125.         // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
  126.         CellRangeAddress region = new CellRangeAddress(0, 0, 0, 4);
  127.         sheet.addMergedRegion(region);
  128.         
  129.         //将数据放入sheet中
  130.         for (int i = 0; i < list.size(); i++) {
  131.         	HSSFRow r2 = sheet.createRow(i+1);
  132.             T t = list.get(i);
  133.             
  134.             //利用反射，根据JavaBean属性的先后顺序，动态调用get方法得到属性的值
  135.             Field[] fields = t.getClass().getDeclaredFields();
  136.             try {
  137.                 for (int j = 0; j < fields.length; j++) {
  138.                 	HSSFCell c2 = r2.createCell(j);
  139.                     Field field = fields[j];
  140.                     String fieldName = field.getName();
  141.                     String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
  142.                     Method getMethod = t.getClass().getMethod(methodName,new Class[]{});
  143.                     Object value = getMethod.invoke(t, new Object[]{});
  144.                     
  145.                     if(null == value)
  146.                         value ="";
  147.                     c2.setCellValue(value.toString());
  148.                     
  149.                 }
  150.             } catch (Exception e) {
  151.  
  152.             }
  153.         }
  154.         
  155.         try {
  156.             workbook.write(os);
  157.         } catch (Exception e) {
  158.  
  159.         }finally{
  160.             try {
  161.                 os.flush();
  162.                 os.close();
  163.             } catch (IOException e) {
  164.  
  165.             }
  166.         }
  167.         
  168.     }
  169.     
  170.     /** 
  171.     * @Title: getCellStyle 
  172.     * @Description: 获取单元格格式
  173.     * @param @param workbook
  174.     * @param @return
  175.     * @return HSSFCellStyle
  176.     * @throws 
  177.     */
  178.     public HSSFCellStyle getCellStyle(HSSFWorkbook workbook){
  179.         HSSFCellStyle style = workbook.createCellStyle();
  180.         style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
  181.         style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  182.         style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  183.         style.setBorderTop(HSSFCellStyle.BORDER_THIN);
  184.         style.setLeftBorderColor(HSSFCellStyle.BORDER_THIN);
  185.         style.setRightBorderColor(HSSFCellStyle.BORDER_THIN);
  186.         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  187.         
  188.         return style;
  189.     }
  190.  
  191.     /** 
  192.     * @Title: getFont 
  193.     * @Description: 生成字体样式
  194.     * @param @param workbook
  195.     * @param @return
  196.     * @return HSSFFont
  197.     * @throws 
  198.     */
  199.     public HSSFFont getFont(HSSFWorkbook workbook){
  200.         HSSFFont font = workbook.createFont();
  201.         font.setColor(HSSFColor.WHITE.index);
  202.         font.setFontHeightInPoints((short)12);
  203.         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  204.         return font;
  205.     }
  206.     
  207.     public boolean isIE(HttpServletRequest request){
  208.         return request.getHeader("USER-AGENT").toLowerCase().indexOf("msie")>0?true:false;    
  209.     }
  210. }

看一下数据库我们需要导出的数据：

那么我们调导出方法测试一下：

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/羊村懒王/article/detail/536776
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号