Export From DataGridView To Excel-Sheet in C#

using Excel = Microsoft.Office.Interop.Excel;  // Add Refference of Microsoft.Office.Interop.Excel.dll

// The Data that would be in the DataGridView will be Export to Excel Sheet. By
private void ExportToExcel () //Here ‘gridview_newadmission’ is the Name of the DataGridView
{

try
{

Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value;

xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

for (int c = 0; c < gridview_newadmission.ColumnCount; c++)
{

xlWorkSheet.Cells[1, c + 1] = gridview_newadmission.Rows[0].Cells[c].OwningColumn.Name.ToString();
xlWorkSheet.Cells.ColumnWidth = 15;

}

for (int r = 0; r < gridview_newadmission.RowCount; r++)
{

for (int c = 0; c < gridview_newadmission.ColumnCount; c++)
{

DataGridViewCell cell = gridview_newadmission[c, r];
xlWorkSheet.Cells[r + 2, c + 1] = cell.Value;

}

}

FolderBrowserDialog SelectTarget = new FolderBrowserDialog();
SelectTarget.ShowDialog();
string path = SelectTarget.SelectedPath;
if (path != “”)
{

xlWorkBook.SaveAs(path + “\\myData.xls”, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();

}
statusMessage.ForeColor = Color.Black;
statusMessage.Text = “Data Saved to ” + path;

 }
catch (Exception)
{}

}

DataGridView To Datatable in C#

public DataTable DataGridView2DataTable(DataGridView dgv, String tblName, int minRow=0)//Simply Use this Function For Conversion it would return Datatble
{

DataTable dt = new DataTable(tblName);

  // Header columns
foreach (DataGridViewColumn column in dgv.Columns)
{

DataColumn dc = new DataColumn(column.Name.ToString());
dt.Columns.Add(dc);

}

// Data cells
for (int i = 0; i < dgv.Rows.Count; i++)
{

DataGridViewRow row = dgv.Rows[i];
DataRow dr = dt.NewRow();
for (int j = 0; j < dgv.Columns.Count; j++)
{

dr[j] = (row.Cells[j].Value == null) ? “” : row.Cells[j].Value.ToString();

}

dt.Rows.Add(dr);
}

// Related to the bug arround min size when using ExcelLibrary for export
for (int i = dgv.Rows.Count; i < minRow; i++)
{

DataRow dr = dt.NewRow();
for (int j = 0; j < dt.Columns.Count; j++)
{

dr[j] = ”  “;

}
dt.Rows.Add(dr);

}
return dt;

}

Easy Shortcut Keys in Windows Form C#

Protected override bool ProcessCmdKey(ref Message msg, Keys keyData) //Simple Put this code in Form and Assign the Keys and Line   of Code that must be executed on that key press.
{

if (keyData == (Keys.Control | Keys.X)) //On Pressing Ctrl + X the Event is Detected.
{

MessageBox.Show(“You Have Press Ctrl + X?”);
this.Close();
return true;

}

return base.ProcessCmdKey(ref msg, keyData);

}

Get Current Financial Year in C#

public static string GetCurrentFinancialYear()
{

int CurrentYear = DateTime.Today.Year;
int PreviousYear = DateTime.Today.Year – 1;
int NextYear = DateTime.Today.Year + 1;
string PreYear = PreviousYear.ToString();
string NexYear = NextYear.ToString();
string CurYear = CurrentYear.ToString();
string FinYear = null;

if (DateTime.Today.Month > 3)

FinYear = CurYear + “-” + NexYear;

else

FinYear = PreYear + “-” + CurYear;

return FinYear.Trim();

}

Amount in Words in Crystal Report

numbervar RmVal:=0;
numbervar Amt:=0;
numbervar pAmt:=0;
stringvar InWords :=”Rupees “;

Amt := 25,12,000; // type your value

if Amt > 10000000 then RmVal := truncate(Amt/10000000);
if Amt = 10000000 then RmVal := 1;

if RmVal = 1 then
InWords := InWords + ” ” + towords(RmVal,0) + ” crore”
else
if RmVal > 1 then InWords := InWords + ” ” + towords(RmVal,0) + ” crores”;

Amt := Amt – Rmval * 10000000;

if Amt > 100000 then RmVal := truncate(Amt/100000);
if Amt = 100000 then RmVal := 1;

if RmVal = 1 then
InWords := InWords + ” ” + towords(RmVal,0) + ” lakhs”
Else
If RmVal > 1 then InWords := InWords + ” ” + ToWords(RmVal,0) + “Lakhs”;

Amt := Amt – Rmval * 100000;

if Amt > 0 then InWords := InWords + ” ” + towords(truncate(Amt),0);

pAmt := (Amt – truncate(Amt)) * 100;

if pAmt > 0 then
InWords := InWords + ” and ” + towords(pAmt,0) + ” paisa only”
else
InWords := InWords + ” only”;

UPPERCASE(InWords)

Concatenate Values in Single Cell in SQL

DECLARE @t TABLE (EmpId INT, EmpName VARCHAR(100)) — Here @t is a static table with Columns EmpId & EmpName
INSERT @t VALUES
(1, ‘Bhavik’),(1, ‘Mayank’),(1, ‘Keyur’),(2, ‘Sudarshan’),(2, ‘Sohan’) –Values are inserted in the Table.
SELECT distinct
EmpId,
(
SELECT EmpName+’,’
FROM @t t2
WHERE t2.EmpId = t1.EmpId
FOR XML PATH(”)
) Concatenated –Names with Same ID are Concatenated and Displayed in Single Cell.
FROM @t t1

Move To Next Cell On Enter Key Press In DataGridView C#.

bool notlastColumn =true; //class level variable— to check either last column is reached or not

private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)

{

if (dataGridView1.ColumnCount – 1 == e.ColumnIndex) //if last column
{

KeyEventArgs forKeyDown = new KeyEventArgs(Keys.Enter);
notlastColumn = false;
dataGridView1_KeyDown(dataGridView1, forKeyDown);

}
else
{

SendKeys.Send(“{up}”);
SendKeys.Send(“{right}”);

}

}

private void dataGridView1_KeyDown(object sender, KeyEventArgs e)
{

if (e.KeyCode == Keys.Enter && notlastColumn) //if not last column move to next
{

SendKeys.Send(“{up}”);
SendKeys.Send(“{right}”);

}
else if (e.KeyCode == Keys.Enter)
{

SendKeys.Send(“{home}”);//go to first column
notlastColumn = true;

}

}