/** * 向浏览器输出JSON数据 * * @param * @return void */ public void outDataToBrowser(TableData tableData) { StringBuffer outData = new StringBuffer(); // 向前台输出数据 outData.append("{pageInfo: {totalRowNum: " + tableData.getTotalRows() + "},"); outData.append("data: ["); boolean isFirst = true; TableHeaderMetaData headerMetaData = tableData.getTableHeader(); List<TableDataRow> dataRows = tableData.getRows(); try { for (TableDataRow dataRow : dataRows) { List<TableDataCell> dataCells = dataRow.getCells(); int size = dataCells.size(); if (!isFirst) { outData.append(",{"); for (int i = 0; i < size; i++) { outData.append( headerMetaData.getColumnAt(i).getId() + ": '" + dataCells.get(i).getValue() + "',"); } int index = outData.lastIndexOf(","); outData.deleteCharAt(index); outData.append("}"); } else { outData.append("{"); for (int i = 0; i < size; i++) { outData.append( headerMetaData.getColumnAt(i).getId() + ": '" + dataCells.get(i).getValue() + "',"); } int index = outData.lastIndexOf(","); outData.deleteCharAt(index); outData.append("}"); isFirst = false; } } } catch (Exception e) { e.printStackTrace(); } outData.append("]"); outData.append("}"); try { out.print(outData.toString()); out.flush(); out.close(); } catch (IOException e) { e.printStackTrace(); } }
/** * 写入工作表 * * @param wb Excel工作簿 * @param title Sheet工作表名称 * @param styles 表头样式 * @param creator 创建人 * @param tableData 表格数据 * @throws Exception */ public HSSFWorkbook writeSheet( HSSFWorkbook wb, HashMap<String, HSSFCellStyle> styles, String creator, List<TableData> tableDataLst) throws Exception { SimpleDateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分"); String create_time = formater.format(new Date()); int cnt = 1; for (TableData tableData : tableDataLst) { String sheetTitle = tableData.getSheetTitle(); sheetTitle = sheetTitle == null || sheetTitle.equals("") ? "sheet" + cnt : sheetTitle; cnt++; TableHeaderMetaData headerMetaData = tableData.getTableHeader(); // 获得HTML的表头元素 HSSFSheet sheet = wb.createSheet(sheetTitle); // 在Excel工作簿中建一工作表 sheet.setDisplayGridlines(false); // 设置表标题是否有表格边框 wb.cloneSheet(0); // 创建标题 HSSFRow row = sheet.createRow(0); // 创建新行 HSSFCell cell = row.createCell(0); // 创建新列 int rownum = 0; cell.setCellValue(new HSSFRichTextString(sheetTitle)); HSSFCellStyle style = styles.get("TITLE"); // 设置标题样式 if (style != null) cell.setCellStyle(style); sheet.addMergedRegion( new CellRangeAddress( 0, 0, 0, headerMetaData.getColumnCount() - 1)); // 合并标题行:起始行号,终止行号, 起始列号,终止列号 // 创建副标题 row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString("创建人:")); style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString(creator)); style = styles.get("SUB_TITLE2"); if (style != null) cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(new HSSFRichTextString("创建时间:")); style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(3); style = styles.get("SUB_TITLE2"); cell.setCellValue(new HSSFRichTextString(create_time)); if (style != null) cell.setCellStyle(style); rownum = 3; // 如果rownum = 1,则去掉创建人、创建时间等副标题;如果rownum = 0, 则把标题也去掉 HSSFCellStyle headerstyle = styles.get("TABLE_HEADER"); int colnum = 0; for (int i = 0; i < headerMetaData.getOriginColumns().size(); i++) { TableColumn tc = headerMetaData.getOriginColumns().get(i); if (i != 0) { colnum += headerMetaData.getOriginColumns().get(i - 1).getLength(); } generateColumn(sheet, tc, headerMetaData.maxlevel, rownum, colnum, headerstyle); } rownum += headerMetaData.maxlevel; List<TableDataRow> dataRows = tableData.getRows(); int index = 0; for (TableDataRow dataRow : dataRows) { row = sheet.createRow(rownum); List<TableDataCell> dataCells = dataRow.getCells(); int size = headerMetaData.getColumns().size(); index = -1; for (int i = 0; i < size; i++) { TableColumn tc = headerMetaData.getColumns().get(i); if (!tc.isVisible()) continue; index++; createCell(row, tc, dataCells, i, index, styles); } rownum++; } // 设置前两列根据数据自动列宽 for (int c = 0; c < headerMetaData.getColumns().size(); c++) { sheet.autoSizeColumn((short) c); String t = headerMetaData.getColumns().get(c).getDisplay(); if (sheet.getColumnWidth(c) < t.length() * 256 * 3) sheet.setColumnWidth(c, t.length() * 256 * 3); } sheet.setGridsPrinted(true); } return wb; }
/** * 写入工作表 * * @param wb Excel工作簿 * @param title Sheet工作表名称 * @param styles 表头样式 * @param creator 创建人 * @param tableData 表格数据 * @throws Exception */ public HSSFWorkbook writeSheet( HSSFWorkbook wb, String title, HashMap<String, HSSFCellStyle> styles, String creator, TableData tableData, String subTitleName) throws Exception { TableHeaderMetaData headerMetaData = tableData.getTableHeader(); // 获得HTML的表头元素 SimpleDateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分"); String create_time = formater.format(new Date()); HSSFSheet sheet = wb.createSheet(title); // 在Excel工作簿中建一工作表 sheet.setDisplayGridlines(false); // 设置表标题是否有表格边框 // 创建标题 HSSFRow row = sheet.createRow(0); // 创建新行 HSSFCell cell = row.createCell(0); // 创建新列 int rownum = 0; cell.setCellValue(new HSSFRichTextString(title)); HSSFCellStyle style = styles.get("TITLE"); // 设置标题样式 if (style != null) cell.setCellStyle(style); sheet.addMergedRegion( new CellRangeAddress( 0, 0, 0, headerMetaData.getColumnCount() - 1)); // 合并标题行:起始行号,终止行号, 起始列号,终止列号 // 创建副标题 row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString(subTitleName)); // updated by zdwang 2014-02-21 style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString(creator)); style = styles.get("SUB_TITLE2"); if (style != null) cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(new HSSFRichTextString("创建时间:")); style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(3); style = styles.get("SUB_TITLE2"); cell.setCellValue(new HSSFRichTextString(create_time)); if (style != null) cell.setCellStyle(style); rownum = 3; // 如果rownum = 1,则去掉创建人、创建时间等副标题;如果rownum = 0, 则把标题也去掉 HSSFCellStyle headerstyle = styles.get("TABLE_HEADER"); int colnum = 0; for (int i = 0; i < headerMetaData.getOriginColumns().size(); i++) { TableColumn tc = headerMetaData.getOriginColumns().get(i); if (i != 0) { colnum += headerMetaData.getOriginColumns().get(i - 1).getLength(); } generateColumn(sheet, tc, headerMetaData.maxlevel, rownum, colnum, headerstyle); } rownum += headerMetaData.maxlevel; List<TableDataRow> dataRows = tableData.getRows(); HashMap<Integer, Integer> counter = new HashMap<Integer, Integer>(); HashMap<Integer, String> word = new HashMap<Integer, String>(); int index = 0; for (TableDataRow dataRow : dataRows) { row = sheet.createRow(rownum); List<TableDataCell> dataCells = dataRow.getCells(); int size = headerMetaData.getColumns().size(); index = -1; for (int i = 0; i < size; i++) { TableColumn tc = headerMetaData.getColumns().get(i); if (!tc.isVisible()) continue; index++; String value = dataCells.get(i).getValue(); if (tc.isGrouped()) { String w = word.get(index); if (w == null) { word.put(index, value); counter.put(index, 1); createCell(row, tc, dataCells, i, index, styles); } else { if (w.equals(value)) { counter.put(index, counter.get(index) + 1); } else { stopGrouping(sheet, word, counter, index, size, rownum, styles.get("STRING")); word.put(index, value); counter.put(index, 1); createCell(row, tc, dataCells, i, index, styles); } } } else { createCell(row, tc, dataCells, i, index, styles); } } rownum++; } stopGrouping(sheet, word, counter, 0, index, rownum, styles.get("STRING")); // 设置前两列根据数据自动列宽 for (int c = 0; c < headerMetaData.getColumns().size(); c++) { sheet.autoSizeColumn((short) c); String t = headerMetaData.getColumns().get(c).getDisplay(); if (sheet.getColumnWidth(c) < t.length() * 256 * 3) sheet.setColumnWidth(c, t.length() * 256 * 3); } sheet.setGridsPrinted(true); return wb; }