Excelsheet are most important document type which we are using in daily life. You can easily Integrate and display microsoft documents in programming language. In our previous article we show how to Export Gridview to Excel using asp.net C#. In this article we are showing how to Import Excel data to Gridview using asp.net C#.
We need to add different ConnectionString for both .xls and xlsx files, because both file required different Microsoft Engine. Please read how to declare ConnectionString in Web.Config file.
We need to add different ConnectionString for both .xls and xlsx files, because both file required different Microsoft Engine. Please read how to declare ConnectionString in Web.Config file.
<connectionStrings>
<add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
<add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 12.0;HDR={1}'"/>
</connectionStrings>
Add Gridview, label and button to your aspx page. We have implement this example with various message you will see in our example.Default.aspx
<asp:Label ID="lblMsg" runat="server" Visible="false"></asp:Label> <asp:FileUpload ID="FileUploadToServer" runat="server" /> <asp:Button ID="btnUpload" Text="Upload" runat="server" OnClick="btnUpload_Click" /> <asp:GridView ID="grvBatchUpload" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None"> </asp:GridView>
For upload file to specific folder you need to create Uploads folder to your system and need to give path to FilePath string.
Default.aspx.cs
public void BindGridView(){
string FilePath = ResolveUrl("~/Uploads/"); // Give Upload File Path
string filename = string.Empty;
if (FileUploadToServer.HasFile) // Check FileControl has any file or not
{
try
{
string[] allowdFile = { ".xls", ".xlsx" };
string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName).ToLower();// get extensions
bool isValidFile = allowdFile.Contains(FileExt);
// check if file is valid or not
if (!isValidFile)
{
lblMsg.Visible = true;
lblMsg.Style.Add("color", "red");
lblMsg.Text = "Please upload only Excel";
}
else
{
int FileSize = FileUploadToServer.PostedFile.ContentLength; // get filesize
if (FileSize <= 1048576) //1048576 byte = 1MB
{
filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName));// get file name
FileUploadToServer.SaveAs(Server.MapPath(FilePath) + filename); // save file to uploads folder
string filePath = Server.MapPath(FilePath) + filename;
string conStr = "";
if (FileExt == ".xls") // check for excel file type
{
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
}
else if (FileExt == ".xlsx")
{
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
}
conStr = String.Format(conStr, filePath, "Yes");
OleDbConnection con = new OleDbConnection(conStr);
OleDbCommand ExcelCommand = new OleDbCommand();
ExcelCommand.Connection = con;
con.Open();
DataTable ExcelDataSet = new DataTable();
ExcelDataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
DataTable dt = new DataTable();
if (ExcelDataSet != null && ExcelDataSet.Rows.Count > 0)
{
string SheetName = ExcelDataSet.Rows[0]["TABLE_NAME"].ToString(); // get sheetname
ExcelCommand.CommandText = "SELECT * From [" + SheetName + "]";
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
ExcelAdapter.SelectCommand = ExcelCommand;
ExcelAdapter.Fill(dt);
}
con.Close();
if (dt != null && dt.Rows.Count > 0) // Check if File is Blank or not
{
grvBatchUpload.DataSource = dt;
grvBatchUpload.DataBind();
lblMsg.Visible = false;
}
else
{
lblMsg.Visible = true;
lblMsg.Style.Add("color", "red");
lblMsg.Text = "There are No Rows in this File!!!";
}
FilePath = ResolveUrl("~/Uploads/");
string fileName = Server.MapPath(FilePath) + filename;
FileInfo f = new FileInfo(fileName);
if (f.Exists)
{
f.IsReadOnly = false;
f.Delete();
}
}
else
{
lblMsg.Visible = true;
lblMsg.Style.Add("color", "red");
lblMsg.Text = "Attachment file size should not be greater then 1 MB!";
}
}
}
catch (Exception ex)
{
lblMsg.Visible = true;
lblMsg.Style.Add("color", "red");
lblMsg.Text = "Error occurred while uploading a file: " + ex.Message;
}
}
else
{
lblMsg.Visible = true;
lblMsg.Style.Add("color", "red");
lblMsg.Text = "Please select a file to upload.";
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
BindGridView();
}
Demo
Download Sample Code
