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

1.首先前端代码如下：

一个导出按钮：<input id="export" class="btn btn-primary" type="button" οnclick="a();" value="导出"/>

实现按钮的点击事件：

function a(currentPage) {
console.log("你好");
var dateFrom = document.getElementById("todayStart").value;
var dateTo = document.getElementById("todayEnd").value;
var url = "<c:url value='/'/>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<CommunityOrderObject> 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()) {
}

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

转载于:https://www.cnblogs.com/yinghuanan/p/9773815.html