Friday, April 8, 2011

To Read the Excel's data by Column Wise

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table align="center">
            <tr>
                <td>
                    <table align="center">
                        <tr>
                            <td>
                                <asp:Label ID="lblLabel" runat="server" Text="Upload the CSV File"></asp:Label>
                            </td>
                            <td>
                                <asp:FileUpload ID="fupUpload" runat="server" />
                            </td>
                        </tr>
                        <tr>
                            <td colspan="2">
                                &nbsp;
                            </td>
                        </tr>
                        <tr>
                            <td align="center" colspan="2">
                                <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
                            </td>
                        </tr>
                        <tr>
                            <td align="center" colspan="2">
                                <asp:Label ID="lblStatus" runat="server"></asp:Label>
                                <asp:Label ID="lblMsg" Text="Please Select Proper File" runat="server" BorderColor="White"
                                    Font-Bold="True" ForeColor="Red" Visible="false"></asp:Label>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Panel ID="pnlGetAllRecods" runat="server">
                        <table align="center">
                            <tr>
                                <td>
                                    Sheet Names from XML are follows :
                                    <br />
                                    <asp:RadioButtonList ID="rdbtnList" runat="server">
                                    </asp:RadioButtonList>
                                    <%-- <asp:GridView ID="gvCSVContent" runat="server">
                                    </asp:GridView>--%>
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    <asp:Button ID="btnGetHeaderColumnName" runat="server" Text="Get Header Column Names"
                                        OnClick="btnGetHeaderColumnName_Click" />
                                </td>
                            </tr>
                            <tr>
                                <td>
                                    <asp:Label ID="lblGetColumnCount" runat="server"></asp:Label>
                                </td>
                            </tr>
                            <asp:Panel ID="pnlColumnData" runat="server">
                                <tr>
                                    <td>
                                        <br />
                                        <br />
                                        Main Column Name are follows :
                                        <br />
                                        <asp:CheckBoxList ID="chkbxList" runat="server">
                                        </asp:CheckBoxList>
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                        <asp:Button ID="btnGetColumnCount" runat="server" Text="Get Column Count" OnClick="btnGetColumnCount_Click" />
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                        <asp:Label ID="lblColumnCount" runat="server"></asp:Label>
                                    </td>
                                </tr>
                            </asp:Panel>
                        </table>
                    </asp:Panel>
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>
----------------------------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text.RegularExpressions;
using System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Data.Common;
using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;
//using System.Data.Odbc;

