<%@ 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">
</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
}
<!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">
</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
}
