ASP.NET을 이용한 엑셀(Excel) 파일 읽어오기

자료/ASP.NET

ASP.NET을 이용한 엑셀(Excel) 파일 읽어오기

네오블루 2010. 1. 26. 15:53

프로젝트를 수행하면서, Excel 파일에 있는 정보를 가져와야 할 필요가 생겼습니다. 단순하게 Excel 파일을 가져오는 것에 대한 정보는 많았지만 Microsoft Office 2007로 저장된 확장자 "xlsx"에 관련된 처리를 하는 정보는 없었습니다. 많은 시간의 검색 끝에 결국 확장자가 "xlsx"인 엑셀 파일의 정보를 가져오는 방법을 알게 되었습니다.

우선, <그림 1>과 같은 엑셀 파일을 만들고 확장자 "xls"와 확장자 "xlsx"로 각각 저장합니다.

<그림 1> Excel 파일의 내용

다음으로는 Excel 파일을 읽어올 웹 페이지의 코드를 작성합니다. 먼저 웹 폼 페이지의 소스 코드입니다.

<div>

</div>

<div>

XLS 파일 : <asp:FileUpload ID="FileUpload1" runat="server" CssClass="txtCommon" Width="300" /> 

<asp:Button ID="ButtonUpload1" runat="server" Text="업로드" Width="100" CssClass="txtCommon" OnClick="ButtonUpload1_Click" /><br />

XLSX 파일 : <asp:FileUpload ID="FileUpload2" runat="server" CssClass="txtCommon" Width="300" /> 

<asp:Button ID="ButtonUpload2" runat="server" Text="업로드" Width="100" CssClass="txtCommon" OnClick="ButtonUpload2_Click" /><br /><br />

</div>

<div>

<asp:GridView ID="GridView1" runat="server" BackColor="White"

BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="3"

ForeColor="Black" GridLines="Vertical" Width="719px">

</asp:GridView>

</div>

</form>

업로드 역할과 업로드한 엑셀 파일의 정보를 읽어오는 코드가 담겨있는 비하인드 파일의 코드는 다음과 같습니다.

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Data.OleDb;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

public partial class UseExcel : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void ButtonUpload1_Click(object sender, EventArgs e)

