How to save workbook without showing save dialog w

Question

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

Answer 1

See MSDN Documentation

wbook.SaveAs(...);

Answer 2

wapp.DisplayAlerts = false;

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

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

Answer 4

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

Answer 5

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.

Answer 6

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.