一、批量导入（将excel文件转成list）
1. 前台代码逻辑
1）首先在html页面加入下面的代码（可以忽略界面的样式）
<label for="uploadFile" class="label-btn">
<img id="uploadbtn" src="../img/upload-on.png" >
<input type="file" name="" id="uploadFileWhite"
onchange="personUpload('uploadFileWhite')">
</label>
2）其次在引入的js里加入以下代码
uploadData = {
// 导入是否正在上传文件
personUploading:false,
//后台返回描述信息
returnStr: "",
}
//导入文件上传
function personUpload(id){
if( uploadData.personUploading === true ){
alerFunc("文件正在上传中，请稍候");
return false;
}
if($('#'+id)[0].files.length!=1){
//alert("请先选择文件");
return false;
}
var file = $('#'+id)[0].files[0];
var arr = file.name.split('.');
if(arr[1]!='xls'){
alerFunc("文件类型不匹配，请使用xls类型的excel导入");
return false;
}
var sendData = new FormData();
sendData.append('uploadFile', file);
sendData.append('cutFileName', file.name);
console.log("ajax请求批量上传人员信息");
var sendDate = new Date();
$.ajax({
url: "uploadImportAction!personUpload.action",
type: 'POST',
cache: false,
async:true,
data: sendData,
processData: false,
contentType: false,
dataType:"json",
beforeSend: function(){
uploadData.personUploading = true;
$('#uploadWait').show();//展示等待上传页面
},
success : function(response) {
var receiveDate = new Date();
var costTime = receiveDate.getTime() - sendDate.getTime();
console.log("批量上传成功" + ",花费时间为：" + costTime + "毫秒");
closeUploadDiv();
uploadData.returnStr = response.data.desc;
if(uploadData.returnStr=='ok'){
alerFunc("导入成功");
}else{
//显示返回信息div
show();
}
},
complete : function(){
uploadData.personUploading = false;
}
});
}
function onTrackUpload(response){
uploadData.returnStr = response.data.desc;
show();
}
function show(){
$('#tipDiv').show();
$('#tipContent').html(uploadData.returnStr);
}
2. 后台java导入代码逻辑
1）action 层
public class UploadAction extends BaseAction<Person>{
private static final long serialVersionUID = 1L;
private static Logger logger = Logger.getLogger(UploadAction.class);
private UploadService uploadService;
private File uploadFile;
private String cutFileName;
public String personUpload(){
logger.info("接收批量上传的请求");
Date start = new Date();try {
String desc = uploadService.personUpload(uploadFile);
result.addData("desc", desc);
logger.info("批量上传成功");
} catch (Exception e) {
result.setResult("error");
e.printStackTrace();
logger.error("批量上传失败！" + e.getMessage());
}
Date end = new Date();
long time = end.getTime() - start.getTime();
logger.info("录批量上传总耗时：" + time + "毫秒");
return SUCCESS;
}
public String getCutFileName() {
return cutFileName;
}
public void setCutFileName(String cutFileName) {
this.cutFileName = cutFileName;
}
public File getUploadFile() {
return uploadFile;
}
public void setUploadFile(File uploadFile) {
this.uploadFile = uploadFile;
}
public UploadService getUploadService() {
return uploadService;
}
public void setUploadService(UploadService uploadService) {
this.uploadService = uploadService;
}
public UploadAction() {
}
}
2）service层
@Override
public String personUpload(File uploadFile) {
// 1,读取Excel
FileInputStream inFile = null;
try {
inFile = new FileInputStream(uploadFile);
} catch (FileNotFoundException e) {
logger.error("读取excel表格失败：" + e);
}
HSSFWorkbook readbook = null;
try {
readbook = new HSSFWorkbook(inFile);
} catch (IOException e) {
logger.error("创建HSSFWorkbook失败：" + e);
}
// 定义执行结果
String returnStr="";
int successNum = 0;// 成功条数
int failNum = 0;// 失败条数 int nSheetNum = readbook.getNumberOfSheets();
if (nSheetNum > 0) {
HSSFSheet readSheet = null;
// 读取第一页
readSheet = readbook.getSheetAt(0);
// 从第二行开始读取
int first = readSheet.getFirstRowNum() + 1;
int last = readSheet.getLastRowNum();
HSSFRow getRow = null;
HSSFCell getCell = null; for (int curIndex = first; curIndex <= last; curIndex++) {
// 把excel单元格的内容保存到person对象
Person person = new Person();
getRow = readSheet.getRow(curIndex);
// 姓名
getCell = getRow.getCell(0);
if (getCell != null) {
getCell.setCellType(Cell.CELL_TYPE_STRING);
person.setName(getCell.getStringCellValue());
}
// 性别
getCell = getRow.getCell(1);
if (getCell != null) {
// 先设置Cell的类型，然后就可以把纯数字作为String类型读进来了，否则报错：Cannot get a text
// value from a numeric cell
getCell.setCellType(Cell.CELL_TYPE_STRING);
person.setGender(getCell.getStringCellValue());
}
// 描述
getCell = getRow.getCell(2);
if (getCell != null) {
getCell.setCellType(Cell.CELL_TYPE_STRING);
person.setDes(getCell.getStringCellValue());
} String currentTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
person.setLastEnterTime(currentTime);// 入库时间
logger.info("第"+curIndex+"行加载完毕");
// 保存到person表
if (person.getName() == null || person.getName() == "") {
failNum = failNum + 1;
returnStr = returnStr + "在" + curIndex + "行发生错误[姓名不能为空]<br>";
continue;
}
if (person.getIdNumber() == null || person.getIdNumber() == ""){
failNum = failNum + 1;
returnStr = returnStr + "在" + curIndex + "行发生错误[证件号码不能为空]<br>";
continue;
}
person.setUpdateTime(currentTime);
if (personDAO.saveOrUpdate(person)) {
successNum = successNum + 1;
} else {
failNum = failNum + 1;
returnStr = returnStr + "在" + curIndex + "行发生错误[数据库执行失败]<br>";
}
}
}
if(failNum>0){
return "导入成功["+successNum+"]条数据，导入失败["+failNum+"]条数据<br>" + returnStr;
}else{
return "ok";
} }
二、导出（将list转成excel文件）
导出可以分为全部导出和批量导出，下面以批量导出为例，直接上后台代码。
1）action层
public String exportByPage(){
result.setResult(JsonResult.SUCCESS);
logger.info("接收导出单页Excel请求==start=="+model.toString());
long start = System. currentTimeMillis();//毫秒数
try{
if (model.getStartDate() == null || "".equals(model.getStartDate()) || model.getEndDate() == null || "".equals(model.getEndDate())) {
result.setResult(JsonResult.ERROR);
result.setErrorMessage("开始时间和结束时间不能为空");
return SUCCESS;
}
if ("1".equals(model.getIsMatchCamera()) && (model.getMatchCameraList() == null || "".equals(model.getMatchCameraList()))) {
result.setResult(JsonResult.ERROR);
result.setErrorMessage("匹配的相机列表不能为空");
return SUCCESS;
}
String basePath = ServletActionContext.getServletContext().getRealPath("/download");
System.out.println(basePath);
List<String> cameraIdList = JSONArray.parseArray(model.getMatchCameraList(), String.class);
pageInfo.setPageSize(pageSize);
pageInfo.setPageNumber(pageNumber);
ExportResult exportResult = warningsService.getExcelOutPut(cameraIdList, model, pageInfo, basePath);
result.setExportNum(exportResult.getExportNum());
result.setExportTaskID(exportResult.getExportTaskID());
result.setFileURL(exportResult.getFileURL());
result.setResult(exportResult.getResult());
result.setErrorMessage(exportResult.getErrorMessage());
}catch(Exception e){
logger.error("导出单页Excel失败");
result.setResult(JsonResult.ERROR);
result.setErrorMessage("导出失败");
e.printStackTrace();
}
long end = System. currentTimeMillis();//毫秒数
long time = end - start;
logger.info("导出单页Excel==end==所需毫秒数为："+time);
return SUCCESS;
}
2）service层
@Override
public ExportResult getExcelOutPut(List<String> cameraIdList, SearchBean search, PageInfo pageInfo, String path) {
ExportResult rm = new ExportResult();
rm.setResult(ResultMessage.SUCCESS);
FileOutputStream bos = null;
try {
// 创建工作簿的实例
HSSFWorkbook workbook = new HSSFWorkbook();// 每个工作表格最多可以存储的条数
int mus = 50000;
// 查询出要导出的数据
List<Warnings> 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(new Date());
//创建目录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 = new FileOutputStream(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();
}
return rm;
} //用数据填充表格里每行的cell
private void setCellValue(Warnings warnings,HSSFCell cell,HSSFRow datarow,int cos){
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 void createAndDelFile(String rootPath,String path,String fileToday,String uuid){
File file = new File(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();
}
}
以上就是批量导入导出的代码。看起来是不是也很简单呢，后台框架使用的是struts2,spring,hibernate
欢迎关注微信公众号【Java典籍】，收看更多Java技术干货！
▼微信扫一扫下图↓↓↓二维码关注
