上个礼拜五做了这个小项目,完了,生成Excel的效果非常不好,今天忙活了一天,还是重新添加了一些方法,才得以成功运行出来相对完美的效果。具体效果看下面讲解。
一、创建工作簿和工作表
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
HSSFSheet sheet = workbook.createSheet("数据库表"); // 创建工作表
二、循环遍历数据库中的数据
CellStyle style = setStyle(workbook);
CellStyle style2 = setCellStyle_1(workbook);
dao.queryTables().forEach(item -> {
// 设置字段的格式
row++;// 插入表信息空行
HSSFRow rowm = sheet.createRow(row);
CellRangeAddress region = new CellRangeAddress(row, row, 1, 2);
sheet.addMergedRegion(region);
CellRangeAddress region2 = new CellRangeAddress(row, row, 4, 5);
sheet.addMergedRegion(region2);
HSSFCell cell = null;
cell = rowm.createCell(0);
cell.setCellValue("表名");
cell.setCellStyle(style2);
cell = rowm.createCell(1);
cell.setCellValue(item.getTable_name());
cell.setCellStyle(style);
cell = rowm.createCell(2);
cell.setCellStyle(style);
cell = rowm.createCell(3);
cell.setCellValue("描述");
cell.setCellStyle(style2);
cell = rowm.createCell(4);
cell.setCellValue(item.getTable_comment());
cell.setCellStyle(style);
cell = rowm.createCell(5);
cell.setCellStyle(style);
row++;// 表头
rowm = sheet.createRow(row);
String titles[] = new String[] { "字段名", "字段描述", "数据类型", "可为空", "是主键", "规则" };
for (int i = 0; i < titles.length; i++) {
rowm.createCell(i).setCellValue(titles[i]);
rowm.getCell(i).setCellStyle(style2);
}
dao.queryFields(item.getTable_name()).forEach(ite -> {
row++;// 字段
//log.info(ite.toString());
// 遍历数据并放入字段
HSSFRow rowf = sheet.createRow(row);
String content[] = new String[] { ite.getColumn_name(), ite.getColumn_comment(), ite.getColumn_type(),
ite.getIs_nullable(), ite.getPri(), "无" };
for (int j = 0; j < content.length; j++) {
rowf.createCell(j).setCellValue(content[j]);
rowf.getCell(j).setCellStyle(style);
}
});
row++;
});
三、设置单元格的格式
private CellStyle setStyle(HSSFWorkbook workbook) {
CellStyle style = workbook.createCellStyle();
style.setBorderBottom(BorderStyle.THIN); // 下边框
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);// 左边框
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(BorderStyle.THIN);// 上边框
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderRight(BorderStyle.THIN);// 右边框
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
return style;
}
private CellStyle setCellStyle_1(HSSFWorkbook workbook) {
CellStyle style = setStyle(workbook);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 增加颜色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
四、遍历之后并写入文件
dao.queryFields(item.getTable_name()).forEach(ite -> {
row++;// 字段
//log.info(ite.toString());
// 遍历数据并放入字段
HSSFRow rowf = sheet.createRow(row);
String content[] = new String[] { ite.getColumn_name(), ite.getColumn_comment(), ite.getColumn_type(),
ite.getIs_nullable(), ite.getPri(), "无" };
for (int j = 0; j < content.length; j++) {
rowf.createCell(j).setCellValue(content[j]);
rowf.getCell(j).setCellStyle(style);
}
});
row++;
});
File file = getTime();
workbook.write(file);
workbook.close();
tool.copyFile(file);
}
六、具体的项目其他逻辑层还需要近一步的整理。谢谢。