Excel interop:保存工作簿，不显示保存对话框。

I have to create a console application that exports a dataset to Excel. I've tried googling and looking at posts on this topic but haven't really found what I'm looking for. 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.

我必须创建一个控制台应用程序，将数据集导出到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.

saveas()的所有参数都是可选的，但是您可以使用Type。如果你想的话，大部分人都会错过。

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将为您设置该扩展名。

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas(v=VS.100).aspx describes each of the arguments.

aspx描述了每个参数。

#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方法。对于很多参数，尝试传递类型。除了第一个参数(文件名)外，所有参数都不存在。

#3

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

Add Conflict Resolution to XlSaveConflictResolution.xlLocalSessionChanges

向XlSaveConflictResolution.xlLocalSessionChanges添加冲突解决

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

并将应用程序的DisplayAlerts属性设置为false，这样窗口就不会显示。

see https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlsaveconflictresolution.aspx for more info.

更多信息请参见https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlsaveconflictresolution.aspx。

#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.

设置应用程序。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或superior不能打开文件。

#1