excelImport/Source/ExcelImprot/LExcelHandler.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using Microsoft.Win32.SafeHandles;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Data;
using System.Drawing;

namespace ExcelImprot
{
    public class LExcelHandler
    {
        private bool _bDisposed = false;
        private SafeHandle _objSafeHandler = new SafeFileHandle(IntPtr.Zero, true);

        private bool _bIsActive = false;

        private Excel.Application _objApp = null;
        private Excel.Workbooks _objWorkbooks = null;
        private Excel.Workbook _objWorkbook = null;
        private Excel.Sheets _objSheets = null;
        private Excel.Worksheet _objWorksheet = null;
        private Excel.Range _objRange = null;

        /// <summary>
        /// 객체 생성자입니다.
        /// </summary>
        /// <param name="bPrepareApplication">사전 엑셀 어플리케이션 생성여부입니다.</param>
        public LExcelHandler(bool bPrepareApplication)
        {
            if (bPrepareApplication)
            {
                try
                {
                    _objApp = new Excel.Application();
                    _objWorkbooks = _objApp.Workbooks;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 지정된 경로의 엑셀파일을 읽어 DataTable형으로 반환합니다.
        /// </summary>
        /// <param name="strFilePath">엑셀파일 경로</param>
        /// <returns>반환된 DataTable형 객체</returns>

        /// <summary>
        /// 지정된 경로의 엑셀파일을 읽어 DataTable형으로 반환합니다.
        /// </summary>
        /// <param name="strFilePath">엑셀파일 경로</param>
        /// <param name="iWorkSheetNumber">읽을 시트 번호입니다.</param>
        /// <param name="bIngnoreReadOnlyRecommended">읽기전용 경고를 무시할지 여부입니다.</param>
        /// <returns>반환된 DataTable형 객체</returns>
        public DataTable ReadAsDataTableFromOpenedExcel(string strFilePath, int iWorkSheetNumber = 1, bool bIngnoreReadOnlyRecommended = false)
        {
            if (_objApp == null)
            {
                try
                {
                    _objApp = new Excel.Application();
                    _objWorkbooks = _objApp.Workbooks;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            DataTable objDataTable = new DataTable();

            try
            {
                _objWorkbook = _objApp.Workbooks.Open(strFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, bIngnoreReadOnlyRecommended);
                _objSheets = _objWorkbook.Worksheets;
                _objWorksheet = _objSheets.get_Item(iWorkSheetNumber) as Excel.Worksheet;
                _objRange = _objWorksheet.UsedRange;
                //Excel.Range objRange = objWorksheet.Range[objWorksheet.Cells[2, 1], objWorksheet.Cells[5, 1]];

                object[,] objData = _objRange.Value;

                for (int i = 0; i < objData.GetLength(1); i++)
                {
                    objDataTable.Columns.Add(DecToAlphabet(i));
                }

                for (int r = 1; r <= objData.GetLength(0); r++)
                {
                    DataRow objRow = objDataTable.NewRow();

                    for (int c = 1; c <= objData.GetLength(1); c++)
                    {
                        object objValue = objData[r, c];
                        string strValue = "";

                        if (objValue != null)
                            strValue = objData[r, c].ToString();

                        objRow[c - 1] = strValue;
                    }
                    objDataTable.Rows.Add(objRow);
                }

                _objWorkbook.Saved = true;
                _objWorkbook.Close(true);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return objDataTable;
        }

        /// <summary>
        /// 지정된 경로의 엑셀파일을 읽어 Object 2차원 배열로 반환합니다.
        /// </summary>
        /// <param name="strFilePath">파일경로</param>
        /// <param name="iWorkSheetNumber">워크시트번호</param>
        /// <param name="bIngnoreReadOnlyRecommended">오류 무시 여부</param>
        /// <returns>엑셀로부터 읽어진 2차원 배열, 오류시 NULL을 반환합니다.</returns>
        public object[,] ReadAsArrayFromOpenedExcel(string strFilePath, int iWorkSheetNumber = 1, bool bIngnoreReadOnlyRecommended = false)
        {
            if (_objApp == null)
            {
                try
                {
                    _objApp = new Excel.Application();
                    _objWorkbooks = _objApp.Workbooks;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            object[,] objData = null;

            try
            {
                _objWorkbook = _objWorkbooks.Open(strFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, bIngnoreReadOnlyRecommended);

                _objSheets = _objWorkbook.Worksheets;
                _objWorksheet = _objSheets.get_Item(iWorkSheetNumber) as Excel.Worksheet;

                _objRange = _objWorksheet.UsedRange;

                objData = _objRange.Value;

                _objWorkbook.Saved = true;

                _objWorkbook.Close(true);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return objData;
        }

        public void CloseOpenedExcel()
        {
            try
            {
                if (_objApp != null)
                    _objApp.Quit();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                LExcelHandler.ReleaseExcelObject(_objRange);
                LExcelHandler.ReleaseExcelObject(_objWorksheet);
                LExcelHandler.ReleaseExcelObject(_objSheets);
                LExcelHandler.ReleaseExcelObject(_objWorkbook);
                LExcelHandler.ReleaseExcelObject(_objWorkbooks);
                LExcelHandler.ReleaseExcelObject(_objApp);

                _objApp = null;
            }
        }

        /// <summary>
        /// 지정된 경로의 엑셀파일을 읽어 Object 2차원 배열로 반환합니다.
        /// </summary>
        /// <param name="strFilePath">파일 경로</param>
        /// <param name="iWorkSheetNumber">워크 시트 번호</param>
        /// <param name="bIngnoreReadOnlyRecommended">오류 무시 여부</param>
        /// <returns>엑셀로 부터 읽어진 2차원 배열, 오류 시 NULL을 반환합니다.</returns>
        public static object[,] ReadAsArray(string strFilePath, int iWorkSheetNumber = 1, bool bIngnoreReadOnlyRecommended = false)
        {
            Excel.Application objExcelApp = null;
            Excel.Workbooks objWorkbooks = null;
            Excel.Workbook objSelectedWorkbook = null;

            Excel.Sheets objSheets = null;
            Excel.Worksheet objSelectedWorksheet = null;
            Excel.Range objSelectedRange = null;

            object[,] objData = null;

            try
            {
                objExcelApp = new Excel.Application();
                objWorkbooks = objExcelApp.Workbooks;

                objSelectedWorkbook = objWorkbooks.Open(strFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, bIngnoreReadOnlyRecommended);

                objSheets = objSelectedWorkbook.Worksheets;
                objSelectedWorksheet = objSheets.get_Item(iWorkSheetNumber) as Excel.Worksheet;

                objSelectedRange = objSelectedWorksheet.UsedRange;

                objData = objSelectedRange.Value;

                objSelectedWorkbook.Saved = true;

                objSelectedWorkbook.Close(true);
                objExcelApp.Quit();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                LExcelHandler.ReleaseExcelObject(objSelectedRange);
                LExcelHandler.ReleaseExcelObject(objSelectedWorksheet);
                LExcelHandler.ReleaseExcelObject(objSheets);
                LExcelHandler.ReleaseExcelObject(objSelectedWorkbook);
                LExcelHandler.ReleaseExcelObject(objWorkbooks);
                LExcelHandler.ReleaseExcelObject(objExcelApp);

                objExcelApp = null;
            }

            return objData;
        }

        /// <summary>
        /// 지정된 경로의 엑셀파일을 읽어 DataTable형으로 반환합니다.
        /// </summary>
        /// <param name="strFilePath">엑셀파일 경로</param>
        /// <returns>반환된 DataTable형 객체</returns>
        public static DataTable ReadAsDataTable(string strFilePath, int iWorkSheetNumber = 1, bool bIngnoreReadOnlyRecommended = false)
        {
            Excel.Application objApp = null;
            Excel.Workbooks objWorkbooks = null;
            Excel.Workbook objWorkbook = null;
            Excel.Sheets objSheets = null;
            Excel.Worksheet objWorksheet = null;
            Excel.Range objTargetRange = null;

            DataTable objDataTable = new DataTable();

            try
            {
                objApp = new Excel.Application();

                objWorkbook = objApp.Workbooks.Open(strFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, bIngnoreReadOnlyRecommended);
                objWorksheet = objWorkbook.Worksheets.get_Item(iWorkSheetNumber) as Excel.Worksheet;
                objTargetRange = objWorksheet.UsedRange;
                //Excel.Range objRange = objWorksheet.Range[objWorksheet.Cells[2, 1], objWorksheet.Cells[5, 1]];

                object[,] objData = objTargetRange.Value;

                for (int i = 0; i < objData.GetLength(1); i++)
                {
                    objDataTable.Columns.Add(DecToAlphabet(i));
                }

                for (int r = 1; r <= objData.GetLength(0); r++)
                {
                    DataRow objRow = objDataTable.NewRow();

                    for (int c = 1; c <= objData.GetLength(1); c++)
                    {
                        object objValue = objData[r, c];
                        string strValue = "";

                        if (objValue != null)
                            strValue = objData[r, c].ToString();

                        objRow[c - 1] = strValue;
                    }
                    objDataTable.Rows.Add(objRow);
                }

                objWorkbook.Close(true);
                objApp.Quit();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                LExcelHandler.ReleaseExcelObject(objTargetRange);
                LExcelHandler.ReleaseExcelObject(objWorksheet);
                LExcelHandler.ReleaseExcelObject(objSheets);
                LExcelHandler.ReleaseExcelObject(objWorkbook);
                LExcelHandler.ReleaseExcelObject(objWorkbooks);
                LExcelHandler.ReleaseExcelObject(objApp);

                objApp = null;
            }

            return objDataTable;
        }

        /// <summary>
        /// 입력된 DataTable형 데이터를 지정된 경로의 엑셀파일로 저장합니다.
        /// </summary>
        /// <param name="objDataTable">저장할 DataTable 객체</param>
        /// <param name="strFilePath">저장할 파일 경로</param>
        /// <param name="bWriteHeader">헤더를 포함할지 여부입니다.</param>
        /// <returns>LReturnMessage형 결과 값으로 함수 실행이 성공하였으면 객체 내 IsOK 변수에 true 반환하며 실패하였다면 false를 반환합니다. false 시, 오류 메세지가 객체 내 ExceptionMessage에 저장됩니다.</returns>
        public bool Save(DataTable objDataTable, string strFilePath, bool bWriteHeader)
        {
            if (_objApp == null)
            {
                try
                {
                    _objApp = new Excel.Application();
                    _objWorkbooks = _objApp.Workbooks;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            try
            {
                _objWorkbook = _objWorkbooks.Add();
                _objWorksheet = _objWorkbook.Worksheets.get_Item(1) as Excel.Worksheet;

                if (bWriteHeader)
                {
                    // 헤더 저장
                    for (int i = 0; i < objDataTable.Columns.Count; i++)
                    {
                        string strHeader = "";
                        if (objDataTable.Columns[i].ColumnName != null)
                            strHeader = objDataTable.Columns[i].ColumnName;

                        _objWorksheet.Cells[1, i + 1] = strHeader;
                    }

                    for (int r = 0; r < objDataTable.Rows.Count; r++)
                    {
                        for (int c = 0; c < objDataTable.Columns.Count; c++)
                        {
                            _objWorksheet.Cells[r + 2, c + 1] = objDataTable.Rows[r][c];
                        }
                    }
                }
                else
                {
                    for (int r = 0; r < objDataTable.Rows.Count; r++)
                    {
                        for (int c = 0; c < objDataTable.Columns.Count; c++)
                        {
                            _objWorksheet.Cells[r + 1, c + 1] = objDataTable.Rows[r][c];
                        }
                    }
                }

                _objWorkbook.SaveAs(strFilePath, Excel.XlFileFormat.xlOpenXMLWorkbook);
                _objWorkbook.Saved = true;

                _objWorkbook.Close(true);

                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #region Excel Drawing

        /// <summary>
        /// 현재 워크북을 저장하고 닫습니다..
        /// </summary>
        /// <param name="strFilePath">저장 경로</param>
        /// <returns>처리 여부</returns>
        public bool SaveCurrentWorkbook(string strFilePath)
        {
            try
            {
                _objWorkbook.SaveAs(strFilePath, Excel.XlFileFormat.xlOpenXMLWorkbook);
                _objWorkbook.Saved = true;

                _objWorkbook.Close(true);

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 엑셀에 입력한 데이터 테이블 정보를 입력합니다.
        /// </summary>
        /// <param name="objDataTable"></param>
        /// <returns>처리 여부</returns>
        public bool SetData(DataTable objDataTable, bool bWriteHeader = true)
        {
            if (_objApp == null)
            {
                try
                {
                    _objApp = new Excel.Application();
                    _objWorkbooks = _objApp.Workbooks;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }

            try
            {
                _objWorkbook = _objWorkbooks.Add();
                _objWorksheet = _objWorkbook.Worksheets.get_Item(1) as Excel.Worksheet;

                if (bWriteHeader)
                {
                    // 헤더 저장
                    for (int i = 0; i < objDataTable.Columns.Count; i++)
                    {
                        string strHeader = "";
                        if (objDataTable.Columns[i].ColumnName != null)
                            strHeader = objDataTable.Columns[i].ColumnName;

                        _objWorksheet.Cells[1, i + 1] = strHeader;
                    }

                    for (int r = 0; r < objDataTable.Rows.Count; r++)
                    {
                        for (int c = 0; c < objDataTable.Columns.Count; c++)
                        {
                            _objWorksheet.Cells[r + 2, c + 1] = objDataTable.Rows[r][c];
                        }
                    }
                }
                else
                {
                    for (int r = 0; r < objDataTable.Rows.Count; r++)
                    {
                        for (int c = 0; c < objDataTable.Columns.Count; c++)
                        {
                            _objWorksheet.Cells[r + 1, c + 1] = objDataTable.Rows[r][c];
                        }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 사용된 전체 셀을 선택합니다.
        /// </summary>
        /// <returns></returns>
        public Excel.Range SelectUsedRange()
        {
            _objRange = _objWorksheet.UsedRange;
            return _objRange;
        }

        /// <summary>
        /// 단일 셀을 선택합니다.
        /// </summary>
        /// <param name="strCell">셀 주소</param>
        /// <returns>선택한 셀 역역</returns>
        public Excel.Range SelectCell(string strCell)
        {
            _objRange = _objWorksheet.Range[strCell, Type.Missing];
            return _objRange;
        }

        /// <summary>
        /// 단일 셀을 선택합니다.
        /// </summary>
        /// <param name="iRow">셀 행</param>
        /// <param name="iCol">셀 열</param>
        /// <returns>선택한 셀 영역</returns>
        public Excel.Range SelectCell(int iRow, int iCol)
        {
            string strCell = CellAddress(iRow, iCol);
            _objRange = _objWorksheet.Range[strCell, Type.Missing];
            return _objRange;
        }

        /// <summary>
        /// 다중 셀을 선택합니다.
        /// </summary>
        /// <param name="strStartCell">시작 셀 주소</param>
        /// <param name="strEndCell">종료 셀 주소</param>
        /// <returns>선택한 셀 영역</returns>
        public Excel.Range SelectCell(string strStartCell, string strEndCell)
        {
            _objRange = _objWorksheet.Range[strStartCell, strEndCell];
            return _objRange;
        }

        /// <summary>
        /// 다중 셀을 선택합니다.
        /// </summary>
        /// <param name="iStartRow">시작 셀 행</param>
        /// <param name="iStartCol">시작 셀 열</param>
        /// <param name="iEndRow">끝 셀 행</param>
        /// <param name="iEndCol">끝 셀 열</param>
        /// <returns></returns>
        public Excel.Range SelectCell(int iStartRow, int iStartCol, int iEndRow, int iEndCol)
        {
            string strStartCell = CellAddress(iStartRow, iStartCol);
            string strEndCell = CellAddress(iEndRow, iEndCol);

            _objRange = _objWorksheet.Range[strStartCell, strEndCell];
            return _objRange;
        }

        /// <summary>
        /// 선택된 셀 영역의 색상을 지정합니다.
        /// </summary>
        /// <param name="objColor">지정할 색상</param>
        /// <returns>처리 여부</returns>
        public bool SetColor(Color objColor)
        {
            if (_objRange == null)
                return false;

            _objRange.Interior.Color = ColorTranslator.ToOle(objColor);
            return true;
        }

        /// <summary>
        /// 선택된 셀 영역의 컬럼을 자동맞춤 합니다.
        /// </summary>
        /// <returns>처리 여부</returns>
        public bool SetColumnWidth()
        {
            if (_objRange == null)
                return false;

            _objRange.Columns.AutoFit();
            return true;
        }

        /// <summary>
        /// 선택된 셀 영역의 컬럼의 폭을 설정합니다.
        /// </summary>
        /// <param name="dWidth">폭</param>
        /// <returns>처리 여부</returns>
        public bool SetColumnWidth(double dWidth)
        {
            if (_objRange == null)
                return false;

            _objRange.ColumnWidth = dWidth;
            return true;
        }

        /// <summary>
        /// 선택된 셀 영역을 병합합니다.
        /// </summary>
        /// <returns>처리 여부</returns>
        public bool SetMerge()
        {
            if (_objRange == null)
                return false;

            _objRange.Merge(true);
            return true;
        }

        /// <summary>
        /// 선택된 셀 영역의 폰트 사이즈를 설정합니다.
        /// </summary>
        /// <param name="iSize">폰트 사이즈</param>
        /// <returns>처리 여부</returns>
        public bool SetSize(int iSize)
        {
            if (_objRange == null)
                return false;

            _objRange.Font.Size = iSize;
            return true;
        }

        /// <summary>
        /// 선택된 셀 영역을 볼드 처리합니다.
        /// </summary>
        /// <returns>처리 여부</returns>
        public bool SetBold()
        {
            if (_objRange == null)
                return false;

            _objRange.Font.Bold = true;
            return true;
        }

        /// <summary>
        /// 선택된 셀 영역을 가운데 정렬 처리 합니다.
        /// </summary>
        /// <returns>처리 여부</returns>
        public bool SetCenterAlign()
        {
            if (_objRange == null)
                return false;

            _objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            return true;
        }

        /// <summary>
        /// 선택한 셀 영역의 테두리를 설정합니다.
        /// </summary>
        /// <param name="bOutline">외곽선</param>
        /// <param name="bHorizontal">내부 세로선</param>
        /// <param name="bVertical">내부 가로선</param>
        /// <returns>처리 여부</returns>
        public bool SetBorder(bool bOutline = true, bool bHorizontal = true, bool bVertical = true)
        {
            if (_objRange == null)
                return false;

            if (bOutline)
                _objRange.BorderAround2(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic);

            if (bHorizontal)
                _objRange.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;

            if (bVertical)
                _objRange.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;

            return true;
        }

        /// <summary>
        /// 엑셀상 셀 주소를 반환합니다.
        /// </summary>
        /// <param name="iRow">열 번호</param>
        /// <param name="iCol">행 번호</param>
        /// <returns>셀 주소</returns>
        public static string CellAddress(int iRow, int iCol)
        {
            string strReturn = DecToAlphabet(iCol - 1);
            strReturn += iRow;

            return strReturn;
        }

        #endregion

        private static bool ReleaseExcelObject(object objObject)
        {
            try
            {
                if (objObject != null)
                {
                    Marshal.ReleaseComObject(objObject);
                    objObject = null;

                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                objObject = null;
                throw ex;
            }
            finally
            {
                GC.Collect();
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool bDisposing)
        {
            if (_bDisposed)
                return;

            if (bDisposing)
                _objSafeHandler.Dispose();

            _bDisposed = true;
        }

        /// <summary>
        /// 정수형 숫자를 문자열 형태로 변환하여 반환합니다.
        /// 반환되는 문자열 형태는 엑셀의 컬럼명 형태입니다.
        /// </summary>
        /// <param name="iDec">변환할 int형</param>
        /// <returns>변환된 문자열</returns>
        public static string DecToAlphabet(int iDec)
        {
            int iRest;
            string alphabet;

            byte[] asciiA = Encoding.ASCII.GetBytes("A");
            iRest = iDec % 26;
            asciiA[0] += (byte)iRest;

            alphabet = Encoding.ASCII.GetString(asciiA);

            iDec = iDec / 26 - 1;
            if (iDec > -1)
            {
                alphabet = alphabet.Insert(0, DecToAlphabet(iDec));
            }
            return alphabet;
        }

    }
}