0

Thank you all for helping me in completing my collage mini-project

//form1
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using Microsoft.Office.Interop.Excel;

using ClosedXML.Excel;

namespace merge_excel
{
    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        public void connection()
        {
            try
            {
                string st;
                st = "select * from [" + shnam.Text + "$] where [name]='" + stuname.Text + "'"; //query 
                OleDbConnection oC = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + opfl.Text + ";Extended Properties='Excel 12.0 Xml;HDR=YES'");// connection details
                OleDbDataAdapter db = new OleDbDataAdapter(st, oC);
                db.Fill(da);// filling internal dataset i.e "da" with data from excel sheet
                dg.DataSource = da.Tables[0];//loading datagrid "dg" with data of dataset "da"
                oC.Close();//closing connection
            }
            catch (Exception exc)
            {
                if (stuname.Text == "" || opfl.Text == "" || shnam.Text == "")
                {
                    MessageBox.Show(exc.Message);

                }
            }

        }
        private void button4_Click(object sender, EventArgs e)
        {

            OpenFileDialog f = new OpenFileDialog();
            f.Filter = "All Excel Document(*.xls,*.xlsx)|*.xlsx;*xls";
            if (f.ShowDialog() == DialogResult.OK)
            {
                opfl.Text = f.FileName;
            }
        }

        private void button10_Click(object sender, EventArgs e)
        {
            connection();  
        }

        private void button2_Click(object sender, EventArgs e)
        {
           saveas();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            SaveFileDialog sv = new SaveFileDialog();
            sv.Filter = "Excel 97-2007 Workbook(*.xls)|*.xls|Excel Workbook(*.xlsx)|*.xlsx";
            if (sv.ShowDialog() == DialogResult.OK)
            {
                safl.Text = sv.FileName;
            }
        }
        public void saveas()
        {
            Form1 frm = new Form1();
            try
            {
                XLWorkbook wb = new XLWorkbook();
                wb.Worksheets.Add(da);
                wb.SaveAs(safl.Text);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error:" + ex.Data);
                frm.Close();
            }

        }
    }
}
//form3
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;


namespace merge_excel
{
    public partial class Form3 : Form
    {
        public Form3()
        {
            InitializeComponent();

        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Hide();
            Form2 f2 = new Form2();
            f2.ShowDialog();
            this.Show();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Form1 f1 = new Form1();
            this.Hide();
            f1.ShowDialog();
            this.Show();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            Form4 f1 = new Form4();
            this.Hide();
            f1.ShowDialog();
            this.Show();
        }
    }
}
//form2
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using Novacode;


namespace merge_excel
{
    public partial class Form2 : Form
    {
        Form1 f1 = new Form1();
        public Form2()
        {
            InitializeComponent();

        }


        private void dop_Click(object sender, EventArgs e)
        {
            OpenFileDialog fl = new OpenFileDialog();
            fl.Filter = "All Document Files(*.docx)|*.docx";
            if (fl.ShowDialog() == DialogResult.OK)
                Docop.Text = fl.FileName;
        }

        private void dsv_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog fl = new FolderBrowserDialog();
            fl.ShowNewFolderButton = true;
            fl.Description = "Choose the folder in which You Want to save the Report";
            if (fl.ShowDialog() == DialogResult.OK)
                Docop.Text = fl.SelectedPath;
        }

        private void dsl_Click(object sender, EventArgs e)
        {
            doc();
        }
        public void doc( )
        {
            try
            {
                int index = 0;
                string p = Docop.Text;
                string q = docsv.Text;
                var g = DocX.Load(p);
                index = f1.dg.CurrentRow.Index;
                int k = index;
                int j = 0;
                foreach (DataGridViewRow row in dg.Rows)
                {


                    if (index < dg.RowCount)
                    {
                        // Perform the replace:
                        g.ReplaceText("<subject" + k + ">", dg.CurrentRow.Cells[j].Value.ToString());
                        g.ReplaceText("<sem>",              dg.CurrentRow.Cells[j + 2].Value.ToString());
                        g.ReplaceText("<regno>",            dg.CurrentRow.Cells[j + 3].Value.ToString());
                        g.ReplaceText("<name>",             dg.CurrentRow.Cells[j + 4].Value.ToString());
                        g.ReplaceText("<mk" + k + ">",      dg.CurrentRow.Cells[j + 5].Value.ToString());
                        g.ReplaceText("<m"+k+"k"+k+">",     dg.CurrentRow.Cells[j + 6].Value.ToString());
                        g.ReplaceText("<att"+k+">",         dg.CurrentRow.Cells[j + 11].Value.ToString());
                        g.ReplaceText("<tg name>",          TGnam.Text);
                        g.ReplaceText("<dept>",             dept.Text);
                        g.ReplaceText("<date>",             DateTime.Now.Date.ToString() + @"\" + DateTime.Now.Month.ToString() + @"\" + DateTime.Now.Year.ToString());
                        //when details of all the subject are replaced then generate word file
                        if (k == dg.RowCount - 1)
                            g.SaveAs(q + dg.CurrentRow.Cells[j + 2].Value + ".docx");//save document with student name
                        k++;//counter to replace right keywords with right data
                        dg.CurrentCell = dg[0, index++];//Make current cell as the cell in the next row
                    }
                }
                MessageBox.Show("DOCUMENT GENERATED");//show message when document is created
                k = 0;
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }

        private void openbrowse_Click(object sender, EventArgs e)
        {
            OpenFileDialog f = new OpenFileDialog();
            f.Filter = "All Excel Document(*.xls,*.xlsx)|*.xlsx;*xls";
            if (f.ShowDialog() == DialogResult.OK)
            {
                opfl.Text = f.FileName;
            }
        }

        private void openload_Click(object sender, EventArgs e)
        {
            connection();
        }

        public void connection()
        {
            try
            {
                string st;
                st = "select * from [" + shnam.Text + "$] where [name]='" + stuname.Text + "'"; //query 
                OleDbConnection oC = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + opfl.Text + ";Extended Properties='Excel 12.0 Xml;HDR=YES'");// connection details
                OleDbDataAdapter db = new OleDbDataAdapter(st, oC);
                db.Fill(da);// filling internal dataset i.e "da" with data from excel sheet
                dg.DataSource = da.Tables[0];//loading datagrid "dg" with data of dataset "da"
                oC.Close();//closing connection
            }
            catch (Exception exc)
            {
                if (stuname.Text == "" || opfl.Text == "" || shnam.Text == "")
                {
                    MessageBox.Show(exc.Message);

                }
            }

        }


    }
}

Edited by johnrosswrock

Attachments openning_page.PNG 251.52 KB Doc_page.PNG 179.99 KB excel-merge_page.PNG 119.22 KB
2
Contributors
1
Reply
23
Views
3 Years
Discussion Span
Last Post by Warrens80
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.