Using excel File in asp.net is very easy and very useful in many places For example in many conditions you want to upload data online through excel file into database Like Offline Attendence,Offline entries.Below code not only help you in just reading the excel file but also to apply many validation like excel sheets count,single sheet column count,single column rows count,file upload extension name etc etc.But this code works only for .xls file extension but not for .xlsx.Before Using below code import namespaces written below and use fileupload control for file upload and button to upload excel and parse it.
using ExcelLibrary.SpreadSheet;
using ExcelLibrary;
// on button click
if (flupload.HasFile == true)
{
string path = Path.GetExtension(flupload.PostedFile.FileName);
if (path == ".xls")
{
string FileName = Path.GetFileName(flupload.PostedFile.FileName);
flupload.SaveAs(Server.MapPath("~/" + "Formats/" + FileName));
string FullPath = HttpContext.Current.Server.MapPath("~/" + "Formats/" + FileName);
string sheetname = null;
Workbook book = Workbook.Load(FullPath);
int countt = book.Worksheets.Count;
if (countt > 1)
{
DisplayAJAXMessage("Excel Contains More Than one Sheet !! Please Use only one Sheet in Excel File !!");
}
else
{
Worksheet sheet = book.Worksheets[0];
sheetname = sheet.Name;
if (sheetname != null)
{
exceldt = ExcelLibrary.DataSetHelper.CreateDataTable(FullPath, sheetname);
}
else
{
DisplayAJAXMessage("No Sheet in Excel File !!");
}
if (exceldt != null)
{
//check for empty list
bool emptycheck = true;
bool duplicatecheck = true;
bool numbercheck = true;
bool countreg = true;
bool emptyexcel = true;
foreach (DataRow gr in exceldt.Rows)
{
if (gr[0].ToString() == "" || gr[0].ToString() == " " || gr[0].ToString() == " ")
{
DisplayAJAXMessage("There is empty row in excel !! Please check it ");
emptycheck = false;
break;
}
}
ArrayList dup = new ArrayList();
ArrayList dup1 = new ArrayList();
foreach (DataRow gg in exceldt.Rows)
{
dup.Add(gg[0].ToString());
}
//check for empty row
if (dup.Count == 0)
{
DisplayAJAXMessage("No Data Found !! Please Check The File");
emptyexcel = false;
}
//count registrationnumber for 400 or not
if (dup.Count > 400)
{
DisplayAJAXMessage("Registration Number count=" + dup.Count.ToString() + " Only 400 Registration Numbers can be promoted at a time !!");
countreg = false;
}
//check duplicate
int county = dup.Count - 1;
if (countreg == true)
{
for (int i = 0; i < county; i++)
{
dup1.Add(dup[0].ToString());
dup.RemoveAt(0);
if (dup.Contains(dup1[i].ToString()))
{
duplicatecheck = false;
DisplayAJAXMessage("Excel File Contains DUPLICATE values !! Please check");
break;
}
}
}
dup.Clear();
dup1.Clear();
Int64 num;
//check number or string
foreach (DataRow gr in exceldt.Rows)
{
bool isNum = Int64.TryParse(gr[0].ToString(), out num);
if (isNum == false)
{
DisplayAJAXMessage("There is string in Excel instead of Registration Number !! Please check it");
numbercheck = false;
break;
}
}
//checking all constraints finally generate grid
if (emptycheck == true && duplicatecheck == true && numbercheck == true && countreg == true && emptyexcel == true)
{
// here you will do your code after checking excel extension,duplicate values ,count in one column,count of sheet ,is excel file empty or not
}
}
Done !! :)
using ExcelLibrary.SpreadSheet;
using ExcelLibrary;
// on button click
if (flupload.HasFile == true)
{
string path = Path.GetExtension(flupload.PostedFile.FileName);
if (path == ".xls")
{
string FileName = Path.GetFileName(flupload.PostedFile.FileName);
flupload.SaveAs(Server.MapPath("~/" + "Formats/" + FileName));
string FullPath = HttpContext.Current.Server.MapPath("~/" + "Formats/" + FileName);
string sheetname = null;
Workbook book = Workbook.Load(FullPath);
int countt = book.Worksheets.Count;
if (countt > 1)
{
DisplayAJAXMessage("Excel Contains More Than one Sheet !! Please Use only one Sheet in Excel File !!");
}
else
{
Worksheet sheet = book.Worksheets[0];
sheetname = sheet.Name;
if (sheetname != null)
{
exceldt = ExcelLibrary.DataSetHelper.CreateDataTable(FullPath, sheetname);
}
else
{
DisplayAJAXMessage("No Sheet in Excel File !!");
}
if (exceldt != null)
{
//check for empty list
bool emptycheck = true;
bool duplicatecheck = true;
bool numbercheck = true;
bool countreg = true;
bool emptyexcel = true;
foreach (DataRow gr in exceldt.Rows)
{
if (gr[0].ToString() == "" || gr[0].ToString() == " " || gr[0].ToString() == " ")
{
DisplayAJAXMessage("There is empty row in excel !! Please check it ");
emptycheck = false;
break;
}
}
ArrayList dup = new ArrayList();
ArrayList dup1 = new ArrayList();
foreach (DataRow gg in exceldt.Rows)
{
dup.Add(gg[0].ToString());
}
//check for empty row
if (dup.Count == 0)
{
DisplayAJAXMessage("No Data Found !! Please Check The File");
emptyexcel = false;
}
//count registrationnumber for 400 or not
if (dup.Count > 400)
{
DisplayAJAXMessage("Registration Number count=" + dup.Count.ToString() + " Only 400 Registration Numbers can be promoted at a time !!");
countreg = false;
}
//check duplicate
int county = dup.Count - 1;
if (countreg == true)
{
for (int i = 0; i < county; i++)
{
dup1.Add(dup[0].ToString());
dup.RemoveAt(0);
if (dup.Contains(dup1[i].ToString()))
{
duplicatecheck = false;
DisplayAJAXMessage("Excel File Contains DUPLICATE values !! Please check");
break;
}
}
}
dup.Clear();
dup1.Clear();
Int64 num;
//check number or string
foreach (DataRow gr in exceldt.Rows)
{
bool isNum = Int64.TryParse(gr[0].ToString(), out num);
if (isNum == false)
{
DisplayAJAXMessage("There is string in Excel instead of Registration Number !! Please check it");
numbercheck = false;
break;
}
}
//checking all constraints finally generate grid
if (emptycheck == true && duplicatecheck == true && numbercheck == true && countreg == true && emptyexcel == true)
{
// here you will do your code after checking excel extension,duplicate values ,count in one column,count of sheet ,is excel file empty or not
}
}
Done !! :)
Comments
Post a Comment