throws

throws

关注
发信
关注(28)粉丝(399)

使用poi导出excel

第一步添加依赖

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-scratchpad</artifactId>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
    </dependency>

编写所需要的工具类

package com.shiwen.yitihui.achievement.util;

import java.util.HashMap;
import java.util.Map;

/**

  • @author wangjie
  • @date 2019/12/21 11:41
  • @description 资料处理导出excel文件的模板

  • @company 石文软件有限公司
    */
    public class Constants {

    public interface Redis {
    String DRILL_STATISTICS_PREFIX = "DRILL-DAILY:";
    }

    // 模板所在位置
    public static final String TEMP_LOCATION = "excel/";

    public static final String EXCEL_SUFFIX = ".xlsx";

    // excel模板下载映射名称
    public static final Map<String, String> excelMap = new HashMap<>();

    static {
    excelMap.put("consolidated", "综合记录列表");
    }

    // 录井详情表头
    // public static final String DETAIL_HEADER = "井生产日报";
    }

package com.shiwen.yitihui.achievement.util;

/**

  • @author wangjie
  • @date 2019/12/21 12:01
  • @description

  • @company 石文软件有限公司
    */
    public class Enums {

    public enum DataReportType {
    CONSOLIDATED("consolidated"),
    SINGLE("single");

     private String value;

 DataReportType(String value) {
     this.value = value;
 }

 /**
  * 根据值获取类型
  *
  * @param value
  * @return
  */
 public static DataReportType getType(String value) {
     for (DataReportType type : DataReportType.values()) {
         if (type.getValue().equals(value)) {
             return type;
         }
     }
     return null;
 }

 public String getValue() {
     return value;
 }

    }
    }

package com.shiwen.yitihui.achievement.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.List;

/**

  • @author wangjie
  • @date 2019/12/20 18:26
  • @description

  • @company 石文软件有限公司
    */
    public class ExcelUtils {

    /**
    • 创建Excel文件
    • @param path 路径
    • @param list 数据
    • @param 模板
    • @return
    • @throws IOException
      */
      public static boolean writeExcel(Path path, List list) throws IOException {
      boolean success = false;
      if (Files.notExists(path)) {
      throw new IllegalArgumentException("文件不存在！");
      } else {
      Workbook workbook = new XSSFWorkbook(new FileInputStream(path.toFile()));
      // 获取第一个sheet
      Sheet sheet = workbook.getSheetAt(0);
      // 遍历行
      Row row;
      Cell cell;
      int index = 1;
      Field[] fields = new Field[0];
      if (list != null && list.size() > 0) {
      // 利用反射获取传入对象第一个元素的所有属性
      fields = list.get(0).getClass().getDeclaredFields();
      }
      for (T value : list) {
      row = sheet.createRow(index++);
      // 遍历列
      for (int i = 0; i < fields.length; i++) {
      // 创建单元格样式
      cell = row.createCell(i);
      // 水平居中，四边设置边框
      CellStyle cellStyle = InitDataStyle.getHorizontalCenterBorder(workbook);
      cellStyle.setWrapText(false); // 不换行
      cell.setCellStyle(cellStyle);
      // 获取当前行内容对象
      try {
      String filed = fields[i].getName();
      // 反射方法过去值(都设置成String)
      String val = getObjectValue(value, filed);
      // 将值写入单元格
      cell.setCellValue(val);
      } catch (Exception e) {
      e.printStackTrace();
      }
      // 设置列宽自动调整
      sheet.autoSizeColumn(i);
      }
      }
      OutputStream os = null;
      try {
      os = new FileOutputStream(path.toFile());
      workbook.write(os);
      success = true;
      } finally {
      if (os != null) {
      os.close();
      }
      if (workbook != null) {
      workbook.close();
      }
      }
      return success;
      }

    }

    /**
    • 反射获取值
    • @param obj 对象
    • @param cellValue 字段属性
    • @param 泛型
    • @return String
    • @throws Exception
      */
      private static String getObjectValue(T obj, String cellValue) throws Exception {
      String getName = "get" + cellValue.substring(0, 1).toUpperCase() + cellValue.substring(1);
      Method method = obj.getClass().getMethod(getName);
      return method.invoke(obj) != null ? String.valueOf(method.invoke(obj)) : "";
      }
    /**

    • 样式
      */
      public static class InitDataStyle {
      // 基础样式
      static CellStyle getNormal(Workbook wb) {
      CellStyle cellStyle = wb.createCellStyle();
      Font font = wb.createFont();
      font.setFontName("宋体");
      font.setFontHeightInPoints((short) 11);
      cellStyle.setFont(font);
      return cellStyle;
      }

      // 水平居中 四周边框
      static CellStyle getHorizontalCenterBorder(Workbook wb) {
      CellStyle cellStyle = getNormal(wb);
      cellStyle.setAlignment(HorizontalAlignment.CENTER);
      cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
      // 边框
      cellStyle.setBorderBottom(BorderStyle.THIN);
      cellStyle.setBorderLeft(BorderStyle.THIN);
      cellStyle.setBorderTop(BorderStyle.THIN);
      cellStyle.setBorderRight(BorderStyle.THIN);
      return cellStyle;
      }

    }

}

package com.shiwen.yitihui.achievement.util;

import java.io.InputStream;

/**

  • @Author Cwang

  • @Date 2019/7/12
    **/
    public class FileUtil {

    public static InputStream getResourcesFileInputStream(String fileName) {
    return Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName);
    }
    }

