Click here to Skip to main content
11,702,396 members (69,106 online)
Rate this: bad
good
Please Sign up or sign in to vote.
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();
 
        }

If the above script C # just to insert the entire mysql data to excel, I want to ask:

The data I have hundreds of rows of data, I want to split into 31 lines per page in excel, page 1 in excel in mysql row data content of 1-31, page 2, the contents of the lines 32-62, page 3 of lines 63-93, and so ...

Page 1 in excel at the start of the line [15, (column)] I want 31 rows, then in excel to [45, (column)], page 2 in excel at the start of the line [66, (column)] to [96, (column)], page 3 [117, (column)], until [147, (column)], and so on ...

what should I add in my script???
Posted 30-Jan-13 22:03pm
ox3nt220

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try this process it will work,
In design form add Savefiledialog from tool box and right click that,inside the savefiledialog write the below coding,
private void saveFileDialog1_FileOk(object sender, CancelEventArgs e)
{
DataSet dds = new DataSet();
SqlConnection con = new SqlConnection("Provider=;Data Source="; User Id=; Pwd=;);
SqlDbDataAdapter da = new SqlDbDataAdapter("Select * from TableName", con);
con.Open();
if (radioButton3.Checked == true)
{
da = new SqlDbDataAdapter("Select * from TableName", con);
da.Fill(dds);
string nm = saveFileDialog1.FileName;
ExcelLibrary.DataSetHelper.CreateWorkbook(nm, dds);
}

}
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 515
1 Sergey Alexandrovich Kryukov 355
2 OriginalGriff 299
3 CHill60 145
4 Mika Wendelius 115
0 OriginalGriff 8,369
1 Sergey Alexandrovich Kryukov 7,547
2 CPallini 4,922
3 Maciej Los 4,411
4 Mika Wendelius 3,556


Advertise | Privacy | Mobile
Web04 | 2.8.150819.1 | Last Updated 31 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100