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:

  • 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)?

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

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

    尝试这个:

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

    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

    链接地址: http://www.djcxy.com/p/6074.html

    上一篇: 复制Excel工作表与NPOI CopySheet; 副本总是空白

    下一篇: 如何将我的导出格式化为microsoft.office.interop.excel中的excel工作簿?