본문 바로가기
C# 개인 공부 기록용/개인 실습

SpreadSheet + Excel + Oracle + Xml (Synthesis Test)

둥둥다랑 부리부리 2022. 7. 26.

TABLE

CREATE TABLE TEST_SUBWAY(
SUBWAY_NUMBER NUMBER NOT NULL PRIMARY KEY,
SUBWAY_NAME VARCHAR2(81),
SUBWAY_LINE VARCHAR2(81)
);

INSERT INTO TEST_SUBWAY VALUES(
1119,
'홍대입구역',
'경의중앙선'
);

UPDATE TEST_SUBWAY SET
SUBWAY_NUMBER = 11111,
SUBWAY_NAME = '서울역',
SUBWAY_LINE = '1호선'
WHERE SUBWAY_NUMBER = 1111

DELETE TEST_SUBWAY WHERE SUBWAY_NUMBER = 1119

SELECT * FROM TEST_SUBWAY ORDER BY SUBWAY_NUMBER ASC
SELECT * FROM TEST_SUBWAY ORDER BY SUBWAY_NUMBER DESC

 

XML + XML CLASS

<?xml version="1.0" encoding="utf-8" ?>
<Oracle_Conn_Data>
	<ip>아이피 주소</ip>
	<service>서비스 이름</service>
	<id>아이디</id>
	<password>패스워드</password>
</Oracle_Conn_Data>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using System.Windows.Forms;

namespace PRACTICE
{
    class xmlClass
    {
        private XmlDocument xd;
        private string xmlurl;
        private XmlNodeList xl;

        private string ip;
        private string sv;
        private string id;
        private string pw;

        public void Xml_Conn_Fn()
        {
            try
            {
                xd = new XmlDocument();
                xmlurl = Application.StartupPath + @"\OCDX.xml";
                xd.Load(xmlurl);

                xl = xd.SelectNodes("Oracle_Conn_Data");
                foreach (XmlNode xn in xl)
                {
                    ip = xn["ip"].InnerText;
                    sv = xn["service"].InnerText;
                    id = xn["id"].InnerText;
                    pw = xn["password"].InnerText;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("xml error");
            }
        }

        public string get_ip
        {
            get { return ip; }
        }

        public string get_sv
        {
            get { return sv; }
        }

        public string get_id
        {
            get { return id; }
        }

        public string get_pw
        {
            get { return pw; }
        }
    }
}

 

ORACLE CLASS

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;
using System.Windows.Forms;

namespace PRACTICE
{
    class oracleClass
    {
        private xmlClass xc = new xmlClass();
        private OracleConnection conn;
        private string connstr;

        private string ip;
        private string sv;
        private string id;
        private string pw;

        public void Oracle_Conn_Fn()
        {
            try
            {
                xc.Xml_Conn_Fn();
                ip = xc.get_ip;
                sv = xc.get_sv;
                id = xc.get_id;
                pw = xc.get_pw;

                connstr = "data source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = " + ip + ")" +
                     "(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = " + sv + ")));USER ID=" + id + ";PASSWORD=" + pw + ";";

                conn = new OracleConnection(connstr);
                conn.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("oracle error");
            }
        }

        public OracleConnection get_conn
        {
            get { return conn; }
        }
    }
}

 

 

BASIC FORM

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 Oracle.DataAccess.Client;
using FarPoint.Win.Spread;

namespace PRACTICE
{
    public partial class BASIC_FORM : Form
    {
        oracleClass oc = new oracleClass();
        string sqlstr;
        OracleCommand cmd;
        DataSet ds;
        OracleDataAdapter oda;
        OracleDataReader reader;

        int row_num;
        string select_cell_sub_num;

        public BASIC_FORM()
        {
            InitializeComponent();
        }

