使用C#检索数据并导出到Excel工作表并存储 [英] Retrieve data from using c# and export to excel sheet and store

问题描述

你好，

我尝试将数据从我的C#Windows应用程序检索到Excel电子表格，并将this.xls文件存储到所需位置.我的问题是我的代码将excel文件保存到预定义的位置.

Hello,

I am try to retrieve data from my C# Windows application to an Excel spreadsheet and store this.xls file to a desired location. My problem is that my code saves the excel file to 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\\;

现在，我正在尝试使用SaveFileDialog控件，该控件将文件保存到所需的位置，例如

Now I am trying to use the SaveFileDialog control which saves the file to a 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();
}

这两个功能都可以正常工作，但是我的要求是我想将Excel电子表格存储到所需位置.

请帮我.我可以使用FolderBrowserDialog以及如何与我的代码一起使用吗?

谢谢与问候
Nilesh

both functions work properly but my requirement is that I want to store the Excel spreadsheet to a desired location.

Please help me. Can i use the FolderBrowserDialog and how can I use with my code?

Thanks and Regards
Nilesh

推荐答案

可以，我可以成功解决此问题... :) :)
在lt FolderBrowserDialog的帮助下，我们可以打开文件对话框并将此路径发送到私有object m_strSampleFolder = Application.StartupPath + "\\ExcelData\\" ;
像这样

Its Ok I am success to solve this problem... :) :)
with the help of lt FolderBrowserDialog We can open file dialog and send this path to private object m_strSampleFolder = Application.StartupPath + "\\ExcelData\\" ;
Like This

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;
        string path11;
        // 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 ;
        private void Button3_Click(object sender, EventArgs e)
        {
            if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
            {
                this.textBox3.Text = folderBrowserDialog1.SelectedPath;
            }
            path11 = textBox3.Text;
            m_strSampleFolder = path11.ToString()+ "\\" ";
            xlsheet();
        }
        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;
           
        }

多亏我的自我
问候
Nilesh:thumbsup:

Thanks to My self
Regards
Nilesh :thumbsup:

这篇关于使用C#检索数据并导出到Excel工作表并存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！