HAP自定义Excel导入

• 项⽬配置 编辑 src/main/resources/spring/applicationContext-beans.xml ⽂件，并添加以下内容：

<!--自定义上传配置-->
    <bean id="multipartResolver"
          class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <!--setthemaxuploadsize100MB-->
        <property name="maxUploadSize">
            <value>104857600</value>
        </property>
        <property name="maxInMemorySize">
            <value>4096</value>
        </property>
    </bean>

• 前端定义上传 Excel 的表单

<!--自定义导入-->
        <form method="POST" enctype="multipart/form-data" id="formUpload"
              action="${base.contextPath}/wht/ora/20796/org/access/export/excel/import/custom?${_csrf.parameterName}=${_csrf.token}">
            <div class="form-group pull-left">
                <div class="k-content">
                    <!--<label class="control-label" style="margin-top: 2px">Attachment</label>-->
                    <input type="file" id="upfile" name="upfile" style="display:none" onchange="changeAgentContent()"/>
                    <input style="width: 200px" value="" disabled id="inputFileAgent" datarole="maskedtextbox" type="text" class="k-textbox"/>
                    <input type="button" class="btn btn-primary"
                           onclick="document.getElementById('upfile').click()"
                           value='<@spring.message "选择文件"/>'/>
                    <script type="text/javascript">
                        function changeAgentContent() {
                            var value = document.getElementById("upfile").value;
                            var split = value.split("\\");
                            document.getElementById("inputFileAgent").value = split[split.length - 1]
                        }
                        function importExcel() {
                            $('#formUpload').ajaxSubmit({
                                dataType: 'json',
                                success: function (data) {
                                    if (data.success) {
                                        kendo.ui.showInfoDialog({
                                            message: data.message
                                        });
                                    } else {
                                        kendo.ui.showErrorDialog({
                                            message: data.message
                                        });
                                    }
                                }
                            });
                        }
                    </script>
                    <span class="btn btn-success" style="margin-left: 5px" onclick="importExcel()" type="button">
                        <i class="fa fa-arrow-circle-up" style="margin-right:3px;"></i><@spring.message "自定义导入"/></span>
                </div>
            </div>
        </form>
        <!--自定义导入结束--->

• 前端增加库引⽤
  将最新版的 jquery-form 库⽂件放置在 src/main/webapp/resources/js/jquery-form/jquery-form.js
  没有文件夹自己新建一个

<script src="${base.contextPath}/resources/js/jquery-form/jquery-form.js"></script>

• 前端⻚⾯增加导⼊⽅法
  在一个<script></script>添加下面代码，当点击“自定义导入"按钮后调用importExcel()方法，importExcel()将数据传送到后台

/**
 *Call api to import excel asynchronously
 */
   function importExcel() {
                      $('#formUpload').ajaxSubmit({
                                dataType: 'json',
                                success: function (data) {
                                    if (data.success) {
                                        kendo.ui.showInfoDialog({
                                            message: data.message
                                        });
                                    } else {
                                        kendo.ui.showErrorDialog({
                                            message: data.message
                                        });
                                    }
                                }
                            });
                        }

• 在控制器层添加 RESTful api 路径和⽅法

/***自定义导入*****/
    @RequestMapping(
            value = {"/wht/ora/20796/org/access/export/excel/import/custom"},
            method = {RequestMethod.POST}
    )
    public ResponseData uploadExcel(HttpServletRequest request, Locale locale, String
            contextPath) throws Exception {
        ResponseData rd = new ResponseData();
        IRequest requestCtx = createRequestContext(request);
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest)
                request;
        MultipartFile file = multipartRequest.getFile("upfile");
        if (file == null || file.isEmpty()) {
            rd.setSuccess(false);
            rd.setMessage("File is empty!");
            return rd;
        }
        InputStream in = file.getInputStream();
      //importExcel()方法在下面的IOra20796OrgAccessService定义
        return service.importExcel(in, file.getOriginalFilename());
    }

• 添加导⼊ Excel 的服务⽅法
  先在IOra20796OrgAccessService 接口中定义importExcel()

public interface IOra20796OrgAccessService extends IBaseService<Ora20796OrgAccess>, ProxySelf<IOra20796OrgAccessService> {
    List<Ora20796OrgAccess> selectOrgAccessByOrder(IRequest iRequest, Ora20796OrgAccess dto, int page, int pageSize);
    ResponseData importExcel(InputStream is, String fileName) throws Exception;
}

再在Ora20796OrgAccessServiceImpl中实现importExcel()

@Service
@Transactional(rollbackFor = Exception.class)
public class Ora20796OrgAccessServiceImpl extends BaseServiceImpl<Ora20796OrgAccess> implements IOra20796OrgAccessService {

    @Autowired
    private Ora20796OrgAccessMapper orgAccessMapper;
    @Autowired
    private ObjectMapper objectMapper;
    private final static String excel2003L =".xls";    //2003- 版本的excel
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel

    @Override
    public List<Ora20796OrgAccess> selectOrgAccessByOrder(IRequest iRequest, Ora20796OrgAccess dto, int page, int pageSize) {
        PageHelper.startPage(page, pageSize);
        return orgAccessMapper.selectOrgAccessByOrder(dto);
    }

    /***自定义导入****/

    public ResponseData importExcel(InputStream is, String fileName) throws Exception {
        ResponseData rd = new ResponseData();
        List<List<Object>> list = new ArrayList<List<Object>>();
// Create a new workbook
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));

        if (excel2003L.equals(fileType)) {
            workbook = new HSSFWorkbook(is); //2003-
        } else if (excel2007U.equals(fileType)) {
            workbook = new XSSFWorkbook(is); //2007+
        } else {
            throw new Exception("解析的⽂文件格式有误！");
        }
        Sheet worksheet = null;
        Row row = null;
        Cell cell = null;
//遍历Excel中所有的sheet
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            worksheet = workbook.getSheetAt(i);
            if (worksheet == null) {
                continue;
            }
//遍历当前sheet中的所有⾏行行
            for (int j = worksheet.getFirstRowNum(); j < worksheet.getLastRowNum() + 1; j++) {
                row = worksheet.getRow(j);
// 跳过空⾏行行和标题⾏行行（第⼀一⾏行行）
                if (row == null || row.getFirstCellNum() == j) {
                    continue;
                }
//遍历所有的列列
                List<Object> li = new ArrayList<Object>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    li.add(this.getCellValue(cell));
                }
                list.add(li);
            }
        }
        workbook.close();
// 从第⼆二⾏行行开始遍历
        for (int i = 1; i < list.size(); i++) {
            List<Object> excelRow = list.get(i);
            Ora20796OrgAccess dto = new Ora20796OrgAccess();
            dto.setAccessName(excelRow.get(1).toString());
            dto.setDescription(excelRow.get(2).toString());
            dto.setOrgType(excelRow.get(3).toString());
            dto.setOrgId(Long.parseLong(excelRow.get(4).toString()));
            dto.setRoleId(1L);
            dto.setUserId(1L);
            orgAccessMapper.insertSelective(dto);
        }
        rd.setSuccess(true);
        rd.setMessage("Import successfully!");
        return rd;
    }

    private Object getCellValue(Cell cell) {
        Object value = null;
        DecimalFormat df = new DecimalFormat("0"); //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //⽇日期格式化
        DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
        if (cell != null) {
            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;
            }
        } else {
            value = "";
        }
        return value;
    }
}

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '4-zh_CN' for key 'PRIMARY'