https://issues.apache.org/bugzilla/show_bug.cgi?id=52638
--- Comment #3 from nithin <[email protected]> 2012-02-10 11:17:18 UTC --- ====================java code====================== package com.hcl.eimcc.createfeed; import java.sql.*; import java.util.*; import java.io.*; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddressList; public class chumma { public static void main(String[] args) throws Exception { String mValueSql = "select * from edis_master_values order by MASTER_TYPE_ID;"; String MTypesSql="select MASTER_TYPE from edis_master_types order by MASTER_TYPE_ID;"; List MasterValues= mysqlv(mValueSql); List MasterTypes= mysqlt(MTypesSql); writeExcel(MasterValues,MasterTypes); } public static List mysqlv(String Sql)throws Exception { Connection conn=getconnection(); conn.setAutoCommit(false); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(Sql); List SourceData= new ArrayList(); List Data = new ArrayList(); ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns=rsMetaData.getColumnCount(); for (int i = 1; i < numberOfColumns + 1; i++) { String columnName = rsMetaData.getColumnLabel(i); // System.out.print(columnName + " "); Data.add(columnName); } SourceData.add(Data); // System.out.println(); // System.out.println("----------------------"); int k=0;// ROW COUNT while (rs.next()) { List Data1 = new ArrayList(); for (int i = 1; i < numberOfColumns + 1; i++) { // System.out.print(rs.getString(i) + " "); Data1.add(rs.getString(i)); if(i==numberOfColumns) { } } // System.out.println(); SourceData.add(Data1); k++;// row count } System.out.println("Row Count :"+ k);// row count System.out.println("arraylist size"+SourceData.size()); //writeExcel(SourceData); rs.close(); st.close(); conn.close(); return SourceData; } public static List mysqlt(String Sql)throws Exception { Connection conn=getconnection(); conn.setAutoCommit(false); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(Sql); List SourceData= new ArrayList(); int k=0;// ROW COUNT while (rs.next()) { System.out.println(rs.getString(1)); SourceData.add(rs.getString(1)); k++;// row count } System.out.println("Row Count cf :"+ k);// row count System.out.println("arraylist size cf "+SourceData.size()); for (int i1=0;i1<SourceData.size();i1++) { System.out.println(SourceData.get(i1).toString()); } rs.close(); st.close(); conn.close(); return SourceData; } public static Connection getconnection() { Connection conn = null; String url = "jdbc:mysql://localhost:3306/"; String dbName = "eimcc"; String driver = "com.mysql.jdbc.Driver"; String userName = "root"; String password = "root"; try { Class.forName(driver).newInstance(); conn = DriverManager.getConnection(url+dbName,userName,password); // System.out.println("Connected to the database"); //System.out.println("Disconnected from database"); } catch (Exception e) { e.printStackTrace(); } return conn; } public static void writeExcel(List SourceData, List MasterTypes) throws Exception { int rownum=SourceData.size(); // String sname1 = "Master",sname2 = "CreateFeed", cname = "TestName",cvalue = "TestVal"; HSSFWorkbook wb = new HSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream("D:\\playbook1.xls"); HSSFSheet worksheet1 = wb.createSheet("Master"); HSSFSheet worksheet2 = wb.createSheet("CreateFeed"); //worksheet1.autoSizeColumn(0); wb.setActiveSheet(1); wb.setSheetHidden(0, true); for (int i1=0;i1<SourceData.size();i1++) { Row row = worksheet1.createRow((short)i1); } int r=0; int c=0; int i=1; String start= null; List cell1 = (List) SourceData.get(1); start=cell1.get(1).toString(); while(i<SourceData.size()) { List cell = (List) SourceData.get(i); String mt=cell.get(1).toString(); if (mt.equals(start)) { r++; Row row = worksheet1.getRow(r); row.createCell(c).setCellValue(cell.get(2).toString()); //System.out.println("mt :"+mt+ " r :"+r+ " c:"+c +" i" +i); }else { r=1; c++; Row row = worksheet1.getRow(r); row.createCell(c).setCellValue(cell.get(2).toString()); start=mt; //System.out.println("mt :"+mt+ " r :"+r+ " c:"+c +" i" +i); } i++; } System.out.println("SourceData size : "+SourceData.size()); // create feed Row row = worksheet2.createRow(0); Row row1 = worksheet2.createRow(1); row.createCell(0).setCellValue("Feed Name"); for (int i1=1;i1<MasterTypes.size();i1++) { String cell2=MasterTypes.get(i1).toString(); row.createCell(i1).setCellValue(cell2); } CellRangeAddressList addressList = new CellRangeAddressList(1,1, 0, 0); DVConstraint dvConstraint ; dvConstraint = DVConstraint.createFormulaListConstraint("'Master'!$A:$A"); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); worksheet2.addValidationData(dataValidation); for (int i1=1;i1<MasterTypes.size();i1++) { } wb.write(fileOut); fileOut.flush(); fileOut.close(); } } -- Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the assignee for the bug. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
