文档章节

Java Excel导入导出(实战)

o
 osc_a22drz29
发布于 2019/03/28 14:48
字数 1917
阅读 47
收藏 0
cos

钉钉、微博极速扩容黑科技,点击观看阿里云弹性计算年度发布会!>>>

一、批量导入(将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技术干货!

  ▼微信扫一扫下图↓↓↓二维码关注

 

 

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
SpringBoot图文教程9—SpringBoot 导入导出 Excel 「Apache Poi」

有天上飞的概念,就要有落地的实现 概念十遍不如代码一遍,朋友,希望你把文中所有的代码案例都敲一遍 先赞后看,养成习惯 SpringBoot 图文教程系列文章目录 SpringBoot图文教程1「概念+案例...

osc_04fmvbv4
04/16
5
0
SpringBoot图文教程9—SpringBoot 导入导出 Excel 「Apache Poi」

本文分享自微信公众号 - 鹿小洋的Java笔记(lulaoshiJava)。 如有侵权,请联系 support@oschina.cn 删除。 本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。...

鹿老师的Java笔记
03/04
0
0
SpringBoot图文教程14—SpringBoot集成EasyExcel「上」

有天上飞的概念,就要有落地的实现 概念十遍不如代码一遍,朋友,希望你把文中所有的代码案例都敲一遍 先赞后看,养成习惯 SpringBoot 图文教程系列文章目录 SpringBoot图文教程1「概念+案例...

osc_ikbuen62
04/16
26
0
POI如何高效导出百万级Excel数据?

阅读原文:POI如何高效导出百万级Excel数据? 在一个具有统计功能的系统中,导出excel功能几乎是一定的,如何导出excel?导出的数据有多少?如何高效的导出? Excel简介 什么是excel就不用介...

码上实战
2019/03/08
1.4K
0
SpringBoot图文教程14—阿里开源EasyExcel「为百万数据读写设计」

本文分享自微信公众号 - 鹿小洋的Java笔记(lulaoshiJava)。 如有侵权,请联系 support@oschina.cn 删除。 本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。...

鹿老师的Java笔记
03/11
6
0

没有更多内容

加载失败,请刷新页面

加载更多

未捕获ReferenceError:未定义$? - Uncaught ReferenceError: $ is not defined?

问题: How come this code throws an 此代码如何引发 Uncaught ReferenceError: $ is not defined 未捕获的ReferenceError:未定义$ when it was OK before? 以前什么时候可以? $(document......

javail
20分钟前
12
0
263. Ugly Number

题目: 263. Ugly Number 题目地址:https://leetcode.com/problems/ugly-number/ Write a program to check whether a given number is an ugly number. Ugly numbers are positive numbers......

JiaMing
43分钟前
46
0
HCIA_ARP01

ARP(地址解析协议) eNSP 常用路由器:AR2220 常用交换机:S5700、S3700 常用终端:PC、MCS(主播服务器) 设备连线:Copper(以太网用到的双绞线)、Serial(串口线,2SA接口)、Auto(自动连...

创业789
46分钟前
19
0
如何在Rails 4中使用问题 - How to use concerns in Rails 4

问题: The default Rails 4 project generator now creates the directory "concerns" under controllers and models. 默认的Rails 4项目生成器现在在控制器和模型下创建目录“Concer”。 ......

fyin1314
50分钟前
19
0
【LeetCode】 57 括号生成

题目: 解题思路: https://leetcode-cn.com/problems/generate-parentheses/solution/hui-su-suan-fa-by-liweiwei1419/ 代码: import java.util.ArrayList;import java.util.List;publ......

JaneRoad
昨天
8
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部