구리

TIL_210325_자바로 엑셀 파일 읽기, 데이터 다루기 관련 문제 본문

Java

TIL_210325_자바로 엑셀 파일 읽기, 데이터 다루기 관련 문제

guriguriguri 2021. 3. 25. 22:42

이클립스에서 자바로 엑셀 파일 읽기

※외부의 엑셀 파일 읽기 전 외부 라이브러리 확인※

※외부 라이브러리 패키지 import 시 다른 라이브러리와 섞이지 않도록 주의※

 

1. 파일 경로 지정

   File f = new File("읽고 싶은 엑셀 파일명.xls");

 

2. 엑셀 파일 객체 얻기 

    Workbook wb = Workbook.getWorkbook(File 객체);  (매개변수로 파일 객체 넣기)

 

3. 시트 얻기 

   Sheet s = wb.getSheet( 시트 인덱스 );

 

4. 셀 얻기

   Cell c = s.getCell( 열 인덱스, 행 인덱스);

 

5. 셀 내용 일기

   c.getContents();

 

다음은 엑셀 파일 읽기 예시이다.

 

읽고 싶은 엑셀 파일의 내용이며 c:/filetest에 위치한다

 

엑셀 파일을 읽어주는 ExcelReaderClass

package com.bjy;

import java.io.File;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class ExcelReaderClass {

	public ExcelReaderClass() {
		
	}
	
	public void loadExcel() {
		File f = new File("c:/filetest/data.xls"); 				// java.io.File
		Workbook wb = null;                        				// jxl.Workbook
		
		try {
			wb = Workbook.getWorkbook(f); 
			Sheet s = wb.getSheet(0);             			    // jxl.Sheet
			
			int i = 0; // 엑셀 시트내의 행번호 카운트 변수

			while(true) {
				try {
					Cell c = s.getCell(0, i);  		// jxl.Cell
					System.out.println(c.getContents());       // cell 안에 data를 읽는 메서드
					i++;
					
				}catch (Exception e) {
					break; // 읽을 데이터가 더이상 없다면 while문 중지 
				}
			}
			
		}catch (Exception e) {
			System.out.println("엑셀 파일 읽기 실패 ERR : " + e.getMessage());
		}
	} // end of loadExcel()

	
}

MainClass에서 ExcelReaderClass 실행시

엑셀 파일의 내용 출력 결과

 

연습문제

사전 환경

1. 첨부 파일의 엑셀을 읽어서 결혼하고 집소유자들의 정보만 bank_data.html 과 bank_data​.xls로 각각 저장하시오.

2. 단, 검색조건은 콘솔창에서 입력 받고, 검색 조건은 달라질 수 있습니다.

3. jxl.jar 외부 라이브러리가 있어야 합니다.

 

패키지 및 클래스 예시

각 클래스 역할 

1. MainClass : 전체 흐름을 담당 (일종의 Controller) / main() 포함

2. BankClass :  모든 변수들은 모두 외부 접근 불가하다.

                          loan : 대출 여부 (있슴, 없슴, 모름 등..)

                         duration​ : 은행 가입 기간 (일 단위)

                         contact : 연락방법  ​(한글로 저장 : 휴대폰 / 집전화.)

3. CustomerClass : 모든 변수들은 모두 외부 접근 불가!!!   BankClass 를 상속

                        age : 나이 (숫자로 저장)

                              job : 직업 ​(한글로 저장 : 학생, 은퇴,  등..)

                             marital : 결혼 여부 (한글로 저장 : 기혼, 미혼, , 이혼, 모름 등)

                               housing : 집 소유 여부 (한글로 저장 : 소유, 미소유, 모름)

4. DataClass : 전체 데이터를 보유 : CustomerClass 객체들 저장

5. MethodsInterface : MethodClass에서 사용할 메서드 명들을 정의

6. MethodClass : 실행 메서드들만  보유  /  해당 메서드들은 MethodsInterface 를 구현받음

7. ExcelReaderClass​ : 엑셀 파일 읽기 / 해당 메서드들은 객체 생성 없이 사용할 수 있도록 선언

8. ExcelWriterClass​ : 엑셀 파일 쓰기​ / 해당 메서드들은 객체 생성 없이 사용할 수 있도록 선언

9. PrintClass :  출력 전용 클래스,  "c:/filetest/bank_data.html" 파일을 생성하는 역할 담당 

                        해당 메서드들은 객체 생성 없이 사용할 수 있도록 선언​ 

 

 

 

