Export excel file using NPOI package

Question

I'm trying to export datatable to an export file. until now that was successful as long as I am doing it to a specific directory. what a user should be able to do basically be able to choose the directory to download the file in excel format. i made an apart class for the Export function as follow:

[Authorize]
public class Export
{
    

    //Export Method
    public static void ExportExcel(string fileName, decimal projectId, ModelContext context)
    {
        ExcelPackage pck = new ExcelPackage();
        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Project");

        var surveys = context.Survey.Where(x => x.ProjectId == projectId)
            .Include(s=> s.Question);

        
        fileName = @("D:\Projects\CodeBooks\Trunk\100-Documents\Codebooks") //it is a specific directory.


        ws.Cells["A1"].Value = "SURVEY_ID";
        ws.Cells["B1"].Value = "PAGE";
        ws.Cells["C1"].Value = "RANKING";
        ws.Cells["D1"].Value = "PARENT_ID";
        ws.Cells["E1"].Value = "ID";
        ws.Cells["F1"].Value = "ITEMTYPE";
        ws.Cells["G1"].Value = "CONTROLTYPE";
        ws.Cells["H1"].Value = "DISPLAYTYPE";
        ws.Cells["J1"].Value = "VARIABLE_DESCRIPTION";
        ws.Cells["K1"].Value = "VARIABLE_NAME";
        ws.Cells["L1"].Value = "VARIABLE_LABEL";
        ws.Cells["M1"].Value = "VALUE_LABEL_ID";
        ws.Cells["N1"].Value = "MISSING_VALUE_ID";
        ws.Cells["O1"].Value = "IS_REQUIRED";
        ws.Cells["P1"].Value = "DATA_TYPE";
        ws.Cells["Q1"].Value = "VARIABLE_LENGTH";
        ws.Cells["R1"].Value = "NR_OF_DECIMALS";
        ws.Cells["S1"].Value = "COMMENT";
        ws.Cells["T1"].Value = "COMMENT_TYPE";
        ws.Cells["U1"].Value = "SHOW_ALL_FOR_STUDY";
        ws.Cells["V1"].Value = "ANSWER_FONT";
        ws.Cells["W1"].Value = "IS_DELETE_QUESTION";
        ws.Cells["X1"].Value = "IS_STOP_QUESTION";
        ws.Cells["Y1"].Value = "ROUTING_TYPE";
        ws.Cells["Z1"].Value = "ROUTING_VALUE";
        ws.Cells["AA1"].Value = "SHOW";

        int rowStart = 2;
        foreach (var survey in surveys)
        {

            foreach (var item in survey.Question)
            {
                ws.Cells[String.Format("A{0}", rowStart)].Value = survey.IdSurvey;
                ws.Cells[String.Format("B{0}", rowStart)].Value = item.Page;
                ws.Cells[String.Format("C{0}", rowStart)].Value = item.Ranking;
                ws.Cells[String.Format("D{0}", rowStart)].Value = item.ParentId;
                ws.Cells[String.Format("E{0}", rowStart)].Value = item.IdQuestion;
                ws.Cells[String.Format("F{0}", rowStart)].Value = item.Itemtype;
                ws.Cells[String.Format("G{0}", rowStart)].Value = item.Controltype;
                ws.Cells[String.Format("H{0}", rowStart)].Value = item.Displaytype;
                ws.Cells[String.Format("J{0}", rowStart)].Value = item.VariableDescription;
                ws.Cells[String.Format("K{0}", rowStart)].Value = item.VariableName;
                ws.Cells[String.Format("L{0}", rowStart)].Value = item.VariableLabel;
                ws.Cells[String.Format("M{0}", rowStart)].Value = item.ValueLabelId;
                ws.Cells[String.Format("N{0}", rowStart)].Value = item.MissingValueId;
                ws.Cells[String.Format("O{0}", rowStart)].Value = item.IsRequired;
                ws.Cells[String.Format("P{0}", rowStart)].Value = item.DataType;
                ws.Cells[String.Format("Q{0}", rowStart)].Value = item.VariableLength;
                ws.Cells[String.Format("R{0}", rowStart)].Value = item.NrOfDecimals;
                ws.Cells[String.Format("S{0}", rowStart)].Value = item.Comment;
                ws.Cells[String.Format("T{0}", rowStart)].Value = item.CommentType;
                ws.Cells[String.Format("U{0}", rowStart)].Value = item.ShowAllForStudy;
                ws.Cells[String.Format("V{0}", rowStart)].Value = item.AnswerFont;
                ws.Cells[String.Format("W{0}", rowStart)].Value = item.IsDeleteQuestion;
                ws.Cells[String.Format("X{0}", rowStart)].Value = item.IsStopQuestion;
                ws.Cells[String.Format("Y{0}", rowStart)].Value = item.RoutingType;
                ws.Cells[String.Format("Z{0}", rowStart)].Value = item.RoutingValue;
                ws.Cells[String.Format("AA{0}", rowStart)].Value = item.Show;
                rowStart++;
            }
            ws.Cells["A:AZ"].AutoFitColumns();

            
        }


        pck.SaveAs(new FileInfo(fileName));

    }
}

and then i call this method in the accossiated controller:

        public IActionResult Export(IFormCollection form, decimal? id)
    {
        var project = _context.Project.First(x => x.ProjectId == id).ProjectId;
        var fileName = form["CodeBoek.xlsx"];
        
        Models.Export.ExportExcel(fileName, project, _context);
        return RedirectToAction(nameof(Index));
    }

this action method is as well being called in the associated view. and since then i am stuck and i would like to receive some help. i have tried to apply various methods but everytime i get an error.

are you creating a download functionality? why not return `FileResult` as done [here](https://www.c-sharpcorner.com/article/fileresult-in-asp-net-core-mvc2/) — Pirate, Dec 04 '20 at 14:24
the is indeed helpful. Still, in the Export class/function, the directory itself (fileName) is a specific directory and the program won't run without it so I want to change it to user input maybe? — Zeus Kaka, Dec 05 '20 at 09:14

Export excel file using NPOI package

0 Answers0