编写控制层

package com.shiwen.yitihui.achievement.controller;

import com.shiwen.yitihui.achievement.service.DataReportExcelService;
import com.shiwen.yitihui.achievement.util.Constants;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.nio.file.Path;

/**

  • @author wangjie
  • @date 2019/12/21 11:39
  • @description 资料处理导出excel

  • @company 石文软件有限公司
    */
    @RestController
    @RequestMapping("/data")
    public class DataReportExcelController {

    @Autowired
    private DataReportExcelService dataReportExcelService;

    /**
    • 导出Excel
    • @param request
    • @return
    • @throws IOException
      /
      @GetMapping("/excel/")
      public ResponseEntity<byte[]> exportExcel(String type,
      String jh, HttpServletRequest request) throws IOException {
      /      type="consolidated";
      vo.setJm("克104");
      vo.setCurPage(1);
      vo.setPageSize(30);*/
      //创建请求头对象
      HttpHeaders headers = new HttpHeaders();
      //设置请求的方式二进制文件
      headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
      //设置读取文件的名字
      headers.setContentDispositionFormData("attachment",
      getFileNameByBrowser(request, Constants.excelMap.get(type) + Constants.EXCEL_SUFFIX));
      //获取二进制的excel文件
      Path path = dataReportExcelService.getExcelFile(type, jh);
      //将二进制的excel文件转换成字节数组
      byte[] bytes = Files.readAllBytes(path);
      //删除获取的二进制文件
      Files.delete(path);
      //将字节数组响应在客户端页面
      return new ResponseEntity<>(bytes, headers, HttpStatus.OK);
      }
    /**
    • 判断客户端浏览器类型
    • @param request
    • @return

    • @throws IOException
      */
      private String getFileNameByBrowser(HttpServletRequest request, String fileName) throws IOException {
      String UserAgent = request.getHeader("USER-AGENT").toLowerCase();

      if (UserAgent.contains("firefox")) {
      return new String(fileName.getBytes("UTF-8"), "iso-8859-1");
      } else {
      return URLEncoder.encode(fileName, "UTF-8").replaceAll("\+", "%20");
      }
      }
      }

package com.shiwen.yitihui.achievement.service;

import java.io.IOException;
import java.nio.file.Path;

/**

  • @author wangjie
  • @date 2019/12/21 11:45
  • @description

  • @company 石文软件有限公司
    */
    public interface DataReportExcelService {

    /**
    • 获取excel的二进制文件
    • @param type
    • @param
    • @return
    • @throws IOException
      */
      Path getExcelFile(String type, String jh) throws IOException;
      }

package com.shiwen.yitihui.achievement.service.impl;

import com.shiwen.yitihui.achievement.dao.basic.ConsolidatedRecordsDao;
import com.shiwen.yitihui.achievement.service.DataReportExcelService;
import com.shiwen.yitihui.achievement.util.Constants;
import com.shiwen.yitihui.achievement.util.Enums;
import com.shiwen.yitihui.achievement.util.ExcelUtils;
import com.shiwen.yitihui.achievement.util.FileUtil;
import com.shiwen.yitihui.domain.basic.ConsolidatedRecords;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.util.List;
import java.util.UUID;

/**

  • @author wangjie
  • @date 2019/12/21 11:50
  • @description

  • @company 石文软件有限公司
    */
    @Service
    public class DataReportExcelServiceImpl implements DataReportExcelService {

    private static final String TEMP_FLODAE = System.getProperty("user.dir") + File.separator + "temp_excel";

    @Autowired
    private ConsolidatedRecordsDao consolidatedRecordsDao;

    @Override
    public Path getExcelFile(String type, String jh) throws IOException {
    Enums.DataReportType reportType = Enums.DataReportType.getType(type);
    if (reportType == null) {
    return null;
    }
    Path path = null;
    switch (reportType) {
    case CONSOLIDATED:
    List list = consolidatedRecordsDao.getListByJh(jh);
    path = handleExcel(type, list);
    break;
    default:
    break;
    }
    return path;
    }

    /**
    • 处理excel
    • @param type
    • @param list
    • @param

    • @throws IOException
      */
      private Path handleExcel(String type, List list) throws IOException {
      //读取对应excel文件的模板
      InputStream excel = FileUtil.getResourcesFileInputStream(Constants.TEMP_LOCATION + type + Constants.EXCEL_SUFFIX);
      //创建带有文件路径的path对象
      Path desPath = Paths.get(TEMP_FLODAE + File.separator + UUID.randomUUID().toString() + Constants.EXCEL_SUFFIX);
      Path pPath = desPath.getParent();

      if (Files.notExists(pPath)) {
      Files.createDirectory(pPath);
      }
      //将excel文件模板,拷贝到path对象中
      Files.copy(excel, desPath, StandardCopyOption.REPLACE_EXISTING);
      //
      if (Enums.DataReportType.SINGLE.getValue().equals(type)) {
      /LoggingDTO logging = new LoggingDTO();
      if (list.size() > 0) {
      logging = (LoggingDTO) list.get(0);
      }
      ExcelUtils.writeExcelDetail(desPath, logging, Constants.DETAIL_HEADER);      /
      System.out.println("=================================使用定制模板");
      } else {
      ExcelUtils.writeExcel(desPath, list);
      }
      return desPath;
      }
      }

12-14 05:20
Powered by LMLPHP ©2024 throws 0.079395