JAVA - 자바 GUI로 엑셀(Excel) 연결해서 수정 저장하기
2024. 1. 12. 17:15ㆍFull Stack Course 풀스택과정/JAVA 자바
728x90
배울내용:
자바 엑셀 연동
poi-bin
poi-bin library
이클립스와 엑셀연동
자바에서 엑셀기능 쓰기
자바에서 엑셀수정 저장하기
자바 스윙
자바 GUI 엑셀연동
자바 스윙 쓰는법
이번시간에는 앞에 썼던 기능들이 다합쳐진 최종본이라고 볼수있다
먼저
DB 에 연결하려고 JDBC(Java Database Connectivity) 로 연결하고
Poi bin 으로 엑셀파일을 불러오게한뒤
그걸 수정, 저장해
다시 DB에 저장하든 바탕화면에 저장하는것이다.
먼저 엑셀에 x 와 y 축의 Cell 을 받을 함수를 새로운 클래스에서 선언해주고
change 나 NotChange를 출력함으로써 잘 나오는지 확인해준다
그리고 엑셀의 cell을 받을때에는 type도 다다르게 받아야해서 새로운
메서드를 만들어서 String 은 String 으로, Numeric은 String 으로 해서 전부 String 형으로 바꿔준다
그외에도 필요한 함수는 직접만든다
먼저 component 들을 생성해서 꾸며준다
그뒤에는 모든 버튼이 작동할수있게 이벤트를 넣어주는데
버튼이 많다.
엄청많다
그래도 어쩔수없다 모두다 이벤트 리스너로 기능을 구현해줘야한다
그런데 처음부터 쉽지가않다 글찾기에서 x 축과 y 축의 위치를 알아낸뒤에
값을 추출하고 그리고 그값만 변경 또는 전체변경을 시켜주고 다시 저장해야하기때문에
생각보다 기능과 많은 시간이 걸린다
먼저 어떻게 엑셀파일을 불러오게 한다면
열기를 했을때
위에처럼 불러와진다
이때까지는 쉽지만 여기서부터 조금 헷갈리고 어렵다
먼저 x , y 의 위치를 알아야하기 때문에
위에 코드를 작성하고 X좌표 , Y좌표 보기를 클릭해준다
먼저 x 와 y 의 좌표를 받아오고 거기에 있는 value (값) 을 찾아야하는데
그값을 정규표현식(regular expression) 을 써서 group을 3개로 나눈뒤 비교를해서
확실하게 구해준다
그뒤에 다시 나와서
1, 2 번을 설정한뒤에 좌표확인을해주고 파란색label이 11C 를 가리키는걸 볼수있다
그리고 아까 excel function 클래스에서 만들어놓은 2차원 배열 함수를 이용해서
변경해준다
그리고 나머지 DB도 연결시켜서 쿼리문과 table에 형식만써주고
새로운 함수를 만들어 로그인 만들던것처럼 해주면
데이터베이스에도 넣어줄수있다
(귀찮아서 안만듬 )
이건 저장이다 .
저장하고자 하는 위치를 System.getProperty 한뒤에 user.home으로 + Desktop 해서 바탕화면에 저장이 된다
참고로 excel 파일이 아닌 txt 파일로 했는데
따로 뛰어주는걸 " " 을 "," 으로바꾼뒤 확장자를 .csv 하면 엑셀파일처럼 저장될수도있다 : )
다시 돌아가
변경되려는 문자열에 좌표확인한 11C 를 55로 변경을 누르면
전부 변경된걸 확인할수있다
그리고 여기서 저장 버튼을 누르면
기존의 바탕화면에
이렇게 되있던게
저장을 클릭후 TextField 창이뜨면 여기에 원하는제목입력하고
저장을 누르면
바탕화면에 저장되었다고 표시가 되면서
바탕화면에 가보면
이렇게 되있는걸 볼수있다 그리고 이걸 클릭해보면
마지막으로 설정된 장면을 저장한다
참고로 아이디를 로그인도있다
여기에서 아이디를 입력받고 로그인하면
알람창을 뛰워주고
이런식으로 바뀐다 그리고 이때 저장을 하면 성공적으로 DB에 저장이 된다 (DB 연결 안만듬)
아래는 코드와 함께 영상이 포함되어있다
잘보고 참고용으로 쓰길바란다
package CARLOS1;
import java.awt.Color;
import java.awt.EventQueue;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.swing.BorderFactory;
import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JPasswordField;
import javax.swing.JRadioButton;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.border.Border;
import javax.swing.border.EmptyBorder;
import javax.swing.border.LineBorder;
import javax.swing.border.MatteBorder;
import javax.swing.border.TitledBorder;
import org.apache.poi.openxml4j.util.ZipSecureFile;
public class _4_Excel extends JFrame {
private static final long serialVersionUID = 1L;
private JPanel contentPane;
public static String FilePath = "..\\ProjectTest_CARLOS\\images\\";
// _전용 =========================
private JPanel EX_panel_1;
private JPanel EX_Panel_Panel_find;
private JPanel EX_Panel_DB_Box;
private JButton EX_B_Find_value;
private JButton EX_B_OK_BACK;
private JButton EX_B_DB_Save_1;
private JButton EX_B_DB_LogOut;
private JButton EX_B_1_Save;
private JButton EX_B_2_Redo;
private JButton EX_B_DB_Save_login;
private JButton EX_B_3_FileInput;
private JButton EX_B_4_Clear;
private JButton EX_B_Change;
private JButton EX_B_Y_Up;
private JButton EX_B_Y_Down;
private JButton EX_B_X_Up;
private JButton EX_B_X_Down;
private JButton btnNewButton;
private JButton m_Excel;
private JLabel EX_L_Found_Count;
private JLabel lblNewLabel_3; //그림
private JLabel EX_L_INFO_1;
private JLabel EX_L_INFO_2;
private JLabel EX_L_INFO_3;
private JLabel EX_L_INFO_4;
private JLabel EX_L_INFO_RED;
private JLabel EX_L_X_Info;
private JLabel EX_L_Y_Info;
private JLabel EX_L_IdInfo;
private JLabel EX_L_PwInfo;
private JLabel lblNewLabel;
private JLabel lblNewLabel_1;
private JLabel lblNewLabel_2;
private JPasswordField EX_TF_DB_Pw;
private JTextField EX_TF_DB_Id;
private JTextField EX_TF_Y;
private JTextField EX_TF_X;
private JTextField EX_TF_SAVE_Info;
private JTextField EX_TF_Find_Input;
private JTextArea EX_TF_Find_1;
private JTextArea EX_TF_Found_1;
private JTextArea EX_TF_1_main;
private JScrollPane EX_SP_Find_1;
private JScrollPane EX_SP_Found_1;
private JScrollPane EX_SP_1;
private int EX_Main_Panel_cnt = 0;
private int EX_XY_Location = 0;
private int EX_findCount = 0;
private int EX_tmp_X1 = 0;
private int EX_tmp_X2 = 0;
private int EX_tmp_Y1 = 0;
private int EX_tmp_Y2 = 0;
private String EX_uid = "";
private String EX_Chg_word = "";
private String excel_main_temp = "";
private static int EX_SAVE_Cnt = 1;
private static JFileChooser fileChooser = new JFileChooser();
private static File selectedFile = fileChooser.getSelectedFile();
public static JRadioButton EX_R_Option1;
// public static int CK_WordCng = 0;
public static int EX_TF_rowIndex = 0;
public static int EX_TF_colIndex = 0;
public static JTextField EX_TF_ChangeOne;
//=====================================================
public static void main(String[] args) {
EventQueue.invokeLater(new Runnable() {
public void run() {
try {
new Database();
_4_Excel frame = new _4_Excel();
frame.setVisible(true);
} catch (Exception e) {
e.printStackTrace();
}
}
});
}
public _4_Excel() {
// 큰 엑셀 파일 열수있게 하는 것
ZipSecureFile.setMinInflateRatio(0.005);
/////////
// MAIN
/////////
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setBounds(0, 0, 1400, 1000);
contentPane = new JPanel();
contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
setContentPane(contentPane);
contentPane.setLayout(null);
EX_panel_1 = new JPanel();
EX_panel_1.setBorder(new LineBorder(new Color(255, 0, 0), 3, true));
EX_panel_1.setBounds(150, 258, 1090, 642);
EX_TF_SAVE_Info = new JTextField("제목을 입력하세요(확장자 X)");
EX_TF_SAVE_Info.setBackground(new Color(255, 255, 255));
EX_TF_SAVE_Info.setFont(new Font("맑은 고딕", Font.BOLD, 15));
EX_TF_SAVE_Info.setForeground(new Color(255, 0, 0));
EX_TF_SAVE_Info.setBounds(620, 321, 280, 100);
EX_B_1_Save = new JButton("저 장");
EX_B_1_Save.setBounds(913, 482, 150, 125);
EX_B_1_Save.setFont(new Font("한컴 고딕", Font.PLAIN, 30));
EX_SP_1 = new JScrollPane();
EX_SP_1.setBounds(34, 70, 567, 562);
EX_L_INFO_1 = new JLabel("Excel");
EX_L_INFO_1.setBounds(12, 7, 250, 30);
EX_L_INFO_1.setFont(new Font("한컴 고딕", Font.BOLD, 20));
EX_L_INFO_2 = new JLabel("변경할 행렬 입력");
EX_L_INFO_2.setBounds(617, 106, 171, 15);
EX_L_INFO_2.setFont(new Font("한컴 고딕", Font.BOLD, 15));
EX_TF_X = new JTextField("0");
EX_TF_X.setFont(new Font("굴림", Font.PLAIN, 25));
EX_TF_X.setBounds(656, 131, 55, 50);
EX_TF_X.setColumns(10);
EX_TF_X.setHorizontalAlignment(JTextField.CENTER);
EX_TF_ChangeOne = new JTextField();
EX_TF_ChangeOne.setBounds(639, 241, 221, 32);
EX_TF_ChangeOne.setColumns(10);
EX_L_INFO_3 = new JLabel("변경되려는 문자열");
EX_L_INFO_3.setBounds(617, 216, 171, 15);
EX_L_INFO_3.setFont(new Font("한컴 고딕", Font.BOLD, 15));
EX_L_INFO_4 = new JLabel("");
EX_L_INFO_4.setForeground(new Color(0, 0, 255));
EX_L_INFO_4.setFont(new Font("맑은 고딕", Font.BOLD, 14));
EX_L_INFO_4.setBounds(620, 326, 280, 100);
EX_B_2_Redo = new JButton("좌표확인");
EX_B_2_Redo.setFont(new Font("한컴 고딕", Font.PLAIN, 30));
EX_B_2_Redo.setBounds(913, 216, 150, 125);
// JLabel lblNewLabel_3 = new JLabel("");
// lblNewLabel_3.setBounds(140, 75, 615, 415);
// lblNewLabel_3.setIcon(new ImageIcon(FilePath + "vogue1.jpg"));
// contentPane.add(lblNewLabel_3);
EX_TF_1_main = new JTextArea();
EX_SP_1.setViewportView(EX_TF_1_main);
EX_TF_1_main.setColumns(10);
EX_Panel_DB_Box = new JPanel();
Font titleFont = new Font("한컴 고딕", Font.PLAIN, 20);
Border border = BorderFactory.createTitledBorder(new LineBorder(Color.black), // 테두리 색상
"DB 에 저장", // 제목
TitledBorder.DEFAULT_JUSTIFICATION, // 기본 정렬
TitledBorder.DEFAULT_POSITION, // 기본 위치
titleFont // 설정한 글꼴
);
EX_Panel_DB_Box.setBorder(border);
EX_Panel_DB_Box.setBackground(new Color(255, 255, 255));
EX_Panel_DB_Box.setBounds(617, 438, 278, 169);
EX_B_DB_Save_login = new JButton("로그인");
EX_B_DB_Save_login.setBounds(60, 105, 98, 54);
EX_B_DB_Save_login.setFont(new Font("한컴 고딕", Font.PLAIN, 20));
EX_B_DB_Save_1 = new JButton("저장");
EX_B_DB_Save_1.setFont(new Font("한컴 고딕", Font.PLAIN, 25));
EX_B_DB_Save_1.setBounds(168, 105, 98, 54);
EX_TF_DB_Id = new JTextField();
EX_TF_DB_Id.setColumns(10);
EX_TF_DB_Id.setBounds(45, 30, 221, 32);
EX_TF_DB_Pw = new JPasswordField();
;
EX_TF_DB_Pw.setColumns(10);
EX_TF_DB_Pw.setBounds(45, 68, 221, 32);
EX_L_IdInfo = new JLabel("ID");
EX_L_IdInfo.setFont(new Font("한컴 고딕", Font.BOLD, 15));
EX_L_IdInfo.setBounds(15, 38, 21, 15);
EX_L_PwInfo = new JLabel("PW");
EX_L_PwInfo.setFont(new Font("한컴 고딕", Font.BOLD, 15));
EX_L_PwInfo.setBounds(12, 73, 32, 24);
EX_B_3_FileInput = new JButton("엑셀파일 불러오기");
EX_B_3_FileInput.setFont(new Font("맑은 고딕", Font.BOLD, 20));
EX_B_3_FileInput.setBounds(623, 26, 214, 70);
EX_B_4_Clear = new JButton("초기화");
EX_B_4_Clear.setFont(new Font("맑은 고딕", Font.BOLD, 20));
EX_B_4_Clear.setBounds(849, 26, 214, 70);
EX_L_INFO_RED = new JLabel("엑셀파일을 불러와주세요.");
EX_L_INFO_RED.setBounds(87, 10, 515, 30);
EX_L_INFO_RED.setForeground(new Color(255, 0, 0));
EX_L_INFO_RED.setFont(new Font("HY헤드라인M", Font.PLAIN, 15));
EX_TF_Y = new JTextField("0");
EX_TF_Y.setFont(new Font("굴림", Font.PLAIN, 25));
EX_TF_Y.setColumns(10);
EX_TF_Y.setBounds(891, 131, 55, 50);
EX_TF_Y.setHorizontalAlignment(JTextField.CENTER);
EX_L_X_Info = new JLabel("X :");
EX_L_X_Info.setFont(new Font("한컴 고딕", Font.BOLD, 20));
EX_L_X_Info.setBounds(610, 158, 34, 15);
EX_L_Y_Info = new JLabel("Y :");
EX_L_Y_Info.setFont(new Font("한컴 고딕", Font.BOLD, 20));
EX_L_Y_Info.setBounds(841, 158, 34, 15);
EX_B_Change = new JButton("변 경");
EX_B_Change.setFont(new Font("한컴 고딕", Font.PLAIN, 30));
EX_B_Change.setBounds(913, 351, 150, 125);
EX_B_Y_Up = new JButton("▲");
EX_B_Y_Up.setFont(new Font("맑은 고딕", Font.BOLD, 15));
EX_B_Y_Up.setBounds(958, 131, 52, 51);
EX_B_Y_Down = new JButton("▼");
EX_B_Y_Down.setFont(new Font("맑은 고딕", Font.BOLD, 15));
EX_B_Y_Down.setBounds(1011, 131, 52, 51);
EX_B_X_Up = new JButton("▲");
EX_B_X_Up.setFont(new Font("맑은 고딕", Font.BOLD, 15));
EX_B_X_Up.setBounds(724, 131, 52, 51);
EX_B_X_Down = new JButton("▼");
EX_B_X_Down.setFont(new Font("맑은 고딕", Font.BOLD, 15));
EX_B_X_Down.setBounds(777, 131, 52, 51);
EX_R_Option1 = new JRadioButton("동일문자 전부변경");
EX_R_Option1.setFont(new Font("맑은 고딕", Font.BOLD, 15));
EX_R_Option1.setBounds(617, 292, 243, 23);
m_Excel = new JButton("엑셀파일 불러오기");
m_Excel.setFont(new Font("맑은 고딕", Font.BOLD, 20));
m_Excel.setBounds(788, 162, 214, 70);
// 찾는 Panel 전용 ========================
lblNewLabel = new JLabel("X →");
lblNewLabel.setFont(new Font("굴림", Font.BOLD, 15));
lblNewLabel.setBounds(43, 50, 52, 15);
lblNewLabel_1 = new JLabel("Y");
lblNewLabel_1.setFont(new Font("굴림", Font.BOLD, 15));
lblNewLabel_1.setBounds(12, 89, 17, 32);
lblNewLabel_2 = new JLabel("↓");
lblNewLabel_2.setFont(new Font("굴림", Font.BOLD, 15));
lblNewLabel_2.setBounds(12, 95, 17, 62);
btnNewButton = new JButton("X,Y 좌표보기");
btnNewButton.setFont(new Font("굴림", Font.BOLD, 13));
btnNewButton.setBounds(472, 26, 129, 30);
EX_Panel_Panel_find = new JPanel();
EX_Panel_Panel_find.setBorder(new MatteBorder(2, 2, 2, 2, (Color) new Color(0, 0, 0)));
EX_Panel_Panel_find.setBounds(760, 268, 464, 622);
EX_Panel_Panel_find.setBackground(new Color(255, 255, 255));
EX_SP_Find_1 = new JScrollPane();
EX_SP_Find_1.setBounds(12, 10, 440, 459);
EX_TF_Find_1 = new JTextArea();
EX_TF_Find_1.setColumns(10);
EX_SP_Find_1.setViewportView(EX_TF_Find_1);
EX_B_Find_value = new JButton("찾기");
EX_B_Find_value.setFont(new Font("맑은 고딕", Font.BOLD, 15));
EX_B_Find_value.setBounds(342, 478, 95, 23);
EX_TF_Find_Input = new JTextField();
EX_TF_Find_Input.setFont(new Font("굴림", Font.BOLD, 12));
EX_TF_Find_Input.setText("찾고자 하는 값 입력");
EX_TF_Find_Input.setBounds(23, 479, 307, 23);
EX_TF_Find_Input.setColumns(10);
EX_SP_Found_1 = new JScrollPane();
EX_SP_Found_1.setBounds(23, 512, 307, 96);
EX_TF_Found_1 = new JTextArea();
EX_TF_Found_1.setFont(new Font("HY그래픽M", Font.BOLD, 12));
EX_TF_Found_1.setText("찾은 값 리스트 ");
EX_TF_Found_1.setColumns(10);
EX_SP_Found_1.setViewportView(EX_TF_Found_1);
EX_L_Found_Count = new JLabel("찾은개수 : ");
EX_L_Found_Count.setFont(new Font("굴림", Font.BOLD, 15));
EX_L_Found_Count.setBounds(335, 510, 125, 23);
EX_B_OK_BACK = new JButton("확인");
EX_B_OK_BACK.setFont(new Font("맑은 고딕", Font.BOLD, 25));
EX_B_OK_BACK.setBounds(342, 541, 95, 67);
EX_B_DB_LogOut = new JButton("New button");
EX_B_DB_LogOut.setBounds(10, 30, 260, 70);
// 순서정하기
contentPane.add(EX_Panel_Panel_find);
contentPane.add(EX_panel_1);
// 찾는 Panel 전용
EX_panel_1.add(EX_L_INFO_RED);
EX_panel_1.add(EX_TF_SAVE_Info);
EX_panel_1.add(lblNewLabel);
EX_panel_1.add(lblNewLabel_1);
EX_panel_1.add(lblNewLabel_2);
EX_panel_1.add(btnNewButton);
EX_Panel_Panel_find.setLayout(null);
EX_Panel_Panel_find.add(EX_B_OK_BACK);
EX_Panel_Panel_find.add(EX_L_Found_Count);
EX_Panel_Panel_find.add(EX_SP_Found_1);
EX_Panel_Panel_find.add(EX_TF_Find_Input);
EX_Panel_Panel_find.add(EX_SP_Find_1);
EX_Panel_Panel_find.add(EX_B_Find_value);
EX_Panel_Panel_find.setVisible(false);
setLocationRelativeTo(null);
// Excel_전용 =========================
contentPane.add(m_Excel);
EX_panel_1.add(EX_R_Option1);
EX_panel_1.add(EX_B_X_Down);
EX_panel_1.add(EX_B_X_Up);
EX_panel_1.add(EX_B_Y_Down);
EX_panel_1.add(EX_B_Y_Up);
EX_panel_1.add(EX_B_Change);
EX_panel_1.add(EX_L_Y_Info);
EX_panel_1.add(EX_L_X_Info);
EX_Panel_DB_Box.add(EX_B_DB_LogOut);
EX_Panel_DB_Box.add(EX_L_PwInfo);
EX_Panel_DB_Box.add(EX_L_IdInfo);
EX_Panel_DB_Box.add(EX_TF_DB_Pw);
EX_Panel_DB_Box.add(EX_TF_DB_Id);
EX_Panel_DB_Box.add(EX_B_DB_Save_login);
EX_Panel_DB_Box.add(EX_B_DB_Save_1);
EX_panel_1.add(EX_B_4_Clear);
EX_panel_1.add(EX_B_3_FileInput);
EX_panel_1.add(EX_Panel_DB_Box);
EX_panel_1.add(EX_SP_1);
EX_panel_1.add(EX_TF_Y);
EX_panel_1.add(EX_TF_X);
EX_panel_1.add(EX_TF_ChangeOne);
EX_panel_1.add(EX_L_INFO_1);
EX_panel_1.add(EX_L_INFO_2);
EX_panel_1.add(EX_L_INFO_3);
EX_panel_1.add(EX_L_INFO_4);
EX_panel_1.add(EX_B_1_Save);
EX_panel_1.add(EX_B_2_Redo);
EX_panel_1.setVisible(true);
EX_panel_1.setLayout(null);
EX_Panel_DB_Box.setLayout(null);
EX_TF_SAVE_Info.setVisible(false);
EX_B_DB_LogOut.setVisible(false);
EX_panel_1.setVisible(false);
// ===================================
//////////////
// EXcel 이벤트
//////////////
// excel_main_temp
// 창 엑셀 메인 창 열고 닫기
m_Excel.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
EX_Main_Panel_cnt++;
if (EX_Main_Panel_cnt % 2 == 1) {
EX_panel_1.setVisible(true);
EX_Panel_Panel_find.setVisible(false);
} else {
EX_panel_1.setVisible(false);
EX_Panel_Panel_find.setVisible(false);
}
EX_B_Change.setVisible(true);
EX_B_Y_Down.setVisible(true);
EX_B_4_Clear.setVisible(true);
EX_B_1_Save.setVisible(true);
EX_B_2_Redo.setVisible(true);
}
});
///////////////////////
// X,y 좌표 보기창 열기
///////////////////////
btnNewButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
EX_XY_Location++;
if (EX_XY_Location % 2 == 1) {
EX_Panel_Panel_find.setVisible(true);
EX_B_Change.setVisible(false);
EX_B_Y_Down.setVisible(false);
EX_B_4_Clear.setVisible(false);
EX_B_1_Save.setVisible(false);
EX_B_2_Redo.setVisible(false);
EX_L_INFO_RED.setText(" ");
} else {
EX_Panel_Panel_find.setVisible(false);
EX_B_Change.setVisible(true);
EX_B_Y_Down.setVisible(true);
EX_B_4_Clear.setVisible(true);
EX_B_1_Save.setVisible(true);
EX_B_2_Redo.setVisible(true);
EX_L_INFO_RED.setText("파일을 선택해주세요.");
}
String temp_main = "";
EX_TF_Find_1.setText("");
if (EX_TF_1_main.getText().equals("") == true || EX_TF_1_main.getText().equals(null)) {
EX_L_INFO_RED.setText("파일을 선택먼저 해주세요. ");
} else {
List<Excel_functions.CellData> data = Excel_functions.modify_CellData(excel_main_temp);
for (Excel_functions.CellData cellData : data) {
temp_main += cellData.rowIndex + " y : " + cellData.columnIndex + " x : " + cellData.value
+ "\n";
}
EX_TF_Find_1.setText(temp_main);
}
}
});
// 글 찾기
EX_B_Find_value.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
EX_findCount = 0;
String EX_find_show = "";
System.out.println("선택한 파일: " + selectedFile.getAbsolutePath());
String lines = EX_TF_Find_1.getText();
String[] linesArray = lines.split("\\n");
// 정규 표현식 패턴 (y와 x 값, 그리고 추가 값 추출)
Pattern pattern = Pattern.compile("(\\d+)\\s+y\\s+:\\s+(\\d+)\\s+x\\s+:\\s+(.+)");
for (String line : linesArray) {
Matcher matcher = pattern.matcher(line);
if (matcher.find()) {
String yValue = matcher.group(1); // 'y' 뒤의 값
String xValue = matcher.group(2); // 'x' 뒤의 값
String additionalValue = matcher.group(3); // 추가 값
if (additionalValue.contains(EX_TF_Find_Input.getText())) {
EX_find_show += "y: " + yValue + ", x: " + xValue + ", 추가 값: " + additionalValue + "\n";
System.out.println("y: " + yValue + ", x: " + xValue + ", 추가 값: " + additionalValue);
EX_findCount++;
}
}
}
EX_TF_Found_1.setText(EX_find_show);
EX_L_Found_Count.setText("찾은 개수 : " + String.valueOf(EX_findCount));
}
});
// 확인
EX_B_OK_BACK.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
EX_Panel_Panel_find.setVisible(false);
EX_B_Change.setVisible(true);
EX_B_Y_Down.setVisible(true);
EX_B_4_Clear.setVisible(true);
EX_B_1_Save.setVisible(true);
EX_B_2_Redo.setVisible(true);
EX_L_INFO_RED.setText("선택한 파일: " + selectedFile.getAbsolutePath());
}
});
// 위치확인
EX_B_2_Redo.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
if (EX_TF_X.getText().equals("") && EX_TF_Y.getText().equals("")) {
EX_L_INFO_4.setText("X 와 Y 값을 전부 채워주세요. ");
System.out.println("X 와 Y 값을 전부 채워주세요. ");
} else {
List<Excel_functions.CellData> data = Excel_functions.modify_CellData(excel_main_temp);
for (Excel_functions.CellData cellData : data) {
if (Integer.valueOf(EX_TF_X.getText()) == cellData.columnIndex
&& Integer.valueOf(EX_TF_Y.getText()) == cellData.rowIndex) {
System.out.println("Row: " + cellData.rowIndex + ", Column: " + cellData.columnIndex
+ "에는 Value: " + cellData.value + " 가 있습니다. ");
EX_L_INFO_4.setText("<html>Row: " + cellData.rowIndex + ", Column: "
+ cellData.columnIndex + "에는 <br>" + cellData.value + "<br> (이)가 있습니다.</html>");
EX_Chg_word = cellData.value;
break;
}
System.out.println();
}
}
} catch (Exception e1) {
EX_L_INFO_4.setText("정상작동이 이루어 지지않았습니다. \\n 파일을 불러왔는지 확인해주세요. ");
System.out.println("정상작동이 이루어 지지않았습니다. \n 파일을 불러왔는지 확인해주세요. ");
e1.printStackTrace();
}
}
});
// 변경
EX_B_Change.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
EX_TF_colIndex = Integer.valueOf(EX_TF_X.getText());
EX_TF_rowIndex = Integer.valueOf(EX_TF_Y.getText());
String changeOne = EX_Chg_word;
String ChangeFinal = "";
String box1[][] = Excel_functions.change_CellData(selectedFile.getAbsolutePath(), changeOne);
for (int i = 0; i < box1.length; i++) {
for (int j = 0; j < box1[0].length; j++) {
System.out.print(box1[i][j] + "\t");
ChangeFinal += (box1[i][j] + "\t");
}
ChangeFinal += "\n";
System.out.println();
}
EX_TF_1_main.setText(ChangeFinal);
}
});
// DB 로그인
EX_B_DB_Save_login.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
EX_uid = EX_TF_DB_Id.getText();
String upass = "";
for (int i = 0; i < EX_TF_DB_Pw.getPassword().length; i++) {
upass = upass + EX_TF_DB_Pw.getPassword()[i];
}
if (EX_uid.equals("") || upass.equals("")) {
JOptionPane.showMessageDialog(null, "아이디와 비밀번호 모두 입력해주세요", "로그인 실패", JOptionPane.ERROR_MESSAGE);
System.out.println("로그인 실패 > 로그인 정보 미입력");
}
else if (EX_uid != null && upass != null) {
if (Database.logincheck(EX_uid, upass)) {
System.out.println("로그인 성공");
EX_L_INFO_4.setBackground(new Color(0, 128, 255));
EX_L_INFO_4.setText("로그인에 성공하셨습니다.");
JOptionPane.showMessageDialog(null, "아이디 : " + EX_uid + " 님 \n 어서오세요.");
EX_B_DB_LogOut.setText("<html> 로그인 계정 : " + EX_uid + "<br>클릭시 로그아웃 </html>");
EX_B_DB_Save_login.setVisible(false);
EX_B_DB_LogOut.setVisible(true);
} else {
EX_L_INFO_4.setBackground(new Color(255, 128, 128));
EX_L_INFO_4.setText("로그인에 실패하셨습니다.");
System.out.println("로그인 실패 > 로그인 정보 불일치");
JOptionPane.showMessageDialog(null, "로그인에 실패하였습니다");
}
}
}
});
// DB 로그아웃
EX_B_DB_LogOut.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
EX_B_DB_LogOut.setText("");
EX_TF_DB_Id.setText("");
EX_TF_DB_Pw.setText("");
EX_L_INFO_4.setText("성공적으로 로그아웃 하셨습니다.");
EX_B_DB_LogOut.setVisible(false);
EX_B_DB_Save_login.setVisible(true);
}
});
// DB 저장
EX_B_DB_Save_login.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
EX_panel_1.setVisible(true);
}
});
// 저장
EX_B_1_Save.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
String contentToSave = EX_TF_1_main.getText();
String fileName = "";
String desktopPath = System.getProperty("user.home") + "/Desktop/";
if (EX_SAVE_Cnt % 3 == 1) {
EX_TF_SAVE_Info.setVisible(true);
} else if (EX_SAVE_Cnt % 3 == 2) {
fileName = desktopPath + EX_TF_SAVE_Info.getText() + ".txt";
try (BufferedWriter writer = new BufferedWriter(new FileWriter(fileName))) {
writer.write(contentToSave);
EX_TF_SAVE_Info.setVisible(false);
EX_L_INFO_4.setText("파일이 바탕화면에 저장되었습니다.");
} catch (IOException e1) {
e1.printStackTrace();
}
}
EX_SAVE_Cnt++;
}
});
// 초기화
EX_B_4_Clear.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
EX_TF_DB_Id.setText("");
EX_TF_DB_Pw.setText("");
EX_TF_ChangeOne.setText("");
EX_TF_1_main.setText("");
EX_TF_X.setText("0");
EX_TF_Y.setText("0");
EX_tmp_X1 = 0;
EX_tmp_X2 = 0;
EX_tmp_Y1 = 0;
EX_tmp_Y2 = 0;
EX_L_INFO_4.setText("");
EX_L_INFO_RED.setText("엑셀파일을 불러와주세요.");
EX_L_INFO_RED.setFont(new Font("HY헤드라인M", Font.PLAIN, 15));
}
});
// 파일 불러오기
EX_B_3_FileInput.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
fileChooser = new JFileChooser();
int returnValue = fileChooser.showOpenDialog(null);
if (returnValue == JFileChooser.APPROVE_OPTION) {
selectedFile = fileChooser.getSelectedFile();
System.out.println("선택한 파일: " + selectedFile.getAbsolutePath());
String temp_main = CARLOS1.Excel_functions.EX_show_all(selectedFile.getAbsolutePath());
excel_main_temp = selectedFile.getAbsolutePath();
EX_TF_1_main.setText(temp_main);
EX_L_INFO_RED.setText("선택한 파일: " + selectedFile.getAbsolutePath());
EX_L_INFO_RED.setFont(new Font("HY헤드라인M", Font.PLAIN, 10));
}
}
});
// 숫자 증감 X , Y 좌표
// X 좌표
EX_B_X_Up.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
EX_tmp_X1 = Integer.valueOf(EX_TF_X.getText());
EX_tmp_X1 += 1;
if (EX_tmp_X1 < 1) {
EX_tmp_X1 = 0;
}
EX_TF_X.setText( String.valueOf(EX_tmp_X1));
}
});
EX_B_X_Down.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
EX_tmp_X2 = Integer.valueOf(EX_TF_X.getText());
EX_tmp_X2 -= 1;
if (EX_tmp_X2 < 1) {
EX_tmp_X2 = 0;
}
EX_TF_X.setText(String.valueOf(EX_tmp_X2));
}
});
// Y 좌표
EX_B_Y_Up.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
EX_tmp_Y1 = Integer.valueOf(EX_TF_Y.getText());
EX_tmp_Y1 += 1;
if (EX_tmp_Y1 < 1) {
EX_tmp_Y1 = 0;
}
EX_TF_Y.setText(String.valueOf(EX_tmp_Y1));
}
});
EX_B_Y_Down.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
EX_tmp_Y2 = Integer.valueOf(EX_TF_Y.getText());
EX_tmp_Y2 -= 1;
if (EX_tmp_Y2 < 1) {
EX_tmp_Y2 = 0;
}
EX_TF_Y.setText(String.valueOf(EX_tmp_Y2));
}
});
}
}
Excel 기능하는 코드
package CARLOS1;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Excel_functions {
static String excel_show = "";
static class CellData {
int rowIndex;
int columnIndex;
String value;
public CellData(int rowIndex, int columnIndex, String value) {
this.rowIndex = rowIndex;
this.columnIndex = columnIndex;
this.value = value;
}
}
public static String[][] change_CellData(String filePath, String changeone) {
String[][] cellDataArray = null;
try {
FileInputStream file = new FileInputStream(new File(filePath));
Workbook workbook = new XSSFWorkbook(file);
Sheet sheet = workbook.getSheetAt(0);
int rowCount = sheet.getPhysicalNumberOfRows();
int colCount = sheet.getRow(0).getPhysicalNumberOfCells();
cellDataArray = new String[rowCount][colCount];
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
Row row = sheet.getRow(rowIndex);
for (int colIndex = 0; colIndex < colCount; colIndex++) {
Cell cell = row.getCell(colIndex);
int findRowIndex = cell.getRowIndex();
int findColumnIndex = cell.getColumnIndex();
String cellValue = getCellValueAsString(cell);
if(Main.EX_R_Option1.isSelected()) {
if(cellValue.equals(changeone)) {
cellDataArray[rowIndex][colIndex] = Main.EX_TF_ChangeOne.getText();
System.out.println("change = " + Main.EX_TF_ChangeOne.getText());
}
else {
cellDataArray[rowIndex][colIndex] = cellValue;
System.out.println("Nchange = " + cellValue);
}
}
else {
System.out.println( "EX_TF row : "+Main.EX_TF_rowIndex+ " find row Index : " + findRowIndex + " EX TF Col : " +_4_Excel.EX_TF_colIndex +" find col : " + findColumnIndex);
if(cellValue.equals(changeone) && Main.EX_TF_rowIndex ==findRowIndex && Main.EX_TF_colIndex == findColumnIndex) {
cellDataArray[rowIndex][colIndex] = Main.EX_TF_ChangeOne.getText();
System.out.println("change = " + Main.EX_TF_ChangeOne.getText());
}
else {
cellDataArray[rowIndex][colIndex] = cellValue;
System.out.println("Nchange = " + cellValue);
}
}
}
}
file.close();
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
return cellDataArray;
}
public static ArrayList<String> change_CellData1(String filePath, String changeone) {
ArrayList<String> cellDataList = new ArrayList<>();
try {
FileInputStream file = new FileInputStream(new File(filePath));
Workbook workbook = new XSSFWorkbook(file);
Sheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
int rowLength = sheet.getRow(0).getPhysicalNumberOfCells();
for (Cell cell : row) {
String cellValue = getCellValueAsString(cell);
cellDataList.add(cellValue);
}
}
file.close();
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
return cellDataList;
}
// 위치보기
public static List<CellData> modify_CellData(String filePath) {
List<CellData> cellDataList = new ArrayList<>();
try {
FileInputStream file = new FileInputStream(new File(filePath));
Workbook workbook = new XSSFWorkbook(file);
Sheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
String cellValue = getCellValueAsString(cell);
cellDataList.add(new CellData(cell.getRowIndex(), cell.getColumnIndex(), cellValue));
}
}
file.close();
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
return cellDataList;
}
public static String ch_filter(String find_by_name) {
return find_by_name;
}
// 형에 맞게 만들어주는 것
public static String getCellValueAsString(Cell cell) {
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue();
case NUMERIC:
return String.valueOf(cell.getNumericCellValue());
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
default:
return "none";
}
}
// 전부 출력
public static String EX_show_all(String filePath) {
try {
excel_show = "";
FileInputStream file = new FileInputStream(new File(filePath));
Workbook workbook = new XSSFWorkbook(file);
Sheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
switch (cell.getCellType()) {
case STRING:
excel_show += cell.getStringCellValue() + "\t";
System.out.println("cell row = " + cell.getRowIndex() + "column = " + cell.getColumnIndex()
+ " value = " + cell.getStringCellValue());
break;
case NUMERIC:
excel_show += cell.getNumericCellValue() + "\t";
System.out.println("cell row = " + cell.getRowIndex() + "column = " + cell.getColumnIndex()
+ " value = " + cell.getNumericCellValue());
break;
case BOOLEAN:
excel_show += cell.getBooleanCellValue() + "\t";
System.out.println("cell row = " + cell.getRowIndex() + "column = " + cell.getColumnIndex()
+ " value = " + cell.getBooleanCellValue());
break;
default:
excel_show += " - \t";
System.out.println("cell row = " + cell.getRowIndex() + "column = " + cell.getColumnIndex()
+ " value = none");
break;
}
}
excel_show += "\n";
}
file.close();
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
return excel_show;
}
}
그럼 20000~
728x90
'Full Stack Course 풀스택과정 > JAVA 자바' 카테고리의 다른 글
| 자바 GUI - 프로젝트 전체 과정 (0) | 2024.01.18 |
|---|---|
| 자바 GUI - 일기장 만들기 (MYSQL 연동) (0) | 2024.01.17 |
| JAVA - 자바 GUI 프로젝트 합치기 (0) | 2024.01.12 |
| JAVA - 자바 GUI 로 MYSQL 연동하기 (2) | 2024.01.10 |
| JAVA - 자바 GUI 로 계산기 만들기 (1) | 2024.01.04 |