java导入导出_Java Excel导入导出（实战）

@Overridepublic ExportResult getExcelOutPut(ListcameraIdList, SearchBean search, PageInfo pageInfo, String path) {

ExportResult rm= newExportResult();

rm.setResult(ResultMessage.SUCCESS);FileOutputStream bos= null;

try{//创建工作簿的实例

HSSFWorkbook workbook = newHSSFWorkbook();//每个工作表格最多可以存储的条数

int mus = 50000;//查询出要导出的数据

List datalist =warningsDao.getHistoryWarnings(cameraIdList, search, pageInfo);//导出多少个excel

int avg = (int) datalist.size() /mus;//String uuid = UUID.randomUUID().toString();

String uuid =String.valueOf(System.currentTimeMillis());HSSFSheet sheet= null;

HSSFRow titlerow= null;

HSSFCell cell= null;

HSSFRow datarow= null;

String fileToday= DateUtils.formatTimeYMD(newDate());//创建目录rootPath

String rootPath = path + "/warnings/"+fileToday+"/";//创建文件夹及删除之前的文件

createAndDelFile(rootPath, path,fileToday,uuid);for (int z = 0; z < avg + 1; z++) {

logger.info("创建工作表实例");//创建工作表实例

sheet = workbook.createSheet("列表" + (z + 1));

titlerow= null;

cell= null;if (datalist.size() > 0) {

logger.info("创建表头");//创建表头

String[] title = { "监控点编号", "监控点名称", "检测日期时间", "检测类型", "故障类型", "故障描述", "检测码流类型", "视频质量级别","检测录像保存位置", "检测录像保存天数","录像丢失时段描述", "报警处理结果", "报警处理描述"};//创建标题行

titlerow = sheet.createRow(0);for (int i = 0; i < title.length; i++) {//创建单元格

cell = titlerow.createCell((short) i);/// 为每个单元格赋值

cell.setCellValue(title[i]);

}int firstNum = z *mus;int index = 0;//推送进度

sendWebsocketMsg(uuid, "filecopy", (int)70/(avg+1)*(z+1));

logger.info("用数据填充表格,firstNum="+firstNum+";datalist.size()="+datalist.size());//用数据填充表格

for (int i = 0; i < datalist.size(); i++) {if (index == mus || (firstNum + i ==datalist.size())) {break;

}short cos = 0;//创建数据行

datarow = sheet.createRow(i + 1);//得到datalist中的第i个对象

Warnings warnings = (Warnings) datalist.get(firstNum +i);

setCellValue(warnings, cell, datarow, cos);

index++;

}//写入一次文件

logger.info("开始创建告警导出文件");//推送进度//sendWebsocketMsg(uuid, "filetrans", (int)70/(avg+1)*(z+1));

String fileName = "warning"+fileToday+"("+z +").xls";

File f= new File(rootPath+uuid+"/", fileName);if (!f.exists()) {

f.createNewFile();

}

logger.info("开始写入告警文件");

bos= newFileOutputStream(f);

workbook.write(bos);

bos.flush();

bos.close();

logger.info("完成写入告警文件");//workbook.removeSheetAt(0);

}

}//将生成的文件放入文件夹中

logger.info("开始压缩，告警记录压缩路径："+rootPath+ "zip/warnings_"+uuid+".zip");

ZipCompressorByAnt zip= new ZipCompressorByAnt(rootPath+"zip/warnings_"+uuid+".zip");

zip.compressExe(rootPath+uuid);

logger.info("压缩结束");

rm.setFileURL("/videodetection/download"+"/warnings/"+fileToday+"/zip/warnings_"+uuid+".zip");

rm.setExportTaskID(uuid);

rm.setExportNum(datalist.size());

sendWebsocketMsg(uuid,"filepack", 100);//推送进度

} catch(Exception e) {

logger.error("导出告警信息发生异常:"+e);

rm.setResult(ResultMessage.ERROR);

rm.setErrorMessage("导出失败");

e.printStackTrace();

}returnrm;

}//用数据填充表格里每行的cell

private void setCellValue(Warnings warnings,HSSFCell cell,HSSFRow datarow,intcos){

cell= datarow.createCell(cos++);

cell.setCellValue(warnings.getCameraID());

cell= datarow.createCell(cos++);

cell.setCellValue(warnings.getCameraName());

cell= datarow.createCell(cos++);

cell.setCellValue(warnings.getDetectDateTime());

cell= datarow.createCell(cos++);//检测类型“real”：实时检测；“rec”：录像检测

if("real".equals(warnings.getDetectType())){

cell.setCellValue("实时检测");

}else if("rec".equals(warnings.getDetectType())){

cell.setCellValue("录像检测");

}

cell= datarow.createCell(cos++);

cell.setCellValue(warnings.getFaultType());

cell= datarow.createCell(cos++);

cell.setCellValue(warnings.getFaultDesc());

cell= datarow.createCell(cos++);//检测码流类型，包括“main”：主码流；“slaver”：从码流

if("main".equals(warnings.getDetectStream())){

cell.setCellValue("主码流");

}else if("slaver".equals(warnings.getDetectStream())){

cell.setCellValue("从码流");

}

cell= datarow.createCell(cos++);//视频质量级别：“highest”，“high”，“normal”，“low”，“lowest”

cell.setCellValue(getDetectLevel(warnings.getDetectLevel()));

cell= datarow.createCell(cos++);//检测录像保存位置，包括“plat”：平台录像；“pu”：前端录像

if("plat".equals(warnings.getDetectRecPositon())){

cell.setCellValue("平台录像");

}else if("pu".equals(warnings.getDetectRecPositon())){

cell.setCellValue("前端录像");

}

cell= datarow.createCell(cos++);

cell.setCellValue(warnings.getDetectRecDuration());

cell= datarow.createCell(cos++);

cell.setCellValue(getRecLostPeriodDesc(warnings.getRecLostPeriod()));//录像丢失时段描述

cell= datarow.createCell(cos++);

cell.setCellValue(getWarningHandle(warnings));//报警处理结果

cell= datarow.createCell(cos++);

cell.setCellValue(warnings.getWarningHandleDesc());

}//创建文件夹及删除之前的文件

private voidcreateAndDelFile(String rootPath,String path,String fileToday,String uuid){

File file= newFile(rootPath);if (!file.exists()) {

file.mkdirs();

}else{//删除昨天及以前的导出文件

deleteDir(new File(path + "/warnings/"), fileToday);

}//创建压缩文件文件夹

File fileZip = new File(rootPath+"zip/");if (!fileZip.exists()) {

fileZip.mkdirs();

}//创建某一客户端导出xls文件路径

File fileXLS = new File(rootPath+uuid+"/");if (!fileXLS.exists()) {

fileXLS.mkdirs();

}

}