Java Excel导入导出（实战）

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

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