Excel Interop - How to prompt the user to save the file?

[mcam9]

Hi guys i have an invoicing application (c#) that creates an excel workbook. At the moment i am using:

oWB.SaveAs("D:\\" + strFile, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, null, null, null);

This automatically saves to my D:\ (still testing the application).

Any ideas?

[tlhintoq]

Use a "SaveFileDialog"

[mcam9]

@tlhintoq
How do incorporate that into my code above?

[tlhintoq]

@mcam9
You've only provided one line of code so I can't exactly tell where to insert...

• Make a new SaveFileDialog
• Show it
• Get the path returned from it
• Use that path as the new save path, in place of your hard coded "D:\" + FileName

[mcam9]

Here is my code:

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Diagnostics;
using System.Reflection;
using Excel; //= Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using System.IO;
using System.Text;
//using System.Collections;
using System.Collections.Generic;
 
 
 
 
namespace ANINVWebApp.BusinessLayer
{
    public class CreateExcel
    {
 
 
 
 
        public static void CreateXL(Guid ID, String NTID)
        {
            Excel.Application oXL = null;
            Excel._Workbook oWB = null;
            Excel._Worksheet oSheet = null;
            //Excel.Sheets NewSheet = null;
            Excel.Range oRng = null;
            oXL = new Excel.Application();
            oXL.Visible = false;
            string fromDate = "";
            string toDate = "";
            string invoiceNo = "";
            string foreName = "";
            string surName = "";
            string phone = "";
            oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
            List<BillingPlanDA> dates = new List<BillingPlanDA>();
            dates = BillingPlan.GetBillingDates(ID);
            foreach (BillingPlanDA rec in dates)
            {
                fromDate = rec.BillingFrom.ToString("MMM dd, yyyy");
                toDate = rec.BillingTo.ToString("MMM dd, yyyy");
                invoiceNo = rec.InvoiceNumber.ToString();
 
            }
 
            List<ProjectInvoiceDA> manager = new List<ProjectInvoiceDA>();
            manager = ProjectInvoice.GetManager(NTID);
            foreach (ProjectInvoiceDA detail in manager)
            {
                foreName = detail.Forename.ToString();
                surName = detail.Surname.ToString();
                phone = detail.Telephone.ToString();
 
 
            }
 
 
 
            List<ProjectInvoiceDA> proj = new List<ProjectInvoiceDA>();
            proj = ProjectInvoice.GetProjectInvoices(ID, NTID);
            int counter2 = 1;
 
            foreach (ProjectInvoiceDA rec in proj)
            {
 
 
                List<EmployeeInvoiceDA> records = new List<EmployeeInvoiceDA>();
                records = EmployeeInvoice.GetEmployeeInvoices(rec.ProjectInvoiceId);
                string ProjCode = rec.ProjectCode.ToString();
                string TeamName = "";
                string CostCenter = rec.CostCenter.ToString();
 
 
 
 
 
                try
                {
                    GC.Collect();// clean up any other excel guys hangin' around...
                    //Get a new workbook.
 
                    oWB.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    oSheet = (Excel._Worksheet)oWB.ActiveSheet;
 
 
                    string strConnect = System.Configuration.ConfigurationManager.AppSettings["connectString"];
 
                    oSheet.get_Range("A1", "Z3").Font.Bold = true;
                    oSheet.get_Range("A16", "L16").Font.Bold = true;
 
 
 
 
 
                    //oSheet.get_Range("A1", "A2").C
                    oRng = oSheet.get_Range("A4", "J4");
                    oRng.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Excel.Constants.xlSolid;
                    oRng = oSheet.get_Range("A14", "J14");
                    oRng.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = Excel.Constants.xlSolid;
                    oRng.Borders[XlBordersIndex.xlEdgeTop].LineStyle = Excel.Constants.xlSolid;
 
 
                    oSheet.Cells[7, 8] = "Invoice Number:";
                    oSheet.Cells[7, 10] = invoiceNo;
                    oSheet.Cells[8, 8] = "Invoice Date:";
                    oSheet.Cells[8, 10] = toDate;
                    oSheet.Cells[10, 8] = "Project Number: ";
                    oSheet.Cells[10, 10] = ProjCode;
                    oSheet.Cells[14, 1] = "For services rendered between " + fromDate + " - " + toDate;
                    oSheet.Cells[16, 1] = "PO";
                    oSheet.Cells[16, 2] = "Service No";
                    oSheet.Cells[16, 3] = "Grade";
                    oSheet.Cells[16, 4] = "Employee Name";
                    oSheet.Cells[16, 5] = "Cost Center";
                    oSheet.Cells[16, 6] = "Internal Order #";
 
                    oSheet.Cells[16, 7] = "Project #";
                    oSheet.Cells[16, 8] = "Hours";
                    oSheet.Cells[16, 9] = "Rate";
                    oSheet.Cells[16, 10] = "Total Due";
 
                    int counter = 17;
                    double total = 0;
                    double hours = 0;
                    foreach (EmployeeInvoiceDA record in records)
                    {
 
 
                        oSheet.Cells[counter, 1] = record.PO.ToString();
                        oSheet.Cells[counter, 2] = record.ServiceNo.ToString();
                        oSheet.Cells[counter, 3] = record.Grade.ToString();
                        oSheet.Cells[counter, 4] = record.EmployeeName.ToString();
                        oSheet.Cells[counter, 5] = CostCenter;
                        oSheet.Cells[counter, 6] = "";
                        oSheet.Cells[counter, 7] = record.ProjectNo.ToString();
                        oSheet.Cells[counter, 8] = record.Hours.ToString();
                        oSheet.Cells[counter, 9] = record.BillingRate.ToString("c");
                        oSheet.Cells[counter, 10] = record.TotalDue.ToString("c");
 
                        hours = hours + Convert.ToDouble(record.Hours);
                        total = total + Convert.ToDouble(record.TotalDue);
                        TeamName = record.ProjectNo.ToString();
                        counter++;
 
 
 
                    }
                    string range = Convert.ToString(counter + 1);
                    oRng = oSheet.get_Range("A" + range, "J" + range);
                    oSheet.get_Range("A" + range, "J" + range).Font.Bold = true;
                    oRng.Borders[XlBordersIndex.xlEdgeTop].LineStyle = Excel.Constants.xlSolid;
                    oSheet.Cells[12, 1] = "Team Name:" + TeamName;
                    oSheet.Cells[counter + 1, 1] = "VENDOR SUMMARY";
                    oSheet.Cells[counter + 1, 5] = "Grand Total";
                    oSheet.Cells[counter + 1, 8] = hours.ToString();
                    oSheet.Cells[counter + 1, 10] = total.ToString("c");
                    oSheet.get_Range("A" + counter+3 , "B" + counter + 3).Font.Bold = true;
                                        oSheet.Name = rec.InvoiceHeading.ToString() + " " + ProjCode;
                    oRng = oSheet.get_Range("A16", "J16");
                    oRng.Font.Bold = true;
                    oRng.Font.Underline = true;    
 
                    //oRng = oSheet.get_Range("A1", "Z1");
                    //oRng.EntireColumn.AutoFit();
                    oXL.Visible = false;
                    oXL.UserControl = false;
                    counter2++;
 
 
                    GC.Collect();  // force final cleanup!
 
 
                }
                catch (Exception theException)
                {
                    String errorMessage;
                    errorMessage = "Error: ";
                    errorMessage = String.Concat(errorMessage, theException.Message);
                    errorMessage = String.Concat(errorMessage, " Line: ");
                    errorMessage = String.Concat(errorMessage, theException.Source);
                    //errLabel.Text = errorMessage;
                }
            }
 
 
            string strFile = "report" + System.DateTime.Now.Ticks.ToString() + ".xls";
 
            dlg.Filter = "Excel Worksheets|*.xls";
 
            //oWB.SaveAs(HttpContext.Current.Server.MapPath(".") + "\\" + strFile, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);
            oWB.SaveAs("D:\\" + strFile, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, null, null, null);
            // Need all following code to clean up and extingush all references!!!
 
            oWB.Close(null, null, null);
            oXL.Workbooks.Close();
            oXL.Quit();
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
            oSheet = null;
            oWB = null;
            oXL = null;
 
        }
    }
}

I have never used the Savefile dialog before. How does it know to save the excel file i have just create? Thanks for your patience, i am relatively new to .NET :)

