Home » Developer & Programmer » Forms » accomdating more than 70,000 rows in an excel (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod, Windows XP) , Forms 6i)
accomdating more than 70,000 rows in an excel [message #410656] Mon, 29 June 2009 09:51 Go to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
We are creating an excel using OLE2. One excel can accomdate round about 60,000 rows. Now if the rows exceed the limit then we need to create multiple files.
I want to ask that is there any particular way to solve this problem or do we need to develop some home made logic?
Re: accomdating more than 70,000 rows in an excel [message #410658 is a reply to message #410656] Mon, 29 June 2009 10:02 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Newer versions of Excel no longer limit number of rows below 64K
Re: accomdating more than 70,000 rows in an excel [message #410660 is a reply to message #410656] Mon, 29 June 2009 10:10 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
what if we are using the older version?
By the way from which version this restriction has relaxed?
Re: accomdating more than 70,000 rows in an excel [message #410664 is a reply to message #410656] Mon, 29 June 2009 10:21 Go to previous messageGo to next message
tmc1
Messages: 2
Registered: June 2009
Location: USA
Junior Member
If you can add another worksheet to your Excel file, then you will another set of 65,000 rows to use.
Re: accomdating more than 70,000 rows in an excel [message #410668 is a reply to message #410656] Mon, 29 June 2009 10:33 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Your problem is an Excel problem not an Oracle problem.

http://www.lmgtfy.com/?q=excel+row+limit
Re: accomdating more than 70,000 rows in an excel [message #410669 is a reply to message #410656] Mon, 29 June 2009 10:39 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Can we do that?
You mean we can do it as

CURSOR Cu
SELECT 	ROWNUM
FROM 	TABLE;
workbooks 	:= OLE2.GET_OBJ_PROPERTY(application, 'Workbooks'); 
workbook 	:= OLE2.GET_OBJ_PROPERTY(workbooks	, 'Add');
worksheets 	:= OLE2.GET_OBJ_PROPERTY(workbook		, 'Worksheets');
worksheet 	:= OLE2.GET_OBJ_PROPERTY(worksheets	, 'Add');
FOR C1 IN Cu LOOP
.....
.....
IF ROWNUM = 60000 THEN
   workbooks 	:= OLE2.GET_OBJ_PROPERTY(application, 'Workbooks'); 
workbook 	:= OLE2.GET_OBJ_PROPERTY(workbooks	, 'Add');
worksheets 	:= OLE2.GET_OBJ_PROPERTY(workbook		, 'Worksheets');
worksheet 	:= OLE2.GET_OBJ_PROPERTY(worksheets	, 'Add');
END IF;
END LOOP;
Re: accomdating more than 70,000 rows in an excel [message #410670 is a reply to message #410656] Mon, 29 June 2009 10:56 Go to previous messageGo to next message
mcelhannon
Messages: 2
Registered: June 2009
Junior Member
Why not .CSV file instead of .XLS? There is not a row restriction on it.

Thanks,
Brad

[Updated on: Mon, 29 June 2009 10:58]

Report message to a moderator

Re: accomdating more than 70,000 rows in an excel [message #410675 is a reply to message #410656] Mon, 29 June 2009 11:08 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
You are rite but this is the requirement
Re: accomdating more than 70,000 rows in an excel [message #410800 is a reply to message #410656] Tue, 30 June 2009 06:03 Go to previous message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
ok issue resolved by using tmc1 solution
Previous Topic: WHY my PJC Bean not show? (merged 2)
Next Topic: Database column in a non-database block
Goto Forum:
  


Current Time: Sun Dec 04 09:00:01 CST 2016

Total time taken to generate the page: 0.09505 seconds