HYTOS

using SqlConnectionDialog;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data.SqlClient;

using Microsoft.Office.Interop.Excel;

using System.Windows.Forms;

using System.Drawing;

using System.IO;

using System.IO.Compression;

namespace ID2ManagerImport

{

    public static class DataManager

    {

        static string tableName = "ImportExcel";

        public static string ConnectionDialog()

        {

            var factory = new ConnectionStringFactory();

            var str = factory.OpenDialog();

            return str;

        }

        public static bool CreateTabile(string connectionString)

        {

            bool result = false;

            try

            {

                using (SqlConnection connection = new SqlConnection(connectionString))

                {

                    connection.Open();

                    string checkTableQuery = $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}'";

                    // 쿼리 실행하여 테이블 존재 여부 확인

                    SqlCommand checkTableCommand = new SqlCommand(checkTableQuery, connection);

                    int tableCount = (int)checkTableCommand.ExecuteScalar();

                    if (tableCount == 0)

                    {

                        // CREATE TABLE 문 작성

                        string createTableQuery = $"CREATE TABLE {tableName} ( "

                                    + " FILE_NAME VARCHAR(MAX),"

                                    + " WORKSHEET_NAME VARCHAR(MAX),"

                                    + " COUMMN_INDEX INT,"

                                    + " ROW_INDEX INT,"

                                    + " VALUE VARCHAR(MAX),"

                                    + " TopLeftCell VARCHAR(MAX),"

                                    + " OBJECT_TYPE INT"

                                  + " )";

                        // CREATE TABLE 쿼리 실행

                        SqlCommand createTableCommand = new SqlCommand(createTableQuery, connection);

                        createTableCommand.ExecuteNonQuery();

                        connection.Close();

                    }

                    result = true;

                }

            }

            catch (Exception)

            {

                throw;

            }

            return result;

        }

        public static void DeleteData(SqlConnection connection,string FileName, string worksheetName)

        {

                string delQuery = $"DELETE FROM {tableName} WHERE FILE_NAME = '{FileName}' AND WORKSHEET_NAME = '{worksheetName}'";

                // SQL 쿼리 실행

                SqlCommand command = new SqlCommand(delQuery, connection);

                command.ExecuteNonQuery();

        }

        public static bool Import(string FileName, Microsoft.Office.Interop.Excel.Worksheet worksheet)

        {

            bool result = true;

            try

            {

                var connectionStr = ConnectionDialog();

                if (connectionStr != null)

                {

                    //Globals.ThisAddIn.Application.Cursor = Microsoft.Office.Interop.Excel.XlMousePointer.xlWait;

                    if (CreateTabile(connectionStr))

                    {

                        using (SqlConnection connection = new SqlConnection(connectionStr))

                        {

                            connection.Open();

                            DeleteData(connection, FileName, worksheet.Name);

                            Range range = worksheet.UsedRange;

                            // 행 반복

                            for (int row = 1; row <= range.Rows.Count; row++)

                            {

                                // 열 반복

                                for (int column = 1; column <= range.Columns.Count; column++)

                                {

                                    string cellAddress = range.Cells[row, column].Address;

                                    var cellValue = range.Cells[row, column].Value;

                                    //Range cell = range.Cells[row, column];

                                    //XlCellType cellType = cell.Cells[row, column].CellType;

                                    //// 현재 셀 값 가져오기

                                    //string cellValue = cell.Cells[row, column].Value2.ToString();

                                    if (cellValue != null)

                                    {

                                        // SQL INSERT 쿼리 작성

                                        string query = $"INSERT INTO {tableName} (FILE_NAME,WORKSHEET_NAME,COUMMN_INDEX,ROW_INDEX,VALUE,TopLeftCell,OBJECT_TYPE) "

                                                     + $" VALUES ('{FileName}','{worksheet.Name}',{column},{row},'{cellValue}','{cellAddress}',0)";

                                        // SQL 쿼리 실행

                                        SqlCommand command = new SqlCommand(query, connection);

                                        command.ExecuteNonQuery();

                                    }

                                }

                            }

                            ImportImage(connection, FileName, worksheet);

                            connection.Close();

                            result = true;

                        }

                    }

                }

            }

            catch (Exception)

            {

                throw;

            }

            return result;

        }

        private static int GetColumnIndex(string CellAddress)

        {

            int columnIndex = 0;

            if (CellAddress.TrimStart('$').Split('$').Length > 0)

            {

                string columnAddress = CellAddress.TrimStart('$').Split('$')[0];

                foreach (char c in columnAddress)

                {

                    columnIndex = columnIndex * 26 + (int)c - 64;

                }

            }

            return columnIndex;

        }

        private static int GetRowIndex(string CellAddress)

        {

            int RowIndex = 0;

            if (CellAddress.TrimStart('$').Split('$').Length > 1)

            {

                string columnAddress = CellAddress.TrimStart('$').Split('$')[1];

                RowIndex = Convert.ToInt32(columnAddress);

            }

            return RowIndex;

        }

