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;
}
}