locked
Export Database to Excel throws up an Error while Opening the Output file RRS feed

  • Question

  • User-1244692504 posted

    The generated file throws up an error:

    "Excel cannot open the file DN00000020.xlsx because the file format or file extension is invalid. Verify that the file has not been corrupted and that the file extension matches the format of the file"

    Kindly find my code snippet below

    public void ExportConsequencetialLossPolicyScheduleToExcel(string id, string policyCode, string clientCode, string policyRefCode)
            {
                var defaultSortBy = Constants.SortField.DebitNoteNo;
                var pagingParameter = MVCExtensionMethods.GetPagingParameters(ControllerContext.RequestContext.HttpContext.Request, defaultSortBy);
                pagingParameter.PageSize = 10000; //until we introduce server side paging
    
                IEnumerable<PolicyScheduleViewDTO> consequentialLossPolicyDetailList = _underwritingService.GetConsequentialLossPolicyScheduleList(id, policyCode, clientCode, policyRefCode, pagingParameter.PageNumber, pagingParameter.PageSize, pagingParameter.SortBy, pagingParameter.SortDirection, string.Empty, new string[] { });
    
                    ViewBag.DebitNoteNo = id;
                    ViewBag.PolicyCode = policyCode;
                    ViewBag.ClientCode = clientCode;
                    ViewBag.PolicyRefCode = policyRefCode;
            
                    ExcelPackage pkt = new ExcelPackage();
                    ExcelWorksheet ws = pkt.Workbook.Worksheets.Add("Report");
    ws.Cells["F1"].Value = string.Format("{0:dd MMMM yyyy} at {0:HH: mm tt}", DateTimeOffset.Now); ws.Cells["A10"].Value = "S/N"; ws.Cells["B10"].Value = "DESCRIPTION"; ws.Cells["C10"].Value = "SUM INSURED"; ws.Cells["D10"].Value = "PREMIUM RATE"; ws.Cells["E10"].Value = "MULT EFF"; ws.Cells["F10"].Value = "PREMIUM"; ws.Cells["G10"].Value = "LTA DISCOUNT %"; ws.Cells["H10"].Value = "LTA AMOUNT"; ws.Cells["I10"].Value = "FE APP. DISCOUNT"; ws.Cells["J10"].Value = "FE APP. AMOUNT"; ws.Cells["K10"].Value = "PERIL RATE"; ws.Cells["L10"].Value = "PREMIUM DUE"; ws.Cells["M10"].Value = "PRORATA DAY"; ws.Cells["N10"].Value = "PRORATA PREMIUM"; int rowStart = 7; foreach(var item in consequentialLossPolicyDetailList) { ws.Cells[string.Format("A{0}", rowStart)].Value = item.SerialNo; ws.Cells[string.Format("B{0}", rowStart)].Value = item.Description; ws.Cells[string.Format("C{0}", rowStart)].Value = item.SumInsured; ws.Cells[string.Format("D{0}", rowStart)].Value = item.PremiumRate; ws.Cells[string.Format("E{0}", rowStart)].Value = item.ProfitRate; ws.Cells[string.Format("F{0}", rowStart)].Value = item.PremiumAmount; ws.Cells[string.Format("G{0}", rowStart)].Value = item.LTADiscount; ws.Cells[string.Format("H{0}", rowStart)].Value = item.LTA_Amount; ws.Cells[string.Format("I{0}", rowStart)].Value = item.FireExtinguishingAppDiscount; ws.Cells[string.Format("J{0}", rowStart)].Value = item.FireExtinguishingAppAmount; ws.Cells[string.Format("K{0}", rowStart)].Value = item.PerilRate; ws.Cells[string.Format("L{0}", rowStart)].Value = item.NetPremiumAmount; ws.Cells[string.Format("M{0}", rowStart)].Value = item.ProrataDay; ws.Cells[string.Format("N{0}", rowStart)].Value = item.ProrataPremium; rowStart++; } ws.Cells["A:AZ"].AutoFitColumns(); Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment: filename =" + "ExportToExcel.xlsx"); Response.BinaryWrite(pkt.GetAsByteArray()); Response.End(); } #endregion

    Your assistant will be greatly appreciated.

    Friday, August 23, 2019 4:52 PM