        public static bool ImportImage(SqlConnection connection, string FILE_NAME, Worksheet worksheet)

        {

            bool result = true;

            try

            {

                const Microsoft.Office.Core.MsoShapeType pictureShapeType = Microsoft.Office.Core.MsoShapeType.msoPicture;

                string query = $"INSERT INTO {tableName} (FILE_NAME,WORKSHEET_NAME,COUMMN_INDEX,ROW_INDEX,TopLeftCell,VALUE,OBJECT_TYPE) "

                                        + $" VALUES (@FileName,@worksheetName,@ColumnIndex,@RowIndex,@TopLeftCell,@cellValue,@ObjectType)";

                List<Microsoft.Office.Interop.Excel.Shape> shapes = new List<Shape>();

                foreach (Shape shape in worksheet.Shapes)

                {

                    shapes.Add(shape);

                }

                foreach (Shape shape in shapes)

                {

                    if (shape.Type == pictureShapeType)

                    {

                        string location = shape.TopLeftCell.Address; // 이미지의 위치 정보

                        string base64String = null;

                        try

                        {

                            float originalHeight = shape.Height;

                            float originalWidth = shape.Width;

                            shape.ScaleWidth(1.0f, Microsoft.Office.Core.MsoTriState.msoTrue, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);

                            // 이미지를 가져올 Shape이 그림인 경우 처리

                            shape.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap);

                                if (Clipboard.ContainsImage())

                                {

                                    // 클립보드에서 이미지 가져오기

                                    using (Image clipboardImage = Clipboard.GetImage())

                                    {

                                        // 이미지 크기를 원래 크기로 되돌리기

                                        // 이미지를 byte 배열로 변환

                                        byte[] imageBytes;

                                        using (MemoryStream ms = new MemoryStream())

                                        {

                                            clipboardImage.Save(ms, System.Drawing.Imaging.ImageFormat.Png);

                                            imageBytes = ms.ToArray();

                                            // 이미지를 Base64 문자열로 변환

                                            base64String = CompressString(Convert.ToBase64String(imageBytes));

                                            imageBytes = null;

                                        }

                                    }

                                    shape.ScaleWidth(originalWidth / shape.Width, Microsoft.Office.Core.MsoTriState.msoTrue, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);

                                    Clipboard.Clear();

                                }

                            }

                            catch (Exception ex)

                            {

                                System.Diagnostics.Debug.WriteLine(ex);

                            }

                        // 이미지를 MSSQL에 저장

                        SqlCommand insertCommand = new SqlCommand(query, connection);

                        insertCommand.Parameters.AddWithValue("@FileName", FILE_NAME);

                        insertCommand.Parameters.AddWithValue("@worksheetName", worksheet.Name);

                        insertCommand.Parameters.AddWithValue("@ColumnIndex", GetColumnIndex(location));

                        insertCommand.Parameters.AddWithValue("@RowIndex", GetRowIndex(location));

                        insertCommand.Parameters.AddWithValue("@TopLeftCell", location);

                        insertCommand.Parameters.AddWithValue("@cellValue", base64String);

                        insertCommand.Parameters.AddWithValue("@ObjectType", 1);

                        insertCommand.ExecuteNonQuery();

                    }

                }

                shapes.Clear();

                result = true;

            }

            catch (Exception)

            {

                throw;

            }

            return result;

        }

        public static string CompressString(string str)

        {

            string result = null;

            using (MemoryStream outStream = new MemoryStream())

            {

                using (GZipStream gzipStream = new GZipStream(outStream, CompressionMode.Compress))

                using (MemoryStream srcStream = new MemoryStream(Encoding.UTF8.GetBytes(str)))

                    srcStream.CopyTo(gzipStream);

                result = Convert.ToBase64String(outStream.ToArray());

            }

            return result;

        }

        public static bool Export(Microsoft.Office.Interop.Excel.Worksheet worksheet)

        {

            bool result = true;

            try

            {

                var connectionStr = ConnectionDialog();

                if (connectionStr != null)

                {

                    if (CreateTabile(connectionStr))

                    {

                        using (SqlConnection connection = new SqlConnection(connectionStr))

                        {

                            connection.Open();

                            Microsoft.Office.Interop.Excel.Range range = worksheet.UsedRange;

                            // SQL INSERT 쿼리 작성

                            string query = $"select TopLeftCell,VALUE from {tableName}";

                            // SQL 쿼리 실행

                            SqlCommand command = new SqlCommand(query, connection);

                            var read = command.ExecuteReader();

                            while (read.Read())

                            {

                                MessageBox.Show($"{read.GetString(0)} ; {read.GetString(1)}");

                                break;

                            }

                            connection.Close();

                            result = true;

                        }

                    }

                }

            }

            catch (Exception)

            {

                throw;

            }

            return result;

        }

    }

}