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> <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