using System;
using System.Data.OleDb;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
 
namespace Excel_Payment_Detail_Report_Conversion
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void btnGetFile_Click(object sender, EventArgs e)
        {
            //Stream myStream;
 
            //------------------------------------------------------------------------------------------
            //Open File Dialog
            //------------------------------------------------------------------------------------------
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
 
            //openFileDialog1.InitialDirectory = "c:\\";
            openFileDialog1.Filter = "csv files (*.csv)|*.csv|All files (*.*)|*.*";
            openFileDialog1.FilterIndex = 2;
            //------------------------------------------------------------------------------------------
            // remember the folder the user changed last
            //------------------------------------------------------------------------------------------
            //openFileDialog1.RestoreDirectory = false;
 
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                //if ((myStream = openFileDialog1.OpenFile()) != null)
                //{
                //    // Insert code to read the stream here.
                //    myStream.Close();
                //}
 
                //------------------------------------------------------------------------------------------
                // Parse folder & fileName
                //------------------------------------------------------------------------------------------
                string folder = Path.GetDirectoryName(openFileDialog1.FileName);
                string fileName = Path.GetFileName(openFileDialog1.FileName);
 
                //------------------------------------------------------------------------------------------
                //readFile
                //------------------------------------------------------------------------------------------
                readFile(folder, fileName);
            }
 
        }
        private void readFile(string folder, string fileName)
        {
            try
            {
                //------------------------------------------------------------------------------------------
                // use OleDbConnection to read file into weak ExcelDataSet
                //------------------------------------------------------------------------------------------
                OleDbConnection ExcelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + folder + ";Extended Properties=Text;");
                OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + fileName + "]", ExcelConnection);
 
                OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
 
                ExcelConnection.Open();
 
                DataSet ExcelDataSet = new DataSet();
                ExcelAdapter.Fill(ExcelDataSet);
 
                ExcelConnection.Close();
                //return ExcelDataSet;
 
                //------------------------------------------------------------------------------------------
                // read rows from ExcelDataSet.Tables[0]
                //------------------------------------------------------------------------------------------
                DataTable dt = ExcelDataSet.Tables[0];
                foreach (DataRow row in dt.Rows)
                {
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        //------------------------------------------------------------------------------------------
                        // write a column
                        //------------------------------------------------------------------------------------------
                        WriteItem(stream, row[i], quoteall);
                        if (i < table.Columns.Count - 1)
                            stream.Write(',');
                        else
                            stream.Write('\n');
                    }
                }
 
            }
            catch (Exception ex)
            {
                string x = ex.ToString();
                System.Diagnostics.Debug.WriteLine(x);
            }
 
 
            //------------------------------------------------------------------------------------------
            //
            //------------------------------------------------------------------------------------------
        }
          
    }
}