Bug 52638 - Workbook created with HSSF doesn't render properly, hidden sheets values are shown in the first non hidden sheet
Summary: Workbook created with HSSF doesn't render properly, hidden sheets values are ...
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-02-10 10:58 UTC by nithin
Modified: 2012-02-10 11:46 UTC (History)
1 user (show)



Attachments
1 hidden sheet and 1 non hidden sheet (8.00 KB, application/vnd.ms-excel)
2012-02-10 10:58 UTC, nithin
Details

Note You need to log in before you can comment on or make changes to this bug.
Description nithin 2012-02-10 10:58:52 UTC
Created attachment 28297 [details]
1 hidden sheet and 1 non hidden sheet

hi,

I have created a excel workbook using hssf.

the workbook contains two sheets 

sheet 1 :"Master" is hidden

sheet 2: "CreateFeed" 

When opening the sheet in excel , nothing is rendered (even the Row and column id's of excel).

When i highlight cells the Values appear , but instead of the "createfeed" values , the "Master" sheets values which are supposed to be hidden are shown.


I have attached the excel sheet.

Please forgive my bad english.

regards,
nithen
Comment 1 Yegor Kozlov 2012-02-10 11:08:46 UTC
Which version of POI? 

Please post the Java code you are using.

Yegor
Comment 2 nithin 2012-02-10 11:10:00 UTC
 I am using POI- 3.7
Comment 3 nithin 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();
	
}

}
Comment 4 Yegor Kozlov 2012-02-10 11:35:37 UTC
Please try with the latest POI 3.8-beta5.

Also, please post an isolated Java code that does not require any requests to a a database. 

Yegor
Comment 5 nithin 2012-02-10 11:46:12 UTC
POI 3.8 Fixes the issue...


I am not sure what caused the issue though...

If anyone faces the issue, please upgrade your jars