flex+java将数据库里的数据导出到指定目录下excel表里(poi)

时间:2022-05-15 11:47:36

数据写入到excel中采用的是Apache POI:

//java后台的一个工具类(该工具类适用于为不同字段添加,方便)

/* 下面这个方法是将list转换为Excel工作表的 */
  public static HSSFWorkbook getWorkbook(List<ExporTable> list)
    throws Exception {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("sheet1");
    String[] columnNames;
    String[] columnMethods;

    // 首先,我们读取list中的第一个元素,根据它来确定工作表的列名,以及输出数据所对应的方法数组
    ExporTable exp = list.get(0);
    columnNames = exp.getColumnNames();
    columnMethods = exp.getColumnMethods();

    HSSFRow row = sheet.createRow(0); // 创建第1行,也就是输出表头
    HSSFCell cell;
    for (int i = 0; i < columnNames.length; i++) {
      cell = row.createCell(i); // 创建第i列
      cell.setCellValue(new HSSFRichTextString(columnNames[i]));
    }

    // 下面是输出各行的数据
    for (int i = 0; i < list.size(); i++) {
      exp = (ExporTable) list.get(i);
      row = sheet.createRow(i + 1);// 创建第i+1行
      for (int j = 0; j < columnMethods.length; j++) {
        cell = row.createCell(j);// 创建第j列
        Method method;
        method = exp.getClass().getMethod(columnMethods[j]); // 这里用到了反射机制,通过方法名来取得对应方法返回的结果对象
        Object obj = method.invoke(exp);
        if (obj != null) {
          cell.setCellValue(obj.toString());
        }
      }
    }
    return workbook;
}

/**
* 将ExportBean转换为Excel工作表
*
* @param exportBeans 集合,每个元素代表一个sheet
* @return
* @throws Exception
*/
public static HSSFWorkbook getWorkbooks(ExportBean<?>... exportBeans)
    throws Exception {

    HSSFWorkbook workbook = new HSSFWorkbook();

    HSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

    for (ExportBean<?> eb : exportBeans) {
    HSSFSheet sheet = workbook.createSheet(eb.getSheetName());

    String[] headers = eb.getHeaders();

    HSSFRow row = sheet.createRow(0); // 创建第1行,也就是输出表头
    HSSFCell cell;

    // 设置表头粗体样式
    HSSFFont font = workbook.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    HSSFCellStyle cellStyleHeader = workbook.createCellStyle();
    cellStyleHeader.setFont(font);

    for (int i = 0; i < headers.length; i++) {
    // 设置列宽
    sheet.setColumnWidth((short) i, 6000);
    cell = row.createCell(i); // 创建第i列
    cell.setCellStyle(cellStyleHeader);

    cell.setCellValue(new HSSFRichTextString(headers[i]));
    }

    String[] getMethods = eb.getGetMethods();
    List rows = eb.getRows();

    // 下面是输出各行的数据
    for (int i = 0; i < rows.size(); i++) {
      Object rowData = rows.get(i);
      row = sheet.createRow(i + 1);// 创建第i+1行

      for (int j = 0; j < getMethods.length; j++) {

      cell = row.createCell(j);// 创建第j列
      // cell.setCellStyle(cellStyle);
      Method method = null;
      try {
        method = rowData.getClass().getMethod(getMethods[j]); // 这里用到了反射机制,通过方法名来取得对应方法返回的结果对象
      } catch (Exception e) {
        // TODO: handle exception
        e.printStackTrace();
      }
        Object obj = method.invoke(rowData);
          if (obj != null) {
        // cell.setCellType(HSSFCell.);
        if (obj instanceof java.util.Date) {
            cell.setCellValue((java.util.Date) obj);
            cell.setCellStyle(cellStyle);
        } else if (obj instanceof BigDecimal) {
            cell.setCellValue(((BigDecimal) obj).longValue());
        } else {
            cell.setCellValue(obj.toString());
        }
      }
    }
  }

}
return workbook;
}

    // 将Workbook写入到InputStream
    public static InputStream workbook2InputStream(HSSFWorkbook workbook,
        ByteArrayOutputStream baos, String fileName) throws Exception {
        workbook.write(baos);
        baos.flush();
        byte[] aa = baos.toByteArray();
        InputStream excelStream = new ByteArrayInputStream(aa, 0, aa.length);
        return excelStream;
    }

    // 将Workbook写入到byte数组
    public static byte[] workbook2ByteArray(HSSFWorkbook workbook,
        ByteArrayOutputStream baos) throws Exception {
        workbook.write(baos);
        baos.flush();
        byte[] aa = baos.toByteArray();
        return aa;
    }

/**
* 考虑到多sheet情况暂时加个数据结构封装下吧
* @author 王云超
* 时间 2014-7-16 下午5:30:23
*/
  public class ExportBean<T> {

    private String sheetName ; //sheet名称

    private String[] headers; //表头
    private String[] getMethods; //方法集合
    private List<T> rows; //数据行

    public String[] getGetMethods() {
        return getMethods;
    }
    public void setGetMethods(String[] getMethods) {
        this.getMethods = getMethods;
    }
    public String getSheetName() {
        return sheetName;
    }
    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public String[] getHeaders() {
        return headers;
    }
    public void setHeaders(String[] headers) {
        this.headers = headers;
    }
    public List<T> getRows() {
        return rows;
    }
    public void setRows(List<T> rows) {
        this.rows = rows;
    }
}

//java代码:

public void DbToExcels(){

    byte[] bytes = null;

    try {

      ExportBean<CirCuit> eb = new ExportBean<CirCuit>();
      eb.setHeaders(new String[] { "id","名字","时间" });
      eb.setGetMethods(new String[] {"getId","getCircuit_type","getSystem_id"});
      List<CirCuit> list = cirCuitBo.findAll();
      eb.setRows(list);
      eb.setSheetName("信息");
      HSSFWorkbook workbook = PoiUtil.getWorkbooks(eb);
      ByteArrayOutputStream baos = new ByteArrayOutputStream();
      bytes = PoiUtil.workbook2ByteArray(workbook, baos);

      } catch (Exception e) {
        e.printStackTrace();
        log.error("导出数据失败",e);
      }
        return bytes;
      }

    //flex代码

    Alert.show("是否要保存文件!","提示",Alert.YES|Alert.NO,null,function(evt){
      if(evt.detail == Alert.YES){
        var fileRef:FileReference = new FileReference();
        fileRef.save(data.result,"信息表.xls");
      }
    });