java 使用poi导入Excel通用方法

需要的jar:

[XML]纯文本查看__复制代码

?

1

2

3

4

5

<`dependency`>

<`groupId>org.apache.poi</groupId`>

<`artifactId>poi-ooxml</artifactId`>

<`version>4.1.0</version`>

</`dependency`>

java代码:

[Java]纯文本查看__复制代码

?

01

02

03

04

05

06

07

08

09

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

public static String importExcel(String filePath) throws Exception {

//判断文件

if (filePath != null && !`""`.equals(filePath)) {

File file = new File(filePath);

//判断格式

if (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)) {

//创建输入流对象

InputStream is = new FileInputStream(file);

Workbook workbook = null`;`

//判断excel版本号

if (file.getName().endsWith(EXCEL_XLS)) {

workbook = new HSSFWorkbook(is);

} else if (file.getName().endsWith(EXCEL_XLSX)) {

workbook = new XSSFWorkbook(is);

}

Map<String, Object> objectMap=`new HashMap<>();`

//循环表格(sheet)

for (`int i = 0`; i < workbook.getNumberOfSheets(); i++) {

Sheet sheet = workbook.getSheetAt(i);

//判断sheet是否有数据

if (sheet.getPhysicalNumberOfRows() <= 0`) {`

continue`;`

}

//存放集合

List<Map<String, Object>> list = new ArrayList<>();

//存放表头名字(第一行的数据)

List<String> header = new ArrayList<>();

for (`int x = 0; x < sheet.getRow(0`).getLastCellNum(); x++) {

Cell cell = sheet.getRow(`0`).getCell(x);

String value = cell.getStringCellValue();

header.add(value);

}

//获取行并进行循环

for (`int j = 1`; j <= sheet.getLastRowNum(); j++) {

Row row = sheet.getRow(j);

//判断row是否有数据

if (row.getPhysicalNumberOfCells() <= 0`) {`

continue`;`

}

//存放数据

Map<String, Object> map = new HashMap<>();

//获取单元格并进行循环

for (`int k = 0`; k < row.getLastCellNum(); k++) {

Cell cell = row.getCell(k);

if (cell == null || cell.toString().trim().equals(`""`)) {

continue`;`

}

CellType cellType = cell.getCellTypeEnum();

//存放值

String cellValue = ""`;`

//字符串

if (cellType == CellType.STRING) {

cellValue = cell.getStringCellValue().trim();

cellValue = StringUtil.isNullOrEmpty(cellValue) ? "" : cellValue;

}

//数据格式

if (cellType == CellType.NUMERIC) {

//判断日期类型

if (HSSFDateUtil.isCellDateFormatted(cell)) {

SimpleDateFormat dff = new SimpleDateFormat(`"yyyy-MM-dd HH:mm:ss"`);

String date1 = dff.format(cell.getDateCellValue());

cellValue = date1;

} else {

//设置数据格式("#.######"是几位小数)

cellValue = new DecimalFormat(`"#.######"`).format(cell.getNumericCellValue());

}

}

if (cellType == CellType.BOOLEAN) {

cellValue = String.valueOf(cell.getBooleanCellValue());

}

//添加数据到map

map.put(header.get(k), cellValue);

}

//把map数据添加到list

list.add(map);

}

objectMap.put(sheet.getSheetName(),list);

}

return objectMap.toString();

} else {

return "文件不是excel"`;`

}

} else {

return "文件不存在"`;`

}

}

评论

暂无评论