sharp-excel使用指北:从模版文件中导出内容(三)

我有个采购订单模版, 通过数据选渲染模版,然后导出. 模版有些cell是需要替换文字, 有些需要自动插入行, 印章(图片)的插入.

模版文件:

http://xhope.top/wp-content/uploads/2023/08/tpl.png

代码示例子:

@Test
    public void testWriteFromTemplate() throws IOException {
         String template = "/Users/rick/Space/Workspace/sharp-admin/src/main/resources/templates/excel/po.xlsx";
        String dist = "/Users/rick/Space/Workspace/sharp-admin/src/main/resources/templates/excel/dist.xlsx";

        // 这样就不会改变模版文件
        byte[] bytes = IOUtils.toByteArray(new FileInputStream(template));

        ExcelWriter excelWriter = new ExcelWriter(new XSSFWorkbook(new ByteArrayInputStream(bytes)));

        excelWriter.writeCell(new ExcelCell(7, 3, "PO NO: PY20230726-100"));

        excelWriter.writeCell(new ExcelCell(1, 5, "供方(Vendor):苏州XX工程有限公司"));
        excelWriter.writeCell(new ExcelCell(2, 6, "李总 18898876623"));
        excelWriter.writeCell(new ExcelCell(2, 7, "0512-88359511"));
        excelWriter.writeCell(new ExcelCell(2, 8, "0512-88359511"));
        excelWriter.writeCell(new ExcelCell(1, 9, "ADD:  江苏省南京市武侯区"));


//        excelWriter.writeCell(new ExcelCell(7, 5, "需方:XX电机制造(苏州)有限公司"));
//        excelWriter.writeCell(new ExcelCell(7, 6, "慧Xx 18898876623"));
//        excelWriter.writeCell(new ExcelCell(7, 7, "0512-77359511"));
//        excelWriter.writeCell(new ExcelCell(7, 8, "0512-77359511"));
//        excelWriter.writeCell(new ExcelCell(7, 10, "交货地点:苏州市高新区"));


        ArrayList<Object[]> data = new ArrayList<>();
        data.add(new Object[]{1, "资材编号1", "品 名", "型号规格", 3, "单位", 1, 11, "2022-11-16"});
        data.add(new Object[]{2, "资材编号2", "品 名", "型号规格", 3, "单位", 1, 12, "2022-11-16"});
        data.add(new Object[]{3, "资材编号3", "品 名", "型号规格", 3, "单位", 1, 11, "2022-11-16"});
        data.add(new Object[]{4, "资材编号4", "品 名", "型号规格", 3, "单位", 1, 12, "2022-11-16"});

        int rowSize = data.size();

        // 获取 cell 样式
        XSSFRow row = excelWriter.getActiveSheet().getRow(11);
        int physicalNumberOfCells = row.getPhysicalNumberOfCells();
        XSSFCellStyle[] cellStyles = new XSSFCellStyle[physicalNumberOfCells];
        for (int i = 0; i < physicalNumberOfCells; i++) {
            cellStyles[i] = row.getCell(i).getCellStyle();
        }

        XSSFColor redColor = new XSSFColor(Color.RED, new DefaultIndexedColorMap());

        // 文字色
        XSSFFont font = excelWriter.getBook().createFont();
        font.setColor(redColor);
        font.setBold(true);

        excelWriter.insertAndWriteRow(1, 12, data, row.getHeightInPoints(), cellStyles, (ecell, cell) -> {
            if (ecell.getX() == 9 && !String.valueOf(ecell.getValue()).matches("\\d{4}-\\d{2}-\\d{2}")) {
                XSSFCellStyle newStyle = cell.getCellStyle().copy();
                // 如果非日期类型, 表示是备注,则用红色加粗显示. CellStyle是copy的, 但是 XSSFFont 需要创建, 然后再赋值. 不然是引用, 会影响其他的cell
                font.setFamily(newStyle.getFont().getFamily());
                font.setFontName(newStyle.getFont().getFontName());
                font.setFontHeight(newStyle.getFont().getFontHeight());
                newStyle.setFont(font);
                cell.setCellStyle(newStyle);
            }
        });

        excelWriter.writeCell(new ExcelCell(3, 13 + rowSize, "¥46.00"));
        excelWriter.writeCell(new ExcelCell(3, 14 + rowSize, "RMB46"));
        excelWriter.writeCell(new ExcelCell(2, 15 + rowSize, "哈哈"));
        excelWriter.writeCell(new ExcelCell(4, 28 + rowSize, "本公司确认:王总2023/08/10"));

        excelWriter.getBook().setSheetName(0, "20230810");

        // 插入图片
        //创建一个excel文件,名称为:
        XSSFWorkbook workbook = excelWriter.getBook();
        //创建一个sheet,名称为工作簿1
        XSSFSheet sheet = excelWriter.getActiveSheet();

        BufferedImage bufferImg;
        //先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        String imageUrl = "/Users/rick/Space/Share/seal.png";

        //获取图片后缀
        bufferImg = ImageIO.read(new File(imageUrl));
        ImageIO.write(bufferImg, "png", byteArrayOut);

        //画图的顶级管理器,一个sheet只能获取一个(一定要注意这点)
        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        //anchor主要用于设置图片的属性
        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 7, 19 + rowSize, (short) 9, 30 + rowSize);
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
        //插入图片
        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));

        excelWriter.toFile(new FileOutputStream(dist));
    }

