The Java implementation imports the SQL result set of the query into Excel, with the Jxl.jar package available for download in http://download.csdn.net/detail/qq8618/8304057
public string Queryresulttoexcel (string sql,string filename,outputstream os) {Connection conn = null; Statement sm = null; ResultSet rs = null;try {conn = getconnection (); sm = Conn.createstatement (); rs = sm.executequery (SQL); ResultSetMetaData RSMD = Rs.getmetadata (); Writableworkbook WWB = workbook.createworkbook (OS); Build Excel file Writablesheet sheet = wwb.createsheet (filename, 10); Create a worksheet//set the text format of the cell writablefont wf = new Writablefont (Writablefont.arial, Writablefont.bold, False, Underlinestyle.no_underline, Colour.black); Writablecellformat WCF = new Writablecellformat (WF); wcf.setverticalalignment (verticalalignment.centre); Wcf.setalignment (alignment.left);//Format Data--number type NumberFormat NumberFormat = new NumberFormat ("# # #0.0#######"); Writablecellformat cellformatnumber = new Writablecellformat (NumberFormat); Cellformatnumber.setverticalalignment (Verticalalignment.centre); Cellformatnumber.setalignment (Alignment.RIGHT) ; NumberFormat numberFormat2 = new NumberFormat ("# # #0"); WritabLecellformat cellFormatNumber2 = new Writablecellformat (NUMBERFORMAT2); Cellformatnumber2.setverticalalignment (Verticalalignment.centre); Cellformatnumber2.setalignment ( Alignment.right);//Format Data--date type DateFormat dateformat=new dateformat ("Yyyy-mm-dd"); Writablecellformat cellformatdate = new Writablecellformat (DateFormat); Cellformatdate.setverticalalignment (Verticalalignment.centre); cellformatdate.setalignment (Alignment.CENTRE);// Formatted data--text Writablecellformat Celltextformat = new Writablecellformat (numberformats.text); Celltextformat.setalignment (Alignment.centre); Celltextformat.setverticalalignment (Verticalalignment.centre); Table Body Data Boolean flag = True;int r = 1;int c = 0;int columns = Rsmd.getcolumncount ();//cache Maximum caption Width vector<integer> Colwidt h = new vector<integer> (); for (int i = 1; I <= columns; i++) {colwidth.add (0);} while (Rs.next ()) {for (int i = 1; I <= columns; i++) {//Add header data if (flag) {String key = Rsmd.getcolumnname (i).toLowerCase (); Sheet.setcolumnview (c, Key.getbytes ("GBK"). length + 4); Sheet.addcell (New Label (c, 0, KEY,WCF)); Colwidth.set (I-1, Key.getbytes ("GBK"). length); Cache the width of the first row of data in each column}//set the column width-if the next column has more data than the previous column, save the maximum width if (rs.getstring (i) = null) {if (Colwidth.get (i-1) <rs.getstring (i). Length ()) {Colwidth.set (i-1, rs.getstring (i). Length ());} Sheet.setcolumnview (c, Colwidth.get (i-1) + 4); Set the width}//determine the data type if (Rsmd.getcolumntypename (i). Equalsignorecase ("number")) {if (rs.getstring (i)!=null) {if ( Rs.getstring (i). IndexOf (".") ==-1) {Sheet.addcell (new number (C, R, Rs.getdouble (i), cellFormatNumber2));} Else{sheet.addcell (new number (C, R, Rs.getdouble (i), cellformatnumber));}} Else{sheet.addcell (new number (C, R, 0,cellformatnumber2));}} else if (rs.getstring (i)!=null && rsmd.getcolumntypename (i). Equalsignorecase ("DATE")) {Sheet.addcell (new DateTime (c, R,rs.getdate (i), cellformatdate));} else {Sheet.addcell (new Label (C, R, Rs.getstring (i), Celltextformat));} Number of columns C + +;} Flag = False;r++;c = 0;} Wwb.write (); Wwb.close (); return r+ "_" +C;} catch (SQLException e) {e.printstacktrace (); return E.getlocalizedmessage ();} catch (FileNotFoundException e) {//TODO Auto-generated catch Blocke.printstacktrace (); return E.getlocalizedmessage ();} catch (IOException e) {//TODO auto-generated catch Blocke.printstacktrace (); return E.getlocalizedmessage ();} catch ( WriteException e) {//TODO auto-generated catch Blocke.printstacktrace (); return E.getlocalizedmessage ();} finally {try { Rs.close (); Sm.close (); Conn.close ();} catch (SQLException e) {}}}
Java imports the execution SQL results into Excel