Export to CSV from SQL is empty in ASP.NET MVC using C#

Question

I want to get SQL data and export it to a .CSV file, but when exporting my data from SQL to Excel, the code can export the .CSV file but it is empty. I have checked the SQL command in database, it does get data from database.

What's wrong with my code?

This is the code:

Controller.cs :

        .......
        string SQL = "select RoomId, Name,Tel,Sdate,Edate from StayDormApplications";
        DataTable qdt = new DataTable();
        qdt = Co.GetData(SQL);
        var roommapplications = ConvertDataTable<OutputClass>(qdt).AsQueryable();       
        var venueregisters = _db.StayDormApplications.OrderByDescending(p => p.Sdate).AsQueryable();

        if (mode == "export")
        {              
           var export = ConvertList<OutputClass>(roommapplications.OrderByDescending(x => x.Sdate).ToList());    
            DateTime now = DateTime.Now;
            string today = now.Year.ToString() + "_" + now.Month.ToString("00") + "_" + now.Day.ToString("00");
            new ExcelFactory().ExportExcel("outputCSV" + today, export, "/upfiles/ExcelTemplate/HA04.xlsx", Response);
            string mode2 = "";
            SndaFun.SetFilterString(ref mode2, mode);
            ViewBag.mode = mode2;
        }

        //end export
        return View(dormapplicationList.OrderByDescending(p => p.Sdate)
           .ToPagedList(currentPageIndex, DefaultPageSize));
    }

 .....

    public static DataTable ConvertList<T>(List<T> items)
    {
        DataTable dataTable = new DataTable(typeof(T).Name);
      
        //Get all the properties
        PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

        foreach (PropertyInfo prop in Props)
        {
            //Defining type of data column gives proper data table 
            var type = (prop.PropertyType.IsGenericType &&
                        prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>)
                ? Nullable.GetUnderlyingType(prop.PropertyType)
                : prop.PropertyType);
            //Setting column names as Property names
            dataTable.Columns.Add(prop.Name, type);
        }

        foreach (T item in items)
        {
            var values = new object[Props.Length];
            for (int i = 0; i < Props.Length; i++)
            {
                //inserting property values to datatable rows
                values[i] = Props[i].GetValue(item, null);
            }

            dataTable.Rows.Add(values);
        }

        //put a breakpoint here and check datatable
        return dataTable;
    }

 public void ExportExcel(string fileName, DataTable dt, string path, HttpResponseBase response)
    {
        FileStream file = new FileStream(HostingEnvironment.MapPath(path), FileMode.Open, FileAccess.Read);
        XSSFWorkbook workbook = DataTableToWorkbook(new XSSFWorkbook(file), dt);
        response.Clear();
       
        MemoryStream ms = new MemoryStream();
        workbook.Write(ms);
       string.Format("attachment; filename=" + fileName + ".xlsx"));
        response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + System.Web.HttpUtility.UrlEncode("" + fileName + "" + ".xlsx", System.Text.Encoding.UTF8)));
        //response.ContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document";
       
        response.BinaryWrite(ms.ToArray());
        workbook.Close();
        ms.Close();
        ms.Dispose();
        response.End();
    }

My ExcelTemplate/HA04.xlsx has five columns:

RoomId, Name, Tel, Sdate, Edate

Work through it with the debugger, find out where the data goes missing — Caius Jard, Apr 07 '22 at 07:10
I suggest you try closing the workbook before you do response.BinaryWrite, just in case closing the book flushes data to the memory stream — Caius Jard, Apr 07 '22 at 07:11
I have try to closing the workbook before to response.BinaryWrite, but stilll out put a empty excel — georgetovrea, Apr 19 '22 at 08:32
It should be fairly simple to debug: does `ms.ToArray()` produce an array of more than 0 bytes? — Caius Jard, Apr 19 '22 at 09:58

score 0 · Answer 1 · answered Apr 07 '22 at 01:58

0

You can refer this answer to this link , link2.

Getting data from database, convert it by DataTable and use the code from the link to export in csv or excel.

answered Apr 07 '22 at 01:58

John Lao

3
1
3

as you see,i have convert it by datatable ,but stlll get empty csv ouput – georgetovrea Apr 07 '22 at 05:08

Export to CSV from SQL is empty in ASP.NET MVC using C#

1 Answers1