How to save workbook without showing save dialog with Excel interop?

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

Igby Largeman · Accepted Answer · 2018-12-20T21:29:57.663

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.

score 5 · Answer 2 · edited Sep 18 '18 at 11:17

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.

Felice Pollano · Answer 3 · 2011-08-10T15:05:35.597

5

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

edited Aug 10 '11 at 15:05

answered Aug 10 '11 at 14:54

Felice Pollano

32,832
9
75
115

ya i tried that but it takes so many: parameters void SaveAs(object Filename, object FileFormat, object Password, object WriteResPassword, object ReadOnlyRecommended, object CreateBackup, XlSaveAsAccessMode AccessMode, object ConflictResolution, object AddToMru, object TextCodepage, object TextVisualLayout, object Local); that i dont know how to use it... – ha1ogen Aug 10 '11 at 14:57
I think just the first two are mandatory, all the other can be used as missing parameters. – Felice Pollano Aug 10 '11 at 15:03
so what would a "object Filename" be? can you give me an example – ha1ogen Aug 10 '11 at 15:07
@ha1: just use a string. `Object` is the base type for _all_ other .NET types, so you can pass any type in place of an `Object` type argument. – Igby Largeman Aug 10 '11 at 15:11

score 3 · Answer 4 · answered Aug 30 '15 at 10:25

3

wapp.DisplayAlerts = false;

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

answered Aug 30 '15 at 10:25

HuBeZa

4,715
3
36
58

score 3 · Answer 5 · answered Aug 10 '11 at 14:54

3

See MSDN Documentation

wbook.SaveAs(...);

answered Aug 10 '11 at 14:54

Stephan Bauer

9,120
5
36
58

score 2 · Answer 6 · edited May 02 '13 at 18:28

2

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

edited May 02 '13 at 18:28

JoshDM

4,939
7
43
72

answered May 02 '13 at 18:00

user2344263

21
1

1

could you please more details to the response and the solution – Olimpiu POP May 02 '13 at 18:28

score 2 · Answer 7 · answered Jan 28 '16 at 11:21

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.

How to save workbook without showing save dialog with Excel interop?

7 Answers7

Linked

Related