Java imports the execution SQL results into Excel

Source: Internet
Author: User
Tags dateformat

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.