Coverage for /var/srv/projects/api.amasfac.comuna18.com/tmp/venv/lib/python3.9/site-packages/tablib/formats/_xlsx.py: 20%

1""" Tablib - XLSX Support. 

2""" 

3 

4import re 

5from io import BytesIO 

6 

7from openpyxl.reader.excel import ExcelReader, load_workbook 

8from openpyxl.styles import Alignment, Font 

9from openpyxl.utils import get_column_letter 

10from openpyxl.workbook import Workbook 

11from openpyxl.writer.excel import ExcelWriter 

12 

13import tablib 

14 

15INVALID_TITLE_REGEX = re.compile(r'[\\*?:/\[\]]') 

16 

17def safe_xlsx_sheet_title(s, replace="-"): 

18 return re.sub(INVALID_TITLE_REGEX, replace, s)[:31] 

19 

20 

21class XLSXFormat: 

22 title = 'xlsx' 

23 extensions = ('xlsx',) 

24 

25 @classmethod 

26 def detect(cls, stream): 

27 """Returns True if given stream is a readable excel file.""" 

28 try: 

29 # No need to fully load the file, it should be enough to be able to 

30 # read the manifest. 

31 reader = ExcelReader(stream, read_only=False) 

32 reader.read_manifest() 

33 return True 

34 except Exception: 

35 return False 

36 

37 @classmethod 

38 def export_set(cls, dataset, freeze_panes=True, invalid_char_subst="-"): 

39 """Returns XLSX representation of Dataset. 

40 

41 If dataset.title contains characters which are considered invalid for an XLSX file 

42 sheet name (http://www.excelcodex.com/2012/06/worksheets-naming-conventions/), they will 

43 be replaced with `invalid_char_subst`. 

44 """ 

45 wb = Workbook() 

46 ws = wb.worksheets[0] 

47 

48 ws.title = ( 

49 safe_xlsx_sheet_title(dataset.title, invalid_char_subst) 

50 if dataset.title else 'Tablib Dataset' 

51 ) 

52 

53 cls.dset_sheet(dataset, ws, freeze_panes=freeze_panes) 

54 

55 stream = BytesIO() 

56 wb.save(stream) 

57 return stream.getvalue() 

58 

59 @classmethod 

60 def export_book(cls, databook, freeze_panes=True, invalid_char_subst="-"): 

61 """Returns XLSX representation of DataBook. 

62 

63 If dataset.title contains characters which are considered invalid for an XLSX file 

64 sheet name (http://www.excelcodex.com/2012/06/worksheets-naming-conventions/), they will 

65 be replaced with `invalid_char_subst`. 

66 """ 

67 

68 wb = Workbook() 

69 for sheet in wb.worksheets: 

70 wb.remove(sheet) 

71 for i, dset in enumerate(databook._datasets): 

72 ws = wb.create_sheet() 

73 ws.title = ( 

74 safe_xlsx_sheet_title(dset.title, invalid_char_subst) 

75 if dset.title else 'Sheet%s' % (i) 

76 ) 

77 

78 cls.dset_sheet(dset, ws, freeze_panes=freeze_panes) 

79 

80 stream = BytesIO() 

81 wb.save(stream) 

82 return stream.getvalue() 

83 

84 @classmethod 

85 def import_set(cls, dset, in_stream, headers=True, read_only=True, skip_lines=0): 

86 """Returns databook from XLS stream.""" 

87 

88 dset.wipe() 

89 

90 xls_book = load_workbook(in_stream, read_only=read_only, data_only=True) 

91 sheet = xls_book.active 

92 

93 dset.title = sheet.title 

94 

95 for i, row in enumerate(sheet.rows): 

96 if i < skip_lines: 

97 continue 

98 row_vals = [c.value for c in row] 

99 if i == skip_lines and headers: 

100 dset.headers = row_vals 

101 else: 

102 dset.append(row_vals) 

103 

104 @classmethod 

105 def import_book(cls, dbook, in_stream, headers=True, read_only=True): 

106 """Returns databook from XLS stream.""" 

107 

108 dbook.wipe() 

109 

110 xls_book = load_workbook(in_stream, read_only=read_only, data_only=True) 

111 

112 for sheet in xls_book.worksheets: 

113 data = tablib.Dataset() 

114 data.title = sheet.title 

115 

116 for i, row in enumerate(sheet.rows): 

117 row_vals = [c.value for c in row] 

118 if (i == 0) and (headers): 

119 data.headers = row_vals 

120 else: 

121 if i > 0 and len(row_vals) < data.width: 

122 row_vals += [''] * (data.width - len(row_vals)) 

123 data.append(row_vals) 

124 

125 dbook.add_sheet(data) 

126 

127 @classmethod 

128 def dset_sheet(cls, dataset, ws, freeze_panes=True): 

129 """Completes given worksheet from given Dataset.""" 

130 _package = dataset._package(dicts=False) 

131 

132 for i, sep in enumerate(dataset._separators): 

133 _offset = i 

134 _package.insert((sep[0] + _offset), (sep[1],)) 

135 

136 bold = Font(bold=True) 

137 wrap_text = Alignment(wrap_text=True) 

138 

139 for i, row in enumerate(_package): 

140 row_number = i + 1 

141 for j, col in enumerate(row): 

142 col_idx = get_column_letter(j + 1) 

143 cell = ws[f'{col_idx}{row_number}'] 

144 

145 # bold headers 

146 if (row_number == 1) and dataset.headers: 

147 cell.font = bold 

148 if freeze_panes: 

149 # Export Freeze only after first Line 

150 ws.freeze_panes = 'A2' 

151 

152 # bold separators 

153 elif len(row) < dataset.width: 

154 cell.font = bold 

155 

156 # wrap the rest 

157 else: 

158 try: 

159 str_col_value = str(col) 

160 except TypeError: 

161 str_col_value = '' 

162 if '\n' in str_col_value: 

163 cell.alignment = wrap_text 

164 

165 try: 

166 cell.value = col 

167 except (ValueError, TypeError): 

168 cell.value = str(col)