package com.ffii.lioner.modules.common.service;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;

import com.ffii.lioner.modules.lioner.reportDao.ImportErrorRecord;

@Service
public class ExcelReportService {
    private static final String DASHBOARD_SUMMARY_FILE_PATH = "templates/report/awardSummaryByDivision.xlsx";
    private static final String IMPORT_TEMPLATE_PATH = "templates/report/Appreciation_Records_Import_Template_v1.0.xlsx";
    private static final String AWARD_IMPORT_TEMPLATE_PATH = "templates/report/Award_Record_Import_Template_v1.0.xlsx";
    private final Log logger = LogFactory.getLog(getClass());

    public byte[] generateDashboardExcelReport(List<Map<String,Object>> queryResults, List<Map<String,Object>> detailResult, Integer year) throws IOException {
        // Generate the Excel report with query results
        Workbook workbook = createDashboardExcelReport(queryResults, detailResult, DASHBOARD_SUMMARY_FILE_PATH,year);

        // Write the workbook to a ByteArrayOutputStream
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream.toByteArray();
    }

    private Workbook createDashboardExcelReport(List<Map<String,Object>> queryResults, List<Map<String,Object>> detailResult, String templatePath, Integer year) throws IOException {
        ClassPathResource resource = new ClassPathResource(templatePath);
        InputStream templateInputStream = resource.getInputStream();
        Workbook workbook = new XSSFWorkbook(templateInputStream);
    
        // Page 1
        Sheet sheet = workbook.getSheetAt(0);
    
        int rowIndex = 1; // Assuming the location is in the first row  
        int columnIndex = 0;
        Row tempRow = sheet.getRow(rowIndex);

        LocalDate currentDate = LocalDate.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd MMM yyyy");
        DateTimeFormatter displayFormatter = DateTimeFormatter.ofPattern("dd-MM-yyyy");
        String formattedDate = currentDate.format(formatter);
        sheet.getHeader().setCenter(year.toString() + " Award Summary by Division" );
        sheet.getHeader().setRight("Printed Date: " + formattedDate );
        Cell tempCell = tempRow.getCell(columnIndex);
        CellStyle cellStyle = tempCell.getCellStyle();
        
        int rowNum = 1;
        for (Map<String,Object> result : queryResults) {
            Row row = sheet.createRow(rowNum++);
            Cell nameCell = row.createCell(0);
            nameCell.setCellValue(result.get("name").toString());
            nameCell.setCellStyle(cellStyle); 

            Cell countCell = row.createCell(1);
            countCell.setCellValue( Integer.parseInt(result.get("count").toString()));
            countCell.setCellStyle(cellStyle);
            // Add more cells/columns as needed
        }
    
        // Auto-size columns
        for (int i = 0; i < queryResults.size(); i++) {
            sheet.autoSizeColumn(i);
        }

        // Page 2
        sheet = workbook.getSheetAt(1);
    
        rowIndex = 1; // Assuming the location is in the first row  
        columnIndex = 0;
        tempRow = sheet.getRow(rowIndex);
        tempCell = tempRow.getCell(columnIndex);
        cellStyle = tempCell.getCellStyle();
       
        currentDate = LocalDate.now();
        formatter = DateTimeFormatter.ofPattern("dd MMM yyyy");
        formattedDate = currentDate.format(formatter);
        sheet.getHeader().setCenter(year.toString() + " Award Summary by Division" );
        sheet.getHeader().setRight("Printed Date: " + formattedDate );

        rowNum = 1;
        for (Map<String,Object> result : detailResult) {
            Row row = sheet.createRow(rowNum++);
            Cell nameCell = row.createCell(0);
            nameCell.setCellValue(result.get("name").toString());
            nameCell.setCellStyle(cellStyle); 
       
            Cell countCell = row.createCell(1);
            countCell.setCellValue( result.get("subDivision").toString());
            countCell.setCellStyle(cellStyle);
            CellStyle temp = countCell.getCellStyle();
            temp.setWrapText(true);
            countCell.setCellStyle(temp);

            Cell eventNameCell = row.createCell(2);
            eventNameCell.setCellValue( result.get("eventName").toString());
            eventNameCell.setCellStyle(cellStyle);

            Cell applicationNameCell = row.createCell(3);
            applicationNameCell.setCellValue( result.get("applicationName").toString());
            applicationNameCell.setCellStyle(cellStyle);

            Cell awardNameCell = row.createCell(4);
            awardNameCell.setCellValue( result.get("awardName").toString());
            awardNameCell.setCellStyle(cellStyle);
            
            Cell receiveDateCell = row.createCell(5);
            receiveDateCell.setCellValue( result.get("receiveDate") == null ? "" : ((LocalDateTime) result.get("receiveDate")).format(displayFormatter));
            receiveDateCell.setCellStyle(cellStyle);

            Cell categoryCell = row.createCell(6);
            categoryCell.setCellValue( result.get("category").toString());
            categoryCell.setCellStyle(cellStyle);

            Cell responsibleOfficerCell = row.createCell(7);
            responsibleOfficerCell.setCellValue(result.get("responsibleOfficer").toString());
            responsibleOfficerCell.setCellStyle(cellStyle);
        }
        
        // Auto-size columns
        for (int i = 0; i < queryResults.size(); i++) {
            if(i!= 1){
                sheet.autoSizeColumn(i);
            }
        }
    
        return workbook;
    }

