Click here to Skip to main content
15,891,864 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have this export function that allows me to export 2 grid views into 2 separated worksheets in one excel.

But my problems are:


1. How can I have a usual popup window like usual download when I click on export button to prompt user to OPEN, SAVE AS, CANCEL of the download instead of saving it to a specific location (currently what I am doing in my codes)?

2. How can I set a code to enable wraptext = true for all my cells and also auto format the column height and width to fixed all the text so that it does not show ###### for date as an example when column width is too small when excel is opened.

C#
protected void EXPORT_BUTTON_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
 
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook  =  app.Workbooks.Add(Type.Missing);
 
String DT1 = "Data table 1";
String DT2 = "Data table 2";
 
ExportToExcel(app, workbook, Gridview1, DT1, 1);
 
ExportToExcel(app, workbook, Gridview2, DT2, 2);   
 
}
public void ExportToExcel(Microsoft.Office.Interop.Excel._Application app, Microsoft.Office.Interop.Excel._Workbook workbook, GridView gridview, string SheetName, int sheetid)
{

 
    // see the excel sheet behind the program
    app.Visible = true;
 
    // get the reference of first sheet. By default its name is Sheet1.
    // store its reference to worksheet

    worksheet = (Excel.Worksheet)workbook,Worksheets.Add();
 
    // changing the name of active sheet
    worksheet.Name = SheetName;
 
    // storing header part in Excel
    for (int i = 1; i < gridview.Columns.Count + 1; i++)
    {
        worksheet.Cells[1, i] = gridview.Columns[i - 1].HeaderText;
    }
 

 
    // storing Each row and column value to excel sheet
    for (int i = 0; i < gridview.Rows.Count - 1; i++)
    {
        for (int j = 0; j < gridview.Columns.Count; j++)
        {
            worksheet.Cells[i + 2, j + 1] = gridview.Rows[i].Cells[j].Text.ToString();
        }
    }
    //save the application
    
    workbook.SaveAs(@"C:\Users\test\Desktop\Test\" + datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                }
}
Posted
Updated 14-Oct-15 21:38pm
v5

You open file save dialog before saving, collect the selected path from the dialog and then call Save on that path.
 
Share this answer
 
After saving it to a file on the server you can then write the file to the filestream. Google "asp.net download file client" for code samples. However what you're doing is not supported and is bound to give you problems as Excel is a desktop app and not suitable for automation in a multi-threaded environment.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900