흠.. 3.5 버전으로 작업함. 3.6 부터는 밑에 코드를 사용할 수 없음. 사용할 수 없는 클래스가 존재하는 듯..
참조
오피스 버전에 따라서 사용하는 API가 다름.
: 2007 을 기준으로( ooo.xls, ooo.xlsx)
본 문서는 ooo.xls 버전을 기준으로 작업할거임. 뭐.. 상위 버전이랑 큰 차이는 없는듯
1. workbook.xls 만들기
package com.excel.sample;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public class Launcher {
final static int rowNumber = 100;
public void makeRowHeader(CreationHelper createHelper, Sheet sheet) {
Row row = sheet.createRow(0);
row.createCell(0).setCellValue(createHelper.createRichTextString("번호"));
row.createCell(1).setCellValue(createHelper.createRichTextString("value"));
row.createCell(2).setCellValue(createHelper.createRichTextString("Description(설 명)"));
row.createCell(3).setCellValue(createHelper.createRichTextString("상태 값"));
row.createCell(4).setCellValue(createHelper.createRichTextString("Date - new Date()"));
row.createCell(5).setCellValue(createHelper.createRichTextString("Date - m/d/yy h:mm"));
row.createCell(6).setCellValue(createHelper.createRichTextString("Date - Calendar.getInstance()"));
row.createCell(7).setCellValue(createHelper.createRichTextString("Date - 비고"));
}
public void makeRow(Workbook wb, CreationHelper createHelper, Sheet sheet) {
for(int i = 1;i<rowNumber;i++) {
Row row = sheet.createRow(i);
Cell cell = row.createCell(0);
cell.setCellValue(1);
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue(createHelper.createRichTextString("This is a string"));
row.createCell(3).setCellValue(true);
row.createCell(4).setCellValue(new Date());
// we style the second cell as a date (and time). It is important to
// create a new cell style from the workbook otherwise you can end up
// modifying the built in style and effecting not only this cell but other cells.
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(
createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell = row.createCell(5);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);
//you can also set date as java.util.Calendar
cell = row.createCell(6);
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);
row.createCell(7).setCellType(HSSFCell.CELL_TYPE_ERROR);
}
}
public void makeExcel() throws IOException {
Workbook wb = new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("sheet1");
makeRowHeader(createHelper, sheet);
makeRow(wb, createHelper, sheet);
FileOutputStream fileOut;
fileOut = new FileOutputStream("D:\\Launcher\\excel\\workbook.xls");
wb.write(fileOut);
fileOut.close();
}
public static void main(String[] args) throws IOException {
new Launcher().makeExcel();
}
}
2. 만든넘 읽기.
public void readExcel() throws InvalidFormatException, IOException {
InputStream inp = new FileInputStream("D:\\Launcher\\excel\\workbook.xls");
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
for(Row row : sheet) {
for(Cell cell : row) {
CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
System.out.print(cellRef.formatAsString());
System.out.print(" - ");
switch(cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.println("string : " + cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)) {
System.out.println("date : " + cell.getDateCellValue());
} else {
System.out.println("number : " + cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println("boolean : " + cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println("formula : " + cell.getCellFormula());
break;
default:
System.out.println(cell.toString());
}
}
}
}
3.6이상 부터는 위 코드를 사용할 수 없었음.. 뭐.. 소스를 다운로드 받으면 그 속에 예제가 있기 때문에 그것보고 사용해도 좋을듯.
