如何在不显示Excel互操作的保存对话框的情况下保存工作簿？

I have to create a Console application that exports a DataSet to Excel. The problem is that it shouldn't pop up the save window, it should automatically create the Excel file. So far I have the following code, but I don't know how to make it save automatically. Would appreciate any help.

我必须创建一个控制台应用程序,将DataSet导出到Excel。问题是它不应该弹出保存窗口,它应该自动创建Excel文件。到目前为止,我有以下代码,但我不知道如何使其自动保存。非常感谢任何帮助。

public static void CreateWorkbook(DataSet ds, String path)
{
    int rowindex = 0;
    int columnindex = 0;

    Microsoft.Office.Interop.Excel.Application wapp = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Worksheet wsheet;
    Microsoft.Office.Interop.Excel.Workbook wbook;

    wapp.Visible = false;

    wbook = wapp.Workbooks.Add(true);
    wsheet = (Worksheet)wbook.ActiveSheet;

    try
    {
        for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
        {
            wsheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;

        }

        foreach (DataRow row in ds.Tables[0].Rows)
        {
            rowindex++;
            columnindex = 0;
            foreach (DataColumn col in ds.Tables[0].Columns)
            {
                columnindex++;
                wsheet.Cells[rowindex + 1, columnindex] = row[col.ColumnName];
            }
        }
    }
    catch (Exception ex)
    {
        String err = ex.Message;
    }
    wapp.UserControl = true;
}

7 个解决方案

#1

All of the arguments to WorkBook.SaveAs() are optional, but you can just use Type.Missing for most of them if you want to.

WorkBook.SaveAs()的所有参数都是可选的,但如果您愿意,可以对大多数参数使用Type.Missing。

The typical call would look like:

典型的通话看起来像:

wbook.SaveAs("c:\\temp\\blah", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
            false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wbook.Close();

Note that I didn't include the file extension, Excel will set that for you.

请注意,我没有包含文件扩展名,Excel会为您设置。

Workbook.SaveAs Method (Microsoft.Office.Tools.Excel) | Microsoft Docs describes each of the arguments.

Workbook.SaveAs方法(Microsoft.Office.Tools.Excel)| Microsoft Docs描述了每个参数。

#2

Try to call the SaveAs method of the workbook. For the lot of parameter, try to pass Type.Missing to all parameters but the first ( the file name ).

尝试调用工作簿的SaveAs方法。对于大量参数,尝试将Type.Missing传递给所有参数,但第一个(文件名)。

#3

Add ConflictResolution to XlSaveConflictResolution.xlLocalSessionChanges
and set the application's DisplayAlerts property to false so the window won't show.

将ConflictResolution添加到XlSaveConflictResolution.xlLocalSessionChanges并将应用程序的DisplayAlerts属性设置为false,这样窗口就不会显示。

m_xlApp.DisplayAlerts = false;

// Quit Excel and clean up.
m_xlWorkbook.SaveAs(Filename: m_xlFilePath, FileFormat: excelFileExtension,
Password: false, ReadOnlyRecommended: XlSaveAsAccessMode.xlNoChange,
ConflictResolution: XlSaveConflictResolution.xlLocalSessionChanges);  

m_xlWorkbook.Close();

see XlSaveConflictResolution Enum (Microsoft.Office.Interop.Excel) | Microsoft Docs for more info.

请参阅XlSaveConflictResolution Enum(Microsoft.Office.Interop.Excel)| Microsoft Docs获取更多信息。

#4

See MSDN Documentation

请参阅MSDN文档

wbook.SaveAs(...);

#5

wapp.DisplayAlerts = false;

setting Application.DisplayAlerts property to false will stop displaying all alerts for all of its workbooks.

将Application.DisplayAlerts属性设置为false将停止显示其所有工作簿的所有警报。

#6

Use open xml sdk to generate documents instead of automation; it is much more reliable.

使用open xml sdk生成文档而不是自动化;它更可靠。

#7

Use the DisplayAlerts property. Its very simple and fast.

使用DisplayAlerts属性。它非常简单快速。

private void SaveAs(Excel.Workbook WorkBook, string FileName)
    {
        m_Saving = true;
        try
        {
            if (Global.CreatingCopy)
                this.ExcelApp.DisplayAlerts = false;

            WorkBook.SaveAs(FileName);
        }
        finally
        {
            m_Saving = false;
            if (this.ExcelApp.DisplayAlerts == false)
                this.ExcelApp.DisplayAlerts = true;
        }
    }

Never let the Excel define the kind of file when you're going to save the file. Because it's possible that the format will be changed.

当您要保存文件时,切勿让Excel定义文件类型。因为格式可能会被更改。

If original doc was .xls and the current user has the office 2013 the default format its .xlsx and then the Excel will convert the file to .xlsx and the users tha doesnt have the Excel 2010 or superior cant open the file.

如果原始doc是.xls并且当前用户将office 2013设置为默认格式,则为.xlsx,然后Excel将文件转换为.xlsx,并且用户不会使Excel 2010或上级无法打开文件。

#1