Java导入Excel中的数据，及一些Excel的逻辑判断

来源：互联网发布：特朗普的对华政策知乎编辑：程序博客网时间：2024/05/21 00:54

Java上传Excel

写的Execl上传包含了很多逻辑和需求，如果业务不需要那么复杂的话可以省略一些判断,只用看readSheet、excData这两个方法，一个读数据，一个讲数据转换为我们需要的实体类

尬包

import jxl.Cell;import jxl.Sheet;import jxl.Workbook;import jxl.read.biff.BiffException;

代码

@Service@Transactionalpublic class ExcelUploadServiceImpl implements ExcelUploadService{    //判断excel的大小    public static final int FILE_MAX_SIZE = 1024 * 1024 * 50;    private String fileFileName;    public File readFile = null;    private File file;    public File getFile() {            return file;        }    public void setFile(File file) {        this.file = file;    }    public String getFileFileName() {        return fileFileName;    }    public void setFileFileName(String fileFileName) {        this.fileFileName = fileFileName;    }    public String excelUpload() throws Exception {        this.readFile = readFile;        Map<String, Object> mapresult = new HashMap<String, Object>();        //判断文件是否是xls文件        if (!(fileFileName.endsWith("xls"))) {            msg = getText("请上传xls格式");            mapresult.put("msg", msg);            mapresult.put("result", "0");            if (request.getAttribute("token") == "false-null") {                mapresult.put("token", "false-null");            }            //为angularJS返回值替换为你们用到的前端返回值就行            this.setResponseJson(mapresult);            return SUCCESS;        }        //msg为解析错误时返回的信息        String msg = excelUploadService.excelUpload(file);        return msg;}    public String excelUpload(File readFile){        // 1.判断该路径下文件是否存在。        if (!readFile.exists())        {            result = "文件不存在！";            logger.info("Files don't exist! ");            return result;        }        // 2.判断文件内容是否符合规则。        if (!validateFile(readFile))        {            result = "格式非法！";            logger.info("The format of illegal ");            return result;        }        // 3.解析每一个excel内容        result = readExcel(readFile);        if (!isNullOrEmpty(result))        {            return result;        }        return result;}    /**     * 读取EXCEL     *      * @param historyMap     * @param historyinfoList     */    private String readExcel(File readFile){        String result = "";        Workbook rwb = null;        // 创建输入流        InputStream stream = null;        try        {            stream = new FileInputStream(readFile);        } catch (FileNotFoundException e1)        {            result = "excel文件不存在";            logger.error("Excel file does not exist ", e1);            return result;        }        // 获取Excel文件对象        try        {            rwb = Workbook.getWorkbook(stream);        } catch (BiffException e)        {            result = "Excel parse error ";            logger.error("Excel parse error ", e);            return result;        } catch (IOException e)        {            result = "excel解析错误";            logger.error("Excel parse error ", e);            return result;        } finally        {            try            {                if (null != stream)                {                    stream.close();// 流关闭                }            } catch (IOException e)            {                result = "excel流关闭错误";                logger.error("Excel stream closure error ", e);                return result;            }        }        // 读取sheet页内容,将列内容组织成list        List<String[]> serverList = readSheet(rwb.getSheet(0));        //数据处理        result = Excute(serverList);        if (!Validate.isNullOrEmpty(result))        {            return result;        }        return result;    }    /**     * 对数据进行处理     *      * @param serverList     * @param historyMap     * @param historyinfoList     */    private String Excute(List<String[]> serverList)    {        String result = "";        // 1.判断列名是否符合规格        Workbook wb = null;        try        {            wb = Workbook.getWorkbook(readFile);        } catch (BiffException e)        {            logger.error("", e);        } catch (IOException e)        {            logger.error("", e);        }        if (!columnNameMatch(deviceColumnName, serverList,wb.getSheet(0).getName()))        {            result = "sheet页格式非法!";            logger.info("Illegal sheet page format ");            return result;        }        // 2.将list转成beanLsit        List<DeviceRowBean> deList = excData(serverList);        if (deList.size() > 3000)        {            result = "导入数据超过3000条！";            logger.info("Import data over 3000 ");            return result;        }        //对获得的Excel数据进行处理        return "上传完了";    }    /**     * 将list转成beanLsit     */    private List<DeviceRowBean> excData(List<String[]> serverList)    {        List<DeviceRowBean> deviceLst_tmp = new ArrayList<DeviceRowBean>();        for (int i = 1; i < serverList.size(); i++)        {            String[] serverRowData = serverList.get(i);            DeviceRowBean device = new DeviceRowBean();            device.setSn(serverRowData[0].replaceAll("\n", "").trim());            device.setHostname(serverRowData[1].replaceAll("\n", "").trim());            device.setBmcIp(serverRowData[2].replaceAll("\n", "").trim());            device.setBmcUserPass(serverRowData[3].replaceAll("\n", "").trim());            device.setBmcipSetting(serverRowData[4].replaceAll("\n", "").trim());            deviceLst_tmp.add(device);        }        return deviceLst_tmp;    }    /**     * 空值判断     * */    public static boolean isNullOrEmpty(String string) {        if (null == string || "".equals(string.trim())) {            return true;        }        return false;    }    /**     * 判断文件内容是否符合规则,只允许2003     * */    public static boolean validateFile(File file) {        // 1.是否为excel格式        if (!file.getAbsolutePath().endsWith("tmp")) {            logger.info("file no excel format,path for :"                    + file.getAbsolutePath());            return false;        }        // 2.判断excel大小        if (file.length() > FILE_MAX_SIZE) {            logger.info("file size More than quota,path for :"                    + file.getAbsolutePath());            return false;        }        // 3.判断sheet页名称是否合法import jxl.Workbook;        Workbook rwb = null;        // 创建输入流        InputStream stream = null;        try {            stream = new FileInputStream(file);        } catch (FileNotFoundException e1) {            logger.error("", e1);        }        // 获取Excel文件对象        try {            rwb = Workbook.getWorkbook(stream);        } catch (BiffException e) {            logger.error("", e);        } catch (IOException e) {            logger.error("", e);        } catch (Exception e) {            logger.error("", e);        }finally {            if (stream != null)                try {                    stream.close();// 流关闭                } catch (IOException e) {                    logger.error(e);                }        }        if (null==rwb) {            logger.info("File read flow error, possibly XLXS changed the suffix ");            return false;        }        if (rwb.getSheets().length != 1                || !rwb.getSheet(0).getName().equals("excel名称")) {            logger.info("The sheet name or format does not match, the path is  :"                    + file.getAbsolutePath());            return false;        }        return true;    }    /**     * 读取sheet页     */    public List<String[]> readSheet(Sheet sheet)    {        // 创建一个list用来存读取的内容        List<String[]> list = new ArrayList<String[]>();        Cell cell = null;        // 行数(表头的目录不需要，从1开始)        for (int i = 0; i < sheet.getRows(); i++)        {            // 创建一个数组 用来存储每一列的值            String[] str = new String[sheet.getColumns()];            // 列数            for (int j = 0; j < sheet.getColumns(); j++)            {                // 获取numberi行，numberj列的值                cell = sheet.getCell(j, i);                str[j] = cell.getContents();            }            list.add(str);        }        return list;    }    /**     * 判断列名是否符合规则     *      * @param servercolumnname     * */    public static boolean columnNameMatch(String[] sheetColumnName,            List<String[]> sheetDataList, String sheetName) {        if (sheetColumnName.length != sheetDataList.get(0).length) {            logger.info(sheetName                    + "Sheet format is illegal and the number of columns is inconsistent");            return false;        }        for (int i = 0; i < sheetColumnName.length; i++) {            if (!sheetColumnName[i].equals(sheetDataList.get(0)[i])) {                logger.info(sheetName + "Sheet format is illegal, first "                        + (i + 1) + "The column names are different: "                        + sheetColumnName[i] + "," + sheetDataList.get(0)[i]);                return false;            }        }        return true;    }}

阅读全文

0 0