{

string strFilePath = string.Empty;

string strFileType = string.Empty;

string strFileName = string.Empty;

string strNewPath = string.Empty;

if (FileUpload1.PostedFile != null)

{

strFilePath = FileUpload1.PostedFile.FileName;

strFileType = FileUpload1.PostedFile.ContentType.ToString();

System.IO.FileInfo fi = new System.IO.FileInfo(strFilePath);

strFileName = fi.Name;

strNewPath = @"D:\TempUploadFolder\" + strFileName;

FileUpload1.SaveAs(strNewPath);

lblUploadText.Text = strFileName + " 파일이 업로드되었습니다.";

ReadContentInExcelFile(strNewPath, false);

}

protected void ButtonUpload2_Click(object sender, EventArgs e)

{

string strFilePath = string.Empty;

string strFileType = string.Empty;

string strFileName = string.Empty;

string strNewPath = string.Empty;

if (FileUpload2.PostedFile != null)

{

strFilePath = FileUpload2.PostedFile.FileName;

strFileType = FileUpload2.PostedFile.ContentType.ToString();

System.IO.FileInfo fi = new System.IO.FileInfo(strFilePath);

strFileName = fi.Name;

strNewPath = @"D:\TempUploadFolder\" + strFileName;

FileUpload2.SaveAs(strNewPath);

lblUploadText.Text = strFileName + " 파일이 업로드되었습니다.";

ReadContentInExcelFile(strNewPath, true);

}

private void ReadContentInExcelFile(string strFilePath, bool bExcelVersion2007)

{

string strProvider = string.Empty;

if (bExcelVersion2007)

{

strProvider = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + strFilePath + "; Extended Properties=Excel 12.0";

}

else

{

strProvider = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFilePath + "; Extended Properties=Excel 8.0";

}

string strQuery = "SELECT * FROM [Sheet1$]";

OleDbConnection oleDBCon = null;

OleDbCommand oleDBCom = null;

OleDbDataReader oleDBReader = null;

try

{

oleDBCon = new OleDbConnection(strProvider);

oleDBCom = new OleDbCommand(strQuery, oleDBCon);

oleDBCon.Open();

oleDBReader = oleDBCom.ExecuteReader(CommandBehavior.CloseConnection);

DataTable dtData = new DataTable();

dtData.Load(oleDBReader);

GridView1.DataSource = dtData.DefaultView;

GridView1.DataBind();

}

catch (Exception ex)

{

Response.Write(ex.Source + "::" + ex.InnerException + "::" + ex.StackTrace);

}

finally

{

oleDBReader.Close();

oleDBReader.Dispose();

oleDBCon.Close();

}

소 스 코드에 대해 간단하게 설명을 드리면, 2개의 FileUpload 컨트롤이 위치하고 있습니다. 위쪽의 FileUpload 컨트롤은 확장자가 "xls"인 Excel 97 또는 Excel 2000, Excel 2003 버전의 엑셀 파일의 업로드를 담당하게 됩니다. 아래쪽의 FileUpload 컨트롤은 확장자가 "xlsx"인 Excel 2007 버전의 엑셀 파일의 업로드를 담당하게 됩니다. 업로드 버튼을 클릭하면, 특정 폴더로 엑셀 파일을 저장한 후에 OleDB 클래스를 이용해서 엑셀 파일에 연결하게 됩니다.
이때, 확장자 "xls" 버전의 경우의 연결 정보와 확장자 "xlsx" 버전의 경우의 연결 정보가 다른 것에 주의하시기 바랍니다. "xlsx" 버전의 경우에는 프로바이더로 "Microsoft.ACE.OLEDB.12.0"을 사용하고 있으며 이 프로바이더를 사용하기 위해서는 http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=ko 에서 "2007 Office system 드라이버: 데이터 연결 구성 요소"를 다운로드받아 설치하셔야 합니다.

확장자 "xls"와 확장자 "xlsx" 엑셀 파일을 업로드하여 GridView에 출력한 화면은 각각 <그림 2>와 <그림 3>과 같습니다.

<그림 2> 확장자 "xls" 파일의 업로드 후 화면

<그림 3> 확장자 "xlsx" 파일의 업로드 후 화면

ps. 첫번째 시트이름 상관없이 가져오기

# Commented By :: 정진환 At 2008-05-21 오후 4:24:08 ///

/// a method that retrieves the worksheet names from the specified excel worksheet
///

///

excel document path
/// an array of worksheet names
public static string[] WorkSheetNames(string excelFilePath, string oledbProviderString)
{
//an array that would hold the extracted worksheet names
string[] workSheetNames;
//create a connection to the excel worksheet
using (OleDbConnection oledbConnection = new OleDbConnection(string.Format(oledbProviderString, excelFilePath)))
{
//open the connection
oledbConnection.Open();
// Get all of the Table names from the Excel workbook
DataTable dataTable = oledbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//specify the dimension of the array
workSheetNames = new string[dataTable.Rows.Count];
//Add the Table name to the string array.
for (int i = 0; i < dataTable.Rows.Count; i++)
{
//append to the array the worksheet names
workSheetNames[i] = (string)dataTable.Rows[i]["TABLE_NAME"];
}
//close the connection
oledbConnection.Close();
}
//return the array
return workSheetNames;
}
---------------------------------------------------------------------------------------
위함수 추가 하시고

string[] WorkSheet = WorkSheetNames(strFilePath, strProvider);
string strQuery = "SELECT * FROM [" + WorkSheet[0] + "]";

가져온 워크시트 배열을 테이블 이름으로 사용하시면 됩니다.

출처 : http://www.neostyx.net/GrayRec/NXBlogPostView.aspx?postid=080226155726364&categoryname=ASP.NET

다른 참조 : http://www.bangsil.pe.kr/Board/Read.aspx?BoardId=da05ba1b-3e9b-4d16-b8f8-ff2e4f7514c5&PostId=3e0e62d8-a02e-4e3e-bbc0-6c3dc6ba92d4