I indent to get generated salary slip from each of the rows of a DataGridView, in C#.
Each of the salary slips must be:
1
generated from a single row
2 each cell should be included in relevant section i.e. deduction in deduction, basics in basics etc.
3 if a cell in the row is empty or has 0 in it, the cell must be not included in the Salary Slip
4 saved in PDF form
5 named as SalarySlip-MonthYear, i.e. SalarySlip-July2019
6 sent via email as attachment
7 deleted once the email sent
8 1 - 7 should be repeated for each of the rows
Anyone could please me to design a Salay Slip
like this?
SalarySlip-July2019.pdf - Google Drive[
^]
Thanking you in anticipation
What I have tried:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Net;
using System.Net.Mail;
using System.Web.UI;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html.simpleparser;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Data.OleDb;
namespace TKSSalarySlipSender
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private async Task SendPDFEmailAsync(System.Data.DataTable dt)
{
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter dh = new HtmlTextWriter(sw))
{
string companyName = "TheKhanSoft";
int orderNo = 2303;
StringBuilder sb = new StringBuilder();
sb.Append("");
sb.Append("Order Sheet");
sb.Append("");
sb.Append("Order No:");
sb.Append(orderNo);
sb.Append("Date: ");
sb.Append(DateTime.Now);
sb.Append(" ");
sb.Append("Company Name : ");
sb.Append(companyName);
sb.Append("");
sb.Append("");
sb.Append("");
sb.Append("");
sb.Append("");
foreach (DataColumn column in dt.Columns)
{
sb.Append("");
sb.Append(column.ColumnName);
sb.Append("");
}
sb.Append("");
foreach (DataRow row in dt.Rows)
{
sb.Append("");
foreach (DataColumn column in dt.Columns)
{
sb.Append("");
sb.Append(row[column]);
sb.Append("");
}
sb.Append("");
}
sb.Append("");
StringReader sr = new StringReader(sb.ToString());
Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
using (MemoryStream memoryStream = new MemoryStream())
{
PdfWriter writer = PdfWriter.GetInstance(pdfDoc, memoryStream);
pdfDoc.Open();
htmlparser.Parse(sr);
pdfDoc.Close();
byte[] bytes = memoryStream.ToArray();
memoryStream.Close();
MailMessage mm = new MailMessage("email@gmail.com");
mm.Subject = "TheKhanSoft Testing Email";
mm.Body = "PDF Attachment";
mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "iTextSharpPDF.pdf"));
mm.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "in-v3.mailjet.com";
smtp.EnableSsl = true;
NetworkCredential NetworkCred = new NetworkCredential();
NetworkCred.UserName = "USERNAME";
NetworkCred.Password = "PASSWORD";
smtp.UseDefaultCredentials = true;
smtp.Credentials = NetworkCred;
smtp.Port = 587;
smtp.Send(mm);
sent = true;
}
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
cmbSheets.SelectedIndex = 0;
button1.Enabled = (dataGridView1.RowCount > 0) ? true : false;
}
bool sent = false;
private void button1_Click(object sender, EventArgs e)
{
if (sent)
{
MessageBox.Show("Email Sent.");
}
}
string fileName;
string strConn = string.Empty;
OleDbConnection con;
private void getDataFromExcel()
{
string sheetName = fileName;
try
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.Filter = "XML Files (*.xml; *.xls; *.xlsx; *.xlsm; *.xlsb) |*.xml; *.xls; *.xlsx; *.xlsm; *.xlsb";
openFileDialog1.FilterIndex = 3;
openFileDialog1.ValidateNames = true;
openFileDialog1.Multiselect = false;
openFileDialog1.Title = "Open Text File-R13";
openFileDialog1.InitialDirectory = @"Desktop";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
string pathName = openFileDialog1.FileName;
fileName = Path.GetFileNameWithoutExtension(openFileDialog1.FileName);
FileInfo file = new FileInfo(pathName);
if (!file.Exists) { throw new Exception("Error, file doesn't exists!"); }
string extension = file.Extension;
switch (extension)
{
case ".xls":
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
case ".xlsx":
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
break;
default:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
}
lblFileName.Text = fileName;
cmbSheets.Items.Clear();
con = new OleDbConnection(strConn);
OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("SELECT * FROM [Sheet1$]"), con);
con.Open();
System.Data.DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
con.Close();
cmbSheets.Items.Add("-- Select Salary Sheet Below -- ");
cmbSheets.SelectedIndex = 0;
foreach (DataRow drSheet in dt.Rows)
if (drSheet["TABLE_NAME"].ToString().Contains("$"))
{
string s = drSheet["TABLE_NAME"].ToString();
cmbSheets.Items.Add(s.StartsWith("'") ? s.Substring(1, s.Length - 3) : s.Substring(0, s.Length - 1));
}
cmbSheets.Enabled = (cmbSheets.Items.Count > 1) ? true : false;
}
}
catch (Exception e)
{
MessageBox.Show("Error! \n" + e.ToString());
}
}
private void button2_Click(object sender, EventArgs e)
{
getDataFromExcel();
}
DataGridViewCheckBoxColumn dgvChkBxCol;
private void cmbSheets_SelectedIndexChanged(object sender, EventArgs e)
{
dgvChkBxCol = new DataGridViewCheckBoxColumn {
Name = "SendEmail",
HeaderText = "Send Email",
ValueType = typeof(bool),
FalseValue = false,
TrueValue = true,
Frozen = false,
ReadOnly = false
};
dataGridView1.DataSource = "";
if (cmbSheets.SelectedIndex != 0)
{
System.Data.DataTable dataTable = new System.Data.DataTable();
OleDbDataAdapter oda = new OleDbDataAdapter(
string.Format("SELECT * FROM [{0}$]", cmbSheets.SelectedItem),
con);
oda.Fill(dataTable);
if (dataTable.Rows.Count > 0)
{
dataGridView1.DataSource = dataTable;
dataGridView1.Columns.Add(dgvChkBxCol);
}
else
{
MessageBox.Show("Sheet does not have any record.");
}
}
button1.Enabled = (dataGridView1.RowCount > 0) ? true : false;
}
private void lblFileName_Click(object sender, EventArgs e)
{
getDataFromExcel();
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
string isSendEmailCol = dataGridView1.Columns[e.ColumnIndex].Name;
dgvChkBxCol.ReadOnly = false;
if (isSendEmailCol.Equals(dgvChkBxCol.Name))
{
dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value = !(bool)(dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value == null ? false : dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value);
MessageBox.Show(dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString());
}
}
}
}
But I want this to be like
SalarySlip-July2019.pdf - Google Drive[
^]
and with less amount of code