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;
}
}