public partial class Account_frmExcelRead : System.Web.UI.Page
{
    #region Page_Load
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.Page.IsPostBack)
        {
            lblColumnCount.Text = "";
            pnlGetAllRecods.Visible = false;
            pnlColumnData.Visible = false;
        }

    }
    #endregion

    #region btnSubmit_Click Event
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        try
        {
            //--- (Start) Saving Process the Uploaded File ---//
            pnlColumnData.Visible = false;
            lblColumnCount.Text = "";
            string SaveLocation;
            if (fupUpload.PostedFile.FileName == string.Empty)
            {
                lblMsg.Visible = true;
                return;
            }
            else
            {
                string strFileName = DateTime.Now.ToBinary() +  "_" +  fupUpload.FileName;

                string[] FileExt = strFileName.Split('.');
                string FileEx = FileExt[FileExt.Length - 1];
                if (FileEx.ToLower() == "xlsx" || FileEx.ToLower() == "xls")
                {
                    SaveLocation = Server.MapPath("~/Files") + "\\" + strFileName;
                    fupUpload.PostedFile.SaveAs(SaveLocation);
                    lblStatus.Text = "File Saved";
                }
                else
                {
                    lblMsg.Visible = true;
                    return;
                }

                ViewState["SaveLocation"] = SaveLocation;
            }
            //--- (Start) Saving Process the Uploaded File ---//

            //--- (Start) Get the Sheet Name from the xls file
            List<string> strSheetName = GetExcelSheetNames(SaveLocation);
            if (strSheetName.Count() > 0)
            {
                pnlGetAllRecods.Visible = true;
                rdbtnList.DataSource = strSheetName;
                rdbtnList.DataBind();
            }
            else
            {
                pnlGetAllRecods.Visible = false;
            }
            //--- (End) Get the Sheet Name from the xls file

        }
        catch (Exception ex)
        {
            lblStatus.Text = ex.Message;
        }
    }
    #endregion

    #region btnGetHeaderColumnName_Click
    protected void btnGetHeaderColumnName_Click(object sender, EventArgs e)
    {
        try
        {
            //--- Counting the coloumn name of Header
            Microsoft.Office.Interop.Excel.Application xlApp;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
            Microsoft.Office.Interop.Excel.Range range;

            string strName;
            int rCnt = 0;
            int cCnt = 0;
            string Path = ViewState["SaveLocation"].ToString().Trim();

            xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Open(Path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

            int intSheetCount = xlWorkBook.Sheets.Count;
            List<string> strListContent = new List<string>();
            //------

            //------
            string strCheckedSheetName = string.Empty;
            string strCheckedCount = string.Empty;

            int j = 1;
            foreach (ListItem listitem in rdbtnList.Items)
            {
                if (listitem.Selected)
                {

                    strCheckedSheetName = listitem.Text;

                    ////-- Not in Use
                    //if (strCheckedCount.Length > 0)
                    //{
                    //    strCheckedCount = strCheckedCount + "," + j.ToString();
                    //}
                    //else
                    //{
                    //    strCheckedCount = j.ToString();
                    //}
                    ////-- Not in Use

                    strCheckedSheetName = strCheckedSheetName + "$";
                    ViewState["CheckedSheetName"] = strCheckedSheetName;

                    xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(j);
                    range = xlWorkSheet.UsedRange;

                    for (rCnt = 1; rCnt <= 1; rCnt++)   // rCnt <= range.Rows.Count;
                    {
                        for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                        {
                            strName = (string)(range.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
                            if (strName !="")
                                strListContent.Add(strName);
                        }
                    }
                }
                j++;
            }

            if (strListContent.Count > 0)
            {

                chkbxList.DataSource = strListContent;
                chkbxList.DataBind();

                pnlColumnData.Visible = true;
            }
           


            //--- Counting the coloumn name of Header

            ////-- Not in Use
            //lblGetColumnCount.Text = " Sheet Position " + strCheckedCount.ToString() + " <BR/ >" + "Sheet that you have selected : " + strCheckedSheetName;
            ////------

            xlWorkBook.Close(true, null, null);
            xlApp.Quit();
        }
        catch (Exception ex)
        {
            lblStatus.Text = ex.Message;
        }
    }
    #endregion

    #region for XLS count of Column
    public int GetNumberOfColumnsInSheet(string filename, string SheetName, string strFieldName)
    {
        int columnsCount = 0;
        string FilePath = filename; //Server.MapPath(filename);
        string excelConnectionString = "";

        // xlsx file
        if (Path.GetExtension(FilePath) == ".xlsx")
        {
            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath +
                                                ";Mode=ReadWrite;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
        }
        // xls extension file
        else if (Path.GetExtension(FilePath) == ".xls")
        {
            excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath +
                                                ";Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
        }

        if (!string.IsNullOrEmpty(excelConnectionString))
        {
            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = excelConnectionString;
                using (DbCommand command = connection.CreateCommand())
                {
                    // Cities$ comes from the name of the worksheet
                    command.CommandText = "SELECT [" + strFieldName + "] FROM [" + SheetName + "]";
                    connection.Open();

                    using (DbDataReader dr = command.ExecuteReader())
                    {
                        int i = 0;
                        while (dr.Read())
                        {
                            if (i == 92)
                            {

                            }
                            if (dr[strFieldName].ToString() != "")
                            {
                                Debug.WriteLine(dr[strFieldName].ToString());
                                i++;
                            }
                        }
                        columnsCount = i;
                    }
                }
            }
        }

        return columnsCount;
    }
    #endregion

    #region GetExcelSheetNamae with Correct Order
    private List<string> GetExcelSheetNames(string fileName)
    {
        //--- counting the coloumn name by giving the Sheet No
        Microsoft.Office.Interop.Excel.Application xlApp;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        List<string> excelSheets = new List<string>();

        try
        {
            xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Open(ViewState["SaveLocation"].ToString().Trim(), 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

            int intSheetCount = xlWorkBook.Sheets.Count;

            if (xlWorkBook != null)
            {
                foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in xlWorkBook.Sheets)
                {
                    excelSheets.Add(sheet.Name);
                }
            }
        }
        catch (Exception e)
        {
            return null;
        }
        return excelSheets;
    }
    #endregion

    #region btnGetColumnCount_Click
    protected void btnGetColumnCount_Click(object sender, EventArgs e)
    {
        try
        {
           
            int intTotColumnsCount = 0;

            foreach (ListItem item in chkbxList.Items)
            {
                if (item.Selected)
                {
                    intTotColumnsCount += GetNumberOfColumnsInSheet(ViewState["SaveLocation"].ToString().Trim(), ViewState["CheckedSheetName"].ToString().Trim(), item.Value);
                }
            }

            lblColumnCount.Text = "Selected Heading Column's Total Count : " + intTotColumnsCount.ToString();
        }
        catch(Exception ex)
        {

        }
    }
    #endregion
}

No comments:

Post a Comment