1.依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13-beta1</version>
</dependency>
2.使用代码示例
/**
* 导出结算报表
* @param hospitalId
* @param startDate
* @param endDate
* @param response
*/
@Override
public void exportBalanceReport(Integer hospitalId, Date startDate, Date endDate, HttpServletResponse response) {
Map dataMap = this.getOrderBalanceInfoReport(hospitalId, startDate, endDate,1,1,true);
List<OrderBalanceInfoReportVo> orderBalanceInfoReportVos = (List<OrderBalanceInfoReportVo>) dataMap.get("data");//数据集
HSSFWorkbook workBook = new HSSFWorkbook();// 创建excel工作簿
// 设置表头字体样式
HSSFFont headfont = workBook.createFont();
headfont.setFontName("黑体");
headfont.setFontHeightInPoints((short) 22);// 字体大小
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
HSSFCellStyle headstyle = workBook.createCellStyle();
headstyle.setFont(headfont);
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
headstyle.setLocked(true);
headstyle.setWrapText(true);// 自动换行
// 设置列头字体样式
HSSFFont columnHeadFont = workBook.createFont();
columnHeadFont.setFontName("宋体");
columnHeadFont.setFontHeightInPoints((short) 11);
columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置列头的样式
HSSFCellStyle columnHeadStyle = workBook.createCellStyle();
columnHeadStyle.setFont(columnHeadFont);
columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
columnHeadStyle.setLocked(true);
columnHeadStyle.setWrapText(true);
HSSFFont font = workBook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 11);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置普通单元格样式
HSSFCellStyle style = workBook.createCellStyle();
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
style.setWrapText(true);
HSSFCellStyle centerstyle = workBook.createCellStyle();
centerstyle.setFont(font);
centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
centerstyle.setWrapText(true);
HSSFSheet sheet = workBook.createSheet();// 创建一个工作表sheet
// 对第一行做处理，标题的创建
HSSFRow row = sheet.createRow(0);// 创建第一行
row.setHeight((short) 600);// 设置表格行的高度
HSSFCell cell0 = row.createCell(0);
cell0.setCellValue(new HSSFRichTextString("结算统计报表"));// 设置表名
cell0.setCellStyle(headstyle);
//合并第一行单元格
CellRangeAddress range = new CellRangeAddress(0, 0, 0, 16); //这四个数字分别表示 要合并的第一个单元格所在行，要合并最后一个单元格所在行，要合并第一个单元格所在列，要合并最后一个单元格所在列
sheet.addMergedRegion(range);
//第二行列名
HSSFRow row2 = sheet.createRow(1);
row2.setHeight((short) 500);
HSSFCell cell = null;
String[] headers = { "订单编号", "床位号","管理老师", "护工姓名", "病人姓名", "日期", "天数", "实际订单金额",
"护工费结算", "管理费结算", "预收款", "预收款方式", "结算收款", "结算方式", "结算日期", "退款金额", "退款方式" };
// 插入第一行数据标题
for (int i = 0; i < headers.length; i++) {
cell = row2.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(columnHeadStyle);
sheet.setColumnWidth(i, 5500);
}
//插入数据行
if (orderBalanceInfoReportVos.size() > 0) {
for (int i = 0; i < orderBalanceInfoReportVos.size(); i++) {
OrderBalanceInfoReportVo orderBalanceInfoReportVo = orderBalanceInfoReportVos.get(i);
HSSFRow nextRow = sheet.createRow(i + 2); //数据行从第三行开始
nextRow.createCell(0).setCellValue(orderBalanceInfoReportVo.getOrderSn());
nextRow.createCell(1).setCellValue(orderBalanceInfoReportVo.getShipAddr());
nextRow.createCell(2).setCellValue(orderBalanceInfoReportVo.getManagerName());
nextRow.createCell(3).setCellValue(String.valueOf(orderBalanceInfoReportVo.getNursingName()));
nextRow.createCell(4).setCellValue(orderBalanceInfoReportVo.getShipName());
nextRow.createCell(5).setCellValue(String.valueOf(orderBalanceInfoReportVo.getDateInfo()));
nextRow.createCell(6).setCellValue(orderBalanceInfoReportVo.getActualDays());
nextRow.createCell(7).setCellValue(orderBalanceInfoReportVo.getOrderPrice());
nextRow.createCell(8).setCellValue(orderBalanceInfoReportVo.getWorkerSum());
nextRow.createCell(9).setCellValue(orderBalanceInfoReportVo.getManagementSum());
nextRow.createCell(10).setCellValue(orderBalanceInfoReportVo.getPrePaySum());
nextRow.createCell(11).setCellValue(orderBalanceInfoReportVo.getPrePayType());
nextRow.createCell(12).setCellValue(orderBalanceInfoReportVo.getBalancePaySum());
nextRow.createCell(13).setCellValue(orderBalanceInfoReportVo.getBalancePayType());
nextRow.createCell(14).setCellValue(orderBalanceInfoReportVo.getBalanceDate());
nextRow.createCell(15).setCellValue(orderBalanceInfoReportVo.getRefundSum());
nextRow.createCell(16).setCellValue(orderBalanceInfoReportVo.getRefundType());
}
}
