Click here to Skip to main content
13,344,508 members (49,072 online)
Rate this:
 
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 23: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
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web03 | 2.8.180111.1 | Last Updated 31 Jan 2013
Copyright © CodeProject, 1999-2018
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