[JAVA] 엑셀 파일 읽기 (API)

// POI 라이브러리를 반드시 추가하고 진행한다.

ReadData.java

package DAO;

import java.io.FileInputStream;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import DataStructure.BookData;

public class ReadData {   

    

public static void main(String[] args) {

// TODO Auto-generated method stub

String sql;

BookInfoDAO dao = new BookInfoDAO();

XSSFRow row;

XSSFCell cell;

try {

BookData book = new BookData();

FileInputStream inputStream = new FileInputStream("C:\\Users\\user\\eclipse-workspace\\OCP_01\\Library.xlsx");

XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

//sheet수 취득

int sheetCn = workbook.getNumberOfSheets();

dao.getLinkDB().connectDB();

for(int cn = 0; cn < sheetCn; cn++){

XSSFSheet sheet = workbook.getSheetAt(cn);

//취득된 sheet에서 rows수 취득

String value = null;

int rows = sheet.getPhysicalNumberOfRows();

//취득된 row에서 취득대상 cell수 취득

int cells = sheet.getRow(cn).getPhysicalNumberOfCells(); //

int r;

for (r = 1; r < 10; r++) {

row = sheet.getRow(r); // row 가져오기

if (row != null) {

for (int c = 0; c < cells; c++) {

cell = row.getCell(c);

if (cell != null) {

value = null;

switch (cell.getCellType()) {

case FORMULA:

value = cell.getCellFormula();

break;

case NUMERIC:

value = "" + cell.getNumericCellValue();

break;

case STRING:

value = "" + cell.getStringCellValue();

break;

case BLANK:

value = "[null 아닌 공백]";

break;

case ERROR:

value = "" + cell.getErrorCellValue();

break;

default:

}

}

// c는 엑셀의 Column을 의미한다. 

if(c==3) {

System.out.print(value + "------------");

book.setBookName(value);

}else if(c==5) {

System.out.print(value  + "------------");

book.setAuthor(value);

}else if(c==6) {

System.out.println(value);

book.setPublish(value);

}

} 

book.setBookID(String.valueOf(r));

dao.insertBook(book);

}

} 

}

dao.getLinkDB().closeDB();

}catch(Exception e) {

e.printStackTrace();

}

}

}

// DAO 패키지에 BookInfoDAO.java

// db.connectDB()를 하는 이유는 매번 인서트를 하면 메모리 손실 

// 

/*

public LinkDB() {

user = new DBInfo();

}

이 메소드를 호출한다. 

 */

  public boolean insertBook(BookData book) {

        //db.connectDB();

        int result = 0;

        String sql = "insert into book(identifi_number,bookname,author,publish) values (?,?,?,?)";

        try {

            db.pstmt = db.conn.prepareStatement(sql);

            db.pstmt.setString(1, book.getBookID());

            db.pstmt.setString(2, book.getBookName());

            db.pstmt.setString(3, book.getAuthor());

            db.pstmt.setString(4, book.getPublish());

            result = db.pstmt.executeUpdate();

            //db.closeDB();

        } catch (SQLException e) {

            e.printStackTrace();

        }

        if (result > 0)

            return true;

        else

            return false;

    }//insertBook()