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

excel 表格处理工具类

 

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelImportUtil {
    private static final String semicolon = ";";

    /**
     * 解析表单
     * 
     * @param sheet
     * @return
     */
    public static List<String> readExcel(Sheet sheet) {
        int rowCount = sheet.getPhysicalNumberOfRows();
        List<String> rList = new ArrayList<String>();
        for (int i = 0; i < rowCount; i++) {
            rList.add(getCellDataXls(sheet.getRow(i).getCell(0)));
        }
        return rList;
    }

    /**
     *
     * 解析表单
     * @param sheet
     * @return
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public static List<Map<String, String>> readExcelForListMap(Sheet sheet,String[] keys) {
        int rowCount = sheet.getPhysicalNumberOfRows();
        List<Map<String, String>> rList = new ArrayList<Map<String, String>>();
        Map m;
        for (int i = 1; i < rowCount; i++) {
            Row row = sheet.getRow(i);
            m = new HashMap();
            for(int j=0;j<keys.length;j++) {
                m.put(keys[j],getCellDataXls(row.getCell(j)));
            }
            rList.add(m);

        }
        return rList;
    }


    /**
     * 判断单元格内容格式，获取单元格内容
     * 
     * @param cell
     * @return
     */
    public static String getCellDataXls(Cell cell) {
        String cellData = "";
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            cellData = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            // 时间格式
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                cellData = cell.getDateCellValue().toString();
            } else {
                BigDecimal big = new BigDecimal(cell.getNumericCellValue());
                String value = big.toString();
                // 解决1234.0 去掉后面的.0
                if (null != value && !"".equals(value.trim())) {
                    String[] item = value.split("[.]");
                    if (1 < item.length && "0".equals(item[1])) {
                        value = item[0];
                    }
                }
                cellData = value;
            }
            break;
        default:
            cellData = "";
            break;
        }
        cellData = removalSpecialCharacters(cellData);
        return cellData;
    }

    /**
     * 获取合并值
     * 
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRowTd(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    if (row == firstRow && column == firstColumn) {
                        String valueString = getCellDataXls(sheet.getRow(firstRow).getCell(firstColumn));
                        int rowRange = lastRow - firstRow;
                        if (rowRange > 0) {
                            rowRange += 1;
                            valueString += "@rowspan:" + rowRange;
                        }
                        int columnRange = lastColumn - firstColumn;
                        if (columnRange > 0) {
                            columnRange += 1;
                            valueString += "@colspan:" + columnRange;
                        }
                        return valueString + semicolon;
                    } else {
                        return "";
                    }
                }
            }
        }
        return getCellDataXls(sheet.getRow(row).getCell(column)) + semicolon;
    }

    /**
     * ava去除字符串中的空格、回车、换行符、制表符
     * 
     * @param
     * @param
     * @param
     * @return
     */
    public static String removalSpecialCharacters(String str) {
        String returnStr = str;
        if (str != null) {
            while (returnStr.length() > 0 && (returnStr.substring(0, 1).equals(" ") || returnStr.substring(returnStr.length() - 1, returnStr.length()).equals(" "))) {
                returnStr.substring(0, 1);
                returnStr.substring(returnStr.length() - 1, returnStr.length());
                // 首部有空格
                if (returnStr.substring(0, 1).equals(" ")) {
                    returnStr = returnStr.substring(1, returnStr.length());
                }
                // 尾部有空格
                if (returnStr.substring(returnStr.length() - 1, returnStr.length()).equals(" ")) {
                    returnStr = returnStr.substring(0, returnStr.length() - 1);
                }
            }
            // 去除字符串中的回车、换行符、制表符
            Pattern p = Pattern.compile("\t|\r|\n");
            Matcher m = p.matcher(returnStr);
            returnStr = m.replaceAll("");
        }
        return returnStr;
    }

    /**
     * 获取合并值-第二种格式
     * 
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRow(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return getCellDataXls(sheet.getRow(firstRow).getCell(firstColumn));
                }
            }
        }
        return getCellDataXls(sheet.getRow(row).getCell(column));
    }
}