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

我有这个导出功能,允许我在一个excel中将2个网格视图导出为2个独立的工作表。

但我的问题是:

  • 当我点击导出按钮以提示用户打开,另存为,取消下载而不是将其保存到特定位置(目前我在我的代码中正在执行的操作)时,如何才能像平常下载一样使用常见的弹出窗口?

  • 我如何设置代码以启用所有单元格的wraptext = true,并自动格式化列高和宽以固定所有文本,以便在列宽不变时不显示######作为示例excel打开时太小。

  • protected void EXPORT_BUTTON_Click(object sender,EventArgs e){Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); //在Excel应用程序中创建新的WorkBook 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);
                }
    }
    

    修改Excel文件的路径以保存到虚拟路径,如下所示

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

    改变这个

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

    尝试以下代码以显示另存为对话框

    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];
    

    如Laxmikant指定的那样修改您的方法“ExportToExcel”的代码,如下所示。

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

    我删除了这两行代码,现在不需要参数“Sheetid”

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

    希望这能解决你的问题

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

    上一篇: How do I format my export to excel workbook in microsoft.office.interop.excel?

    下一篇: C# method failing to function for no known reason