POI导入模板,下载表格接口
package com.lg.ticket.utils;
import cn.hutool.poi.excel.RowUtil;
import com.baomidou.mybatisplus.extension.api.R;
import com.lg.ticket.dto.SaleDetailDto;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.stereotype.Component;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
@Component
public class ExportExcelUtil {
public void exportDataExcel(HttpServletResponse response, List<SaleDetailDto> saleDetailDtoList) {
OutputStream out = null;
try {
String filePath = "D:\\票据销售明细.xls";
FileInputStream tps = new FileInputStream(new File(filePath));
HSSFWorkbook tpWorkbook = new HSSFWorkbook(tps);
out = response.getOutputStream();
response.reset();
response.setHeader("content-disposition",
"attachment;filename=" + new String(("票据销售明细").getBytes("gb2312"), "ISO8859-1") + ".xls");
response.setContentType("APPLICATION/MSEXCEL");
HSSFWorkbook workbook = tpWorkbook;
workbook=addData(workbook,saleDetailDtoList);
workbook.write(out);
}catch (Exception e){
e.printStackTrace();
}finally {
try {
response.flushBuffer();
if (out != null) {
out.flush();
out.close();
}
} catch (final IOException e) {
e.printStackTrace();
}
}
}
public static HSSFWorkbook addData(HSSFWorkbook workbook,List<SaleDetailDto> saleDetailDtoList){
int rowNum=3;
int size;
boolean bool=true;
CreationHelper createHelper = workbook.getCreationHelper();
CellStyle cellStyleTime=workbook.createCellStyle();
cellStyleTime.setDataFormat(
createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss")
);
cellStyleTime.setAlignment(HorizontalAlignment.LEFT);
cellStyleTime.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFCellStyle left=workbook.createCellStyle();
left.setAlignment(HorizontalAlignment.LEFT);
left.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFCellStyle right=workbook.createCellStyle();
right.setAlignment(HorizontalAlignment.RIGHT);
right.setVerticalAlignment(VerticalAlignment.CENTER);
Sheet sheet=workbook.getSheetAt(0);
for (SaleDetailDto saleDetailDto : saleDetailDtoList) {
size = Math.max(saleDetailDto.getSaleHandDetailDtoList().size(), saleDetailDto.getSaleTicketDetailDtoList().size());
Row row = sheet.createRow(rowNum++);
if (size >= 2) {
for (int k = 0; k < 12; k++) {
CellRangeAddress region = new CellRangeAddress(rowNum-1, rowNum + size - 2, k, k);
sheet.addMergedRegion(region);
RegionUtil.setBorderBottom(BorderStyle.MEDIUM,region,sheet);
}
}
Cell cell01 = row.createCell(0);
cell01.setCellValue(saleDetailDto.getOrderId());
Cell cell02 = row.createCell(1);
cell02.setCellValue(saleDetailDto.getMemberName());
Cell cell03 = row.createCell(2);
cell03.setCellValue(saleDetailDto.getCardId());
Cell cell04 = row.createCell(3);
cell04.setCellValue(saleDetailDto.getPhone());
Cell cell05 = row.createCell(4);
cell05.setCellValue(saleDetailDto.getVenueName());
Cell cell06 = row.createCell(5);
cell06.setCellStyle(right);
cell06.setCellValue(String.valueOf(saleDetailDto.getPricePay()));
Cell cell07 = row.createCell(6);
cell07.setCellValue("");
Cell cell08 = row.createCell(7);
switch (saleDetailDto.getSource()){
case "W" :
cell08.setCellValue("微信");
break;
case "B" :
cell08.setCellValue("后台");
break;
case "T" :
cell08.setCellValue("淘票票");
break;
case "D" :
cell08.setCellValue("大卖网");
break;
}
Cell cell09 = row.createCell(8);
cell09.setCellValue(saleDetailDto.getOrderState());
switch (saleDetailDto.getOrderState()){
case "P":
cell09.setCellValue("已支付");
case "U":
cell09.setCellValue("未支付");
}
Cell cell10 = row.createCell(9);
cell10.setCellValue(saleDetailDto.getRemark());
Cell cell11 = row.createCell(10);
cell11.setCellValue(saleDetailDto.getCreateName());
Cell cell12 = row.createCell(11);
cell12.setCellStyle(cellStyleTime);
cell12.setCellValue(saleDetailDto.getCreateTime());
if (saleDetailDto.getSaleTicketDetailDtoList().size() >= saleDetailDto.getSaleHandDetailDtoList().size()) {
for (int j = 0; j < saleDetailDto.getSaleTicketDetailDtoList().size(); j++) {
if (j != 0) {
sheet.createRow(rowNum++);
}
Row rowTicket = sheet.getRow(rowNum - 1);
Cell cell13 = rowTicket.createCell(12);
cell13.setCellValue(saleDetailDto.getSaleTicketDetailDtoList().get(j).getTypeName());
Cell cell14 = rowTicket.createCell(13);
cell14.setCellStyle(right);
cell14.setCellValue(saleDetailDto.getSaleTicketDetailDtoList().get(j).getNum());
Cell cell15 = rowTicket.createCell(14);
cell15.setCellStyle(right);
cell15.setCellValue(saleDetailDto.getSaleTicketDetailDtoList().get(j).getMoney());
Cell cell16 = rowTicket.createCell(15);
cell16.setCellValue(saleDetailDto.getSaleTicketDetailDtoList().get(j).getRemark());
if (saleDetailDto.getSaleHandDetailDtoList().size() > j) {
Cell cell17 = rowTicket.createCell(16);
cell17.setCellValue(saleDetailDto.getSaleHandDetailDtoList().get(j).getHandId());
Cell cell18 = rowTicket.createCell(17);
cell18.setCellStyle(right);
cell18.setCellValue(String.valueOf(saleDetailDto.getSaleHandDetailDtoList().get(j).getDeposit()));
Cell cell19=rowTicket.createCell(18);
cell19.setCellValue(saleDetailDto.getSaleHandDetailDtoList().get(j).getHandState());
Cell cell20 = rowTicket.createCell(19);
cell20.setCellStyle(cellStyleTime);
cell20.setCellValue(saleDetailDto.getSaleHandDetailDtoList().get(j).getCreateTime());
Cell cell21 = rowTicket.createCell(20);
cell21.setCellStyle(cellStyleTime);
if (saleDetailDto.getSaleHandDetailDtoList().get(j).getReturnTime()==null){
cell21.setCellValue("");
}else {
cell21.setCellValue(saleDetailDto.getSaleHandDetailDtoList().get(j).getReturnTime());
}
Cell cell22 = rowTicket.createCell(21);
cell22.setCellValue(saleDetailDto.getSaleHandDetailDtoList().get(j).getRemark());
}
}
} else {
for (int j = 0; j < saleDetailDto.getSaleHandDetailDtoList().size(); j++) {
if (j != 0) {
sheet.createRow(rowNum++);
}
Row rowTicket = sheet.getRow(rowNum - 1);
if (saleDetailDto.getSaleTicketDetailDtoList().size() > j) {
Cell cell13 = rowTicket.createCell(12);
cell13.setCellValue(saleDetailDto.getSaleTicketDetailDtoList().get(j).getTypeName());
Cell cell14 = rowTicket.createCell(13);
cell14.setCellStyle(right);
cell14.setCellValue(saleDetailDto.getSaleTicketDetailDtoList().get(j).getNum());
Cell cell15 = rowTicket.createCell(14);
cell15.setCellStyle(right);
cell15.setCellValue(saleDetailDto.getSaleTicketDetailDtoList().get(j).getMoney());
Cell cell16 = rowTicket.createCell(15);
cell16.setCellValue(saleDetailDto.getSaleTicketDetailDtoList().get(j).getRemark());
}
Cell cell17 = rowTicket.createCell(16);
cell17.setCellValue(saleDetailDto.getSaleHandDetailDtoList().get(j).getHandId());
Cell cell18 = rowTicket.createCell(17);
cell18.setCellStyle(right);
cell18.setCellValue(String.valueOf(saleDetailDto.getSaleHandDetailDtoList().get(j).getDeposit()));
Cell cell19=rowTicket.createCell(18);
cell19.setCellValue(saleDetailDto.getSaleHandDetailDtoList().get(j).getHandState());
Cell cell20 = rowTicket.createCell(19);
cell20.setCellStyle(cellStyleTime);
cell20.setCellValue(saleDetailDto.getSaleHandDetailDtoList().get(j).getCreateTime());
Cell cell21 = rowTicket.createCell(20);
cell21.setCellStyle(cellStyleTime);
if (saleDetailDto.getSaleHandDetailDtoList().get(j).getReturnTime()==null){
cell21.setCellValue("");
}else {
cell21.setCellValue(saleDetailDto.getSaleHandDetailDtoList().get(j).getReturnTime());
}
Cell cell22 = rowTicket.createCell(21);
cell22.setCellValue(saleDetailDto.getSaleHandDetailDtoList().get(j).getRemark());
}
}
if (size==0){
CellRangeAddress cellRangeAddress=new CellRangeAddress(rowNum-1, rowNum-1,0,21);
RegionUtil.setBorderBottom(BorderStyle.THIN,cellRangeAddress,sheet);
RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(),cellRangeAddress,sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN,cellRangeAddress,sheet);
RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(),cellRangeAddress,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,cellRangeAddress,sheet);
RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(),cellRangeAddress,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,cellRangeAddress,sheet);
RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(),cellRangeAddress,sheet);
}else {
CellRangeAddress cellRangeAddress=new CellRangeAddress(rowNum-size, rowNum-1 ,0,21);
RegionUtil.setBorderBottom(BorderStyle.THIN,cellRangeAddress,sheet);
RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(),cellRangeAddress,sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN,cellRangeAddress,sheet);
RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(),cellRangeAddress,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,cellRangeAddress,sheet);
RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(),cellRangeAddress,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,cellRangeAddress,sheet);
RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(),cellRangeAddress,sheet);
}
if (size==0){
for (int k = 0; k < 12; k++) {
CellRangeAddress region = new CellRangeAddress(rowNum-1, rowNum-1, k, k);
RegionUtil.setBorderBottom(BorderStyle.THIN,region,sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN,region,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,region,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,region,sheet);
}
}else{
for (int k = 0; k < 12; k++) {
CellRangeAddress region = new CellRangeAddress(rowNum-size, rowNum-1, k, k);
RegionUtil.setBorderBottom(BorderStyle.THIN,region,sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN,region,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,region,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,region,sheet);
}
}
}
return workbook;
}
}