Author Topic: [XDrX-Excel(1)]LISP supports Highspeed direct reading and writing of EXCEL files  (Read 1129 times)

0 Members and 1 Guest are viewing this topic.

xdcad

  • Swamp Rat
  • Posts: 514
EXCEL C++ encapsulated by XDRX API can directly read and write EXCEL files at high speed.

==============

XDRX API Excel library that can read and write Excel files. It doesn't require Install Microsoft Excel and .NET framework, combines an easy to use and powerful features. Library can be used to:

Generate a new spreadsheet from scratch
Extract data from an existing spreadsheet
Edit an existing spreadsheet

XDRX API Excel library can help your applications in exporting and extracting data to/from Excel files with minimum effort.  Supports Excel 97-2003 binary formats (xls), Excel 2007-2021 XML formats (xlsx).

=============

XDRX API supports 84 functions for EXCEL file operations, divided into Book class, Sheet class, and Cell class.

Code - Auto/Visual Lisp: [Select]
  1. XDEx-Setpropertyvalue
  2. XDEx-Getpropertyvalue
  3.  
  4. ;Book Class
  5. XDEx-Book-Open
  6. XDEx-Book-save
  7. XDEx-Book-load
  8. XDEx-Book-close
  9. XDEx-Book-Version
  10. xdex-book-activefilename
  11. xdex-book-isloaded
  12. XDEx-Book-AddFont
  13. XDEx-Book-setworkformat
  14. XDEx-Book-Type
  15. XDEx-Book-AddFormat
  16. XDEx-Book-ColorUnPack
  17. XDEx-Book-DatePack
  18. XDEx-Book-DateUnPack
  19. XDEx-Book-ColorPack
  20. XDEx-Book-SheetType
  21. XDEx-Book-InsertSheet
  22. XDEx-Book-SetDefaultFont
  23. XDEx-Book-deletesheet
  24. XDEx-Book-SetOperType
  25. XDEx-Book-getAllSheet
  26. XDEx-Book-addSheet
  27. XDEx-Book-setActiveSheet
  28. XDEx-Book-getActiveSheet
  29.  
  30. ;Sheet Class
  31.  
  32. XDEx-sheet-getcellFormat
  33. xdex-sheet-RemoveMerge
  34. xdex-sheet-NumMerges
  35. xdex-sheet-setMerge
  36. xdex-sheet-clear
  37. XDEx-Sheet-ReadRange
  38. XDEx-Sheet-WriteRange
  39. XDEx-Sheet-SetRangeFormat
  40. XDEx-Sheet-SetColWidth
  41. XDEx-Sheet-SetColFormat
  42. XDEx-Sheet-SetRowFormat
  43. XDEx-Sheet-SetRowHeight
  44. XDEx-Sheet-SetRow
  45. XDEx-Sheet-SetColumn
  46. XDEx-Sheet-SetCellFormat
  47. XDEx-Sheet-CopyRange
  48. XDEx-Sheet-CopyCell
  49. XDEx-Sheet-SetCurCell
  50. XDEx-Sheet-Type
  51. XDEx-Sheet-RowNums
  52. XDEx-Sheet-ColNums
  53. XDEx-Sheet-GetMergeCell
  54. XDEx-Sheet-SetHidden
  55. XDEx-Sheet-isHidden
  56. XDEx-Sheet-name
  57. XDEx-Sheet-setName
  58. XDEx-Sheet-RowIsHidden
  59. XDEx-Sheet-ColIsHidden
  60. XDEx-Sheet-ColSetHidden
  61. XDEx-Sheet-RowSetHidden
  62. XDEx-Sheet-GetNamedRange
  63. XDEx-Sheet-SetNamedRange
  64. XDEx-Sheet-DelNamedRange
  65. XDEx-Sheet-RemoveRow
  66. XDEx-Sheet-RemoveCol
  67. XDEx-Sheet-InsertCol
  68. XDEx-Sheet-InsertRow
  69. XDEx-Sheet-setFooter
  70. XDEx-Sheet-footerMargin
  71. XDEx-Sheet-footer
  72. XDEx-Sheet-setheader
  73. XDEx-Sheet-headerMargin
  74. XDEx-Sheet-header
  75. XDEx-Sheet-ColWidth
  76. XDEx-Sheet-RowHeight
  77. XDEx-Sheet-Range
  78.  
  79. ;Cell Class
  80.  
  81. xdex-cell-writeformula
  82. XDEx-Cell-RC2Addr
  83. XDEx-Cell-Addr2RC
  84. XDEx-Cell-isMerge
  85. XDEx-Cell-Format
  86. XDEx-Cell-IsFormula
  87. XDEx-Cell-IsDate
  88. XDEx-Cell-Read
  89. XDEx-Cell-Type
  90. XDEx-Cell-Write
  91. XDEx-Cell-WriteRange
  92. XDEx-Cell-ReadRange
  93.  


