You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2012/02/10 11:58:52 UTC
DO NOT REPLY [Bug 52638] New: Workbook created with HSSF doesn't
render properly, hidden sheets values are shown in the first non hidden sheet
https://issues.apache.org/bugzilla/show_bug.cgi?id=52638
Bug #: 52638
Summary: Workbook created with HSSF doesn't render properly,
hidden sheets values are shown in the first non hidden
sheet
Product: POI
Version: unspecified
Platform: PC
Status: NEW
Severity: major
Priority: P2
Component: HSSF
AssignedTo: dev@poi.apache.org
ReportedBy: nithen.ac@hcl.com
Classification: Unclassified
Created attachment 28297
--> https://issues.apache.org/bugzilla/attachment.cgi?id=28297
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
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 52638] Workbook created with HSSF doesn't render
properly, hidden sheets values are shown in the first non hidden sheet
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=52638
nithin <ni...@hcl.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |nithen.ac@hcl.com
OS/Version| |All
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 52638] Workbook created with HSSF doesn't render
properly, hidden sheets values are shown in the first non hidden sheet
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=52638
Yegor Kozlov <ye...@dinom.ru> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #1 from Yegor Kozlov <ye...@dinom.ru> 2012-02-10 11:08:46 UTC ---
Which version of POI?
Please post the Java code you are using.
Yegor
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 52638] Workbook created with HSSF doesn't render
properly, hidden sheets values are shown in the first non hidden sheet
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=52638
--- Comment #3 from nithin <ni...@hcl.com> 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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 52638] Workbook created with HSSF doesn't render
properly, hidden sheets values are shown in the first non hidden sheet
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=52638
--- Comment #4 from Yegor Kozlov <ye...@dinom.ru> 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
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 52638] Workbook created with HSSF doesn't render
properly, hidden sheets values are shown in the first non hidden sheet
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=52638
--- Comment #2 from nithin <ni...@hcl.com> 2012-02-10 11:10:00 UTC ---
I am using POI- 3.7
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 52638] Workbook created with HSSF doesn't render
properly, hidden sheets values are shown in the first non hidden sheet
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=52638
nithin <ni...@hcl.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |RESOLVED
Resolution| |WORKSFORME
--- Comment #5 from nithin <ni...@hcl.com> 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
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org