excel导出

package com.zdww.supervision.application.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**

• @Auther: Tinko

• @Date: 2019/2/20 17:46

• @Description: 导出到Excel
  */
  public class ExcelUtil {
  public static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

  /**

  • 创建工作表

  • @param list 数据列表

  • @param mappingList 表头和字段映射

  • @return HSSFWorkbook
    /
    public static HSSFWorkbook createHSSFWorkbook(List<?> list, List mappingList) {
    if (null == mappingList || mappingList.size() < 1) {
    return null;
    }
    String[] fields = mappingList.stream().map(FieldMapping::getField).collect(Collectors.toList()).toArray(new String[mappingList.size()]);
    String[] titles = mappingList.stream().map(FieldMapping::getTitle).collect(Collectors.toList()).toArray(new String[mappingList.size()]);
    return createHSSFWorkbook(list, fields, titles);
    }
    /*

  • 将对象转换成Map<String, String>格式

  • @param obj

  • @return
    */
    public static Map<String, String> getNamValMap(Object obj, boolean isSort) {
    Map<String, String> map = null;
    if(isSort) {
    map = new TreeMap<String, String>();
    }else{
    map = new HashMap<String, String>();
    }

    Field[] fieldArr = obj.getClass().getDeclaredFields();
    try {
    for (Field field : fieldArr) {
    field.setAccessible(true);
    if (field.get(obj) != null && !"".equals(field.get(obj).toString())) {
    map.put(field.getName(), field.get(obj).toString());
    }
    }
    } catch (IllegalAccessException e) {
    logger.error(e.getMessage());
    }

    return map;
    }

  /**

  • 创建文件
  • @param list 数据列表
  • @param fieldList 表头和字段映射
  • @param path 文件路径
  • @return File
    */
    public static File createFile(List<?> list, List fieldList, String path) {
    if (null == fieldList || fieldList.size() < 1) {
    return null;
    }
    String[] fields = fieldList.stream().map(FieldMapping::getField).collect(Collectors.toList()).toArray(new String[fieldList.size()]);
    String[] titles = fieldList.stream().map(FieldMapping::getTitle).collect(Collectors.toList()).toArray(new String[fieldList.size()]);
    return createFile(list, fields, titles, path);
    }

  private static HSSFWorkbook createHSSFWorkbook(List<?> list, String[] fields, String[] titles) {

   HSSFWorkbook workbook = new HSSFWorkbook();
   HSSFSheet sheet = workbook.createSheet();
   addTitleRow(sheet, titles);
   addRowWithList(list, sheet, fields);
   return workbook;
  

  }

  private static File createFile(List<?> list, String[] fields, String[] titles, String path) {
  HSSFWorkbook workbook = createHSSFWorkbook(list, fields, titles);
  File file = new File(path);
  try {
  workbook.write(file);
  } catch (IOException e) {
  e.printStackTrace();
  }
  return file;
  }

  private static void addTitleRow(HSSFSheet sheet, String[] titles) {
  HSSFRow row = sheet.createRow(0);
  for (int i = 0; i < titles.length; i++) {
  HSSFCell cell = row.createCell(i);
  cell.setCellValue(titles[i]);
  cell.setCellType(CellType.STRING);
  }
  }

  private static void addRowWithList(List<?> list, HSSFSheet sheet, String[] fields) {
  if (null == list || list.isEmpty()) {
  return;
  }
  for (int i = 0; i < list.size(); i++) {
  Object o = list.get(i);
  //创建一行
  HSSFRow row = sheet.createRow(i + 1);
  addToCell(row, fields, o);
  }
  }

  private static void addToCell(HSSFRow row, String[] fields, Object vo) {
  Class<?> clazz = vo.getClass();
  Method method;
  for (int i = 0; i < fields.length; i++) {
  HSSFCell cell = row.createCell(i);
  cell.setCellType(CellType.STRING);
  String field = fields[i];
  if (null == field || “”.equals(field)){
  // continue;
  }
  String value = “”;
  if (vo instanceof Map) {
  value = String.valueOf(((Map) vo).get(field));
  } else {
  try {
  method = clazz.getDeclaredMethod(captureName(field));
  if (method.invoke(vo) instanceof Date) {
  try {
  value = (new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”)).format(method.invoke(vo));
  } catch (Exception e) {
  value = String.valueOf(method.invoke(vo));
  }
  } else {
  value = String.valueOf(method.invoke(vo));
  }
  } catch (Exception e) {
  logger.error(“反射取值错误:” + field, e);
  }
  }
  if(value == null || “null”.equals(value)) {
  value = " ";
  }
  cell.setCellValue(value);
  }
  }

  private static String captureName(String name) {
  byte[] bytes = name.getBytes();
  bytes[0] = (byte) ((char) bytes[0] - ‘a’ + ‘A’);
  name = new String(bytes);
  name = “get” + name;
  return name;
  }

  public static Workbook exportStream(Workbook workbook, HttpServletResponse response, String fileName) {
  // 生成提示信息，
  response.setContentType(“application/vnd.ms-excel”);
  OutputStream fOut = null;
  try {
  // 进行转码，使其支持中文文件名
  String codedFileName = URLEncoder.encode(fileName, “UTF-8”);
  response.setHeader(“content-disposition”, “attachment;filename=” + codedFileName + “.xls”);
  CellStyle cellStyle = workbook.createCellStyle();
  cellStyle.setAlignment(HorizontalAlignment.CENTER);
  fOut = response.getOutputStream();
  workbook.write(fOut);
  } catch (Exception e) {
  logger.error(“导出报表错误，错误: {}”, e);
  } finally {
  try {
  if (null != fOut) {
  fOut.flush();
  fOut.close();
  }
  } catch (IOException e) {
  logger.error(“导出报表生成错误,错误: {}”, e);
  }
  }
  logger.info(“文件生成…”);
  return workbook;
  }

  public static void exportDocStream(XWPFDocument document, HttpServletResponse response, String fileName) {
  // 生成提示信息，
  response.setContentType(“application/msword”);
  OutputStream fOut = null;
  try {
  // 进行转码，使其支持中文文件名
  String codedFileName = URLEncoder.encode(fileName, “UTF-8”);
  response.setHeader(“content-disposition”, “attachment;filename=” + codedFileName + “.docx”);

       fOut = response.getOutputStream();
       document.write(fOut);
   } catch (Exception e) {
       logger.error("导出报表错误，错误: {}", e);
   } finally {
       try {
           if (null != fOut) {
               fOut.flush();
               fOut.close();
           }
       } catch (IOException e) {
           logger.error("导出报表生成错误,错误: {}", e);
       }
   }
   logger.info("文件生成...");
  

  }

  public static void export2007Stream(Workbook workbook, HttpServletResponse response, String fileName) {
  // 生成提示信息，
  response.setContentType(“application/vnd.ms-excel”);
  OutputStream fOut = null;
  try {
  // 进行转码，使其支持中文文件名
  String codedFileName = URLEncoder.encode(fileName, “UTF-8”);
  response.setHeader(“content-disposition”, “attachment;filename=” + codedFileName + “.xlsx”);

       fOut = response.getOutputStream();
       workbook.write(fOut);
   } catch (Exception e) {
       logger.error("导出报表错误，错误: {}", e);
   } finally {
       try {
           if (null != fOut) {
               fOut.flush();
               fOut.close();
           }
       } catch (IOException e) {
           logger.error("导出报表生成错误,错误: {}", e);
       }
   }
   logger.info("文件生成...");
  

  }
  /**

  • 表头字段映射关系
    */
    public static class FieldMapping {
    private String title;
    private String field;

    public FieldMapping(String title, String field) {
    this.title = title;
    this.field = field;
    }

    public String getTitle() {
    return title;
    }

    public void setTitle(String title) {
    this.title = title;
    }

    public String getField() {
    return field;
    }

    public void setField(String field) {
    this.field = field;
    }
    }
    }