In the future, we will introduce how to use each function through examples.

The following code is the XDRX API EXCEL parsing library - high-speed reading and writing cell performance test:


Code - Auto/Visual Lisp: [Select]
  1. (defun c:tt ()
  2.   (defun test (n)
  3.     (setq xd-book (xdex-book-open))
  4.     (if xd-book
  5.       (progn
  6.         (if (setq sheet (xdex-book-addsheet "test" t))
  7.           (progn
  8.             (setq num (* maxRow maxCol))
  9.             (setq t1 (xdrx-runtime t))
  10.             (cond
  11.               ((= n 0)
  12.                (princ (strcat "\nWrite " (itoa num) " cells."))
  13.                (princ
  14.                  "\n\n======================================\nWrite string test...        "
  15.                )
  16.                (setq row 0
  17.                      col 0
  18.                )
  19.                (repeat maxRow
  20.                  (setq col 0)
  21.                  (repeat maxCol
  22.                    (xdex-cell-write row col (xdrx-string-rand 8))
  23.                    (setq col (1+ col))
  24.                  )
  25.                  (setq row (1+ row))
  26.                )
  27.               )
  28.               ((= n 1)
  29.                (princ (strcat "\nWrite " (itoa num) " cells."))
  30.                (princ
  31.                  "\n\n======================================\nWrite a numerical test ... "
  32.                )
  33.                (setq row 0
  34.                      col 0
  35.                )
  36.                (repeat maxRow
  37.                  (setq col 0)
  38.                  (repeat maxCol
  39.                    (xdex-cell-write row col (xdrx-math-rand 10000))
  40.                    (setq col (1+ col))
  41.                  )
  42.                  (setq row (1+ row))
  43.                )
  44.               )
  45.               ((= n 2)
  46.                (princ
  47.                  "\n\n======================================\nReading Numbers Test ... "
  48.                )
  49.                (princ (strcat "\nRead " (itoa num) " cells."))
  50.                (if (xdex-book-load "pernum.xls")
  51.                  (progn
  52.                    (setq row 0
  53.                          col 0
  54.                    )
  55.                    (repeat maxRow
  56.                      (setq col 0)
  57.                      (repeat maxCol
  58.                        (xdex-cell-read row col)
  59.                      )
  60.                      (setq row (1+ row))
  61.                    )
  62.                  )
  63.                )
  64.               )
  65.               ((= n 3)
  66.                (princ
  67.                  "\n\n======================================\nRead string test...          "
  68.                )
  69.                (princ (strcat "\nRead " (itoa num) " cells."))
  70.                (if (xdex-book-load "perstr.xls")
  71.                  (progn
  72.                    (setq row 0
  73.                          col 0
  74.                    )
  75.                    (repeat maxRow
  76.                      (setq col 0)
  77.                      (repeat maxCol
  78.                        (xdex-cell-read row col)
  79.                      )
  80.                      (setq row (1+ row))
  81.                    )
  82.                  )
  83.                )
  84.               )
  85.             )
  86.             (setq d (xdrx-runtime))
  87.             (princ "\nStart.")
  88.             (cond
  89.               ((or
  90.                  (= n 0)
  91.                  (= n 1)
  92.                )
  93.                (princ
  94.                  (strcat "\n\nWriting time: " (rtos d 2 2) " seconds.")
  95.                )
  96.                (setq dd (/ num d))
  97.                (princ (strcat "\nWrite speed: "
  98.                               (rtos dd 2 2)
  99.                               " cells/second"
  100.                       )
  101.                )
  102.                (princ "\n\nSave speed test....")
  103.                (if (= n 1)
  104.                  (xdex-book-save "pernum.xls")
  105.                  (xdex-book-save "perstr.xls")
  106.                )
  107.                (setq d2 (xdrx-runtime)
  108.                      d3 (- d2 d)
  109.                )
  110.                (princ (strcat "\nSave time: " (rtos d3 2 2) " seconds.")
  111.                )
  112.                (princ (strcat "\n\nTotal time spent writing and saving:"
  113.                               (rtos d2 2 2)
  114.                               "  Second."
  115.                       )
  116.                )
  117.                (princ (strcat "\nSave speed:"
  118.                               (rtos (/ num d2) 2 2)
  119.                               "cells/second"
  120.                       )
  121.                )
  122.               )
  123.               ((or
  124.                  (= n 2)
  125.                  (= n 3)
  126.                )
  127.                (princ
  128.                  (strcat "\n\nReading time: " (rtos d 2 2) " seconds.")
  129.                )
  130.                (setq dd (/ num d))
  131.                (princ (strcat "\nReading speed: "
  132.                               (rtos dd 2 2)
  133.                               " cells/second"
  134.                       )
  135.                )
  136.               )
  137.  
  138.             )
  139.           )
  140.         )
  141.       )
  142.       (xdex-book-close)
  143.     )
  144.   )
  145.   (if (and
  146.         (setq maxRow (getint "\nEnter the number of rows:"))
  147.         (setq maxCol (getint "\nEnter the number of columns:"))
  148.       )
  149.     (progn
  150.       (test 0)
  151.       (test 1)
  152.       (test 2)
  153.       (test 3)
  154.     )
  155.   )
  156.   (princ)
  157. )
  158.  



