Java reads the database and writes the Excel document

// Download the poi3.1jar package from Apache

Package Excel;
Import org. Apache. Poi. hssf. util .*;
Import java. SQL .*;
Import java. Io. filenotfoundexception;
Import java. Io. fileoutputstream;
Import org. Apache. Poi. hssf. usermodel .*;
// Insert the jar package for image import
Import java. AWT. image. bufferedimage;
Import java. Io. bytearrayoutputstream;
Import java.net. url;
Import javax. ImageIO. ImageIO;

Public class writeexcel {
Private connection con = NULL;

Public connection getconnection (){
String clssforname = "com. Microsoft. JDBC. sqlserver. sqlserverdriver ";
String url = "JDBC: Microsoft: sqlserver: // localhost: 1433; databasename = pubs ";
If (con = NULL ){

Try {
Class. forname (clssforname );
Con = drivermanager. getconnection (URL, "sa ","");

} Catch (classnotfoundexception e ){
E. printstacktrace ();
} Catch (sqlexception cone ){
System. Out. println (cone );
}
}

Return con;
}
 
Public static void main (string [] ARGs) throws exception {
Connection conn = NULL;
Statement ST = NULL;
Resultset rs = NULL;
Int I = 0;
String SQL = "select * from authors ";
Writeexcel wR = new writeexcel ();

Conn = Wr. getconnection ();
St = conn. createstatement ();
Rs1_st.exe cutequery (SQL );

// Declare a workbook
Hssfworkbook WB = new hssfworkbook ();
// Generate a table
Hssfsheet sheet = WB. createsheet ("table 1 ");
// Generate a column
// Hssfrow ROW = sheet. createrow (0 );
// Generate a style
Hssfcellstyle style = WB. createcellstyle ();
// Set these styles
Style. setfillforegroundcolor (hssfcolor. sky_blue.index );
Style. setfillpattern (hssfcellstyle. solid_foreground );
Style. setborderbottom (hssfcellstyle. border_thin );
Style. setborderleft (hssfcellstyle. border_thin );
Style. setborderright (hssfcellstyle. border_thin );
Style. setbordertop (hssfcellstyle. border_thin );
Style. setalignment (hssfcellstyle. align_center );
// Generate a font
Hssffont font = WB. createfont ();
Font. setcolor (hssfcolor. Violet. Index );
Font. setfontheightinpoints (short) 10 );
Font. setboldweight (hssffont. boldweight_bold );
// Apply the font to the current style
Style. setfont (font );
// Fill the cell
While (Rs. Next ()){
Hssfrow ROW = sheet. createrow (I );
// Declare a cell
Hssfcell cell = row. createcell (short) 0 );
// Set the character value of a cell
Cell. setcellvalue (New hssfrichtextstring (Rs. getstring ("au_id"). tostring ()));
// Set the cell style
Cell. setcellstyle (style );
// Set the second column Value
Hssfcell au_lname = row. createcell (short) 1 );
Au_lname.setcellvalue (New hssfrichtextstring (Rs. getstring ("au_lname ")));
// Set the value of the third column
Hssfcell au_fname = row. createcell (short) 2 );
Au_fname.setcellvalue (New hssfrichtextstring (Rs. getstring ("au_fname ")));
Hssfcell phone = row. createcell (short) 3 );
Phone. setcellvalue (New hssfrichtextstring (Rs. getstring ("phone ")));
Hssfcell address = row. createcell (short) 4 );
Address. setcellvalue (New hssfrichtextstring (Rs. getstring ("Address ")));
I ++;
}
Fileoutputstream fout = new fileoutputstream ("D: // my first excel.xls ");
// Output to file
WB. Write (fout );
Fout. Close ();
}
/**
* Insert an image
* @ Throws exception
*
*/
Public void insertimg () throws exception {

// Declare a workbook
Hssfworkbook WB = new hssfworkbook ();
// Generate a table
Hssfsheet sheet = WB. createsheet ("table 1 ");
// Generate a column
Hssfrow ROW = sheet. createrow (0 );
// Generate a style
Hssfcellstyle style = WB. createcellstyle ();
// Set these styles
Style. setfillforegroundcolor (hssfcolor. sky_blue.index );
Style. setfillpattern (hssfcellstyle. solid_foreground );
Style. setborderbottom (hssfcellstyle. border_thin );
Style. setborderleft (hssfcellstyle. border_thin );
Style. setborderright (hssfcellstyle. border_thin );
Style. setbordertop (hssfcellstyle. border_thin );
Style. setalignment (hssfcellstyle. align_center );
// Generate a font
Hssffont font = WB. createfont ();
Font. setcolor (hssfcolor. Violet. Index );
Font. setfontheightinpoints (short) 16 );
Font. setboldweight (hssffont. boldweight_bold );
// Apply the font to the current style
Style. setfont (font );
// Declare a top-level manager for drawing
Hssfpatriarch patriarch = sheet. createdrawingpatriarch ();
// Fill the cell
For (short I = 0; I <5; I ++ ){
// Declare a cell
Hssfcell cell = row. createcell (I );
Switch (I ){
Case 0:
// Set plain text
Cell. setcellvalue (New hssfrichtextstring ("plain text "));
Break;
Case 1:
// Set to shape
Hssfclientanchor a1 = new hssfclientanchor (0, 0, 1023,255, (short) 1, 0, (short) 1, 0 );
Hssfsimpleshape shape1 = patriarch. createsimpleshape (A1 );
// You can set the shape style here
Shape1.setshapetype (hssfsimpleshape. object_type_oval );

Break;
Case 2:
// Set it to a Boolean Value
Cell. setcellvalue (true );
Break;
Case 3:
// Set it to double.
Cell. setcellvalue (12.5 );
Break;
Case 4:
// Set to image]
// URL url = test3.class. getresource ("hello.jpg ");
// Insertimage (WB, patriarch, getimagedata (ImageIO. Read (URL), 0, 4, 1 );
Break;

}

// Set the cell style
Cell. setcellstyle (style );
}
Fileoutputstream fout = new fileoutputstream ("My first excel.xls ");
// Output to file
WB. Write (fout );
Fout. Close ();
}
// Custom method: insert an image to the specified index location
Private Static void insertimage (hssfworkbook WB, hssfpatriarch Pa, byte [] data, int row, int column, int index ){
Int X1 = Index * 250;
Int Y1 = 0;
Int X2 = X1 + 255;
Int y2 = 255;
Hssfclientanchor anchor = new hssfclientanchor (x1, Y1, X2, Y2, (short) column, row, (short) column, row );
Anchor. setanchortype (2 );
Pa. createpicture (anchor, WB. addpicture (data, hssfworkbook. picture_type_jpeg ));
}
// Obtain the byte array from the image
Private Static byte [] getimagedata (bufferedimage Bi ){
Try {
Bytearrayoutputstream bout = new bytearrayoutputstream ();
ImageIO. Write (Bi, "PNG", Bout );
Return bout. tobytearray ();
} Catch (exception exe ){
EXE. printstacktrace ();
Return NULL;
}

}

}