Read/write XLSX without OLE

[Srdjan]

Now make XLSX class for read data but only data without styles

[Baxajaun]

Dear Srdan,

thanks a lot for your time and help.

King regards,

El miércoles, 15 de abril de 2020, 10:53:19 (UTC+2), Srdjan escribió:

[Anand K Gupta]

Thanks a lot Srdjan.

Regards,

Anand

On Wed, Apr 15, 2020 at 2:23 PM 'Srdjan' via Harbour Users <harbou...@googlegroups.com> wrote:

Now make XLSX class for read data but only data without styles

--
--
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: http://groups.google.com/group/harbour-users

---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/ea7a82c0-bdee-4f39-bc33-c697af65f7ec%40googlegroups.com.

[Paola Bruccoleri]

Hi Srdjan

Have you updated your class?

Verey thanks

---

De: "'Srdjan' via Harbour Users" <harbou...@googlegroups.com>
Para: "Harbour Users" <harbou...@googlegroups.com>
Enviados: Miércoles, 15 de Abril 2020 5:53:19
Asunto: [harbour-users] Read/write XLSX without OLE

Now make XLSX class for read data but only data without styles

[Srdjan]

Yes, I update. Add drawing class, rowdetail method in worksheet class and add read method in workbook class for read data from an existing workbook but simple read data without styles, margine, merge cells ...

On Wednesday, April 15, 2020 at 10:47:00 PM UTC+2, Paola Bruccoleri wrote:

Hi Srdjan

Have you updated your class?

Verey thanks

---

De: "'Srdjan' via Harbour Users" <harbou...@googlegroups.com>
Para: "Harbour Users" <harbou...@googlegroups.com>
Enviados: Miércoles, 15 de Abril 2020 5:53:19
Asunto: [harbour-users] Read/write XLSX without OLE

Now make XLSX class for read data but only data without styles

--
--
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.

Unsubscribe: harbou...@googlegroups.com

Web: http://groups.google.com/group/harbour-users

---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to harbou...@googlegroups.com.

[Auge & Ohr]

hi,

i got a Error XLSXClass.prg in Line 664

   aADD( ::aDrawinngsObjects, oDrawing )

must be

   aADD( ::aDrawingsObjects, oDrawing )

greetings by OHR
Jimmy

[Srđan]

Yes. Thank you.

I make small revision

[Auge & Ohr]

thx for new Version.

i have work with your Contribution, GREAT

now when i want to "export" DBF into Excel Sheet and i have to use CELL() Method to fill it "one-by-one"

DO WHILE !EOF()
   ii := 1
   FOR ii := 1 TO iMax
      cCell  := Num2Cell(ii)+LTRIM(STR(nCount)) // A1 - ZZ1
      xValue := FieldGet(ii)

      oSheet1:Cell(cCell,xValue)
   NEXT
   nCount ++

using ActiveX i can assign hole Array to Excel-Sheed so this is my Question :

   oSheet:range( "A1:"+cEnde+LTRIM(STR(numRows)) ):value := aExcel

is there any Way to pass hole Array ?

---

i have try to use Path+Name in Workbook

   oExcel := WorkBook() :New(cExportTo)

but it does not work with Path ...

i have look into Source

   c := DISKNAME() + hb_OSDriveSeparator() + hb_PS() + CURDIR()
   DIRCHANGE( ::cTempDir )
   FERASE( c + cSep + ::cName )
   myzip( c + cSep + ::cName, "*.*", ::cTempDir )
   DIRCHANGE( c )
   hb_DirRemoveAll( ::cTempDir )
RETURN Self

so i have to "MoveFile" after create ...

---

when transfer Data to Excel Data Type might recognize wrong

Question : how to "Format" Column so Excel recognize right if Number, Date or String

thx for your great Work

greetings by OHR
Jimmy

[Srđan]

resolve problem with path in filename

get from

212.24.108.10/download.php?file=XLSXClass.prg

[Auge & Ohr]

THX

greetings by OHR

Jimmy

[Auge & Ohr]

hi,

