How to Retrieve data from c#.net to excel and store file at user desired location?

[Nilesh K R]

hello,
I am try to retrieve data from my c# windows application to excel sheet and store this .xls file to desired location. but my problem is that my code save excel file at a predefined location.

private Excel.Application m_objExcel = null;
        private Excel.Workbooks m_objBooks = null;
        private Excel._Workbook m_objBook = null;
        private Excel.Sheets m_objSheets = null;
        private Excel._Worksheet m_objSheet = null;
        private Excel.Range m_objRange = null;
        private Excel.Font m_objFont = null;
        private Excel.QueryTables m_objQryTables = null;
        private Excel._QueryTable m_objQryTable = null;
        // Frequenty-used variable for optional arguments.
        private object m_objOpt = System.Reflection.Missing.Value;
 
        // Paths used by the sample code for accessing and storing data.
        private object m_strSampleFolder = Application.StartupPath  + "\\ExcelData\\" ;
 
        private void xlsheet()
        {
            // Start a new workbook in Excel.
 
            m_objExcel = new Excel.Application();
            m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
            m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
            m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
            // Create an array for the headers and add it to cells A1:C1.
            object[] objHeaders = { "X-Axis CH1", "Y-Axis CH1","Nilesh", "X-Axis CH2", "Y-Axis CH2" };
            m_objRange = m_objSheet.get_Range("A1", "E1");
            m_objRange.set_Value(m_objOpt, objHeaders);
            m_objFont = m_objRange.Font;
            m_objFont.Bold = true;
            // Create an array with 3 columns and 100 rows and add it to
            // the worksheet starting at cell A2.
            object[,] objData = new Object[bufn.Length, 5];
            Int64 c_xl1 = 0, c_xl2=0;
            for (int r = 0; r < bufn.Length; r++)
            {
                objData[r, 0] = c_xl1.ToString();
                objData[r, 1] = (bufn[r] + vsc2).ToString();
                c_xl1 += 1;
                objData[r, 3] = c_xl2.ToString();
                objData[r, 4] = (bufb[r] + vsc4).ToString();
                c_xl2 += 1;
            }
            m_objRange = m_objSheet.get_Range("A2", m_objOpt);
            m_objRange = m_objRange.get_Resize(bufn.Length, 5);
            m_objRange.set_Value(m_objOpt, objData);
            // Save the workbook and quit Excel.
           m_objBook.SaveAs(m_strSampleFolder+nm.ToString()+"Book24.xlsx", m_objOpt, m_objOpt,
                m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
                m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
            m_objBook.Close(false, m_objOpt, m_objOpt);
            m_objExcel.Quit();
            nm += 1;
        }
 
 
 
private void iFormGlassButton3_Click(object sender, EventArgs e)
        {
            xlsheet();
        }
 

This code saves file at predefine location

private object m_strSampleFolder = Application.StartupPath  + "\\ExcelData\\;

Now i am trying to us SaveFileDialog control which save file to desired location like this

SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.Filter = "All files (*.*)|*.*|All files (*.*)|*.*";
            saveFileDialog1.Title = "Save an excel File";
            saveFileDialog1.DefaultExt = "xls";
            saveFileDialog1.ShowDialog();
 
            // If the file name is not an empty string open it for saving.
            if (saveFileDialog1.FileName != "")
            {
                // Saves the Image via a FileStream created by the OpenFile method.
                System.IO.FileStream fs =
                   (System.IO.FileStream)saveFileDialog1.OpenFile();
                // Saves the Image in the appropriate ImageFormat based upon the
                // File type selected in the dialog box.
                // NOTE that the FilterIndex property is one-based.
 
 
                fs.Close();
}

both of functions work properly but my Requirement is that i want to store excel sheet at desired location.
please help me

Thanks and Regards
Nilesh

[Aimee Bailey]

Have you considered CSV export instead? it's much easier, excel still open's the file's, and it would avoid using an interop with office to use it.

I try to discourage interops as you don't always know if the target computer your application will run on will use Microsoft Office (which is a requirement for this to work).

Infact here's an example using the DataTable...

public void SaveCSV(DataTable data, string filename)
{
    List<string> csv_lines = new List<string>();
 
    string csv_header = "";
    foreach (DataColumn col in data.Columns)
    {
        csv_header += col.ColumnName + ", ";
    }
    csv_lines.Add(csv_header.TrimEnd(' ', ','));
 
    foreach (DataRow row in data.Rows)
    {
        string csv_line = "";
        foreach (DataColumn col in data.Columns)
        {
            string val = row[col].ToString();
            double dval = 0;
 
            if (double.TryParse(val, out dval))
            {
                csv_line += string.Format("{0}, ", dval);
            }
            else
            {
                csv_line += string.Format("'{0}', ", val);
            }
 
        }
        csv_lines.Add(csv_line.TrimEnd(' ',','));
    }
    File.WriteAllLines(filename, csv_lines);
}