摘要:JAVA + POI(EXCEL)
=================================匯出excel====================================================
public main(){
HSSFWorkbook wb = new HSSFWorkbook();
createSheet1(wb); //6.臨員勞保總表
OutputStream fos = response.getOutputStream();
//FileOutputStream fos = new FileOutputStream(filenm);
wb.write(fos);
fos.flush();
fos.close();
}
void createSheet1(HSSFWorkbook wb) {
HSSFSheet sheet = wb.createSheet(empClassDesc+thisMm+"明細表");
sheet.createFreezePane(0, 1, 0, 1); //凍結第一行
HSSFRow titlerow = sheet.createRow(0);
HSSFCell cell = titlerow.createCell(0);
cell.setCellValue();
cell.setCellStyle(null);
}
=====讀excel===============================================
// 處理上傳內容 Function 1:
POIFSFileSystem fs = new POIFSFileSystem(new ByteArrayInputStream(getQIC954Data().getUploadFile1()));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = getWorkSheet(wb);
int start = sheet.getFirstRowNum();
int rowNum = sheet.getLastRowNum(); //total line
try {
for (int i = start + 2; i <= rowNum; i++) { // read from third line
String[] excelStr = getExcelRowAsStrArr(sheet.getRow(i));
if (!"".equals(ObjectUtils.toString(excelStr[0])) )
{
log.debug("empid===="+ excelStr[0]);//公保員編
}
}
}catch (Exception e) {
}
//設定每列N個欄位與儲存格格式 Function 2
public String[] getExcelRowAsStrArr(HSSFRow row) {
int colCount = 31;//設定有幾個欄位
ArrayList<String> rowStr = new ArrayList<String>();
HSSFCell cell;
for (int i = 0; i < colCount; i++) {
cell = row.getCell(i);
String valStr = null;
if (cell != null) {
switch(cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
valStr = cell.getStringCellValue().trim();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
valStr = String.valueOf(
(int) cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
valStr = String.valueOf(cell.getBooleanCellValue());
break;
default:
break;
}
}
if (valStr == null) {
valStr = "";
}
rowStr.add(valStr);
}
return rowStr.toArray(new String[rowStr.size()]);
}
/**
* 取得excel檔案中對應於元件類別的資料
*
* @param wb 工作簿元件
* @return 符合的工作表元件
*/
private HSSFSheet getWorkSheet(final HSSFWorkbook wb) {
HSSFSheet findSheet = getClassWorkSheet(wb);
if (findSheet == null) {
return null;
}
return findSheet;
}
/**
* 取得excel檔案中資料
*
* @param wb 工作簿元件
* @return 符合的工作表元件
*/
public static HSSFSheet getClassWorkSheet(HSSFWorkbook wb) {
int sheetCount = wb.getNumberOfSheets();
HSSFSheet sheet = null;
for (int i = 0; i < sheetCount; i++) {
if (wb.isSheetHidden(i) || wb.isSheetVeryHidden(i)) {
continue;
}
sheet = wb.getSheetAt(i);
break;
}
if (sheet == null) {
throw new RuntimeException("no sheet");
} else {
return sheet;
}
}
