java数据导入excel表_在java中实现数据导入excel表格中

1.首先前端代码如下：

一个导出按钮：

实现按钮的点击事件：

function a(currentPage) {

console.log("你好");

var dateFrom = document.getElementById("todayStart").value;

var dateTo = document.getElementById("todayEnd").value;

var url = "communityOrder/export?dateFrom="+dateFrom+"&dateTo="+dateTo;

window.location.href = url;

}

其中dateTo和var dateFrom是条件查询，url是请求后台的路径

2.后台代码如下：

/**

* 导出数据

* @param response

*/

@RequestMapping(value = "/export")

public void export(HttpSession session,HttpServletResponse response, HttpServletRequest request,

Model model,

@RequestParam(value = "dateFrom",required = false)String dateFrom,

@RequestParam(value = "dateTo",required = false)String dateTo,

@ModelAttribute("pageSupport")PageResult pageResult) throws IOException{

Date dDateFrom = null;

Date dDateTo = null;

Long startTime=null;

Long endTime=null;

// 处理申请日

if (!ComUtil.isEmpty(dateFrom) && dateFrom.matches("2[0-9]{7}")) {

dDateFrom = ComUtil.parseDate(dateFrom + "000000");

startTime=dDateFrom.getTime()/1000;

}

if (!ComUtil.isEmpty(dateTo) && dateTo.matches("2[0-9]{7}")) {

dDateTo = ComUtil.parseDate(dateTo + "235959");

endTime=dDateTo.getTime()/1000;

}

//三大步

//第一步:查询数据

Integer totalCount =communityOrderService.getAllCount(startTime,endTime);

List list = communityOrderService.findByPage(startTime,endTime,1, totalCount);

//第二步：将列表写成excel文件流

//平时如何写excel？

//1.创建一个excel的工作簿(创建是97格式还是2007的格式)

try (HSSFWorkbook workbook = new HSSFWorkbook()) {

//2.创建一个工作表(sheet)

//workbook.createSheet();

HSSFSheet sheet = workbook.createSheet("物业订单");

//3.先创建第一行，写excel表格：标题一列的信息

HSSFRow headerRow = sheet.createRow(0);

headerRow.createCell(0).setCellValue("编号");

headerRow.createCell(1).setCellValue("会员编号");

headerRow.createCell(2).setCellValue("订单号");

headerRow.createCell(3).setCellValue("房间编号");

headerRow.createCell(4).setCellValue("支付金额");

headerRow.createCell(5).setCellValue("支付方式");

headerRow.createCell(6).setCellValue("支付时间");

headerRow.createCell(7).setCellValue("创建时间");

//创建其他行，数据行

for (CommunityOrderObject CommunityOrderObject : list) {

HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum()+1);

dataRow.createCell(0).setCellValue(CommunityOrderObject.getId());

dataRow.createCell(1).setCellValue(CommunityOrderObject.getMobile());

dataRow.createCell(2).setCellValue(CommunityOrderObject.getSn());

dataRow.createCell(3).setCellValue(CommunityOrderObject.getRoomId());

dataRow.createCell(4).setCellValue(CommunityOrderObject.getPayFee());

if(StringUtils.equals(CommunityOrderObject.getPayType(),"offline")){

dataRow.createCell(5).setCellValue("线下支付");

}

if(StringUtils.equals(CommunityOrderObject.getPayType(),"alipay")){

dataRow.createCell(5).setCellValue("线上支付");

}

String payTime = CommunityOrderObject.getPayTime();

/* Date d =new Date();

if(null!=payTime&&!"".equals(payTime.trim())){

d= new Date(Long.valueOf(payTime)*1000l);

}

String format1 = sdf2.format(d);*/

if(null!=payTime&&!"".equals(payTime)&&"1970-01-01".equals(payTime.trim())){

dataRow.createCell(6).setCellValue("");

}else{

dataRow.createCell(6).setCellValue(payTime);

}

Date createdOn = CommunityOrderObject.getCreatedOn();

String format1 = sdf2.format(createdOn);

dataRow.createCell(7).setCellValue(format1);

}

//第三步：将文件流写入响应，交给客户端浏览器

//1。设置文件类型

//文件名

StringBuffer sb = new StringBuffer();

Date date = new Date();

String time = sdf2.format(date);

sb.append(time).append("物业订单.xls");

String downFilename= sb.toString();

//获取文件的MIME类型：

String contentType = request.getContentType();

//String contentType=" multipart/form-data";两种方式都可以

//将MIME类型放入响应

response.setContentType(contentType);

//2,文件名和附件打开方式

//浏览器类型

String agent = request.getHeader("user-agent");

//附件名编码，解决中文乱码问题

downFilename = ComUtil.encodeDownloadFilename(downFilename, agent);

//获取附件的名字和下载方式

String contentDisposition="attachment;filename="+downFilename;

//将附件名字和下载方式放入响应头信息中

response.setHeader("Content-Disposition", contentDisposition);

response.setCharacterEncoding("UTF-8");

//3.写入到响应

workbook.write(response.getOutputStream());

}//end of try (HSSFWorkbook workbook = new HSSFWorkbook()) {

}

在这里需要注意的是条件查询和没有条件查询结果的导出