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