    public byte[] generateEventExcelReport(List<Map<String,Object>> queryResults) throws IOException {
        // Generate the Excel report with query results
        Workbook workbook = createEventExcelReport(queryResults);

        // Write the workbook to a ByteArrayOutputStream
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream.toByteArray();
    }

    private Workbook createEventExcelReport(List<Map<String,Object>> queryResults) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

        // Page 1
        Sheet sheet = workbook.createSheet("Event Data");

        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("id");
        headerRow.createCell(1).setCellValue("Created By");
        headerRow.createCell(2).setCellValue("Created");
        headerRow.createCell(3).setCellValue("Name");
        headerRow.createCell(4).setCellValue("Chinese Name");
        headerRow.createCell(5).setCellValue("Description");
        headerRow.createCell(6).setCellValue("Region");
        headerRow.createCell(7).setCellValue("Division");        
        headerRow.createCell(8).setCellValue("Organization");
        headerRow.createCell(9).setCellValue("Event Type");
        headerRow.createCell(10).setCellValue("Frequency");
        headerRow.createCell(11).setCellValue("Series");
        headerRow.createCell(12).setCellValue("Application Date");
        headerRow.createCell(13).setCellValue("Application Deadline");
        headerRow.createCell(14).setCellValue("Next Application Date");
        headerRow.createCell(15).setCellValue("Announcement Date");
        headerRow.createCell(16).setCellValue("Award Date");
        headerRow.createCell(17).setCellValue("Reminder Flag");
        headerRow.createCell(18).setCellValue("Reminder Threshold");
        headerRow.createCell(19).setCellValue("Reminder Interval");
        headerRow.createCell(20).setCellValue("Reminder Limit");