sharp-database 使用注解 @Sql 自定义查询

sharp-database 使用注解 @Sql 自定义查询

实体类中添加注解 @Sql

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Table(value = "t_book", comment = "书")
public class Book extends BaseEntity {

    private String title;

    /**
     * 书的拥有者
     */
    @ParamName({"person_id", "personId"})
    @JsonAlias("personId")
    @JsonDeserialize(using = EntityWithLongIdPropertyDeserializer.class)
    @ManyToOne(value = "person_id", parentTable = "t_person")
    private Person person;

    @Sql("select * from t_person")
    private List<Person> allPerson;

    @Sql("select * from t_person where id = 552098712424472576")
    private Person p1;

    @Sql(value = "select * from t_person where id = :id", params = "id@person.id")
    private Person p2;

    @Sql(value = "select * from t_person where id = :id AND name like :title", params = "id@person.id, title@title")
    private Person p3;

}
public @interface Sql {

    String value();

    String params() default "";

    /**
     * 参数为null的时候, 直接返回结果null,无需数据库查询
     * @return
     */
    String[] nullWhenParamsIsNull() default {};

}

value: sql 语句
params: sql 参数. 多个参数用 , 隔开; 每个参数 参数名@参数值. 参数值可以是静态的,也可以是来自对象的属性值
nullWhenParamsIsNull: 参数为null的时候, 直接返回结果null,无需数据库查询

Bean类中添加注解 @Sql

BookQuery.java 虽然标记 @Table 但是不是实体对象

@Data
@Table
public class BookQuery {

    private Long id;

    private String title;

    private String personId;

    @Sql(value = "select * from t_person where id = :personId", params = "personId@personId")
    private Person person;

    /**
     * 标签
     */
    @Sql(value = "select t_tag.* from t_book, t_tag, t_book_tag where t_book_tag.book_id = t_book.id AND t_book_tag.tag_id = t_tag.id AND t_book.id = :id", params = "id@id")
    private List<Tag> tagList;

    @Sql("select * from t_person")
    private List<Person> allPerson;

    @Sql("select * from t_person where id = 552098712424472576")
    private Person p1;

    @Sql(value = "select * from t_person where id = :id", params = "id@person.id")
    private Person p2;

    @Sql(value = "select * from t_person where id = :id AND name like :title", params = "id@person.id, title@title")
    private Person p3;

    @Sql(value = "select * from t_person where id = :id", params = "id@person.id")
    Map<String, Object> personMap;
}

测试,借助对象 EntityDAOSupport 进行查询

    @Autowired
    private EntityDAOSupport entityDAOSupport;

    @Test
    public void testBySql() {
        List<BookQuery> query = entityDAOSupport.query("select * from t_book where id = 617342584087388160", null, BookQuery.class);
        System.out.println(query);

        Optional<BookQuery> optional = entityDAOSupport.queryForObject("select * from t_book where id = 617342584087388160", null, BookQuery.class);
        System.out.println(optional.get());
    }

    @Test
    public void testByInstance() {
        BookQuery bookQuery = new BookQuery();
        bookQuery.setId(617342584087388160L);
        bookQuery.setPersonId("552100575806939136");

        entityDAOSupport.query(bookQuery);
        System.out.println(bookQuery);
    }


sharp-database 添加EntityDAOSupport支持,省略 DAO 模版代码

