[C＃] EXPORT DATABASE MYSQL到EXCEL 2010 [英] [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();

        }

如果上面的脚本C＃只是为了将整个mysql数据插入excel，我想要问：



我有数百行数据的数据，我想在excel中每页拆分31行，第1页在excel的mysql行数据内容1-31，第2页，第32-62行的内容，第63-93行的第3页，等等......



页面1在excel的行开头[15，（列）] 我想要31行，然后在excel到[45，（列）]，第2页在excel中行[66，（列）]到[96，（列）]，第3页[117，（列）]的开头，直到[147，（列）]等等......



我应该在我的剧本中添加什么？

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???

推荐答案

尝试这个过程它会工作，

在设计表单中添加Savefiledialog从工具箱中右键单击，在savefiledialog中写下面的代码，

private void saveFileDialog1_FileOk（object sender， CancelEventArgs e）

{

DataSet dds = new DataSet（）;

SqlConnection con = new SqlConnection（Provider =; Data Source = ;用户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）;

}



}

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);
}

}

这篇关于[C＃] EXPORT DATABASE MYSQL到EXCEL 2010的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！