因为业务需求,需要将数据库中的日期类型的数据导出为可以按年月日时分秒筛选的格式,百度良久无果,最后自己探索出一个简单实用的方法,分享如下:
1:需要事前创建好Excel模板(包含表头),将表格中日期那一列的单元格格式改为日期格式,具体如下:
2.将模板创建好之后就是上代码了:
private void exportData(WorkOrder workOrder,HttpServletRequest request,HttpServletResponse response){
OutputStream out = null;
InputStream is = null;
try {
//获取模板的路径
String path = request.getRealPath("/WEB-INF/classes/templates/deliverData.xls");
FileInputStream template = new FileInputStream(path);
HSSFWorkbook workbook=new HSSFWorkbook(template);
// 获取第一个sheet页
HSSFSheet sheet=workbook.getSheetAt(0);
//设置导出数据格式为Excel
response.reset();
response.setContentType("application/vnd.ms-excel");
String fileName=java.net.urlencoder.encode('数据.xls','UTF-8');
response.setheader('Content-disposition','attachment;filename='+new String(filename.getBytes('utf-8'),'GBK'));
//将要遍历的数据以list集合的形式查询
List list = findExportOrder(workOrder);
if (null != list && list.size() > 0) {
//获取第一行,用于创建第二行
HSSFRow row = sheet.getRow(0);
StringBuffer stringBuffer = new StringBuffer();
//遍历list结果集
for (int i = 0; i < list.size(); i++) {
Object[] dd = (Object[]) list.get(i);
String ss = toString(dd).replaceAll("null", "");
stringBuffer.append(ss);
stringBuffer.deleteCharAt(stringBuffer.length() - 1);
stringBuffer.deleteCharAt(0);
//创建下一行
row = sheet.createRow(i + 1);
//将处理过的数据拆分成字符串数组
String[] rowArr = stringBuffer.toString().split(",,,");
for (int j = 0; j < rowArr.length; j++) {
HSSFCell cell = row.createCell(j);
//将制定位置的数据转换为日期类型的数据
if(j == 8 || j == 17 || j == 19 ){
if(!StringUtils.isBlank(rowArr[j])){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(sdf.parse(rowArr[j].trim()));
continue;
}
}
cell.setCellValue(rowArr[j].trim());
}
stringBuffer.setLength(0);
}
}
out = response.getOutputStream();
workbook.write(out);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.flush();
out.close();
} catch (IOException e) {
}
}
}
3.注意事项:在查询数据库的时候,直接将数据查询出来就行,不需要将数据库的日期做任何形式的转换。