Springboot+mybatis+poi Output Report

Source: Internet
Author: User

In the Springboot project, combine mybatis to access the database and use POI to organize the required data into report data output.

First, the introduction of pom file dependency

<parent> <groupId>org.springframework.boot</groupId> <artifactid>spring-boot-start er-parent</artifactid> <version>1.5.2.RELEASE</version> </parent> <dependencies > <!--springboot core components--<dependency> <groupid>org.springframework.boot
        </groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactid>s pring-boot-starter-tomcat</artifactid> </dependency> <dependency> <groupi
            D>org.mybatis.spring.boot</groupid> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.1.1</version> </dependency> <dependency> <gr Oupid>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!--generation excel--> <dependency> <groupId>org.apache.poi</groupId> <artifacti d>poi</artifactid> <version>3.15</version> </dependency> <depend Ency> <groupId>org.apache.poi</groupId> <artifactid>poi-ooxml</artifactid&
            Gt <version>3.15</version> </dependency> </dependencies> <build> &LT;PL Ugins> <plugin> <groupId>org.codehaus.mojo</groupId> < Artifactid>exec-maven-plugin</artifactid> <version>1.5.0</version> &L T;executions> <execution> <goals> & lt;goal>java</goal> </goals> </execution> </ex Ecutions> <configuration> <mainClass>App</mainClass> <!--the bit of the startup class </configuration> </plugin> </plugins> </build>

Ii. preparation of application.yml under the resources

    url:jdbc:mysql://localhost:3306/test?useunicode=true&characterencoding=utf-8 &usessl=false


Third, write mapper layer, use annotation development

public interface usermapper{
    @Select (' select * from user ') public
    list<user> getAll ();

User entity class

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;

Iv. Preparation of controller classes

@Controller public class Excelcontroller {@Autowired private usermapper usermapper; @RequestMapping ("/getexcel") public void Getexcel (HttpServletResponse response) throws Exception {List<us

        Er> userlist = Usermapper.getall ();
        Hssfworkbook wb = new Hssfworkbook ();
        Hssfsheet sheet =wb.createsheet ("Get Excel Test Form");

        Hssfrow row = null;
        row = Sheet.createrow (0);
        Row.setheight ((short) (26.25*20));
        Row.createcell (0). Setcellvalue ("User Information list"); Row.getcell (0). Setcellstyle (GetStyle (wb,0));//Set style for (int i = 1;i <= 3;i++) {Row.createcell (i). Set
        CellStyle (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 ("User ID");
        Row.createcell (2). Setcellvalue ("user name");
        Row.createcell (3). Setcellvalue ("User password");
        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));
        }}//Default line High Sheet.setdefaultrowheight ((short) (16.5*20));
        Column width adaptive 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 (); }/** * Get style * @param hssfworkbook * @param stylenum * @return */public Hssfcellstyle G
        Etstyle (Hssfworkbook hssfworkbook, Integer stylenum) {Hssfcellstyle style = Hssfworkbook.createcellstyle (); Style.setborderright (Borderstyle.thin)///Right Border Style.setborderbottom (Borderstyle.thin);//Bottom frame Hssffont fo
        NT = Hssfworkbook.createfont (); Font.setfontname ("Microsoft Jas Black");//Set the font for Microsoft Jas Black Hssfpalette palette = Hssfworkbook.getcustompalette ();//Get Palette Color board, can be set according to Color switch (stylenum) {case (0): {style.setalignment (horizontalalignment.center_selecti
                on);//cross-column Center Font.setbold (TRUE);//Bold Font.setfontheightinpoints ((short) 14);//font size
                Style.setfont (font);
      Palette.setcoloratindex (HSSFColor.BLUE.index, (byte) 184, (byte) 204, (byte) 228);//Replace color in color palette          Style.setfillforegroundcolor (HSSFColor.BLUE.index);
            Style.setfillpattern (Fillpatterntype.solid_foreground);
            } break;
                Case (1): {Font.setbold (TRUE);//Bold Font.setfontheightinpoints ((short) 11);//font size
            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), (Byte) 96);//Replace color in color palette Style.setfillforegrou
                Ndcolor (HSSFColor.GREEN.index);
            Style.setfillpattern (Fillpatterntype.solid_foreground);
        } break;
    } return style;

V. Start-up class

@ComponentScan (basepackages = {"Cn.iponkan.controller"})
@MapperScan (basepackages = {"Cn.iponkan.mapper"})
@EnableAutoConfiguration Public
class app{public
    static void Main (string[] args) {
        Springapplication.run (App.class,args);

Vi. Results of Project operation

Project complete demo:http://download.csdn.net/download/qq_36135928/10173276 Click to open link github:https://github.com/tangqiangdong/ Springboot-poi

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.