1,先将数据查询出写入到Excel
@RequestMapping("exportCorpData")
@ResponseBody
@ApiOperation(value = "exportCorpData", notes = "导出数据")
public Response exportCorpData(@RequestBody CorpTaskParam param) {
XSSFWorkbook wb = null;
String url = null;
try {
// 查询数据
List<CorpTaskVo> list = corpManager.queryAllCorpData(param);
if(!CollectionUtils.isEmpty(list)){
//创建工作簿
wb = corpManager.exportCorpData(list);
//写入流,保存到服务器
ByteArrayOutputStream baos = new ByteArrayOutputStream();
wb.write(baos);
ByteArrayInputStream swapStream = new ByteArrayInputStream(baos.toByteArray());
String container = PropertiesManager.instance().getString("oss_client_container","document");
String extName = "export_corp_task.xlsx";
String objectName=UUID.randomUUID().toString().replaceAll("-", "").toUpperCase()+"."+extName;
url = OssUtils.uploadByInputStream(OssUtils.getOSSClient(), container, objectName, swapStream);
}
} catch (Exception e) {
e.printStackTrace();
}
return ResponseHelper.buildOk(url);
}
public XSSFWorkbook exportCorpData(List<CorpTaskVo> list){
XSSFWorkbook xWorkbook = new XSSFWorkbook();
XSSFSheet xSheet = xWorkbook.createSheet("export_corp_task");
String [] columnNames =
{"任务编码","任务状态","出发场地","目的场地","线路编码","甩挂挂号","甩挂类型","任务开始执行时间","任务结束执行时间","创建人","共建伙伴","处理人","车牌号","甩挂服务商","里程(KM)","实际耗时(MIN)","审核人","备注","共建伙伴账单编码","甩挂服务商账单编码","审核时间","创建时间","更新时间"};
//set Sheet页头部
POIUtils.setSheetHeader(xWorkbook, xSheet,columnNames);
List<Object[]> cellList = new ArrayList<>();
Object[] cellValues = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
int j = 0;
for(int i=0;i<list.size();i++){
cellValues = new Object[23];
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getTaskCode()) ? "" : list.get(i).getTaskCode();
String taskStatus = list.get(i).getStatus().toString();
if(!ObjectUtils.isEmpty(taskStatus)){
if(taskStatus.equals("1")) taskStatus = "待执行";
if(taskStatus.equals("2")) taskStatus = "执行中";
if(taskStatus.equals("3")) taskStatus = "待审核";
if(taskStatus.equals("4")) taskStatus = "已完成";
if(taskStatus.equals("5")) taskStatus = "已取消";
}else taskStatus="";
cellValues[j++] = taskStatus;
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getBeginAreaName()) ? "" : list.get(i).getBeginAreaName();
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getEndAreaName()) ? "" : list.get(i).getEndAreaName();
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getLineCode()) ? "" : list.get(i).getLineCode();
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getSwingCode()) ? "" : list.get(i).getSwingCode();
String swingType=list.get(i).getSwingType();
if(!ObjectUtils.isEmpty(swingType)){
if(swingType.equals("1")) swingType="空挂";
if(swingType.equals("2")) swingType="重挂";
}else swingType="";
cellValues[j++] =swingType;
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getBeginTaskTm()) ? "" : sdf.format(list.get(i).getBeginTaskTm());
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getEndTaskTm()) ? "" : sdf.format(list.get(i).getEndTaskTm());
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getCreator()) ? "" : list.get(i).getCreator();
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getGjSupplier()) ? "" : list.get(i).getGjSupplier();
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getUsername()) ? "" : list.get(i).getUsername();
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getVehicleCode()) ? "" : list.get(i).getVehicleCode();
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getGjSupplier()) ? "" : list.get(i).getGjSupplier();
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getMileage()) ? "" : list.get(i).getMileage();
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getPracticalTime()) ? "" : list.get(i).getPracticalTime();
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getCheckPerson()) ? "" : list.get(i).getCheckPerson();
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getCheckResion()) ? "" : list.get(i).getCheckResion();
//共建伙伴账单编号,现在没有用空填写
cellValues[j++] = "" ;
//服务商账单编号,现在没有用空填写
cellValues[j++] = "" ;
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getCheckTm()) ? "" : sdf.format(list.get(i).getCheckTm());
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getCreateTm()) ? "" : sdf.format(list.get(i).getCreateTm());
cellValues[j++] = ObjectUtils.isEmpty(list.get(i).getModifyTm()) ? "" : sdf.format(list.get(i).getModifyTm());
cellList.add(cellValues);
j = 0;
}
try {
if(!ObjectUtils.isEmpty(cellValues)) {
//set Sheet页内容
POIUtils.setSheetContent(xWorkbook, xSheet,columnNames,list,cellList);
}
} catch (Exception e) {
logger.error("import excel error:",e);
return null;
}
return xWorkbook;
}
public static void setSheetHeader(XSSFWorkbook xWorkbook, XSSFSheet xSheet,String[] columnNames) {
for(int i=0;i<columnNames.length;i++){
xSheet.setColumnWidth(i, 40 * 256);
}
CellStyle cs = xWorkbook.createCellStyle();
//设置水平垂直居中
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//设置字体
Font headerFont = xWorkbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontName("宋体");
cs.setFont(headerFont);
cs.setWrapText(true);//是否自动换行
XSSFRow xRow0 = xSheet.createRow(0);
for(int i=0;i<columnNames.length;i++){
XSSFCell xCell = xRow0.createCell(i);
xCell.setCellStyle(cs);
xCell.setCellValue(columnNames[i]);
}
}
public static void setSheetContent(XSSFWorkbook xWorkbook, XSSFSheet xSheet,
String[] columnNames,List<CorpTaskVo> list,List<Object[]> value) {
CellStyle cs = xWorkbook.createCellStyle();
cs.setWrapText(true);
for (int i = 0; i < list.size(); i++) {
XSSFRow xRow = xSheet.createRow(i + 1);
Object[] obj = value.get(i);
for (int j = 0; j < columnNames.length; j++) {
XSSFCell xCell = xRow.createCell(j);
xCell.setCellStyle(cs);
xCell.setCellValue(String.valueOf(obj[j]));
}
}
}
// 导出方法
private void downloadFileForWeb(String filePath,String title, HttpServletResponse response) {
try{
InputStream ins = null;
OutputStream os = null;
try {
String container=filePath.split("/")[4];
String objectName=filePath.split("/")[5];
response.setContentType("application/octet-stream");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename="+URLEncoder.encode(title, "UTF-8").replace('+', ' ') );
ins=OssUtils.getInputStreamByOSS(OssUtils.getOSSClient(), container, objectName);
os = new BufferedOutputStream(response.getOutputStream());
byte[] buff = new byte[1024];
int bytesRead = 0;
while (-1 != (bytesRead = ins.read(buff, 0, buff.length))) {
os.write(buff, 0, bytesRead);
}
os.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (ins!= null) {
try {
ins.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (os != null) {
try {
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}catch(Exception e) {
logger.error("download file error",e);
}
}