How to save an excel file in C#

Question

I'm creating an MVC controller action where JSON data passed in to the method is to be written to an excel file. Right now, I'm testing out the functionality by using hardcoded data from a data table based on the example from this blog post.

Here is the code I have:

[HttpPost]
        public ActionResult ExportData()
            {
                Microsoft.Office.Interop.Excel.Application excel;
                Microsoft.Office.Interop.Excel.Workbook worKbooK;
                Microsoft.Office.Interop.Excel.Worksheet worKsheeT;
                Microsoft.Office.Interop.Excel.Range celLrangE;

                try
                {
                    excel = new Microsoft.Office.Interop.Excel.Application();
                    excel.Visible = false;
                    excel.DisplayAlerts = false;
                    worKbooK = excel.Workbooks.Add(Type.Missing);


                    worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
                    worKsheeT.Name = "StudentRepoertCard";

                    worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 8]].Merge();
                    worKsheeT.Cells[1, 1] = "Student Report Card";
                    worKsheeT.Cells.Font.Size = 15;


                    int rowcount = 2;

                    foreach (DataRow datarow in ExportToExcel().Rows)
                    {
                        rowcount += 1;
                        for (int i = 1; i <= ExportToExcel().Columns.Count; i++)
                        {

                            if (rowcount == 3)
                            {
                                worKsheeT.Cells[2, i] = ExportToExcel().Columns[i - 1].ColumnName;
                                worKsheeT.Cells.Font.Color = System.Drawing.Color.Black;

                            }

                            worKsheeT.Cells[rowcount, i] = datarow[i - 1].ToString();

                            if (rowcount > 3)
                            {
                                if (i == ExportToExcel().Columns.Count)
                                {
                                    if (rowcount % 2 == 0)
                                    {
                                        celLrangE = worKsheeT.Range[worKsheeT.Cells[rowcount, 1], worKsheeT.Cells[rowcount, ExportToExcel().Columns.Count]];
                                    }

                                }
                            }

                        }

                    }

                    celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[rowcount, ExportToExcel().Columns.Count]];
                    celLrangE.EntireColumn.AutoFit();
                    Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;
                    border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                    border.Weight = 2d;

                    celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[2, ExportToExcel().Columns.Count]];

                    worKbooK.SaveAs("\\root\\test.xlsx"); 
                    worKbooK.Close();
                    excel.Quit();

                }
                catch (Exception ex)
                {
                    return Json(new { saveSuccess = false }, JsonRequestBehavior.AllowGet);

                }
                finally
                {
                    worKsheeT = null;
                    celLrangE = null;
                    worKbooK = null;

                }

                return Json(new { saveSuccess = true }, JsonRequestBehavior.AllowGet);
            }
            //private void Form1_Load(object sender, EventArgs e)
            //{
            //    dataGridView1.DataSource = ExportToExcel();
            //}
            public System.Data.DataTable ExportToExcel()  
            {  
                System.Data.DataTable table = new System.Data.DataTable();  
                table.Columns.Add("ID", typeof(int));  
                table.Columns.Add("Name", typeof(string));  
                table.Columns.Add("Sex", typeof(string));  
                table.Columns.Add("Subject1", typeof(int));  
                table.Columns.Add("Subject2", typeof(int));  
                table.Columns.Add("Subject3", typeof(int));  
                table.Columns.Add("Subject4", typeof(int));  
                table.Columns.Add("Subject5", typeof(int));  
                table.Columns.Add("Subject6", typeof(int));  
                table.Rows.Add(1, "Amar", "M", 78, 59, 72, 95, 83, 77);  
                table.Rows.Add(2, "Mohit", "M", 76, 65, 85, 87, 72, 90);  
                table.Rows.Add(3, "Garima", "F", 77, 73, 83, 64, 86, 63);  
                table.Rows.Add(4, "jyoti", "F", 55, 77, 85, 69, 70, 86);  
                table.Rows.Add(5, "Avinash", "M", 87, 73, 69, 75, 67, 81);  
                table.Rows.Add(6, "Devesh", "M", 92, 87, 78, 73, 75, 72);  
                return table;  
            }  

Right now, the code works up until the point where the save happens. For some reason, the file location is not found. I was assuming that the name of the file had to be listed at the end of the path after the containing folder in order to provide the name, but maybe this isn't the correct way to specify the file path.

What I actually need to do is to allow the user to choose the file location in file explorer, provide a name, and then save the file. Since this is the case, the file path would have to be provided dynamically. I've looked at many SO posts and articles but I haven't seen a clear example of how to do this.

How should the code be modified for the user to be able to specify the file name and path?

Answer 1

You cannot choose to save the file from their browser. You need to serve up the file and let them download it and save it where they like.

Also, the server you want a production ASP.NET application deployed to probably doesn't have a copy of Excel installed (and even if it does interop gets a little messy IMHO) so you probably want to use a openXml library such as EPPlus instead.

This would let you do something like this:

public IActionResult ExportData()
{
    using (var excel = new ExcelPackage())
    {
        var wks = excel.Workbook.Worksheets.Add("StudentReportCard");
        wks.Cells[1,1].LoadFromCollection(GetStudentRecords(), PrintHeaders:true);
        return File(excel.GetAsByteArray(),"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "export.xlsx");
    };
}
private static IEnumerable<StudentRecord> GetStudentRecords()
{
    yield return new StudentRecord
    {
        Id = 1,
        Name = "John",
        Subject = "Maths",
        Score = 77.9
    };
    yield return new StudentRecord
    {
        Id = 2,
        Name = "Jane",
        Subject = "Maths",
        Score = 78.9
    };
    yield return new StudentRecord
    {
        Id = 3,
        Name = "Jo",
        Subject = "Maths",
        Score = 99.9
    };
}

Which sends a file like this named 'export.xlsx' for the user to save from their browser: