近来使用poi做了一个导出单据的功能。
@RequestMapping(path = "/export_client_check", method = {RequestMethod.GET})
public ArapFileResponse getExportData(@RequestParam(name = "ids", required = true) String[] ids) {
ArapFileResponse response = new ArapFileResponse();
response.setSuccess(true);
String filepath = null;
try {
filepath = arapClientCheckExportService.exportClientCheck(Lists.newArrayList(ids));
} catch (Exception e) {
response.setSuccess(false);
System.out.println(e);
response.errors = Lists.newArrayList(EXPORT_MESSAGE);
}
response.setFilePath(filepath);
return response;
}
public String exportClientCheck(List<String> ids) throws Exception {
List<ClientChecked> clientCheckedList = clientCheckedService.findByIdIn(ids);
ByteArrayOutputStream output = new ByteArrayOutputStream();
String tempalatePath = "/template/ClientCheckTemplate.xls";
ClassPathResource classPathStyle = new ClassPathResource(tempalatePath);
InputStream inputStreamBody = classPathStyle.getInputStream();
POIFSFileSystem fs = new POIFSFileSystem(inputStreamBody);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
for (int i = 1; i < clientCheckedList.size(); i++) {
// i 从1开始先打印后面的sheet 否则会出现格式混乱
List<ArApBill> billList = billService.findCheckedNo(clientCheckedList.get(i).getCheckedNo());
// 读取了模板内所有sheet内容
HSSFSheet createSheet = wb.createSheet(clientCheckedList.get(i).getCheckedNo());
poiUtilService.copySheet(createSheet, sheet, wb, wb);
setSheetValueClientCheck(billList, wb, createSheet);
}
List<ArApBill> billListFirst = billService.findCheckedNo(clientCheckedList.get(0).getCheckedNo());
wb.setSheetName(0, clientCheckedList.get(0).getCheckedNo());
setSheetValueClientCheck(billListFirst, wb, sheet);
wb.write(output);
String strName = "RBill" + DateTime.now().toString("yyyyMMddHHmmss");
arapBillExportService.witerFile(strName, output);
return REQUEST_PATH + strName;
}
void setSheetValueClientCheck(List<ArApBill> billList, HSSFWorkbook wb,
HSSFSheet sheet) throws IOException {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");
HSSFFont createFont = wb.createFont();
createFont.setFontName("宋体");
createFont.setFontHeightInPoints((short) 9);
//单元格样式处理
HSSFCellStyle cellStyle = wb.createCellStyle();
HSSFDataFormat format = wb.createDataFormat();
cellStyle.setDataFormat(format.getFormat("#,##0.00"));
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
cellStyle.setFont(createFont);
HSSFCellStyle cellBoldStyle = wb.createCellStyle();
cellBoldStyle.setDataFormat(format.getFormat("#,##0.00"));
cellBoldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
cellBoldStyle.setFont(createFont);
excelService.setExcelValue(billList.get(0).getReceivableName(), sheet, 4, 1);
List<SailingScheduleBean> beanList = Lists.newArrayList();
SailingScheduleBean sailingScheduleForFind;
for (int i = 0; i < billList.size(); i++) {
sailingScheduleForFind = sailingScheduleBeanService.find(SailingScheduleBean.class, billList.get(i).getSailingScheduleId());
beanList.add(sailingScheduleForFind);
}
sortBeanListByEtdArrivedTime(beanList);
String arriveTimeStr = simpleDateFormat.format(beanList.get(0).getEtdArrivedTime());
sortBeanListByEtdDepartTime(beanList);
String etdTimeStr = simpleDateFormat.format(beanList.get(0).getEtdDepartTime());
excelService.setExcelValue(arriveTimeStr + "-" + etdTimeStr, sheet, 4, 7);
excelService.setExcelValue(billList.get(0).getCheckedNo(), sheet, 4, 11);
setClientCheckListValue(billList.get(0), sheet,cellBoldStyle,cellStyle, billList);
CellRangeAddress region = new CellRangeAddress(5, 5, 8, 9);
sheet.addMergedRegion(region);
excelService.setExcelValue("P/DISB", sheet, 5, 8);
excelService.setExcelValue("BALANCE", sheet, 5, 11);
excelService.setExcelValue("RECEIVED", sheet, 5, 10);
// 插入图片
HSSFPatriarch patriarch = (HSSFPatriarch) sheet
.createDrawingPatriarch();
arapBillExportService.insetIntoPictureClient(patriarch, wb, beanList.get(0).getOrgGroupCode());
}
private void setClientCheckListValue(ArApBill bill, HSSFSheet sheet,HSSFCellStyle cellBoldStyle,HSSFCellStyle cellStyle,
List<ArApBill> billList) {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");
BigDecimal totalCnyAmount = new BigDecimal(0);
BigDecimal totalHkdAmount = new BigDecimal(0);
BigDecimal totalUsdAmount = new BigDecimal(0);
List<ClientCheckedBean> checkedBeanList = Lists.newArrayList();
for (int i = 0; i < billList.size(); i++) {
ClientCheckedBean clientCheckedBean = new ClientCheckedBean();
SailingScheduleBean sailingScheduleForFind = sailingScheduleBeanService.find(SailingScheduleBean.class, billList.get(i).getSailingScheduleId());
String shipCnName = sailingScheduleForFind.getShipCnName();
String shipEnName = sailingScheduleForFind.getShipEnName();
String impVoyageCode = sailingScheduleForFind.getImpVoyageCode();
String expVoyageCode = sailingScheduleForFind.getExpVoyageCode();
String voyageMessage = billExportCommonService.getNullToBlank(shipCnName) +billExportCommonService.getNullToBlank(shipEnName) + " "
+ billExportCommonService.getNullToBlank(impVoyageCode) + "/"
+ billExportCommonService.getNullToBlank(expVoyageCode);
clientCheckedBean.setVoyageMessage(voyageMessage);
clientCheckedBean.setEtdDate(simpleDateFormat.format(sailingScheduleForFind.getEtdArrivedTime()) + "-" + simpleDateFormat.format(sailingScheduleForFind.getEtdDepartTime()));
clientCheckedBean.setBillNo(billList.get(i).getBillNo());
clientCheckedBean.setSettleCurrency(billList.get(i).getSettleCurrency());
clientCheckedBean.setCheckedTime(billList.get(i).getCheckedTime());
if (CNY.equals(billList.get(i).getSettleCurrency())) {
totalCnyAmount = totalCnyAmount.add(billList.get(i).getSettleAmount());
clientCheckedBean.setCnyAmount(billList.get(i).getSettleAmount());
}
if (HKD.equals(billList.get(i).getSettleCurrency())) {
totalHkdAmount = totalHkdAmount.add(billList.get(i).getSettleAmount());
clientCheckedBean.setHkdAmount(billList.get(i).getSettleAmount());
}
if (USD.equals(billList.get(i).getSettleCurrency())) {
totalUsdAmount = totalUsdAmount.add(billList.get(i).getSettleAmount());
clientCheckedBean.setUsdAmount(billList.get(i).getSettleAmount());
}
checkedBeanList.add(clientCheckedBean);
}
sortBeanListByVoyageMessage(checkedBeanList);
for (int i = 0; i < checkedBeanList.size(); i++) {
int indexPlace = 6;
indexPlace = indexPlace + i;
if (i != (billList.size() - 1)) {
HSSFRow sourceRow = sheet.getRow(5);
sheet.createRow(sheet.getLastRowNum() + 1);
sheet.shiftRows(indexPlace, sheet.getLastRowNum(), 1, true,
false);
HSSFRow targetRow = sheet.createRow(indexPlace);
poiUtilService.copyRow(sheet, sourceRow, targetRow);
}
excelService.setExcelValue(
i + 1 + "",
sheet, indexPlace, 0);
excelService.setExcelValue(checkedBeanList.get(i).getSettleCurrency(), sheet, indexPlace, 8);
excelService.setExcelValue(
checkedBeanList.get(i).getBillNo(),
sheet, indexPlace, 6);
excelService.setExcelValue(checkedBeanList.get(i).getSettleCurrency(), sheet, indexPlace, 8);
if (CNY.equals(checkedBeanList.get(i).getSettleCurrency())) {
excelService.setExcelValueFormatAmount(checkedBeanList.get(i).getCnyAmount(), sheet, indexPlace, 9,cellStyle);
excelService.setExcelValueFormatAmount(checkedBeanList.get(i).getCnyAmount(), sheet, indexPlace, 11,cellStyle);
}
if (HKD.equals(checkedBeanList.get(i).getSettleCurrency())) {
excelService.setExcelValueFormatAmount(checkedBeanList.get(i).getHkdAmount(), sheet, indexPlace, 9,cellStyle);
excelService.setExcelValueFormatAmount(checkedBeanList.get(i).getHkdAmount(), sheet, indexPlace, 11,cellStyle);
}
if (USD.equals(checkedBeanList.get(i).getSettleCurrency())) {
excelService.setExcelValueFormatAmount(checkedBeanList.get(i).getUsdAmount(), sheet, indexPlace, 9,cellStyle);
excelService.setExcelValueFormatAmount(checkedBeanList.get(i).getUsdAmount(), sheet, indexPlace, 11,cellStyle);
}
BigDecimal amount = BigDecimal.ZERO;
excelService.setExcelValueFormatAmount(amount, sheet, indexPlace, 10,cellStyle);
excelService.setExcelValue(simpleDateFormat.format(checkedBeanList.get(i).getCheckedTime()), sheet, indexPlace, 12);
excelService.setExcelValue(checkedBeanList.get(i).getEtdDate(), sheet, indexPlace, 4);
excelService.setExcelValue(checkedBeanList.get(i).getVoyageMessage(), sheet, indexPlace, 1);
}
excelService.setExcelValueFormatAmount(totalCnyAmount,sheet,6 + billList.size(), 9,cellBoldStyle);
excelService.setExcelValueFormatAmount(totalCnyAmount, sheet, 6 + billList.size(), 11,cellBoldStyle);
excelService.setExcelValueFormatAmount(totalHkdAmount, sheet, 7 + billList.size(), 9,cellBoldStyle);
excelService.setExcelValueFormatAmount(totalHkdAmount, sheet, 7 + billList.size(), 11,cellBoldStyle);
excelService.setExcelValueFormatAmount(totalUsdAmount, sheet, 8 + billList.size(), 9,cellBoldStyle);
excelService.setExcelValueFormatAmount(totalUsdAmount, sheet, 8 + billList.size(), 11,cellBoldStyle);
ArapAuthUser user = arapAuthUserService.findByCode(bill.getCheckedBy());
excelService.setExcelValue(user.getName(), sheet, 13 + billList.size(), 1);
excelService.setExcelValue(user.getTel(), sheet, 13 + billList.size(), 6);
excelService.setExcelValue(user.getEmail(), sheet, 13 + billList.size(), 11);
}
/**
* 插入图片
*
* @param patriarch
* @param wb
* @throws IOException
*/
public void insetIntoPictureClient(HSSFPatriarch patriarch, HSSFWorkbook wb, String orgGroupCode) throws IOException {
String logoPath = GroupConst.GROUP_LOGO_IMAGE.get(orgGroupCode);
// 公司logo
if (ObjectUtils.isNotEmpty(logoPath)) {
ClassPathResource classPathStyle = new ClassPathResource(logoPath);
InputStream inputStreamBody = classPathStyle.getInputStream();
ByteArrayOutputStream stream = new ByteArrayOutputStream();
StreamUtils.copy(inputStreamBody, stream);
inputStreamBody.close();
HSSFClientAnchor UAlogo = new HSSFClientAnchor(20, 20, 600,
255, (short) 0, 0, (short) 1, 2);
patriarch.createPicture(UAlogo, wb.addPicture(
stream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
stream.close();
}
}
代码涉及到很多poi的技能，包含设定单元格的合并，样式处理，字体添加，加入图片，金额格式化等，还有很麻烦的循环，这个表格是使用固定模板并且赋值的方式，所以出现了很多设定的数据和模板冲突的问题，直接报错，样式全无。
网友评论