[tlhintoq]

I'm a big believer in reading about new controls. Always best to actually understand how they work, than just shoehorn them in and hope for the best.
Here's the MSDN page for this control.

At around line 200 is where you will want to put in the SaveFileDialog

Make a new SaveFileDialog
Show it
Get the path returned from it
Use that path as the new save path, in place of your hard coded "D:\" + FileName

How does it know to save the excel file i have just create?

It doesn't. The dialog does not save your file. You already have that written. The Dialog presents a standard Save dialog box from which you can browse and receive the path and file name the user selected.

The best way to understand it is to use it... play with it... give it a try then let me know how it is treating you.

[mcam9]

What is the namespace for the savefiledialog i.e. using System.blah.blah. When i try to us

 SaveFileDialog DialogSave = new SaveFileDialog()

i get the error 'type or namespace name 'SaveFileDialog' could not be found (are you missing a using directive or an assembly reference'

[GaryTexmo]

System.Windows.Forms

It's kind of goofy how you don't get that information on the MSDN page... bleh.

[tlhintoq]

What is the namespace for the savefiledialog i.e. using System.blah.blah. When i try to us

Here's the MSDN page for this control.

It's kind of goofy how you don't get that information on the MSDN page... bleh.

Guys... Its right on the top of the MSDN page.

