POI
Apache POI - the Java API for Microsoft Documents
Hello World
- jar
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<verison>${poi.version}</verison>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<verison>${poi-ooxml.version}</verison>
</dependency>
- get first sheet
/**
* 獲取Excel第一個Sheet
* @param file excel檔案
* @param fileSuffix excel型別 xls/xlsx
*/
public static Sheet getFirstSheet(File file, String fileSuffix) throws IOException {
InputStream stream = new FileInputStream(file);
Workbook wb = null;
if (fileSuffix.equals("xls")) {
wb = new HSSFWorkbook(stream);
} else if (fileSuffix.equals("xlsx")) {
wb = new XSSFWorkbook(stream);
}
return wb.getSheetAt(0);
}
- get cell value
/**
* 根據列型別,獲得對應的String型別
* @return 不存在/不支援的型別,則返回""
*/
public static String getCellValueStr(Cell cell, String dateFormatStr) {
String cellValueStr = "";
if (null != cell) {
Object cellValue = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValueStr = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
cellValue= cell.getDateCellValue();
SimpleDateFormat formatter = new SimpleDateFormat(dateFormatStr);
cellValueStr = formatter.format(cellValue);
} else {
cellValue=cell.getNumericCellValue();
cellValueStr = String.valueOf(cellValue);
}
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = cell.getBooleanCellValue();
cellValueStr = String.valueOf(cellValue);
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = cell.getCellFormula();
cellValueStr = String.valueOf(cellValue);
break;
default:
System.out.println("不支援的excel單元格型別");
}
}
return cellValueStr;
}
- get excel content --> CSV
/**
* 獲取Excel工作區的檔案內容 - 字串形式
* - 需要置換excel每列的資料(除了每行的結束)以外所有換行符 "\n"
* - 所有CEll都視為String型別
*/
public static String getSheetContent(Sheet sheet, String charset) throws UnsupportedEncodingException {
StringBuffer stringBuffer = new StringBuffer();
String dateTimeFormatStr = "yyyy-MM-dd HH:mm:ss";
String lineSeparator = System.getProperty("line.separator", "\n"); //換行符
for(Row row : sheet) {
for(Cell cell : row) {
cell.setCellType(Cell.CELL_TYPE_STRING); //全部以String型別讀取
String cellStr = new String(getCellValueStr(cell, dateTimeFormatStr).getBytes(), charset);
String trimCellStr = cellStr.replaceAll(lineSeparator, StringUtils.EMPTY);
stringBuffer.append(trimCellStr).append(",");
}
//此行有內容
if(row.getFirstCellNum() != CommonConstant.INVALID_NUMBER) {
stringBuffer.deleteCharAt(stringBuffer.lastIndexOf(",")); //最後一個“,”
stringBuffer.append(lineSeparator);
}
}
return stringBuffer.toString();
}
本文由部落格一文多發平臺 OpenWrite 釋出!