How to save workbook without showing save dialog w

问题:

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.

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

回答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.

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.

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

回答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 ).

回答3:

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

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.

回答4:

See MSDN Documentation

wbook.SaveAs(...);

回答5:

wapp.DisplayAlerts = false;

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

回答6:

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

回答7:

Use the DisplayAlerts property. Its very simple and fast.

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.

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.