很多时候DAO继承父类都可以满足操作需求, 所以大部分情况下继承 EntityDAOImpl 变成一个样板代码, sharp-database 做了增强,可以不显示地继承 EntityDAOImpl, 可以通过扫描实体类自动完成. 如果自己继承父类,那么就会忽略扫描.

// 这类可以有扫描实体类自动完成
@Repository
public class BomDAO extends EntityDAOImpl<Bom, Long> {
    // 空
}

实例

添加对 entity 自动扫描的支持

启动类添加注解 @DependsOn("entityDAOSupport")

@SpringBootApplication
@EnableCaching
@DependsOn("entityDAOSupport")
public class SharpAdminApplication {

    public static void main(String[] args) {
        SpringApplication.run(SharpAdminApplication.class, args);
    }

}

实体类

编写实体类 Bom.java Item.java
Bom 1 <=> N Item

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@FieldDefaults(level = AccessLevel.PRIVATE)
@SuperBuilder
@Table(value = "produce_bom", comment = "物料BOM表")
public class Bom extends BaseEntity {

    @Column(comment = "物料")
    Long materialId;

    @OneToMany(subTable = "produce_bom_detail", reversePropertyName = "bomId", cascadeInsertOrUpdate = true, joinValue = "bom_id")
    List<Item> itemList;

    @Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @FieldDefaults(level = AccessLevel.PRIVATE)
    @SuperBuilder
    @Table(value = "produce_bom_detail", comment = "物料BOM表详情")
    public static class Item extends BaseEntity {

        @Column(comment = "物料")
        Long materialId;

        BigDecimal quantity;

        String unit;

        String remark;

        Long bomId;
    }

}

实体类生成Table

    tableGenerator.createTable(Bom.class);
    tableGenerator.createTable(Bom.Item.class);

配置实体扫描

application.yml

sharp:
  database:
    entity-base-package: com.rick.admin.module.**.entity

测试

1. EntityDAOSupport

    @Autowired
    private EntityDAOSupport entityDAOSupport

    @Test
    public void testInsertBom() {
        Bom bom = Bom.builder()
                .materialId(697175396411510784L)
                .itemList(Arrays.asList(
                        Bom.Item.builder()
                                .materialId(697175443165417472L)
                                .quantity(BigDecimal.TEN)
                                .unit("KG")
                                .remark("remark")
                                .build()
                ))
                .build();

        // 不参与包扫描也可以使用
        entityDAOSupport.getEntityDAO(bom.getClass()).insert(bom);
    }

2. 包扫描自动注入

bomDAO 是DAO bean的名字. 规则是「实体类名驼峰+DAO」,如果是内部类「外部类+内部实体类名驼峰+DAO」,比如例子中的 bomItemDAO

    /**
     * entity 参与包扫描,可以注入使用
     */
    @Autowired(required = false)
    @Qualifier("bomDAO")
    private EntityDAO<Bom, Long> entityDAO;

    @Test
    public void testInsertBom() {
        Bom bom = Bom.builder()
                .materialId(697175396411510784L)
                .itemList(Arrays.asList(
                        Bom.Item.builder()
                                .materialId(697175443165417472L)
                                .quantity(BigDecimal.TEN)
                                .unit("KG")
                                .remark("remark")
                                .build()
                ))
                .build();

        entityDAO.insert(bom);
    }

3. 手动获取DAO bean

    @Resource
    private ApplicationContext applicationContext;

    @Test
    public void testInsertBom() {
        Bom bom = Bom.builder()
                .materialId(697175396411510784L)
                .itemList(Arrays.asList(
                        Bom.Item.builder()
                                .materialId(697175443165417472L)
                                .quantity(BigDecimal.TEN)
                                .unit("KG")
                                .remark("remark")
                                .build()
                ))
                .build();

        applicationContext.getBean("bomDAO", EntityDAO.class)
                .insert(bom);
    }

04.属性名和bean名保持一致,spring按照名称自动注入(推荐)

    // @Autowired
    @Resource
    private EntityDAO<Bom, Long> bomDAO;

    @Test
    public void testInsertBom() {
        Bom bom = Bom.builder()
                .materialId(697175396411510784L)
                .itemList(Arrays.asList(
                        Bom.Item.builder()
                                .materialId(697175443165417472L)
                                .quantity(BigDecimal.TEN)
                                .unit("KG")
                                .remark("remark")
                                .build()
                ))
                .build();

        bomDAO.insert(bom);
    }

注意事项

如果没有手动编写 DA0 类,那么根据 BeanName 进行注册, 写法是固定的.