after i can write XLSx i want to read XLSx an store it to Array

i saw Method Read() but i need "more Information" about "Size of XLSx"

   DATA nMaxRow PROTECTED
   DATA nMaxCol PROTECTED

to use CELL() Method i need Coordinate using ColumnIndexToColumnLetter()

Question : why a STATIC FUNCTION ColumnIndexToColumnLetter() and not a Method ?

---

to open a Workbook your Sample use

   oSheet1 := oExcel:WorkSheet( "Test1" )

but when get a *.XLSx i don´t know how User have named Workbook.

is there a Solution to

1.) open *.XLSx
2.) read Size of XLSx and create Array with same Size
3.) read CELL and fill Array when Type C,N,D,L
4.) use Array for GRID

[Srđan]

Add methods for reading nMaxRow and nMaxCol

get from

212.24.108.10/download.php?file=XLSXClass.prg

for first arg in Cell method you can write "A1" or {1,1}

[Auge & Ohr]

hi,

i want to run XLSXReader Sample but now it fail ?

ERROR BASE/1108   Argument Error: AT

i guess it have to do with PATH which was change in CLASS.

can you please have a look, THX

#INCLUDE "hbclass.ch"
REQUEST HB_CODEPAGE_UTF8EX
REQUEST HB_GT_WIN_DEFAULT                                             // Console

PROCEDURE MAIN( a )
LOCAL oExcel, oSheet1, oSheet2, nFont
   hb_cdpSelect( 'UTF8EX' )
   SETMODE( 25, 80 )
   IF a == NIL
      ALTD()
   ENDIF
   SET DATE FORMAT "dd.mm.yyyy"
   oExcel := WorkBook() :New()
   oExcel:Read( "sample.xlsx" )

   oSheet1 := oExcel:WorkSheet( "Test1" )

   ? "A1=", oSheet1:Cell( "A1" ), "B1=", oSheet1:Cell( "B1" ), "C1=", oSheet1:Cell( "C1" )
   ? "A3=", oSheet1:Cell( "A3" ), "C3=", oSheet1:Cell( "C3" )

   WAIT

RETURN

[Srđan]

download new version

[Auge & Ohr]

hi,

download new version

i mean that Version from 27.05.2020 where i get (new) Error.

it work with Original Version as it was the include Demo Sample.

as new Version now work with PATH in Filename i guess that AT have to do with some String.

