-3

I made a program to open Excel file.

There are exist data page and blank page. Can I add only the pages where the data exists in the combo box? Can I use ButtonEvent to view only pages that contain data?

        string filePath = openFileDialog1.FileName;//파일 경로 가져오기
        string filename = openFileDialog1.SafeFileName;//파일 이름만 가져오기
        string fileExtension = Path.GetExtension(filePath);

        //string connectionString = string.Empty;
        string sheetName = string.Empty;

     using (OleDbConnection con = new OleDbConnection(ConnectStr()))
        {
            using (OleDbCommand cmd = new OleDbCommand())
            {
                using (OleDbDataAdapter oda = new OleDbDataAdapter())
                {
                    DataTable dt = new DataTable();

                    cmd.CommandText = "SELECT * From [" + sheetName + "]";
                    cmd.Connection = con;
                    con.Open();
                    oda.SelectCommand = cmd;
                    oda.Fill(dt);
                    con.Close();

                    dataGridView1.DataSource = dt; 

                }
            }
        }

 public string ConnectStr()
    {
        string filePath = openFileDialog1.FileName;
        string filename = openFileDialog1.SafeFileName;//파일 이름만 가져오기
        string fileExtension = Path.GetExtension(filePath);

        string connectionString = string.Empty;
        string sheetName = string.Empty;

        switch (fileExtension)
        {
            case ".xls":    //Excel 97-03버전

                connectionString = string.Format(Excel03ConString, filePath); 
                break;
            case ".xlsx":  //Excel 07이상 버전
                connectionString = string.Format(Excel16ConString, filePath);
                break;
        }
        return connectionString;
    }
K.gwang
  • 1
  • 1
  • 1
    _"....but can I add only pages with data to the combobox? ...."_ - huh? –  Jul 19 '17 at 00:51
  • 2
    _"...can I see only those pages that have data through button events?..."_ - no idea what this means –  Jul 19 '17 at 00:52
  • There are 64 pages in the Excel file. data exists page and blank page. only data exists page are added to the combobox. Can I use the ButtonEvent to view only the pages that contain the data? – K.gwang Jul 19 '17 at 01:33
  • Ok I think I get you now. What you do in a button's click event is entirely up to you. Yes, you could use OLEDB to fetch data from certain pages. If there is no data you can skip showing anything for that page –  Jul 19 '17 at 02:57
  • How can I view only data exists page using OLEDB? – K.gwang Jul 19 '17 at 03:50
  • I'm sorry but you are not making any sense. :( Wishing you well –  Jul 19 '17 at 05:32
  • I'm sorry ㅠ-ㅠ Because English is not native language. Which part doesn't understand? – K.gwang Jul 19 '17 at 05:56

1 Answers1

0

I don't really get what is this. But I beg you need to show only specific sheet from spreadsheet correct me if i'm wrong.

There is a SQL commend that we can query from the specific sheet. 

try
            {
                this.txtImportFilePath.Text = opd.FileName;
                OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" + this.txtImportFilePath.Text + ";Extended Properties=Excel 8.0;");

                StringBuilder stbQuery = new StringBuilder();
                stbQuery.Append("Select * From [Sheet1$]");
                OleDbDataAdapter adp = new OleDbDataAdapter(stbQuery.ToString(), con);

                DataSet dsXLS = new DataSet();
                adp.Fill(dsXLS);

                DataView dvEmp = new DataView(dsXLS.Tables[0]);

                trnxlistDataview.DataSource = dvEmp;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
            }
Ayoung
  • 23
  • 4