Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
15
Erster Beitrag:
vor 6 Jahren, 10 Monaten
Letzter Beitrag:
vor 6 Jahren, 9 Monaten
Beteiligte Autoren:
Al, DW, Jimbo, Allard, Claude H.pcs.crosspost, M. Beaven, John, Arie

[WD16] Exporting from table to Excel

Startbeitrag von Al am 24.09.2011 03:03

Hello All

I am trying to export a large quantity of records (130,000+) from a memory table to Excel
The AAF option does not offer an xlsx (Excel 2007 + ) option and stops at 65000 records.
If I manually change the extension on the file being created in the AAF export option then the app crashes.

Reading the help doco indicates that if I manually write the data from the table the I can nominate an xlsx file extension and Windev will write a file that exceeds the 65000 row limit
However this also crashed the app with a Windows error - Your program has stopped report to Microsoft...

From the help file:
The Excel files cannot exceed 65536 rows and 256 columns if the extension used is ".xls". This limit can be exceeded by using the ".xlsx" extension in order to create files in Excel 2007 format. Caution: in this case, the former versions of Excel must be equipped with the "Office 2007 compatibility pack", that can be downloaded

Has anyone successfully exported more that 65000 rows from a memory table to an Excel xlsx file ?

Regards
Al

Antworten:

Hi, Excel up to version 2003 cannot display / use worksheets with more than 65000 records. finito. Excel 2007 and 2010 work with up to 1 million records. LibreOffice offers 1 million lines as well starting from version 3.3
http://www.xlam.ch/xlimits/excel14.htm
Regards, Guenter




von Jimbo - am 24.09.2011 16:39
Hello Guenter

I know about the limits, but the WIndev Help doco states that it handles both 2003 "xls" and 2007 "xlsx" type files and further it indicates that simply by changing the extension on the file you write from Windev to the 2007 version of ". xlsx" will allow it to write more than 65000 records but whenever I try, Windev crashes the app.

Regards
Al

von Al - am 24.09.2011 22:56
Hi Al, if this malfunction hurts you, please report to our friends in Montpellier! Thank you, Guenter


von Jimbo - am 25.09.2011 03:10
Hi Al,

I have the same problem with WD14 too. Unfortunately my clients are a bit pissed that despite having Office 2007 they still cannot export to Excel. I don't blame them. Ended up using DevExpress Reports to make it work.

Perhaps PCSoft will fix it in the next 996 new features ! Or perhaps not......

Regards

John

von John - am 25.09.2011 22:10
Hello John

I thought I might be able to work around it with xml. Windev will export an xml file from a table regardless of the number of rows. The problem then was that Excel 2007 opened it, asked me all the right questions regarding formating and then went away and never came back with the data.

So next question, is there some trick to get Excel 2007 to read a large XML file of 130,000+ rows ?

Regards
Al

von Al - am 26.09.2011 00:00
Hello Al,

Just a idea, have you tried to open a excel 2007 file write to it with code and then save it?

DW

von DW - am 26.09.2011 00:33
Hello DW

Thanks for the thought - I had tried that using the code from the Help but it crashed also.
I think it just doesn't work as the Help indicates in terms of xlsx as an extension with large data sets. The aaf and my code all work with the xlsx extension to produce an xlsx file, it is just the large number of rows that causes it to crash.

Regards
Al

von Al - am 26.09.2011 01:11
Al,

We were able to work around this limitation in creating some exports in a Webdev project, but I don't have the project in front of me right now to see what we had to do.

If I remember correctly- perhaps we saved them as a .txt then used FileDisplay with the MIME type for "text/plain" on the client side.

This results in excel opening the file provided that is the default for text files, and I know some of the exports exceed 65,000 rows.

Not sure this would be the same for Windev, but thought I would throw it out there.

Regards,

-Marc

von M. Beaven - am 26.09.2011 15:13
And then you could use SaveAs to "convert" it to the xlsx format.

// Class for controlling Excel
pautExcel is object OLE dynamic

// allocate a new one
pautExcel = new object OLE "Excel.Application"

// open the requested workbook
pautExcel>>Workbooks>>Open(sFile)

pautExcel>>DisplayAlerts = False // skip "Do you want to overwrite..."