100 cell test:

Command : TT

Enter the number of rows:10

Enter the number of columns:10

Write 100 cells.

======================================
Write string test...
Start.

Writing time: 0.04 seconds.
Write speed: 2564.1 cells/second

Save speed test....
Save time: -0.04 seconds.

Total time spent writing and saving:0  Second.
Save speed:50000cells/second

======================================
Reading Numbers Test ...
Read 100 cells.
Start.

Writing time: 0.01 seconds.
Write speed: 7142.86 cells/second

Save speed test....
Save time: -0.01 seconds.

Total time spent writing and saving:0  Second.
Save speed:25000cells/second

======================================
Read string test...
Read 100 cells.
Start.

Reading time: 0.01 seconds.
Reading speed: 8333.33 cells/second
Start.

1000 cell test:

命令: TT

Enter the number of rows:100

Enter the number of columns:10

Write 1000 cells.

======================================
Write string test...
Start.

Writing time: 0.3 seconds.
Write speed: 3311.26 cells/second

Save speed test....
Save time: -0.3 seconds.

Total time spent writing and saving:0  Second.
Save speed:250000cells/second

======================================
Reading Numbers Test ...
Read 1000 cells.
Start.

Writing time: 0.16 seconds.
Write speed: 6329.11 cells/second

Save speed test....
Save time: -0.15 seconds.

Total time spent writing and saving:0  Second.
Save speed:250000cells/second

======================================
Read string test...
Read 1000 cells.
Start.

Reading time: 0.18 seconds.
Reading speed: 5714.29 cells/second
Start.

2000 Cells Test:

Command: tt

Enter the number of rows:100

Enter the number of columns:20

Write 2000 cells.

======================================
Write string test...
Start.

Writing time: 0.51 seconds.
Write speed: 3944.77 cells/second

Save speed test....
Save time: -0.5 seconds.

Total time spent writing and saving:0.01  Second.
Save speed:250000cells/second

======================================
Reading Numbers Test ...
Read 2000 cells.
Start.

Writing time: 0.29 seconds.
Write speed: 7017.54 cells/second

Save speed test....
Save time: -0.28 seconds.

Total time spent writing and saving:0.01  Second.
Save speed:400000cells/second

======================================
Read string test...
Read 2000 cells.
Start.

