Java tutorial
/** * Copyright (c) 2014 http://www.lushapp.wang * * Licensed under the Apache License, Version 2.0 (the "License"); */ package com.lushapp.common.excel; import com.lushapp.common.excel.annotation.Excel; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import java.io.*; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.lang.reflect.Type; import java.math.BigDecimal; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.*; public final class ExcelUtil { private ExcelUtil() { } /** Excel ?? */ public static String outputFile = "/zlxx/excel_demo.xls"; /** * */ private static String[] HEAD_LIST = { "??", "??", "", "" }; private static String[] VALUE_LIST = { "01", "", "20", "1986-04-03", "........." }; /** * */ private static String[] FIELD_LIST = { "index", "name", "age", "content" }; /** * @param args * @throws Exception */ public static void main(String[] args) throws Exception { // ------------------------------------------------------------ // List<String[]> list = new ArrayList<String[]>(); // list.add(VALUE_LIST); // list.add(VALUE_LIST); // list.add(VALUE_LIST); // createExcel(outputFile, HEAD_LIST, list); // ------------------------------------------------------------ // List<Map<String, Object>> dataList = new ArrayList<Map<String, // Object>>(); // Map<String, Object> map = new HashMap<String, Object>(); // map.put("index", "001"); // map.put("name", ""); // map.put("age", "22"); // map.put("content", ""); // dataList.add(map); // dataList.add(map); // dataList.add(map); // // createExcel(outputFile, HEAD_LIST, FIELD_LIST, dataList); // ------------------------------------------------------------- // readExcel(null); // -------------------------------------------------------------- new ExcelUtil().testReadExcel(); } /** * * */ public void testCreateExcel() { List<Map<String, Object>> dataList = getDataList(); List<String> headList = getHeadList(); List<String> fieldList = getFieldList(); try { createExcel("TEST01.xls", headList, fieldList, dataList); } catch (Exception e) { e.printStackTrace(); } } /** * * * @throws Exception * */ @SuppressWarnings("static-access") public void testReadExcel() throws Exception { String excelUrl = "C:/javadeveloper/workspace/Mybatis_one/src/?2003.xls"; List<String[]> list = this.readExcel(excelUrl); for (String[] str : list) { for (String s : str) { //System.out.print(s + " | "); } //System.out.println(""); } } /** * * * @return */ private List<Map<String, Object>> getDataList() { /** * ?? */ List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>(); // ?? Map<String, Object> map = new HashMap<String, Object>(); map.put("index", "001"); map.put("name", ""); map.put("age", "22"); map.put("content", ""); map.put("date", new Date()); dataList.add(map); dataList.add(map); dataList.add(map); return dataList; } /** * * * @return */ private List<String> getHeadList() { List<String> headList = new ArrayList<String>(); headList.add("??"); headList.add("??"); headList.add(""); headList.add(""); headList.add(""); return headList; } /** * * * @return */ private List<String> getFieldList() { List<String> fieldList = new ArrayList<String>(); fieldList.add("index"); fieldList.add("name"); fieldList.add("age"); fieldList.add("date"); fieldList.add("content"); return fieldList; } /** * @param excel_name * ?Excel+?? * @param headList * ExcelHead? * @param fieldList * ExcelField? * @param dataList * Excel? * @throws Exception */ public static void createExcel(String excel_name, String[] headList, String[] fieldList, List<Map<String, Object>> dataList) throws Exception { // Excel HSSFWorkbook workbook = new HSSFWorkbook(); // Excel??? // ???""? // HSSFSheet sheet = workbook.createSheet(""); HSSFSheet sheet = workbook.createSheet(); // 0? HSSFRow row = sheet.createRow(0); // =============================================================== for (int i = 0; i < headList.length; i++) { // 0?? HSSFCell cell = row.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(headList[i]); } // =============================================================== for (int n = 0; n < dataList.size(); n++) { // 1? HSSFRow row_value = sheet.createRow(n + 1); Map<String, Object> dataMap = dataList.get(n); // =============================================================== for (int i = 0; i < fieldList.length; i++) { // 0?? HSSFCell cell = row_value.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(objToString(dataMap.get(fieldList[i]))); } // =============================================================== } // ? FileOutputStream fOut = new FileOutputStream(excel_name); // Excel workbook.write(fOut); fOut.flush(); // ?? fOut.close(); //System.out.println("[" + excel_name + "]" + "?..."); } /** * @param excel_name * ?Excel+?? * @param headList * ExcelHead? * @param fieldList * ExcelField? * @param dataList * Excel? * @throws Exception */ public static void createExcel(String excel_name, List<String> headList, List<String> fieldList, List<Map<String, Object>> dataList) throws Exception { // Excel HSSFWorkbook workbook = new HSSFWorkbook(); // Excel??? // ???""? // HSSFSheet sheet = workbook.createSheet(""); HSSFSheet sheet = workbook.createSheet(); // 0? HSSFRow row = sheet.createRow(0); // =============================================================== for (int i = 0; i < headList.size(); i++) { // 0?? HSSFCell cell = row.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(headList.get(i)); } // =============================================================== for (int n = 0; n < dataList.size(); n++) { // 1? HSSFRow row_value = sheet.createRow(n + 1); Map<String, Object> dataMap = dataList.get(n); // =============================================================== for (int i = 0; i < fieldList.size(); i++) { // 0?? HSSFCell cell = row_value.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(objToString(dataMap.get(fieldList.get(i)))); } // =============================================================== } // ? FileOutputStream fOut = new FileOutputStream(excel_name); // Excel workbook.write(fOut); fOut.flush(); // ?? fOut.close(); //System.out.println("[" + excel_name + "]" + "?..."); } /** * @param headList * ExcelHead? * @param fieldList * ExcelField? * @param dataList * Excel? * @throws HSSFWorkbook */ public static HSSFWorkbook createExcel(List<String> headList, List<String> fieldList, List<Map<String, Object>> dataList) throws Exception { // Excel HSSFWorkbook workbook = new HSSFWorkbook(); // Excel??? // ???""? // HSSFSheet sheet = workbook.createSheet(""); HSSFSheet sheet = workbook.createSheet(); // 0? HSSFRow row = sheet.createRow(0); // =============================================================== for (int i = 0; i < headList.size(); i++) { // 0?? HSSFCell cell = row.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(headList.get(i)); } // =============================================================== for (int n = 0; n < dataList.size(); n++) { // 1? HSSFRow row_value = sheet.createRow(n + 1); Map<String, Object> dataMap = dataList.get(n); // =============================================================== for (int i = 0; i < fieldList.size(); i++) { // 0?? HSSFCell cell = row_value.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(objToString(dataMap.get(fieldList.get(i)))); } // =============================================================== } return workbook; } private static String objToString(Object obj) { if (obj == null) { return ""; } else { if (obj instanceof String) { return (String) obj; } else if (obj instanceof Date) { return null;// DateUtil.dateToString((Date) // obj,DateUtil.DATESTYLE_SHORT_EX); } else { return obj.toString(); } } } /** * * @param excel_name * ?Excel+?? * @param headList * ExcelHead * @param valueList * Excel? * @throws Exception */ public static void bulidExcel(String excel_name, String[] headList, List<String[]> valueList) throws Exception { // Excel HSSFWorkbook workbook = new HSSFWorkbook(); // Excel??? // ???""? // HSSFSheet sheet = workbook.createSheet(""); HSSFSheet sheet = workbook.createSheet(); // 0? HSSFRow row = sheet.createRow(0); // =============================================================== for (int i = 0; i < headList.length; i++) { // 0?? HSSFCell cell = row.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(headList[i]); } // =============================================================== for (int n = 0; n < valueList.size(); n++) { // 1? HSSFRow row_value = sheet.createRow(n + 1); String[] valueArray = valueList.get(n); // =============================================================== for (int i = 0; i < valueArray.length; i++) { // 0?? HSSFCell cell = row_value.createCell(i); // ? cell.setCellType(HSSFCell.CELL_TYPE_STRING); // ? cell.setCellValue(valueArray[i]); } // =============================================================== } // ? FileOutputStream fOut = new FileOutputStream(excel_name); // Excel workbook.write(fOut); fOut.flush(); // ?? fOut.close(); //System.out.println("[" + excel_name + "]" + "?..."); } /** * ? Excel * * @param excel_name * @return * @throws Exception */ public static List<String[]> readExcel(String excel_name) throws Exception { // List<String[]> list = new ArrayList<String[]>(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(excel_name)); // ??i? getNumberOfSheets HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0); // ??,j getPhysicalNumberOfRows for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) { HSSFRow hssfrow = hssfsheet.getRow(j); if (hssfrow != null) { int col = hssfrow.getPhysicalNumberOfCells(); // ?? String[] arrayString = new String[col]; for (int i = 0; i < col; i++) { HSSFCell cell = hssfrow.getCell(i); if (cell == null) { arrayString[i] = ""; } else if (cell.getCellType() == 0) { // arrayString[i] = new Double(cell.getNumericCellValue()).toString(); if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { if (HSSFDateUtil.isCellDateFormatted(cell)) { Date d = cell.getDateCellValue(); // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd"); DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); arrayString[i] = formater.format(d); } else { arrayString[i] = new BigDecimal(cell.getNumericCellValue()).longValue() + ""; } } } else {// EXCEL? arrayString[i] = cell.getStringCellValue().trim(); } } list.add(arrayString); } } return list; } /** * ? Excel * * @param excel_name * @return * @throws Exception */ public static List<List<Object>> readExcelByList(String excel_name) throws Exception { // List<List<Object>> list = new ArrayList<List<Object>>(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(excel_name)); // ??i? getNumberOfSheets HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0); // ??,j getPhysicalNumberOfRows for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) { HSSFRow hssfrow = hssfsheet.getRow(j); if (hssfrow != null) { int col = hssfrow.getPhysicalNumberOfCells(); // ?? List<Object> arrayString = new ArrayList<Object>(); for (int i = 0; i < col; i++) { HSSFCell cell = hssfrow.getCell(i); if (cell == null) { arrayString.add(""); } else if (cell.getCellType() == 0) { arrayString.add(new Double(cell.getNumericCellValue()).toString()); } else {// EXCEL? arrayString.add(cell.getStringCellValue().trim()); } } list.add(arrayString); } } return list; } /** * ? Excel * * @param inputstream * @return * @throws Exception */ public static List<List<Object>> readExcelByInputStream(InputStream inputstream) throws Exception { // List<List<Object>> list = new ArrayList<List<Object>>(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream); // ??i? getNumberOfSheets HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0); // ??,j getPhysicalNumberOfRows // //System.out.println("excel "+hssfsheet.getPhysicalNumberOfRows()); for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) { HSSFRow hssfrow = hssfsheet.getRow(j); if (hssfrow != null) { int col = hssfrow.getPhysicalNumberOfCells(); // ?? List<Object> arrayString = new ArrayList<Object>(); for (int i = 0; i < col; i++) { HSSFCell cell = hssfrow.getCell(i); if (cell == null) { arrayString.add(""); } else if (cell.getCellType() == 0) { arrayString.add(new Double(cell.getNumericCellValue()).toString()); } else {// EXCEL? arrayString.add(cell.getStringCellValue().trim()); } } list.add(arrayString); } } return list; } /** * excel * * @param file : Excel * @param pojoClass : (?) * @return */ public static Collection importExcel(File file, Class pojoClass) { try { // FileFileInputStream; FileInputStream in = new FileInputStream(file); return importExcelByIs(in, pojoClass); } catch (FileNotFoundException e) { e.printStackTrace(); return null; } } /** * * excel * * @param inputstream : ? * @param pojoClass : (?) * @return */ public static Collection importExcelByIs(InputStream inputstream, Class pojoClass) { Collection dist = new ArrayList<Object>(); try { // Field filed[] = pojoClass.getDeclaredFields(); // Annotation??,map Map<String, Method> fieldSetMap = new HashMap<String, Method>(); Map<String, Method> fieldSetConvertMap = new HashMap<String, Method>(); // ? for (int i = 0; i < filed.length; i++) { Field f = filed[i]; // ?Annotation Excel excel = f.getAnnotation(Excel.class); // Annotationd? if (excel != null) { // AnnotationSetter String fieldname = f.getName(); String setMethodName = "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1); // method Method setMethod = pojoClass.getMethod(setMethodName, new Class[] { f.getType() }); // methodAnnotaion??key? // ??? ?? fieldSetMap.put(excel.exportName(), setMethod); if (excel.importConvert() == true) { // get/setXxxxConvert??? ?Entity? StringBuffer setConvertMethodName = new StringBuffer("convertSet"); setConvertMethodName.append(fieldname.substring(0, 1).toUpperCase()); setConvertMethodName.append(fieldname.substring(1)); Method getConvertMethod = pojoClass.getMethod(setConvertMethodName.toString(), new Class[] { String.class }); fieldSetConvertMap.put(excel.exportName(), getConvertMethod); } } } // FileFileInputStream; // // HSSFWorkbook book = new HSSFWorkbook(inputstream); // // HSSFSheet sheet = book.getSheetAt(0); // // ? Iterator<Row> row = sheet.rowIterator(); // Row title = row.next(); // Iterator<Cell> cellTitle = title.cellIterator(); // map Map titlemap = new HashMap(); // int i = 0; // while (cellTitle.hasNext()) { Cell cell = cellTitle.next(); String value = cell.getStringCellValue(); titlemap.put(i, value); i = i + 1; } // ??DateFormat // SimpleDateFormat sf; while (row.hasNext()) { // Row rown = row.next(); // Iterator<Cell> cellbody = rown.cellIterator(); // Object tObject = pojoClass.newInstance(); int k = 0; // ?? while (cellbody.hasNext()) { Cell cell = cellbody.next(); // String titleString = (String) titlemap.get(k); // ?Annotation?set if (fieldSetMap.containsKey(titleString)) { Method setMethod = (Method) fieldSetMap.get(titleString); // setter? Type[] ts = setMethod.getGenericParameterTypes(); // ??? String xclass = ts[0].toString(); // ? if (Cell.CELL_TYPE_STRING == cell.getCellType() && fieldSetConvertMap.containsKey(titleString)) { // ???String? fieldSetConvertMap.get(titleString).invoke(tObject, cell.getStringCellValue()); } else { if (xclass.equals("class java.lang.String")) { // Cell??String? cell.setCellType(Cell.CELL_TYPE_STRING); setMethod.invoke(tObject, cell.getStringCellValue()); } else if (xclass.equals("class java.util.Date")) { // update-start--Author:Quainty Date:20130523 for??(?Excel?) Date cellDate = null; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { // ? cellDate = cell.getDateCellValue(); } else { // Cell.CELL_TYPE_STRING: ? yyyy-mm-dd hh:mm:ss ??(wait to do:?) cellDate = stringToDate(cell.getStringCellValue()); } setMethod.invoke(tObject, cellDate); //// -------------------------------------------------------------------------------------------- //String cellValue = cell.getStringCellValue(); //Date theDate = stringToDate(cellValue); //setMethod.invoke(tObject, theDate); //// -------------------------------------------------------------------------------------------- } else if (xclass.equals("class java.lang.Boolean")) { boolean valBool; if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) { valBool = cell.getBooleanCellValue(); } else {// Cell.CELL_TYPE_STRING valBool = cell.getStringCellValue().equalsIgnoreCase("true") || (!cell.getStringCellValue().equals("0")); } setMethod.invoke(tObject, valBool); } else if (xclass.equals("class java.lang.Integer")) { Integer valInt; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { valInt = (new Double(cell.getNumericCellValue())).intValue(); } else {// Cell.CELL_TYPE_STRING valInt = new Integer(cell.getStringCellValue()); } setMethod.invoke(tObject, valInt); } else if (xclass.equals("class java.lang.Long")) { Long valLong; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { valLong = (new Double(cell.getNumericCellValue())).longValue(); } else {// Cell.CELL_TYPE_STRING valLong = new Long(cell.getStringCellValue()); } setMethod.invoke(tObject, valLong); } else if (xclass.equals("class java.math.BigDecimal")) { BigDecimal valDecimal; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { valDecimal = new BigDecimal(cell.getNumericCellValue()); } else {// Cell.CELL_TYPE_STRING valDecimal = new BigDecimal(cell.getStringCellValue()); } setMethod.invoke(tObject, valDecimal); } } } // k = k + 1; } dist.add(tObject); } } catch (Exception e) { e.printStackTrace(); return null; } return dist; } /** * ?Date??? YYYY-MM-DD hh:mm:ssYYYY/MM/DD hh:mm:ss * * @param cellValue : ? * @return */ private static Date stringToDate(String cellValue) { if (cellValue.length() > 19) cellValue = cellValue.substring(0, 19); Calendar calendar = Calendar.getInstance(); String[] dateStr = cellValue.split(" "); String[] dateInfo = dateStr[0].split("-"); if (dateInfo.length != 3) { dateInfo = dateStr[0].split("/"); // yyyy/mm/dd ?? } if (dateInfo.length == 3) { int year = Integer.parseInt(dateInfo[0]); int month = Integer.parseInt(dateInfo[1]) - 1; // 0~11 int day = Integer.parseInt(dateInfo[2]); calendar.set(year, month, day); } else { return null; // ?? } if (dateStr.length > 1) {// ?? hh:mm:ss String[] timeStr = dateStr[1].split(":"); if (timeStr.length == 3) { int hour = Integer.parseInt(timeStr[0]); int minute = Integer.parseInt(timeStr[1]); int second = Integer.parseInt(timeStr[2]); calendar.set(Calendar.HOUR_OF_DAY, hour); calendar.set(Calendar.MINUTE, minute); calendar.set(Calendar.SECOND, second); } else { return null; // ?? } } return calendar.getTime(); } }