Java常用工具类-excel 表格处理工具类

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 }