Datatable to Excel Interop VS other approaches; required popup with Save As / Open

Question

I've implemented the following approach that seems to be working just fine however it saves the file into pre-defined location, instead I'd like it to give an option to the user, just like any regular download popup: Open, Save, Save As. Any idea?

---

In order to overcome “Current thread must be set to single thread apartment (STA) mode before OLE calls can be made” exception, Threading had to be implemented, see EDIT!

EDIT:

1)

 using System.Threading;

2)

[STAThread]
private static void ExportToExcel(DataTable dt)
{
    Thread t = new Thread(SaveIt);
    t.SetApartmentState(ApartmentState.STA);
    t.Start(dt);
}

3)

static void SaveIt(Object dt)
{
    DataTable table = (DataTable)dt;
    System.Windows.Forms.SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog();

    if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        SaveName = sfd.FileName;
    else
        SaveName = "ResourceProfile";
    workbook.SaveAs(.....
} 

END OF EDIT.

private static void ExportToExcel(DataTable dt)
{
    Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
    Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(true);

    int iCol = 0;
    foreach (DataColumn c in dt.Columns)
    {
        iCol++;
        excel.Cells[1, iCol] = c.ColumnName;
    }

    int iRow = 0;
    foreach (DataRow r in dt.Rows)
    {
        iRow++;

        // add each row's cell data...
        iCol = 0;
        foreach (DataColumn c in dt.Columns)
        {
             iCol++;
             excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
        }
    }

    object missing = System.Reflection.Missing.Value;

    DateTime CurrentDate = DateTime.Now;          

    String CurDate = CurrentDate.ToShortDateString().Replace("/",".");

    workbook.SaveAs("ResourceProfile-" + CurDate + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);

    excel.Visible = true;
    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
    ((Microsoft.Office.Interop.Excel._Worksheet)worksheet).Activate();

    ((Microsoft.Office.Interop.Excel._Application)excel).Quit();
}

Answer 1

Just add a SaveFileDialog to your application, and get the file name from the user:

String SaveName;

if saveFileDialog1.ShowDialog  = DialogResult.OK 
{
  SaveName := saveFileDialog1.FileName;
  workBook.SaveAs(SaveName, ...);
}

You can configure the default folder location, file extension, and other information by setting various properties of the SaveFileDialog. You can even set the SaveDialog.FileName to the default name you've created above before you ShowDialog for them to accept or modify.

You can also do it using excel.Application.get_FileDialog - here's an example:

Microsoft.Office.Core.FileDialog fd = 
  excel.Application.get_FileDialog(Microsoft.Office.Core.MsoFileDialogType.msoFileDialogSaveAs);

fd.AllowMultiSelect = true; 
fd.Filters.Clear(); 
fd.Filters.Add("Excel Files", "*.xls;*.xlw", missing); 
fd.Filters.Add("All Files", "*.*", missing); 

if (fd.Show() != 0)
{
  fd.Execute();
}