引入的依賴
<!--利用poi 讀取excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
簡(jiǎn)單說(shuō)明
主要有四個(gè)屬性慌盯，Workbook(工作表)峭火，Sheet(表單)横辆，Row(行), Cell(單元格)
需要讀取的文件
讀取的Excel文件
讀取Excel
思路是按照Workbook梢睛，Sheet层皱，Row缕棵，Cell一層一層往下讀取孵班。
首先是初始化Workbook
private Workbook getReadWorkBookType(String filePath) throws BusinessException {
//xls-2003, xlsx-2007
FileInputStream is = null;
try {
is = new FileInputStream(filePath);
if (filePath.toLowerCase().endsWith("xlsx")) {
return new XSSFWorkbook(is);
} else if (filePath.toLowerCase().endsWith("xls")) {
return new HSSFWorkbook(is);
} else {
// 拋出自定義的業(yè)務(wù)異常
throw OnlinePayErrorCode.EXCEL_ANALYZE_ERROR.convertToException("excel格式文件錯(cuò)誤");
}
} catch (IOException e) {
// 拋出自定義的業(yè)務(wù)異常
throw OnlinePayErrorCode.EXCEL_ANALYZE_ERROR.convertToException(e.getMessage());
} finally {
IOUtils.closeQuietly(is);
}
}
關(guān)于版本不同的Excel如何初始化Workbook
java解析獲取Excel中的數(shù)據(jù)－－同時(shí)兼容2003及2007 @藍(lán)緣
解析Excel文件
因?yàn)槲业膃xcel只有一頁(yè)sheet，所以直接讀取第0頁(yè)sheet(workbook.getSheetAt(0))招驴，如果有多個(gè)可以自行順序循環(huán)讀雀莩獭；
并且我只有一列數(shù)據(jù)别厘，所以每次只讀取第0列的數(shù)據(jù)(row.getCell(0))虱饿，如果有多個(gè)可以依次循環(huán)讀取。
把excel文件里的數(shù)據(jù)讀取放入一個(gè)List<String>中
public List<String> readExcel(String sourceFilePath) throws BusinessException {
Workbook workbook = null;
try {
workbook = getReadWorkBookType(sourceFilePath);
List<String> contents = Lists.newArrayList();
//獲取第一個(gè)sheet
Sheet sheet = workbook.getSheetAt(0);
//第0行是表名触趴，忽略郭厌，從第二行開(kāi)始讀取
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
Cell cell = row.getCell(0);
contents.add(getCellStringVal(cell).trim());
}
return contents;
} finally {
IOUtils.closeQuietly(workbook);
}
}
如果excel中的數(shù)據(jù)是數(shù)字，會(huì)發(fā)現(xiàn)java中對(duì)應(yīng)的變成了科學(xué)計(jì)數(shù)法的雕蔽，
所以在獲取值的時(shí)候就要做一些特殊處理折柠，這樣就能保證獲取的值是我想要的值。
private String getCellStringVal(Cell cell) {
CellType cellType = cell.getCellTypeEnum();
switch (cellType) {
case NUMERIC:
return cell.getStringCellValue();
case STRING:
return cell.getStringCellValue();
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
case FORMULA:
return cell.getCellFormula();
case BLANK:
return "";
case ERROR:
return String.valueOf(cell.getErrorCellValue());
default:
return StringUtils.EMPTY;
}
}
寫(xiě)入Excel
我需要實(shí)現(xiàn)的寫(xiě)入格式是
寫(xiě)入的Excel格式
三列分別是租戶id批狐， 門(mén)店id和入件狀態(tài)扇售，前兩列是數(shù)值類型，最后一個(gè)列是字符串類型嚣艇。
按照Workbook承冰，Sheet， Row食零， Cell依次創(chuàng)建困乒，并將數(shù)據(jù)寫(xiě)入cell中
同理先初始化Workbook
private Workbook getWriteWorkBoolType(String filePath) throws BusinessException{
if (filePath.toLowerCase().endsWith("xlsx")) {
return new XSSFWorkbook();
} else if (filePath.toLowerCase().endsWith("xls")) {
return new HSSFWorkbook();
} else {
//拋出自定的業(yè)務(wù)異常
throw OnlinePayErrorCode.EXCEL_ANALYZE_ERROR.convertToException("excel格式文件錯(cuò)誤");
}
}
將數(shù)據(jù)寫(xiě)入到Excel中
public void writeExcel(String targetFilePath, List<? extends GetPoiAccountSettingsFromExcelVO> contents) throws BusinessException {
Workbook workbook = null;
FileOutputStream fos = null;
workbook = getWriteWorkBoolType(targetFilePath);
//創(chuàng)建sheet
Sheet sheet = workbook.createSheet("門(mén)店入件狀態(tài)");
//在sheet第一行寫(xiě)出表單的各個(gè)字段名
Row titleRow = sheet.createRow(0);
titleRow.createCell(0).setCellValue("租戶id");
titleRow.createCell(1).setCellValue("門(mén)店id");
titleRow.createCell(2).setCellValue("入件狀態(tài)");
//每行的單元格一次寫(xiě)入
Integer rowIndex = contents.size();
for (int i = 0; i < rowIndex; i++) {
//第1行作為表格列名，所以從第2行開(kāi)始讀取
Row row = sheet.createRow(i + 1);
Cell cellTenantId = row.createCell(0);
cellTenantId.setCellValue(contents.get(i).getTenantId());
Cell cellPoiId = row.createCell(1);
cellPoiId.setCellValue(contents.get(i).getMerchantId());
Cell cellStatus = row.createCell(2);
cellStatus.setCellValue(contents.get(i).getMerchantStatus());
}
//寫(xiě)入到文件流中
try {
fos = new FileOutputStream(targetFilePath);
workbook.write(fos);
} catch (IOException e) {
throw OnlinePayErrorCode.EXCEL_ANALYZE_ERROR.convertToException(e.getMessage());
} finally {
IOUtils.closeQuietly(workbook);
}
}
讀取測(cè)試
@Test
public void testReadXls() {
String path = "../crm-onlinepay-web/tenantId.xls";
try {
List<String> tenantIds = excelUtils.readExcel(path);
LOGGER.info("tenantIds:{}", tenantIds);
} catch (Exception e) {
LOGGER.info("exception:", e);
}
}
測(cè)試結(jié)果：
2017-09-12 11:01:41,124 INFO (AccountSettingRemoteServiceTest.java:138) - tenantIds:[10193, 8902, 10191, 10129, 10107, 10105, 10041]
寫(xiě)入測(cè)試
@Test
public void writeXls() {
String path = "../crm-onlinepay-web/PoiStatus.xls";
GetPoiAccountSettingsFromExcelVO excelVO = new GetPoiAccountSettingsFromExcelVO(1, 2, "WAIT");
GetPoiAccountSettingsFromExcelVO excelVO1 = new GetPoiAccountSettingsFromExcelVO(3, 4, "WAIT");
GetPoiAccountSettingsFromExcelVO excelVO2 = new GetPoiAccountSettingsFromExcelVO(5, 6, "WAIT");
GetPoiAccountSettingsFromExcelVO excelVO3 = new GetPoiAccountSettingsFromExcelVO(7, 8, "WAIT");
List<GetPoiAccountSettingsFromExcelVO> excelVOS = Lists.newArrayList(excelVO, excelVO1, excelVO2, excelVO3);
try {
excelUtils.writeExcel(path, excelVOS);
} catch (Exception e) {
LOGGER.info("exception:", e);
}
}
測(cè)試結(jié)果：
測(cè)試結(jié)果
