Coverage for src/lexigram/admin/services/export/adapters/excel.py: 25%

1from __future__ import annotations 

2 

3from datetime import UTC 

4import io 

5from typing import TYPE_CHECKING, Any 

6 

7from lexigram.admin.services.export.sanitize import sanitize_cell_value 

8from lexigram.admin.services.export.service import IExportBackend 

9 

10if TYPE_CHECKING: 

11 from lexigram.admin.services.export.scheduler import ExportJob 

12 

13try: 

14 import openpyxl # type: ignore[import-untyped] 

15 from openpyxl.styles import ( # type: ignore[import-untyped] 

16 Alignment, 

17 Font, 

18 PatternFill, 

19 ) 

20 

21 HAS_OPENPYXL = True 

22except ImportError: 

23 HAS_OPENPYXL = False 

24 

25 

26class ExcelExportBackend(IExportBackend): 

27 """Backend for Excel exports.""" 

28 

29 async def generate_file( 

30 self, 

31 job: ExportJob, 

32 data: list[dict[str, Any]], 

33 storage: Any, 

34 export_dir: str, 

35 ) -> str: 

36 """Export data to Excel format.""" 

37 if not HAS_OPENPYXL: 

38 raise ImportError("openpyxl is required for Excel export") 

39 

40 from datetime import datetime 

41 

42 timestamp = datetime.now(UTC).strftime("%Y%m%d_%H%M%S") 

43 filename = f"{job.resource_name}_export_{timestamp}.xlsx" 

44 file_path = f"{export_dir}/{filename}" 

45 

46 workbook = openpyxl.Workbook() 

47 worksheet = workbook.active 

48 worksheet.title = "Export" 

49 

50 if data: 

51 # Determine columns 

52 fieldnames = job.columns or list(data[0].keys()) 

53 

54 # Write headers 

55 for col_num, header in enumerate(fieldnames, 1): 

56 cell = worksheet.cell(row=1, column=col_num, value=header) 

57 cell.font = Font(bold=True) 

58 cell.fill = PatternFill( 

59 start_color="FFE6E6FA", 

60 end_color="FFE6E6FA", 

61 fill_type="solid", 

62 ) 

63 

64 # Write data 

65 for row_num, row in enumerate(data, 2): 

66 for col_num, field in enumerate(fieldnames, 1): 

67 value = sanitize_cell_value(row.get(field, "")) 

68 cell = worksheet.cell(row=row_num, column=col_num, value=value) 

69 

70 # Basic formatting 

71 if isinstance(value, (int, float)): 

72 cell.alignment = Alignment(horizontal="right") 

73 elif isinstance(value, datetime): 

74 cell.number_format = "YYYY-MM-DD HH:MM:SS" 

75 

76 # Auto-adjust column widths 

77 for col_num, field in enumerate(fieldnames, 1): 

78 column_letter = openpyxl.utils.get_column_letter(col_num) 

79 max_length = len(field) # Start with header length 

80 

81 for row in data[:100]: # Sample first 100 rows 

82 value = str(row.get(field, "")) 

83 max_length = max(max_length, len(value)) 

84 

85 worksheet.column_dimensions[column_letter].width = min( 

86 max_length + 2, 

87 50, 

88 ) 

89 

90 # Save to storage 

91 buffer = io.BytesIO() 

92 workbook.save(buffer) 

93 await storage.upload( 

94 file_path, 

95 buffer.getvalue(), 

96 content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", 

97 ) 

98 

99 return file_path