格式"/>
导出 exl 按照已存在的固定格式
//获取已经存在exl 的地址 根据存在的exl格式创建显示数据(其实显示成想要的格式就是复制 固定的格式然后填充数据,看到了什么位置会发生变化 重新复制一行填充数据)
HSSFWorkbook workbook=new HSSFWorkbook(request.getSession(true).getServletContext().getResourceAsStream("/template/distributerCollect.xls"));
private void getDistributeReport(HSSFWorkbook workbook,
Map<String, Object> filterMap,List<DistributerReportDto> list){HSSFSheet sheet = workbook.getSheetAt(0);
String beginDate = map.get("beginDate");
String endDate = map.get("endDate");
int rowNum = list.size();
int index = 4;
int c=5;
//判断是否是相同的
String isSameName="null";
//记录小计位置
List<Integer> subtotal=new ArrayList<Integer>();
for(int i=0;i < list.size(); i++){
DistributerReportDto distributerDto = list.get(i);
if(!distributerDto.getSupinstno().equals(isSameName)&&!isSameName.equals("null")){
//复制要写入数据的那一列
ExcelUtils.copyRow(workbook, sheet, index+1, index);
HSSFRow row = sheet.getRow(index);
String[] excelTitle = {"A","B","C","D","E","F","G","H","I","J","K","L"};
for(int a = 3;a<row.getPhysicalNumberOfCells();a++){
if(a == 10){
continue;
}
String titleChar = excelTitle[a];
HSSFCell cell = row.getCell(a);
if(a==3){
cell.setCellValue(isSameName);
}
if(a==4){
cell.setCellValue(beginDate);
}
if(a==5){
cell.setCellValue(endDate);
}
if(a>5){
String fromula = rowNum == 0 ? "0" : "SUM("+titleChar+""+c+":"+titleChar+""+(index)+")";
cell.setCellFormula(fromula);
}
}
subtotal.add(index+1);//记录小计位置(记录的写值的位置要+1)
index++;
c=index+1;//记录小计开始的位置
}
ExcelUtils.copyRow(workbook, sheet, 3, index);
HSSFRow row = sheet.getRow(index);
Object[] obj = this.objArray(distributerDto);
ExcelUtils.setdefaultRow(row, obj);
isSameName=distributerDto.getSupinstno();
index++;
//获得最后一个小计
if(list.size()-1==i){
HSSFRow lastRowSum = sheet.getRow(index);
String[] excelTitle = {"A","B","C","D","E","F","G","H","I","J","K","L"};
for(int a = 3;a<lastRowSum.getPhysicalNumberOfCells();a++){
if(a == 10){
continue;
}
String titleChar = excelTitle[a];
HSSFCell cell = lastRowSum.getCell(a);
if(a==3){
cell.setCellValue(isSameName);
}
if(a==4){
cell.setCellValue(beginDate);
}
if(a==5){
cell.setCellValue(endDate);
}
if(a>5){
String fromula = rowNum == 0 ? "0" : "SUM("+titleChar+""+c+":"+titleChar+""+(index)+")";
cell.setCellFormula(fromula);
}
}
}
}
//写入汇总行
int lastRowNum = index+1;
HSSFRow lastRow = sheet.getRow(lastRowNum);
String[] excelTitle = {"A","B","C","D","E","F","G","H","I","J","K","L"};
for(int i = 4;i<lastRow.getPhysicalNumberOfCells();i++){
if(i == 10){
continue;
}
String titleChar = excelTitle[i];
HSSFCell cell = lastRow.getCell(i);
if(i==4){
cell.setCellValue(beginDate);
}
if(i==5){
cell.setCellValue(endDate);
}
if(i>5){
//SUM("+titleChar+"4:"+titleChar+""+(lastRowNum-2)+")
String toalSum="SUM("+titleChar+""+(index)+":"+titleChar+""+(index)+")";
for(int j=0;j<subtotal.size();j++){
//所以小计的和为总计
toalSum=toalSum+"+"+"SUM("+titleChar+""+subtotal.get(j)+":"+titleChar+""+(subtotal.get(j))+")";
}
String fromula = rowNum == 0 ? "0" : toalSum;
cell.setCellFormula(fromula);
}
}
sheet.shiftRows(4, lastRowNum, -1);
}
更多推荐
导出 exl 按照已存在的固定格式
发布评论