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) ;
	//	}


	}

}