Java tutorial
/** * Copyright 2015. * * Licensed under the Apache License, Version 2.0 (the "License"); */ package cn.mypandora.util; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.*; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.*; import java.util.regex.Pattern; /** * Excel * <p>User: kaibo * <p>Date: 2015/7/17 * <p>Version: 1.0 */ public class MyExcelUtil { private static final Logger logger = LoggerFactory.getLogger(MyExcelUtil.class); /** * ??ExcelTitle * * @param excelFile * @param sheetName sheet??? * @return */ public static List<String> scanExcelTitles(File excelFile, String... sheetName) { List<String> titles = new ArrayList<>(); try { Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFile)); Sheet sheet; if (sheetName.length == 0) { sheet = workbook.getSheetAt(0); } else { sheet = workbook.getSheet(sheetName[0]); } Row row = sheet.getRow(0); if (row != null) { int i = 0; while (true) { Cell cell = row.getCell(i); if (cell == null) { break; } titles.add(cell.getStringCellValue()); i++; } } } catch (Exception e) { logger.debug("Scan Excel [" + excelFile.getPath() + excelFile.getName() + "] Error"); throw new RuntimeException(e); } return titles; } /** * ?Excel List<Map<String K,String V>>? * * @param excelFile Excel * @param fieldNames MapKeyValuesheet? * @param sheetName ?? * @return */ public static List<Map<String, String>> readExcelToMap(File excelFile, String fieldNames, String... sheetName) { List<Map<String, String>> list = Collections.EMPTY_LIST; try { Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFile)); list = execRead(workbook, fieldNames, sheetName); } catch (Exception e) { logger.error("?:" + e); } return list; } /** * ?Excel List<Map<String K,String V>>? * * @param excelFile ? * @param fieldNames Key * @param sheetName sheet?? * @return */ public static List<Map<String, String>> readExcelToMap(InputStream excelFile, String fieldNames, String... sheetName) { List<Map<String, String>> list = Collections.EMPTY_LIST; try { Workbook workbook = WorkbookFactory.create(excelFile); list = execRead(workbook, fieldNames, sheetName); } catch (Exception e) { logger.error("?:" + e); } return list; } /** * @param workbook * @param fieldNames ?? * @param sheetName ??? * @return */ private static List<Map<String, String>> execRead(Workbook workbook, String fieldNames, String... sheetName) { String[] strKey = fieldNames.split(","); List<Map<String, String>> listMap = new ArrayList<>(); int i = 1; try { Sheet sheet; if (sheetName.length == 0) { sheet = workbook.getSheetAt(0); } else { sheet = workbook.getSheet(sheetName[0]); } while (true) { Row row = sheet.getRow(i); if (row == null) { break; } Map<String, String> map = new HashMap<String, String>(); map.put("rowid", String.valueOf(row.getRowNum())); for (int keyIndex = 0; keyIndex < strKey.length; keyIndex++) { Cell cell; cell = row.getCell(keyIndex); String cellValue = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: { // ?cell?Date if (DateUtil.isCellDateFormatted(cell)) { // Date?CellDate SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())); } // else { // ??Cell Integer num = new Integer((int) cell.getNumericCellValue()); cellValue = String.valueOf(num); } break; } case Cell.CELL_TYPE_STRING: cellValue = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); break; default: cellValue = " "; } } map.put(strKey[keyIndex], cellValue); } listMap.add(map); i++; } } catch (Exception e) { logger.debug("?" + i + "??"); throw new RuntimeException(e); } return listMap; } /** * Excel, ? * * @param filepath ? * @param sheetTitle Sheet?? * @param fieldTitles Sheet???? */ public static void writeExcel(String filepath, String sheetTitle, String fieldTitles) { Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }; for (int i = 0; i < wbs.length; i++) { Workbook wb = wbs[i]; // ExcelSheet Sheet sheet = wb.createSheet(); wb.setSheetName(0, sheetTitle); // Sheet createTitle(sheet, fieldTitles); // ?Excel saveExcelFile(wb, filepath); } } /** * Excel ????List<Map<String K,String V>> * * @param filepath ? * @param sheetTitle Sheet?? * @param fieldTitles Sheet???? * @param objList ?? * @param fieldNames ?objClassfield?? */ public static void writeExcel(String filepath, String sheetTitle, String fieldTitles, List<Map<String, String>> objList, String fieldNames) { Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }; for (int j = 0; j < wbs.length; j++) { Workbook workbook = wbs[j]; CreationHelper creationHelper = workbook.getCreationHelper(); // ExcelSheet Sheet sheet = workbook.createSheet(sheetTitle); workbook.setSheetName(0, sheetTitle); // Sheet createTitle(sheet, fieldTitles); // Sheet? String[] strArray = fieldNames.split(","); for (int objIndex = 0; objIndex < objList.size(); objIndex++) { Map<String, String> map = objList.get(objIndex); Row row = sheet.createRow(objIndex + 1); for (int cellNum = 0; cellNum < strArray.length; cellNum++) { Cell cell = row.createCell(cellNum); cell.setCellType(CellType.STRING); if (map.get(strArray[cellNum]) != null) cell.setCellValue(map.get(strArray[cellNum]).toString()); else { cell.setCellValue(""); } } } // ?Excel saveExcelFile(workbook, filepath); } } /** * Excle * * @param filepath ? * @param sheetTitle Sheet?? * @param fieldTitles Sheet???? * @param objList ?? * @param objClass ??? * @param fieldNames ?objClassfield?? */ public static void writeExcel(String filepath, String sheetTitle, String fieldTitles, List<?> objList, Class<?> objClass, String fieldNames) { Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }; for (int j = 0; j < wbs.length; j++) { Workbook workbook = wbs[j]; CreationHelper creationHelper = workbook.getCreationHelper(); Sheet sheet = workbook.createSheet(); workbook.setSheetName(0, sheetTitle); createTitle(sheet, fieldTitles); createBody(sheet, objList, objClass, fieldNames); // ?Excel saveExcelFile(workbook, filepath); } } /** * Excel?sheet? * * @param sheet Excelsheet * @param fieldTitles sheet?(sheet?) */ private static void createTitle(Sheet sheet, String fieldTitles) { Row row = sheet.createRow(0); Cell cell; String[] strArray = fieldTitles.split(","); for (int i = 0; i < strArray.length; i++) { cell = row.createCell(i); cell.setCellType(CellType.STRING); cell.setCellValue(strArray[i]); } } /** * Excel?sheet * * @param sheet sheet * @param objList ?? * @param objClass ??? * @param fieldNames ?objClassfield?? */ private static void createBody(Sheet sheet, List<?> objList, Class<?> objClass, String fieldNames) { String[] targetMethod = fieldNames.split(","); Method[] ms = objClass.getMethods(); Pattern pattern = Pattern.compile("^get.*"); // objList?sheet for (int objIndex = 0; objIndex < objList.size(); objIndex++) { Object obj = objList.get(objIndex); Row row = sheet.createRow(objIndex + 1); // strBody?sheet for (int strIndex = 0; strIndex < targetMethod.length; strIndex++) { String targetMethodName = targetMethod[strIndex]; // msstrBody for (int i = 0; i < ms.length; i++) { Method srcMethod = ms[i]; if (pattern.matcher(srcMethod.getName()).matches()) { int len = targetMethodName.indexOf(".") < 0 ? targetMethodName.length() : targetMethodName.indexOf("."); if (srcMethod.getName() .equals(("get" + String.valueOf(targetMethodName.substring(0, len).charAt(0)).toUpperCase() + targetMethodName.substring(1, len)))) { Cell cell = row.createCell(strIndex); cell.setCellType(CellType.STRING); try { // if (targetMethodName.contains(".")) { cell.setCellValue(referenceInvoke(targetMethodName, obj)); // } else { cell.setCellValue((srcMethod.invoke(obj)).toString()); } } catch (Exception e) { throw new RuntimeException(e); } } } } } } } /** * ?Excel * * @param workbook Excel * @param outputPath ? */ private static void saveExcelFile(Workbook workbook, String outputPath) { try { FileOutputStream fos = new FileOutputStream(outputPath); workbook.write(fos); fos.flush(); fos.close(); } catch (IOException e) { throw new RuntimeException(e); } } /** * getHomeplace.getName? * ??? * * @param targetMethod ? * @param obj ? * @return */ private static String referenceInvoke(String targetMethod, Object obj) { // ??(??objgetHomeplace()) String refMethod = targetMethod.substring(0, targetMethod.indexOf(".")); // ??(getName()) targetMethod = targetMethod.substring(targetMethod.indexOf(".") + 1); try { // (?objobj.getHomeplace()) obj = obj.getClass() .getMethod("get" + String.valueOf(refMethod.charAt(0)).toUpperCase() + refMethod.substring(1)) .invoke(obj); } catch (Exception e) { e.printStackTrace(); } // ?? if (targetMethod.contains(".")) { return referenceInvoke(targetMethod, obj); // ?? } else { try { // obj? Method tarMethod = obj.getClass().getMethod( "get" + String.valueOf(targetMethod.charAt(0)).toUpperCase() + targetMethod.substring(1)); return tarMethod.invoke(obj).toString(); } catch (Exception e) { throw new RuntimeException(e); } } } public static void main(String[] args) { // ? String fileName = "C:\\Users\\JUSFOUN\\Desktop\\10.xlsx"; List<String> titles = scanExcelTitles(new File(fileName)); List<Map<String, String>> listMap = readExcelToMap(new File(fileName), StringUtils.join(titles, ','), "Sheet1"); // ? writeExcel("C:\\Users\\JUSFOUN\\Desktop\\11.xlsx", "test1", "name,leader", listMap, StringUtils.join(titles, ',')); } }