Coverage for cc_modules/tests/cc_spreadsheet_tests.py: 21%

1""" 

2camcops_server/cc_modules/tests/cc_spreadsheet_tests.py 

3 

4=============================================================================== 

5 

6 Copyright (C) 2012, University of Cambridge, Department of Psychiatry. 

7 Created by Rudolf Cardinal (rnc1001@cam.ac.uk). 

8 

9 This file is part of CamCOPS. 

10 

11 CamCOPS is free software: you can redistribute it and/or modify 

12 it under the terms of the GNU General Public License as published by 

13 the Free Software Foundation, either version 3 of the License, or 

14 (at your option) any later version. 

15 

16 CamCOPS is distributed in the hope that it will be useful, 

17 but WITHOUT ANY WARRANTY; without even the implied warranty of 

18 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 

19 GNU General Public License for more details. 

20 

21 You should have received a copy of the GNU General Public License 

22 along with CamCOPS. If not, see <https://www.gnu.org/licenses/>. 

23 

24=============================================================================== 

25 

26""" 

27 

28import io 

29from typing import Any, Dict 

30from unittest import TestCase 

31import uuid 

32from xml.dom.minidom import parseString 

33import zipfile 

34 

35from camcops_server.cc_modules.cc_spreadsheet import ( 

36 SpreadsheetCollection, 

37 SpreadsheetPage, 

38 XLSX_VIA_PYEXCEL, 

39) 

40 

41if XLSX_VIA_PYEXCEL: 

42 import pyexcel_xlsx # e.g. pip install pyexcel-xlsx==0.5.7 

43 

44 openpyxl = XLWorkbook = XLWorksheet = None 

45else: 

46 import openpyxl 

47 from openpyxl.workbook.workbook import Workbook as XLWorkbook 

48 

49 pyexcel_xlsx = None 

50 

51 

52# ============================================================================= 

53# Unit tests 

54# ============================================================================= 

55 

56 

57class SpreadsheetCollectionTests(TestCase): 

58 def test_xlsx_created_from_zero_rows(self) -> None: 

59 page = SpreadsheetPage(name="test", rows=[]) 

60 coll = SpreadsheetCollection() 

61 coll.add_page(page) 

62 

63 output = coll.as_xlsx() 

64 

65 # https://en.wikipedia.org/wiki/List_of_file_signatures 

66 self.assertEqual(output[0], 0x50) 

67 self.assertEqual(output[1], 0x4B) 

68 self.assertEqual(output[2], 0x03) 

69 self.assertEqual(output[3], 0x04) 

70 

71 def test_xlsx_worksheet_names_are_page_names(self) -> None: 

72 page1 = SpreadsheetPage(name="name 1", rows=[{"test data 1": "row 1"}]) 

73 page2 = SpreadsheetPage(name="name 2", rows=[{"test data 2": "row 1"}]) 

74 page3 = SpreadsheetPage(name="name 3", rows=[{"test data 3": "row 1"}]) 

75 coll = SpreadsheetCollection() 

76 

77 coll.add_pages([page1, page2, page3]) 

78 

79 data = coll.as_xlsx() 

80 buffer = io.BytesIO(data) 

81 expected_sheetnames = ["name 1", "name 2", "name 3"] 

82 if openpyxl: 

83 wb = openpyxl.load_workbook(buffer) # type: XLWorkbook 

84 self.assertEqual(wb.sheetnames, expected_sheetnames) 

85 else: 

86 wb = pyexcel_xlsx.get_data(buffer) # type: Dict[str, Any] 

87 sheetnames = list(wb.keys()) 

88 self.assertEqual(sheetnames, expected_sheetnames) 

89 

90 def test_xlsx_page_name_exactly_31_chars_not_truncated(self) -> None: 

91 page = SpreadsheetPage( 

92 name="abcdefghijklmnopqrstuvwxyz78901", 

93 rows=[{"test data 1": "row 1"}], 

94 ) 

95 coll = SpreadsheetCollection() 

96 

97 self.assertEqual( 

98 coll.get_sheet_title(page), "abcdefghijklmnopqrstuvwxyz78901" 

99 ) 

100 

101 def test_xlsx_page_name_over_31_chars_truncated(self) -> None: 

102 page = SpreadsheetPage( 

103 name="abcdefghijklmnopqrstuvwxyz78901234", 

104 rows=[{"test data 1": "row 1"}], 

105 ) 

