原创

真实项目中java使用POI导出excel数据

直接上代码

@RequestMapping(value = "exportVecleInfo")
public void exportVecleInfo(String vehiclenum, String enterprisename, String vehicletypeid, String energytypeid,
String vehicletypenumbertypeid, String ischeck, String codenum, String checkcodenum,
HttpServletResponse response, RedirectAttributes redirectAttributes, HttpServletRequest request)
throws IOException {
try {
// 创建车辆对象
Vehicle vehi = new Vehicle();
vehi.setVehiclenum(vehiclenum);
vehi.setEnterprisename(enterprisename);
vehi.setVehicletypeid(vehicletypeid);
vehi.setEnergytypeid(energytypeid);
vehi.setVehicletypenumbertypeid(vehicletypenumbertypeid);
vehi.setIscheck(ischeck);
vehi.setCodenum(codenum);
if ("1".equals(checkcodenum)) {
vehi.getSqlMap().put("dsf", " AND a.codenum<4 ");
}
// 获取查询数据
List<Vehicle> vehicleList = vehicleService.findAllList(vehi);

// excel标题车牌号码、所属企业、车辆状况、号牌种类、燃料种类、车辆类型、车辆登记日期、发动冬季型号、车辆型号、车辆质量、排放标准、审核状态
String[] title = { "车牌号码", "所属企业", "车辆状况", "号牌种类", "燃料种类", "车辆类型", "车辆登记日期", "发动冬季型号", "车辆型号", "车辆质量",
"排放标准", "审核状态" };
// excel文件名
String fileName = "车辆信息表" + System.currentTimeMillis() + ".xlsx";
// sheet名
String sheetName = "车辆信息表";
XSSFWorkbook wb = null;
// 创建HSSFWorkbook对象(excel的文档对象)
wb = new XSSFWorkbook();
// 建立新的sheet对象(excel的表单)
XSSFSheet sheet = wb.createSheet("报表");
// 在sheet里创建第一行,参数为行索引(excel的行),可以是0~65535之间的任何一个
XSSFRow row1 = sheet.createRow(0);
// 创建单元格(excel的单元格,参数为列索引,可以是0~255之间的任何一个
XSSFCell cell = row1.createCell(0);
// 1.生成字体对象
XSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 12);
font.setFontName("新宋体");
// 2.生成样式对象,这里的设置居中样式和版本有关,我用的poi用HSSFCellStyle.ALIGN_CENTER会报错,所以用下面的
XSSFCellStyle style = wb.createCellStyle();
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置居中样式
style.setFont(font); // 调用字体样式对象
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.CENTER);// 设置居中样式
style.setFillForegroundColor((short) 40);// 设置背景色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 3.单元格应用样式
cell.setCellStyle(style);
// 创建标题
for (int i = 0; i < title.length; i++) {
cell = row1.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
sheet.autoSizeColumn((short) i);
}
// 创建内容
for (int i = 1; i < vehicleList.size() + 1; i++) {
// 单元格宽度自适应
XSSFRow row = sheet.createRow(i);
sheet.autoSizeColumn(i);
Vehicle vehiclei = vehicleList.get(i - 1);
// 车牌号码、所属企业、车辆状况、号牌种类、燃料种类、
row.createCell(0).setCellValue(vehiclei.getVehiclenum());
row.createCell(1).setCellValue(vehiclei.getEnterprisename());
if("1".equals(vehiclei.getIsscrap())) {
row.createCell(2).setCellValue("已报废");
} else {
row.createCell(2).setCellValue("在用");
}
row.createCell(3).setCellValue(vehiclei.getVehicletypenumbertypename());
row.createCell(4).setCellValue(vehiclei.getEnergytypename());
//车辆类型、车辆登记日期、发动冬季型号、车辆型号、车辆质量、排放标准、审核状态
row.createCell(5).setCellValue(vehiclei.getVehicletypename());
//格式化时间
String cdate =DateUtils.formatDateTime(vehiclei.getRegisterdate());
row.createCell(6).setCellValue(cdate);
row.createCell(7).setCellValue(vehiclei.getMotortype());
row.createCell(8).setCellValue(vehiclei.getVehiclemodel());
row.createCell(9).setCellValue(vehiclei.getMass());
row.createCell(10).setCellValue(vehiclei.getCodename());
if("0".equals(vehiclei.getIscheck())) {
row.createCell(11).setCellValue("待审核");
}else if ("1".equals(vehiclei.getIscheck())) {
row.createCell(11).setCellValue("已审核");
}else if ("2".equals(vehiclei.getIscheck())) {
row.createCell(11).setCellValue("不通过");
}
}
// 响应到客户端
try {
SysEnterpriseService.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}

 调用的响应到客户端的方法

//发送响应流方法
public static void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(),"ISO8859-1");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}

前端代码

第一种方式

js

function exportVecleInfo(){
window.location.href="url";

}

html:

<li class="btns"><input id="js-exportVecleInfo"
class="btn btn-primary" value="导出" type="button"
onclick="exportVecleInfo()" /></li>

第二种方式

function exportVecleInfo(){

var url = "url";

$('<form method="post" action="' + url + '"></form>').appendTo('body').submit().remove();

}

正文到此结束
本文目录