Click here to Skip to main content
15,920,217 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Globalization;

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        System.Data.DataTable dtUK = null;
        System.Data.DataTable dt = null;
      
        public Form1()
        {
            InitializeComponent();
        }        
        private void btnbrowse_Click(object sender, EventArgs e)
        {
            /* Used to browse the Excel Sheet and viewd in data grid*/
            try
            {
                OpenFileDialog dlg = new OpenFileDialog();
                dlg.DefaultExt = ".txt";
                dlg.Filter = "EXCEL Files (*.xls)|*.xlsx";
                // dlg.ShowDialog();
                //bool result = (bool)dlg.ShowDialog();
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    string filename = dlg.FileName;

                    Microsoft.Office.Interop.Excel.Application excelapp = new Microsoft.Office.Interop.Excel.Application();
                    Microsoft.Office.Interop.Excel.Workbook workbook = excelapp.Workbooks.Open(filename);
                    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["UK"];

                    int column = 0;
                    int row = 0;
                    Microsoft.Office.Interop.Excel.Range range = worksheet.UsedRange;

                    System.Data.DataTable dt = new System.Data.DataTable();
                    //Earnings Table

                    dt.Columns.Add("Name of the Employe", typeof(String));
                    dt.Columns.Add("Status", typeof(String));
                    dt.Columns.Add("DOL", typeof(String));
                    dt.Columns.Add("PAN No.", typeof(String));
                    dt.Columns.Add("Designation", typeof(String));
                    dt.Columns.Add("Present Days", typeof(String));
                    dt.Columns.Add("BankBranchName", typeof(String));
                    dt.Columns.Add("A/cNo", typeof(String));
                    dt.Columns.Add("PFA/cNo", typeof(String));
                    dt.Columns.Add("Basic Pay", typeof(String));
                    dt.Columns.Add("HRA", typeof(String));
                    dt.Columns.Add("Conveyance Allowance", typeof(String));
                    dt.Columns.Add("Medical Allowance", typeof(String));
                    dt.Columns.Add("Other Allowance", typeof(String));
                    dt.Columns.Add("Special Allowance", typeof(String));
                    dt.Columns.Add("Incentive", typeof(String));
                    dt.Columns.Add("Bonus", typeof(String));
                    dt.Columns.Add("Arrears", typeof(String));
                    dt.Columns.Add("Joining Bonus", typeof(String));
                    dt.Columns.Add("Leave Encashment", typeof(String));
                    //Deductions
                    dt.Columns.Add("TDS", typeof(String));
                    dt.Columns.Add("Professional Tax", typeof(String));
                    dt.Columns.Add("ProvidentFund", typeof(String));
                    dt.Columns.Add("DeductionagainstLoan/advances", typeof(String));
                    dt.Columns.Add("Cab Deductions", typeof(String));
                    dt.Columns.Add("ESI", typeof(String));
                    dt.Columns.Add("NetPay", typeof(String));
                    //Gross (TDS Details)
                    dt.Columns.Add("BasicPay", typeof(String));
                    dt.Columns.Add("emp1HRA", typeof(String));
                    dt.Columns.Add("ConveyanceAllowance", typeof(String));
                    dt.Columns.Add("MedicalAllowance1", typeof(String));
                    dt.Columns.Add("OtherAllowance1", typeof(String));
                    dt.Columns.Add("SpecialAllowance1", typeof(String));
                    dt.Columns.Add("Incentive1", typeof(String));
                    dt.Columns.Add("Bonus1", typeof(String));
                    dt.Columns.Add("Arrears1", typeof(String));
                    dt.Columns.Add("JoiningBonus1", typeof(String));
                    dt.Columns.Add("LeaveEncashment1", typeof(String));
                    dt.Columns.Add("empHRA1", typeof(String));
                    //Previous Employer Details
                    dt.Columns.Add("SalaryfromPreviousEmployer1", typeof(String));
                    dt.Columns.Add("DeductionFromPreviousEmployer1", typeof(String));
                    dt.Columns.Add("RemunerationFromPreviousEmployer1", typeof(String));
                    //Income tax Deduction
                    dt.Columns.Add("Grosssalary1", typeof(String));
                    dt.Columns.Add("Professionaltax1", typeof(String));
                    dt.Columns.Add("OtherDeductions1", typeof(String));
                    dt.Columns.Add("HouseProperty1", typeof(String));
                    dt.Columns.Add("IncomefromOtherSource1", typeof(String));
                    dt.Columns.Add("GrossIncome1", typeof(String));
                    dt.Columns.Add("TotalVI-ADeductions1", typeof(String));
                    dt.Columns.Add("TaxableIncome1", typeof(String));
                    dt.Columns.Add("TotalTax", typeof(String));
                    dt.Columns.Add("Rebateu/s87A", typeof(String));
                    dt.Columns.Add("Totaltaxafterrebate", typeof(String));
                    dt.Columns.Add("Educationcess", typeof(String));
                    dt.Columns.Add("TaxDeductedtilldate", typeof(String));
                    dt.Columns.Add("Taxtobededucted", typeof(String));
                    // Deductions under Chapter VI-A
                    dt.Columns.Add("Deduction80C", typeof(String));
                    dt.Columns.Add("ActualDeduction80C", typeof(string));
                    dt.Columns.Add("ActualDeduction80D", typeof(string));

                    dt.Columns.Add("Deduction80E", typeof(String));
                    dt.Columns.Add("Deduction80E1", typeof(String));
                    dt.Columns.Add("Deduction80E13", typeof(String));

                    for (row = 1; row <= range.Rows.Count; row++)
                    {
                        DataRow dr = dt.NewRow();
                        for (column = 1; column <= range.Columns.Count; column++)
                        {
                            dr[column - 1] = (range.Cells[row, column] as Range).Value2.ToString();
                        }
                        dt.Rows.Add(dr);
                        dt.AcceptChanges();
                    }

                    workbook.Close();
                    excelapp.Quit();

                    dataGridView1.DataSource = dt;
                }               
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}
Posted
Updated 2-Jan-14 18:17pm
v4
Comments
srikanth492 2-Jan-14 23:52pm    
in that gridview values by using existing datatable how to insert datatatable
srikanth492 3-Jan-14 0:23am    
in that datatable 1 row 62 coloums .using stored procedure how insert to database.. my store proc is earning_sp

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900