java 导出excle

import com.qingxing.ManagerComplex.exception.DataNotFoundException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;

import java.io.ByteArrayOutputStream;
import java.lang.reflect.Field;
import java.nio.charset.Charset;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * Excel导出工具类
 *
 * @author zyj34
 *
 */
public class ExportUtils {

  /**
   * 导出结构
   * @param filename
   * @param data
   * @param titles
   * @param columns
   * @return
   */
  public  static ResponseEntity<byte[]> getExportResultsEntity(String filename, List data, String[] titles, String[] columns) {
    byte[] bytes = null;
    if (data == null) {
      throw new DataNotFoundException("导出失败");
    }
    else  {
      bytes = ExportUtils.outputExcelEntity(filename,data,titles,columns);
    }
    filename = StringUtils.isBlank(filename) ? "Book1" : filename;
    HttpHeaders headers = new HttpHeaders();
    headers.add("filename", filename + ".xls");
    headers.setContentType(MediaType.parseMediaType("application/vnd.ms-excel"));
    // headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
    headers.setContentDispositionFormData("attchemnt", filename + ".xls", Charset.forName("UTF-8"));
    return new ResponseEntity<>(bytes, headers, HttpStatus.CREATED);
  }

  /**
   * 导出结果
   * @param filename
   * @param data
   * @param titles
   * @param columns
   * @return
   */
  public static ResponseEntity<byte[]> getExportResultsMap(String filename, List<Map<String, Object>> data, String[] titles, String[] columns) {
    byte[] bytes = null;
    if (data == null) {
      throw new DataNotFoundException("导出失败");
    }
    else  {
      bytes = ExportUtils.outputExcelMap(data,titles,filename,columns);
    }
    filename = StringUtils.isBlank(filename) ? "Book1" : filename;
    HttpHeaders headers = new HttpHeaders();
    headers.add("filename", filename + ".xls");
    headers.setContentType(MediaType.parseMediaType("application/vnd.ms-excel"));
    // headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
    headers.setContentDispositionFormData("attchemnt", filename + ".xls", Charset.forName("UTF-8"));
    return new ResponseEntity<>(bytes, headers, HttpStatus.CREATED);
  }

  /**
   * 导出excel
   * @param list
   * @param titles
   * @param sheetname
   * @param columns
   * @return
   */
  public static byte[] outputExcelMap(List<Map<String, Object>> list, String[] titles, String sheetname, String[] columns) {
    return outputExcelMap(list, titles, sheetname, columns, new String[columns.length]);
  }
  /**
   * 导出excel
   * @param list 数据
   * @param titles 标题
   * @param sheetname 工作表名称
   * @param columns 每列对应的数据key值
   * @param convernames 值转换字符，转换后的名称,分割，从0开始
   * @return
   */
  public static byte[] outputExcelMap(List<Map<String, Object>> list, String[] titles, String sheetname, String[] columns, String[] convernames) {
    ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
    HSSFWorkbook workbook = null;
    try {
      workbook = new HSSFWorkbook();
      sheetname = StringUtils.isBlank(sheetname) ? "sheet1" : sheetname;
      HSSFSheet sheet = workbook.createSheet(sheetname);
      HSSFCellStyle cellStyle = workbook.createCellStyle();
      cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
      cellStyle.setAlignment(HorizontalAlignment.CENTER);
      // 生成标题
      int rowNum = 0;
      HSSFRow row = sheet.createRow((short) rowNum);
      HSSFCell cell = null;
      for (int i = 0; i < titles.length; i++) {
        cell = row.createCell(i);
        cell.setCellStyle(cellStyle);
        cell.setCellType(CellType.STRING);
        cell.setCellValue(titles[i]);
        sheet.setColumnWidth(i, 15 * 256);
      }
      rowNum++;
      // 生成数据
      for (int j = 0; j < list.size(); j++) {
        row = sheet.createRow((short) rowNum);
        Map<String, Object> map = list.get(j);
        for (int i = 0; i < columns.length; i++) {
          cell = row.createCell(i);
          cell.setCellStyle(cellStyle);
          cell.setCellType(CellType.STRING);
          String key = columns[i];
          Object value = map.get(key);
          if (value != null) {
            String convername = convernames[i];
            if (convername != null && !"".equals(convername)) {
              value = Arrays.asList(convername.split(",")).get(Integer.parseInt(value.toString()));
            }
            cell.setCellValue(value.toString());
          } else {
            cell.setCellValue("");
          }
        }
        rowNum++;
      }
      workbook.write(byteArrayOutputStream);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (workbook != null) {
        try {
          workbook.close();
        } catch (Exception e) {
          e.printStackTrace();
        }
      }
    }
    return byteArrayOutputStream.toByteArray();
  }

