java 导出excel

1)jsp:

form:

<form id="excelForm" action="${path }/jsp/excel.jsp" method="post">
   <input type="hidden" name="title" value="月度风险监测指标监测结果汇总表——日常监控类指标" />
   <textarea id="excelInput" name="excel" rows="10" cols="10" style="display: none;"></textarea>
  </form>

 

事件:

<button class="button2" type="button" onclick="$('#excelInput').val($('#dataTable table').html());$('#excelForm').submit();">
        <span><em>导出 EXCEL</em> </span>
 </button>

 

$('#dataTable table').html()  获得页面的数据

 

 

数据显示:

<div id="dataTable">
    <table width="100%" border="0" cellpadding="0" cellspacing="0" class="table3">

　　页面数据

 

2)action:

//获得参数值

String xlsexport = request.getParameter("xlsexport");
List<Map<String, Object>> beanList = null;

// 数据库中的数据,页面上展示的数据

beanList = reportService.searchDailyMonitorMapList(params);

if (null != xlsexport && "true".equals(xlsexport)) {
   response.setContentType("application/ms-excel");// 设置页面ContentType的属性
   response.setHeader("Content-disposition", "attachment; filename="
     + response.encodeURL(new String(("日常风险监控").getBytes(),
       "iso8859_1")) + params.get("statdate") + ".xls");// 将页面导出成Excel文件
   export(new ExcelUtility(response.getOutputStream()), beanList);
   return null;
  }

 

3)导出export--excel

private void export(ExcelUtility eu, List<Map<String, Object>> beanList)
   throws Exception {
  try {

　　// 导出excel名
   eu.flipOver("关键风险监控");

　　// 表头名
   eu.printLabel("指标类别", new int[] { 20, 20 }, 2, 1);
   eu.printLabel("指标名称", 20);
   eu.printLabel("月度指标值", 15);
   eu.printLabel("指标值所在预警区间", 50);
   eu.printLabel("指标风险管理建议", 110);
   eu.printLabel("监控部门", 20);
   eu.printLabel("备注", 20);
   eu.println();

 

　　// excel数据
   List<Map<String, Object>> bean1List = null;
   List<Map<String, Object>> bean2List = null;
   List<Map<String, Object>> bean3List = null;
   List<Map<String, Object>> bean4List = null;
   for (Map<String, Object> bean : beanList) {
    Object children = bean.get("children");
    bean1List = (List<Map<String, Object>>) children;
    int s1 = 0;
    for (Map<String, Object> bean1 : bean1List) {
     children = bean1.get("children");
     bean2List = (List<Map<String, Object>>) children;
     int s2 = 0;
     for (Map<String, Object> bean2 : bean2List) {
      children = bean2.get("children");
      bean3List = (List<Map<String, Object>>) children;
      int s3 = 0;
      for (Map<String, Object> bean3 : bean3List) {
       children = bean3.get("children");
       bean4List = (List<Map<String, Object>>) children;
       int s4 = 0;
       for (Map<String, Object> bean4 : bean4List) {
        if (s1 == 0 && s2 == 0 && s3 == 0 && s4 == 0) {
         eu.printLabel((String) bean
           .get("measuretype1"), 1,
           (Integer) bean.get("length"));
        }
        if (s2 == 0 && s3 == 0 && s4 == 0) {
         eu.printLabel((String) bean1
           .get("measuretype2"), 1,
           (Integer) bean1.get("length"));
        }
        if (s4 == 0) {
         eu.printLabel((String) bean3
           .get("measurename"), 1,
           (Integer) bean3.get("length"));
        }
        try {
         eu.printNumber(Double
           .parseDouble((String) bean4
             .get("measurevalue")));
        } catch (Exception e) {
         eu.printLabel((String) bean4
           .get("measurevalue"));
        }
        

          if (s3 == 0 && s4 == 0) {
         eu.printLabel((String) bean2
           .get("departmentname"), 1,
           (Integer) bean2.get("length"));
        }

        eu
          .printLabel((String) bean4
            .get("measuredesc"));

        eu.println();

        s4++;
       }
       s3++;
      }
      s2++;
     }
     s1++;
    }
   }
  } finally {
   eu.close();
  }
 }

 

 

4)工具类--将list转为excel(ExcelUtility)

public class ExcelUtility {

 private static final org.apache.commons.logging.Log log = org.apache.commons.logging.LogFactory
   .getLog(ExcelUtility.class);

 // 普通
 private jxl.write.WritableCellFormat commonCellFormat;

 // 数字型
 private jxl.write.WritableCellFormat numberCellFormat;

 public jxl.write.WritableCellFormat getCommonCellFormat() {
  if (null == commonCellFormat) {
   setCommonCellFormat(defaultWritableCellFormat());
  }
  return commonCellFormat;
 }

