java excel paser example
<!-- 엑셀 제어(xls) maven setting -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.12</version>
</dependency>
<!-- 엑셀 제어(xlsx) maven setting-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 ExcelConverter {
private readXlsxExcelDataListener callBack = null;
public interface readXlsxExcelDataListener{
void readExcel(String tableName, ArrayList<String> columnNames, ArrayList<ArrayList<String>> dataArray);
}
public void setReadXlsxExcelDataListener(readXlsxExcelDataListener callBack){
this.callBack = callBack;
}
//xlsx 읽기
//엑셀의 0번라인은 테이블 이름입니다.
//엑셀의 1번라인은 테이블 퀄럼명들이 있어야합니다.
//엑셀의 2번라인부터 데이터가 있어야합니다.
public void readXlsxExcelData(InputStream io){
String tableName = "";
ArrayList<String> columnNames = new ArrayList<String>();
ArrayList<String> resultRowsData = null;
ArrayList<ArrayList<String>> resultDataArr = new ArrayList<ArrayList<String>>();
int rowindex = 0;
int columnindex = 0;
try{
FileInputStream fis = (FileInputStream) io;
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet = workbook.getSheetAt(0);
int rows = sheet.getPhysicalNumberOfRows();
for(rowindex = 0; rowindex<rows; rowindex++){
XSSFRow row = sheet.getRow(rowindex);
resultRowsData = new ArrayList<String>();
if(row != null){
//셀의 수
int cells = row.getPhysicalNumberOfCells();
for(columnindex = 0; columnindex<cells;columnindex++){
XSSFCell cell = row.getCell(columnindex);
if(cell == null){
continue;
}
else{
//타입별로 내용읽기
switch (cell.getCellType()){
case XSSFCell.CELL_TYPE_STRING:
if(rowindex == 0){ // tablename
tableName = cell.getStringCellValue();
}
else if(rowindex == 1){ // 컬럼 네임
columnNames.add(cell.getStringCellValue());
}
else if(rowindex > 1){//데이터들 쌓는곳
resultRowsData.add(cell.getStringCellValue());
}
break;
case XSSFCell.CELL_TYPE_BLANK:
if(rowindex > 1){//데이터가 없어도 디비에 인서트할때 위치를 알아야하기때문에 값이 없어도 넣어줬다
resultRowsData.add("null");
}
break;
}
}
}
if(rowindex > 1){
resultDataArr.add(resultRowsData);
}
}
}
fis.close();
if(callBack != null){//엑셀 데이터를 다읽고나서 콜백으로 넘겨준다
callBack.readExcel(tableName, columnNames, resultDataArr);
}
}catch(Exception e){
e.printStackTrace();
}finally{
}
//System.out.println("호출종료");
}
//xlsx 쓰기
public void writeXlsxExcelData(){
try{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("testSheet");
XSSFRow row = null;
XSSFCell cell = null;
for(int i = 0; i < 10 ; i++){
//시트에 하나의 행을 생성한다 (i 값이 0 이면 첫번째)
row = sheet.createRow(i);
for(int z = 0; z<5;z++){
//생성된 row에 컬럼을 생성한다
cell = row.createCell(z);
cell.setCellValue("testvalue" + i);
}
}
FileOutputStream fileoutputstream = new FileOutputStream("/Users/achee7059/Desktop/testData2.xlsx");
workbook.write(fileoutputstream);
fileoutputstream.close();
System.out.println("엑셀파일 생성 성공");
}catch(Exception e){
e.printStackTrace();
}
}
//xls 읽기
public void readXlsExcelData(InputStream io){
String tableName = "";
ArrayList<String> columnNames = new ArrayList<String>();
ArrayList<String> resultRowsData = null;
ArrayList<ArrayList<String>> resultDataArr = new ArrayList<ArrayList<String>>();
int rowindex = 0;
int columnindex = 0;
try{
HSSFWorkbook workbook = new HSSFWorkbook(io);
HSSFSheet sheet = workbook.getSheetAt(0);
int rows = sheet.getPhysicalNumberOfRows();
for(rowindex = 0; rowindex<rows; rowindex++){
HSSFRow row = sheet.getRow(rowindex);
resultRowsData = new ArrayList<String>();
if(row != null){
//셀의 수
int cells = row.getPhysicalNumberOfCells();
for(columnindex = 0; columnindex<cells;columnindex++){
HSSFCell cell = row.getCell(columnindex);
if(cell == null){
continue;
}
else{
//타입별로 내용읽기
switch (cell.getCellType()){
case HSSFCell.CELL_TYPE_STRING:
if(rowindex == 0){ // tablename
tableName = cell.getStringCellValue();
}
else if(rowindex == 1){ // 컬럼 네임
columnNames.add(cell.getStringCellValue());
}
else if(rowindex > 1){//데이터들 쌓는곳
resultRowsData.add(cell.getStringCellValue());
}
break;
case HSSFCell.CELL_TYPE_BLANK:
if(rowindex > 1){//데이터가 없어도 디비에 인서트할때 위치를 알아야하기때문에 값이 없어도 넣어줬다
resultRowsData.add("null");
}
break;
}
}
}
if(rowindex > 1){
resultDataArr.add(resultRowsData);
}
}
}
workbook.close();
if(callBack != null){//엑셀 데이터를 다읽고나서 콜백으로 넘겨준다
callBack.readExcel(tableName, columnNames, resultDataArr);
}
}catch(Exception e){
e.printStackTrace();
}finally{
}
}
//xls 쓰기
public void writeXlsExcelData(){
try{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("testSheet");
HSSFRow row = null;
HSSFCell cell = null;
for(int i = 0; i < 10 ; i++){
//시트에 하나의 행을 생성한다 (i 값이 0 이면 첫번째)
row = sheet.createRow(i);
for(int z = 0; z<5;z++){
//생성된 row에 컬럼을 생성한다
cell = row.createCell(z);
cell.setCellValue("testvalue" + i);
}
}
FileOutputStream fileoutputstream = new FileOutputStream("/Users/achee7059/Desktop/testData3.xls");
workbook.write(fileoutputstream);
fileoutputstream.close();
workbook.close();
System.out.println("엑셀파일 생성 성공");
}catch(Exception e){
e.printStackTrace();
}
}
}