java test file to generate excel

dbexcel.java (text/plain), 6.35 KB, created by vijay_alluri on 2003-08-20 22:07:18 UTC

(hide)

Description: java test file to generate excel

Filename:

MIME Type:

Creator: vijay_alluri

Created: 2003-08-20 22:07:18 UTC

Size: 6.35 KB

patch

obsolete

> >import java.io.* ; >import java.sql.*; >import org.apache.poi.hssf.usermodel.* ; >import org.apache.poi.hssf.util.*; > >public class dbexcel { > > > public static void main(String[] args){ > process_request() ; > } > > > public static void process_request() > { > System.out.println("Excel start:"); > > String _driver="oracle.jdbc.driver.OracleDriver"; > String _URL="jdbc:oracle:thin:@livdsdw1:1528:WIB_DEV"; > String _UID="valluri",PWD="valluri"; > String sqlstr = "select GROUPING(TYPE_CODE) ST1,GROUPING(DSP_DIV_NO) ST2, TYPE_CODE, DSP_DIV_NO Division, sum(BOUND#) BOUND#, sum(LOST#) LOST#, sum(BOUND$) BOUND$, sum(LOST$) LOST$, sum(EXP#) EXP#, sum(REN#) REN#, sum(EXP$) EXP$, sum(REN$) REN$ from vsbuc_data a,sbuc_producer b where a.producer_no=b.producer_no group by CUBE(TYPE_CODE,DSP_DIV_NO) order by 1,3 desc,2,4"; > > Timer timer = new Timer(); > > HSSFWorkbook wb = new HSSFWorkbook() ; > HSSFSheet wk_sht = wb.createSheet("PAR by Div") ; > HSSFCellStyle st_header = wb.createCellStyle() ; > HSSFCellStyle cs2 = wb.createCellStyle() ; > HSSFCellStyle cstot = wb.createCellStyle() ; > > wk_sht.setGridsPrinted(true); > > int col_cnt=0, rw_cnt=0 ; > > > HSSFRow rw = null ; > HSSFCell cell =null; > > HSSFFont f1 = wb.createFont() ; > HSSFFont f2 = wb.createFont() ; > > f1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD) ; > f1.setColor(HSSFColor.BLACK.index); //BLUE-4, Green-3,Red-2,White-1 > > f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD) ; > f2.setColor(HSSFColor.WHITE.index); //BLUE-4, Green-3,Red-2,White-1 > > st_header.setFont(f1) ; > cs2.setFont(f2) ; > cstot.setFont(f1); >/* > st_header.setBorderBottom(HSSFCellStyle.BORDER_THIN) ; > st_header.setBorderLeft(HSSFCellStyle.BORDER_THIN) ; > st_header.setBorderRight(HSSFCellStyle.BORDER_THIN) ; > st_header.setBorderTop(HSSFCellStyle.BORDER_THIN) ; >*/ > st_header.setAlignment(HSSFCellStyle.ALIGN_CENTER); > st_header.setFillPattern((short)1);//BIG_SPOTS > st_header.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);//0xd Yellow > cs2.setFillPattern((short)1);//BIG_SPOTS > cs2.setFillForegroundColor(HSSFColor.BLACK.index);//GREY_25_PERCENT > > //Title > rw = wk_sht.createRow((short)0) ; > cell = rw.createCell((short)2) ; > cell.setCellValue("Producer Analysis Report By Division") ; > cell.setCellStyle(cstot); > rw = wk_sht.createRow((short)1) ; > //cell.setCellStyle(); > > > Statement stmt =null; > ResultSet rs =null; > ResultSetMetaData md =null; > Connection conn = null; > > try{ > timer.start(); > > Class.forName(_driver); > conn = DriverManager.getConnection(_URL,_UID,PWD); > stmt = conn.createStatement (); > > rs = stmt.executeQuery (sqlstr); > md = rs.getMetaData(); > col_cnt = md.getColumnCount(); > > timer.stop(); > System.out.println("db stuff:" + timer.getElapsedTime()/1000.0); > > timer.start(); > > int []CELLTYPE= new int[col_cnt]; > String temp=null; > int size=0; > short i=2; > boolean hdflag=true; > > //Header row > rw = wk_sht.createRow((short)i) ; > for(short j=4; j<col_cnt; j++){ > cell = rw.createCell((short)(j-4)) ; > temp=md.getColumnLabel(j); > cell.setCellValue(md.getColumnLabel(j)) ; > cell.setCellStyle(st_header) ; > > switch(md.getColumnType(j)) { > case Types.CHAR: > case Types.VARCHAR: > case Types.LONGVARCHAR: > CELLTYPE[j]=HSSFCell.CELL_TYPE_STRING; > break; > case Types.TINYINT: > case Types.SMALLINT: > case Types.INTEGER: > case Types.BIGINT: > case Types.FLOAT: > case Types.DOUBLE: > case Types.DECIMAL: > case Types.NUMERIC: > //case Types.NUMBER: > CELLTYPE[j]=HSSFCell.CELL_TYPE_NUMERIC; > break; > default: > CELLTYPE[j]=HSSFCell.CELL_TYPE_STRING; > } > //System.out.println("Type:" + j + ":" + md.getColumnType(j)); > size = md.getColumnDisplaySize(j); > size = size > temp.length() + 2 ? size:temp.length() + 2; > size = size > 30 ? 30:size; > wk_sht.setColumnWidth((short) (j-4),(short) (size * 256)); > > } > > > //sheet1.createFreezePane( 0, 1, 0, 1 ); > wk_sht.createFreezePane( 1, 3 ); > > while (rs.next()){ > i++; > > rw = wk_sht.createRow((short)i) ; > if(hdflag){ > cell = rw.createCell((short)0) ; > if(rs.getString(1).equals("1")) cell.setCellValue("Total"); > else cell.setCellValue(rs.getString(3)) ; //Wholesale > cell.setCellStyle(cs2) ; > hdflag=false; > for(short k=4;k<col_cnt-1;k++){ > cell = rw.createCell((short)(k-3)) ; > cell.setCellStyle(cs2) ; > } > //Header row > //------ > //row > i++; > rw = wk_sht.createRow((short)i) ; > for(short j=4; j<col_cnt; j++){ > cell = rw.createCell((short)(j-4)) ; > cell.setCellValue(rs.getString(j)) ; > } > > } > else{ > for(short j=4; j<col_cnt; j++){ > cell = rw.createCell((short)(j-4)) ; > if(rs.getString(2).equals("1")){ > hdflag=true; > if(j==4)cell.setCellValue("Total "); > else cell.setCellValue(rs.getString(j)); > cell.setCellStyle(cstot); > } > else { > if(j > 4)cell.setCellValue(rs.getDouble(j)) ; > else cell.setCellValue(rs.getString(j)) ; > > //cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); > //System.out.println("Type:" + CELLTYPE[j] + "," + HSSFCell.CELL_TYPE_NUMERIC + "," + HSSFCell.CELL_TYPE_STRING); > } > } > } > }//end while > > rs.close(); > stmt.close(); > conn.close(); > }//try > catch ( Exception e){ > System.out.println ("error:" + e ); > } > finally { > try{rs.close();} > catch(Exception e){} > try{stmt.close();} > catch(Exception e){} > try{conn.close();} > catch(Exception e){} > } > > wk_sht.setDefaultColumnWidth((short) 18) ; > //for(short j=0; j<col_cnt; j++){ > // if(j%2==1) wk_sht.setColumnWidth(j,(short) (6 * 256)) ; > // else wk_sht.setColumnWidth(j,(short) (18 * 256)) ; > //} > wk_sht.getPrintSetup().setLandscape(true); > timer.stop(); > System.out.println("Excel Object Created:" + timer.getElapsedTime()/1000.0); > > try > { > timer.start(); > FileOutputStream out = new FileOutputStream("test.xls") ; > wb.write(out) ; > > out.close() ; > timer.stop(); > System.out.println("Excel write to File :" + timer.getElapsedTime()/1000.0); > } > catch(IOException io_Excp) > { > System.out.println(io_Excp.getMessage()) ; > } > } > > public void createsubtype(HSSFRow rw,HSSFCellStyle cs,int col_cnt){ > > //HSSFCell c=null; > // for(int j=0; j<col_cnt; j++){ > // c=rw.createCell((short)j) ; > // } > > > } > >} > View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]