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

1from __future__ import annotations 

2 

3from datetime import UTC 

4import io 

5from typing import TYPE_CHECKING, Any 

6 

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

8 

9if TYPE_CHECKING: 

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

11 

12try: 

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

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

15 Alignment, 

16 Font, 

17 PatternFill, 

18 ) 

19 

20 HAS_OPENPYXL = True 

21except ImportError: 

22 HAS_OPENPYXL = False 

23 

24 

25class ExcelExportBackend(IExportBackend): 

26 """Backend for Excel exports.""" 

27 

28 async def generate_file( 

29 self, 

30 job: ExportJob, 

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

32 storage: Any, 

33 export_dir: str, 

34 ) -> str: 

35 """Export data to Excel format.""" 

36 if not HAS_OPENPYXL: 

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

38 

39 from datetime import datetime 

40 

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

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

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

44 

45 workbook = openpyxl.Workbook() 

46 worksheet = workbook.active 

47 worksheet.title = "Export" 

48 

49 if data: 

50 # Determine columns 

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

52 

53 # Write headers 

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

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

56 cell.font = Font(bold=True) 

57 cell.fill = PatternFill( 

58 start_color="FFE6E6FA", 

59 end_color="FFE6E6FA", 

60 fill_type="solid", 

61 ) 

62 

63 # Write data 

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

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

66 value = row.get(field, "") 

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

68 

69 # Basic formatting 

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

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

72 elif isinstance(value, datetime): 

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

74 

75 # Auto-adjust column widths 

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

77 column_letter = openpyxl.utils.get_column_letter(col_num) 

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

79 

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

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

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

83 

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

85 max_length + 2, 

86 50, 

87 ) 

88 

89 # Save to storage 

90 buffer = io.BytesIO() 

91 workbook.save(buffer) 

92 await storage.upload( 

93 file_path, 

94 buffer.getvalue(), 

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

96 ) 

97 

98 return file_path