POI解析Excel封装工具

packagecom.rf.dtd.systemsupport.dataImport.common;

 

importcom.rf.dtd.datacommons.JsonUtil;

importjava.io.File;

importjava.io.FileInputStream;

importjava.io.InputStream;

importjava.math.BigDecimal;

importjava.text.SimpleDateFormat;

importjava.util.ArrayList;

importjava.util.Date;

importjava.util.HashMap;

importjava.util.LinkedHashMap;

importjava.util.List;

importjava.util.Map;

importjava.util.Arrays;

importjava.util.regex.Matcher;

importjava.util.regex.Pattern;

 

importorg.apache.poi.hssf.usermodel.HSSFCellStyle;

importorg.apache.poi.hssf.util.PaneInformation;

importorg.apache.poi.ss.usermodel.Cell;

importorg.apache.poi.ss.usermodel.CellStyle;

importorg.apache.poi.ss.usermodel.DateUtil;

importorg.apache.poi.ss.usermodel.Font;

importorg.apache.poi.ss.usermodel.IndexedColors;

importorg.apache.poi.ss.usermodel.Row;

importorg.apache.poi.ss.usermodel.Sheet;

importorg.apache.poi.ss.usermodel.Workbook;

importorg.apache.poi.ss.usermodel.WorkbookFactory;

importorg.apache.poi.ss.util.CellRangeAddress;

importorg.apache.poi.ss.util.CellReference;

importorg.apache.poi.xssf.usermodel.XSSFCellStyle;

 

/**

 * 主要提供对Excel的各种处理,侧重点是取数据

 *

 * @author 李元元

 *

 */

