spring boot 整合 poi 導出excel
- 2020 年 6 月 1 日
- 筆記
一、 第一種方式
1.首先從中央倉庫中導入架包Poi3.14以及Poi-ooxml3.14.
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency>
2.編寫工具類
編寫用於導出Excel的工具類ExportExcelUtils,工具類可以直接使用,不需要更改。
package com.roncoo.education.web.common; import org.apache.poi.hssf.usermodel.*; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.List; /** * @author ZhangRF */ public class ExcelUtil { /** * Excel表格導出 * * @param response HttpServletResponse對象 * @param excelData Excel表格的數據,封裝為List<List<String>> * @param sheetName sheet的名字 * @param fileName 導出Excel的文件名 * @param columnWidth Excel表格的寬度,建議為15 * @throws IOException 拋IO異常 */ public static void exportExcel(HttpServletResponse response, List<List<String>> excelData, String sheetName, String fileName, int columnWidth) throws IOException { //聲明一個工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); //生成一個表格,設置表格名稱 HSSFSheet sheet = workbook.createSheet(sheetName); //設置表格列寬度 sheet.setDefaultColumnWidth(columnWidth); //寫入List<List<String>>中的數據 int rowIndex = 0; for (List<String> data : excelData) { //創建一個row行,然後自增1 HSSFRow row = sheet.createRow(rowIndex++); //遍歷添加本行數據 for (int i = 0; i < data.size(); i++) { //創建一個單元格 HSSFCell cell = row.createCell(i); //創建一個內容對象 HSSFRichTextString text = new HSSFRichTextString(data.get(i)); //將內容對象的文字內容寫入到單元格中 cell.setCellValue(text); } } //準備將Excel的輸出流通過response輸出到頁面下載 //八進位輸出流 response.setContentType("application/octet-stream"); //設置導出Excel的名稱 response.setHeader("Content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8")); //刷新緩衝 response.flushBuffer(); //workbook將Excel寫入到response的輸出流中,供頁面下載該Excel文件 workbook.write(response.getOutputStream()); //關閉workbook workbook.close(); } }
3.service導出實現
public void orderExport(OrderInfoQO orderInfoQO, HttpServletResponse response) { orderInfoQO.setPageCurrent(1); orderInfoQO.setPageSize(1000); Integer courseFlag = orderInfoQO.getCourseFlag(); CourseFlagEnum courseFlagEnum = CourseFlagEnum.values(courseFlag); boolean isGet = true; List<List<String>> excelData = new ArrayList<>(); List<String> head = new ArrayList<>(); head.add("訂單編號"); head.add("講師資訊"); head.add("用戶手機號"); head.add("用戶姓名"); if (courseFlagEnum == CourseFlagEnum.YUNXIAOHUI) { head.add("機構名稱"); } head.add("課程顧問"); head.add("課程名稱"); head.add("課程原價"); head.add("課程實付"); head.add("齒幣"); head.add("交易類型"); head.add("支付方式"); head.add("購買渠道"); head.add("支付時間"); head.add("客戶備註"); excelData.add(head); do { Page<OrderInfoVO> page = feignOrderInfo.listForPage(orderInfoQO); if (page.getPageCurrent() == page.getTotalPage()) { isGet = false; } else { orderInfoQO.setPageCurrent(page.getPageCurrent() + 1); } page.getList().stream().map(m -> { UserExtVO userExtVO = feignUserExt.getByUserNo(m.getUserNo()); List<String> data = new ArrayList<>(); data.add(String.valueOf(m.getOrderNo())); data.add(m.getLecturerName()); data.add(m.getMobile()); if (userExtVO != null && userExtVO.getId() != null) { data.add(userExtVO.getRealName()); if (courseFlagEnum == CourseFlagEnum.YUNXIAOHUI) { data.add(userExtVO.getOrgName()); } data.add(userExtVO.getKefuName()); } else { data.add(""); if (courseFlagEnum == CourseFlagEnum.YUNXIAOHUI) { data.add(""); } data.add(""); } data.add(m.getCourseName()); data.add(String.valueOf(m.getPricePayable())); data.add(String.valueOf(m.getPricePaid())); data.add(String.valueOf(m.getUsePoint())); TradeTypeEnum tradeTypeEnum = TradeTypeEnum.values(m.getTradeType()); if (tradeTypeEnum == null) { data.add(""); } else { data.add(tradeTypeEnum.getDesc()); } PayTypeEnum payTypeEnum = PayTypeEnum.values(m.getPayType()); if (payTypeEnum == null) { data.add(""); } else { data.add(payTypeEnum.getDesc()); } ChannelTypeEnum channelTypeEnum = ChannelTypeEnum.values(m.getChannelType()); if (channelTypeEnum == null) { data.add(""); } else { data.add(channelTypeEnum.getDesc()); } data.add(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(m.getPayTime())); data.add(m.getRemarkCus()); excelData.add(data); return m; }).collect(Collectors.toList()); } while (isGet); String sheetName = "課程訂單"; String fileName = "訂單報表-"; if (courseFlagEnum == CourseFlagEnum.YUNXIAOHUI) { fileName = "雲校匯訂單報表"; } fileName = fileName + new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + ".xls"; try { ExcelUtil.exportExcel(response, excelData, sheetName, fileName, 15); } catch (IOException e) { e.printStackTrace(); } }
4.介面調用
/** * 報表導出 * * @param response * @throws IOException */ @RequestMapping(value = "/export", method = RequestMethod.GET) public void orderExport(@ModelAttribute OrderInfoQO orderInfoQO, HttpServletResponse response) throws IOException { biz.orderExport(orderInfoQO, response); }
二、第二種方式
1.首先從中央倉庫中導入架包Poi3.14以及Poi-ooxml3.14.
<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.14</version> </dependency>
2.編寫工具類
編寫用於導出Excel的工具類ExportExcelUtils,工具類可以直接使用,不需要更改。
package org.emall.website.util; import org.apache.poi.hssf.usermodel.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.lang.reflect.Field; import java.util.Date; import java.text.SimpleDateFormat; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; /** * @version v.0.1 * @Description TODO * @date 2020/6/1 * @備註 **/ public class ExportExcelUtils { private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtils.class); /** * 導出Excel * * @param excelName 要導出的excel名稱 * @param list 要導出的數據集合 * @param fieldMap 中英文欄位對應Map,即要導出的excel表頭 * @param response 使用response可以導出到瀏覽器 * @param <T> */ public static <T> void export(String excelName, List<T> list, LinkedHashMap<String, String> fieldMap, HttpServletResponse response) { // 設置默認文件名為當前時間:年月日時分秒 if (excelName == null || excelName == "") { excelName = new SimpleDateFormat("yyyyMMddhhmmss").format( new Date()).toString(); } // 設置response頭資訊 response.reset(); response.setContentType("application/vnd.ms-excel"); // 改成輸出excel文件 try { response.setHeader("Content-disposition", "attachment; filename=" + new String(excelName.getBytes("gb2312"), "ISO-8859-1") + ".xls"); } catch (UnsupportedEncodingException e1) { logger.info(e1.getMessage()); } try { //創建一個WorkBook,對應一個Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); //在Workbook中,創建一個sheet,對應Excel中的工作薄(sheet) HSSFSheet sheet = wb.createSheet(excelName); //創建單元格,並設置值表頭 設置表頭居中 HSSFCellStyle style = wb.createCellStyle(); //創建一個居中格式 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 填充工作表 fillSheet(sheet, list, fieldMap, style); //將文件輸出 OutputStream ouputStream = response.getOutputStream(); wb.write(ouputStream); ouputStream.flush(); ouputStream.close(); } catch (Exception e) { logger.info("導出Excel失敗!"); logger.error(e.getMessage()); } } /** * 根據欄位名獲取欄位對象 * * @param fieldName 欄位名 * @param clazz 包含該欄位的類 * @return 欄位 */ public static Field getFieldByName(String fieldName, Class<?> clazz) { logger.info("根據欄位名獲取欄位對象:getFieldByName()"); // 拿到本類的所有欄位 Field[] selfFields = clazz.getDeclaredFields(); // 如果本類中存在該欄位,則返回 for (Field field : selfFields) { //如果本類中存在該欄位,則返回 if (field.getName().equals(fieldName)) { return field; } } // 否則,查看父類中是否存在此欄位,如果有則返回 Class<?> superClazz = clazz.getSuperclass(); if (superClazz != null && superClazz != Object.class) { //遞歸 return getFieldByName(fieldName, superClazz); } // 如果本類和父類都沒有,則返回空 return null; } /** * 根據欄位名獲取欄位值 * * @param fieldName 欄位名 * @param o 對象 * @return 欄位值 * @throws Exception 異常 */ public static Object getFieldValueByName(String fieldName, Object o) throws Exception { logger.info("根據欄位名獲取欄位值:getFieldValueByName()"); Object value = null; //根據欄位名得到欄位對象 Field field = getFieldByName(fieldName, o.getClass()); //如果該欄位存在,則取出該欄位的值 if (field != null) { field.setAccessible(true);//類中的成員變數為private,在類外邊使用屬性值,故必須進行此操作 value = field.get(o);//獲取當前對象中當前Field的value } else { throw new Exception(o.getClass().getSimpleName() + "類不存在欄位名 " + fieldName); } return value; } /** * 根據帶路徑或不帶路徑的屬性名獲取屬性值,即接受簡單屬性名, * 如userName等,又接受帶路徑的屬性名,如student.department.name等 * * @param fieldNameSequence 帶路徑的屬性名或簡單屬性名 * @param o 對象 * @return 屬性值 * @throws Exception 異常 */ public static Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throws Exception { logger.info("根據帶路徑或不帶路徑的屬性名獲取屬性值,即接受簡單屬性名:getFieldValueByNameSequence()"); Object value = null; // 將fieldNameSequence進行拆分 String[] attributes = fieldNameSequence.split("\\."); if (attributes.length == 1) { value = getFieldValueByName(fieldNameSequence, o); } else { // 根據數組中第一個連接屬性名獲取連接屬性對象,如student.department.name Object fieldObj = getFieldValueByName(attributes[0], o); //截取除第一個屬性名之後的路徑 String subFieldNameSequence = fieldNameSequence .substring(fieldNameSequence.indexOf(".") + 1); //遞歸得到最終的屬性對象的值 value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj); } return value; } /** * 向工作表中填充數據 * * @param sheet excel的工作表名稱 * @param list 數據源 * @param fieldMap 中英文欄位對應關係的Map * @param style 表格中的格式 * @throws Exception 異常 */ public static <T> void fillSheet(HSSFSheet sheet, List<T> list, LinkedHashMap<String, String> fieldMap, HSSFCellStyle style) throws Exception { logger.info("向工作表中填充數據:fillSheet()"); // 定義存放英文欄位名和中文欄位名的數組 String[] enFields = new String[fieldMap.size()]; String[] cnFields = new String[fieldMap.size()]; // 填充數組 int count = 0; for (Map.Entry<String, String> entry : fieldMap.entrySet()) { enFields[count] = entry.getKey(); cnFields[count] = entry.getValue(); count++; } //在sheet中添加表頭第0行,注意老版本poi對Excel的行數列數有限制short HSSFRow row = sheet.createRow((int) 0); // 填充表頭 for (int i = 0; i < cnFields.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellValue(cnFields[i]); cell.setCellStyle(style); sheet.autoSizeColumn(i); } // 填充內容 for (int index = 0; index < list.size(); index++) { row = sheet.createRow(index + 1); // 獲取單個對象 T item = list.get(index); for (int i = 0; i < enFields.length; i++) { Object objValue = getFieldValueByNameSequence(enFields[i], item); String fieldValue = objValue == null ? "" : objValue.toString(); row.createCell(i).setCellValue(fieldValue); } } } }
3.編寫實體類
需要輸出到Excel的實體表。
package org.emall.website.entity; import java.math.BigInteger; /** * @version v.0.1 * @Description TODO * @date 2020/6/1 * @備註 **/ public class Excel { private BigInteger id; private String order_number; private String amount_real; private String date_add; private String status_str; private String link_man; private String mobie; private String address; private String detailValue; public Excel() { } public Excel(BigInteger id, String order_number, String amount_real, String date_add, String status_str, String link_man, String mobie, String address, String detailValue) { this.id = id; this.order_number = order_number; this.amount_real = amount_real; this.date_add = date_add; this.status_str = status_str; this.link_man = link_man; this.mobie = mobie; this.address = address; this.detailValue = detailValue; } public BigInteger getId() { return id; } public void setId(BigInteger id) { this.id = id; } public String getOrder_number() { return order_number; } public void setOrder_number(String order_number) { this.order_number = order_number; } public String getAmount_real() { return amount_real; } public void setAmount_real(String amount_real) { this.amount_real = amount_real; } public String getDate_add() { return date_add; } public void setDate_add(String date_add) { this.date_add = date_add; } public String getStatus_str() { return status_str; } public void setStatus_str(String status_str) { this.status_str = status_str; } public String getLink_man() { return link_man; } public void setLink_man(String link_man) { this.link_man = link_man; } public String getMobie() { return mobie; } public void setMobie(String mobie) { this.mobie = mobie; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getDetailValue() { return detailValue; } public void setDetailValue(String detailValue) { this.detailValue = detailValue; } }
4.編寫控制層即得到Json數據
編寫控制層ExcelController,注意fieldMap.put(「實體類屬性名」,「Excel中顯示的屬性名」); 設置實體屬性名和資料庫中對應。如果得不到數據對應不上,請採用ResultMap設置或者用Select XX(資料庫名)As 實體屬性名。
package org.emall.website.controller; import org.emall.website.entity.Excel; import org.emall.website.entity.Order; import org.emall.website.service.OrderService; import org.emall.website.util.ExportExcelUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.util.LinkedHashMap; import java.util.List; /** * @version v.0.1 * @Description TODO * @date 2020/6/1 * @備註 **/ @Controller public class ExcelController { @Autowired private HttpServletRequest request; @Autowired private OrderService orderService; @RequestMapping(value = "/excel/exportBankCheckInfo",method = RequestMethod.GET) public void ExportBankCkeckInfo(HttpServletResponse response, HttpServletRequest request){ //這裡是筆者實際業務需求中需要得到時間間隔。可忽略 String start=request.getParameter("start"); String end=request.getParameter("end"); System.out.println("列印的起始日期為:"+start+",列印的結束日期為:"+end); //得到所有要導出的數據 List<Excel> orderlist =orderService.listexcel(start,end); //定義導出的excel名字 String excelName = "訂單詳情表"; //獲取需要轉出的excel表頭的map欄位 LinkedHashMap<String, String> fieldMap = new LinkedHashMap<>(); fieldMap.put("id","編號"); fieldMap.put("link_man","姓名"); fieldMap.put("amount_real","價格"); fieldMap.put("date_add","日期"); fieldMap.put("status_str","訂單狀態"); fieldMap.put("mobie","收貨電話"); fieldMap.put("address","地址"); fieldMap.put("detailValue","訂單詳情"); //導出用戶相關資訊 new ExportExcelUtils().export(excelName,orderlist,fieldMap,response); } }
5.驗證
在導出工具類中,通過HttpServletResponse response設置輸出為Excel。所以通過Controller層的Url可以直接得到URL。保存位置是保存在瀏覽器的默認保存位置,因為是下載請求。