java中excel导入导出

最新推荐文章于 2024-08-28 16:35:10 发布

菜鸡rick

最新推荐文章于 2024-08-28 16:35:10 发布

阅读量117

点赞数

分类专栏：细节文章标签： java

本文链接：https://blog.csdn.net/whwwycl/article/details/109046559

版权

细节专栏收录该内容

19 篇文章 0 订阅

订阅专栏

本文档展示了如何使用Java的Apache POI库实现Excel文件的自动创建，包括表头样式设置和数据填充，以及从Excel导入数据并转换为List<Local>对象的过程。涉及到了单元格合并、字体样式设置和数据读取的细节。

摘要由CSDN通过智能技术生成

导出

 //生成存放地模板
    public static XSSFWorkbook creatPlaceModel(List<Local> locals) {
        try {
            //创建工作薄对象
            XSSFWorkbook workbook = new XSSFWorkbook();//这里也可以设置sheet的Name
            //创建工作表对象
            XSSFSheet sheet = workbook.createSheet();
            // 字体样式
            XSSFFont xssfFont = workbook.createFont();
            // 加粗
            xssfFont.setBold(true);
            // 字体名称
            xssfFont.setFontName("楷体");
            // 字体大小
            xssfFont.setFontHeight(12);

            // 表头样式
            XSSFCellStyle headStyle = workbook.createCellStyle();
            // 设置字体css
            headStyle.setFont(xssfFont);
            // 竖向居中
            headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 横向居中
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            // 边框
            headStyle.setBorderBottom(BorderStyle.THIN);
            headStyle.setBorderLeft(BorderStyle.THIN);
            headStyle.setBorderRight(BorderStyle.THIN);
            headStyle.setBorderTop(BorderStyle.THIN);

            // 内容字体样式
            XSSFFont contFont = workbook.createFont();
            // 加粗
            contFont.setBold(false);
            // 字体名称
            contFont.setFontName("楷体");
            // 字体大小
            contFont.setFontHeight(11);

            //创建工作表的行
            Row firstNow = sheet.createRow(0);

            Cell cell1 = firstNow.createCell(0);
            cell1.setCellValue("序号");
            cell1.setCellStyle(headStyle);
            // 列宽
//            sheet.setColumnWidth(cell1.getColumnIndex(), 60 * 50);
            Cell cell2 = firstNow.createCell(1);
            cell2.setCellValue("name1");
            cell2.setCellStyle(headStyle);
            Cell cell3 = firstNow.createCell(2);
            cell3.setCellStyle(headStyle);
            Cell cell4 = firstNow.createCell(3);
            cell4.setCellValue("name2");
            cell4.setCellStyle(headStyle);
            Cell cell5 = firstNow.createCell(4);
            cell5.setCellValue("name3");
            cell5.setCellStyle(headStyle);
            Cell cell6 = firstNow.createCell(5);
            cell6.setCellValue("name4");
            cell6.setCellStyle(headStyle);
            Cell cell7 = firstNow.createCell(6);
            cell7.setCellValue("name5");
            cell7.setCellStyle(headStyle);


            //合并单元格
            //第二行模板
            Row twoNow = sheet.createRow(1);

            Cell cell00 = twoNow.createCell(0);
            cell00.setCellStyle(headStyle);
            Cell cell01 = twoNow.createCell(1);
            cell01.setCellValue("名称");
            cell01.setCellStyle(headStyle);
            Cell cell02 = twoNow.createCell(2);
            cell02.setCellValue("编号");
            cell02.setCellStyle(headStyle);
            Cell cell03 = twoNow.createCell(3);
            cell03.setCellStyle(headStyle);
            Cell cell04 = twoNow.createCell(4);
            cell04.setCellStyle(headStyle);
            Cell cell05 = twoNow.createCell(5);
            cell05.setCellStyle(headStyle);
            Cell cell06 = twoNow.createCell(6);
            cell06.setCellStyle(headStyle);
            for (int i = 0; i < locals.size(); i++) {
                Row localNow = sheet.createRow(2 + i);

                Cell celo1 = localNow.createCell(0);
                celo1.setCellValue(i);
                celo1.setCellStyle(headStyle);
                Cell celo2 = localNow.createCell(1);
                celo2.setCellValue(locals.get(i).getName());
                celo2.setCellStyle(headStyle);
                Cell celo3 = localNow.createCell(2);
                celo3.setCellValue(locals.get(i).getNumber());
                celo3.setCellStyle(headStyle);
                Cell celo4 = localNow.createCell(3);
                celo4.setCellValue(locals.get(i).getCategory());
                celo4.setCellStyle(headStyle);
                Cell celo5 = localNow.createCell(4);
                celo5.setCellValue(locals.get(i).getRoomNo());
                celo5.setCellStyle(headStyle);
                Cell celo6 = localNow.createCell(5);
                celo6.setCellValue(locals.get(i).getCabinetNo());
                celo6.setCellStyle(headStyle);
                Cell celo7 = localNow.createCell(6);
                celo7.setCellValue(locals.get(i).getLatticeNo());
                celo7.setCellStyle(headStyle);
            }


            // 合并单元格第二个代表终止行，第三个起始列，第四个终止列
            /*
              第一个参数代表起始行，
             */

            CellRangeAddress region1 = new CellRangeAddress(0, 1, (short) 0, (short) 0);
            CellRangeAddress region2 = new CellRangeAddress(0, 0, (short) 1, (short) 2);
            CellRangeAddress region3 = new CellRangeAddress(0, 1, (short) 3, (short) 3);
            CellRangeAddress region4 = new CellRangeAddress(0, 1, (short) 4, (short) 4);
            CellRangeAddress region5 = new CellRangeAddress(0, 1, (short) 5, (short) 5);
            CellRangeAddress region6 = new CellRangeAddress(0, 1, (short) 6, (short) 6);

            sheet.addMergedRegion(region1);
            sheet.addMergedRegion(region2);
            sheet.addMergedRegion(region3);
            sheet.addMergedRegion(region4);
            sheet.addMergedRegion(region5);
            sheet.addMergedRegion(region6);

//        int coloumNum=sheet.getRow(0).getPhysicalNumberOfCells();//获取一行的列数
//        int rowNum=sheet.getLastRowNum();//获得总行数
//        System.out.println("列数："+coloumNum);
            workbook.setSheetName(0, "Place export");//设置sheet的Name
            File file = new File("/opt/PlaceExport.xlsx");
            if (!file.exists()) {
                file.createNewFile();
            }
            return workbook;
            //文档输出
//            FileOutputStream out = new FileOutputStream(file);
//            workbook.write(out);
//            out.close();
        } catch (Exception e) {
            System.out.println(e.toString());
        }
        return null;
    }

