读取Excel文档的内容工具类

import java.io.ByteArrayInputStream;import java.io.IOException;import java.math.BigDecimal;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;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.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class Excel2ObjectMappingUtil {public static Map<String,List<CZZbMapping>> para(Context context,String fileName,byte[] data) throws Exception{if(fileName.endsWith(".xls")|| fileName.endsWith(".xlsx")) {try {Map<String,List<CZZbMapping>> map=new HashMap<String, List<CZZbMapping>>();ByteArrayInputStream datas=new ByteArrayInputStream(data);boolean isExcel2007 = getExcelVersion(fileName);Workbook wb = (!isExcel2007)?new XSSFWorkbook(datas):new HSSFWorkbook(datas);    int count = wb.getNumberOfSheets();//sheet个数       for(int i = 0; i < count; i++){    List<CZZbMapping> list=new ArrayList<CZZbMapping>();    Sheet sheet = wb.getSheetAt(i);    int rowCount = sheet.getLastRowNum()+1;//行数 //计算列数int colCount = getColCount(sheet, rowCount);if(rowCount < 2 || colCount <5) {continue;}    String[][] sheetData = oneSheetProcess(sheet, rowCount, colCount);    for(int j=1 ;j < rowCount ;j++) {    CZZbMapping zm=new CZZbMapping();    zm.setRecid(GUID.randomID());//主键    String key = sheetData[j][0];    System.out.println(key);    String value = sheetData[j][0];    System.out.println("第"+j+"行第1列"+value);    zm.setJqrcode(sheetData[j][0]);//jqrcode    zm.setCzcode(sheetData[j][1]);//czcode    zm.setMapid(GUID.tryValueOf(sheetData[j][2]));//mappingid    zm.setReportname(sheetData[j][3]);//报表名称    zm.setTabName(sheetData[j][4]);//项目标识名称    list.add(zm);    }    String sheetName = sheet.getSheetName();//报表名称    map.put(sheetName, list);    }    return map;} catch (IOException e) {e.printStackTrace();}}else{System.out.println("文件名不对");}return null;}/** * 将sheet中的数据按二阶数组获取 * @param sheet * @param rowCount * @param colCount * @return */private static String[][] oneSheetProcess(Sheet sheet, int rowCount, int colCount) {String showTexts[][];showTexts = new String[rowCount][colCount];for(int r = 0;r<rowCount;r++){for(int c=0;c<colCount;c++){showTexts[r][c] = doGetShowText(sheet, r, c);}}return showTexts;}/** * 获取单元格显示的值 * @param sheet * @param row * @param col * @return */public static String doGetShowText(Sheet sheet, int row, int col) {if(row>=sheet.getFirstRowNum() && row<=sheet.getLastRowNum()){Row hssfRow = sheet.getRow(row);if(hssfRow!=null && col>=hssfRow.getFirstCellNum() && col<=hssfRow.getLastCellNum()){Cell cell = getSheetCell(sheet,row,col);return getStringCellValue(cell);}}return "";}/** * 获取sheet的单元格 * @param sheet * @param row * @param col * @return */private static Cell getSheetCell(Sheet sheet, int row, int col) {int theRow = row;int theCol = col;int num = sheet.getNumMergedRegions();for(int i=0;i<num;i++){CellRangeAddress region = sheet.getMergedRegion(i);if(col>=region.getFirstColumn() && col<=region.getLastColumn() &&row>=region.getFirstRow() && row<=region.getLastRow()){theRow = region.getFirstRow();theCol = region.getFirstColumn();break;}}Row sheetRow = sheet.getRow(theRow);Cell cell = sheetRow.getCell(theCol);return cell;}/** * 获取单元格字符值 * @param cell * @return */private static String getStringCellValue(final Cell cell) {if(cell==null)return "";switch(cell.getCellType()){case HSSFCell.CELL_TYPE_STRING:return cell.getStringCellValue();case HSSFCell.CELL_TYPE_NUMERIC:if (HSSFDateUtil.isCellDateFormatted(cell)) {        Date d = cell.getDateCellValue();        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");        return formater.format(d);   } return handleNumericStr(cell.getNumericCellValue());case HSSFCell.CELL_TYPE_BLANK:return "";}return "";}/** * 将数字转换成字符 * @param numericCellValue * @return */private static String handleNumericStr(double numericCellValue) {BigDecimal bd = new BigDecimal(numericCellValue);return bd.toString();}/** * 获取列数 * @param sheet * @param rowCount * @return */private static int getColCount(Sheet sheet, int rowCount) {int colCount = 0;for(int r=0;r< rowCount;r++){Row hssfRow = sheet.getRow(r);if(hssfRow!=null){if(colCount<hssfRow.getLastCellNum()){colCount = hssfRow.getLastCellNum();}}}return colCount;}public static boolean getExcelVersion(String fileName) {if(fileName.matches("^.+\\.(?i)(xlsx)$"))return true;return false;}}