영문, 한글 변환을 위한 단어장 클래스

package com.bjy.util;

/** 영문, 한글 단어장을 위한 클래스 **/
public class EnglishToHangul {
	
	private String eng = "";
	private String hangul = "";

	public EnglishToHangul() {
		
	}
	
	public EnglishToHangul(String eng, String hangul) {
		this.eng = eng;
		this.hangul = hangul;
	}

	public String getEng() {
		return eng;
	}

	public String getHangul() {
		return hangul;
	}

}

데이터 저장을 위한 변수 클래스 

package com.bjy.data;

public class BankClass {
	private String loan="";
	private String contact="";
	private int duration=0;

	public BankClass() {
		
	}
	
	public BankClass(String loan, String contact, int duration) {
		this.loan = loan;
		this.contact = contact;
		this.duration = duration;
	}

	public String getLoan() {
		return loan;
	}

	public int getDuration() {
		return duration;
	}

	public String getContact() {
		return contact;
	}
 
}

BankClass를 상속받은 CustomerClass

package com.bjy.data;

import java.util.ArrayList;

import com.bjy.util.EnglishToHangul;

public class CustomerClass extends BankClass{
	private int age=0;
	private String job="";
	private String marital="";
	private String housing=""; 
	
	public CustomerClass() {}
	
	/** 
	 * 엑셀 파일 한줄 데이터를 age ~ duration까지 문자열로 전달 받아
	 * 숫자 관련 데이터 : 문자열=> 숫자로 바꾼 후 저장
	 * 영문 데이터 : 단어장 이용하여 한글 변환 후 저장
	 * 데이터 중 loan, contact, duration => 변환 후 부모 생성자에게 전달
	 * **/
	public CustomerClass(String age, String job, String marital, String housing, String loan, String contact, String duration) {
		super(usToKr(loan, DataClass.us_kr_loan), usToKr(contact,DataClass.us_kr_contact), Integer.parseInt(duration));
		this.age = Integer.parseInt(age);
		this.job= usToKr(job, DataClass.us_kr_job);
		this.marital = usToKr(marital,DataClass.us_kr_marital);
		this.housing = usToKr(housing, DataClass.us_kr_housing);
	}
	
	/** 영어를 한글로 바꿔 반환하는 메서드 (매개변수 : 영어단어, 해당 단어장)**/
	private static String usToKr(String word, ArrayList<EnglishToHangul> en_kr_list) {
		String kr = "";
		
		for(EnglishToHangul en : en_kr_list) {
			if(word.equals(en.getEng())) {
				kr = en.getHangul();
			}
		}
		return kr;
	}

	public void testToString() {
		System.out.println(this.getAge() + " : " +this.getJob() + " : " +this.getMarital() + " : " +this.getHousing() + " : " +super.getLoan() + " : " +super.getContact() + " : " +super.getDuration());

	}

	public int getAge() {
		return age;
	}

	public String getJob() {
		return job;
	}

	public String getMarital() {
		return marital;
	}

	public String getHousing() {
		return housing;
	}
	
	

}

DataClass

package com.bjy.data;

import java.util.ArrayList;

import com.bjy.util.EnglishToHangul;

public class DataClass {
	public static ArrayList<EnglishToHangul> us_kr_header=new ArrayList<EnglishToHangul>();
	
	/** 각 데이터에 맞는 단어장 리스트 **/
	public static ArrayList<EnglishToHangul> us_kr_job = new ArrayList<EnglishToHangul>();
	public static ArrayList<EnglishToHangul> us_kr_marital = new ArrayList<EnglishToHangul>();
	public static ArrayList<EnglishToHangul> us_kr_housing = new ArrayList<EnglishToHangul>();
	public static ArrayList<EnglishToHangul> us_kr_loan = new ArrayList<EnglishToHangul>();
	public static ArrayList<EnglishToHangul> us_kr_contact = new ArrayList<EnglishToHangul>();
	
	/** 하나의 정보 객체들을 저장 받는 리스트  **/
	public static ArrayList<CustomerClass> customerData = new ArrayList<CustomerClass>();

	/** 헤더 부분 **/
	public static String[] header = {"번호", "나이", "직업", "결혼여부", "집소유여부", "대출여부", "연락방법" , "은행 계약기간(일단위)" }; 
	public DataClass() {
		
	}
	