SaveFileDialog Class
Prompts the user to select a location for saving a file. This class cannot be inherited.

Namespace: System.Windows.Forms
Assembly: System.Windows.Forms (in System.Windows.Forms.dll)
Syntax

Please don't be offended when I point out this as a good example of making things tougher for yourself than necessary. Sometimes the best thing someone can do is walk away from the computer. Take a break even if you are behind schedule. Play with the dog. Take a walk. Put down the caffeine. When you come back to the computer move slower not faster. Take the time to actually read everything you are looking at instead of just skimming it for what look to be the important bits. In some situations an extra 30 seconds reading the entire page of the book, or the entire page on a site can save you 30 hours trying to figure something out that was already explained.

[GaryTexmo]

Funny, the top hit on google for "C# save file dialog" takes you to *this* page, which is where I was looking at and doesn't list the namespace. I didn't go to yours... but yes, there it is indeed.

You'd think Microsoft would be more consistent ;)

[tlhintoq]

@GaryTexmo
In at least 10 places on that page is a clickable link to the SaveFileDialog component.

The first paragraph for example:

How to: Save Files Using the SaveFileDialog Component
The SaveFileDialog component allows users to browse the file system and select files to be saved. The dialog box returns the path and name of the file the user has selected in the dialog box. However, you must write the code to actually write the files to disk.

TIP: Just hover over any of the links and look at their link address in the status bar of your web browser. Hovering over 'SaveFileDialog" link will show you the target of the link:
http://msdn.microsoft.com/en-us/library/system.windows.forms.savefiledialog.aspx
Which in this case even gives us the namespace because MS isn't really creative about their links.

Because you were in the MSDN area for Visual Studio 2008 (creating Windows Forms applications) there really isn't a need to tell you to use the System.Windows.Forms namespace. When you created a Windows Forms application the using statement would have been put into your Form automatically.

Google is a great tool and I use the living daylights out of it, but you have to take a moment and decide if it's top result is your top result.
Sometimes it pays to keep a folder of your own bookmarks in your browser. I find a good starting place to be the top of the System namespace. But that's just me.

[GaryTexmo]

Yes I know, but thank you anyway for your in depth explanation.

mcam9, if you have any more troubles, feel free to ask :)