JAVA imports SQL Execution results into excel

JAVA imports SQL Execution results into excel

Java imports the queried SQL result set to excel. The jxl. jar package can be found at www.bkjia.com.

 

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.exe cuteQuery (SQL); ResultSetMetaData rsmd = rs. getMetaData (); WritableWorkbook wwb = Workbook. createWorkbook (OS); // create an excel file WritableSheet sheet = wwb. createSheet (filename, 10); // create a worksheet // set the text format of the cell WritableFont wf = new WritableFont (WritableFont. ARIAL, 10, WritableFont. BOLD, false, UnderlineStyle. NO_UNDERLINE, Colour. BLACK); WritableCellFormat wcf = new WritableCellFormat (wf); wcf. setVerticalAlignment (verticalignment. CENTRE); wcf. setAlignment (Alignment. LEFT); // format the data -- NUMBER type NumberFormat numberFormat = new NumberFormat (### 0.0 #######); WritableCellFormat cellFormatNumber = new WritableCellFormat (numberFormat); cellFormatNumber. setVerticalAlignment (verticalignment. CENTRE); cellFormatNumber. setAlignment (Alignment. RIGHT); NumberFormat numberFormat2 = new NumberFormat (### 0); WritableCellFormat cellFormatNumber2 = new WritableCellFormat (numberFormat2); cellformatnumber2.setverticalignment (verticalignment. CENTRE); cellFormatNumber2.setAlignment (Alignment. RIGHT); // format data -- DATE type DateFormat dateFormat = new DateFormat (yyyy-MM-dd); WritableCellFormat cellFormatDate = new WritableCellFormat (dateFormat); cellFormatDate. setVerticalAlignment (verticalignment. CENTRE); cellFormatDate. setAlignment (Alignment. CENTRE); // format the data -- text WritableCellFormat cellTextFormat = new WritableCellFormat (NumberFormats. TEXT); cellTextFormat. setAlignment (Alignment. CENTRE); cellTextFormat. setVerticalAlignment (verticalignment. CENTRE); // table body data boolean flag = true; int r = 1; int c = 0; int columns = rsmd. getColumnCount (); // Maximum Cache title width Vector
 
  
ColWidth = new Vector
  
   
(); 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 (the I-1, key. getBytes (GBK ). length); // cache the width of the data in the first row of each column} // set the column width -- if the data in the next column is wider than that in the previous column, the maximum width if (rs) is saved. getString (I )! = Null) {if (colWidth. get (I-1)