Excel Export, Serch, Insert, Upload - 초간단 버전(MS-SQL)

J_Bin 2022. 1. 4. 15:29

※ 초기 레이아웃

1. Search - 조회 프로시저를 이용하여 grid view에 나타내기

private void Search()
        {
            string strconn = @"Data Source=127.0.0.1;Initial Catalog=jkb;Persist Security Info=True;User ID=jkb;Password= DB 패스워드";
            //			            localhost          DB Instance                                 ID         Password


            using (SqlConnection conn = new SqlConnection(strconn))
            {
                conn.Open();

                SqlDataAdapter da = new SqlDataAdapter("TEST_SEL0123", conn);	// 조회 프로시저
                DataTable dt = new DataTable();
                da.Fill(dt);

                dataGridView1.DataSource = dt;


            }

        }

1-1. 결과

2. Export - grid view의 내용을 Excel File로 Export

private void Excel_Export(bool captions)
        {


            SaveFileDialog saveFileDialog = new SaveFileDialog();

            saveFileDialog.FileName = "TempName";
            saveFileDialog.DefaultExt = "xls";
            saveFileDialog.Filter = "Excel files (*.xls)|*.xls";
            saveFileDialog.InitialDirectory = "c:\\";



            DialogResult result = saveFileDialog.ShowDialog();

            if (result == DialogResult.OK)
            {
                int num = 0;
                object missingType = Type.Missing;

                Excel.Application objApp;
                Excel._Workbook objBook;
                Excel.Workbooks objBooks;
                Excel.Sheets objSheets;
                Excel._Worksheet objSheet;
                Excel.Range range;

                string[] headers = new string[dataGridView1.ColumnCount];
                string[] columns = new string[dataGridView1.ColumnCount];

                for (int c = 0; c < dataGridView1.ColumnCount; c++)
                {
                    headers[c] = dataGridView1.Rows[0].Cells[c].OwningColumn.HeaderText.ToString();
                    num = c + 65;
                    columns[c] = Convert.ToString((char)num);
                }

                try
                {
                    objApp = new Excel.Application();
                    objBooks = objApp.Workbooks;
                    objBook = objBooks.Add(Missing.Value);
                    objSheets = objBook.Worksheets;
                    objSheet = (Excel._Worksheet)objSheets.get_Item(1);

                    if (captions)
                    {
                        for (int c = 0; c < dataGridView1.ColumnCount; c++)
                        {
                            range = objSheet.get_Range(columns[c] + "1", Missing.Value);
                            range.set_Value(Missing.Value, headers[c]);
                        }
                    }

                    for (int i = 0; i < dataGridView1.RowCount - 1; i++)
                    {
                        for (int j = 0; j < dataGridView1.ColumnCount; j++)
                        {
                            range = objSheet.get_Range(columns[j] + Convert.ToString(i + 2),
                                                                   Missing.Value);
                            range.set_Value(Missing.Value,
                                                  dataGridView1.Rows[i].Cells[j].Value.ToString());
                        }
                    }

                    objApp.Visible = false;
                    objApp.UserControl = false;

                    objBook.SaveAs(@saveFileDialog.FileName,
                              Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                              missingType, missingType, missingType, missingType,
                              Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                              missingType, missingType, missingType, missingType, missingType);
                    objBook.Close(false, missingType, missingType);

                    Cursor.Current = Cursors.Default;

                    MessageBox.Show("저장되었습니다.");
                }
                catch (Exception theException)
                {
                    String errorMessage;
                    errorMessage = "Error: ";
                    errorMessage = String.Concat(errorMessage, theException.Message);
                    errorMessage = String.Concat(errorMessage, " Line: ");
                    errorMessage = String.Concat(errorMessage, theException.Source);

                    MessageBox.Show(errorMessage, "Error");
                }
            }
        }

2-1. 결과

3. Upload - Excel File을 Upload 하여 grid view에 뿌리기