	public static void translationData() {
		us_kr_job.add(new EnglishToHangul("admin.", "관리"));
		us_kr_job.add(new EnglishToHangul("blue-collar", "노동"));
		us_kr_job.add(new EnglishToHangul("entrepreneur", "사업"));
		us_kr_job.add(new EnglishToHangul("housemaid", "가정부"));
		us_kr_job.add(new EnglishToHangul("management", "경영"));
		us_kr_job.add(new EnglishToHangul("retired", "은퇴"));
		us_kr_job.add(new EnglishToHangul("self-employed", "자영업"));
		us_kr_job.add(new EnglishToHangul("services", "서비스"));
		us_kr_job.add(new EnglishToHangul("student", "학생"));
		us_kr_job.add(new EnglishToHangul("technician", "기술자"));
		us_kr_job.add(new EnglishToHangul("unemployed", "실직"));
		us_kr_job.add(new EnglishToHangul("unknown", "모름"));
		
		us_kr_marital.add(new EnglishToHangul("divorced", "이혼"));
		us_kr_marital.add(new EnglishToHangul("married", "기혼"));
		us_kr_marital.add(new EnglishToHangul("single", "미혼"));
		
		us_kr_housing.add(new EnglishToHangul("no", "없슴"));
		us_kr_housing.add(new EnglishToHangul("unknown", "모름"));
		us_kr_housing.add(new EnglishToHangul("yes", "소유"));
		
		us_kr_loan.add(new EnglishToHangul("no", "없슴"));
		us_kr_loan.add(new EnglishToHangul("unknown", "모름"));
		us_kr_loan.add(new EnglishToHangul("yes", "소유"));

		us_kr_contact.add(new EnglishToHangul("cellular", "휴대폰"));
		us_kr_contact.add(new EnglishToHangul("telephone", "집전화"));
		
		us_kr_header.add(new EnglishToHangul("age", "나이"));
		us_kr_header.add(new EnglishToHangul("job", "직업"));
		us_kr_header.add(new EnglishToHangul("marital", "결혼여부"));
		us_kr_header.add(new EnglishToHangul("housing", "집소유여부"));
		us_kr_header.add(new EnglishToHangul("loan", "대출여부"));
		us_kr_header.add(new EnglishToHangul("contact", "연락방법"));
		us_kr_header.add(new EnglishToHangul("duration", "은행 가입기간 (일단위)"));
	}

}

 

엑셀 파일 읽어주고 DataClass에 데이터 저장해주는 ExcelReaderClass

package com.bjy.util;

import java.io.File;

import com.bjy.data.CustomerClass;
import com.bjy.data.DataClass;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

/** 엑셀 파일 읽은 후 DataClass ArrayList<CustomerClass> customerData 배열에 객체 추가 **/
public class ExcelReaderClass {

	public ExcelReaderClass() {
		
	}
	
	public static void loadExcel(String uri_excel) {
		DataClass.customerData.clear();
		
		File f = new File(uri_excel);
		Workbook wb = null;
		
		
		try {
			wb = Workbook.getWorkbook(f);
			Sheet s = wb.getSheet(0);
			//s.getColumns(); // 시트 열 개수 반환
			
			CustomerClass customer = null;
			
			/** 열(칸) 관련 **/
			Cell[] cell = new Cell[7];
			
			
			/** 행관련 변수 **/
			int i = 1;
			
			while(true) {
				try {
					
					for(int idx=0;idx<cell.length;idx++) {
						cell[idx] = s.getCell(idx+1,i);	
					}
					customer = new CustomerClass(cell[0].getContents(), cell[1].getContents(), cell[2].getContents(), cell[3].getContents(), cell[4].getContents(), cell[5].getContents(), cell[6].getContents());
					//customer.testToString();
					DataClass.customerData.add(customer);
					i++;
					
				}catch (Exception e) {
					System.out.println("엑셀 데이터 모두 읽음" + e.getMessage());
					break;
				}
			
			} // end of while()
			
			
		}catch(Exception e) {
			System.out.println(" 엑셀 데이터 읽기 실패 ERR : " + e.getMessage());
		}

	}

}

MethodsInterface

package com.bjy.util;

import java.util.ArrayList;

public interface MethodsInterface {
	
	/** 결혼 여부, 집소유 여부에 따른 검색 메서드**/
	public ArrayList<Integer> searchInfo(String search1, String search2);
	
	/** 검색 결과 이용해 html 태그 생성 **/
	public abstract String makeTags(ArrayList<Integer> resultIndex);
	
	
}

