excel 表格处理工具类
1 import org.apache.poi.hssf.usermodel.HSSFDateUtil;
2 import org.apache.poi.ss.usermodel.Cell;
3 import org.apache.poi.ss.usermodel.Row;
4 import org.apache.poi.ss.usermodel.Sheet;
5 import org.apache.poi.ss.util.CellRangeAddress;
6
7 import java.math.BigDecimal;
8 import java.util.ArrayList;
9 import java.util.HashMap;
10 import java.util.List;
11 import java.util.Map;
12 import java.util.regex.Matcher;
13 import java.util.regex.Pattern;
14
15 public class ExcelImportUtil {
16 private static final String semicolon = ";";
17
18 /**
19 * 解析表单
20 *
21 * @param sheet
22 * @return
23 */
24 public static List<String> readExcel(Sheet sheet) {
25 int rowCount = sheet.getPhysicalNumberOfRows();
26 List<String> rList = new ArrayList<String>();
27 for (int i = 0; i < rowCount; i++) {
28 rList.add(getCellDataXls(sheet.getRow(i).getCell(0)));
29 }
30 return rList;
31 }
32
33 /**
34 *
35 * 解析表单
36 * @param sheet
37 * @return
38 */
39 @SuppressWarnings({ "rawtypes", "unchecked" })
40 public static List<Map<String, String>> readExcelForListMap(Sheet sheet,String[] keys) {
41 int rowCount = sheet.getPhysicalNumberOfRows();
42 List<Map<String, String>> rList = new ArrayList<Map<String, String>>();
43 Map m;
44 for (int i = 1; i < rowCount; i++) {
45 Row row = sheet.getRow(i);
46 m = new HashMap();
47 for(int j=0;j<keys.length;j++) {
48 m.put(keys[j],getCellDataXls(row.getCell(j)));
49 }
50 rList.add(m);
51
52 }
53 return rList;
54 }
55
56
57 /**
58 * 判断单元格内容格式,获取单元格内容
59 *
60 * @param cell
61 * @return
62 */
63 public static String getCellDataXls(Cell cell) {
64 String cellData = "";
65 if (cell == null) {
66 return "";
67 }
68 switch (cell.getCellType()) {
69 case Cell.CELL_TYPE_STRING:
70 cellData = cell.getStringCellValue();
71 break;
72 case Cell.CELL_TYPE_NUMERIC:
73 // 时间格式
74 if (HSSFDateUtil.isCellDateFormatted(cell)) {
75 cellData = cell.getDateCellValue().toString();
76 } else {
77 BigDecimal big = new BigDecimal(cell.getNumericCellValue());
78 String value = big.toString();
79 // 解决1234.0 去掉后面的.0
80 if (null != value && !"".equals(value.trim())) {
81 String[] item = value.split("[.]");
82 if (1 < item.length && "0".equals(item[1])) {
83 value = item[0];
84 }
85 }
86 cellData = value;
87 }
88 break;
89 default:
90 cellData = "";
91 break;
92 }
93 cellData = removalSpecialCharacters(cellData);
94 return cellData;
95 }
96
97 /**
98 * 获取合并值
99 *
100 * @param sheet
101 * @param row
102 * @param column
103 * @return
104 */
105 public static String getMergedRowTd(Sheet sheet, int row, int column) {
106 int sheetMergeCount = sheet.getNumMergedRegions();
107 for (int i = 0; i < sheetMergeCount; i++) {
108 CellRangeAddress range = sheet.getMergedRegion(i);
109 int firstColumn = range.getFirstColumn();
110 int lastColumn = range.getLastColumn();
111 int firstRow = range.getFirstRow();
112 int lastRow = range.getLastRow();
113 if (row >= firstRow && row <= lastRow) {
114 if (column >= firstColumn && column <= lastColumn) {
115 if (row == firstRow && column == firstColumn) {
116 String valueString = getCellDataXls(sheet.getRow(firstRow).getCell(firstColumn));
117 int rowRange = lastRow - firstRow;
118 if (rowRange > 0) {
119 rowRange += 1;
120 valueString += "@rowspan:" + rowRange;
121 }
122 int columnRange = lastColumn - firstColumn;
123 if (columnRange > 0) {
124 columnRange += 1;
125 valueString += "@colspan:" + columnRange;
126 }
127 return valueString + semicolon;
128 } else {
129 return "";
130 }
131 }
132 }
133 }
134 return getCellDataXls(sheet.getRow(row).getCell(column)) + semicolon;
135 }
136
137 /**
138 * ava去除字符串中的空格、回车、换行符、制表符
139 *
140 * @param
141 * @param
142 * @param
143 * @return
144 */
145 public static String removalSpecialCharacters(String str) {
146 String returnStr = str;
147 if (str != null) {
148 while (returnStr.length() > 0 && (returnStr.substring(0, 1).equals(" ") || returnStr.substring(returnStr.length() - 1, returnStr.length()).equals(" "))) {
149 returnStr.substring(0, 1);
150 returnStr.substring(returnStr.length() - 1, returnStr.length());
151 // 首部有空格
152 if (returnStr.substring(0, 1).equals(" ")) {
153 returnStr = returnStr.substring(1, returnStr.length());
154 }
155 // 尾部有空格
156 if (returnStr.substring(returnStr.length() - 1, returnStr.length()).equals(" ")) {
157 returnStr = returnStr.substring(0, returnStr.length() - 1);
158 }
159 }
160 // 去除字符串中的回车、换行符、制表符
161 Pattern p = Pattern.compile("\t|\r|\n");
162 Matcher m = p.matcher(returnStr);
163 returnStr = m.replaceAll("");
164 }
165 return returnStr;
166 }
167
168 /**
169 * 获取合并值-第二种格式
170 *
171 * @param sheet
172 * @param row
173 * @param column
174 * @return
175 */
176 public static String getMergedRow(Sheet sheet, int row, int column) {
177 int sheetMergeCount = sheet.getNumMergedRegions();
178 for (int i = 0; i < sheetMergeCount; i++) {
179 CellRangeAddress range = sheet.getMergedRegion(i);
180 int firstColumn = range.getFirstColumn();
181 int lastColumn = range.getLastColumn();
182 int firstRow = range.getFirstRow();
183 int lastRow = range.getLastRow();
184 if (row >= firstRow && row <= lastRow) {
185 if (column >= firstColumn && column <= lastColumn) {
186 return getCellDataXls(sheet.getRow(firstRow).getCell(firstColumn));
187 }
188 }
189 }
190 return getCellDataXls(sheet.getRow(row).getCell(column));
191 }
192 }