  public static byte[] AssoutputExcel(String sheetname, List<Map<String, Object>> list1,
                                      List<Map<String, Object>> list2, String[] titles1, String[] titles2, String[] columns1,
                                      String[] columns2,String[] convernames1,String[] convernames2) {
    ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
    HSSFWorkbook workbook = null;
    try {
      workbook = new HSSFWorkbook();
      sheetname = StringUtils.isBlank(sheetname) ? "sheet1" : sheetname;
      HSSFSheet sheet = workbook.createSheet(sheetname);
      HSSFCellStyle cellStyle = workbook.createCellStyle();
      cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
      cellStyle.setAlignment(HorizontalAlignment.CENTER);
      // 生成标题
      int rowNum = 0;
      HSSFRow row = sheet.createRow((short) rowNum);
      HSSFCell cell = null;
      cell = row.createCell(0);
      cell.setCellStyle(cellStyle);
      cell.setCellType(CellType.STRING);
      cell.setCellValue("个人信息");
      sheet.setColumnWidth(0, 60 * 256);

      rowNum++;
      row = sheet.createRow((short) rowNum);
      for (int i = 0; i < titles1.length; i++) {
        cell = row.createCell(i);
        cell.setCellStyle(cellStyle);
        cell.setCellType(CellType.STRING);
        cell.setCellValue(titles1[i]);
        sheet.setColumnWidth(i, 15 * 256);
      }
      rowNum++;
      // 生成数据
      for (int j = 0; j < list1.size(); j++) {
        row = sheet.createRow((short) rowNum);
        Map<String, Object> map = list1.get(j);
        for (int i = 0; i < columns1.length; i++) {
          cell = row.createCell(i);
          cell.setCellStyle(cellStyle);
          cell.setCellType(CellType.STRING);
          String key = columns1[i];
          Object value = map.get(key);
          if (value != null) {
            String convername1 = convernames1[i];
            if (convername1 != null && !"".equals(convername1)) {
              value = Arrays.asList(convername1.split(",")).get(Integer.parseInt(value.toString()));
            }
            cell.setCellValue(value.toString());
          } else {
            cell.setCellValue("");
          }
        }
        rowNum++;
      }


      rowNum++;
      row = sheet.createRow((short) rowNum);
      cell = row.createCell(0);
      cell.setCellStyle(cellStyle);
      cell.setCellType(CellType.STRING);
      cell.setCellValue("关联人员信息");
      sheet.setColumnWidth(0, 45 * 256);

      rowNum++;
      row = sheet.createRow((short) rowNum);
      for (int i = 0; i < titles2.length; i++) {
        cell = row.createCell(i);
        cell.setCellStyle(cellStyle);
        cell.setCellType(CellType.STRING);
        cell.setCellValue(titles2[i]);
        sheet.setColumnWidth(i, 15 * 256);
      }
      rowNum++;
      for (int j = 0; j < list2.size(); j++) {
        row = sheet.createRow((short) rowNum);
        Map<String, Object> map = list2.get(j);
        for (int i = 0; i < columns2.length; i++) {
          cell = row.createCell(i);
          cell.setCellStyle(cellStyle);
          cell.setCellType(CellType.STRING);
          String key = columns2[i];
          Object value = map.get(key);
          if (value != null) {
            String convername2 = convernames2[i];
            if (convername2 != null && !"".equals(convername2)) {
              value = Arrays.asList(convername2.split(",")).get(Integer.parseInt(value.toString()));
            }
            cell.setCellValue(value.toString());
          } else {
            cell.setCellValue("");
          }
        }
        rowNum++;
      }


      workbook.write(byteArrayOutputStream);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (workbook != null) {
        try {
          workbook.close();
        } catch (Exception e) {
          e.printStackTrace();
        }
      }
    }
    return byteArrayOutputStream.toByteArray();
  }

  public static byte[] outputExcelEntity(String sheetname,List list, String[] titles,String[] columns) {
    return outputExcelEntity(sheetname,list, titles,columns, new String[columns.length]);
  }


  public static byte[] outputExcelEntity(String sheetname,List list, String[] titles,String[] columns,String[] convernames) {
    ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
    HSSFWorkbook workbook = null;
    try {
      workbook = new HSSFWorkbook();
      sheetname = StringUtils.isBlank(sheetname) ? "sheet1" : sheetname;
      HSSFSheet sheet = workbook.createSheet(sheetname);
      HSSFCellStyle cellStyle = workbook.createCellStyle();
      cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
      cellStyle.setAlignment(HorizontalAlignment.CENTER);
      // 生成标题
      int rowNum = 0;
      HSSFRow row = sheet.createRow((short) rowNum);
      HSSFCell cell = null;
      for (int i = 0; i < titles.length; i++) {
        cell = row.createCell(i);
        cell.setCellStyle(cellStyle);
        cell.setCellType(CellType.STRING);
        cell.setCellValue(titles[i]);
        sheet.setColumnWidth(i, 15 * 256);
      }
      rowNum++;
      // 生成数据
      for (int j = 0; j < list.size(); j++) {
        row = sheet.createRow((short) rowNum);
        Object entity = list.get(j);
        Class<?> clz = entity.getClass();
        for (int i = 0; i < columns.length; i++) {
          cell = row.createCell(i);
          cell.setCellStyle(cellStyle);
          cell.setCellType(CellType.STRING);
          String key = columns[i];
          Field field = clz.getDeclaredField(key);
          field.setAccessible(true);
          Object value = field.get(entity);
          if (value != null) {
            String convername = convernames[i];
            if (convername != null && !"".equals(convername)) {
              value = Arrays.asList(convername.split(",")).get(Integer.parseInt(value.toString()));
            }
            cell.setCellValue(value.toString());
          } else {
            cell.setCellValue("");
          }
        }
        rowNum++;
      }
      workbook.write(byteArrayOutputStream);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (workbook != null) {
        try {
          workbook.close();
        } catch (Exception e) {
          e.printStackTrace();
        }
      }
    }
    return byteArrayOutputStream.toByteArray();
  }

}

  List<FilingUnit> list = managementUnitsService.getFilingUnitByUnitIds(unitIdArr);
    String[] titles =  {“name”,"age"};
    String[] columns = {"林","18"};
    return ExportUtils.getExportResultsEntity(filename, list, titles, columns);