Spring MVC Excel的导入和导出

最新推荐文章于 2024-08-03 14:12:41 发布

低头望明月

最新推荐文章于 2024-08-03 14:12:41 发布

阅读量755

点赞数

分类专栏： EXCEL导入导出文章标签：导出EXCEL spring mvc

本文链接：https://blog.csdn.net/qq_25320187/article/details/76066958

版权

EXCEL导入导出专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1、EXCEL的导入

需要使用包：

1.1、Action

@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
@ResponseBody
public void importExcel(@ModelAttribute("javaBeanName") JavaBeanName m, @RequestParam("file") MultipartFile file) {
    try {
        attachmentService.saveAttachmentImport(file);
    } catch (Exception e) {
        e.printStackTrace();
   		//作其他异常处理（友好提示，根据框架来选择）
    }
    //可返回页面或是json数据提示导入成功
}

1.2、serviceImpl

1.2.1

@Override
public void saveImport(MultipartFile file) throws IOException {
//获取excel中数据，如1.2.2
    List<List<Object>> list = this.getExcelData(file);
    //1、转换excel的值
    for (int i = 0; i < list.size(); i++) {
        List<Object> lo = list.get(i);
        JavaBeanName javaBeanName = new JavaBeanName();
        javaBeanName.setFileName(String.valueOf(lo.get(0)));
        javaBeanName.setFileExtension(String.valueOf(lo.get(1)));
        //用来测试
        System.out.println("导入信息" + i + "-->文件名称:" + javaBeanName.getFileName() + "  类型：" + javaBeanName.getFileExtension());
    }
    //2、校验，返回错误信息
    // 3、保存到数据库
}

1.2.2

/**
 * 获取excel中的数据
 *
 * @param file
 * @return
 * @throws IOException
 */
private List<List<Object>> getExcelData(MultipartFile file) throws IOException {
    InputStream in = file.getInputStream();
    Workbook work = new HSSFWorkbook(in);
    List<List<Object>> list = new ArrayList<List<Object>>();
    ;
    Sheet sheet = null;
    Row row = null;
    Cell cell = null;
    //遍历Excel中所有的sheet
    for (int i = 0; i < work.getNumberOfSheets(); i++) {
        sheet = work.getSheetAt(i);
        if (sheet == null) {
            continue;
        }
        //遍历当前sheet中的所有行，可以控制从第几行开始获取值
        for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum() + 1; j++) {
            row = sheet.getRow(j);
            //||row.getFirstCellNum()==j
            if (row == null) {
                continue;
            }
            //遍历所有的列
            List<Object> li = new ArrayList<Object>();
            for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                cell = row.getCell(y);
						//转换excel中数据格式,如1.2.3
                li.add(this.getCellValue(cell));
            }
            list.add(li);
        }
    }
    work.close();
    in.close();
    return list;
}

1.2.3

/**
 * 转换excel中数据格式
 *
 * @param cell
 * @return
 */
private Object getCellValue(Cell cell) {
    Object value = null;
    DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  //日期格式化
    DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字

    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            value = cell.getRichStringCellValue().getString();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                value = df.format(cell.getNumericCellValue());
            } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                value = sdf.format(cell.getDateCellValue());
            } else {
                value = df2.format(cell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_BLANK:
            value = "";
            break;
        default:
            break;
    }
    return value;
}

2、EXCEL导出

2.1、Action

/**
 * excel导出
 * @param m
 * @return
 */
@RequestMapping(value = "/exportExcel", method = RequestMethod.POST)
@ResponseBody
public void exportExcel(@ModelAttribute("JavaBeanName") JavaBeanName m, HttpServletResponse response) {
    try {
        OutputStream os = null;
        HSSFWorkbook hssfWorkbook= javaBeanNameService.getExportExcel();
        os = response.getOutputStream();
                     response.reset();
        //设置导出的文件名           		
        response.setHeader("Content-disposition", "attachment; filename = " + URLEncoder.encode("附件信息.xls", "UTF-8"));
        response.setContentType("application/octet-streem");
        hssfWorkbook.write(os);
    } catch (Exception e) {
        e.printStackTrace();
       	//处理异常信息
    }
}

2.2、封装EXCEL数据

