How to Open,Edit, Save Excel Sheet in .NET Windows Application

Note: Don't Forget to add References. For Add reference follow given below process:

1. Right Click on Reference folder in Solution Explorer.

2. Choose Add reference Option, then a Reference Dialog box will be open.

3. In the dialog box Choose .NET Tab, then choose three assemblies.

          (a.)Interop.Microsoft.Office.Interop.Excel

          (b.)Interop.Microsoft.Office.Interop

          (c.)Microsoft.VisualBasic

Now Add these Assemblies in Your project.

using Excel=Microsoft.Office.Interop.Excel;

using Microsoft.Office.Interop;

using Microsoft.VisualBasic;

public void EditExcel()

        {

            try

            {

             

                int row = 1;

        openFileDialog1.ShowDialog();// Open Dialog Box //control should be defined in Form Conrol

        textBox1.Text = openFileDialog1.FileName;

            if (textBox1.Text =="")

            {

                Interaction.MsgBox("Please Select File Path", MsgBoxStyle.Information, "Employee");

                return;

            }

string file =System.IO.Path.GetFullPath(textBox1.Text);

                Excel.Application app = new Excel.Application();

                Excel.Workbook workbook;

                app = new Excel.Application();

                app.Visible = false;

                workbook = app.Workbooks.Open(file, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                Excel.Worksheet excel = (Excel.Worksheet)workbook.Sheets.get_Item(1);//Missing.Value, Missing.Value, 1, Excel.XlSheetType.xlWorksheet);

                excel.Name = "In_&_Out_data";

                // for delete the "A" Index Column

                while (row < excel.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Missing.Value).Row + 1)

                {

                    if (excel.Cells[row, 5].ToString() == "Total : ")

                    {

                        excel.Cells[row, 5] = "";

                    }

                    row++;

                }

                Excel.Range range = (Excel.Range)excel.get_Range("A1", Missing.Value);

                range.EntireColumn.Delete(Missing.Value);

                System.Runtime.InteropServices.Marshal.ReleaseComObject(range);

                // Remove the "Total:" string from file

                Filename = "C:\\" + excel.Name + ".xlsx";

                if (System.IO.File.Exists(Filename))

                {

                    System.IO.File.Delete(Filename);

                }

                excel.SaveAs(Filename, Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, 1, 1, 1, 1, 1, 1);

                //excel.SaveAs(Filename, Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            }

            catch (Exception ex)

            {

                MessageBox.Show("There is Problem to Read and Edit Primary Excel Sheet");

                return;

            }

        }

How to Show sum of Time from a Table for Total working Hours Calculation in Sql Server 2005

Create Given Below is Attendance Table :

Now Calculate Total Working Hours group by Personal_code. The OutPut Should be Like Given below :

For Getting this output use Given Below Query:

SELECT Personal_Code, convert(varchar(2),SUM(DATEDIFF(minute, '00:00:00', convert(datetime, totalTime, 8)))/60)+':'

+

case when len(convert(varchar(2),SUM(DATEDIFF(minute, '00:00:00', convert(datetime, totalTime, 8)))%60)) = 1

then

'0'+convert(varchar(2),SUM(DATEDIFF(minute, '00:00:00', convert(datetime, totalTime, 8)))%60)

else

convert(varchar(2),SUM(DATEDIFF(minute, '00:00:00', convert(datetime, totalTime, 8)))%60)

end

as totalTime

FROM Attandance

GROUP BY Personal_Code

How to Import Google Contacts Using Google Data API

Step 1 : Download Google Data API Microsoft Installer from Here


Step 2 : Make a folder Named Bin in Your solution Explorer and right Click on it and choose Add reference option from the Popup menu.

 

Step 3 : Choose Google Data API Contact Library option and click OK.

Now You can see the added library files in you bin folder.

Step 4 : Now add the put the code in your Default.aspx.cs file.

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using Google.GData.Contacts;

using Google.GData.Client;

using Google.GData.Extensions;

using Google.Contacts;

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        RequestSettings rs = new RequestSettings("Application Name", "jeetu.choudhary13@gmail.com", "***********");

        ContactsRequest cr = new ContactsRequest(rs);//Request All Contacts

        rs.AutoPaging = true;

        Feed<Contact> f = cr.GetContacts();

        DataTable dt = new DataTable();

        DataRow dr;

        dt.Columns.Add("Name");

        dt.Columns.Add("Home Emails");

        dt.Columns.Add("Work Mails");

        dt.Columns.Add("Other Mails");

        dt.Columns.Add("Work Phone");

        dt.Columns.Add("Other");

        foreach (Contact contact in f.Entries)

        {

            dr = dt.NewRow();

            Name n = contact.Name;

            dr[0] = n.FullName;

            string homeEmail = String.Empty;

            string workEmail = String.Empty;

            string otherEmail = String.Empty;

            string homePhone = String.Empty;

            string workPhone = String.Empty;

            string otherPhone = String.Empty;

            foreach (EMail email in contact.Emails)

            {

                if (email.Home == true)

                {

                    if (homeEmail.Equals(""))

                    {

                        homeEmail += email.Address;

                    }

                    else

                    {

                        homeEmail += ",";

                        homeEmail += email.Address;

                    }

                }

                if (email.Work == true)

                {

                    if (workEmail.Equals(""))

                    {

                        workEmail += email.Address;

                    }

                    else

                    {

                        workEmail += ",";

                        workEmail += email.Address;

                    }

                }

                else

                {

                    if (otherEmail.Equals(""))

                    {

                        otherEmail += email.Address;

                    }

                    else

                    {

                        otherEmail += ",";

                        otherEmail += email.Address;

                    }

                }

                dr[1] = homeEmail;

                dr[2] = workEmail;

                dr[3] = otherEmail;

            }

            dt.Rows.Add(dr);

            gv1.DataSource = dt;

            gv1.DataBind();

        }

    }

}

Step 5 : Put the code in Default.aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

    <form id="form1" runat="server">

        <asp:ScriptManager ID="ScriptManager1" runat="server" />

        <div>

       

            <asp:GridView ID="gv1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">

                <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />

                <RowStyle BackColor="#E3EAEB" />

                <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />

                <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />

                <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />

                <EditRowStyle BackColor="#7C6F57" />

                <AlternatingRowStyle BackColor="White" />

            </asp:GridView>

        </div>

    </form>

</body>

</html>

Step 6 : Final output is Given Below :