//创建poi导出数据对象 SXSSFWorkbook sxssfWorkbook=new SXSSFWorkbook(); //创建sheet页 SXSSFSheet sheet=sxssfWorkbook.createSheet("附加费导入模板"); //创建表头 SXSSFRow headRow=sheet.createRow(0); //设置表头信息 SXSSFCell cell1 = headRow.createCell(0); cell1.setCellValue("订单号"); SXSSFCell cell2 =headRow.createCell(1); cell2.setCellValue("附加费类型"); SXSSFCell cell3 =headRow.createCell(2); cell3.setCellValue("附加费"); SXSSFCell cell4 =headRow.createCell(3); cell4.setCellValue("附加费备注"); SXSSFCell cell5 =headRow.createCell(4); cell5.setCellValue("完成时间"); SXSSFCell cell6 =headRow.createCell(5); cell6.setCellValue("唯一码"); String[] values; if (type.equals("1")) { values = new String[]{"远程费", "椅子", "搬运费", "实木款", "其它"}; } else { values = new String[]{"远程费", "配件费", "补漆", "拆卸打包", "其它"}; } //设置下拉框位置 CellRangeAddressList addressList = new CellRangeAddressList(1, 5000, 1, 1); DataValidationHelper helper = sheet.getDataValidationHelper(); // 设置下拉框数据 DataValidationConstraint constraint = helper.createExplicitListConstraint(values); DataValidation dataValidation = helper.createValidation(constraint, addressList); sheet.addValidationData(dataValidation); //设置样式 CellStyle titleStyle = sxssfWorkbook.createCellStyle(); //设置背景色 titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); //必须设置 否则背景色不生效 titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cell1.setCellStyle(titleStyle); cell2.setCellStyle(titleStyle); cell3.setCellStyle(titleStyle); cell4.setCellStyle(titleStyle); cell5.setCellStyle(titleStyle); cell6.setCellStyle(titleStyle);
//内容居中 //CellStyle cellStyle=sxssfWorkbook.createCellStyle(); //cellStyle.setAlignment(HorizontalAlignment.CENTER); //cell5.setCellStyle(cellStyle); //合并单元格 //SXSSFCell cell5 =headRow.createCell(4); //cell5.setCellValue("神娱"); //CellRangeAddress region5=new CellRangeAddress(开始行下标,结束行下标,开始列下标,结束列下标); //sheet.addMergedRegion(region5);
// 下载导出 String filename="附加费导入模板"; // 设置头信息 response.setCharacterEncoding("UTF-8"); response.setContentType("application/vnd.ms-excel"); //一定要设置成xlsx格式 response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(filename+".xlsx","UTF-8")); //创建一个输出流 ServletOutputStream outputStream=response.getOutputStream(); //写入数据 sxssfWorkbook.write(outputStream); // 关闭 outputStream.close(); sxssfWorkbook.close();
导出结果如下: