How to Import data from excel sheet to datatable and bind to listview

Here what we are doing is we have to upload the file into our application and the import data into the data table and then delete the file from our application.
After uploading the document using Fileupload control we have to check weather the upload document is path existing or not,file format is correct or not

Note : excel sheet format should be like this
      Sno      Email
        1         1@1.1
        2         2@2.2

protected void btnImportto_Click(object sender, EventArgs e)
        {
            try
            {
                //Checking weather file exist or not
                if (uploadExcel.HasFile)
                {
                    //checking the weather existing file has extension .xlsx or .xls
                    if (Path.GetExtension(uploadExcel.FileName) == ".xlsx" ||                              Path.GetExtension(uploadExcel.FileName) == ".xls")
                 {
                        //getting path here
                        string filepath = Server.MapPath("~/");
                        //saving into paticular path
                        uploadExcel.SaveAs(filepath + uploadExcel.PostedFile.FileName);
                        //exact file path
                        string relativePath = Server.MapPath("~/" + uploadExcel.PostedFile.FileName);
                        lblStatusmsg.Text = "Upload status: File uploaded!";
                        //importing data from the excel sheet to our application
                        TestExcel(relativePath);
                    }
                    else
                    {
                        lblStatusmsg.Text = "The file you uploaded has invalid extension,Please upload xls or xlsx file";
                    }

                    string filepathtodelete = Server.MapPath("~/"+uploadExcel.PostedFile.FileName);
                    if (uploadExcel.HasFile)
                    {
                        //delete file from the respective path
                        File.Delete(filepathtodelete);
                    }
                }
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
        }

private void TestExcel(string filePath)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Workbook book = null;
            Range range = null;
            try
            {
                app.Visible = false;
                app.ScreenUpdating = false;
                app.DisplayAlerts = false;
                string execPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
                //Getting the Excel path

                book = app.Workbooks.Open(filePath, 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);
                foreach (Worksheet sheet in book.Worksheets)
                {
                    Console.WriteLine(@"Values for Sheet " + sheet.Index);
                    // get a range to work with
                    range = sheet.get_Range("A1", Missing.Value);
                    // get the end of values to the right (will stop at the first empty cell)
                    range = range.get_End(XlDirection.xlToRight);
                    // get the end of values toward the bottom, looking in the last column (will stop at first empty cell)
                    range = range.get_End(XlDirection.xlDown);
                    // get the address of the bottom, right cell
                    string downAddress = range.get_Address(false, false, XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
                    // Get the range, then values from a1
                    range = sheet.get_Range("A1", downAddress);
                    object[,] values = (object[,])range.Value2;
                    // View the values
                    Console.Write("\t");
                    Console.WriteLine();
                    for (int i = 2; i <= values.GetLength(0); i++)
                    {
                        for (int j = 1; j <= values.GetLength(1); j++)
                        {
                            //Console.Write("{0}\t", values[i, j]);
                            System.Data.DataTable dtbulk = new System.Data.DataTable();

                            if (Session["BulkReceipts"] == null)
                            {
                                //here we are creating the colums
                                //Here you can add the columns as your wish
                                dtbulk.Columns.Add("Active", typeof(bool));
                                dtbulk.Columns.Add("Receiptent");
                            }
                            else
                            {
                                dtbulk = (System.Data.DataTable)Session["BulkReceipts"];
                            }
                             //Here we getting paticular colum value or text              
                            String strContains = values[i, j].ToString( );
                            if (strContains.Contains("@"))//Checking weather mailid or not
                            {
                                dtbulk.Rows.Add(true, values[i, j]);
                                //dtbulk = (System.Data.DataTable)Session["Receipts"];
                                Session["BulkReceipts"] = dtbulk;
                                //dtbulk.Rows.Clear();
                            }
                        }
                    }
                    if (Session["BulkReceipts"] != null)
                    {
                        System.Data.DataTable dtbulk = (System.Data.DataTable)Session["BulkReceipts"];
                       //here i am adding the data to listview
                        lvReceiptents.DataSource = dtbulk;
                        lvReceiptents.DataBind();
                    }
                }
            }
            catch (Exception e)
            {
               //Console.WriteLine(e);
            }
            finally
            {
                range = null;
                if (book != null)
                    book.Close(false, Missing.Value, Missing.Value);
                book = null;
                if (app != null)
                    app.Quit();
                app = null;
            }
        }

How to bind the records to a list in MVC

• The major advantage in MVC is there is no need to write Bulk amount of code. Here by writing the single line of code we can bind the records from database to list....

VIEW::viewlist.aspx:

<body>

    <table>

        <tr>

            <th></th>

            <%--<th>

                EmpID

            </th>--%>

            <th>

                Code

            </th>

            <th>

                Salary

            </th>

            <th>

                CustomerName

            </th>

        </tr>

    <% foreach (var item in Model) { %>

    

        <tr>

            <td>

                <%: Html.ActionLink("Edit", "Editcustomer","Customer", new { id =item.EmpID }, new object { })%> |

                <%: Html.ActionLink("Details", "Details", new { id = item.EmpID })%> |

                <%: Html.ActionLink("Delete", "DeleteCustomer", new { id = item.EmpID })%>                

            </td>

           <%-- <td>

                <%: item.EmpID %>

            </td>--%>

            <td>

                <%: item.Code %>

            </td>

            <td>

                <%: item.Salary %>

            </td>

            <td>

                <%: item.CustomerName %>

            </td>

        </tr>

    

    <% } %>

    </table>

    <p>

        <%: Html.ActionLink("Create New", "returntofillcustomer1", "Customer")%>

    </p>

</body>

Controller:: Customer

//Here we are binding the list of customers to list
public ActionResult Viewlist()
        {  
            //here we are returning the list of customers from the model class(from tb_EmpDetails)
            return View(Customer.GetCustomerDetails());
        }

Modelclass:: Customer.cs

//Here we are returning the employee list
 public static List<tb_EmpDetails> GetCustomerDetails( )
        {
            var customer1 = new CustomerEntities1( );//CustomerEntities1 is nothing but Database (.emdf) file
            //adding all the records from tb_EmpDetails to the list and returning the list
            return customer1.tb_EmpDetails.ToList( );
        }

How to insert data into table in MVC

After filling the form and click the submit, definately  the the form will post some data
View: fillcustomer1.aspx
<body>
    <% using (Html.BeginForm("FillCustomer1", "Customer", FormMethod.Post))
       {%>
        <%: Html.ValidationSummary(true) %>

        <fieldset>
            <legend>Fields</legend>
         
            <div class="editor-label">
                <%: Html.LabelFor(model => model.Code) %>
            </div>
            <div class="editor-field">
                <%: Html.TextBoxFor(model => model.Code) %>
                <%: Html.ValidationMessageFor(model => model.Code) %>
            </div>
         
            <div class="editor-label">
                <%: Html.LabelFor(model => model.CustomerName) %>
            </div>
            <div class="editor-field">
                <%: Html.TextBoxFor(model => model.CustomerName) %>
                <%: Html.ValidationMessageFor(model => model.CustomerName) %>
            </div>
         
            <div class="editor-label">
                <%: Html.LabelFor(model => model.Salary) %>
            </div>
            <div class="editor-field">
                <%: Html.TextBoxFor(model => model.Salary) %>
                <%: Html.ValidationMessageFor(model => model.Salary) %>
            </div>
         
            <p>
                <input type="submit" value="Create" />
            </p>
        </fieldset>

    <% } %>

    <div>
        <%: Html.ActionLink("Back to List", "Viewlist","Customer")%>
    </div>
</body>

Now we have to insert the data into database:

Controller: Customer: in the below code CustomerEntities1  is nothing but our database(.emdx) file

        [Authorize] //Here we are just designing our action with authorize for form authentications
        [HttpPost]
        public ActionResult FillCustomer1(tb_EmpDetails emp_details)
        {
            //here we are using the .edmx(copy of database) file.....
            using (CustomerEntities1 customerdetails = new CustomerEntities1())
            {
                if (ModelState.IsValid)
                {
                    //here we are passing(adding) the  values to table using addtotable
                    customerdetails.AddTotb_EmpDetails(emp_details);
                    //after adding the values to table save the table
                    customerdetails.SaveChanges();
                    //redirecting from one action to another action
                    return RedirectToAction("Viewlist", "Customer");
                }
                else
                {
                    ModelState.AddModelError("", "Please enter fields properly");
                    //Content("<script language='javascript' type='text/javascript'>alert('Please enter fields properly');</script>");
                    return View();                      
                }
            }

        }

HOW TO DELETE ROWS FROM A TABLE WITHOUT USING WHERE CONDITION

If we want to delete top 1 row (or) top 2 (or) top 3 (or) top 5 ... etc

We use the query below

Syntax::  Delete top (no. of rows) from <table name>

example:: delete top (2) from tbEmployees

USE OF LOGGERS IN ASP.NET

The main purpose of creating logger file in our project is that, after deploying our project in to the server we are not able to check the errors or line by line debugging, In that time we have to know , what are the errors are coming when our project is running on the live(available in the website).

In that time if we use the below code in our project, then all the errors will be saved/stored in a text document in our system in a folder.

Why i wrote the path in web.config file means, when we are Hosting(up) our project in to server, in that time we will change only web.config file not the code.
You can save this log files in your database also.
-->We are creating our logger text file in E: Drive.(We can change the drive also)


<appSettings>
<add key="log" value="E:\\Logger.Log"/>
</appSettings>


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.IO;

namespace Project
{
   public static class logger
    {
       public static void log(string className, string method, string message)
        {
            FileStream fs=null; StreamWriter sw=null;
            try
            {
                // Here we are calling the path from web.config file
                string str=ConfigurationManager.AppSettings["log"];
                fs = new FileStream(str, FileMode.Append);
                sw = new StreamWriter(fs);
                sw.WriteLine(DateTime.Now.ToString() + "\t" + className+ "\t" + method + "\t" + message);
                //Don't forgot to close Stream Writer before File Stream
                sw.Close();
                fs.Close();
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                if(sw!=null)
                    sw.Close();
                if(fs!=null)
                    fs.Close();
            }
        }
    }
}


// how we will use this Logger method in our project

public class MessageDB
    {
public void InsertMessage(long UID, MessageType Type, string Message, string Remarks)
        {
// logger is the class name and log is the method name
           logger.log(" MessageDB","InsertMessage","Begin");          
            try
            {
               // our code
                    logger.log(" MessageDB", "InsertMessage", "End");
                }
            }
            catch (Exception ex)
            {
                logger.log("MessageDB", "InsertMessage", "Exception" + ex.Message);              
               throw ex;
            }
            finally
            {

            }
        }


// how the file is stored in our directory
In the text file format it will store.
Logger.Log // text file(Logger.Log is file name)
the text inside of the file will be stored like below

--> If we wont get any error in our project means the text look like blow
6/8/2013 10:37:15 AM     " MessageDB",   "InsertMessage",   "Begin"
6/8/2013 10:37:15 AM    " MessageDB",    "InsertMessage",    "END"

--> If we got any error in our project means the text look like blow
6/8/2013 10:37:15 AM Login login Start
6/8/2013 10:37:15 AM UserBiz AuthenticateUser start
6/8/2013 10:37:15 AM UserBiz AuthenticateUser end
6/8/2013 10:37:15 AM UserDB AutenticateUser start
6/8/2013 10:37:15 AM UserDB AuthenticateUser MessageCannot open database "EmployeeDataBase" requested by the login. The login failed.

PURPOSE OF STREAM WRITER IN C#.NET:-
StreamWriter writes text files. It enables easy and efficient text output. It is best placed in a using-statement to ensure it is removed from memory when no longer needed. It provides several constructors and many methods.
We first declare and initialize a new StreamWriter instance in a using construct. Please note how the System.IO namespace is included at the top of the file. The keyword using means different things in different places.
Example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace StreamReadWrite
{
    class Program
    {
        static void Main(string[] args)
        {
            // Get the directories currently on the C drive.
            DirectoryInfo[] cDirs = new DirectoryInfo(@"c:\").GetDirectories();

            // Write each directory name to a file. 
            using (StreamWriter sw = new StreamWriter("CDriveDirs.txt"))
            {
                foreach (DirectoryInfo dir in cDirs)
                {
                    sw.WriteLine(dir.Name);

                }
            }

            // Read and show each line from the file. 
            string line = "";
            using (StreamReader sr = new StreamReader("CDriveDirs.txt"))
            {
                while ((line = sr.ReadLine()) != null)
                {
                    Console.WriteLine(line);
                }
            }
        }
    }
}

HOW TO COMPARE TWO EXCEL SHEETS AND DISPLAY THE RESULT IN NEW EXCEL SHEET USING C#.NET?

Consider two excel sheets and specify the data as follows
Excel1.xslx
ID      NAME   Qualification
1        bindu       B-tech
2        Anu         Degree
3        Deepthi   M-tech
4        indu         Mca
5        Amulya    Mba
Excel2.xslx
ID      NAME   Qualification
1        bindu       B-tech
2        Anu         Degree
3        Deepthi   M-tech
Now write the code and it will automatically generates a new Excel sheet as follows
Result.xslx
ID      NAME   Qualification
4        indu         Mca
5        Amulya    Mba

Coding:-
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;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelEg
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void BtnBrwse_Click(object sender, EventArgs e)
        {
            OpenFileDialog DialogA = new OpenFileDialog();
            DialogA.CheckFileExists = true;
            //DialogA.InitialDirectory = "Desktop"
            DialogA.Title = "Select a File";

            if (DialogA.ShowDialog() == DialogResult.OK)
            {
                FileTxt1.Text = DialogA.FileName;
            }
            else if (FileTxt1.Text == "")
            {
                MessageBox.Show("Please Select a File");
            }
        }

        private void BtnBrwse2_Click(object sender, EventArgs e)
        {
            OpenFileDialog DialogB = new OpenFileDialog();
            DialogB.CheckFileExists = true;
            DialogB.Title = "Select a file";
            if (DialogB.ShowDialog() == DialogResult.OK)
            {
                FileTxt2.Text = DialogB.FileName;
            }
            else if (FileTxt2.Text == "")
            {
                MessageBox.Show("Please Select a File");
            }
        }

        private void Btn_cmpare_Click(object sender, EventArgs e)
        {
            string filename1 = FileTxt1.Text;
            string filename2 = FileTxt2.Text;
            //string filename3 = FileTxt3.Text;
            string file1_sheet = GetExcelSheets(filename1);
            string file2_sheet = GetExcelSheets(filename2);
            //string file3_sheet = GetExcelSheets(filename3);
            String sConnectionString1 = "Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source=" + filename1 + ";" +
            "Extended Properties=Excel 12.0;";
            String sConnectionString2 = "Provider=Microsoft.ACE.OLEDB.12.0;" +
           "Data Source=" + filename2 + ";" +
           "Extended Properties=Excel 12.0;";
             // Create connection object by using the preceding connection string.
            OleDbConnection objConn = new OleDbConnection(sConnectionString1);
            // Open connection with the database.
            objConn.Open();
            // The code to follow uses a SQL SELECT command to display the data from the worksheet.
            // Create new OleDbCommand to return data from worksheet.
            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + file1_sheet + "$]", objConn);
            // Create new OleDbDataAdapter that is used to build a DataSet
            // based on the preceding SQL SELECT statement.
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
            //Pass the Select command to the adapter.
            objAdapter1.SelectCommand = objCmdSelect;
            // Create new DataSet to hold information from the worksheet.
            DataSet objDataset1 = new DataSet();
            objAdapter1.Fill(objDataset1, "XLData");
            DataTable dt1 = objDataset1.Tables[0];
            objConn.Close();
            objConn = new OleDbConnection(sConnectionString2);
            // Open connection with the database.
            objConn.Open();
            // The code to follow uses a SQL SELECT command to display the data from the worksheet.
            // Create new OleDbCommand to return data from worksheet.
            objCmdSelect = new OleDbCommand("SELECT * FROM [" + file2_sheet + "$]", objConn);
            // Create new OleDbDataAdapter that is used to build a DataSet
            // based on the preceding SQL SELECT statement.
            objAdapter1 = new OleDbDataAdapter();
            // Pass the Select command to the adapter.
            objAdapter1.SelectCommand = objCmdSelect;
            // Create new DataSet to hold information from the worksheet.
            objDataset1 = new DataSet();
            // Fill the DataSet with the information from the worksheet.
            objAdapter1.Fill(objDataset1, "XLData");
            DataTable dt2 = objDataset1.Tables[0];
            // Clean up objects.
            objConn.Close();
            DataTable dtresult = new DataTable();
            dtresult = Comparedata(dt1, dt2);
         
        }
        public DataTable Comparedata(DataTable Firsttb, DataTable Secondtb)
        {
            Firsttb.TableName = "FirstTable";
            Secondtb.TableName = "SecondTable";
            //Create Empty Table
            DataTable table = new DataTable("Difference");
            try
            {
                //Must use a Dataset to make use of a DataRelation object
                using (DataSet ds = new DataSet())
                {
                    //Add tables
                    ds.Tables.AddRange(new DataTable[]
                     {
                    Firsttb.Copy(), Secondtb.Copy()});
                    DataColumn[] firstcolumns = new DataColumn[ds.Tables[0].Columns.Count];
                    for (int i = 0; i < firstcolumns.Length; i++)
                    {
                        firstcolumns[i] = ds.Tables[0].Columns[i];
                    }
                    DataColumn[] secondcolumns = new DataColumn[ds.Tables[1].Columns.Count];
                    for (int i = 0; i < secondcolumns.Length; i++)
                    {
                        secondcolumns[i] = ds.Tables[1].Columns[i];
                    }
                    //Create DataRelation
                    DataRelation r = new DataRelation(string.Empty, firstcolumns, secondcolumns, false);
                    ds.Relations.Add(r);

                    for (int i = 0; i < Firsttb.Columns.Count; i++)
                    {
                        table.Columns.Add(Firsttb.Columns[i].ColumnName, Firsttb.Columns[i].DataType);
                    }
                    //If First Row not in Second, Add to return table.

                    table.BeginLoadData();

                    foreach (DataRow parentrow in ds.Tables[0].Rows)
                    {
                        DataRow[] childrows = parentrow.GetChildRows(r);
                        if (childrows == null || childrows.Length == 0)

                            table.LoadDataRow(parentrow.ItemArray, true);
                    }
                    table.EndLoadData();
               }

            }
            catch (Exception ex)
            {
            }
            string fileName = "Result";
            ExportDataTableToExcel(table, fileName);
            return table;
        }

        private void ExportDataTableToExcel(DataTable dt, string filename)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            int i = 0;
            int j = 0;
            for (i = 0; i < dt.Columns.Count; i++)
            {
                xlWorkSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
            }

            for (i = 0; i < dt.Rows.Count; i++)
            {
                for (j = 0; j < dt.Columns.Count; j++)
                {
                    xlWorkSheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
                }
            }
            try
            {
                xlWorkBook.SaveAs(filename + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
       public string GetExcelSheets(string excelFileName)
        {
            Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workBookObject = null;
            workBookObject = excelFileObject.Workbooks.Open(excelFileName, 0, true, 5, "", "", false,
            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
            "",
            true,
            false,
            0,
            true,
            false,
            false);
            Excel.Sheets sheets = workBookObject.Worksheets;

            // get the first and only worksheet from the collection of worksheets
            Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
            MessageBox.Show(worksheet.Name);

            return worksheet.Name;
        }
    }
}    

     
   

       
   

Purpose of DateTime.ParseExact Method

Converts the specified string representation of a date and time to its DateTime equivalent. The format of the string representation must match a specified format exactly.

Converts the specified string representation of a date and time to its DateTime equivalent using the specified format and culture-specific format information. The format of the string representation must match the specified format exactly.

Supported by the .NET Compact Framework.

[Visual Basic] Overloads Public Shared Function ParseExact(String, String, IFormatProvider) As DateTime

[C#] public static DateTime ParseExact(string, string, IFormatProvider);

[C++] public: static DateTime ParseExact(String*, String*, IFormatProvider*);

[JScript] public static function ParseExact(String, String, IFormatProvider) : DateTime;

DateTime.ParseExact("9/1/2009", "M'/'d'/'yyyy", null);

DateTime.ParseExact("9/1/2009", "M/d/yyyy", CultureInfo.InvariantCulture); 

DateTime.Parse("9/1/2009", new CultureInfo("en-US")); 

The parameters passed to ParseExact are as follows:
1) The actual date, StartDateTime
2) The format the StartDateTime is CURRENTLY in,
3) The culture (or null)

So what you want to try is this:

//
// Get our date
//
string StartDateTime = "11/24/2006 23:59";
//
// Create a datetime object
//
DateTime myDate = DateTime.ParseExact(StartDateTime,"MM/dd/yyyy H:mm",
null);
//
// Capture our desired format in a string
//
string FormattedDate = myDate.ToString("yyyy-MM-dd HH:mm tt");
//
// Now we can use it as we please
//
Console.WriteLine(FormattedDate);

How to convert Numeric values into Roman numbers

The simple way for converting numeric values to Roman numbers :

public static string NumberToRoman(int number)
        {
            //taking string builder here, so we can append roman numbers to this string builder
            StringBuilder result = new StringBuilder();
            if (number <= 0 || number > 3999)
            {
                //throw new ArgumentException("Value must be in the range 0 – 3,999.");
                Console.WriteLine("Value must be in the range 0 to 3,999....!");
                Console.ReadLine();
            }
            else
            {
                int[] values = new int[] { 1000, 900, 500, 400, 100, 90, 50, 40, 10, 9, 5, 4, 1 };
                string[] numerals = new string[] { "M", "CM", "D", "CD", "C", "XC", "L", "XL", "X", "IX", "V", "IV", "I" };

                // Loop through each of the values to diminish the number
                for (int i = 0; i < values.Length; i++)
                {
                    // If the number being converted is less than the test value, append the corresponding numeral or numeral pair to the resultant string(string builder)
                    while (number >= values[i])
                    {
                        number -= values[i];
                        result.Append(numerals[i]);
                    }
                }
                Console.WriteLine(result);
                Console.ReadLine();              
            }
            return result.ToString();
        }

How to print only the screen without taskbar

Press "Alt+PrintScreen" instead of print screen inorder to get only the screen without Taskbar.

How to Import data from grid to pdf and excel in VB.Net

 Import data from grid to pdf :To import data from grid to pdf we can use third part control namely "iTextSharp" so we have to download this "iTextSharp.dll" and add to to your project

http://sourceforge.net/projects/itextsharp/  you can download the iTextSharp.dll from this link

Imports iTextSharp
Imports iTextSharp.text
Imports iTextSharp.text.pdf

Imports iTextSharp.text.html.simpleparser

Protected Sub imgexportpdf_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgexportpdf.Click
        Using sw As New StringWriter()
            Using hw As New HtmlTextWriter(sw)
                'To Export all pages
                gvCustomer.AllowPaging = False
                BindCustomerDetails()

                gvCustomer.RenderControl(hw)
                Dim sr As New StringReader(sw.ToString())
                Dim pdfDoc As New Document(PageSize.A2, 10.0F, 10.0F, 10.0F, 0.0F)
                Dim htmlparser As New HTMLWorker(pdfDoc)
                PdfWriter.GetInstance(pdfDoc, Response.OutputStream)
                pdfDoc.Open()
                htmlparser.Parse(sr)
                pdfDoc.Close()

                Response.ContentType = "application/pdf"
                Response.AddHeader("content-disposition",     "attachment;filename=GridViewExport.pdf")
                Response.Cache.SetCacheability(HttpCacheability.NoCache)
                Response.Write(pdfDoc)
                Response.[End]()
            End Using
        End Using
    End Sub


Import data from grid to Excel:

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Drawing

Imports System.Data.OleDb


 Protected Sub imgexportexcel_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgexportexcel.Click
        Response.Clear()
        Response.Buffer = True
        Response.AddHeader("content-disposition", "attachment;filename=CustomerDetails.xls")
        Response.Charset = ""
        Response.ContentType = "application/vnd.ms-excel"
        Using sw As New StringWriter()
            Dim hw As New HtmlTextWriter(sw)

            'To Export all pages
            gvCustomer.AllowPaging = False
            BindCustomerDetails()

            gvCustomer.HeaderRow.BackColor = Drawing.Color.GreenYellow

            For Each cell As TableCell In gvCustomer.HeaderRow.Cells
                cell.BackColor = Drawing.Color.GreenYellow
            Next
            For Each row As GridViewRow In gvCustomer.Rows
                row.BackColor = Drawing.Color.White
                For Each cell As TableCell In row.Cells
                    If row.RowIndex Mod 2 = 0 Then
                        cell.BackColor = gvCustomer.AlternatingRowStyle.BackColor
                    Else
                        cell.BackColor = gvCustomer.RowStyle.BackColor
                    End If
                    cell.CssClass = "textmode"
                Next
            Next

            gvCustomer.RenderControl(hw)
            'style to format numbers to string
            Dim style As String = "<style> .textmode { } </style>"
            Response.Write(style)
            Response.Output.Write(sw.ToString())
            Response.Flush()
            Response.[End]()
        End Using

    End Sub

How to Convert Rows to Columns in SQL Server Without Using Pivot?

If you observe above table we have name “Suresh” which repeated for 3 times but it’s having different mobiles number in this case I want to display single username with all mobiles numbers like as shown below

 

To implement this kind of functionality we need to write the query like as shown below

DECLARE @tempUserInfo TABLE (UserName VARCHAR(50),Mobile VARCHAR(50))

INSERT INTO @tempUserInfo VALUES ('Suresh',9999999999)

INSERT INTO @tempUserInfo VALUES ('Suresh',8888888888)

INSERT INTO @tempUserInfo VALUES ('Suresh',7777777777)

INSERT INTO @tempUserInfo VALUES ('Mahesh',9000000000)

INSERT INTO @tempUserInfo VALUES ('Mahesh',9005000000)

INSERT INTO @tempUserInfo VALUES ('Madhav',8125000000)

INSERT INTO @tempUserInfo VALUES ('Madhav',8160000000)

INSERT INTO @tempUserInfo VALUES ('Madhav',8165555000)

;WITH UserDetails AS

(   SELECT

*,ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY UserName) AS ColumnNumber

FROM @tempUserInfo

)

SELECT DISTINCT

t.UserName

,t1.Mobile AS website1,t2.Mobile AS website2,t3.Mobile AS website3

FROM @tempUserInfo t

LEFT OUTER JOIN UserDetails t1 ON t.UserName=t1.UserName AND t1.ColumnNumber=1

LEFT OUTER JOIN UserDetails t2 ON t.UserName=t2.UserName AND t2.ColumnNumber=2

LEFT OUTER JOIN UserDetails t3 ON t.UserName=t3.UserName AND t3.ColumnNumber=3 Order byt.userName desc

Above query will change row values to column values and the ouptput like as shown below