动态生成excel

最新推荐文章于 2024-02-05 12:01:14 发布

一只皮皮兔

最新推荐文章于 2024-02-05 12:01:14 发布

阅读量181

点赞数

分类专栏： java下载

本文链接：https://blog.csdn.net/m18066975852/article/details/89877177

版权

java下载专栏收录该内容

2 篇文章 0 订阅

订阅专栏

public static void makeBillCA(AccountReceivable receivable, HttpServletResponse response) throws IOException {
OutputStream outputStream=null;
HSSFWorkbook workbook =null;
try{
receivable = receivableService.get(receivable);
//查询改账单下的集装箱，按对应集装箱的order_id,sequence_num排序
List<AccountContainer> list = containerService.getByAccountId(receivable.getId());
List<AdditionalCost> additionalCostList=costService.getByAccountId(receivable.getId());
//titleList 动态的列数，从数据库取
List<String> titleList= Lists.newArrayList();
titleList.add("运费(￥)");
for(AdditionalCost additionalCost:additionalCostList){
if (!titleList.contains(additionalCost.getExpenseItem().getName() + "("+ additionalCost.getCurrencyTypeName() +")")) {
titleList.add(additionalCost.getExpenseItem().getName() + "("+ additionalCost.getCurrencyTypeName() +")");
}
}

//20gp的合并运费，补录费用，箱号
Iterator<AccountContainer> it = list.iterator();
//20gp箱子数量
int twetyGp = 0;
//临时箱号
String tempContainerNo="";
//临时运费
BigDecimal yf = new BigDecimal(0);
//需要删除箱子的临时id
String tempId = "";
while(it.hasNext()){
AccountContainer accountContainer = it.next();
accountContainer.setContainer(orderContainerService.get(accountContainer.getContainer()));
//20gp需要合并
if (accountContainer.getContainer().getContainerType().equals(ContainerTypeEnum.TWENTY_GP)) {
twetyGp++;
if (twetyGp==1) {
   //箱号
tempContainerNo=accountContainer.getContainer().getContainerNo();
//运费
yf = accountContainer.getFreightPrice();
//箱子id
tempId = accountContainer.getId();
it.remove();
}else{
accountContainer.getContainer().setContainerNo(tempContainerNo+"/"+accountContainer.getContainer().getContainerNo());
accountContainer.setFreightPrice(accountContainer.getFreightPrice().add(yf));
//查出临时id下的所有补录费用集合和现在这个id下的所有补录费用集合
List<AdditionalCost> additionalCostListNow = costService.getByAccountIdAccountContainerId(receivable.getId(), accountContainer.getId());
List<AdditionalCost> additionalCostListTemp = costService.getByAccountIdAccountContainerId(receivable.getId(), tempId);
for (AdditionalCost additionalCostTemp : additionalCostListTemp) {
   //旧的费用项id
   String tempItemId = additionalCostTemp.getExpenseItem().getId();
   //新的是否包含此费用项
   boolean flag = false;
   for (AdditionalCost additionalCostNow : additionalCostListNow) {
                               //如果临时费用项id和现在的费用项id相同，则加起来，给到新的费用
                               if (additionalCostNow.getExpenseItem().getId().equals(tempItemId)) {
                                   additionalCostNow.setAmount(additionalCostNow.getAmount().add(additionalCostTemp.getAmount()));
                                   flag = true;
                               }
                           }
   //新的不包含旧的费用项
   if (!flag) {
       additionalCostListNow.add(additionalCostTemp);
                           }
                       }
accountContainer.setAdditionalCostList(additionalCostListNow);
twetyGp=0;
}

}else{
//其他箱型需要把查出自己的补录费用
accountContainer.setAdditionalCostList(costService.getByAccountIdAccountContainerId(receivable.getId(), accountContainer.getId()));
}
}

workbook = new HSSFWorkbook();
CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,7+titleList.size());//起始行,结束行,起始列,结束列

//左对齐，无边框
HSSFCellStyle headStyle = createCellStyle(workbook,"楷体",(short)12,true,false,true,"",false);
HSSFCellStyle erStyle = createCellStyle(workbook,"楷体",(short)16,true,true,false,"",false);
HSSFCellStyle colStyle = createCellStyle(workbook,"宋体",(short)11,true,true,false,"",true);

//2.创建工作表
HSSFSheet sheet = workbook.createSheet("sheet1");
//打印方向，true:横向，false:纵向
sheet.getPrintSetup().setLandscape(true);
//使得一个Sheet适合一页
sheet.setAutobreaks(true);
//2.1加载合并单元格对象
sheet.addMergedRegion(callRangeAddress);
//设置默认列宽
sheet.setDefaultColumnWidth(15);
//3.创建行
//3.1创建头标题行;并且设置头标题
HSSFRow rower = sheet.createRow(0);
HSSFCell celler = rower.createCell(0);
//加载单元格样式
celler.setCellStyle(erStyle);

celler.setCellValue(DateUtils.formatDate(receivable.getTrain().getDepartureDate(),"yyyy年MM月dd日") +" " + "中亚班列" + " "+"阿拉山口对账单");
//3.2创建列标题;并且设置列标题
HSSFRow row2 = sheet.createRow(1);
row2.setHeightInPoints((short) 30);
HSSFCell cellRow =row2.createCell(0);
cellRow.setCellStyle(colStyle);
cellRow.setCellValue("序号");
HSSFCell cellRow1 =row2.createCell(1);
cellRow1.setCellStyle(colStyle);
cellRow1.setCellValue("客户");
HSSFCell cellRow2=row2.createCell(2);
cellRow2.setCellStyle(colStyle);
cellRow2.setCellValue("箱号");
HSSFCell cellRow3=row2.createCell(3);
cellRow3.setCellStyle(colStyle);
cellRow3.setCellValue("箱型");
HSSFCell cellRow4=row2.createCell(4);
cellRow4.setCellStyle(colStyle);
cellRow4.setCellValue("品名");
HSSFCell cellRow5=row2.createCell(5);
cellRow5.setCellStyle(colStyle);
cellRow5.setCellValue("口岸");
HSSFCell cellRow6=row2.createCell(6);
cellRow6.setCellStyle(colStyle);
cellRow6.setCellValue("报关");
HSSFCell cellRow7 =row2.createCell(7);
cellRow7.setCellStyle(colStyle);
cellRow7.setCellValue("出口国家");

for(int i=0;i<titleList.size();i++)
{
HSSFCell cell2 = row2.createCell(i+8);
//加载单元格样式
cell2.setCellStyle(colStyle);
cell2.setCellValue(titleList.get(i));
}

//根据titleList合并单元格
CellRangeAddress callRangeTitle10 = new CellRangeAddress(3+list.size(),3+list.size(),8,titleList.size()-1+8);
sheet.addMergedRegion(callRangeTitle10);

HSSFRow createRow=null;
for(int i=0;i<list.size();i++){
createRow= sheet.createRow(i+2);
createRow.setHeightInPoints((short) 30);
HSSFCell cell=createRow.createCell(0);
cell.setCellStyle(colStyle);
cell.setCellValue(i+1+"");
HSSFCell cell1=createRow.createCell(1);
cell1.setCellStyle(colStyle);
cell1.setCellValue(list.get(i).getCompany().getFullName());
HSSFCell cell2=createRow.createCell(2);
cell2.setCellStyle(colStyle);
cell2.setCellValue(list.get(i).getContainer().getContainerNo());
HSSFCell cell3=createRow.createCell(3);
cell3.setCellStyle(colStyle);
cell3.setCellValue(list.get(i).getContainer().getContainerTypeName());
HSSFCell cell4=createRow.createCell(4);
cell4.setCellStyle(colStyle);
cell4.setCellValue(list.get(i).getContainer().getGoodName());
HSSFCell cell5=createRow.createCell(5);
cell5.setCellStyle(colStyle);
cell5.setCellValue("阿拉山口");
HSSFCell cell6=createRow.createCell(6);
cell6.setCellStyle(colStyle);
cell6.setCellValue(list.get(i).getContainer().getBgType().equals("0")?"本地报关":"异地报关");
HSSFCell cell7=createRow.createCell(7);
cell7.setCellStyle(colStyle);
cell7.setCellValue(list.get(i).getContainer().getUnloadStationName());

for(int j=0;j<titleList.size();j++){
HSSFCell createCell=createRow.createCell(8+j);
createCell.setCellStyle(colStyle);
if(j==0&&null!=list.get(i).getFreightPrice()){
createCell.setCellValue(list.get(i).getFreightPrice()!=null?String.valueOf(list.get(i).getFreightPrice()):"0");
} else{
for (AdditionalCost additionalCost : list.get(i).getAdditionalCostList()) {
if (additionalCost != null) {
if((additionalCost.getExpenseItem().getName()+"("+additionalCost.getCurrencyTypeName()+")").equals(titleList.get(j))){
createCell.setCellValue(String.valueOf(additionalCost.getAmount()));
}
}
}
}
}
}

HSSFRow rows = sheet.createRow(list.size()+2);
rows.setHeightInPoints((short)20);
HSSFCell cellRows=rows.createCell(7);
cellRows.setCellStyle(colStyle);
cellRows.setCellValue("小计:");
char begin_letter='H';
for(int k=0;k<titleList.size();k++){
cellRows=rows.createCell(8+k);
colStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
cellRows.setCellStyle(colStyle);
cellRows.setCellType(HSSFCell.CELL_TYPE_FORMULA);
String basePrice="";
char char2;
char2=(char) (begin_letter +1);
for(int j=0;j<list.size();j++){
basePrice+=(char2)+""+(3+j)+"+";
}
begin_letter=char2;
if(basePrice!=""){
cellRows.setCellFormula("\"￥\"&"+basePrice.substring(0,basePrice.length()-1));
}
}

HSSFRow rows2 = sheet.createRow(list.size()+3);
rows2.setHeightInPoints((short)20);
HSSFCell cellRows2=rows2.createCell(7);
cellRows2.setCellStyle(colStyle);
cellRows2.setCellValue("合计:");
HSSFCell cellRows8=rows2.createCell(8);
colStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
cellRows8.setCellStyle(colStyle);
cellRows8.setCellType(HSSFCell.CELL_TYPE_FORMULA);
for (int j = 0; j < titleList.size()-1; j++) {
HSSFCell cellRowsn = rows2.createCell(9+j);
cellRowsn.setCellStyle(colStyle);
}

char begin_letter1='H';
String basePrice="";
for(int k=0;k<titleList.size();k++){
begin_letter1=(char) (begin_letter1 + 1);
basePrice+=(begin_letter1)+""+(3+list.size())+"+";
}
if(basePrice!=""){
   if(titleList.size()==1){
       cellRows8.setCellFormula(basePrice.substring(0,basePrice.length()-1));
   }else{
       cellRows8.setCellFormula("\"￥\"&"+basePrice.substring(0,basePrice.length()-1));
   }
}

HSSFRow rows3 = sheet.createRow(list.size()+6);
rows3.setHeightInPoints((short)20);
HSSFCell cellRows3=rows3.createCell(0);
cellRows3.setCellStyle(headStyle);
cellRows3.setCellValue("制表人");
HSSFCell cellRows31=rows3.createCell(6);
cellRows31.setCellStyle(headStyle);
cellRows31.setCellValue("订舱确认人");

HSSFRow rows4 = sheet.createRow(list.size()+10);
rows4.setHeightInPoints((short)20);
HSSFCell cellRows4=rows4.createCell(0);
cellRows4.setCellStyle(headStyle);
cellRows4.setCellValue("现场操作");
HSSFCell cellRows41=rows4.createCell(6);
cellRows41.setCellStyle(headStyle);
cellRows41.setCellValue("企业盖章");

HSSFRow rows5 = sheet.createRow(list.size()+14);
rows5.setHeightInPoints((short)20);
HSSFCell cellRows5=rows5.createCell(0);
cellRows5.setCellStyle(headStyle);
cellRows5.setCellValue("境外费用确认人");

/**
* 下载文件
*/
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader( "Content-Disposition", "attachment; filename="+ Encodes.urlEncode(receivable.getTrain().getTrainCode()+"集装箱对账单.xls"));
outputStream= response.getOutputStream();
workbook.write(outputStream);
}catch(Exception e) {
e.printStackTrace();
} finally {
outputStream.close();
}

}

一只皮皮兔

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
动态生成excel

public static void makeBillCA(AccountReceivable receivable, HttpServletResponse response) throws IOException { OutputStream outputStream=null; HSSFWorkbook workbook =null; try{...
复制链接

扫一扫

专栏目录