publicclass POIExcelUtil {

 

    staticSimpleDateFormat sFormat = newSimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    staticshort[] yyyyMMdd = {14,31, 57,58, 179,184, 185,186, 187,188};

    staticshort[] HHmmss = {20,32, 190,191, 192};

    staticList<short[]> yyyyMMddList = Arrays.asList(yyyyMMdd);

    staticList<short[]> hhMMssList = Arrays.asList(HHmmss);

 

    /**

     * 根据路径,获取WorkBook对象

     *

     * @param filePath 文件路径

     * @return workbook

     * @throws Exception

     */

    publicstatic Workbook getExcelWorkbook(String filePath)throws Exception {

        Workbook workbook =null;

        File file =new File(filePath);

        if(file.exists()) {

            workbook = getWorkBookByStream(newFileInputStream(file));

        }

        returnworkbook;

    }

 

    /**

     * 根据输入流ins获取WorkBook对象

     *

     * @param ins 输入流

     * @return workbook

     * @throws Exception

     */

    publicstatic Workbook getWorkBookByStream(InputStream ins)throws Exception {

        returnWorkbookFactory.create(ins);

    }

 

    /**

     * 根据Workbook,sheetIndex获取sheet对象

     *

     * @param book WorkBook对象

     * @param number sheetIndex ,从1开始

     * @return sheet

     * @throws Exception

     */

    publicstatic Sheet getSheetByNum(Workbook book,int number) throws Exception {

        returnbook.getSheetAt(number - 1);

    }

 

    /**

     * 根据 Workbook对象返回该Workbook对象中所有sheet的Map数组.

     *

     * @param book

     * @return Map<sheetIndex , sheetName>

     * @throws Exception

     */

    publicstatic Map<Integer, String> getSheetNameByBook(Workbook book)throws Exception {

        Map<Integer, String> map =new LinkedHashMap<Integer, String>();

        intsheetNum = book.getNumberOfSheets();

        for(int indexSheet = 1; indexSheet <= sheetNum; indexSheet++) {

            Sheet sheet = getSheetByNum(book, indexSheet);

            map.put(indexSheet, sheet.getSheetName());

        }

        returnmap;

    }

 

    /**

     * 获取workbook数据Map集合

     *

     * @param book

     * @return Map<Integer, List<List<String>>> @

     * throws Exception

     */

    publicstatic Map<Integer, List<List<String>>> getWorkbookDatas(Workbook book)throws Exception {

        Map<Integer, List<List<String>>> bookdatas =new HashMap<Integer, List<List<String>>>();

        intsheetNum = book.getNumberOfSheets();

        for(int indexSheet = 1; indexSheet <= sheetNum; indexSheet++) {

            Sheet sheet = getSheetByNum(book, indexSheet);

            bookdatas.put(indexSheet, getSheetDataList(sheet));

        }

        returnbookdatas;

    }

 

    /**

     * 获取sheet中的数据

     *

     * @param sheet

     * @return List<List<String>> @

     * throws Exception

     */

    publicstatic List<List<String>> getSheetDataList(Sheet sheet)throws Exception {

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

        //需要先合并单元格数据

        mergedRegion(sheet);

        intlastRowNum = getRowNum(sheet);

        intlastCellNum = getColumnNum(sheet);

        for(int i = 0; i < lastRowNum; i++) {

            Row row = sheet.getRow(i);

            sheetdatas.add(getRowDataList(sheet, row, lastCellNum));

        }

        returnsheetdatas;

    }

 

    /**

     * 获取的数据对象是符合easyui格式的标准JSON对象数据集[{A:x,B:xx,C:xxx},{A:x,B:xx,C:xxx}]

     *

     * @param sheet

     * @return

     */

    publicstatic List<Map<String, String>> getSheetDataMap(Sheet sheet) {

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

        intlastRowNum = getRowNum(sheet);

        Row row;

        for(int i = 0; i < lastRowNum; i++) {

            row = sheet.getRow(i);

            Map<String, String> map = getRowDataMap(sheet, row);

            if(!map.isEmpty()) {

                sheetdatas.add(map);

            }

        }

        returnsheetdatas;

    }

 

    /**

     * 获取的数据对象是符合dHtml格式的非标准JSON对象数据集[{id:1 , data:[x,xx,xxx]},{id:2

     * ,data:[x,xx,xxx]}]

     *

     * @param sheet

     * @return

     */

    publicstatic List<Map<String, Object>> getSheetDataMapAndId(Sheet sheet)throws Exception {

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

        List<List<String>> sheetLists = getSheetDataList(sheet);

        for(int i = 0; i < sheetLists.size(); i++) {

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

            dataMap.put("id", i);

            dataMap.put("data", sheetLists.get(i));

            sheetdatas.add(dataMap);

        }

        returnsheetdatas;

    }

 

    /**

     * 读取一行的数据,返回的是数据集合List,[x,xx,xxx]

     *

     * @param row

     */

    publicstatic List<String> getRowDataList(Sheet sheet, Row row,int lastCellNum) {

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

        if(row == null) {

            for(int i = 0; i < lastCellNum; i++) {

                rowdatas.add("");

            }

        }else {

            for(int i = 0; i < lastCellNum; i++) {

                rowdatas.add(getCellData(row.getCell(i)));

            }

        }

        returnrowdatas;

    }

 

    /**

     * 获取一行的数据集合,体现的是Map<String , String>{A:x,B:xx,C:xxx}

     *

     * @param row

     * @return

     */

    publicstatic Map<String, String> getRowDataMap(Sheet sheet, Row row) {

        Map<String, String> rowdatas =new LinkedHashMap<String, String>();

        String cellVaue;

        intcolumnNum = 0;

        intlastCellNum = getColumnNum(sheet);

        for(int j = 0; j < lastCellNum; j++) {

            cellVaue = getCellData(row.getCell(j));

            rowdatas.put(getCharByNum(columnNum) +"", cellVaue);

            columnNum = columnNum +1;

        }

        returnrowdatas;

    }

 

    /**

     * 获取指定Sheet中指定一列的数据.

     *

     * @param sheet 指定的Sheet

     * @param colIndex 指定的列

     * @return

     * @throws Exception

     */

    publicstatic List<String> getColumnDataList(Sheet sheet,int colIndex) throws Exception {

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

        intlastRowNum = getRowNum(sheet);

        for(int i = 0; i < lastRowNum; i++) {

            coldatas.add(getSheetCellValueWithRowIndexAndColIndex(sheet, i, colIndex));

        }

        returncoldatas;

    }

 

    /**

     * 返回指定sheet页的最大行数,例如:25,则表示下标从0...24

     *

     * @param book

     * @param sheetIndex

     * @return

     */

    publicstatic int getRowNum(Sheet sheet) {

        returnsheet.getLastRowNum() + 1;

    }

 

    /**

     * 返回指定sheet页的最大列数,例如:25,则表示下标从0...24

     *

     * @param book

     * @param sheetIndex

     * @return 列数

     */

    publicstatic int getColumnNum(Sheet sheet) {

        intmaxclNum = 0;

        intlastRowNum = getRowNum(sheet);

        for(int i = 0; i < lastRowNum; i++) {

            if(sheet.getRow(i) != null) {

                inttempNum = sheet.getRow(i).getLastCellNum();

                if(tempNum > maxclNum) {

                    maxclNum = tempNum;

                }

            }

        }

        returnmaxclNum;

    }

 

    /**

     * 获取单元格的名称 按照excel常见的名称 例如A1

     *

     * @param int rowInt 行数 从0开始

     * @param int columnInt 列数 从0开始

     * @return String

     */

    publicstatic String getCellName(introwInt, int columnInt) {

        CellReference cellReference =new CellReference(rowInt, columnInt);

        returncellReference.formatAsString();

    }

 

    /**

     * 获取指定单元格的行坐标

     *

     * @param cellName 例如A1

     * @return 2

     */

    publicstatic int getCellRowIndex(String cellName) {

        CellReference cellReference =new CellReference(cellName);

        returncellReference.getRow();

    }

 

    /**

     * 获取指定单元格的列坐标

     *

     * @param cellName 例如A1

     * @return 0

     */

    publicstatic int getCellColIndex(String cellName) {

        CellReference cellReference =new CellReference(cellName);

        returncellReference.getCol();

    }

 

    /**

     * 获取指定sheet中指定rowNum和cellNum的值

     *

     * @param sheet

     * @param rowNum

     * @param cellNum

     * @return

     * @throws Exception

     */

    publicstatic String getSheetCellValueWithRowIndexAndColIndex(Sheet sheet,int rowNum, int cellNum) throwsException {

        Row row = sheet.getRow(rowNum);

        Cell cell = row.getCell(cellNum);

        returngetCellData(cell);

    }

 

    /**

     * 获取给定SHEET中指定单元格的值

     *

     * @param sheet 指定SHEET

     * @param cellName 格式为：A1,B3

     * @return

     */

    publicstatic String getSheetCellValueWithCellName(Sheet sheet, String cellName) {

        CellReference cellReference =new CellReference(cellName);

        Row row = sheet.getRow(cellReference.getRow());

        Cell cell = row.getCell(cellReference.getCol());

        returngetCellData(cell);

    }

 

    /**

     * 获得cell单元格的TypeNumber,范围是0~5

     *

     * @param cell

     * @return

     */

    publicstatic int getColumnTypeNumber(Cell cell) {

        if(cell != null) {

            inttype = cell.getCellType();

            returntype;

        }

        return-1;

    }

 

    /**

     * 获取指定Sheet页 所有合并单元格数据信息

     *

     * @param sheet

     * @return List<Map<String, String>>

     */

    publicstatic List<Map<String, String>> getSheetRegion(Sheet sheet) {

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

        //合并的单元格数量

        intmerged = sheet.getNumMergedRegions();

        //预读合并的单元格

        for(int i = 0; i < merged; i++) {

            CellRangeAddress range = sheet.getMergedRegion(i);

            Map<String, String> map =new LinkedHashMap<String, String>();

            intcolstart = range.getFirstColumn();

            intcolend = range.getLastColumn();

            introwstart = range.getFirstRow();

            introwend = range.getLastRow();

            map.put("colstart", colstart +"");

            map.put("colend", colend +"");

            map.put("rowstart", rowstart +"");

            map.put("rowend", rowend +"");

            map.put("field", getCharByNum(colstart));

            map.put("colspan", (colend - colstart +1) + "");

            map.put("rowspan", (rowend - rowstart +1) + "");

            map.put("index", rowstart +"");

            list.add(map);

        }

        returnlist;

    }

 

    /**

     * 获取sheet中指定column的列宽度,这里的宽度是近似宽度,不是很精确

     *

     * @param sheet

     * @param cloumI

     * @return

     */

    publicstatic int getColumnWidth(Sheet sheet, int cloumI) {

        returnnew BigDecimal(sheet.getColumnWidth(cloumI) *37 / 1200).setScale(0, BigDecimal.ROUND_HALF_UP).intValue();

    }

 

    /**

     * 获取sheet中指定column的列宽度集合,这里的宽度是近似宽度,不是很精确

     *

     * @param sheet

     * @return

     */

    publicstatic List<Integer> getColumnWidths(Sheet sheet) {

        List<Integer> columnWidths =new ArrayList<Integer>();

        intlastCellNum = getColumnNum(sheet);

        for(int i = 0; i < lastCellNum; i++) {

            columnWidths.add(newBigDecimal(sheet.getColumnWidth(i) * 37/ 1200).setScale(0, BigDecimal.ROUND_HALF_UP).intValue());

        }

        returncolumnWidths;

    }

 

    /**

     * 获取一个Sheet的冻结信息,包括冻结列和冻结行

     *

     * @param sheet

     * @return

     * @throws Exception

     */

    publicstatic Map<String, Short> getSheetFrazenColAndRow(Sheet sheet)throws Exception {

        Map<String, Short> frazenMap =new HashMap<String, Short>();

        PaneInformation paneInformation = sheet.getPaneInformation();

        if(paneInformation != null) {

            //有多少列是冻结的

            frazenMap.put("freezeCol", paneInformation.getVerticalSplitLeftColumn());

            //有多少行是冻结

            frazenMap.put("freezeRow", paneInformation.getHorizontalSplitTopRow());

        }

        returnfrazenMap;

    }

 

    /**

     * 获取单元中值(字符串类型)

     *

     * @param cell

     * @return

     */

    publicstatic String getCellData(Cell cell) {

        String cellValue ="";

        if(cell != null) {

            try{

                switch(cell.getCellType()) {

                    caseCell.CELL_TYPE_BLANK://空白

                        cellValue ="";

                        break;

                    caseCell.CELL_TYPE_NUMERIC: //数值型 0----日期类型也是数值型的一种

                        if(DateUtil.isCellDateFormatted(cell)) {

                            shortformat = cell.getCellStyle().getDataFormat();

 

                            if(yyyyMMddList.contains(format)) {

                                sFormat =new SimpleDateFormat("yyyy-MM-dd");

                            }else if(hhMMssList.contains(format)) {

                                sFormat =new SimpleDateFormat("HH:mm:ss");

                            }

                            Date date = cell.getDateCellValue();

                            cellValue = sFormat.format(date);

                        }else {

                            Double numberDate =new BigDecimal(cell.getNumericCellValue()).setScale(4, BigDecimal.ROUND_HALF_UP).doubleValue();

                            cellValue = numberDate +"";

                        }

                        break;

                    caseCell.CELL_TYPE_STRING: //字符串型 1

                        cellValue = replaceBlank(cell.getStringCellValue());

                        break;

                    caseCell.CELL_TYPE_FORMULA: //公式型 2

                        cell.setCellType(Cell.CELL_TYPE_STRING);

                        cellValue = replaceBlank(cell.getStringCellValue());

                        break;

                    caseCell.CELL_TYPE_BOOLEAN: //布尔型 4

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

                        break;

                    caseCell.CELL_TYPE_ERROR: //错误 5

                        cellValue ="!#REF!";

                        break;

                }

            }catch (Exception e) {

                System.out.println("读取Excel单元格数据出错："+ e.getMessage());

                returncellValue;

            }

        }

        returncellValue;

    }

 

    publicstatic String replaceBlank(String source) {

        String dest ="";

        if(source != null) {

            Pattern p = Pattern.compile("\\s*|\t|\r|\n");

            Matcher m = p.matcher(source);

            dest = m.replaceAll("");

        }

        returndest;

    }

 

    /**

     * 给SHEET某一个单元格赋值

     *

     * @param sheet 指定单元格

     * @param rowNum 行号

     * @param cellNum 列号

     * @param value 值

     */

    publicstatic void setCellValue(Sheet sheet, int rowNum, int cellNum, String value) {

        Row row = sheet.getRow(rowNum);

        Cell cell = row.getCell(cellNum);

        if(cell == null) {

            row.createCell(cellNum).setCellValue(value);

        }else {

            cell.setCellValue(value);

        }

    }

 

    publicstatic void mergedRegion(Sheet sheet) throws Exception {

        //合并的单元格数量

        intmerged = sheet.getNumMergedRegions();

        //预读合并的单元格

        for(int i = 0; i < merged; i++) {

            CellRangeAddress range = sheet.getMergedRegion(i);

            inty0 = range.getFirstRow();

            intx0 = range.getFirstColumn();

            inty1 = range.getLastRow();

            intx1 = range.getLastColumn();

 

            String value = getSheetCellValueWithRowIndexAndColIndex(sheet, y0, x0);

 

            for(int m = y0; m <= y1; m++) {

                for(int n = x0; n <= x1; n++) {

                    setCellValue(sheet, m, n, value);

                }

            }

        }

    }

 

    /**

     * 生成表头名称,A,B,C,D...

     *

     * @param number

     * @return

     */

    publicstatic String getCharByNum(intnumber) {

        intindex = number / 26- 1;

        if(index < 0) {

            return(char) (65+ number % 26) +"";

        }else if(index >= 0) {

            return(char) (65+ index) + "" + (char) (65+ number % 26) +"";

        }

        return"@";

    }

 

    /**

     * 补全String字符串,

     *

     * @param str 字符窜

     * @param len 长度

     * @param pre 补全字符

     * @return 补全之后的字符串

     */

    publicstatic String preFillString(String str,int len, char pre) {

        intlength = len - str.length();

        for(int i = 0; i < length; i++) {

            str = pre + str;

        }

        returnstr;

    }

 

    /**

     * 获取颜色的HTML表示方式,

     *

     * @param str getHexString()

     * @return

     */

    publicstatic String getColorByHex(String str) {

        String[] hexString = str.split(":");

        String colorRGB ="";

        for(int i = 0; i < hexString.length; i++) {

            hexString[i] = preFillString(hexString[i],4, '0');

            colorRGB += hexString[i].substring(0,2);

        }

        if("000000".equals(colorRGB)) {

            colorRGB ="";

        }

        returncolorRGB;

    }

 

    /**

     * 获取颜色

     *

     * @param shortColor

     * @return

     */

    publicstatic String getColorByShortColor(shortshortColor) {

        String returnColor ="";

        for(IndexedColors color : IndexedColors.values()) {

            if(shortColor == color.getIndex()) {

                returnColor = color.toString();

            }

        }

        if("AUTOMATIC".equals(returnColor)) {

            returnColor ="";

        }

        returnreturnColor;

    }

 

    /**

     * 获取Sheet中所有单元格样式合集

     *

     * @param sheet

     * @return

     * @throws Exception

     */

    publicstatic List<Map<String, Object>> getSheetCellStyleMaps(Sheet sheet)throws Exception {

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

        intlastRowNum = getRowNum(sheet);

        Row row;

        for(int i = 0; i < lastRowNum; i++) {

            row = sheet.getRow(i);

            if(row == null) {

                continue;

            }

            intcolumnNumMax = getColumnNum(sheet);

            for(int j = 0; j < columnNumMax; j++) {

                Cell cell = row.getCell(j);

                if(cell == null) {

                    continue;

                }

                Map<String, Object> cellMap = getCellStyleMap(sheet, cell);

                cellMap.put("y", i);

                cellMap.put("x", j);

                sheetCellStyles.add(cellMap);

            }

        }

        returnsheetCellStyles;

    }

 

    /**

     * 获取Sheet中,某一个Cell的样式,Cell的背景颜色单独去取,借助于HSSFSheet和XSSFSheet

     *

     * @param sheet

     * @param cell

     * @return

     */

    publicstatic Map<String, Object> getCellStyleMap(Sheet sheet, Cell cell) {

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

 

        Short alignShort = cell.getCellStyle().getAlignment();

        String alignment ="c";

        if(alignShort == 1) {

            alignment ="l";

        }else if(alignShort == 3) {

            alignment ="r";

        }

 

        CellStyle cellStyle = cell.getCellStyle();

        Workbook workbook = sheet.getWorkbook();

        Font font = workbook.getFontAt(cellStyle.getFontIndex());

        cellStyleMap.put("fontColor", getColorByShortColor(font.getColor()));

        cellStyleMap.put("fontBold", font.getBoldweight());

        cellStyleMap.put("fontSize", font.getFontHeightInPoints());

        cellStyleMap.put("alignment", alignment);

        try{

            HSSFCellStyle hSSFCellStyle = (HSSFCellStyle) cell.getCellStyle();

            cellStyleMap.put("cellColor", getColorByHex(hSSFCellStyle.getFillForegroundColorColor().getHexString()));

        }catch (Exception e) {

            XSSFCellStyle xSSFCellStyle = (XSSFCellStyle) cell.getCellStyle();

            String xssfCellColor ="";

            if(xSSFCellStyle.getFillBackgroundColorColor() != null) {

                xssfCellColor = xSSFCellStyle.getFillForegroundColorColor().getARGBHex().substring(2);

            }

            xssfCellColor ="000000".equals(xssfCellColor) ?"" : xssfCellColor;

            cellStyleMap.put("cellColor", xssfCellColor);

        }

        returncellStyleMap;

    }

 

    publicstatic void main(String[] args) throws Exception {

        Workbook workbook = getExcelWorkbook("D:/test/aa.xlsx");

        Sheet sheet = getSheetByNum(workbook,1);

        System.out.println(JsonUtil.toJsonString(getSheetDataMapAndId(sheet)));

    }

}

本文引用于：https://www.oschina.net/code/snippet_2283492_48487#