/// <summary>
        /// 업로드 : 엑셀파일을 불러와서 gridview로 뿌려진다.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnUpload_Click(object sender, EventArgs e)
        {
            OleDbConnection excel_con = null;
            string xls_filename;
            try
            {
                OpenFileDialog Exfile = new OpenFileDialog(); // openfiledialog 객체 생성
                Exfile.InitialDirectory = @"C:\Users\Dsinfo\Desktop";             // 초기 path 설정
                Exfile.Filter = "엑셀 파일(*.xlsx)|*.xlsx";     // 파일 선택시 엑셀파일만 필터
                Exfile.CheckPathExists = true;
                Exfile.RestoreDirectory = true;




                if (Exfile.ShowDialog() == DialogResult.OK)
                {
                    xls_filename = Exfile.FileName;

                    string str_con = "Provider = Microsoft.ACE.OLEDB.12.0.0;Data Source=" + xls_filename + ";Extended Properties='Excel 12.0;HDR=YES'";
                    excel_con = new OleDbConnection(str_con);

                    excel_con.Open();
                    string excel_sql = @"select * from[Sheet1$]";

                    OleDbDataAdapter excel_adapter = new OleDbDataAdapter(excel_sql, excel_con);
                    DataSet excel_DS = new DataSet();
                    excel_adapter.Fill(excel_DS);


                    DataTable excel_table = excel_DS.Tables[0];



                    for (int i = 0; i < dataGridView1.ColumnCount; i++)
                    {
                        dataGridView1.AutoResizeColumn(i, DataGridViewAutoSizeColumnMode.AllCells); // 데이터에 맞게 컬럼 크기 조정
                    }



                    dataGridView1.DataSource = excel_table;


                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

3-1. 결과

4. Insert

- Update 프로시저 내용

- 코드

private void InsertData2() 
        {
            string strconn = @"Data Source=127.0.0.1;Initial Catalog=jkb;Persist Security Info=True;User ID=jkb;Password=본인 db 패스워드";

            List<string> temp = new List<string>();



            using (SqlConnection conn = new SqlConnection(strconn))
            {
                conn.Open();

				// 파라미터 저장
                List<string> temp1 = new List<string>();
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    temp1.Add(dataGridView1.Rows[i].Cells["id"].Value.ToString());
                }

                List<string> temp2 = new List<string>();
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    temp2.Add(dataGridView1.Rows[i].Cells["FirstName"].Value.ToString());
                }

                List<string> temp3 = new List<string>();
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    temp3.Add(dataGridView1.Rows[i].Cells["LastName"].Value.ToString());
                }

                List<string> temp4 = new List<string>();
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    temp4.Add(dataGridView1.Rows[i].Cells["Salary"].Value.ToString());
                }

                try
                {

                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "TEST_SEL012345";		// 수정 프로시저 명

                    for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                    {
                        cmd.Parameters.AddWithValue("@IN_ID", Int32.Parse(temp1[i].ToString()));
                        cmd.Parameters.AddWithValue("@IN_FIRST_NAME", temp2[i].ToString());
                        cmd.Parameters.AddWithValue("@IN_LAST_NAME", temp3[i].ToString());
                        cmd.Parameters.AddWithValue("@IN_SALARY", Int32.Parse(temp4[i].ToString()));

                        //cmd.Parameters.AddWithValue("@IN_RACK_CD", temp2[i].ToString());
                        //cmd.Parameters.AddWithValue("@IN_RACK_NM", temp3[i].ToString());

                        cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }

                    MessageBox.Show("저장에 성공했습니다.");

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }

4-1. 결과

※ 본인은 수정 프로시저 (UPDATE 문을 이용했지만 INSERT,DELETE 프로시저를 만들어서 사용가능하다.)

＊ 전체코드

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Win32;
using System.IO;
using System.Data.OleDb;
using Microsoft.Office.Interop;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;



namespace PracticeExcelUpload
{
    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //btnExport.Visible = false;

            
        }

        /// <summary>
        /// 업로드 : 엑셀파일을 불러와서 gridview로 뿌려진다.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnUpload_Click(object sender, EventArgs e)
        {
            OleDbConnection excel_con = null;
            string xls_filename;
            try
            {
                OpenFileDialog Exfile = new OpenFileDialog();                       // openfiledialog 객체 생성
                Exfile.InitialDirectory = @"C:\Users\Dsinfo\Desktop";             // 초기 path 설정
                Exfile.Filter = "엑셀 파일(*.xlsx)|*.xlsx";                         // 파일 선택시 엑셀파일만 필터
                Exfile.CheckPathExists = true;
                Exfile.RestoreDirectory = true;




                if (Exfile.ShowDialog() == DialogResult.OK)
                {
                    xls_filename = Exfile.FileName;

                    string str_con = "Provider = Microsoft.ACE.OLEDB.12.0.0;Data Source=" + xls_filename + ";Extended Properties='Excel 12.0;HDR=YES'";
                    excel_con = new OleDbConnection(str_con);

                    excel_con.Open();
                    string excel_sql = @"select * from[Sheet1$]";

                    OleDbDataAdapter excel_adapter = new OleDbDataAdapter(excel_sql, excel_con);
                    DataSet excel_DS = new DataSet();
                    excel_adapter.Fill(excel_DS);


                    DataTable excel_table = excel_DS.Tables[0];



                    for (int i = 0; i < dataGridView1.ColumnCount; i++)
                    {
                        dataGridView1.AutoResizeColumn(i, DataGridViewAutoSizeColumnMode.AllCells); // 데이터에 맞게 컬럼 크기 조정
                    }



                    dataGridView1.DataSource = excel_table;


                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }





        private void btnSearch_Click(object sender, EventArgs e)
        {
            Search();
        }

        /// <summary>
        /// 그리드뷰에 있는 데이터를 db에 저장
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnInsert_Click(object sender, EventArgs e)
        {
            //InsertData();

            InsertData2();
            
        }

        private void InsertData2() 
        {
            string strconn = @"Data Source=127.0.0.1;Initial Catalog=jkb;Persist Security Info=True;User ID=jkb;Password=본인 DB 패스워드";

            List<string> temp = new List<string>();



            using (SqlConnection conn = new SqlConnection(strconn))
            {
                conn.Open();

                List<string> temp1 = new List<string>();
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    temp1.Add(dataGridView1.Rows[i].Cells["id"].Value.ToString());
                }

                List<string> temp2 = new List<string>();
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    temp2.Add(dataGridView1.Rows[i].Cells["FirstName"].Value.ToString());
                }

                List<string> temp3 = new List<string>();
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    temp3.Add(dataGridView1.Rows[i].Cells["LastName"].Value.ToString());
                }

                List<string> temp4 = new List<string>();
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    temp4.Add(dataGridView1.Rows[i].Cells["Salary"].Value.ToString());
                }

                try
                {

                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "TEST_SEL012345";

                    for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                    {
                        cmd.Parameters.AddWithValue("@IN_ID", Int32.Parse(temp1[i].ToString()));
                        cmd.Parameters.AddWithValue("@IN_FIRST_NAME", temp2[i].ToString());
                        cmd.Parameters.AddWithValue("@IN_LAST_NAME", temp3[i].ToString());
                        cmd.Parameters.AddWithValue("@IN_SALARY", Int32.Parse(temp4[i].ToString()));

                        //cmd.Parameters.AddWithValue("@IN_RACK_CD", temp2[i].ToString());
                        //cmd.Parameters.AddWithValue("@IN_RACK_NM", temp3[i].ToString());

                        cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }

                    MessageBox.Show("저장에 성공했습니다.");

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }

        /// <summary>
        /// 조회 함수(저장 프로시저 사용)
        /// </summary>
        private void Search()
        {
            string strconn = @"Data Source=127.0.0.1;Initial Catalog=jkb;Persist Security Info=True;User ID=jkb;Password=본인 DB 패스워드";


            using (SqlConnection conn = new SqlConnection(strconn))
            {
                conn.Open();

                SqlDataAdapter da = new SqlDataAdapter("TEST_SEL0123", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);

                dataGridView1.DataSource = dt;


            }

        }


        /// <summary>
        /// 그리드뷰의 데이터를 엑셀로 export
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnExport_Click(object sender, EventArgs e)
        {

            Excel_Export(true);

        }

        private void Excel_Export(bool captions)
        {


            SaveFileDialog saveFileDialog = new SaveFileDialog();

            saveFileDialog.FileName = "TempName";
            saveFileDialog.DefaultExt = "xls";
            saveFileDialog.Filter = "Excel files (*.xls)|*.xls";
            saveFileDialog.InitialDirectory = "c:\\";



            DialogResult result = saveFileDialog.ShowDialog();

            if (result == DialogResult.OK)
            {
                int num = 0;
                object missingType = Type.Missing;

                Excel.Application objApp;
                Excel._Workbook objBook;
                Excel.Workbooks objBooks;
                Excel.Sheets objSheets;
                Excel._Worksheet objSheet;
                Excel.Range range;

                string[] headers = new string[dataGridView1.ColumnCount];
                string[] columns = new string[dataGridView1.ColumnCount];

                for (int c = 0; c < dataGridView1.ColumnCount; c++)
                {
                    headers[c] = dataGridView1.Rows[0].Cells[c].OwningColumn.HeaderText.ToString();
                    num = c + 65;
                    columns[c] = Convert.ToString((char)num);
                }

                try
                {
                    objApp = new Excel.Application();
                    objBooks = objApp.Workbooks;
                    objBook = objBooks.Add(Missing.Value);
                    objSheets = objBook.Worksheets;
                    objSheet = (Excel._Worksheet)objSheets.get_Item(1);

                    if (captions)
                    {
                        for (int c = 0; c < dataGridView1.ColumnCount; c++)
                        {
                            range = objSheet.get_Range(columns[c] + "1", Missing.Value);
                            range.set_Value(Missing.Value, headers[c]);
                        }
                    }

                    for (int i = 0; i < dataGridView1.RowCount - 1; i++)
                    {
                        for (int j = 0; j < dataGridView1.ColumnCount; j++)
                        {
                            range = objSheet.get_Range(columns[j] + Convert.ToString(i + 2),
                                                                   Missing.Value);
                            range.set_Value(Missing.Value,
                                                  dataGridView1.Rows[i].Cells[j].Value.ToString());
                        }
                    }

                    objApp.Visible = false;
                    objApp.UserControl = false;

                    objBook.SaveAs(@saveFileDialog.FileName,
                              Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                              missingType, missingType, missingType, missingType,
                              Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                              missingType, missingType, missingType, missingType, missingType);
                    objBook.Close(false, missingType, missingType);

                    Cursor.Current = Cursors.Default;

                    MessageBox.Show("저장되었습니다.");
                }
                catch (Exception theException)
                {
                    String errorMessage;
                    errorMessage = "Error: ";
                    errorMessage = String.Concat(errorMessage, theException.Message);
                    errorMessage = String.Concat(errorMessage, " Line: ");
                    errorMessage = String.Concat(errorMessage, theException.Source);

                    MessageBox.Show(errorMessage, "Error");
                }
            }
        }




       
        

        

        private void btnClear_Click(object sender, EventArgs e)
        {
            ((DataTable)dataGridView1.DataSource).Rows.Clear();
        }


        

        

       

        private void btnInit_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
        }




        

        private void btnTest_Click(object sender, EventArgs e)
        {

        }

        

    }
}

저작자표시

'C#' 카테고리의 다른 글

2020.09.17 - 온/습도 모듈 센서를 이용, 차트 작성 (0)	2020.09.17
2020.09.16 (0)	2020.09.16
2020.09.15 - c# review (0)	2020.09.15
2020.09.11 - 온/습도 측정하기 / 리눅스 (0)	2020.09.11
2020.09.10 - (0)	2020.09.10

현재글Excel Export, Serch, Insert, Upload - 초간단 버전(MS-SQL)

J_Bin

Excel Export, Serch, Insert, Upload - 초간단 버전(MS-SQL)

'C#' 카테고리의 다른 글

'C#'의 다른글

티스토리툴바

Excel Export, Serch, Insert, Upload - 초간단 버전(MS-SQL)

'C#' 카테고리의 다른 글

'C#'의 다른글

관련글

티스토리툴바