pautExcel>>ActiveWorkbook>>SaveAs(sFile,nExcelFormat)

pautExcel>>DisplayAlerts = True

pautExcel>>ActiveWorkbook>>Close(False)


where nExcelFormat can be

CONSTANT
xlExcelWorkbookNormal = -4143 //Workbook Normal
xlExcel5 = 39 //Excel5.0
xlExcel8 = 56 //Excel8 97-2003
xlExcel9795 = 43 //Excel9795
xlOpenXMLWorkbook = 51 //Open XML Workbook 2007-2010
xlOpenXMLWorkbookMacroEnabled = 52 //Open XML Workbook Macro Enabled 2007-2010
END


(Google for more options...)

von Arie - am 26.09.2011 15:22
Hello Al,

It's an old known bug : 61 879.
Very very slow export followed by a crash, the xlsx file is also corrupted.
Please report to PC Soft too.


Claude

Message forwarded from pcsoft.us.windev

von Claude H.pcs.crosspost - am 26.09.2011 16:00
Hello

Marc & Arie - thanks for the idea and code I shall try out that solution

Claude, I am a bit disillusioned with PCSoft developers and their desire to fix things. They clearly have the technical ability so there is no real excuse for long running bugs but I shall report it.

The help doco explicitly states that using an xlsx extension provides support for large exports past the 65000 limit so how did it get past their testing.

"The Excel files cannot exceed 65536 rows and 256 columns if the extension used is ".xls". This limit can be exceeded by using the ".xlsx" extension in order to create files in Excel 2007 format"

I now have two areas where I have to admit to my clients that the reason I can't deliver some fairly basic functionality is that my base development platform has "limitations" (bugs) in it and I have no idea if or when they will be fixed.

Regards
Al

von Al - am 26.09.2011 21:16
Hello Al,

Tested write 190,000 rows to Excel no problems.

Code:


PROCEDURE lp_SaveToExcelTest()

//Create an empty Excel file next to the exe called TestExcel.xlsx
sMyFile is string = CompleteDir(fExeDir()) + "TestExcel.xlsx"

IDXLSFile is xlsDocument = xlsOpen(sMyFile,xlsWrite)

nCount is int = 190000
nRow is int = 1

//This is a window I use as a progress bar
bWinOpen is boolean = False
rStepValue is real = Round(80/nCount,10)
IF nCount > 3 THEN
OpenSister(WIN_Dialog_ProgressBar,"Test Excel","Testing Excell 190,000 rows",10)
bWinOpen=True
Multitask(25)
END
//For progress bar
rRecCounter is real = 10+rStepValue



IF nCount > 0 THEN
FOR i =1 TO nCount
IF bWinOpen THEN WIN_Dialog_ProgressBar.SC.DispProgressBar(rRecCounter)
IDXLSFile[nRow,"A"]="Hello - "+nRow
IDXLSFile[nRow,"B"]="My Name is - "+nRow
IDXLSFile[nRow,"D"]="World "+nRow

nRow++
HReadNext(QRY_CS_Table)
rRecCounter = rRecCounter+rStepValue
IF bWinOpen THEN WIN_Dialog_ProgressBar.SC.STC_Text="Processing row Number : "+nRow
END
END

IF bWinOpen THEN WIN_Dialog_ProgressBar.SC.DispProgressBar(100)
Multitask(25)
IF bWinOpen THEN Close(WIN_Dialog_ProgressBar)

//Save it to the desktop
sDirectory is string = SysDir(srDesktop)
//Make the full file path to save to
sSaveFile is string = sDirectory+"\"+"TestExcel"+".xlsx"

xlsSave(IDXLSFile,sSaveFile)


xlsClose(IDXLSFile)

IF YesNo("Excel file is on your desktop","","Would you like to open it?") = Yes THEN
ShellExecute(sSaveFile)
END



If you have to write to a older version of Excel, write 50,000 lines to each worksheet.

DW


von DW - am 26.09.2011 23:15
Hello DW

Thanks very much for the code example.

In your earlier post, I missed the part about creating a file first and then writing to it. I was using the example from the help which simply used the table export function.

Regards
Al

von Al - am 27.09.2011 01:32
Hi,
Maybe post this to the windev solutions forum ?

Allard

von Allard - am 27.09.2011 09:57
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.