Unable to cast string to OfficeOpenXml

Question

I am exporting table data to excel, but I get an error with the following details: System.InvalidCastException: 'Unable to cast object of type 'System.String' to type 'OfficeOpenXml.ExcelColumn'.'. I am using ExcelPackage package.

My code method is:

public void ExportToExcel()
        {
//FECTH DATA FROM DB
            List<ExcellExport> excellExports = db2.MaintenanceWorkFlows.Select(
                x => new ExcellExport
                {
                    MaintenanceId = x.MaintenanceId,
                    RequisitionNo = x.RequisitionNo,
                    MaintenanceDate = x.MaintenanceDate,
                    MaintenanceFrequency = x.MaintenanceFrequency,
                    ContractNo = x.ContractNumber,
                    ContractorName = x.ContractorName,
                    MaintenanceStatus = x.MaintenanceStatus
                }).ToList();

from here. I use the excelpackage to export the data

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            ExcelPackage pck = new ExcelPackage();

            ExcelWorksheet ws = pck.Workbook.Worksheets.Add("ExpiredContracts");
            ws.Cells["A1"].Value = "Report";
            ws.Cells["B1"].Value = "Expired Contracts Report";

            ws.Cells["A1"].Value = "Date";
            ws.Cells["B1"].Value = string.Format("{0:dd MMMM yyy} at {0:H:mm tt}", DateTimeOffset.Now);

            ws.Cells["A4"].Value = "MainenanceId";
            ws.Cells["B4"].Value = "RequisitionNo";
            ws.Cells["C4"].Value = "MaintenanceDate";
            ws.Cells["D4"].Value = "MaintenanceFrequency";
            ws.Cells["E"].Value = "ContractNo";
            ws.Cells["F4"].Value = "ContractorName";
            ws.Cells["G4"].Value = "MaintenanceStatus";

            int rowStart = 10;

            foreach(var item in excellExports)
            {
                ws.Cells[string.Format("A{0}", rowStart)].Value = item.MaintenanceId;
                ws.Cells[string.Format("A{0}", rowStart)].Value = item.RequisitionNo;
                ws.Cells[string.Format("A{0}", rowStart)].Value = item.MaintenanceDate;
                ws.Cells[string.Format("A{0}", rowStart)].Value = item.MaintenanceFrequency;
                ws.Cells[string.Format("A{0}", rowStart)].Value = item.ContractNo;
                ws.Cells[string.Format("A{0}", rowStart)].Value = item.ContractorName;
                ws.Cells[string.Format("A{0}", rowStart)].Value = item.MaintenanceStatus;
            }
            ws.Cells["A:AZ"].AutoFitColumns();
            Response.Clear();
            Response.ContentType = "application/vnd.Openxmlformats.Officedocument-spreadsheetml.sheet";
            Response.AddHeader("content-disposition" ,"attachment:filename="+"ExpiredContractsReport.xlsx");
            Response.BinaryWrite(pck.GetAsByteArray());
            Response.End();


        }

What am I doing wrong? I have looked at codes but none solves the issue.

and which line here is problematic? Error seem self-explaining — T.S., May 21 '20 at 16:13
this is the part with the error : `ws.Cells["A:AZ"].AutoFitColumns();` — Nehemiah Limo, May 21 '20 at 16:30
see this https://stackoverflow.com/questions/15642950/auto-column-width-in-epplus/15643058 — T.S., May 21 '20 at 16:35
i changed to `ws.Column(1).AutoFit()` but I got this after running it `System.NullReferenceException: 'Object reference not set to an instance of an object.'` — Nehemiah Limo, May 21 '20 at 17:16
place a breakpoint on this line and use the mouse to investigate, which instance is not there `ws` or `Column(1)` — T.S., May 21 '20 at 18:10
it is ok upto here: `ws.Column(1).AutoFit();` then break at `Response.BinaryWrite(pck.GetAsByteArray());` — Nehemiah Limo, May 23 '20 at 18:03

Unable to cast string to OfficeOpenXml

0 Answers0