package cc.mrbird.common.util; import java.io.InputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.RichTextString; 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.xssf.usermodel.XSSFRichTextString; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelUtil2 { private final static String excel2003L = ".xls"; // 2003- 版本的excel private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel /** * 将流中的Excel数据转成List<Map> * * @param in * 输入流 * @param fileName * 文件名（判断Excel版本） * @param mapping * 字段名称映射 * @return * @throws Exception */ public static List<Map<String, Object>> parseExcel(InputStream in, String fileName, Map<String, String> mapping) throws Exception { // 根据文件名来创建Excel工作薄 Workbook work = getWorkbook(in, fileName); if (null == work) { throw new Exception("创建Excel工作薄为空！"); } Sheet sheet = null; Row row = null; Cell cell = null; // 返回数据 List<Map<String, Object>> ls = new ArrayList<Map<String, Object>>(); // 遍历Excel中所有的sheet for (int i = 0; i < work.getNumberOfSheets(); i++) { sheet = work.getSheetAt(i); if (sheet == null) continue; // 取第一行标题 row = sheet.getRow(0); String title[] = null; if (row != null) { title = new String[row.getLastCellNum()]; for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) { cell = row.getCell(y); title[y] = (String) getCellValue(cell); } } else continue; //log.info(JSON.toJSONString(title)); // 遍历当前sheet中的所有行 for (int j = 1; j < sheet.getLastRowNum() + 1; j++) { row = sheet.getRow(j); Map<String, Object> m = new HashMap<String, Object>(); // 遍历所有的列 //System.out.println("行："+j); boolean flag = true;//判断当前格子是否没有任何数据 for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) { cell = row.getCell(y); if(cell==null){//格子是空的就创建一个，并填充空的富文本内容 row.createCell(y).setCellValue(getRichText("",fileName)); cell = row.getCell(y); } String key = title[y]; // log.info(JSON.toJSONString(key)); //System.out.println(title[y]+" 列："+y); m.put(mapping.get(key), getCellValue(cell)); if(!"".equals(getCellValue(cell).toString())){ flag = false; } } if(flag){ throw new Exception("当前表格中有空行，请删除后再重新导入！"); } ls.add(m); } } work.close(); return ls; } /** * 根据文件后缀，自适应创建富文本 * @param inStr * @param fileName * @return * @throws Exception */ public static RichTextString getRichText(String inStr,String fileName) throws Exception{ RichTextString rs = null; String fileType = fileName.substring(fileName.lastIndexOf(".")); if (excel2003L.equals(fileType)) { rs = new HSSFRichTextString(inStr); // 2003- } else if (excel2007U.equals(fileType)) { rs = new XSSFRichTextString(inStr); // 2007+ } else { throw new Exception("请选择正确的excel文件！"); } return rs; } /** * 描述：根据文件后缀，自适应上传文件的版本 * * @param inStr * ,fileName * @return * @throws Exception */ public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception { Workbook wb = null; String fileType = fileName.substring(fileName.lastIndexOf(".")); if (excel2003L.equals(fileType)) { wb = new HSSFWorkbook(inStr); // 2003- } else if (excel2007U.equals(fileType)) { wb = new XSSFWorkbook(inStr); // 2007+ } else { throw new Exception("请选择正确的excel文件！"); } return wb; } /** * 描述：对表格中数值进行格式化 * * @param cell * @return */ public static Object getCellValue(Cell cell) { Object value = null; DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符 SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化 DecimalFormat df2 = new DecimalFormat("0"); // 格式化数字 // if(cell==null){ // return null; // } switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: value = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC: if ("General".equals(cell.getCellStyle().getDataFormatString())) { value = df.format(cell.getNumericCellValue()); } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) { value = sdf.format(cell.getDateCellValue()); } else { value = df2.format(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK: value = ""; break; default: break; } return value; } public static void main(String[] args) throws Exception { File file = new File("D:\2.xlsx"); FileInputStream fis = new FileInputStream(file); Map<String, String> m = new HashMap<String, String>(); m.put("IP","IP"); m.put("Vlan","Vlan"); m.put("Computer Name *","ComputerName"); m.put("Mac Address *","MacAddress"); m.put("Inventory No. *","InventoryNo"); m.put("Type * 类型，是否PLC","Type"); m.put("OS version * 系统版本","OSversion"); m.put("Area*地点","Area"); m.put("Machine Description设备描述","MachineDescription"); m.put("Sepecial Softwares*特殊软件","SepecialSoftwares"); m.put("Responsible*负责人","Responsible"); m.put("Section部门","Section"); m.put("Antivirus*是否安装防病毒","Antivirus"); m.put("Patch updated*是否打补丁","PatchUpdated"); m.put("Secure*是否安全","Secure"); m.put("Block USB","BlockUSB"); m.put("Block CD-ROM","BlockCD-ROM"); m.put("A-SC 可用性","A-SC"); m.put("C-SC 机密性","C-SC"); m.put("I-SC 完整性","I-SC"); m.put("Emergency Plan紧急恢复计划","EmergencyPlan"); m.put("备注","Remark"); List<Map<String, Object>> ls = parseExcel(fis, file.getName(), m); System.out.println(JSON.toJSONString(ls)); for(int i=0;i<ls.size();i++){ Map<String,Object> map = ls.get(i); map.get("IP"); System.out.println(map.get("IP")); } } }