MethodClass

package com.bjy.util;

import java.util.ArrayList;

import com.bjy.data.CustomerClass;
import com.bjy.data.DataClass;

public class MethodClass implements MethodsInterface{

	@Override
	public ArrayList<Integer> searchInfo(String search1, String search2) {
		ArrayList<CustomerClass> all = DataClass.customerData;
		
		ArrayList<Integer> resultIndex = new ArrayList<Integer>(); 
		// 검색 결과에 대한 인덱스 번호 저장 
		
		for(int i=0;i<all.size();i++) {
			if(search1.equals(all.get(i).getMarital()) && search2.equals(all.get(i).getHousing()))
				resultIndex.add(i);
		}
		return resultIndex;
	} // end of searchInfo() 

	@Override
	public String makeTags(ArrayList<Integer> resultIndex) {
		String tags="";
		tags = tags + "<!doctype>";
		tags = tags + "<html>";
		tags = tags + "<head><title>Bank Data</title></head>";
		tags = tags + "<body>";
		
		if(resultIndex.size() > 0) {
			tags = tags + "총 " + resultIndex.size() + "명이 검색되었습니다.";
			tags = tags + "<table border='1'>";
			tags = tags + "<tr>";
			tags = tags + "<td>번호</td>";
			tags = tags + "<td>나이</td>";
			tags = tags + "<td>직업</td>";
			tags = tags + "<td>결혼여부</td>";
			tags = tags + "<td>집소유여부</td>";
			tags = tags + "<td>대출여부</td>";
			tags = tags + "<td>연락처</td>";
			tags = tags + "<td>은행 가입 기간 (일 단위)</td>";
			tags = tags + "</tr>";
			
			int noCount=1;
			for(int customerIndex : resultIndex) {
				tags = tags + "<tr>";
				tags = tags + "<td>"+ noCount +"</td>";
				tags = tags + "<td>"+ DataClass.customerData.get(customerIndex).getAge() +"</td>";
				tags = tags + "<td>"+ DataClass.customerData.get(customerIndex).getJob() +"</td>";
				tags = tags + "<td>"+ DataClass.customerData.get(customerIndex).getMarital() +"</td>";
				tags = tags + "<td>"+ DataClass.customerData.get(customerIndex).getHousing() +"</td>";;
				tags = tags + "<td>"+ DataClass.customerData.get(customerIndex).getLoan() +"</td>";
				tags = tags + "<td>"+ DataClass.customerData.get(customerIndex).getContact() +"</td>";
				tags = tags + "<td>"+ DataClass.customerData.get(customerIndex).getDuration() +"</td>";
				tags = tags + "</tr>";
				noCount++;
			}
			tags = tags + "</table>"; // table 닫기
			
		}else {
			tags = tags + "조회된 결과가 없습니다.";
		}
		tags = tags + "</body>"; // body 닫기
		tags = tags + "</html>"; // html 닫기
		
		return tags;
	}
	
}

tags, uri_html를 이용해 html 파일을 만드는 PrintClass

package com.bjy.out;

import java.io.FileWriter;
import java.io.IOException;

public class PrintClass {

	public PrintClass() {
		
	}
	
	public static void makeHTML(String tags, String uri_html) {
		FileWriter fw = null;
		
		try {
			fw = new FileWriter(uri_html);
			System.out.println("fw : 파일 객체 생성");
			fw.write(tags);
			System.out.println("fw wrtie : 파일 태그 작성");

		}catch (IOException e) {
			System.out.println("HTML 파일 생성 ERR : " + e.getMessage());
		}finally {
			try {
				fw.close();
			} catch (IOException e) {
				System.out.println("HTML파일 닫기 ERR : " + e.getMessage());
			}
		}
		
		
	}

}

원하는 데이터를 엑셀 파일로 만들어 작성해주는 ExcelWriterClass

package com.bjy.util;

import java.io.File;
import java.util.ArrayList;