/**
     * 封装excel数据
     * @return
     */
    @Override
    public HSSFWorkbook getExportExcel() {
        HSSFWorkbook book = new HSSFWorkbook();
        //设置工作表名称
        HSSFSheet sheet = book.createSheet("Sheet1");
        sheet.autoSizeColumn(1, true);//自适应列宽度
        //样式设置
        HSSFCellStyle style = book.createCellStyle();
        //颜色
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = book.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);


        HSSFCellStyle style2 = book.createCellStyle();
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //设置上下左右边框
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //TODO 正式使用时从数据库中获取数据
        List<JavaBeanName>  list=new ArrayList<JavaBeanName>();
        JavaBeanName javaBeanName1=new JavaBeanName();
        javaBeanName1.setFileName("文件名称1");
        javaBeanName1.setFileExtension("文件类型1");
        javaBeanName1.setUpdateTime(new Date());
        list.add(javaBeanName1);
        JavaBeanName javaBeanName2=new JavaBeanName();
        javaBeanName2.setFileName("文件名称2");
        javaBeanName2.setFileExtension("文件类型2");
        javaBeanName2.setUpdateTime(new Date());
        list.add(javaBeanName2);
        //填充表头标题
        int colSize = list.size();
        System.out.println("size:" + colSize);
        //合并单元格供标题使用(表名)
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
        HSSFRow firstRow = sheet.createRow(0);//第几行（从0开始）
        HSSFCell firstCell = firstRow.createCell(0);
        firstCell.setCellValue("附件信息表");
        firstCell.setCellStyle(style);

        //填充表头header
        HSSFRow row = sheet.createRow(1);
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("附件名称");
        cell.setCellStyle(style2);
        HSSFCell cell2 = row.createCell(1);
        cell2.setCellValue("附件类型");
        cell2.setCellStyle(style2);
        HSSFCell cell3= row.createCell(2);
        cell3.setCellValue("上传时间");
        cell3.setCellStyle(style2);

        //填充表格内容
        System.out.println("list:" + list.size());
        for(int i=0; i<list.size(); i++) {
            HSSFRow row2 = sheet.createRow(i+2);//index：第几行
             for(int j=0;j<3;j++) {
                 HSSFCell cell4 = row2.createCell(j);//第几列：从0开始
                 if(j==0) {
                     cell4.setCellValue(list.get(i).getFileName());
                     cell4.setCellStyle(style2);
                 }
                 if(j==1) {
                     cell4.setCellValue(list.get(i).getFileExtension());
                     cell4.setCellStyle(style2);
                 }
                 if(j==3) {
                     cell4.setCellValue(list.get(i).getUpdateTime());
                     cell4.setCellStyle(style2);
                 }
             }
        }
        return book;
}

3、JSP页面

<form:form id="form" cssClass="form" commandName="m"
           action="${ctx}/xxx/xxxAction/add" acceptCharset="multipart/form-data"
           method="post">
    <table align="center">
        <tr>
            <td>导入文件</td>
            <td>
                <input name="file" id="file" class="easyui-filebox" data-options="prompt:'文件上传',buttonText:'选择文件'"
                      />
            </td>
        </tr>
        <tr>
            <td></td>
            <td> <input type="button" value="导入" οnclick="importFile()"/></td>
            <td> <input type="button" value="导出" οnclick="exportExcel()"/></td>
        </tr>
    </table>
</form:form>

<script type="text/javascript">
    
    /**
     * 导入
     * @returns {boolean}
     */
    function importFile(){
        var formData = new FormData($('#form')[0]);
        var file = formData.get("file");
        if (file.size == 0) {
            $.messager.alert("请选择文件！");
            return false;
        }
        $.ajaxform({
            url: ctx + "/xxx/xxxAction/importExcel",
            data: formData,
            processData: false,//不处理发送的数据
            contentType: false,//不要设置Content-Type请求头
            success: function (responseStr) {
                if (responseStr.code == "0") {
                    $.messager.alert(responseStr.message);
                    $grid.datagrid("reload");
                } else {
                    $.messager.alert(responseStr.message);
                }
            }
        });
    }
    /**
     * 导出
     */
    function exportExcel() {
        var form = $("<form>");   //定义一个form表单
        form.attr('style', 'display:none');   //在form表单中添加查询参数
        form.attr('target', '');
        form.attr('method', 'post');
        form.attr('action', ctx + "/xxx/xxxAction/exportExcel");
        $('body').append(form);  //将表单放置在web中
        form.submit();

    }
</script>