layout: post title: springMCVC下poiExcel文件的导入导出 categories: [springMVC] tags: [poi]

date: 2015-10-10

使用springMVC 导入导出Excel表格

导出

使用springMVC的AbstractExcelView类，结合BeanNameViewSolver视图解析器 在 springmvc中配置 BeanName视图解析器

  <!-- 配置BeanNameViewSolver自定义视图的解析器 -->
  <bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver">
    <!-- 注意:配置该视图解析器必须配置优先级 -->
    <property name="order" value="1"/>
  </bean>

自定义视图类实现 AbstractExcelView 在设置 cell颜色的时候，要 使用不同的HSSFCellStyle对象。

/**
 * 参数map可以携带Handler方法传来数据然后生成Execl数据的。
 */
@Override
protected void buildExcelDocument(Map<String, Object> map,
        HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    // 创建 sheet
    HSSFSheet sheet = workbook.createSheet("域名解析");
    sheet.setDefaultColumnWidth(15); // 设置默认的单元格 宽度

    // 创建表头
    createTitles(sheet, workbook);

    // 填充数据
    createContent(map, sheet, workbook);

}

private void createContent(Map<String, Object> map, HSSFSheet sheet,
        HSSFWorkbook workbook) {
    ResolutionDto resolutionDto = (ResolutionDto) map.get("resolution");
    List<ResolutionDto> dataList = resolutionApp
            .queryResolution(resolutionDto);
    HSSFCellStyle style = getStyle(workbook);

    style.setFillForegroundColor(HSSFColor.YELLOW.index);
    HSSFRow row = null;
    HSSFCell cell = null;
    ResolutionDto dto = null;
    for (int i = 0; i < dataList.size(); i++) {
        dto = dataList.get(i);
        row = sheet.createRow(i + 1);
        // 填充单元格
        cell = row.createCell(0);
        cell.setCellValue(resolutionApp.getDomainName(dto.getDomainId()));
        cell.setCellStyle(style);
    }

}

private void createTitles(HSSFSheet sheet, HSSFWorkbook workbook) {
    // TODO Auto-generated method stub
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = null;

    HSSFCellStyle style = getStyle(workbook);
    style.setFillForegroundColor(HSSFColor.LIME.index);
    HSSFFont font = workbook.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    style.setFont(font);
    for (int i = 0; i < titles.length; i++) {
        cell = row.createCell(i);
        cell.setCellValue(new HSSFRichTextString(titles[i].split(":")[0]));
        cell.setCellStyle(style);
    }
}

private HSSFCellStyle getStyle(HSSFWorkbook workbook) {

    HSSFCellStyle style = workbook.createCellStyle(); // 设置单元格背景颜色
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置竖直方向居中
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置水平方向居中
    // style.setFillForegroundColor(HSSFColor.LIME.index);

    return style;
}

导入

导入使用MultipartResolver接口。

前台页面使用文件上传即可。

<input id="excelFile" runat="server" name="excelFile" accept="xls" type="file" /></td>
$.ajaxFileUpload({
    url:'../../../../resolution/import',//处理图片脚本
    secureuri :false,
    fileElementId :'excelFile',//file控件id
    dataType : 'json',
    success : function (data, status){
        if(typeof(data.error) != 'undefined'){
            if(data.error != ''){
                W.AA("1");
            }else{
                alert(data.msg);
                W.AA("1");
            }
        }
    },
    error: function(data, status, e){
        W.AA("1");
    }
});

MultipartResolver配置进springmvc.xml并设置字符集和流量大小

 <!-- 配置文件上传MultipartResolver -->
 <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
   <property name="defaultEncoding" value="utf-8"/>
   <property name="maxUploadSize" value="1048576"/> <!-- 1M -->
 </bean>

后台使用MultipartFile 处理文件。 Controller层

    @RequestMapping("/testUpload")
     public String testUpload(@RequestParam("file") MultipartFile[] file) throws IllegalStateException, IOException
     {
       for (MultipartFile multipartFile : file)
       {
        if(!multipartFile.isEmpty())
        {
          multipartFile.transferTo(new File("D:\\44\\"+multipartFile.getOriginalFilename()));     
        }
       }
       return "ok";
     }

app 层

public void readExcel(MultipartFile excelFile, String userId,
        String userName) throws IOException {
    if (!excelFile.isEmpty()) {
        LOGGER.info("开始读取Excel...");
        // 获取上传的文件名
        // String fileName = excelFile.getOriginalFilename();
        InputStream inputStream = excelFile.getInputStream();
        // 获取上传文件的后缀
        // String fileType = fileName.substring(fileName.lastIndexOf("."));
        // 使用 buffer流
        BufferedInputStream in = new BufferedInputStream(inputStream);
        HSSFWorkbook wb = new HSSFWorkbook(in);
        // 获取第一个表
        HSSFSheet sheet1 = wb.getSheetAt(0);
        ResolutionDto resolution = null;
        for (int rowNum = 1; rowNum <= sheet1.getLastRowNum(); rowNum++) {
            HSSFRow hssfRow = sheet1.getRow(rowNum);
            if (hssfRow != null) {
                // 获取域名名字
                String domainName = hssfRow.getCell(0).getStringCellValue();
                // 获取状态
                String status = hssfRow.getCell(7).getStringCellValue();

                // 赋值保存
                resolution = new ResolutionDto();
                RmDnsDomainExample example = new RmDnsDomainExample();
                example.createCriteria().andZoneEqualTo(domainName)
                        .andUserIdEqualTo(userId);
                // 根据域名的名字获取相应的域名ID

                try {
                    String domainId = rmDnsDomainDao.find(example).get(0)
                            .getId();
                    resolution.setDomainId(domainId);
                    resolution.setStatus("启动".equals(status) ? "1" : "2");

                    LOGGER.info("保存一个resolution对象: " + resolution);
                    saveResolution(resolution, userName);
                } catch (Exception e) {
                    LOGGER.debug("当前用户下该域名不存在 : 域名" + domainName);
                    e.printStackTrace();
                }

            }
        }
        LOGGER.info("Excel保存完毕...");
    }
}