[C#] EXPORT DATABASE MYSQL TO EXCEL 2010

using MySql.Data.MySqlClient;

using Excel = Microsoft.Office.Interop.Excel;

using System.Runtime.InteropServices;

using System.Reflection;



private void MySqlConnected()

        {

            #region Connect to Mysql

            String conString = "Data Source=127.0.0.1;database=absen;User ID=root;Password=";

            con = new MySqlConnection(conString);



            int a = int.Parse(txt_Line.Text);

            int b = a + 1;

            int c = a * 31;

            int d = b * 31;



            string divisi = txt_NmLevel.Text.ToString();



            sql1 = @"SELECT trans_date, jam_masuk, jam_keluar, clock_in, clock_out, lembur_datang, terlambat, cepat_pulang, lembur, jlh_jamkerja, description FROM `a1207` WHERE `nama_level` LIKE '%" + @divisi + "%' LIMIT " + c + ", 31";



            da1 = new MySqlDataAdapter(sql1, con);

            ds1 = new DataSet();

            da1.Fill(ds1);



            sql2 = @"SELECT trans_date, jam_masuk, jam_keluar, clock_in, clock_out, lembur_datang, terlambat, cepat_pulang, lembur, jlh_jamkerja, description FROM `a1207` WHERE `nama_level` LIKE '%" + @divisi + "%' LIMIT " + d + ", 31";



            da2 = new MySqlDataAdapter(sql2, con);

            ds2 = new DataSet();

            da2.Fill(ds2);

            #endregion

        }



private void btnSaveAs_Click(object sender, EventArgs e)

        {

            SaveFileDialog SaveFileDialog1 = new SaveFileDialog();



            SaveFileDialog1.Title = "Save Convert Data Absen...";

            SaveFileDialog1.Filter = "Excel files 2007 (*.xls)|*.xls|Excel files 2010 (*.xlsx)|*.xlsx";

            SaveFileDialog1.FilterIndex = 1;

            SaveFileDialog1.RestoreDirectory = true;

            SaveFileDialog1.DefaultExt = "xls";



            if (string.IsNullOrEmpty(tFilename.Text) == false)

                SaveFileDialog1.FileName = tFilename.Text;

            else

                SaveFileDialog1.FileName = @txt_NmLevel.Text.ToString() + txt_Line.Text.ToString();



            SaveFileDialog1.CreatePrompt = false;

            SaveFileDialog1.OverwritePrompt = true;



            if (SaveFileDialog1.ShowDialog() == DialogResult.OK)

            {

                if (string.IsNullOrEmpty(SaveFileDialog1.FileName) == false)

                { tFilename.Text = SaveFileDialog1.FileName; }

            }



            SaveFileDialog1.Dispose();

        }



private void ConvertExcel()

        {

            string OpenExcelFolder = @"E:\Absen\Dumb Absen";

            string OpenExcelFileName = "absen - 2012-07.xlsx";

            string CombineOpenExcel = Path.Combine(OpenExcelFolder, OpenExcelFileName);



            string tfile = tFilename.Text.ToString();

            FileInfo file = new FileInfo(tfile);

            #endregion



            MessageBox.Show("File Berhasil di convert ke Ms. Excel di dalam folder : \n" + tfile.ToString(), "Complete"); System.Threading.Thread.Sleep(100);



            #region Convert to Excel

            //EXCEL

            Excel.Application excelApp = new Excel.Application();

            Excel.Workbook workbook = (Excel.Workbook)excelApp.Workbooks.Add(Missing.Value);

            Excel.Worksheet worksheet;



            workbook = excelApp.Workbooks.Open(CombineOpenExcel, 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, 1, 0);

            worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];



            string data = null;

            int i = 0;

            int j = 0;



            for (i = 0; i <= ds1.Tables[0].Rows.Count - 1; i++)

            {

                for (j = 0; j <= ds1.Tables[0].Columns.Count - 1; j++)

                {

                    data = ds1.Tables[0].Rows[i].ItemArray[j].ToString();

                    ((Excel.Range)worksheet.Cells[i + 15, j + 1]).Value2 = data;

                }

            }



            workbook.SaveAs(tfile, Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            workbook.Close(false, Type.Missing, Type.Missing);

            excelApp.Quit();

            #endregion

        }

private void btn_Convert_Click(object sender, EventArgs e)

        { 

            ConvertExcel();

        }