导出Excel表格

使用导出Excel数据的插件jexcelapi

 

一、工具类

/**
  * 导出Excel表格
  *
  * @param request
  *            请求
  * @param response
  *            响应
  * @param list
  *            需要写入Excel的数据
  * @param title
  *            Excel的文件名(不包含后缀名)
  * @param sheetTitle
  *            Excel的Sheet名
  * @param header
  *            第一行头部标题信息
  * @param fields
  *            需要查询的字段(便于HashMap排序)
  */
 @SuppressWarnings("unchecked")
 public void Export(HttpServletRequest request,
   HttpServletResponse response, List<Map> list, String title,
   String sheetTitle, String[] header, String[] fields) {
  OutputStream os = null;
  try {
   response.reset(); // 清空输出流
   os = response.getOutputStream(); // 取得输出流
   String fileName = title + ".xls";// 导出文件名
   response.setHeader("Content-disposition", "attachment; filename="
     + new String(fileName.getBytes(), "ISO-8859-1")); // 设定输出文件头
   response.setContentType("application/msexcel"); // 定义输出类型
   WritableWorkbook workbook = Workbook.createWorkbook(os);
   WritableSheet sheet = workbook.createSheet(sheetTitle, 0);
   WritableFont wfc = new WritableFont(WritableFont.ARIAL, 10,
     WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
     jxl.format.Colour.BLACK);
   @SuppressWarnings("unused")
   WritableCellFormat format = new WritableCellFormat(wfc);
   WritableCellFormat wcfFC = new jxl.write.WritableCellFormat();
   // 行居中
   format.setAlignment(Alignment.CENTRE);
   // 列居中
   format.setVerticalAlignment(VerticalAlignment.CENTRE);
   Label labelCk = null;
   // 3、生成Excel数据
   // 生成头部标题行
   for (int i = 0; i < fields.length; i++) {
    labelCk = new Label(i, 0, header[i], format);
    sheet.addCell(labelCk);
   }
   int colnum = 0; // 用于循环时Excel的列号
   int rownum = 1;// 用于循环时Excel的行号
   for (Map row : list) {
    colnum = 0;
    for (int i = 0; i < fields.length; i++) {
     String value = (row.get(fields[i]) == null ? "" : row.get(
       fields[i]).toString());
     labelCk = new Label(colnum, rownum, value, wcfFC);
     sheet.addCell(labelCk);
     colnum++;
    }
    rownum++;
   }
   // 设置每列为70的宽度
   for (int i = 0; i < sheet.getColumns(); i++) {
    sheet.setColumnView(i, 26);
   }
   workbook.write();
   workbook.close();
  } catch (Exception ex) {// 捕捉异常
   ex.printStackTrace();
  }
 }

 

 

 

二、业务类

 

List<Map> list = DataBase.select(sql);
  // 导出Excel表格
  ExportExcel excel = new ExportExcel();
  excel.Export(request, response, list, "内容列表" + DataBase.getTimeNow().substring(0, 10),
    "内容列表", new String[] {"编号","名称"}, new String[] {
      "ID", "name"});