-
Notifications
You must be signed in to change notification settings - Fork 8
Expand file tree
/
Copy pathDBFXLStools2.py
More file actions
1604 lines (1448 loc) · 70.8 KB
/
DBFXLStools2.py
File metadata and controls
1604 lines (1448 loc) · 70.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
# Copyright (C) 2010-2018 by M-P Systems Services, Inc.,
# PMB 136, 1631 NE Broadway, Portland, OR 97232.
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU Lesser General Public License as published by
# the Free Software Foundation, version 3 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
# or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General
# Public License for more details.
#
# You should have received a copy of the GNU Lesser General Public License
# along with this program. If not, see <https://www.gnu.org/licenses/>.
"""DBFtools.py created by M-P System Services, Inc. to provide access to the CodeBaseTools
and ExcelTools modules in an integrated package. """
# VERSION 2 that uses 'with' features for better exception handling
# Fix applied 07/16/2011. JSH: changed name of _setdbfstru to setdbfstru.
# 7/20/2011, JMc default to .dbf if no extension passed as VFP filename
# 04/30/2014 - JSH - Added code to terminate reading an Excel sheet when a completely blank row is encountered.
from __future__ import print_function, absolute_import
from datetime import date, datetime
import copy
from time import time
import os, tempfile
from CodeBaseTools import VFPFIELD, cbTools
from ExcelTools import *
import MPSSBaseTools as mTools
__author__ = "J. S. Heuer"
d2xConvertFrom = "NYCDTILMBFGZW"
d2xConvertTo = "NNSDDNLSNNXSX"
x2dConvert = {"N": "N", "S": "C", "L": "L", "D": "T", "X": "X", "B": "X", "E": "X"}
cErrorMessage = "" # Keeping some old code happy.
if sys.version_info[0] <= 2:
_ver3x = False
xLongType = long
else:
_ver3x = True
xLongType = int
def addExt(name, ext='dbf'):
n, e = os.path.splitext(name)
if e != "":
return name
return name + "." + ext
class DbfXlsEngine(object):
"""
DbfXlsEngine provides a suite of methods to copy data from DBF tables to Excel files and the reverse. It requires
the installation of the ExcelTools module and the CodeBaseTools module. Note that the ExcelTools module requires
a licensed version of LibXL and the LibXLWrapper.dll. LibXLWrapper.dll is an open source module that is part
of the ExcelTools distribution, but LibXL is a commercial product requiring a user name and license code to
be stored in a libXLLicenseInfo.txt file. See the documentation for ExcelTools for more details.
The normal usage for this object is to pass it an object reference to an instance of cbTools() from the
CodeBaseTools module:
oEngine = DbfXlsEngine(cbTools())
But, if no cbTools instance is supplied, one will be created if possible, providing this is invoked via the
"with" mechanism. See the dtTest() function for an example of this usage.
"""
def __init__(self, oVFP=None):
global d2xConvertFrom
global d2xConvertTo
global x2dConvert
self.d2xconvertFrom = d2xConvertFrom
self.d2xconvertTo = d2xConvertTo
self.cSheetName = ""
self.x2dconvert = x2dConvert
self.cErrorMessage = ""
self.nErrorNumber = 0
self.cOutputFieldList = "" # Set either externally or via the parm, to limit fields output. Comma
# delimited list of field names
self.xlmgr = None
self.dbfmgr = oVFP
self.xWidthList = None # Python list() of widths for each column. bWidthPixels determines the width metric.
self.bWidthPixels = False # Set to False for values in xWidthList being in Excel column width units. If
# True, assumes the width units are in pixels. Pixel values must be integers, but Excel width units may
# be floating point values.
# An Excel column width unit is approximately 8.4 pixels as of version 2007.
self.cCellDelim = "\a\v\a"
self.bFormatDecimals = False # if set to True, then when converting from DBF to XLS will look for
# number type fields with 2 decimal points (usually money fields) and output them with the special
# format NUMFORMAT_NUMBER_SEP_D2.
self.xDefaultValues = None # Will be a dict() with fieldname:default value pairs. Any fieldname
# not found in the source XLS will be set to the default value in the output DBF.
self.bForceDefault = False # Set this to True if you want the default values to override anything in
# the current table fields.
self.lAppendSheet = False # If True, then will attempt to append the sheet to the end of the sheets
# collection if the file already exists.
self.cHiddenCellValue = None # Calls for writing something hidden into one row/col. Set font color white.
self.nHiddenCellRow = None
self.nHiddenCellCol = None
self.cLastExcelName = ""
self.bXLSXmode = False # XLS files are faster to output.
def __enter__(self):
if self.dbfmgr is None:
self.dbfmgr = cbTools()
self.xlmgr=xlWrap()
self.xlmgr.SetCellDelimiter("\a\v\a") # Bell, VTab, Bell
self.cCellDelim = "\a\v\a"
return self
def makeHeaderFormat(self):
"""
Format creator for the column header cells containing the field names.
:return: Format Object
"""
lpFmat = XLFORMAT()
lpFmat.nBorderTop = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderLeft = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderRight = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderBottom = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nAlignV = xlAlignV["ALIGNV_TOP"]
lpFmat.bBold = 1
lpFmat.cFontName = b"Arial"
lpFmat.nColor = xlColor["COLOR_BLACK"]
lpFmat.nWordWrap = 1
lpFmat.nPointSize = 10 # Changed to 10 points from previous 11, which is too big. 01/04/2013. JSH.
return copy.copy(lpFmat)
def makeHeaderFormatXLSX(self):
"""
Format creator for the column header cells containing the field names. Uses default fonts from
XLSX type files.
:return: Format Object
"""
lpFmat = XLFORMAT()
lpFmat.nBorderTop = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderLeft = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderRight = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderBottom = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nAlignV = xlAlignV["ALIGNV_TOP"]
lpFmat.bBold = 1
lpFmat.cFontName = b"Calibri"
lpFmat.nColor = xlColor["COLOR_BLACK"]
lpFmat.nWordWrap = 1
lpFmat.nPointSize = 10 # Changed to 10 points from previous 11, which is too big. 01/04/2013. JSH.
return copy.copy(lpFmat)
def makeBodyHiddenFormat(self):
"""
Format creator for the cells in a hidden row. The hidden row option allows the raw DBF field names to
be included in the XLS file without actually displaying them -- the column headings can then display
human readable names.
:return: Format Object
"""
lpFmat = XLFORMAT()
lpFmat.nBorderTop = xlBorder["BORDERSTYLE_NONE"]
lpFmat.nBorderLeft = xlBorder["BORDERSTYLE_NONE"]
lpFmat.nBorderRight = xlBorder["BORDERSTYLE_NONE"]
lpFmat.nBorderBottom = xlBorder["BORDERSTYLE_NONE"]
lpFmat.nAlignV = xlAlignV["ALIGNV_TOP"]
lpFmat.bBold = 1
lpFmat.cFontName = b"Arial"
lpFmat.nColor = xlColor["COLOR_WHITE"]
lpFmat.nWordWrap = 0
lpFmat.nPointSize = 10
return copy.copy(lpFmat)
def makeTitleFormat(self):
"""
Format creator for the top-of-the-page title if one is to be displayed.
:return: Format Object
"""
lpFmat = XLFORMAT()
lpFmat.nBorderTop = xlBorder["BORDERSTYLE_NONE"]
lpFmat.nBorderLeft = xlBorder["BORDERSTYLE_NONE"]
lpFmat.nBorderRight = xlBorder["BORDERSTYLE_NONE"]
lpFmat.nBorderBottom = xlBorder["BORDERSTYLE_NONE"]
lpFmat.nAlignV = xlAlignV["ALIGNV_TOP"]
lpFmat.bBold = 1
lpFmat.cFontName = b"Arial"
lpFmat.nColor = xlColor["COLOR_BLACK"]
lpFmat.nWordWrap = 0
lpFmat.nPointSize = 12
return copy.copy(lpFmat)
def makeBodyFormat(self):
"""
Format creator for the standard body cells containing data displayed in text format (including dates)
:return: Format Object
"""
lpFmat = XLFORMAT()
lpFmat.nBorderTop = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderLeft = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderRight = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderBottom = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nAlignV = xlAlignV["ALIGNV_TOP"]
lpFmat.nWordWrap = 1
return copy.copy(lpFmat)
def makeBodyDecimalFormat(self):
"""
Format creator for the standard body cells containing data in numeric format. Defaults to 2 decimal places
and the standard thousands separator character.
:return: Format Object
"""
lpFmat = XLFORMAT()
lpFmat.nBorderTop = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderLeft = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderRight = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderBottom = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nAlignV = xlAlignV["ALIGNV_TOP"]
lpFmat.nWordWrap = 1
lpFmat.nNumFormat = xlNumFormat["NUMFORMAT_NUMBER_SEP_D2"]
return copy.copy(lpFmat)
def makeBodyFormatXLSX(self):
"""
Format creator for the standard body cells containing data displayed in text format (including dates).
This version uses font types standard for XLSX files.
:return: Format Object
"""
lpFmat = XLFORMAT()
lpFmat.nBorderTop = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderLeft = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderRight = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderBottom = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nAlignV = xlAlignV["ALIGNV_TOP"]
lpFmat.nWordWrap = 1
lpFmat.bBold = 0
lpFmat.cFontName = b"Calibri"
lpFmat.nColor = xlColor["COLOR_BLACK"]
lpFmat.nPointSize = 11
return copy.copy(lpFmat)
def makeBodyDecimalFormatXLSX(self):
"""
Format creator for the standard body cells containing data in numeric format. Defaults to 2 decimal places
and the standard thousands separator character. This version uses font types standard for XLSX files.
:return: Format Object
"""
lpFmat = XLFORMAT()
lpFmat.nBorderTop = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderLeft = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderRight = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nBorderBottom = xlBorder["BORDERSTYLE_THIN"]
lpFmat.nAlignV = xlAlignV["ALIGNV_TOP"]
lpFmat.nWordWrap = 1
lpFmat.bBold = 0
lpFmat.cFontName = b"Calibri"
lpFmat.nColor = xlColor["COLOR_BLACK"]
lpFmat.nPointSize = 11
lpFmat.nNumFormat = xlNumFormat["NUMFORMAT_NUMBER_SEP_D2"]
return copy.copy(lpFmat)
def calcColumnWidth(self, lcType, lnWidth, lnOverride=0.0):
if lnOverride != 0.0:
return lnOverride / (8.4 if self.bWidthPixels else 1.0)
lnWidthRet = 12.0
if (lcType == "C") or (lcType == "N") or (lcType == "F"):
lnWidthRet = lnWidth + 2
if lnWidthRet > 90:
lnWidthRet = 90
elif lcType == "Y":
lnWidthRet = 13.0
elif lcType == "I":
lnWidthRet = 12.0
elif lcType == "T":
lnWidthRet = 16.0
elif lcType == "D":
lnWidthRet = 12.0
elif lcType == "L":
lnWidthRet = 12.0
elif lcType == "M":
lnWidthRet = 40.0
elif lcType == "B":
lnWidthRet = 13.0
if lnWidthRet < 8:
lnWidthRet = 8
if not self.bXLSXmode:
lnWidthRet += 1
return lnWidthRet
def bizobj2excel(self, oBiz=None, cExcelName="", xHeaderList=None, cTitleOne="", cTitleTwo="", bHiddenNames=False,
bNoClose=False, cFieldList="", cForExpr="", cSortOrder=""):
"""
Similar to dbf2excel except that takes as input a fully configured biz object with a table attached. This
can be a Biz Object for either a DBF or a MongoDB table. This pulls all the data out with a copytoarray()
call, then stringifies the data for storing into the cells and outputs the specified Excel. Outputs ONLY
.XLSX files, although that may be slower than .XLS.
:param oBiz: Object reference to the BizObject which will be the source of the records
:param cExcelName: Fully qualified path name for the Excel output file. Will be forced to ext of .XLSX
:param xHeaderList: List() object with one text string for each column of output
:param cTitleOne: Optional title for first row
:param cTitleTwo: Optional title for second row
:param bHiddenNames: If True, then we'll output a hidden row with the field names directly after the title row.
:param bNoClose: Ignored
:param cFieldList: Comma separated list of field names that will be output and in the order listed.
:param cForExpr: If not all records are to be output, specify a legal "for" expression here. This will be
passed to the Biz Object to restrict the list of records in the copytoarray() call
:param cSortOrder: Pass the name of a DBF index TAG or the name of a MongoDB field to control the sort order.
:return: True on Success, otherwise False, and set the error message.
"""
def convString(cValue=""):
return cValue
def convNumber(nValue=0):
return str(nValue).strip()
def convDate(xValue=None):
if xValue is None:
return ""
else:
return ""
def convBool(bValue=False):
if bValue:
return "TRUE"
else:
return "FALSE"
def convOther(xValue=None):
return str(xValue).strip()
lbReturn = True
if not oBiz or oBiz is None:
raise ValueError("Bad BizObj value")
if not oBiz.bTableOpen:
bTest = oBiz.reattachtable()
if not bTest:
self.cErrorMessage = "Table is not open..."
return False
bMongoMode = oBiz.bMongoMode
if oBiz.nMaxQueryCount > 100000:
oBiz.nMaxQueryCount = 100000 # Won't output more than 100,000 Excel rows with this routine due to
# time and memory limits in this version.
nCurRow = 0
nColWidthOverride = 0.0
nColCount = 0
aRawFlds = oBiz.xFieldList
if cFieldList:
self.cOutputFieldList = cFieldList.strip()
else:
self.cOutputFieldList.strip()
if self.cOutputFieldList:
# first produce a list of fields containing only fields which are in the table
# and which are in the cOutputFieldList.
aNewFlds = list()
self.cOutputFieldList = self.cOutputFieldList.upper()
xFlds = self.cOutputFieldList.split(",")
xOrder = dict()
nNewPos = 0
for lf in aRawFlds:
cTest = lf.cName.upper()
if cTest in xFlds:
aNewFlds.append(lf)
xOrder[cTest] = nNewPos
nNewPos += 1
nFldCnt = len(aNewFlds)
if nFldCnt == 0:
self.cErrorMessage = "Table fields don't match supplied field list"
oBiz.detachtable()
return False
aFlds = list()
for cN in xFlds:
nPos = xOrder.get(cN, -1)
if nPos != -1:
aFlds.append(aNewFlds[nPos])
# now aFlds should be ordered in the same sequence as the fieldList.
else:
aFlds = copy.copy(aRawFlds)
fList = list()
cTypes = ""
cHdr = ""
cHdrTypes = ""
cFldNames = ""
cWorkExcelName = cExcelName
nFldCnt = 0
dList = list() # Where we store the special formats for each column, if any.
bFoundDecimals = False
convList = list()
xFldList = list()
xColList = list()
for lf in aFlds:
cType = lf.cType
if cType is None:
cType = "_" # Probably the _id field in a MongoDB table.
if cType in self.d2xconvertFrom:
nPt = self.d2xconvertFrom.index(cType)
cTest = self.d2xconvertTo[nPt: nPt + 1]
else:
cTest = "X"
if cTest != "X":
if cType in "MC":
convList.append(convString)
elif cType == "L":
convList.append(convBool)
elif cType in "NBYIF":
convList.append(convNumber)
elif cType in "DT":
convList.append(convDate)
else:
convList.append(convOther)
fList.append(lf.cName)
xColList.append(lf)
cTypes = cTypes + cTest
cHdr = cHdr + lf.cName + self.cCellDelim
cFldNames = cFldNames + lf.cName + self.cCellDelim # In case we need to save a separate hidden
# field name row.
cHdrTypes = cHdrTypes + "S"
nColCount += 1
nFldCnt += 1
cWorkFieldList = ",".join(fList)
cWorkFieldList = cWorkFieldList.upper()
if xHeaderList is not None:
if isinstance(xHeaderList, list):
cHdr = self.cCellDelim.join(xHeaderList)
else:
cHdr = cHdr.lower()
if len(fList) > 0:
# They have at least one valid field to output
if bMongoMode:
cWorkFieldList = cWorkFieldList.lower()
if mTools.isstr(cSortOrder) and cSortOrder:
xSortRequested = [(cSortOrder, '1')]
else: # Hope for the best
xSortRequested = cSortOrder # Must be a list of ordering tuples. See the MongoDB biz object docs.
else:
xSortRequested = cSortOrder
if not xSortRequested:
xSortRequested = None
xRecs = oBiz.copytoarray(maxcount=-1, cExpr=cForExpr, cFieldList=cWorkFieldList, xSortInfo=xSortRequested)
if xRecs is None:
self.cErrorMessage = "Unable to get table records: " + oBiz.cErrorMessage
self.cLastExcelName = ""
else:
lnRecs = len(xRecs)
bXLSXmode = False
self.bXLSXmode = False
if lnRecs < 64000:
xParts = os.path.splitext(cWorkExcelName)
cExt = xParts[1].upper()
if cExt == ".XLSX": # Do what they tell us, if they specify XLSX
bXLSXmode = True
self.bXLSXmode = True
else:
cWorkExcelName = mTools.FORCEEXT(cWorkExcelName, "XLS")
else:
# Too big for XLS format, so we force to XLSX
cWorkExcelName = mTools.FORCEEXT(cWorkExcelName, "XLSX")
bXLSXmode = True
self.bXLSXmode = True
if self.cSheetName:
cSheetName = self.cSheetName
else:
cSheetName = mTools.JUSTSTEM(oBiz.cTableName)
if self.lAppendSheet and os.path.exists(cExcelName):
nSheetCount = self.xlmgr.OpenWorkbook(cExcelName)
nSheet = self.xlmgr.AddSheet(cSheetName)
else:
self.xlmgr.CreateWorkbook(cExcelName, cSheetName)
nSheetCount = 1
nSheet = 0
if bXLSXmode:
xFmatT = self.makeTitleFormat()
xFmatH = self.makeHeaderFormat()
xFmatT.cFontName = b"Calibri"
xFmatH.cFontName = b"Calibri"
xFmatB = self.makeBodyFormatXLSX()
xFmatD = self.makeBodyDecimalFormatXLSX()
else:
xFmatB = self.makeBodyFormat()
xFmatT = self.makeTitleFormat()
xFmatH = self.makeHeaderFormat()
xFmatD = self.makeBodyDecimalFormat()
nBodyFmat = self.xlmgr.AddFormat(xFmatB)
nTitleFmat = self.xlmgr.AddFormat(xFmatT)
nHdrFmat = self.xlmgr.AddFormat(xFmatH)
nDecFmat = 0
if self.bFormatDecimals:
nDecFmat = self.xlmgr.AddFormat(xFmatD)
xFmatHide = self.makeBodyHiddenFormat()
nHiddenFmat = self.xlmgr.AddFormat(xFmatHide)
for jj, lf in enumerate(xColList):
nColWidthOverride = 0.0
if self.xWidthList is not None:
try:
nColWidthOverride = float(self.xWidthList[jj])
except:
nColWidthOverride = 0.0
if nColWidthOverride >= 0.0:
nTestWidth = lf.nWidth
nNameWidth = len(lf.cName)
if nNameWidth > nTestWidth:
nTestWidth = nNameWidth
self.xlmgr.SetColWidths(nSheet, jj, jj, self.calcColumnWidth(lf.cType, nTestWidth,
lnOverride=nColWidthOverride))
else:
self.xlmgr.SetColHidden(nSheet, nColCount, True)
if self.bFormatDecimals and (cType == "N") and (lf.nDecimals == 2):
dList.append(nDecFmat)
bFoundDecimals = True
else:
dList.append(None)
if cTitleOne != "":
self.xlmgr.WriteCellValue(nSheet, 0, 0, cTitleOne, "S")
self.xlmgr.FormatCells(nSheet, 0, 1, 0, 1, nTitleFmat)
nCurRow += 1
if cTitleTwo != "":
self.xlmgr.WriteCellValue(nSheet, nCurRow, 0, cTitleTwo, "S")
self.xlmgr.FormatCells(nSheet, nCurRow, nCurRow + 1, 0, 1, nTitleFmat)
nCurRow += 1
self.xlmgr.WriteRowValues(nSheet, nCurRow, 0, nColCount - 1, cHdr, cHdrTypes)
self.xlmgr.FormatCells(nSheet, nCurRow, nCurRow + 1, 0, nColCount, nHdrFmat)
nCurRow += 1
if bHiddenNames:
cFldNames = cFldNames.upper()
self.xlmgr.WriteRowValues(nSheet, nCurRow, 0, nColCount - 1, cFldNames, cHdrTypes)
self.xlmgr.SetRowHidden(nSheet, nCurRow, True)
nCurRow += 1
for valDict in xRecs:
xRow = list()
for ii, lv in enumerate(fList):
cItem = str(convList[ii](valDict[lv]))
xRow.append(cItem)
cRow = self.cCellDelim.join(xRow)
self.xlmgr.WriteRowValues(nSheet, nCurRow, 0, nColCount, cRow, cTypes)
if self.bFormatDecimals:
for nn in range(0, nColCount):
if not (dList[nn] is None):
self.xlmgr.FormatCells(nSheet, nCurRow, nCurRow + 1, nn, nn + 1, dList[nn])
nCurRow += 1
if self.cHiddenCellValue is not None:
self.xlmgr.WriteCellValue(nSheet, self.nHiddenCellRow, self.nHiddenCellCol,
self.cHiddenCellValue, "S")
self.xlmgr.FormatCells(nSheet, self.nHiddenCellRow, self.nHiddenCellRow + 1, self.nHiddenCellCol,
self.nHiddenCellCol + 1, nHiddenFmat)
self.xlmgr.CloseWorkbook(1)
self.cLastExcelName = cWorkExcelName
else:
lbReturn = False
self.cErrorMessage = "No fields found to output"
oBiz.detachtable()
self.xlmgr.CloseWorkbook(0)
self.cLastExcelName = ""
return lbReturn
def dbf2excel(self, lcDbfName, lcExcelName, headerlist=None, titleone="", titletwo="", rawcopy=False,
hiddenNames=False, noclose=False, fieldList=""):
"""
Copies the contents of the specific DBF file into the spreadsheet named lcExcelName.
If the spreadsheet already exists, it is overwritten. Otherwise it is created. There
will be a header row containing field names and one row following that for each table
record (row).
If headerlist is of type list(), it should contain one text value for each column in the table/sheet.
These text values will replace the field names in the top row. (In that case, the output can't be used
for import back into dbf via excel2dbf().
If titleone and/or titletwo are non-empty strings, they will be placed in A1 and A2 respectively
in 12 point Bold, and all content will be moved down accordingly.
Data table fields will be properly converted to Excel value types.
Fields such as General will not be converted. Memo will be included.
If you are confident you know that all fields in the table are convertable to Excel types (for example
that there are no "General" or "Memo-Binary" fields in the table), then you can use the rawcopy option
which does no type or content checking other than the standard conversions from VFP to Excel formats
for dates, etc. Pass the optional parameter rawcopy=True in that case.
Table ordering is by record.
If hiddenNames == True, then inserts a row directly below the title row (either containing the
field names or the values in headerlist) which will be filled with the field names AND be set to
hidden.
Returns True on success. False on Failure.
"""
lbReturn = True
lnResult = self.dbfmgr.use(addExt(lcDbfName), readOnly=True)
lcLoadAlias = self.dbfmgr.alias()
lnCurRow = 0
lnColWidthOverride = 0.0
if lnResult == 1:
if self.cSheetName:
lcSheetName = self.cSheetName
else:
lcSheetName = self.dbfmgr.alias()
if self.lAppendSheet and os.path.exists(lcExcelName):
lnSheetCount = self.xlmgr.OpenWorkbook(lcExcelName)
lnSheet = self.xlmgr.AddSheet(lcSheetName)
else:
self.xlmgr.CreateWorkbook(lcExcelName, lcSheetName)
lnSheetCount = 1
lnSheet = 0
lnColCount = 0
laRawFlds = self.dbfmgr.afields()
if fieldList:
self.cOutputFieldList = fieldList.strip()
else:
self.cOutputFieldList.strip()
if self.cOutputFieldList:
# first produce a list of fields containing only fields which are in the table
# and which are in the cOutputFieldList.
aNewFlds = list()
xFlds = self.cOutputFieldList.split(",")
xOrder = dict()
nNewPos = 0
for lf in laRawFlds:
cTest = lf.cName.upper()
if cTest in xFlds:
aNewFlds.append(lf)
xOrder[cTest] = nNewPos
nNewPos += 1
nFldCnt = len(aNewFlds)
if nFldCnt == 0:
self.cErrorMessage = "Table fields don't match supplied field list"
self.dbfmgr.closetable(lcLoadAlias)
return False
laFlds = list()
for cN in xFlds:
nPos = xOrder.get(cN, -1)
if nPos != -1:
laFlds.append(aNewFlds[nPos])
# now laFlds should be ordered in the same sequence as the fieldList.
else:
laFlds = copy.copy(laRawFlds)
if ".XLSX" in lcExcelName.upper():
xFmatT = self.makeTitleFormat()
xFmatH = self.makeHeaderFormat()
xFmatT.cFontName = b"Calibri"
xFmatH.cFontName = b"Calibri"
xFmatB = self.makeBodyFormatXLSX()
xFmatD = self.makeBodyDecimalFormatXLSX()
else:
xFmatB = self.makeBodyFormat()
xFmatT = self.makeTitleFormat()
xFmatH = self.makeHeaderFormat()
xFmatD = self.makeBodyDecimalFormat()
lnBodyFmat = self.xlmgr.AddFormat(xFmatB)
lnTitleFmat = self.xlmgr.AddFormat(xFmatT)
lnHdrFmat = self.xlmgr.AddFormat(xFmatH)
lnDecFmat = 0
if self.bFormatDecimals:
lnDecFmat = self.xlmgr.AddFormat(xFmatD)
xFmatHide = self.makeBodyHiddenFormat()
lnHiddenFmat = self.xlmgr.AddFormat(xFmatHide)
lfList = list()
ltList = list()
lcTypes = ""
lcHdr = ""
lcHdrTypes = ""
lcFldNames = ""
lnFldCnt = 0
ldList = list() # Where we store the special formats for each column, if any.
lbFoundDecimals = False
for lf in laFlds:
if lf.cType in self.d2xconvertFrom:
lnPt = self.d2xconvertFrom.index(lf.cType)
lcTest = self.d2xconvertTo[lnPt: lnPt + 1]
else:
lcTest = "X"
if lcTest != "X":
lfList.append(lf.cName)
lcTypes = lcTypes + lcTest
lcHdr = lcHdr + lf.cName + self.cCellDelim
lcFldNames = lcFldNames + lf.cName + self.cCellDelim # In case we need to save a separate hidden
# field name row.
lcHdrTypes = lcHdrTypes + "S"
if self.xWidthList is not None:
try:
lnColWidthOverride = float(self.xWidthList[lnFldCnt])
except:
lnColWidthOverride = 0.0
if lnColWidthOverride >= 0.0:
self.xlmgr.SetColWidths(lnSheet, lnColCount, lnColCount,
self.calcColumnWidth(lf.cType, lf.nWidth,
lnOverride=lnColWidthOverride))
else:
self.xlmgr.SetColHidden(lnSheet, lnColCount, True)
if self.bFormatDecimals and (lf.cType == "N") and (lf.nDecimals == 2):
ldList.append(lnDecFmat)
lbFoundDecimals = True
else:
ldList.append(None)
lnColCount += 1
lnFldCnt += 1
if headerlist is not None:
if isinstance(headerlist, list):
lcHdr = self.cCellDelim.join(headerlist)
else:
lcHdr = lcHdr.lower()
if len(lfList) > 0:
# They have at least one valid field to output
self.dbfmgr.goto("TOP")
lnRecs = self.dbfmgr.reccount()
if titleone != "":
self.xlmgr.WriteCellValue(lnSheet, 0, 0, titleone, "S")
self.xlmgr.FormatCells(lnSheet, 0, 1, 0, 1, lnTitleFmat)
lnCurRow += 1
if titletwo != "":
self.xlmgr.WriteCellValue(lnSheet, lnCurRow, 0, titletwo, "S")
self.xlmgr.FormatCells(lnSheet, lnCurRow, lnCurRow + 1, 0, 1, lnTitleFmat)
lnCurRow += 1
self.xlmgr.WriteRowValues(lnSheet, lnCurRow, 0, lnColCount - 1, lcHdr, lcHdrTypes)
self.xlmgr.FormatCells(lnSheet, lnCurRow, lnCurRow + 1, 0, lnColCount, lnHdrFmat)
lnCurRow += 1
if hiddenNames:
lcFldNames = lcFldNames.upper()
self.xlmgr.WriteRowValues(lnSheet, lnCurRow, 0, lnColCount - 1, lcFldNames, lcHdrTypes)
self.xlmgr.SetRowHidden(lnSheet, lnCurRow, True)
lnCurRow += 1
if not rawcopy: # Apply type checking to eliminate fields that we can't convert.
self.dbfmgr.select(lcLoadAlias)
self.dbfmgr.goto("TOP")
for jj in range(0, lnRecs):
if (not self.dbfmgr.eof()) and (not self.dbfmgr.deleted()):
valDict = self.dbfmgr.scatter(alias=lcLoadAlias, converttypes=False, stripblanks=True)
if valDict is None:
lbReturn = False
self.cErrorMessage = self.dbfmgr.cErrorMessage
self.xlmgr.CloseWorkbook(0)
# Serious crash so close it all.
# self.dbfmgr.closedatabases()
self.dbfmgr.closetable(lcLoadAlias)
return lbReturn
xRow = list()
for lv in lfList:
xRow.append(valDict[lv].strip("\x00 "))
lcRow = self.cCellDelim.join(xRow)
self.xlmgr.WriteRowValues(lnSheet, lnCurRow, 0, lnColCount, lcRow, lcTypes)
if self.bFormatDecimals:
for nn in range(0, lnColCount):
if not (ldList[nn] is None):
self.xlmgr.FormatCells(lnSheet, lnCurRow, lnCurRow + 1, nn, nn + 1, ldList[nn])
lnCurRow += 1
self.dbfmgr.goto("NEXT")
valDict = None
if self.dbfmgr.eof():
break
else: # Copy the field data directly into the spreadsheet for maximum speed
for jj in range(0, lnRecs):
if not self.dbfmgr.deleted():
lcRow = self.dbfmgr.scatterraw(converttypes=False, stripblanks=True,
lcDelimiter=self.cCellDelim)
if lcRow is not None:
self.xlmgr.WriteRowValues(lnSheet, lnCurRow, 0, lnColCount, lcRow, lcTypes)
lnCurRow += 1
self.dbfmgr.goto("NEXT")
lcRow = None
if not (self.cHiddenCellValue is None):
self.xlmgr.WriteCellValue(lnSheet, self.nHiddenCellRow, self.nHiddenCellCol, self.cHiddenCellValue, "S")
self.xlmgr.FormatCells(lnSheet, self.nHiddenCellRow, self.nHiddenCellRow + 1, self.nHiddenCellCol, self.nHiddenCellCol + 1, lnHiddenFmat)
self.xlmgr.CloseWorkbook(1)
self.dbfmgr.closetable(lcLoadAlias)
else:
lbReturn = False
cErrorMessage = self.dbfmgr.cErrorMessage
self.cErrorMessage = cErrorMessage
self.xlmgr.CloseWorkbook(0)
# Serious crash so close it all.
self.dbfmgr.closetable(lcLoadAlias)
return lbReturn
def setdbfstru(self, lcFieldVals, lcFieldTypes, lxDbfTypes, lxDbfWidths, lxDbfDecimals):
lxVals = lcFieldVals.split(self.cCellDelim)
lcVal = ""
lnCnt = len(lcFieldTypes)
for jj in range(0, lnCnt):
lcXType = lcFieldTypes[jj]
if (lcXType == "X") or (lcXType == "E") or (lcXType == "B"):
continue # can't get any information from this cell.
if lcXType == "S": # string
lnTestWidth = len(lxVals[jj])
lcVal = lxVals[jj]
if (lcVal == "TRUE") or (lcVal == "FALSE"):
if lxDbfTypes[jj] == "X":
lxDbfTypes[jj] = "L"
lxDbfWidths[jj] = 1
else:
lxDbfTypes[jj] = "C"
if lnTestWidth == 0:
lnTestWidth = 1
if lnTestWidth > lxDbfWidths[jj]:
lxDbfWidths[jj] = lnTestWidth
if lxDbfWidths[jj] > 254:
lxDbfWidths[jj] = 10
lxDbfTypes[jj] = "M" # Memo for big strings.
elif lcXType == "N": # Number
lcVal = lxVals[jj]
lxParts = lcVal.split('.')
lcBase = lxParts[0]
lcDec = lxParts[1]
lcDec = lcDec.rstrip('0') # Get rid of useless trailing zeros.
if len(lcDec) > lxDbfDecimals[jj]:
lxDbfDecimals[jj] = len(lcDec)
if (lxDbfTypes[jj] == "X") and (lxDbfDecimals[jj] == 0):
lxDbfTypes[jj] = "I"
lxDbfWidths[jj] = 4
elif (lxDbfTypes[jj] == "X") and (lxDbfDecimals[jj] > 0):
lxDbfTypes[jj] = "N"
elif (lxDbfTypes[jj] == "I") and (lxDbfDecimals[jj] > 0):
lxDbfTypes[jj] = "N"
elif lxDbfTypes[jj] in "CLDT": # already something else so we make it 'C'
lxDbfTypes[jj] = 'C'
lxDbfDecimals[jj] = 0
lxDbfWidths[jj] = 20 # will hold something or another.
break
lnTestWidth = len(lcDec) + lxDbfDecimals[jj] + 1
if (lxDbfTypes[jj] == "N") and (lnTestWidth > lxDbfWidths[jj]):
lxDbfWidths[jj] = lnTestWidth
if lxDbfWidths[jj] < 10:
lxDbfWidths[jj] = 10 # nothing narrower than this for a number by default.
elif lcXType == "L":
if lxDbfTypes[jj] == "X":
lxDbfTypes[jj] = "L"
lxDbfWidths[jj] = 1
lxDbfDecimals[jj] = 0
elif lxDbfTypes[jj] in "CNTDI":
lxDbfTypes[jj] = "C"
lxDbfWidths[jj] = 20
lxDbfDecimals[jj] = 0
elif lcXType == "D":
if lcVal == "00000000000000000":
# this is an empty datetime, so just break
break
lcVal = lxVals[jj]
if lcVal[0:8] != "00000000":
lxDbfTypes[jj] = "D"
lxDbfWidths[jj] = 8
lxDbfDecimals[jj] = 0
if lcVal[8:99] != "000000000":
# this is a datetime cause it has a time component.
if lxDbfTypes[jj] == "D":
lxDbfTypes[jj] = "T" # Change the type to a datetime
elif lxDbfTypes[jj] == "X":
lxDbfTypes[jj] = "C" # Just a time, which we'll handle as an 8 character string
lxDbfWidths[jj] = 8
lxDbfDecimals[jj] = 0
else:
xyx = True # nothing.
def excel2dbf(self, lcExcelName, lcDbfName, sheetname="", appendflag=False, templatedbf="", fieldlist=None,
columnsonly=False, showErrors=False):
"""
Writes the contents of the specified Excel file (xls or xlsx) into the target DBF type table. The existence of the
lcDbfName table and the appendflag value determine the action taken:
lcDbfName exists, appendflag = False:
- lcDbfName records are removed (zapped), all records are replaced with newly imported records. Any value in templatedbf
is ingnored.
lcDbfName exists, appendflag = True:
- Records from the spreadsheet are appended into the table after the existing records. Any value in templatedbf
is ignored.
lcDbfName doesn't exist -- appendflag has no meaning:
If templatedbf contains the name of a valid DBF table, then uses the structure of that table to create a new table
and imports data into it.
If templatedbf == "" or None, then inspects the contents of the spreadsheet and creates a table the best way it can
from the cell information.
Takes the data from the first worksheet in the workbook. You can pass a worksheet name to use instead in the
sheetname optional parameter. Note that in Excel, worksheet names are case sensitive.
If the templatedbf parameter contains the name of an existing DBF type table, then appendflag is ignored and
a new table is created (overwriting any existing one by that name) using the identical structure of the template
DBF table.
You can supply a list() of field names to include in the output DBF table. In that case, the fields in the output
table consist of those that are BOTH in the spreadsheet AND in the fieldlist. If appendflag == True, the fieldlist
will be respected -- records will be appended to the lcDbfName table, but only the fields specified will be populated.
The first row of the spreadsheet must contain text values that are valid field names. No more than 255 are permitted.
Field names may be upper or lower case but MUST start with a letter or underscore. They may be no longer than 10 characters
and may NOT include blanks. No two field names may be the same. Any violation of these rules will result in an error flag
being returned.
Returns the number of records stored or appended. Returns -1 on error, however if 0 is returned, you likely should investigate
the source of the problem.
NOTE: The CodeBase utilities are fairly slow in creating new DBF tables. Not sure why. At least the first one they create
seems to take around 0.45 seconds on a very fast machine. The second and third are faster, however. So... for simple things
just let this method write into an existing table, for much better performance.
"""
lbDBFexists = False
lbSetDefaults = False
cDelimString = "/a!~!~/a" # Added 08/07/2013 so as to handle case where ~~ may be contained in a field,
# which is the CBTools
# default delimiter. JSH.
self.cErrorMessage = ""
self.nErrorNumber = 0
lnResult = self.xlmgr.OpenWorkbook(lcExcelName)
if lnResult < 1:
self.cErrorMessage = "Can't open Excel File: %s because - "%(lcExcelName,) + self.xlmgr.cErrorMessage
self.nErrorNumber = self.xlmgr.nErrorNumber
if showErrors:
print(self.cErrorMessage)
return -1
if sheetname != "":
lnSheet = self.xlmgr.GetSheetFromName(sheetname)
else:
lnSheet = 0
if lnSheet == -1:
self.cErrorMessage = "No Active Sheet found"
self.xlmgr.CloseWorkbook(0)
if showErrors:
print(self.cErrorMessage)
return -1
lcSheetName, lnFromRow, lnToRow, lnFromCol, lnToCol = self.xlmgr.GetSheetStats(lnSheet)
xHdrVals, xHdrTypes = self.xlmgr.GetRowValues(lnSheet, 0, lnFromCol, lnToCol)
lbBadType = False
lnColCnt = 0
for jj in range(0, (lnToCol + 1)):
if xHdrTypes[jj] != "S":
break
else:
if xHdrVals[jj] == "":
break
lnColCnt += 1
if lnColCnt < 1:
lbBadType = True
else:
lnToCol = lnColCnt - 1 # A zero based column numbering system.
if lnColCnt > 254:
self.cErrorMessage = "Too many fields for a DBF table"
self.xlmgr.CloseWorkbook(0)
if showErrors:
print(self.cErrorMessage)
return -1
if lbBadType:
self.cErrorMessage = "Bad Column Cell Types: " + xHdrTypes
self.xlmgr.CloseWorkbook(0)
if showErrors:
print(self.cErrorMessage)
return -1
xHdrTypes = xHdrTypes[0:lnColCnt]
lxFieldNames = xHdrVals.split(self.cCellDelim) # This exotic string is the standard delimiter
# for the xls tools module.
lnLimit = lnColCnt
if lnLimit > len(lxFieldNames):
lnLimit = len(lxFieldNames)
lxFieldNames = lxFieldNames[0: lnColCnt]
if columnsonly:
self.xlmgr.CloseWorkbook(0)
return lxFieldNames
if not (self.xDefaultValues is None):
for xFld in lxFieldNames:
cTest = xFld.upper()
if not self.bForceDefault:
if cTest in self.xDefaultValues:
# No need for this in the default list...
del self.xDefaultValues[cTest]
if len(self.xDefaultValues) > 0:
lbSetDefaults = True
lbMakeNewTable = True