[c#] Excel Read , 속도 개선, OleDbConnection 사용

하기와 같이 사용하여 엑셀을 읽는 방법은 지난번 글에 소개한적이 있다.

하기 링크를 참조하자. 쓰기도 한다면 다음 방법을 사용 

Excel.Application application = null;
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
Excel.Range workrange = null;

 

 

https://hengs.tistory.com/39

[c#] Excel 읽기, 쓰기

 

 

이 방법을 쓰면 속도가 너무 느려, 속도 개선을 위해 다른 방법을 찾아보았다.

함수도 있고, namespace 추가할 것도 있고, 차근차근 따라해보자 

1. namespace 추가 

using System.Collections;
using System.IO;
using System.Data.OleDb;

뭐에 필요한 건지는 각각 함수에서 주석으로 달아놓았으니 확인하면 된다.

 

2. main 함수 작성 (본인은 form이 load 되자자마 엑셀을 읽어서, richtextbox에 값을 입력해줄 것이다.)

 

     private void Form1_Load(object sender, EventArgs e)
        {
            string ExcelFilePath = @"C:\testexcel.xlsx";
            //arraylist 사용 시 using System.Collections; 추가 
            ArrayList ReadData = new ArrayList();
            ReadData.Clear();

            string connstr = "";
            try
            {

                bool Header = false;
                string[] Option = { "NO", "YES" };
                string HDR = "";
                if (Header) HDR = Option[1];
                else HDR = Option[0];

                int ExcelType = GetExcelFileType(ExcelFilePath);
                if (ExcelType == 0) connstr = string.Format(ConnectStrFrm_Excel97_2003, ExcelFilePath, HDR, "1");
                else if (ExcelType == 1) connstr = string.Format(ConnectStrFrm_Excel, ExcelFilePath, HDR, "1");


                DataSet ds = getExcelData(connstr);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        ArrayList dsData = new ArrayList();
                        dsData.Clear();
                        for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                        {
                            dsData.Add(ds.Tables[0].Rows[i][j].ToString().Trim());
                        }

                        ReadData.Add(dsData);
                    }
                }

                StringBuilder str = new StringBuilder();
                for (int i = 0; i < ReadData.Count; i++)
                {
                    str = new StringBuilder();
                    ArrayList rowData = (ArrayList)ReadData[i];
                    for (int j = 0; j < rowData.Count; j++)
                    {
                        str.AppendFormat("{0},", rowData[j].ToString());
                    }
                    richTextBox1.AppendText(str.ToString());
                }

           

            }
            catch (Exception er)
            {
                Console.WriteLine(er.ToString());
            }

        }

 

다음과 같이 작성하면

 - GetExcelFileType 함수도 없고

 - ConnectStrFrm_Excel97_2003, ConnectStrFrm_Excel 정의도 없고, 

 - getExcelData 함수도 없고 

 - richTextBox1.AppendText(str.ToString()); 이거는 본인이 확인할 방법으로 바꾸면 된다. 

 

using namespace~~ 작업을 (1.) 먼저 안 하면 다른 오류도 뜰 거니, 꼭 먼저 추가하도록 하자 

 

 

3. GetExcelFileType 함수 추가 

     public static int GetExcelFileType(string ExcelFile)
        {
            byte[,] ExcelHeader = {
                { 0xD0, 0xCF, 0x11, 0xE0, 0xA1 }, // XLS  File Header
                { 0x50, 0x4B, 0x03, 0x04, 0x14 }  // XLSX File Header
            };
            // return 
            // error = -2 
            // not excel = -1
            // xls = 0;
            // xlsx = 1;
            int result = -1;

            //File 사용 시 using System.IO; 추가 해야 함 
            FileInfo fileinfo = new FileInfo(ExcelFile);
            FileStream filestream = fileinfo.Open(FileMode.Open);
            try
            {
                byte[] FH = new byte[5];
                filestream.Read(FH, 0, 5);
                for (int i = 0; i < 2; i++)
                {
                    for (int j = 0; j < 5; j++)
                    {
                        if (FH[j] != ExcelHeader[i, j]) break;
                        else if (j == 4) result = i;
                    }
                    if (result >= 0) break;
                }
            }
            catch
            {
                result = -2;
            }
            finally
            {
                filestream.Close();
            }
            return result;
        }

4. ConnectStrFrm_Excel97_2003, ConnectStrFrm_Excel 정의 

class 아래에 정의해줬다. 필요한대로 변경 가능 

 public partial class Form1 : Form
    {

        // 확장명 XLS (Excel 97~2003 용)
        private const string ConnectStrFrm_Excel97_2003 =
            "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=\"{0}\";" +
            "Mode=ReadWrite|Share Deny None;" +
            "Extended Properties='Excel 8.0; HDR={1}; IMEX={2}';" +
            "Persist Security Info=False";
        // 확장명 XLSX (Excel 2007 이상용)
        private const string ConnectStrFrm_Excel =
            "Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source=\"{0}\";" +
            "Mode=ReadWrite|Share Deny None;" +
            "Extended Properties='Excel 12.0; HDR={1}; IMEX={2}';" +
            "Persist Security Info=False";

        public Form1()
        {
            InitializeComponent();
        }

5. getExcelData  함수 추가 

  private static DataSet getExcelData(string conn)
        {
            DataSet dataset = null;


            // using System.Data.OleDb; 추가 
            OleDbConnection OleDBConn = null;
            OleDbDataAdapter OleDBAdap = null;
            DataTable dt;
            try
            {
                OleDBConn = new OleDbConnection(conn);
                OleDBConn.Open();
                dt = OleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                dataset = new DataSet();
                foreach (DataRow DR in dt.Rows)
                {
                    OleDBAdap = new OleDbDataAdapter(DR["TABLE_NAME"].ToString(), OleDBConn);
                    OleDBAdap.SelectCommand.CommandType = CommandType.TableDirect;
                    OleDBAdap.AcceptChangesDuringFill = false;
                    string TableName = DR["TABLE_NAME"].ToString().Replace("$", String.Empty).Replace("'", String.Empty);
                    if (DR["TABLE_NAME"].ToString().Contains("$")) OleDBAdap.Fill(dataset, TableName);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (OleDBConn != null) OleDBConn.Close();
            }
            return dataset;
        }

 

결과 확인