saving an excel file from datatable

Question

User-325797852 posted

Hello,

I am working on an excel based report using asp.net with C#. I have moved my datatable to an excel sheet using the interop library. My datatable is dt.

 Microsoft.Office.Interop.Excel.Worksheet Sheet1;

Microsoft.Office.Interop.Excel.Workbook WB1;

Sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)WB1.ActiveSheet; //binding the sheet to the workbook

int rowCount = 1;
        foreach (DataRow dr in dt.Rows)
        {
            rowCount += 1;
            for (int i = 1; i < dt.Columns.Count + 1; i++)
            {
                            Sheet1.Cells[rowCount+1, i] = dr[i - 1].ToString(); //writing the datarows
                
            }
           
        }

After this, I have written some code for formatting on the Sheet1. While this code runs, an excel sheet appears with gradual data population and formatting. I can save the file when the sheet gets completed.

Now this is when I am running the website at my local PC, when I would deploy the solution at the server I would certainly need to give a dialog box to the user so that he can save that sheet to his client PC.

Can anyone help me in developing a solution for that dialog box thing.

Answer 1

User-1793347006 posted

I suggest you save the excel file to a temporary location on the server using the interop library SaveAs function. The next step is to read the file using a BinaryReader and output it to the HttpResponse using the Response.BinaryWrite method, combined with the correct contant header.

Answer 2

User-325797852 posted

Actually due to some policy restrictions I can't save the file at server. I did use the SaveAs function at my local server, but then came the news to me that I can't save any files at the server. So what's the way to go now?