시소당
이하의 두 패키지는 MicroSoft의 엑셀포맷문서를 처리하기 위한 프로그램이다.
Standard CSV 문서의 경우 오류를 발생한다.Jexcel
http://www.andykhan.comhttp://www.andykhan.com/jexcelapi/tutorial.html
/**
* Description of the Class
*
*@author
*@created 2002/ 10/ 17
*/
import java.io.File; import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
public class ExcelReadSecond {
/**
* The main program for the ExcelRead class
*
*@param args The command line arguments
*@exception Exception Description of the Exception
*/
public static void main(String[] args) throws Exception {
// 쓰기 테스트
// WritableWorkbook workbook = Workbook.createWorkbook(new File(file_path+"/output.xls"));
// WritableSheet sheet = workbook.createSheet("First Sheet", 0);
//
// Label label = new Label(0,0, "001");
// sheet.addCell(label);
//
// jxl.write.Number number = new jxl.write.Number(3, 4, 3.1459);
// sheet.addCell(number);
// workbook.write();
// workbook.close();
// 읽기 테스트
Workbook workbook = Workbook.getWorkbook(new File("c:/myfile.xls"));
Sheet sheet = workbook.getSheet(0); Cell a1 = sheet.getCell(0,0);
Cell arrayCell[] = new Cell[50];
for(int i=0; i<arrayCell.length; i++) {
arrayCell[i] = sheet.getCell(i,0);
}
//Cell b2 = sheet.getCell(1,1);
//Cell c2 = sheet.getCell(2,1); String stringa1 = a1.getContents();
String arrayStr[] = new String[arrayCell.length];
for(int i=0; i<arrayStr.length; i++) {
arrayStr[i] = arrayCell[i].getContents();
System.out.println(arrayStr[i]);
}
workbook.close();
System.out.println(stringa1);
}
} 소스출처 :
http://www.okjsp.pe.kr/seq/25170 POIhttp://www.javaservice.net/~java/bbs/index.cgi?m=devtip&b=poihttp://jakarta.apache.org/poi/index.html
/**
* Description of the Class
*
*@author 김진영
*@created 2002년 10월 17일 (목)
*/
import java.io.*;
import java.util.*;
import java.text.SimpleDateFormat;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.*;
import org.apache.poi.hssf.util.Region;
public class ExcelRead {
private FileInputStream fin;
private POIFSFileSystem fs;
private HSSFWorkbook workbook;
private HSSFSheet sheet;
private Region region;
private int startRow = 0;
private int lastRow = 0;
/**
* 생성자
*
*@param source Description of the Parameter
*@exception FileNotFoundException Description of the Exception
*@exception IOException Description of the Exception
*/
public ExcelRead(String source) throws FileNotFoundException, IOException {
fin = new FileInputStream(source);
fs = new POIFSFileSystem(fin);
// 읽어들인 Excel파일을 다루기 위한 workbook instance를 생성시킨다.
workbook = new HSSFWorkbook(fs);
}
/**
* 시작열을 지정한다.
*/
public void setStartRow(int startRow) {
this.startRow = startRow - 1;
}
/**
* 시작열을 리턴한다.
*/
public int getStartRow() {
return startRow;
}
/**
* Merged Cell 여부를 검사한다.
*
*@param row Description of the Parameter
*@param column Description of the Parameter
*@return boolean The mergedRegions value
*/
private boolean isMergedRegions(int row, int column) {
int numRegions = 0;
boolean result = false;
try {
numRegions = sheet.getNumMergedRegions();
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
Region region = sheet.getMergedRegionAt(i);
if (region.contains(row, (short) column)) {
result = true;
break;
}
}
} catch (Exception e) {}
return result;
}
/**
* 엑셀파일을 읽은 결과를 2차원 배열로 리턴한다.
*
*@return String[][]
*/
public String[][] read() throws Exception { // 생성된 workbook으로부터 sheet를 읽어 들인다.
sheet = workbook.getSheetAt(0);
lastRow = sheet.getLastRowNum();
startRow = getStartRow();
String[][] result = new String[lastRow -startRow+1][];
for (int i = startRow; i <= lastRow; i++) {
HSSFRow row = sheet.getRow(i);
result[i-startRow] = new String[row.getLastCellNum()];
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
HSSFCell cell = row.getCell((short) j);
//cell.setEncoding(HSSFCell.ENCODING_UTF_16); if (cell == null) {
break;
}
int cellType = cell.getCellType();
// cell 타입에 따른 cell의 값을 출력시킨다
switch (cellType) {
case HSSFCell.CELL_TYPE_NUMERIC: // 0
double d = cell.getNumericCellValue();
// 데이트 타입여부를 체크한다.
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// format in form of YYYYMMDD
SimpleDateFormat formatter =
new SimpleDateFormat("yyyyMMdd", java.util.Locale.KOREA);
String cellText = formatter.format(HSSFDateUtil.getJavaDate(d));
result[i-startRow][j] = cellText;
} else {
result[i-startRow][j] = String.valueOf(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING: // 1
result[i-startRow][j] = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_FORMULA: // 2
//System.out.print( "Formula" );
break;
case HSSFCell.CELL_TYPE_BLANK: // 3
if( i-startRow >0 && isMergedRegions(i,j)) {
result[i-startRow][j] = result[i-startRow-1][j];
} else {
result[i-startRow][j] = "";
}
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // 4
result[i-startRow][j] = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR: // 5
result[i-startRow][j] = String.valueOf(cell.getErrorCellValue());
break;
default:
break;
}
}
}
return result;
}
/**
* The main program for the ExcelRead class
*
*@param args The command line arguments
*@exception Exception Description of the Exception
*/
public static void main(String[] args) throws Exception {
String[][] result =null;
try {
ExcelRead excel = new ExcelRead(args[0]);
excel.setStartRow(Integer.parseInt(args[1]));
result = excel.read();
} catch(FileNotFoundException e) {
System.out.println("파일을 찾을수 없습니다.");
return;
} catch(IOException ex) {
System.out.println("파일을 읽는데 실패했습니다.");
return;
}
for (int i = 0; i < result.length; i++) {
for (int j = 0; j < result[i].length; j++) {
System.out.print(result[i][j] + "|");
}
System.out.println("");
}
}
}
소스 출처 :
http://www.javaservice.net/~java/bbs/read.cgi?m=devtip&b=poi&c=r_p&n=1035471131&k=Re:
엑셀파일 읽기 예제&d=tb#1035471131