0

I have a list of items from the database base that I will like to generate in excel and PDF. I want to do the excel first. I have not done this before, following a tutorial online I was able to get it to return FIle. But the problem is that I got "non invocable member 'File" cannot be used like a method". I have already used "using System.IO;"

Can someone help figure out what I am doing wrong

        {

            try
            {

                //var parameters = new { UserName = username, Password = password };
                //var sql = "select * from users where username = @UserName and password = @Password";
                //var result = connection.Query(sql, parameters);

                IEnumerable<DailyInterest> dailyInterests = null;

                var Id = id;
                using (var conn = new SqlConnection(connectionstring))
                {
                    await conn.OpenAsync();

                    //var parameters = new { Id = id };

                    dailyInterests = conn.Query<DailyInterest>("Select * from DailyInterest where LoanAccountNo=@Id", new { Id = id });


                    //step1: create array to holder header labels
                    string[] col_names = new string[]{
                        "Loan Account No",
                        "Transaction Amount",
                        "Interest Rate",
                        "Interest Amount",
                        "Original Loan Amount",
                        "Narration",
                        "DRCR",
                        "Transaction Date"
                    };

                    //step2: create result byte array
                    byte[] result;

                    //step3: create a new package using memory safe structure
                    using (var package = new ExcelPackage())
                    {
                        //step4: create a new worksheet
                        var worksheet = package.Workbook.Worksheets.Add("final");

                        //step5: fill in header row
                        //worksheet.Cells[row,col].  {Style, Value}
                        for (int i = 0; i < col_names.Length; i++)
                        {
                            worksheet.Cells[1, i + 1].Style.Font.Size = 14;  //font
                            worksheet.Cells[1, i + 1].Value = col_names[i];  //value
                            worksheet.Cells[1, i + 1].Style.Font.Bold = true; //bold
                                                                              //border the cell
                            worksheet.Cells[1, i + 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
                            //set background color for each sell
                            worksheet.Cells[1, i + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                            worksheet.Cells[1, i + 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 243, 214));

                        }
                        int row = 8;

                        //step6: loop through query result and fill in cells
                        foreach (var item in dailyInterests)
                        {
                            for (int col = 1; col <= 2; col++)
                            {
                                worksheet.Cells[row, col].Style.Font.Size = 12;
                                //worksheet.Cells[row, col].Style.Font.Bold = true;
                                worksheet.Cells[row, col].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
                            }

                            //set row,column data
                            worksheet.Cells[row, 1].Value = item.LoanAccountNo;
                            worksheet.Cells[row, 2].Value = item.TranAmount;
                            worksheet.Cells[row, 3].Value = item.InterestRatePerDay;
                            worksheet.Cells[row, 4].Value = item.AmountPerDay;
                            worksheet.Cells[row, 5].Value = item.OriginalLoanAmount;
                            worksheet.Cells[row, 6].Value = item.Narration;
                            worksheet.Cells[row, 7].Value = item.DRCR;
                            worksheet.Cells[row, 8].Value = item.TranDate;

                            //toggle background color
                            //even row with ribbon style
                            if (row % 8 == 0)
                            {
                                worksheet.Cells[row, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                worksheet.Cells[row, 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(154, 211, 157));

                                worksheet.Cells[row, 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                worksheet.Cells[row, 2].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(154, 211, 157));

                            }
                            row++;
                        }

                        //step7: auto fit columns
                        worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();

                        //step8: convert the package as byte array
                        result = package.GetAsByteArray();

                    }

                    //step9: return byte array as a file
                    **//This is where the error is**
                    return File(result, "application/vnd.ms-excel", "test.xls");

                }

            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
techstack
  • 657
  • 7
  • 25
  • Does this answer your question? [Non-invocable member 'File' cannot be used like a method while generating Reports](https://stackoverflow.com/questions/35936628/non-invocable-member-file-cannot-be-used-like-a-method-while-generating-report) – Selim Yildiz Apr 05 '20 at 21:24
  • Is this method in a controller? – haldo Apr 05 '20 at 21:56
  • @SelimYıldız I moved it to the controller and no error. The method ran successfully. But I was expecting the excel file to download automatically. but nothing happened – techstack Apr 05 '20 at 22:11
  • @haldo I have moved to the controller. but no excel was download after running the method – techstack Apr 05 '20 at 22:12
  • Its ajax call from the view. An id is passed as a parameter to get a list of items from the DB. The list is successfully retrieved. – techstack Apr 05 '20 at 23:05
  • There is a table in the view. user clicks on any row and sends a request with id to the controller and method – techstack Apr 05 '20 at 23:14
  • It _cannot_ be an AJAX call (i.e. you want to open it in a new tab, not get it in an AJAX call). That is the mistake you are making. See https://forums.asp.net/t/2137185.aspx?The+ActionResult+with+return+File+doesn+t+show+up+when+calls+with+Ajax+why+ . – mjwills Apr 06 '20 at 00:22

0 Answers0