Springboot+mybatis+poi输出报表

在Springboot项目中，结合mybatis访问数据库，使用poi将需要的数据整理成报表数据输出。

一、引入pom文件依赖

<parent>        <groupId>org.springframework.boot</groupId>        <artifactId>spring-boot-starter-parent</artifactId>        <version>1.5.2.RELEASE</version>    </parent>    <dependencies>        <!-- SpringBoot 核心组件 -->        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-web</artifactId>        </dependency>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-tomcat</artifactId>        </dependency>        <dependency>            <groupId>org.mybatis.spring.boot</groupId>            <artifactId>mybatis-spring-boot-starter</artifactId>            <version>1.1.1</version>        </dependency>        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>        </dependency>        <!--生成Excel-->        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi</artifactId>            <version>3.15</version>        </dependency>        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>3.15</version>        </dependency>    </dependencies>    <build>        <plugins>            <plugin>                <groupId>org.codehaus.mojo</groupId>                <artifactId>exec-maven-plugin</artifactId>                <version>1.5.0</version>                <executions>                    <execution>                        <goals>                            <goal>java</goal>                        </goals>                    </execution>                </executions>                <configuration>                    <mainClass>App</mainClass> <!--启动类的位置-->                </configuration>            </plugin>        </plugins>    </build>

二、在resources下编写application.yml

spring:  datasource:    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false    username : root    password : root    driverClassName : com.mysql.jdbc.Driverserver:  port: 8082

三、编写Mapper层,使用注解开发

public interface UserMapper{    @Select("select * from user")    public List<User> getAll();}

User实体类

public class User {    private Integer id;    private String name;    private Integer age;    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public Integer getAge() {        return age;    }    public void setAge(Integer age) {        this.age = age;    }}

四、编写controller类

@Controllerpublic class ExcelController {    @Autowired    private UserMapper userMapper;    @RequestMapping("/getExcel")    public void getExcel (HttpServletResponse response) throws Exception {        List<User> userList = userMapper.getAll();        HSSFWorkbook wb = new HSSFWorkbook();        HSSFSheet sheet =wb.createSheet("获取excel测试表格");        HSSFRow row = null;        row = sheet.createRow(0);        row.setHeight((short)(26.25*20));        row.createCell(0).setCellValue("用户信息列表");        row.getCell(0).setCellStyle(getStyle(wb,0));//设置样式        for(int i = 1;i <= 3;i++){            row.createCell(i).setCellStyle(getStyle(wb,0));        }        CellRangeAddress rowRegion = new CellRangeAddress(0,0,0,3);        sheet.addMergedRegion(rowRegion);        CellRangeAddress columnRegion = new CellRangeAddress(1,4,0,0);        sheet.addMergedRegion(columnRegion);        row = sheet.createRow(1);        row.createCell(0).setCellStyle(getStyle(wb,3));        row.setHeight((short)(22.50*20));        row.createCell(1).setCellValue("用户Id");        row.createCell(2).setCellValue("用户名");        row.createCell(3).setCellValue("用户密码");        for(int i = 1;i <= 3;i++){            row.getCell(i).setCellStyle(getStyle(wb,1));        }        for(int i = 0;i<userList.size();i++){            row = sheet.createRow(i+2);            User user = userList.get(i);            row.createCell(1).setCellValue(user.getId());            row.createCell(2).setCellValue(user.getName());            row.createCell(3).setCellValue(user.getAge());            for(int j = 1;j <= 3;j++){                row.getCell(j).setCellStyle(getStyle(wb,2));            }        }        //默认行高        sheet.setDefaultRowHeight((short)(16.5*20));        //列宽自适应        for(int i=0;i<=13;i++){            sheet.autoSizeColumn(i);        }        response.setContentType("application/vnd.ms-excel;charset=utf-8");        OutputStream os = response.getOutputStream();        wb.write(os);        os.flush();        os.close();    }    /**     * 获取样式     * @param hssfWorkbook     * @param styleNum     * @return     */    public HSSFCellStyle getStyle(HSSFWorkbook hssfWorkbook, Integer styleNum){        HSSFCellStyle style = hssfWorkbook.createCellStyle();        style.setBorderRight(BorderStyle.THIN);//右边框        style.setBorderBottom(BorderStyle.THIN);//下边框        HSSFFont font = hssfWorkbook.createFont();        font.setFontName("微软雅黑");//设置字体为微软雅黑        HSSFPalette palette = hssfWorkbook.getCustomPalette();//拿到palette颜色板,可以根据需要设置颜色        switch (styleNum){            case(0):{                style.setAlignment(HorizontalAlignment.CENTER_SELECTION);//跨列居中                font.setBold(true);//粗体                font.setFontHeightInPoints((short) 14);//字体大小                style.setFont(font);                palette.setColorAtIndex(HSSFColor.BLUE.index,(byte)184,(byte)204,(byte)228);//替换颜色板中的颜色                style.setFillForegroundColor(HSSFColor.BLUE.index);                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);            }            break;            case(1):{                font.setBold(true);//粗体                font.setFontHeightInPoints((short) 11);//字体大小                style.setFont(font);            }            break;            case(2):{                font.setFontHeightInPoints((short)10);                style.setFont(font);            }            break;            case(3):{                style.setFont(font);                palette.setColorAtIndex(HSSFColor.GREEN.index,(byte)0,(byte)32,(byte)96);//替换颜色板中的颜色                style.setFillForegroundColor(HSSFColor.GREEN.index);                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);            }            break;        }        return style;    }}

五、启动类

@ComponentScan(basePackages = {"cn.iponkan.controller"})@MapperScan(basePackages = {"cn.iponkan.mapper"})@EnableAutoConfigurationpublic class App{    public static void main(String[] args) {        SpringApplication.run(App.class,args);    }}

六、项目运行结果

项目完整Demo：http://download.csdn.net/download/qq_36135928/10173276 点击打开链接

GitHub：https://github.com/tangqiangDong/Springboot-poi