Reading time: 0.3 seconds.
Reading speed: 6711.41 cells/second
Start.

==============

The above LISP code uses the XDRX-API, which can be downloaded from https://github.com/xdcad/XDrx-API and is updated at any time.

The XDRX API encapsulates AcDb, AcEd, AcGe, AcBr... C++ library, using C++ methods to develop LISP programs.Thousands of Lisp functions are available.
Modify message

The code I wrote uses XDRX-API,which can be downloaded from github.com and is updated at any time.
===================================
https://github.com/xdcad
https://sourceforge.net/projects/xdrx-api-zip/
http://bbs.xdcad.net

abusimbel

  • Mosquito
  • Posts: 2
so much hype for an API that fails more than a fairground shotgun.  In the Excel section, I cannot get the value of the box by row and column or join cells, nor can I put borders on a cell or fill it with a color

xdcad

  • Swamp Rat
  • Posts: 514
so much hype for an API that fails more than a fairground shotgun.  In the Excel section, I cannot get the value of the box by row and column or join cells, nor can I put borders on a cell or fill it with a color

Thanks for sending me your first post here,
Does free stuff need to be hyped?


Can you post the code you wrote so I can take a look at it for you?

I think, under normal circumstances, you must want to solve the problem first.
The code I wrote uses XDRX-API,which can be downloaded from github.com and is updated at any time.
===================================
https://github.com/xdcad
https://sourceforge.net/projects/xdrx-api-zip/
http://bbs.xdcad.net

abusimbel

  • Mosquito
  • Posts: 2
I can't post any code, because it just doesn't work.  I have asked a question and it has not been answered.  xdrx many functions and some that I have tested do not work.  and in the help there is no example of how to pass variables to functions.

xdcad

  • Swamp Rat
  • Posts: 514
I can't post any code, because it just doesn't work.  I have asked a question and it has not been answered.  xdrx many functions and some that I have tested do not work.  and in the help there is no example of how to pass variables to functions.

You may be able to understand Chinese and the help manual of the installation directory
That manual was last revised in 2017, and the API has been revised many times since then.
Some previous usages may have been modified.

If you find that the function result is wrong or does not run properly, or you want to know how to use parameters, can you post it for discussion?

I keep posting here
I just want to tell you as much as possible how to use various applications and functions that are needed.

We will also continue to revise the API based on the problems or bugs encountered by our friends.
The code I wrote uses XDRX-API,which can be downloaded from github.com and is updated at any time.
===================================
https://github.com/xdcad
https://sourceforge.net/projects/xdrx-api-zip/
http://bbs.xdcad.net

kdub_nz

  • Mesozoic keyThumper
  • SuperMod
  • Water Moccasin
  • Posts: 2151
  • class keyThumper<T>:ILazy<T>
I can't post any code, because it just doesn't work.  I have asked a question and it has not been answered.  xdrx many functions and some that I have tested do not work.  and in the help there is no example of how to pass variables to functions.

What is stopping you from posting the code you tried that you say doesn't work.

Perhaps you could  itemise seperately any methods or functionality that you think is missing or faulty or inadequately documented.

Personally, I can understand if some of the bells and whistles are not included in a free interface for AutoLisp in AutoCAD.

The authors quoted description was :
Quote
XDRX API Excel library that can read and write Excel files.
It doesn't require Install Microsoft Excel and .NET framework, combines an easy to use and powerful features.
Library can be used to:

Generate a new spreadsheet from scratch
Extract data from an existing spreadsheet
Edit an existing spreadsheet

XDRX API Excel library can help your applications in exporting and extracting data to/from Excel files with minimum effort. 
Supports Excel 97-2003 binary formats (xls), Excel 2007-2021 XML formats (xlsx).

If you feel you should have more than the provided functionality, perhaps you should ask the author politely.

I have some idea of the effort, time  and knowledge writing just a module like this takes and I think the author deserves a little more respect that you've shown.

Regards,


« Last Edit: November 26, 2023, 03:47:44 PM by kdub_nz »
Called Kerry in my other life
Retired; but they dragged me back in !

I live at UTC + 13.00

---
some people complain about loading the dishwasher.
Sometimes the question is more important than the answer.