import com.bjy.data.CustomerClass;
import com.bjy.data.DataClass;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExcelWriterClass {

	public ExcelWriterClass() {
		
	}
	
	public static void writeExcel(String uri_xls, ArrayList<Integer> resultIndex) {
		File f = new File(uri_xls);
		WritableWorkbook wb = null;
		WritableSheet s = null;
		
		
		try {
			wb = Workbook.createWorkbook(f);
			s = wb.createSheet("첫번째 시트", 0);
			ArrayList<Label> label = new ArrayList<Label>();
			label.add(new Label(0,0,"번호"));
			ArrayList<CustomerClass> customer = DataClass.customerData;
			int n=0;
			
			
			for(int i=1;i<8;i++) {
				label.add(new Label(i,0,DataClass.header[i-1]));
			}
			
			
			
			for(int i=0;i<resultIndex.size();i++) {
				n=resultIndex.get(i);
				label.add(new Label(0,i+1,i+1 + ""));
				label.add(new Label(1,i+1,customer.get(n).getAge() + " "));
				label.add(new Label(2,i+1,customer.get(n).getJob()));
				label.add(new Label(3,i+1,customer.get(n).getMarital()));
				label.add(new Label(4,i+1,customer.get(n).getHousing()));
				label.add(new Label(5,i+1,customer.get(n).getLoan()));
				label.add(new Label(6,i+1,customer.get(n).getContact()));
				label.add(new Label(7,i+1,customer.get(n).getDuration() + " "));
			}
			
			for(int i=0;i<label.size();i++) {
				s.addCell(label.get(i));
			}
			
			wb.write();
			wb.close();
			
			
			
			
		}catch (Exception e) {
			System.out.println("엑셀 파일 생성 ERR : " + e.getMessage());
		}
		
	}

}

 

MainClass에서 실행시

package com.bjy;

import java.util.ArrayList;
import java.util.Scanner;

import com.bjy.data.DataClass;
import com.bjy.out.PrintClass;
import com.bjy.util.MethodClass;
import com.bjy.util.ExcelReaderClass;
import com.bjy.util.ExcelWriterClass;

public class MainClass {

	public static void main(String[] args) {
		DataClass.translationData();
		String uri_excel = "c:/filetest/bank_data.xls";
		
		ExcelReaderClass.loadExcel(uri_excel);
		
		Scanner scan = new Scanner(System.in);
		System.out.println("첫번째 검색어를 입력해주세요.");
		System.out.println("결혼 여부 : 미혼, 기혼, 이혼");
		
		String search1 = scan.next();
		System.out.println("집소유 : 없음, 소유, 모름");
		System.out.println("==================");
		
		String search2 = scan.next();
	    scan.close();
		
	    /**1. 검색 : search1, search2 DataClass의 customerData로 부터 **/
	    // 검색 결과 : ArratList<Integer>
	    MethodClass method = new MethodClass();
	    ArrayList<Integer> resultIndex = method.searchInfo(search1, search2);
	    //System.out.println(resultIndex.size());
	    
	    
	    /**2. 태그 생성 : 검색 결과를 이용해서~ **/
	    String tags = method.makeTags(resultIndex);
	    //System.out.println(tags);
	    
	    /**3. HTML 파일 생성 : 완성된 태그 이용 **/
	    String uri_html = "c:/filetest/bank_data.html";
	    PrintClass.makeHTML(tags, uri_html);
	    
	    /**4. 엑셀 파일 생성 : 검색된 결과 이용 **/
	    String uri_xls = "c:/filetest/bank_result_data.xls";
	    ExcelWriterClass.writeExcel(uri_xls, resultIndex);
	}

}

이혼, 소유로 검색한 결과

bank_data.html
bank_result_data.xls

 

배운 것 및 느낀 점

영어를 한글로 변환하는 부분에서 막혀 데이터 처리 방법을 어떻게 설계해야할지 몰라서 막혔다. 해설을 들어도 머릿속에 잘 그려지지 않아 몇번씩 그려봤으나 아직은 더 연습이 필요한 것 같다. 그리고 프로젝트 설계시 클래스 및 메서드, 변수에 어떤 역할을 하는지 주석처리해놔야 수정이 편리하고 잘못된 코딩 부분도 쉽게 파악할 수 있다.

그리고 클래스 설계하면 맞게 되었는지 항상 테스트 후 다음 단계로 넘어가는 것이 좋다.

 

'Java' 카테고리의 다른 글

TIL_210330_Collection 인터페이스  (0) 2021.03.30
TIL_210326_데이터 가공 관련 문제  (0) 2021.03.28
TIL_210324_추상클래스, 라이브러리 추가, 엑셀파일 다루기  (0) 2021.03.24
이클립스_자바 라이브러리 추가  (0) 2021.03.24
TIL_210323_상속, 오버라이딩  (0) 2021.03.23
'Java' Related Articles more