i can´t say more as i don´t get a Error.LOG ... just a red ALERT Box :(

greetings by OHR

Jimmy

[Srđan]

I change and upload new on 28.05.2020 6:02AM

[Enrique Cabrerizo]

It´s great job

How i can download ?

Is it a link ?

212.24.108.10/download.php?file=XLSXClass.prg 

  Thanks   

Best Regards

El jue., 28 may. 2020 a las 9:10, 'Srđan' via Harbour Users (<harbou...@googlegroups.com>) escribió:

I change and upload new on 28.05.2020 6:02AM

--

--
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.

Unsubscribe: harbour-user...@googlegroups.com

Web: http://groups.google.com/group/harbour-users

---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/5e0d2d1e-20de-408e-ac30-1fb30a1651a3%40googlegroups.com.

[Srđan]

Yes, this link

On Thursday, May 28, 2020 at 11:16:16 PM UTC+2, Enrique wrote:

It´s great job

How i can download ?

Is it a link ?

212.24.108.10/download.php?file=XLSXClass.prg 

  Thanks   

Best Regards

El jue., 28 may. 2020 a las 9:10, 'Srđan' via Harbour Users (<harbou...@googlegroups.com>) escribió:

I change and upload new on 28.05.2020 6:02AM

--
--
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.

Unsubscribe: harbou...@googlegroups.com

Web: http://groups.google.com/group/harbour-users

---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to harbou...@googlegroups.com.

[Enrique Cabrerizo]

Thank you very much

Best regards

Unsubscribe: harbour-user...@googlegroups.com

Web: http://groups.google.com/group/harbour-users

---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/6c9c64d3-1254-4195-bfba-d41687819294%40googlegroups.com.

[Auge & Ohr]

hi,

i have try with different DBF -> XLSX using this Code

   nRowLine := 1
   GO TOP
   DO WHILE !EOF()

      ii := 1
      FOR ii := 1

TO iMax
         cField := aStructure[ ii ] [ DBS_NAME ]
         cType := aStructure[ ii ] [ DBS_TYPE ]
         nPosi := FIELDPOS( cField )

         // made Function Public
         cCell := ColumnIndexToColumnLetter(ii)+HB_NTOS(nRowLine)
         xValue := FIELDGET( nPosi )

         IF cType $ "CNDLM"
            oSheet1:Cell( cCell, xValue )
         ELSE
            oSheet1:Cell( cCell, "unknown Type " + cType )
         ENDIF
      NEXT
      nRowLine ++

      IF ( nRowLine % nEvery ) = 0
         nProz := CalcPos( nRowLine, nMax )
         IF nProz > 100
            nProz := 100
         ENDIF
         SetProperty( "ExportDbf", "ProgressBar_1", "Value", nProz )
      ENDIF

      SKIP
   ENDDO
   oExcel:Save()
   hb_cdpSelect( cOldLang )

than i try to READ it

some XLSX run without Error ("sample.xlsx","sokun.XLSX")... but some XLSX ("ARTIKEL.XLSX") crash ?
 

     cOldLang := hb_cdpSelect( 'UTF8EX' )

      SET DATE FORMAT "dd.mm.yyyy"
      oExcel := WorkBook():New()

      * oExcel:Read(cFile)             // fail ... hm ... Path ?
      * oExcel:Read( "sample.xlsx" )   // work ...
      * oExcel:Read( "ARTIKEL.XLSX" )  // FAIL
      oExcel:Read( "sokun.XLSX" )      // work ... hm

      oSheet1 := oExcel:WorkSheet( "Test1" )

      nRow  := oSheet1:GetMaxRow()
      nCol  := oSheet1:GetMaxCol()
      aData := oSheet1:GetArray()

      msgInfo( "Row : "+LTRIM(STR(nRow))+" Col : "+LTRIM(STR(nCol)) )

      hb_cdpSelect( cOldLang )

i also add Method GetArray to get PROTECTED VAR and it have all what i want for virtual GRID :)

---

i have to find out why some DBF work and other don´t.
but i´m confuse as Error Message like

Error BASE/1132 Bound error array access Parameter : [ 1] = A { 6949 } [ 2] = N 6950
Called from WORKSHEET:READWORKSHEET(1076)
Called from WORKBOOK:READ(124)

or

Error BASE/1004 No exported method DEPTH Parameter : [ 1] = U NIL
Called from DEPTH(0)
Called from TXMLITERATOR:NEW(0)
Called from TXMLITERATORSCAN:NEW(0)
Called from WORKSHEET:READWORKSHEET(1044)
Called from WORKBOOK:READ(124)

i will try to make some Demos

[Paola Bruccoleri]

Hello Srdjan

Where can I download the latest version of xlsxclass?
I have the version of 1/29. Now I am creating a file where I am having trouble with some texts. Maybe it's some bug fixed in your latest version and I would like to try.

Thank you very much

---

De: "'Srdjan' via Harbour Users" <harbou...@googlegroups.com>
Para: "Harbour Users" <harbou...@googlegroups.com>
Enviados: Miércoles, 15 de Abril 2020 5:53:19
Asunto: [harbour-users] Read/write XLSX without OLE

Now make XLSX class for read data but only data without styles

--
--
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: http://groups.google.com/group/harbour-users

---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/ea7a82c0-bdee-4f39-bc33-c697af65f7ec%40googlegroups.com.

[Diego Pego]

Great work! thank you!

[be...@cssw.at]

Hello Srdjan

Where can I download the latest version of xlsxclass?

Thank you.

[Marek Olszewski "MOL"]

Hi!

How can I get latest version of xlsclass? Link above does not work...

Is this project abandoned?

[Srđan]

I currently do not have time to continue working on this class