        protected internal void basic_load()
        {
            try
            {
                oc.Oracle_Conn_Fn();
                sqlstr = "SELECT * FROM TEST_SUBWAY ORDER BY SUBWAY_NUMBER ASC";
                cmd = new OracleCommand(sqlstr, oc.get_conn);
                cmd.ExecuteReader();

                ds = new DataSet();

                oda = new OracleDataAdapter(sqlstr, oc.get_conn);
                oda.Fill(ds);

                fpSpread1.DataSource = ds.Tables[0];

                oc.get_conn.Close();
                fpSpread1_Sheet1.Columns[0, 2].Width = 300;
                for (int i = 0; i < fpSpread1.ActiveSheet.ColumnCount; i++)
                {
                    fpSpread1.ActiveSheet.Columns[i].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
                    fpSpread1.ActiveSheet.Columns[i].VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("load error");
            }
        }

        private void BASIC_FORM_Load(object sender, EventArgs e)
        {
            fpSpread1.Sheets[0].OperationMode = FarPoint.Win.Spread.OperationMode.SingleSelect;
            basic_load();
        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            I_U_FORM frm = new I_U_FORM();
            frm.conversion = true;
            frm.Owner = this;
            frm.ShowDialog();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            row_num = fpSpread1.ActiveSheet.ActiveRowIndex;
            select_cell_sub_num = fpSpread1.ActiveSheet.Cells[row_num, 0].Value.ToString();

            I_U_FORM frm = new I_U_FORM();
            frm.sub_num = select_cell_sub_num;
            frm.conversion = false;
            frm.Owner = this;
            frm.ShowDialog();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            row_num = fpSpread1.ActiveSheet.ActiveRowIndex;
            select_cell_sub_num = fpSpread1.ActiveSheet.Cells[row_num, 0].Value.ToString();
            string select_cell_sub_name = fpSpread1.ActiveSheet.Cells[row_num, 1].Value.ToString();
            string select_cell_sub_line = fpSpread1.ActiveSheet.Cells[row_num, 2].Value.ToString();

            if (MessageBox.Show(select_cell_sub_num +
                "\n" + select_cell_sub_name +
                "\n" + select_cell_sub_line +
                "\n\n해당 데이터를 삭제하시겠습니까?", "DELETE", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                oc.Oracle_Conn_Fn();
                sqlstr = "DELETE FROM TEST_SUBWAY WHERE SUBWAY_NUMBER = " + select_cell_sub_num + "";

                // StringBuilder nquery = new StringBuilder();
                // nquery.AppendFormat("DELETE FROM TEST_SUBWAY WHERE SUBWAY_NUMBER = '{0}'", select_cell_sub_num);
                // cmd = new OracleCommand(nquery.ToString(), oc.get_conn);

                cmd = new OracleCommand(sqlstr, oc.get_conn);
                cmd.ExecuteNonQuery();

                oc.get_conn.Close();

                basic_load();
            }
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                if (tbNum.Text == string.Empty && tbName.Text == string.Empty)
                {
                    MessageBox.Show("검색할 역사번호와 역사명을 입력해주세요!");
                    return;
                }
                else if (tbNum.Text == string.Empty)
                {
                    MessageBox.Show("검색할 역사번호를 입력해주세요");
                    return;
                }
                else if (tbName.Text == string.Empty)
                {
                    MessageBox.Show("검색할 역사명을 입력해주세요");
                    return;
                }
                oc.Oracle_Conn_Fn();
                sqlstr = "SELECT * FROM TEST_SUBWAY WHERE SUBWAY_NUMBER =" + tbNum.Text + " AND SUBWAY_NAME = '" + tbName.Text + "'";

                cmd = new OracleCommand(sqlstr, oc.get_conn);
                reader = cmd.ExecuteReader();

                if (!reader.Read())
                {
                    MessageBox.Show("조회된 데이터가 없습니다!");
                    return;
                }

                ds = new DataSet();

                oda = new OracleDataAdapter(sqlstr, oc.get_conn);
                oda.Fill(ds);

                fpSpread1.DataSource = ds.Tables[0];

                fpSpread1_Sheet1.Columns[0, 2].Width = 300;
                for (int i = 0; i < fpSpread1.ActiveSheet.ColumnCount; i++)
                {
                    fpSpread1.ActiveSheet.Columns[i].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
                    fpSpread1.ActiveSheet.Columns[i].VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
                }

                oc.get_conn.Close();
                sqlstr = "SELECT * FROM TEST_SUBWAY ORDER BY SUBWAY_NUMBER DESC";
            }
            catch (Exception ex)
            {
                MessageBox.Show("해당 데이터가 없습니다!");
            }
        }

        private void btnAlign_Click(object sender, EventArgs e)
        {
            try
            {
                oc.Oracle_Conn_Fn();

                if (sqlstr == "SELECT * FROM TEST_SUBWAY ORDER BY SUBWAY_NUMBER ASC")
                {
                    sqlstr = "SELECT * FROM TEST_SUBWAY ORDER BY SUBWAY_NUMBER DESC";
                }
                else
                {
                    sqlstr = "SELECT * FROM TEST_SUBWAY ORDER BY SUBWAY_NUMBER ASC";
                }

                cmd = new OracleCommand(sqlstr, oc.get_conn);
                cmd.ExecuteReader();

                ds = new DataSet();

                oda = new OracleDataAdapter(sqlstr, oc.get_conn);
                oda.Fill(ds);

                fpSpread1.DataSource = ds.Tables[0];

                oc.get_conn.Close();

                fpSpread1_Sheet1.Columns[0, 2].Width = 300;
                for (int i = 0; i < fpSpread1.ActiveSheet.ColumnCount; i++)
                {
                    fpSpread1.ActiveSheet.Columns[i].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
                    fpSpread1.ActiveSheet.Columns[i].VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("filter error");
            }
        }

        private void btnExcel_Click(object sender, EventArgs e)
        {
            try
            {
                // 저장
                SaveFileDialog saveFile = new SaveFileDialog();
                saveFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
                saveFile.Filter = "Excel files (*.xls)|*.xl|All files (*.*)|*.*"; // 틀리면 오류 발생
                saveFile.FilterIndex = 1;

                if (saveFile.ShowDialog() == DialogResult.OK)
                {
                    fpSpread1.SaveExcel(saveFile.FileName, FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders);
                    MessageBox.Show("저장이 완료 되었습니다.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("excel save error");
            }
        }
    }
}

 

INSERT & UPDATE

using Oracle.DataAccess.Client;
using System;
using System.Windows.Forms;

namespace PRACTICE
{
    public partial class I_U_FORM : Form
    {
        protected internal bool conversion;

        protected internal string sub_num;
        private string sub_name;
        private string sub_line;
        private string past_sub_num;

        oracleClass oc = new oracleClass();
        string sqlstr;
        OracleCommand cmd;
        OracleDataReader reader;

        public I_U_FORM()
        {
            InitializeComponent();
        }
        private void I_U_FORM_Load(object sender, EventArgs e)
        {
            if (conversion)
            {
                tbNumber.Clear();
                tbName.Clear();
                tbLine.Clear();
            }
            else if (!conversion)
            {
                try
                {
                    tbNumber.Enabled = false;
                    // tbNumber.ReadOnly = true;

                    past_sub_num = sub_num;
                    oc.Oracle_Conn_Fn();
                    sqlstr = "SELECT * FROM TEST_SUBWAY WHERE SUBWAY_NUMBER = " + sub_num + "";

                    cmd = new OracleCommand(sqlstr, oc.get_conn);
                    reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        tbNumber.Text = reader.GetInt32(0).ToString();
                        tbName.Text = reader.GetString(1).ToString();
                        tbLine.Text = reader.GetString(2).ToString();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("update load error");
                }
            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            if (conversion)
            {
                try
                {
                    sub_num = tbNumber.Text;
                    sub_name = tbName.Text;
                    sub_line = tbLine.Text;

                    if (tbNumber.Text == string.Empty || tbName.Text == string.Empty || tbLine.Text == string.Empty)
                    {
                        MessageBox.Show("빈칸 없이 입력해주세요");
                        return;
                    }

                    oc.Oracle_Conn_Fn();
                    sqlstr = "SELECT SUBWAY_NUMBER FROM TEST_SUBWAY WHERE SUBWAY_NUMBER = " + sub_num + "";

                    cmd = new OracleCommand(sqlstr, oc.get_conn);
                    reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        if (reader.GetInt32(0) == Int32.Parse(sub_num))
                        {
                            MessageBox.Show("이미 등록된 역사번호입니다.");
                            tbNumber.Clear();
                            oc.get_conn.Close();
                            return;
                        }
                    }

                    sqlstr = "INSERT INTO TEST_SUBWAY VALUES(" + Int32.Parse(sub_num) + ", '" + sub_name + "', '" + sub_line + "')";

                    cmd = new OracleCommand(sqlstr, oc.get_conn);
                    cmd.ExecuteNonQuery();

                    oc.get_conn.Close();

                    MessageBox.Show("역사번호 : " + sub_num +
                        "\n역사명 : " + sub_name +
                        "\n역사라인 : " + sub_line + "\n등록되었습니다.");
                    ((BASIC_FORM)this.Owner).basic_load();

                    tbNumber.Clear();
                    tbName.Clear();
                    tbLine.Clear();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("insert error");
                }
            }
            else if (!conversion)
            {
                try
                {
                    sub_num = tbNumber.Text;
                    sub_name = tbName.Text;
                    sub_line = tbLine.Text;


                    if (tbNumber.Text == string.Empty || tbName.Text == string.Empty || tbLine.Text == string.Empty)
                    {
                        MessageBox.Show("빈칸 없이 입력해주세요");
                        return;
                    }

                    oc.Oracle_Conn_Fn();
                    sqlstr = "SELECT SUBWAY_NUMBER FROM TEST_SUBWAY WHERE SUBWAY_NUMBER NOT IN(" + past_sub_num + ")";
                    cmd = new OracleCommand(sqlstr, oc.get_conn);
                    reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        if (reader.GetInt32(0).ToString() == tbNumber.Text)
                        {
                            MessageBox.Show("이미 존재하는 역사번호입니다.\n다른 번호를 입력해주세요");
                            return;
                        }
                    }

                    sqlstr = "UPDATE TEST_SUBWAY SET " +
                        "SUBWAY_NUMBER = " + sub_num +
                        ", SUBWAY_NAME = '" + sub_name +
                        "', SUBWAY_LINE = '" + sub_line +
                        "' WHERE SUBWAY_NUMBER = " + past_sub_num + "";

                    cmd = new OracleCommand(sqlstr, oc.get_conn);
                    cmd.ExecuteNonQuery();

                    MessageBox.Show("수정이 완료되었습니다.");

                    ((BASIC_FORM)this.Owner).basic_load();

                    oc.get_conn.Close();

                    I_U_FORM.ActiveForm.Close(); // 폼 종료
                }
                catch (Exception ex)
                {
                    MessageBox.Show("update error");
                }
            }
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            I_U_FORM.ActiveForm.Close(); // 폼 종료
        }
    }
}

 

기본 화면

 

등록

 

수정

 

삭제

 

엑셀

저작자표시 비영리 변경금지 (새창열림)

'C# 개인 공부 기록용 > 개인 실습' 카테고리의 다른 글

Xml + Oracle + Sql + StringBuilder  (0) 2022.08.03
Preference (Settings) + Xml (아이디 저장? 데이터 저장?)  (0) 2022.08.02
fpSpread Practice (SpreadSheet) + Xml + Oracle  (0) 2022.07.21
DataGridView + Selected (Cell, Row)  (0) 2022.07.18
XML + ORACLE DB + SQL QUERY  (0) 2022.07.13

'C# 개인 공부 기록용/개인 실습' 관련글

티스토리툴바