자바 POI라이브러리로 엑셀파일 읽고쓰기

첨부 http://neosm.tistory.com/entry/JAVA-POI-%EC%97%91%EC%85%80%ED%8C%8C%EC%9D%BC-%EC%9D%BD%EA%B8%B0-xlsx-%ED%99%95%EC%9E%A5%EC%9E%90

다운경로 http://poi.apache.org/download.html

binary distribution에 .zip 으로된걸 다운

xls는 2007이전버전

POIFSFileSystem fileSystem = new POIFSFileSystem(new FileInputStream(new File(excelFile)));

SSFWorkbook work = new HSSFWorkbook(fileSystem);

xlsx는 2007이후버전

XSSFWorkbook work = new XSSFWorkbook(new FileInputStream(new File(excelFile)));

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;

public class XlsxRead {

public static void main(String[] args) {

XSSFRow row;

XSSFCell cell;

try {

FileInputStream inputStream = new FileInputStream("XlsxRead.xlsx");

XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

//sheet수 취득

int sheetCn = workbook.getNumberOfSheets();

System.out.println("sheet수 : " + sheetCn);

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

System.out.println("취득하는 sheet 이름 : " + workbook.getSheetName(cn));

System.out.println(workbook.getSheetName(cn) + " sheet 데이터 취득 시작");

//0번째 sheet 정보 취득

XSSFSheet sheet = workbook.getSheetAt(cn);

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

int rows = sheet.getPhysicalNumberOfRows();

System.out.println(workbook.getSheetName(cn) + " sheet의 row수 : " + rows);

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

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

System.out.println(workbook.getSheetName(cn) + " sheet의 row에 취득대상 cell수 : " + cells);

for (int r = 0; r < rows; r++) {

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

if (row != null) {

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

cell = row.getCell(c);

if (cell != null) {

String value = null;

switch (cell.getCellType()) {

case XSSFCell.CELL_TYPE_FORMULA:

value = cell.getCellFormula();

break;

case XSSFCell.CELL_TYPE_NUMERIC:

value = "" + cell.getNumericCellValue();

break;

case XSSFCell.CELL_TYPE_STRING:

value = "" + cell.getStringCellValue();

break;

case XSSFCell.CELL_TYPE_BLANK:

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

break;

case XSSFCell.CELL_TYPE_ERROR:

value = "" + cell.getErrorCellValue();

break;

default:

}

System.out.print(value + "\t");

} else {

System.out.print("[null]\t");

}

} // for(c) 문

System.out.print("\n");

}

} // for(r) 문

}

} catch (Exception e) {

e.printStackTrace();

}

}

}