Java tutorial
/* * @(#)POIExcelUtil.java * * Copyright(c)2001-2012 SANY Heavy Industry Co.,Ltd * All right reserved. * * 17??????1717 * ?17??????17 * This software is the confidential and proprietary information * of SANY Heavy Industry Co, Ltd. You shall not disclose such * Confidential Information and shall use it only in accordance * with the terms of the license agreement you entered into with * SANY Heavy Industry Co, Ltd. */ package com.frameworkset.platform.sanylog.util; import java.io.IOException; import java.lang.reflect.InvocationTargetException; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFDataValidationHelper; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataValidationConstraint; import org.apache.poi.ss.util.CellRangeAddressList; import org.frameworkset.web.multipart.MultipartFile; /** * POIExcel?17. * * @author gw_liaozh */ public class POIExcelUtil { /** * Sheet???. * * @param sheet * @param dvcMap ??fieldName"type_name" * @param colDesc 17"?:user_id,??:user_name,:type_name" * @author gw_liaozh */ public static void addDataValidationConstraints(HSSFSheet sheet, Map<String, DataValidationConstraint> dvcMap, String colDesc) {// List<String> colFieldList = getColumnFieldList(colDesc); HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet); //?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); DataValidationConstraint constraint = dvcMap.get(fieldName); if (constraint == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } /** * 17??17. * * @param sheet * @param dataList * @param fieldName * @return * @throws IllegalArgumentException * @throws IllegalAccessException * @throws InvocationTargetException * @author gw_liaozh */ public static DataValidationConstraint createExplicitListConstraint(HSSFSheet sheet, List<?> dataList, String fieldName) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {// HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet); List<String> strList = new ArrayList<String>(); for (Object obj : dataList) { Object value = BeanConvertUtil.getProperty(obj, fieldName); //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value != null) { strList.add(value.toString()); } } return dvHelper.createExplicitListConstraint(strList.toArray(new String[strList.size()])); } /** * Excel Workbook?. * * @param colDesc 17"?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static HSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { //???17 //TODO: ? List<String> colTitleList = getColumnTitleList(colDesc); List<String> colFieldList = getColumnFieldList(colDesc); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFFont font = getBaseFont(wb); HSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); //? CellStyle dateCellStyle = getDateTimeCellStyle(wb); //CellStyle strCellStyle = getStringCellStyle(wb); //??17 HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); // HSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < colTitleList.size(); i++) { HSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(colTitleList.get(i)); } //?17 for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); HSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); HSSFCell cell = row.createCell(j); if (obj == null) { continue; } Object value = BeanConvertUtil.getProperty(obj, fieldName); //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } //?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) value); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } //?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < colTitleList.size(); i++) { //?? //sheet.autoSizeColumn(i); } return wb; } private static HSSFFont getBaseFont(HSSFWorkbook wb) {// HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); return font; } private static List<String> getColumnFieldList(String colDesc) {// List<String> fieldList = new ArrayList<String>(); for (String col : StringUtils.split(colDesc, ',')) { String[] titleFieldArr = StringUtils.split(col, ':'); fieldList.add(titleFieldArr[1].trim()); } return fieldList; } private static List<String> getColumnTitleList(String colDesc) {// List<String> titleList = new ArrayList<String>(); for (String col : StringUtils.split(colDesc, ',')) { String[] titleFieldArr = StringUtils.split(col, ':'); titleList.add(titleFieldArr[0].trim()); } return titleList; } private static CellStyle getDateTimeCellStyle(HSSFWorkbook wb) {// CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); return cellStyle; } @SuppressWarnings("unused") private static CellStyle getStringCellStyle(HSSFWorkbook wb) {// CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@")); return cellStyle; } private static HSSFCellStyle getHeadCellStyle(HSSFWorkbook wb, HSSFFont font) {// HSSFCellStyle headCellStyle = wb.createCellStyle(); headCellStyle.setFont(font); headCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headCellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); headCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headCellStyle.setWrapText(false); headCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return headCellStyle; } /** * ?Excel?MapList?Excel??Java. * * @param file * @return * @throws IOException */ public static List<Map<String, Object>> parseHSSFMapList(MultipartFile file) throws IOException {// POIFSFileSystem poiFs = new POIFSFileSystem(file.getInputStream()); HSSFWorkbook wb = new HSSFWorkbook(poiFs); HSSFSheet sheet = wb.getSheetAt(0); int rowNum = sheet.getLastRowNum(); HSSFRow titleRow = sheet.getRow(0); int colNum = titleRow.getLastCellNum(); //?17 List<String> titleList = new ArrayList<String>(); for (int i = 0; i < colNum; i++) { String title = titleRow.getCell(i).getStringCellValue(); titleList.add(trimTitle(title)); } List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>(); for (int i = 1; i <= rowNum; i++) { HSSFRow row = sheet.getRow(i); Map<String, Object> map = new LinkedHashMap<String, Object>(); for (int j = 0; j < colNum; j++) { HSSFCell cell = row.getCell(j); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: double d = cell.getNumericCellValue(); CellStyle style = cell.getCellStyle(); //? if (HSSFDateUtil.isCellDateFormatted(cell) || (style != null && (style.getDataFormat() == 57 || style.getDataFormat() == 58))) { map.put(titleList.get(j), HSSFDateUtil.getJavaDate(d)); } else { map.put(titleList.get(j), d); } break; default: cell.setCellType(HSSFCell.CELL_TYPE_STRING); map.put(titleList.get(j), row.getCell(j).getStringCellValue()); break; } } else { map.put(titleList.get(j), null); } } mapList.add(map); } return mapList; } /** * ?17. * * @param title * @return * @author liaozh <Aug 29, 2011> */ private static String trimTitle(String title) {// return StringUtils.replace(title, "\n", ""); } }