excel导入导出与通过poi处理数据

导出excel文件

需要用到webUpLoader插件

 

• controller端代码

@RequiresPermissions("financial:financialList:list")
@RequestMapping("export")
public String exportFile(WorkOrder workOrder, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes) throws Exception {
    try {
        String fileName = "BankStatement" + DateUtils.getDate("yyyyMMddHHmmss")+".xlsx";
        List<TestingRule> list = new ArrayList<>();
        new ExportExcel("模版", 实体类.class).setDataList(list).write(response, fileName).dispose();
    } catch (Exception e) {
        e.printStackTrace();
        response.getWriter().write("<script>top.layer.alert('导出失败!', {icon: 0, title:'提示'})</script>");
    }
    return null;
}

• html端代码

<button type="button" id="export-btn" class="btn btn-primary btn-sm" data-toggle="tooltip" data-placement="left" title="导出Excel">
<i class="glyphicon glyphicon-cloud-upload"></i>导出Excel
</button>
<script>
    $(function(){
        $("#export-btn").on("click", function () {
            location.href = "${ctx}/system/sys/testingRule/export";
        });
    });
</script>

导入excel读取内容

• controller端代码

//上传模版
    @RequestMapping("upload")
    @ResponseBody
    public AjaxJson upload(@RequestParam MultipartFile file) {
        try {
            //将MultiparFile文件转换成InputStream
            CommonsMultipartFile cf = (CommonsMultipartFile) file;
            InputStream inputStream = cf.getInputStream();
            //获取excel工作簿
            Workbook workbook = new HSSFWorkbook(inputStream);
            //获取第一个表
            Sheet sheet=workbook.getSheetAt(0);
            //获取行数
            int rows = sheet.getLastRowNum();
            List<String> list = new ArrayList<>();
            //遍历所有行
            for(int i = 0; i<=rows ; i++) {
                //获取第i行第一个单元格内容并自动转为String类型
                list.add(this.getStringValueFromCell(sheet.getRow(i).getCell(0)));
            }
            
            AjaxJson json = getSuccessAjaxJson("上传成功!");
            json.getBody().put("uploadId", "ss");
            return json;
        } catch (Exception e) {
            e.printStackTrace();
            return getErrorAjaxJson("1", "上传失败!");
        }
    }

• 获取excel 文件内容自动更改为String

public static String getStringValueFromCell(Cell cell) {
        SimpleDateFormat sFormat = new SimpleDateFormat("MM/dd/yyyy");
        DecimalFormat decimalFormat = new DecimalFormat("#.#");
        String cellValue = "";
        if(cell == null) {
            return cellValue;
        }
        else if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
            cellValue = cell.getStringCellValue();
        }

        else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
            if(HSSFDateUtil.isCellDateFormatted(cell)) {
                double d = cell.getNumericCellValue();
                Date date = HSSFDateUtil.getJavaDate(d);
                cellValue = sFormat.format(date);
            }
            else {
                cellValue = decimalFormat.format((cell.getNumericCellValue()));
            }
        }
        else if(cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            cellValue = "";
        }
        else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            cellValue = String.valueOf(cell.getBooleanCellValue());
        }
        else if(cell.getCellType() == Cell.CELL_TYPE_ERROR) {
            cellValue = "";
        }
        else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            cellValue = cell.getCellFormula().toString();
        }
        return cellValue;
    }

• 导入html

$(function (){
            // 初始化 WebUploader
            var uploader = WebUploader.create({
                auto: true,
                swf: '${ctxStatic}/webuploader-0.1.5/Uploader.swf',
                server: '${ctx}/system/sys/testingRule/upload',
                resize: false,
                pick: '#filePicker',        //绑定div转为按钮
                duplicate: true,
                fileNumLimit: 1
            });
            $("#qwe").click(function(){
               $("#filePicker").find("input:file").trigger("click");
            })
            uploader.on( 'uploadSuccess', function( file ) {
                layer.alert("导入成功！");
                location.reload();
            });
            uploader.on( 'uploadError', function( file ) {
                layer.alert("导入出错！");
            });
        });

<div id="filePicker" class="hidden"></div>
<button id="qwe" type="button" class="btn btn-primary btn-sm">
     <i class="glyphicon glyphicon-cloud-upload"></i>&nbsp;上传
</button>