@Resource
private EntityDAO<Entity, Long> entityDAO;

如果手动编写 DA0 类,那么根据 BeanTypeBeanName 进行注册, 可以使用两种方式进行注册. 都是同一引用. 比如:

    @Resource
    private EntityDAO<Dog, Long> dogDAO2; // 指定了范型, 根据类型注册名字随便起 

    // @Resource
    // private EntityDAO dogDAO3; // 如果写成这样, 启动会抛出异常找不到合适的Bean

    // @Resource
    // private EntityDAO dogDAO; // 如果写成这样没有使用范型, 根据Bean的名称注册, 使用的时候需要强转, 不推荐使用此方式. 

    @Resource
    private DogDAO dogDAO;

    @Test
    public void test() {
        Assertions.assertThat(dogDAO2 == dogDAO).isEqualTo(true);
    }

监听端口,未监控到则后台启动

schedule.bat

@echo off

netstat -aon|findstr "7000" >nul 2>nul && set state=1 || set state=0
if %state%==1 (echo "Running...")
if %state%==0 (
D:
cd D:\AC\nginx-1.20.2
nginx -s stop
start nginx
cd ..
start jdk1.8.0_251\bin\javaw -jar ac-0.0.1-SNAPSHOT.jar --spring.profiles.active=prod,dict
)

exit

schedule.bat 可以加入计划任务

sharp-database 通过SQL多表查询, 并组装返回Bean

业务Bean SchoolBO.java

@Data
public class SchoolBO {

    private Long id;

    private String name;

    private LocalDate buildDate;

    private SchoolLicenseBO sl;

    private List<StudentBO> studentList;

    private List<TeacherBO> teacherList;

    /**
     * 1 <-> 1
     */
    @Data
    public static class SchoolLicenseBO {

        private Long id;

        private String number;

        private String remark;
    }

    /**
     * 1 <-> N
     */
    @Data
    public static class StudentBO {

        private Long id;

        private String name;

        private Integer grade;

        private String sex;

        private Long schoolId;
    }

    /**
     * N <-> N
     */
    @Data
    public static class TeacherBO {

        private Long id;

        private String name;

        private Integer age;

        private Long schoolId;
    }
}

查询测试:

@Test
public void testQueryUtils() {
    String parentSql = "select s.id,\n" +
            "       s.name,\n" +
            "       s.build_date as \"buildDate\",\n" +
            "       sl.id as \"sl.id\",\n" +
            "       sl.number as \"sl.number\",\n" +
            "       sl.remark as \"sl.remark\"\n" +
            "from t_school s\n" +
            "         LEFT JOIN t_school_license sl on sl.id = s.school_license_id\n" +
            "where s.id IN (:id)";

    List<SchoolBO> schoolBOList = sharpService.query(parentSql, Params.builder(1).pv("id", "552173736070144000, 552181593272410112").build(), SchoolBO.class);

    if (CollectionUtils.isEmpty(schoolBOList)) {
        return;
    }

    Collection<?> refValues = schoolBOList.stream().map(SchoolBO::getId).collect(Collectors.toSet());

    // 1 <-> N
    // select id, name, grade, sex from t_school_student where school_id IN (552173736070144000, 552181593272410112);
    Map<?, List<SchoolBO.StudentBO>> schoolStudentMap = QueryUtils.subTableValueMap("id, name, grade, sex, school_id", "t_school_student", "school_id", refValues, SchoolBO.StudentBO.class);

    // N <-> N
    // select t.id, t.name, t.age, school_id from t_school_teacher_related r INNER JOIN t_school_teacher t ON r.teacher_id = t.id where school_id IN (552173736070144000, 552181593272410112)
    Map<?, List<SchoolBO.TeacherBO>> schoolTeacherMap = QueryUtils.subTableValueMap("t.id, t.name, t.age, school_id", "t_school_teacher_related r INNER JOIN t_school_teacher t ON r.teacher_id = t.id", "school_id", refValues, SchoolBO.TeacherBO.class);

    schoolBOList.forEach(t -> {
        t.setStudentList(ObjectUtils.defaultIfNull(schoolStudentMap.get(t.getId()), Collections.emptyList()));
        t.setTeacherList(ObjectUtils.defaultIfNull(schoolTeacherMap.get(t.getId()), Collections.emptyList()));
    });

    System.out.println(schoolBOList);
}

参考阅读: https://xhope.top/?p=1577