如何在microsoft.office.interop.excel中设置导出到excel工作簿的格式?

本文关键字：excel 工作簿 格式 microsoft office interop 设置 | 更新日期: 2023-09-27 18:17:38

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

但我的问题是:

  1. 当我点击导出按钮提示用户打开，另存为，取消下载而不是将其保存到特定位置(目前我在我的代码中所做的)时，我怎么能有一个像往常一样的弹出窗口?

  2. 我如何设置一个代码，使wraptext = true为我所有的单元格，也自动格式化列的高度和宽度，以固定所有的文本，使它不显示######日期为例，当列宽度太小时，excel打开

protected void EXPORT_BUTTON_Click(对象发送者，EventArgs e){Microsoft.Office.Interop.Excel。_Application app = new Microsoft.Office.Interop.Excel.Application();//在Excel应用程序中创建新的工作簿Microsoft.Office.Interop.Excel。

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

如何在microsoft.office.interop.excel中设置导出到excel工作簿的格式?

修改excel文件保存到虚拟路径，如下所示

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

将其改为

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;

希望这将解决您的问题

您没有在工作簿中添加工作表

尝试下面的代码

worksheet = (Excel._Worksheet) excel . worksheets . add ();

工作表。Name = SheetName;

使用range

设置标题
  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;
相关文章：