PoiCellUtil.java
3.35 KB
1
2
3
4
5
6
7
8
9
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
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
package com.bckefu.excel.util;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* @author caoliang1918@aliyun.com
* @Date 2017/11/5:23:47
*/
public class PoiCellUtil {
/**
* 读取单元格的值
*
* @param sheet
* @param row
* @param column
* @return
*/
public static String getCellValue(Sheet sheet, int row, int column) {
String value = null;
if (isMergedRegion(sheet, row, column)) {
value = getMergedRegionValue(sheet, row, column);
} else {
Row rowData = sheet.getRow(row);
Cell cell = rowData.getCell(column);
value = getCellValue(cell);
}
return value;
}
/**
* 获取合并单元格的值
*
* @param sheet
* @param row
* @param column
* @return
*/
public static String getMergedRegionValue(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell);
}
}
}
return null;
}
/**
* 判断指定的单元格是否是合并单元格
*
* @param sheet
* @param row
* @param column
* @return
*/
public static boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
/**
* 获取单元格的值
*
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
if (cell == null) {
return "";
}
if (cell.getCellTypeEnum() == CellType.STRING) {
return cell.getStringCellValue();
} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellTypeEnum() == CellType.FORMULA) {
return cell.getCellFormula();
} else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
} else {
cell.setCellType(CellType.STRING);
return cell.getStringCellValue();
}
}
}