Springboot+mybatis+poi Output Report

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

Spring:
  DataSource:
    url:jdbc:mysql://localhost:3306/test?useunicode=true&characterencoding=utf-8 &usessl=false
    username:root
    password:root
    driverClassName:com.mysql.jdbc.Driver

server:
  port:8082

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