 /**
  * 普通单元格样式
  */
 public void setCommonCellFormat(
   jxl.write.WritableCellFormat commonCellFormat) {
  log.debug("commonCellFormat已经创建!");
  this.commonCellFormat = commonCellFormat;
 }

 public jxl.write.WritableCellFormat getNumberCellFormat() {
  if (null == numberCellFormat) {
   setNumberCellFormat(defaultWritableCellFormat());
   try {
    numberCellFormat.setAlignment(jxl.format.Alignment.RIGHT);
   } catch (Exception e) {
    e.printStackTrace();
   }
  }
  return numberCellFormat;
 }

 /**
  * 数字型单元格样式
  */
 public void setNumberCellFormat(
   jxl.write.WritableCellFormat numberCellFormat) {
  log.debug("numberCellFormat已经创建!");
  this.numberCellFormat = numberCellFormat;
 }

 public jxl.write.WritableFont defaultWritableFont() {
  jxl.write.WritableFont font = new jxl.write.WritableFont(
    jxl.write.WritableFont.TIMES);
  try {
   // 设置字体大小
   font.setPointSize(12);
   // 设置非粗体
   font.setBoldStyle(jxl.write.WritableFont.NO_BOLD);
   // 设置无下划线
   font.setUnderlineStyle(jxl.format.UnderlineStyle.NO_UNDERLINE);
   // 设置黑色字
   font.setColour(jxl.format.Colour.BLACK);
   // 非斜体设置
   font.setItalic(false);
  } catch (Exception e) {
   e.printStackTrace();
  }
  return font;
 }

 public jxl.write.WritableCellFormat defaultWritableCellFormat(
   jxl.write.WritableFont font) {
  jxl.write.WritableCellFormat format = null;
  try {
   format = new jxl.write.WritableCellFormat(font);
   format.setAlignment(jxl.format.Alignment.LEFT);
   // 把垂直对齐方式指定为居中
   format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
   // 设置自动换行
   format.setWrap(false);
  } catch (Exception e) {
   e.printStackTrace();
  }
  return format;
 }

 public jxl.write.WritableCellFormat defaultWritableCellFormat(
   jxl.write.WritableFont font, jxl.write.NumberFormat nf) {
  jxl.write.WritableCellFormat format = null;
  try {
   format = new jxl.write.WritableCellFormat(font, nf);
   format.setAlignment(jxl.format.Alignment.LEFT);
   // 把垂直对齐方式指定为居中
   format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
   // 设置自动换行
   format.setWrap(false);
  } catch (Exception e) {
   e.printStackTrace();
  }
  return format;
 }

 public jxl.write.WritableCellFormat defaultWritableCellFormat() {
  return defaultWritableCellFormat(defaultWritableFont());
 }

 public jxl.write.WritableCellFormat defaultWritableCellFormat(
   jxl.write.NumberFormat nf) {
  return defaultWritableCellFormat(defaultWritableFont(), nf);
 }

 // 一下是详细

 private jxl.write.WritableWorkbook workbook;

 private jxl.write.WritableSheet sheet;

 private int page = 0;

 private int row = 0;

 private int num = 0;

 public ExcelUtility(java.io.OutputStream outputStream) throws Exception {
  workbook = jxl.Workbook.createWorkbook(outputStream);
 }

 /**
  * 建立一个新的页面
  *
  * @param title
  *            标题
  * @param format
  *            默认格式
  * @throws Exception
  */
 public void flipOver(String title) throws Exception {
  sheet = workbook.createSheet(title, page++);
 }

 /**
  * 建立一个新行
  *
  * @param format
  *            默认格式
  */
 public void println() {
  row = row + 1;
  num = 0;
 }

 /**
  * 普通型单元格
  *
  * @param value
  * @throws Exception
  */
 public void printLabel(String value) throws Exception {
  printLabel(value, getCommonCellFormat(), new int[] {}, 1, 1);
 }

 /**
  * 普通型单元格
  *
  * @param value
  * @throws Exception
  */
 public void printLabel(String value, int width) throws Exception {
  printLabel(value, getCommonCellFormat(), new int[] { width }, 1, 1);
 }

 /**
  * 普通型单元格
  *
  * @param value
  * @throws Exception
  */
 public void printLabel(String value, int colspan, int rowspan)
   throws Exception {
  printLabel(value, getCommonCellFormat(), new int[] {}, colspan, rowspan);
 }

 /**
  * 普通型单元格
  *
  * @param value
  * @throws Exception
  */
 public void printLabel(String value, int[] widths, int colspan, int rowspan)
   throws Exception {
  printLabel(value, getCommonCellFormat(), widths, colspan, rowspan);
 }