        int rowNum = 1;
        for (Map<String,Object> result : queryResults) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(Integer.parseInt(result.get("id").toString()));
            row.createCell(1).setCellValue(result.get("username") == null ? "" : result.get("username").toString());
            row.createCell(2).setCellValue(((LocalDateTime)result.get("created")).format(formatter));
            row.createCell(3).setCellValue(result.get("name").toString());
            row.createCell(4).setCellValue(result.get("nameCht") == null ? "" : result.get("nameCht").toString());
            row.createCell(5).setCellValue(result.get("description") == null ? "" : result.get("description").toString());
            row.createCell(6).setCellValue(result.get("region") == null ? "" : result.get("region").toString());
            row.createCell(7).setCellValue(result.get("divisionList") == null ? "" : result.get("divisionList").toString());
            row.createCell(8).setCellValue(result.get("organization") == null ? "" : result.get("organization").toString());
            row.createCell(9).setCellValue(result.get("eventType") == null ? "" : result.get("eventType").toString());
            row.createCell(10).setCellValue(result.get("frequency") == null ? "" : result.get("frequency").toString());
            row.createCell(11).setCellValue(result.get("series") == null ? "" : result.get("series").toString());
            row.createCell(12).setCellValue(((LocalDateTime)result.get("startDate")).format(formatter));
            row.createCell(13).setCellValue(((LocalDateTime)result.get("applicationDeadline")).format(formatter));
            row.createCell(14).setCellValue(result.get("nextApplicationDate") == null ? "" :((LocalDateTime)result.get("nextApplicationDate")).format(formatter));
            row.createCell(15).setCellValue(result.get("announcementDate") == null ? "" : ((LocalDateTime)result.get("announcementDate")).format(formatter));
            row.createCell(16).setCellValue(result.get("awardDate") == null ? "" : ((LocalDateTime )result.get("awardDate")).format(formatter));
            row.createCell(17).setCellValue(result.get("reminderFlag").toString());
            row.createCell(18).setCellValue(result.get("reminderThreshold") == null ? 0 : Integer.parseInt(result.get("reminderThreshold").toString()));
            row.createCell(19).setCellValue(result.get("reminderInterval") == null ? 0 : Integer.parseInt(result.get("reminderInterval").toString()));
            row.createCell(20).setCellValue(result.get("reminderLimit") == null ? 0 : Integer.parseInt(result.get("reminderLimit").toString()));
            // Add more cells/columns as needed
        }
    
        // Auto-size columns
        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            sheet.autoSizeColumn(i);
        }

        return workbook;
    }

    public byte[] generateApplicationExcelReport(List<Map<String,Object>> queryResults) throws IOException {
        // Generate the Excel report with query results
        Workbook workbook = createApplicationExcelReport(queryResults);

        // Write the workbook to a ByteArrayOutputStream
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream.toByteArray();
    }

    private Workbook createApplicationExcelReport(List<Map<String,Object>> queryResults) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

        // Page 1
        Sheet sheet = workbook.createSheet("Application Data");
    
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("id");
        headerRow.createCell(1).setCellValue("Created By");
        headerRow.createCell(2).setCellValue("Created");
        headerRow.createCell(3).setCellValue("Event Name");
        headerRow.createCell(4).setCellValue("Application Name");
        headerRow.createCell(5).setCellValue("Description");
        headerRow.createCell(6).setCellValue("Division");        
        headerRow.createCell(7).setCellValue("Responsible Officer");
        headerRow.createCell(8).setCellValue("Status");

        int rowNum = 1;
        for (Map<String,Object> result : queryResults) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(Integer.parseInt(result.get("id").toString()));
            row.createCell(1).setCellValue(result.get("username") == null ? "" : result.get("username").toString());
            row.createCell(2).setCellValue(((LocalDateTime)result.get("created")).format(formatter));

            row.createCell(3).setCellValue(result.get("eventName").toString());
            row.createCell(4).setCellValue(result.get("name").toString());
            row.createCell(5).setCellValue(result.get("description") == null ? "" : result.get("description").toString());
            row.createCell(6).setCellValue(result.get("divisionList") == null ? "" : result.get("divisionList").toString());
            row.createCell(7).setCellValue(result.get("responsibleOfficer").toString());
            row.createCell(8).setCellValue(result.get("status").toString());
            // Add more cells/columns as needed
        }
    
        // Auto-size columns
        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            sheet.autoSizeColumn(i);
            
        }

        return workbook;
    }

    public byte[] generateAwardExcelReport(List<Map<String,Object>> queryResults) throws IOException {
        // Generate the Excel report with query results
        Workbook workbook = createAwardExcelReport(queryResults);

        // Write the workbook to a ByteArrayOutputStream
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream.toByteArray();
    }

    private Workbook createAwardExcelReport(List<Map<String,Object>> queryResults) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

        // Page 1
        Sheet sheet = workbook.createSheet("Award Data");
    
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("id");
        headerRow.createCell(1).setCellValue("Created By");
        headerRow.createCell(2).setCellValue("Created");
        headerRow.createCell(3).setCellValue("Event Name");
        headerRow.createCell(4).setCellValue("Application Name");
        headerRow.createCell(5).setCellValue("Award Name");
        headerRow.createCell(6).setCellValue("Award Chinese Name");
        headerRow.createCell(7).setCellValue("Remarks");
        headerRow.createCell(8).setCellValue("Division");        
        headerRow.createCell(9).setCellValue("Category");
        headerRow.createCell(10).setCellValue("Receive Date");
        headerRow.createCell(11).setCellValue("Storage Location");
        headerRow.createCell(12).setCellValue("Physical Award");
        headerRow.createCell(13).setCellValue("Responsible Officer");

        int rowNum = 1;
        for (Map<String,Object> result : queryResults) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(Integer.parseInt(result.get("id").toString()));
            row.createCell(1).setCellValue(result.get("username") == null ? "" : result.get("username").toString());
            row.createCell(2).setCellValue(((LocalDateTime)result.get("created")).format(formatter));
            row.createCell(3).setCellValue(result.get("eventName").toString());
            row.createCell(4).setCellValue(result.get("applicationName").toString());
            row.createCell(5).setCellValue(result.get("name").toString());
            row.createCell(6).setCellValue(result.get("nameCht") == null ? "" : result.get("nameCht").toString());
            row.createCell(7).setCellValue(result.get("remarks") == null ? "" : result.get("remarks").toString());
            row.createCell(8).setCellValue(result.get("divisionList") == null ? "" : result.get("divisionList").toString());
            row.createCell(9).setCellValue(result.get("categoryName").toString());
            row.createCell(10).setCellValue(result.get("receiveDate") == null ? "" : ((LocalDateTime)result.get("receiveDate")).format(formatter));
            row.createCell(11).setCellValue(result.get("storageLocation") == null ? "" : result.get("storageLocation").toString());
            row.createCell(12).setCellValue(result.get("physicalAward") == null ? "" : result.get("physicalAward").toString());
            row.createCell(13).setCellValue(result.get("responsibleOfficer") == null ? "" : result.get("responsibleOfficer").toString());
            // Add more cells/columns as needed
        }
    
        // Auto-size columns
        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            sheet.autoSizeColumn(i);
            
        }

        return workbook;
    }

    public byte[] exportAppreciationTemplate() throws IOException {
        ClassPathResource resource = new ClassPathResource(IMPORT_TEMPLATE_PATH);
        InputStream templateInputStream = resource.getInputStream();
        Workbook workbook = new XSSFWorkbook(templateInputStream);

        // Write the workbook to a ByteArrayOutputStream
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream.toByteArray();
    }

    public byte[] exportAwardImportTemplate(Map<String,List<Map<String,Object>>> masterData) throws IOException {
        ClassPathResource resource = new ClassPathResource(AWARD_IMPORT_TEMPLATE_PATH);
        InputStream templateInputStream = resource.getInputStream();
        Workbook workbook = new XSSFWorkbook(templateInputStream);

        // Iterate over the map entries
        for (Map.Entry<String, List<Map<String,Object>>> entry : masterData.entrySet()) {
            String key = entry.getKey();
            List<Map<String,Object>> values = entry.getValue();
            // Find the sheet by the key
            Sheet sheet = workbook.getSheet(key);

            if (sheet != null) {

                // Write the list values to the sheet
                int rowNum = 0;
                for (Map<String,Object>value : values) {
                    Row row = sheet.createRow(rowNum++);
                    Cell cell = row.createCell(0);
                    cell.setCellValue(value.get("name").toString());  
                }
            } else {
                // Handle case where sheet with the key doesn't exist
                System.out.println("Sheet with key " + key + " not found.");
            }
        }

        // Write the workbook to a ByteArrayOutputStream
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream.toByteArray();
    }

    public byte[] generateAppreciationExcelReport(List<Map<String,Object>> queryResults) throws IOException {
        // Generate the Excel report with query results
        Workbook workbook = createAppreciationExcelReport(queryResults);

        // Write the workbook to a ByteArrayOutputStream
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream.toByteArray();
    }

    private Workbook createAppreciationExcelReport(List<Map<String,Object>> queryResults) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

        // Page 1
        Sheet sheet = workbook.createSheet("Award Data");
    
        Row headerRow = sheet.createRow(0);
        //headerRow.createCell(0).setCellValue("id");
        headerRow.createCell(0).setCellValue("Created By");
        headerRow.createCell(1).setCellValue("Created");
        headerRow.createCell(2).setCellValue("SBU / Division");
        headerRow.createCell(3).setCellValue("Receipt Date");
        headerRow.createCell(4).setCellValue("Brief Description");
        headerRow.createCell(5).setCellValue("Venue Involved");
        headerRow.createCell(6).setCellValue("Client Department / Organization");
        headerRow.createCell(7).setCellValue("Client Full Name");
        headerRow.createCell(8).setCellValue("Client Post");
        headerRow.createCell(9).setCellValue("Client Reply Date");
        headerRow.createCell(10).setCellValue("Staff Reply Date");
        headerRow.createCell(11).setCellValue("No. of Staff");
        headerRow.createCell(12).setCellValue("LN Receipt Date");
        headerRow.createCell(13).setCellValue("Category");   
        headerRow.createCell(14).setCellValue("Remarks");


        int rowNum = 1;
        for (Map<String,Object> result : queryResults) {
            Row row = sheet.createRow(rowNum++);
            //row.createCell(0).setCellValue(Integer.parseInt(result.get("id").toString()));
            row.createCell(0).setCellValue(result.get("username") == null ? "" : result.get("username").toString());
            row.createCell(1).setCellValue(((LocalDateTime)result.get("created")).format(formatter));
            row.createCell(2).setCellValue(result.get("divisionString").toString());
            row.createCell(3).setCellValue(result.get("receiptDate") == null ? "" : ((LocalDateTime)result.get("receiptDate")).format(formatter));
            row.createCell(4).setCellValue(result.get("description").toString());
            row.createCell(5).setCellValue(result.get("venue") == null ? "" : result.get("venue").toString());
            row.createCell(6).setCellValue(result.get("clientDepartment") == null ? 
                result.get("clientOrganization").toString().length() == 0 ? "" : result.get("clientOrganization").toString() 
                : result.get("clientDepartment").toString());
            row.createCell(7).setCellValue(result.get("clientFullname") == null ? "" : result.get("clientFullname").toString());
            row.createCell(8).setCellValue(result.get("clientPost") == null ? "" : result.get("clientPost").toString());
            row.createCell(9).setCellValue(result.get("clientReplyDate") == null ? "" : ((LocalDateTime)result.get("clientReplyDate")).format(formatter));
            row.createCell(10).setCellValue(result.get("staffReplyDate") == null ? "" : ((LocalDateTime)result.get("staffReplyDate")).format(formatter));
            row.createCell(11).setCellValue(result.get("noOfStaff") == null ? "" : result.get("noOfStaff").toString());
            row.createCell(12).setCellValue(((LocalDateTime)result.get("lnReceiptDate")).format(formatter));
            row.createCell(13).setCellValue(result.get("category").toString());
            row.createCell(14).setCellValue(result.get("remarks") == null ? "" : result.get("remarks").toString());
            // Add more cells/columns as needed
        }
    
        // Auto-size columns
        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            sheet.autoSizeColumn(i);
            
        }

        return workbook;
    }

    public byte[] generateAppreciationImportErrorReport(List<ImportErrorRecord> record) throws IOException {
        // Generate the Excel report with query results
        Workbook workbook = createAppreciationImportErrorExcelReport(record);

        // Write the workbook to a ByteArrayOutputStream
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream.toByteArray();
    }

    private Workbook createAppreciationImportErrorExcelReport(List<ImportErrorRecord> record) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

        // Page 1
        Sheet sheet = workbook.createSheet("Error Record");
    
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("Row");
        headerRow.createCell(1).setCellValue("Column");
        headerRow.createCell(2).setCellValue("Error Message");


        int rowNum = 1;
        for (ImportErrorRecord result : record) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(result.getRowId().toString());
            row.createCell(1).setCellValue(result.getColumn());
            row.createCell(2).setCellValue(result.getRemarks());
        }
    
        // Auto-size columns
        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            sheet.autoSizeColumn(i);
        }

        return workbook;
    }

    public byte[] generateAwardImportErrorReport(List<ImportErrorRecord> record) throws IOException {
        // Generate the Excel report with query results
        Workbook workbook = createAwardImportErrorExcelReport(record);

        // Write the workbook to a ByteArrayOutputStream
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream.toByteArray();
    }

    private Workbook createAwardImportErrorExcelReport(List<ImportErrorRecord> record) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

        // Page 1
        Sheet sheet = workbook.createSheet("Error Record");
    
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("Row");
        headerRow.createCell(1).setCellValue("Page Name");
        headerRow.createCell(2).setCellValue("Column");
        headerRow.createCell(3).setCellValue("Error Message");


        int rowNum = 1;
        for (ImportErrorRecord result : record) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(result.getRowId().toString());
            row.createCell(1).setCellValue(result.getPageName());
            row.createCell(2).setCellValue(result.getColumn());
            row.createCell(3).setCellValue(result.getRemarks());
        }
    
        // Auto-size columns
        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            sheet.autoSizeColumn(i);
        }

        return workbook;
    }

}