106 coll = SpreadsheetCollection() 

107 

108 self.assertEqual( 

109 coll.get_sheet_title(page), "abcdefghijklmnopqrstuvwxyz78..." 

110 ) 

111 

112 def test_xlsx_invalid_chars_in_page_name_replaced(self) -> None: 

113 page = SpreadsheetPage( 

114 name="[a]b\\c:d/e*f?g'h", rows=[{"test data 1": "row 1"}] 

115 ) 

116 coll = SpreadsheetCollection() 

117 

118 self.assertEqual(coll.get_sheet_title(page), "_a_b_c_d_e_f_g_h") 

119 

120 def test_ods_page_name_sanitised(self) -> None: 

121 # noinspection PyUnresolvedReferences 

122 page = SpreadsheetPage( 

123 name="What perinatal service have you accessed?", 

124 rows=[{"test data 1": "row 1"}], 

125 ) 

126 coll = SpreadsheetCollection() 

127 coll.add_pages([page]) 

128 

129 data = coll.as_ods() 

130 

131 zf = zipfile.ZipFile(io.BytesIO(data), "r") 

132 content = zf.read("content.xml") 

133 doc = parseString(content) 

134 sheets = doc.getElementsByTagName("table:table") 

135 self.assertEqual( 

136 sheets[0].getAttribute("table:name"), 

137 "What perinatal service have ...", 

138 ) 

139 

140 def test_worksheet_names_are_not_duplicated(self) -> None: 

141 page1 = SpreadsheetPage( 

142 name="abcdefghijklmnopqrstuvwxyz78901234", 

143 rows=[{"test data 1": "row 1"}], 

144 ) 

145 page2 = SpreadsheetPage( 

146 name="ABCDEFGHIJKLMNOPQRSTUVWXYZ789012345", 

147 rows=[{"test data 2": "row 1"}], 

148 ) 

149 page3 = SpreadsheetPage( 

150 name="abcdefghijklmnopqrstuvwxyz7890123456", 

151 rows=[{"test data 3": "row 1"}], 

152 ) 

153 coll = SpreadsheetCollection() 

154 

155 coll.add_pages([page1, page2, page3]) 

156 

157 valid_sheet_names = coll.get_pages_with_valid_sheet_names() 

158 

159 names = [v for k, v in valid_sheet_names.items()] 

160 

161 self.assertIn("abcdefghijklmnopqrstuvwxyz78...", names) 

162 self.assertIn("ABCDEFGHIJKLMNOPQRSTUVWXYZ78..1", names) 

163 self.assertIn("abcdefghijklmnopqrstuvwxyz78..2", names) 

164 

165 def test_uuid_exported_to_ods_as_string(self) -> None: 

166 test_uuid = uuid.UUID("6457cb90-1ca0-47a7-9f40-767567819bee") 

167 

168 page = SpreadsheetPage(name="Testing", rows=[{"UUID": test_uuid}]) 

169 coll = SpreadsheetCollection() 

170 coll.add_pages([page]) 

171 

172 data = coll.as_ods() 

173 zf = zipfile.ZipFile(io.BytesIO(data), "r") 

174 content = zf.read("content.xml") 

175 doc = parseString(content) 

176 text_values = [ 

177 t.firstChild.nodeValue for t in doc.getElementsByTagName("text:p") 

178 ] 

179 

180 self.assertIn("UUID", text_values) 

181 self.assertIn("6457cb90-1ca0-47a7-9f40-767567819bee", text_values) 

182 

183 def test_uuid_exported_to_xlsx_as_string(self) -> None: 

184 test_uuid = uuid.UUID("6457cb90-1ca0-47a7-9f40-767567819bee") 

185 

186 page = SpreadsheetPage(name="Testing", rows=[{"UUID": test_uuid}]) 

187 coll = SpreadsheetCollection() 

188 coll.add_pages([page]) 

189 

190 data = coll.as_xlsx() 

191 buffer = io.BytesIO(data) 

192 if openpyxl: 

193 self.fail("This test has not been written for openpyxl") 

194 else: 

195 wb = pyexcel_xlsx.get_data(buffer) # type: Dict[str, Any] 

196 self.assertIn(["UUID"], wb["Testing"]) 

197 self.assertIn( 

198 ["6457cb90-1ca0-47a7-9f40-767567819bee"], wb["Testing"] 

199 )