[C#, .net] 엑셀 다운로드 시 바로 엑셀 창 표시 코드

엑셀 다운로드 시 스프레드 데이터를 바로 엑셀 프로그램 실행 후 출력하는 방법

 

FpSpread를 엑셀 프로그램 실행을 통해서 확인하고 싶은 경우에 사용할 코드

 

핵심은

데이터를 하나씩 넣어주는 코드는 >>> 느림

데이터를 배열에 담은 후 >>> Range를 써서 한 번에 그려주는 식이 훨씬 속도면에서 빠름

 

 

/// <summary>
        /// 엑셀 표시
        /// </summary>
        /// <param name="spread">스프레드시트</param>
        public static void ShowExcel(FpSpread spread)
        {
            Excel.Application excelApp = null;

            try
            {
                excelApp = new Excel.Application();
                excelApp.Visible = false;

                Excel.Workbook workbook = excelApp.Workbooks.Add();
                Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[1];

                // 컬럼 셀의 숫자 형식을 Text로 설정
                worksheet.Columns.NumberFormat = "@";

                // 헤더 입력
                var columnHeader = spread.Sheets[0].ColumnHeader;
                int headerRowCount = columnHeader.Rows.Count;

                for (int row = 0; row < headerRowCount; row++)
                {
                    for (int col = 0; col < columnHeader.Columns.Count; col++)
                    {
                        // 헤더 셀 값 설정
                        var cell = worksheet.Cells[row + 1, col + 1];
                        cell.Value = columnHeader.Cells[row, col].Text;
                        // 헤더 셀 가운데 정렬
                        cell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    }
                }

                int rowCount = spread.Sheets[0].RowCount;
                int columnCount = spread.Sheets[0].ColumnCount;
                object[,] cellValues = new object[rowCount, columnCount];

                for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
                {
                    for (int colIndex = 0; colIndex < columnCount; colIndex++)
                    {
                        // 데이터를 배열에 저장
                        cellValues[rowIndex, colIndex] = spread.Sheets[0].Cells[rowIndex, colIndex].Text;
                    }
                }

                // Excel에 배열 값 할당
                Excel.Range startCell = worksheet.Cells[headerRowCount + 1, 1];
                Excel.Range endCell = worksheet.Cells[headerRowCount + rowCount, columnCount];
                Excel.Range writeRange = worksheet.Range[startCell, endCell];
                writeRange.Value = cellValues;

                // 컬럼 자동 맞춤
                worksheet.Columns.AutoFit();

                // 변경 사항 저장 팝업 미표시
                workbook.Saved = true;

                // 엑셀 표시
                excelApp.Visible = true;
            }
            catch (Exception ex)
            {
                MCComm.Log(MCComhttp://m.ELogType.ERROR, ex.Message, ex);
            }
        }

  /// <summary>
        /// 엑셀 표시
        /// </summary>
        /// <param name="spread">스프레드시트</param>
        public static void ShowExcel(FpSpread spread)
        {
            Excel.Application excelApp = null;

            try
            {
                excelApp = new Excel.Application();
                excelApp.Visible = false;

                Excel.Workbook workbook = excelApp.Workbooks.Add();
                Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[1];

                // 컬럼 셀의 숫자 형식을 Text로 설정
                worksheet.Columns.NumberFormat = "@";

                // 헤더 입력
                var columnHeader = spread.Sheets[0].ColumnHeader;
                int headerRowCount = columnHeader.Rows.Count;

                for (int row = 0; row < headerRowCount; row++)
                {
                    for (int col = 0; col < columnHeader.Columns.Count; col++)
                    {
                        // 헤더 셀 값 설정
                        var cell = worksheet.Cells[row + 1, col + 1];
                        cell.Value = columnHeader.Cells[row, col].Text;
                        // 헤더 셀 가운데 정렬
                        cell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    }
                }

                int rowCount = spread.Sheets[0].RowCount;
                int columnCount = spread.Sheets[0].ColumnCount;
                object[,] cellValues = new object[rowCount, columnCount];

                for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
                {
                    for (int colIndex = 0; colIndex < columnCount; colIndex++)
                    {
                        // 데이터를 배열에 저장
                        cellValues[rowIndex, colIndex] = spread.Sheets[0].Cells[rowIndex, colIndex].Text;
                    }
                }

                // Excel에 배열 값 할당
                Excel.Range startCell = worksheet.Cells[headerRowCount + 1, 1];
                Excel.Range endCell = worksheet.Cells[headerRowCount + rowCount, columnCount];
                Excel.Range writeRange = worksheet.Range[startCell, endCell];
                writeRange.Value = cellValues;

                // 컬럼 자동 맞춤
                worksheet.Columns.AutoFit();

                // 변경 사항 저장 팝업 미표시
                workbook.Saved = true;

                // 엑셀 표시
                excelApp.Visible = true;
            }
            catch (Exception ex)
            {
                MCComm.Log(MCComm.ELogType.ERROR, ex.Message, ex);
            }
        }