/**
* 向浏览器输出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();
}
}
@Test
public void testTableHeaderUidNoId() {
CharStream stream = new ANTLRStringStream("{header: 3}");
UdlLexer lexer = new UdlLexer(stream);
TokenStream tokenStream = new CommonTokenStream(lexer);
UdlParser parser = new UdlParser(tokenStream);
try {
MetaData data = parser.uid();
assertNotNull(data);
assertTrue(data instanceof TableHeaderMetaData);
TableHeaderMetaData th = (TableHeaderMetaData) data;
assertEquals("_3", th.getId());
assertEquals("3", th.getIndex().getValue());
assertEquals(IndexType.VAL, th.getIndex().getType());
} catch (RecognitionException e) {
fail(e.getMessage());
}
}
/**
* 写入工作表
*
* @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;
}