导入

 //导入存放地清单
    public static List<Local> importPlace(InputStream is) {
        List<Local> locals=new ArrayList<>();
        try {
            //获取系统文档
//            InputStream is = new FileInputStream("/opt/PlaceExport.xlsx");
            //创建工作薄对象
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            //创建工作表对象
            XSSFSheet sheet = workbook.getSheetAt(0);
            Long lid= System.currentTimeMillis();
            for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                Local local=new Local();
                local.setId((lid+i)+"");
                //得到Excel表格
                XSSFRow row = sheet.getRow(i);
                //得Excel工作表指定行的单元格
                XSSFCell cell = row.getCell(1);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                try{
                    local.setName(cell.getRichStringCellValue().getString());//名称
                }catch (Exception e){
                    e.printStackTrace();
                    System.out.println("名称类型不匹配");
                }

                XSSFCell cel2 = row.getCell(2);
                cel2.setCellType(Cell.CELL_TYPE_STRING);
                try{
                    local.setNumber(cel2.getRichStringCellValue().getString());//编号
                }catch (Exception e){
                    e.printStackTrace();
                    System.out.println("编号类型不匹配");
                }

                XSSFCell cel3 = row.getCell(3);
                cel3.setCellType(Cell.CELL_TYPE_STRING);
                try{
                    local.setCategory(cel3.getRichStringCellValue().getString());
                }catch (Exception e){
                    e.printStackTrace();
                    System.out.println("类型不匹配");
                }

                XSSFCell cel4 = row.getCell(4);
                cel4.setCellType(Cell.CELL_TYPE_STRING);
                try{
                    local.setRoomNo(cel4.getRichStringCellValue().getString());
                }catch (Exception e){
                    e.printStackTrace();
                    System.out.println("类型不匹配");
                }

                XSSFCell cel5 = row.getCell(5);
                cel5.setCellType(Cell.CELL_TYPE_STRING);
                try{
                    local.setCabinetNo(cel5.getRichStringCellValue().getString());
                }catch (Exception e){
                    e.printStackTrace();
                    System.out.println("类型不匹配");
                }

                XSSFCell cel6 = row.getCell(6);
                cel6.setCellType(Cell.CELL_TYPE_STRING);
                try{
                    local.setLatticeNo(cel6.getRichStringCellValue().getString());
                }catch (Exception e){
                    e.printStackTrace();
                    System.out.println("类型不匹配");
                }

                locals.add(local);
            }



        } catch (IOException e) {
            e.printStackTrace();
        }
        return locals;
    }