How do I format my export to excel workbook in microsoft.office.interop.excel?

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.

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:UserstestDesktopTest" + 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);
            }
}

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Modify the path of the excel file to save to a virtual path as follows

workbook.SaveAs(@"C:UserstestDesktopTest" + 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);

change this to

workbook.SaveAs(@"~/ExcelFiles/Filename.xlsx" + 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);

Try the following code for displaying save as dialog

String FileName = "FileName.xlsx";
String FilePath = "~/ExcelFiles/FileName.xlsx"; 
System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
response.ClearContent();
response.Clear();
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
response.AddHeader("Content-Disposition", "attachment; filename=" + FileName + ";");
response.TransmitFile(FilePath);
response.Flush();
response.End();

Method 2

Try this:

Excel.Worksheet worksheet =(Excel.Worksheet)workbook.Worksheets["Sheet" + sheetid];

Method 3

as specified by Laxmikant modify the code of your method “ExportToExcel” as follows.

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;

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();
    }
}

I removed these two lines of code and now there is no need of the parameter “Sheetid”

Excel.Worksheet worksheet =(Excel.Worksheet)workbook.Worksheets["Sheet" + sheetid];
worksheet = workbook.ActiveSheet;

Hope this will solve your issue

Method 4

you haven’t added worksheet in Workbook

try below code

worksheet = (Excel._Worksheet)oXL.Worksheets.Add();

worksheet.Name = SheetName;

for more info check https://geeksarray.com/blog/how-to-export-sql-data-to-excel-using-microsoft-office-interop

set your headers using rangs

  string[] colNames = new string[gv.Columns.Count];

  int col = 0;

  foreach(gvvolumns dc in GridView.Columns)
    colNames[col++] = dc.ColumnName;

  char lastColumn = (char)(65 + dtProducts.Columns.Count - 1);

  oSheet.get_Range("A1", lastColumn + "1").Value2 = colNames;
  oSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
  oSheet.get_Range("A1", lastColumn + "1").VerticalAlignment 
        = Excel.XlVAlign.xlVAlignCenter;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0