05_excel Operation _02_ User list export for simulated web environment

"Explanation of ideas"

Before formally going to webproject, prepare to simulate the webproject background export process.

Mainly written in the Excelservice layer, in the Excel service layer, first to get userlist, that is, the database query user list of users, this userlist from the Userdao getuserlist () method obtained, The Getuserlist () method actually does not query the database, but creates some data on its own.

After Excelservice obtains the userlist, it then creates excel in a certain format, writing data in Excel.

Engineering

"User.java"

 Package com. Higgin.Excel.domain;  Public class User {    privateint   ID;      Private String name;     Private String sex;     Private String phone;     Private String Email;
... Omit get and Set methods ...}

"Userdao.java"//simulate getting data from the database

 Packagecom. Higgin.Excel.dao;Importjava.util.ArrayList;Importjava.util.List;Importorg.junit.Test;Importcom. Higgin.Excel.domain.User; Public classUserdao {/*** Simulate the user list obtained from the database *@return     */     PublicList<user>getuserlist () {List<User>userlist=NewArraylist<user>();  for(inti=1;i<=99;i++) {User user =NewUser ();            user. setId (i); user. SetName ("Higgin" +i); user. setsex (i%3==0? " Male ":" Female "); user. Setphone ("000000000" + (i*i)); user. Setemail ("Myemail" +i+ "@qq. com");        userlist. Add (user); }        return userlist; }    }

"Excelservice.java"

 Packagecom. Higgin.Excel.service;ImportJava.io.FileOutputStream;Importjava.io.IOException;Importjava.util.List;ImportOrg.apache.poi.hssf.usermodel.HSSFCell;ImportOrg.apache.poi.hssf.usermodel.HSSFCellStyle;ImportOrg.apache.poi.hssf.usermodel.HSSFFont;ImportOrg.apache.poi.hssf.usermodel.HSSFRow;ImportOrg.apache.poi.hssf.usermodel.HSSFSheet;ImportOrg.apache.poi.hssf.usermodel.HSSFWorkbook;Importorg.apache.poi.ss.util.CellRangeAddress;Importorg.junit.Test;Importcom. Higgin.Excel.dao.UserDao;Importcom. Higgin.Excel.domain.User; Public classExcelservice {PrivateUserdaoUserdao=NewUserdao (); //simulate a query from the database to get the corresponding user list    PrivateList<user>userlist= Userdao. getuserlist (); @Test Public voidOutputexcel ()throwsioexception{//1. Create a workbookHssfworkbookWorkbook=NewHssfworkbook (); //1.1 Creating merged cell Objects Cellrangeaddresscellrangeaddress=NewCellrangeaddress (0,0,0,4); //1.2 Create a header title and set its font HssfcellstyleStyle1=Createcellstyle(Workbook,( Short) 16); You write the method of setting the cell style see the last//1.3 Creating a heading style HssfcellstyleStyle2=Createcellstyle(Workbook,( Short) 13); //2. Create a worksheetHssfsheetsheet=Workbook. Createsheet ("User list"); //2.1 Loading merged cell objects sheet. addmergedregion (cellrangeaddress); //3. Create a row//3.1 Create Header header row and write header headerHssfrowRow1=sheet. CreateRow (0); Hssfcell cell1 =Row1. Createcell (0);        cell1. Setcellstyle (Style1); cell1. Setcellvalue ("User List"); //3.2 Create column headings and write to column headingsHssfrowRow2=sheet. CreateRow (1); String[] titles ={"id", "name", "Gender", "Phone", "Mailbox"};  for(inti=0;i<titles.length;i++) {Hssfcell cell2 = row2. Createcell (i);            cell2. Setcellstyle (Style2);        cell2. Setcellvalue (Titles[i]); }                //4. Create a cell, write user data to Excel        if(userlist!=NULL&&userlist. Size () >0){             for(intj=0;j<userlist. Size (); j + +) {hssfrow row =sheet. CreateRow (j+2);//since the first two lines are header and column headings respectively, start at line 3rd                Row. Createcell (0). Setcellvalue (string.valueof (userlist. Get (J). GetId ())); row. Createcell (1). Setcellvalue (userlist. Get (J). GetName ()); row. Createcell (2). Setcellvalue (userlist. Get (J). Getsex ()); row. Createcell (3). Setcellvalue (userlist. Get (J). Getphone ()); row. Createcell (4). Setcellvalue (userlist. Get (J). Getemail ()); }        }                //5. OutputStringFileName= "d:\\ user list. xls"; FileOutputStream FileOutputStream =NewFileOutputStream (fileName);        Workbook. Write (fileoutputstream);        workbook. Close ();    FileOutputStream. Close (); }        /*** Create a cell style *@paramWorkbook Workbook *@paramfontSize Font Size *@returncell style*/    Private StaticHssfcellstyleCreatecellstyle(HssfworkbookWorkbook, Short fontSize) {Hssfcellstyle style = Workbook. Createcellstyle (); //Create a Cell object  style. setalignment (Hssfcellstyle.align_center);         //Center Horizontally        style. Setverticalalignment (Hssfcellstyle.vertical_center);//Center Vertically//Creating FontsHssffontFont= Workbook. CreateFont (); font. Setboldweight (Hssffont.  Boldweight_bold); //Bold Font        Font. setfontheightinpoints (fontSize);//Set Font size//to load a font in a style style. SetFont (font); return style; }}

"Run Results"

05_excel Operation _02_ User list export for simulated web environment