使用apache的poi实现导入导出excel

来源：互联网发布：sql case when isnull 编辑：程序博客网时间：2024/06/07 13:50

@Override
public Map<String, Object> parseExcel(String fileName) {

// 1.准备返回的变量
Map<String, Object> resultMap = new HashMap<String, Object>();
String message = "success";
List<Stone> stones = new ArrayList<Stone>();

boolean isE2007 = false; // 判断是否是excel2007格式
if (fileName.endsWith("xlsx")) {
isE2007 = true;
}
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");

// 2.准备workbook
// 同时支持Excel 2003、2007
File excelFile = new File(fileName); // 创建文件对象
Workbook workbook = null;
// 根据文件格式(2003或者2007)来初始化
try {
FileInputStream is = new FileInputStream(excelFile); // 文件流
if (isE2007) {
workbook = new XSSFWorkbook(is);
} else {
workbook = new HSSFWorkbook(is);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}

// 3.遍历集合，组装结果
int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
// 遍历每个Sheet
for (int s = 0; s < sheetCount; s++) {
Sheet sheet = workbook.getSheetAt(s);
int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数
// 遍历每一行
for (int r = 1; r < rowCount; r++) {
Stone stone = new Stone();
Row row = sheet.getRow(r);
int cellCount = row.getPhysicalNumberOfCells(); // 获取总列数
// 遍历每一列
for (int c = 0; c < cellCount; c++) {
Cell cell = row.getCell(c);
int cellType = cell.getCellType();
String cellStringValue = null;
switch (cellType) {
case Cell.CELL_TYPE_STRING: // 文本
cellStringValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC: // 数字、日期
if (DateUtil.isCellDateFormatted(cell)) {
cellStringValue = fmt.format(cell.getDateCellValue()); // 日期型
} else {
cellStringValue = String.valueOf(cell.getNumericCellValue()); // 数字
if (cellStringValue.contains("E")) {
cellStringValue = String.valueOf(new Double(cell.getNumericCellValue()).longValue()); // 数字
}
}
break;
case Cell.CELL_TYPE_BOOLEAN: // 布尔型
cellStringValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK: // 空白
cellStringValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_ERROR: // 错误
cellStringValue = "错误";
break;
case Cell.CELL_TYPE_FORMULA: // 公式
cellStringValue = "错误";
break;
default:
cellStringValue = "错误";
}

if (cellStringValue.equals("错误")) {
message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第[" + (c + 1)
+ "]列解析错误";
resultMap.put("message", message);
return resultMap;
}

cellStringValue = cellStringValue.trim();

switch (c) {
case ConstantsUtil.STONE_EXCEL_COLUMN_STONEID:
try {
new Long(cellStringValue);
} catch (NumberFormatException e) {
message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["
+ (c + 1) + "]列值类型转换异常";
resultMap.put("message", message);
return resultMap;
}
stone.setStoneId(new Long(cellStringValue));
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_SHAPE:
stone.setShape(cellStringValue);
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_CARAT:
try {
new Double(cellStringValue);
} catch (NumberFormatException e) {
message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["
+ (c + 1) + "]列值类型转换异常";
resultMap.put("message", message);
return resultMap;
}
stone.setCarat(new Double(cellStringValue));
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_COLOUR:
stone.setColour(cellStringValue);
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_CLARITY:
stone.setClarity(cellStringValue);
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_CUT:
stone.setCut(cellStringValue);
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_POLISH:
stone.setPolish(cellStringValue);
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_SYM:
stone.setSym(cellStringValue);
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_FLUOR:
stone.setFluor(cellStringValue);
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_DIAMETER:
try {
new Double(cellStringValue);
} catch (NumberFormatException e) {
message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["
+ (c + 1) + "]列值类型转换异常";
resultMap.put("message", message);
return resultMap;
}
stone.setDiameter(new Double(cellStringValue));
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_PLENGTH:
try {
new Double(cellStringValue);
} catch (NumberFormatException e) {
message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["
+ (c + 1) + "]列值类型转换异常";
resultMap.put("message", message);
return resultMap;
}
stone.setpLength(new Double(cellStringValue));
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_PWEIGHT:
try {
new Double(cellStringValue);
} catch (NumberFormatException e) {
message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["
+ (c + 1) + "]列值类型转换异常";
resultMap.put("message", message);
return resultMap;
}
stone.setpWeight(new Double(cellStringValue));
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_PDEPTH:
try {
new Double(cellStringValue);
} catch (NumberFormatException e) {
message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["
+ (c + 1) + "]列值类型转换异常";
resultMap.put("message", message);
return resultMap;
}
stone.setpDepth(new Double(cellStringValue));
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_PDEPTHPER:
try {
new Double(cellStringValue);
} catch (NumberFormatException e) {
message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["
+ (c + 1) + "]列值类型转换异常";
resultMap.put("message", message);
return resultMap;
}
stone.setpDepthPer(new Double(cellStringValue));
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_PTABLE:
try {
new Double(cellStringValue);
} catch (NumberFormatException e) {
message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["
+ (c + 1) + "]列值类型转换异常";
resultMap.put("message", message);
return resultMap;
}
stone.setpTable(new Double(cellStringValue));
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_LAB:
stone.setLab(cellStringValue);
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_CERTIID:
stone.setCertiId(cellStringValue);
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_STOCKIN:
Integer stockIn = 0;
if ("是".equals(cellStringValue)) {
stockIn = 1;
}
stone.setStockIn(stockIn);
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_STOCKCITY:
stone.setStockCity(cellStringValue);
break;
case ConstantsUtil.STONE_EXCEL_COLUMN_SINGLEPRICE:
try {
new BigDecimal(cellStringValue);
} catch (NumberFormatException e) {
message = "解析Excel时发生错误，第[" + (s + 1) + "]sheet，第[" + (row.getRowNum() + 1) + "]行，第["
+ (c + 1) + "]列值类型转换异常";
resultMap.put("message", message);
return resultMap;
}
stone.setSinglePrice(new BigDecimal(cellStringValue));
break;
default:
message = "解析Excel时发生错误，第[" + (row.getRowNum() + 1) + "]行，第[" + (c + 1) + "]列不应该有值";
resultMap.put("message", message);
return resultMap;
}
}
stone.setIsOnSale(false);
stone.setIsDelete(false);
stones.add(stone);
}
}
resultMap.put("message", message);
resultMap.put("stones", stones);
return resultMap;

}

http://www.cnblogs.com/mabaishui/p/5826864.html

阅读全文

0 0