OleDB Connection 을 이용하여 간단하게 엑셀 파일을 Read/Write 를 할 수 있는 방법을 찾음.
SqlDataAdapter로 DataSet을 채운 뒤 사용.
버전에 따라 ConnectionString이 다름, 이 점만 주의하면 됨.
--------------------------------------------------------------------------------------------
Fetch the records from excel file using C# .Net, Asp.Net -
Step 1 - Let's create an interface which will contain two buttons for Refresh data from .xls and Refresh data from .xlsx respectively.
Step 2 - Create a gridview in Asp.Net which will present the data fetched from the excel.
Source code for the reference
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<asp:Button ID="btnGetData" runat="server" Text="Refresh xls" OnClick="btnGetData_Click" />
<asp:Button ID="Button1" runat="server" Text="Refresh xlsx" OnClick="Button1_Click" />
<br />
<asp:GridView ID="grdExcel" runat="server">
</asp:GridView>
</asp:Content>
Step 3 - Required name spaces -
using System.Data.OleDb; //To operate the Oledb operations
using System.Data; //To have the data objects like data tables/sets
Step 3 - Write the logic under click event for both buttons respectively.
For .xls file type
/// <summary>
/// Click event: To fetch data from the excel file of .xls type
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnGetData_Click(object sender, EventArgs e)
{
//Specify the excel provider for .xls file type and the file path which contain the excel file
string con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/vsachan/Desktop/Aditya/Sample_Data.xls;Extended Properties='Excel 8.0;HDR=Yes;'";
using (OleDbConnection connection = new OleDbConnection(con))
{
//Open the Oledb connection
connection.Open();
//Specify the command, assume that the sheet name is Sheet1
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
//Define the object of data adaper to run the query
OleDbDataAdapter adap = new OleDbDataAdapter(command);
//Define the dataset to hold the records
DataSet ds=new DataSet();
//Fill the data set
adap.Fill(ds);
//Check the condition if dataset contains any table, It should be at least greater than one
if (ds.Tables.Count >= 1)
{
grdExcel.DataSource = ds.Tables[0];
grdExcel.DataBind();
}
}
}
For .xlsx file type
/// <summary>
/// Click event: To fetch data from the excel file of .xlsx type
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
//Specify the excel provider for .xlsx file type and the file path which contain the excel file
string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/vsachan/Desktop/Aditya/Sample_Data.xlsx;Extended Properties=Excel 12.0 Xml";
using (OleDbConnection connection = new OleDbConnection(con))
{
//Open the Oledb connection
connection.Open();
//Specify the command, assume that the sheet name is Sheet1
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
//Define the object of data adaper to run the query
OleDbDataAdapter adap = new OleDbDataAdapter(command);
//Define the dataset to hold the records
DataSet ds=new DataSet();
//Fill the data set
adap.Fill(ds);
//Check the condition if dataset contains any table, It should be at least greater than one
if (ds.Tables.Count >= 1)
{
grdExcel.DataSource = ds.Tables[0];
grdExcel.DataBind();
}
}
}
Difference in logic - In both the logic only difference is in the connection strings which is highlighted in the above explanation else everything remains same.
At the Interface - Once all the source code writing and logic building have been completed then run the application. and click on the respective buttons. The user screen or web site should be look like the below screen shot -
-- 출처
http://vsstack.blogspot.kr/2015/02/c-read-and-write-excel-xls-and-xlsx.html