 /**
  * 普通型单元格
  *
  * @param value
  * @throws Exception
  */
 public void printLabel(String value, jxl.write.WritableCellFormat format)
   throws Exception {
  printLabel(value, format, new int[] {}, 1, 1);
 }

 /**
  * 普通型单元格
  *
  * @param value
  * @throws Exception
  */
 public void printLabel(String value, jxl.write.WritableCellFormat format,
   int width) throws Exception {
  printLabel(value, format, new int[] { width }, 1, 1);
 }

 /**
  * 普通型单元格
  *
  * @param value
  * @throws Exception
  */
 public void printLabel(String value, jxl.write.WritableCellFormat format,
   int colspan, int rowspan) throws Exception {
  printLabel(value, format, new int[] {}, colspan, rowspan);
 }

 /**
  * 普通型单元格
  *
  * @param value
  * @throws Exception
  */
 public void printLabel(String value, jxl.write.WritableCellFormat format,
   int[] widths, int colspan, int rowspan) throws Exception {
  for (jxl.Range range : sheet.getMergedCells()) {
   jxl.Cell cell1 = range.getTopLeft();
   jxl.Cell cell2 = range.getBottomRight();
   if (num >= cell1.getColumn() && num <= cell2.getColumn()
     && row >= cell1.getRow() && row <= cell2.getRow()) {
    num = cell2.getColumn() + 1;
   }
  }
  for (int i = 0; i < widths.length; i++) {
   sheet.setColumnView(num + i, widths[i]);
  }
  if (colspan > 1 || rowspan > 1) {
   sheet.mergeCells(num, row, num + colspan - 1, row + rowspan - 1);
  }
  sheet.addCell(new jxl.write.Label(num, row, value, format));
  num = num + 1;
 }

 /**
  * 数字型单元格
  *
  * @param value
  * @param format
  * @throws Exception
  */
 public void printNumber(Double value) throws Exception {
  printNumber(value, getNumberCellFormat(), new int[] {}, 1, 1);
 }

 /**
  * 数字型单元格
  *
  * @param value
  * @param format
  * @throws Exception
  */
 public void printNumber(Double value, int width) throws Exception {
  printNumber(value, getNumberCellFormat(), new int[] { width }, 1, 1);
 }

 /**
  * 数字型单元格
  *
  * @param value
  * @param format
  * @throws Exception
  */
 public void printNumber(Double value, int colspan, int rowspan)
   throws Exception {
  printNumber(value, getNumberCellFormat(), new int[] {}, colspan,
    rowspan);
 }

 /**
  * 数字型单元格
  *
  * @param value
  * @param format
  * @throws Exception
  */
 public void printNumber(Double value, int[] widths, int colspan, int rowspan)
   throws Exception {
  printNumber(value, getNumberCellFormat(), widths, colspan, rowspan);
 }

 /**
  * 数字型单元格
  *
  * @param value
  * @param format
  * @throws Exception
  */
 public void printNumber(Double value, jxl.write.WritableCellFormat format)
   throws Exception {
  printNumber(value, format, new int[] {}, 1, 1);
 }

 /**
  * 数字型单元格
  *
  * @param value
  * @param format
  * @throws Exception
  */
 public void printNumber(Double value, jxl.write.WritableCellFormat format,
   int width) throws Exception {
  printNumber(value, format, new int[] { width }, 1, 1);
 }

 /**
  * 数字型单元格
  *
  * @param value
  * @param format
  * @throws Exception
  */
 public void printNumber(Double value, jxl.write.WritableCellFormat format,
   int colspan, int rowspan) throws Exception {
  printNumber(value, format, new int[] {}, colspan, rowspan);
 }

 /**
  * 数字型单元格
  *
  * @param value
  * @param format
  * @throws Exception
  */
 public void printNumber(Double value, jxl.write.WritableCellFormat format,
   int[] widths, int colspan, int rowspan) throws Exception {
  for (jxl.Range range : sheet.getMergedCells()) {
   jxl.Cell cell1 = range.getTopLeft();
   jxl.Cell cell2 = range.getBottomRight();
   if (num >= cell1.getColumn() && num <= cell2.getColumn()
     && row >= cell1.getRow() && row <= cell2.getRow()) {
    num = cell2.getColumn() + 1;
   }
  }
  for (int i = 0; i < widths.length; i++) {
   sheet.setColumnView(num + i, widths[i]);
  }
  if (colspan > 1 || rowspan > 1) {
   sheet.mergeCells(num, row, num + colspan - 1, row + rowspan - 1);
  }
  sheet.addCell(new jxl.write.Number(num, row, value, format));
  num = num + 1;
 }

 public void close() {
  try {
   workbook.write();
   workbook.close();
  } catch (Exception e) {
   e.printStackTrace();
  }
 }
}