poi 기반 excel 읽기/쓰기 유틸

ExcelCreateUtil.java

package com.dataworld.common.utils;

import java.awt.Color;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.BiFunction;
import java.util.function.Function;

import com.dataworld.common.exceptions.ExcelCreateException;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLProperties;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author reikop
 * 엑셀 생성유틸
 */
public class ExcelCreateUtil {
	
	private static List<List<Boolean>> initLists(int rowAmount, int colAmount) {
        List<List<Boolean>> lists = new ArrayList<List<Boolean>>();
        for (int i = 0; i < rowAmount; ++i) {
            List<Boolean> list = new ArrayList<Boolean>();
            for (int j = 0; j < colAmount; ++j) {
                list.add(false);
            }
            lists.add(list);
        }
        return lists;
    }
	
	
	public static XSSFWorkbook createWorkBook(List list, String[] title, String[] keys) throws ExcelCreateException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {
		return createWorkBook(list, title, keys, null);
	}
	/** 
	 * list 를 엑셀로 생성
	 * 
	 * @param list
	 * @param title
	 * @param keys
	 * @return
	 * @throws Exception
	 */
	public static XSSFWorkbook createWorkBook(List list, String[] title, String[] keys, BiFunction<Integer, Object, Object> formatter) throws ExcelCreateException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
		XSSFWorkbook workbook = new XSSFWorkbook();
		POIXMLProperties properties = workbook.getProperties();
		XSSFSheet sheet = workbook.createSheet();
		XSSFFont font = workbook.createFont();
		XSSFCellStyle cellStyleHeader = workbook.createCellStyle();
		POIXMLProperties.CoreProperties coreProperties = properties.getCoreProperties();
		coreProperties.setCreator("Smart Devops");
		font.setColor(new XSSFColor(Color.white));
		cellStyleHeader.setAlignment(HorizontalAlignment.CENTER);
		cellStyleHeader.setFillForegroundColor(new XSSFColor(Color.decode("0x336699")));
		cellStyleHeader.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cellStyleHeader.setFont(font);
		cellStyleHeader.setBorderBottom(BorderStyle.THIN);
		cellStyleHeader.setBorderLeft(BorderStyle.THIN);
		cellStyleHeader.setBorderRight(BorderStyle.THIN);
		cellStyleHeader.setBorderTop(BorderStyle.THIN);
		sheet.autoSizeColumn(title.length);
		
		XSSFRow header = sheet.createRow(0);
		for (int i = 0; i < title.length; i++) {
			XSSFCell hcell = header.createCell(i);
			hcell.setCellStyle(cellStyleHeader);
			hcell.setCellValue(title[i]);
		}
		for (int i = 0 ; i< list.size(); i++) {
			Map m = null;
			if(!(list.get(i) instanceof Map)){
				try {
					m = PropertyUtils.describe(list.get(i));
				} catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
					throw new ExcelCreateException(i);
				}
			}else{
				m = (Map) list.get(i);
			}

			XSSFRow row = sheet.createRow(i+1);
			for (int j = 0; j < keys.length; j++) {
				String key = keys[j];
				XSSFCell cell = row.createCell(j);
				Object value = null; // 바꿔야햄 nested
				if(key.contains(".")){
					String[] split = key.split("\\.");
					Object o = m.get(split[0]);
					String next = StringUtils.join(Arrays.copyOfRange(split, 1, split.length), ".");
					value = com.dataworld.common.utils.PropertyUtils.getNestedProperty(o, next);
				}else {
					value = m.get(key);
				}

				if(value != null){
					if(value instanceof Date){
						cell.setCellValue(sdf.format((Date) value));
					}else if(value instanceof BigDecimal){
						cell.setCellValue(((BigDecimal) value).intValue());
					}else if(formatter != null){
						cell.setCellValue(String.valueOf(formatter.apply(j, value)));
					}else {
						cell.setCellValue(String.valueOf(value));
					}
				}
			}
		}
		for (int i = 0; i < title.length; i++) {
			sheet.setColumnWidth(i, (sheet.getColumnWidth(i)+2048));
		}
		return workbook;
	}

	public final static class DARK_TEAL
     extends HSSFColor
 {
     public final static short   index     = 0x38;
     public final static short[] triplet   =
     {
         0, 51, 102
     };
     public final static String  hexString = "0:3333:6666";

     public short getIndex()
     {
         return index;
     }

     public short [] getTriplet()
     {
         return triplet;
     }

     public String getHexString()
     {
         return hexString;
     }
 }
}

ExcelReadUtil.java

package com.dataworld.common.utils;

import com.dataworld.common.exceptions.ExcelReadException;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.poi.POIXMLException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.hibernate.validator.constraints.NotEmpty;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.List;
import java.util.function.BiFunction;

/**
 * @author reikop
 * 엑셀 유틸
 */
public class ExcelReadUtil {	
	
	public static <T> List<T> readExcelToList(final MultipartFile multipartFile, String[] colNames, Class<T> clazz, int startIndex, BiFunction<Integer, Object, Object> formatter) throws IOException, InvalidFormatException, ExcelReadException {
		final Workbook workbook = readWorkbook(multipartFile);
	    final Sheet sheet = workbook.getSheetAt(0);
	    int numOfRows = sheet.getPhysicalNumberOfRows();
	    List<T> returnList = new ArrayList<>();
		Row row;
	    Cell cell = null;
	    T classInstance;
	    int cellIndex = 0;
	    int rowIndex = sheet.getFirstRowNum()+startIndex;
	    try {
		    for(; rowIndex < numOfRows; ++rowIndex) {
 		    	row = sheet.getRow(rowIndex); 
		    	if(row != null) {
					classInstance = clazz.newInstance();
		    		for(; cellIndex < colNames.length; cellIndex++) {
						String colName = colNames[cellIndex];
		    			cell = row.getCell(cellIndex);

						Object value = getCellData(cell);
						if (formatter != null) {
							value = formatter.apply(cellIndex, value);
						}
						Field nestedField = PropertyUtils.getNestedField(classInstance, colName);
						if (nestedField != null){
							if( nestedField.getAnnotation(NotEmpty.class) != null) {
								throw new ExcelReadException(rowIndex, cellIndex, null);
							}
							value = ConvertUtils.convert(value, nestedField.getType());
						}
						PropertyUtils.setNestedProperty(classInstance, colName, value);
	    			}
		    		returnList.add(classInstance);
		    		cellIndex = 0;
		    	}
		    }		    
		} catch (IllegalAccessException | InstantiationException | NoSuchMethodException | InvocationTargetException e) {
			throw new ExcelReadException(rowIndex, cellIndex, getCellData(cell));
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		}
		return returnList;
	}

	public static <T> List<T> readExcelToList(final MultipartFile multipartFile, String[] colNames, Class<T> clazz) throws ExcelReadException, IOException, InvalidFormatException {
	    return readExcelToList(multipartFile, colNames, clazz, 0);
	}
	public static <T> List<T> readExcelToList(final MultipartFile multipartFile, String[] colNames, Class<T> clazz, int startIndex) throws ExcelReadException, IOException, InvalidFormatException {
		return readExcelToList(multipartFile, colNames, clazz, startIndex, null);
	}
	public static Workbook readWorkbook(MultipartFile multipartFile) throws IOException, InvalidFormatException {
	    return multipartFileToWorkbook(multipartFile);
	}
	
	public static Workbook multipartFileToWorkbook(MultipartFile multipartFile)
			throws IOException, InvalidFormatException {
		try{
			return WorkbookFactory.create(multipartFile.getInputStream());
		}catch (POIXMLException | IllegalArgumentException e){
			throw new InvalidFormatException("");
		}
    }
	
	private static Object getCellData(Cell cell) {
		if(cell == null) {
			return null;
		}
		switch(cell.getCellType()) {
		case Cell.CELL_TYPE_NUMERIC:
			if(DateUtil.isCellDateFormatted(cell)){ 
				return cell.getDateCellValue();
			}else{
				return (int)cell.getNumericCellValue();
			}
		case Cell.CELL_TYPE_STRING :
			return cell.getStringCellValue();
		case Cell.CELL_TYPE_BLANK :
			return cell.toString();
		case Cell.CELL_TYPE_BOOLEAN :
			return cell.getBooleanCellValue();
		case Cell.CELL_TYPE_ERROR :
			return cell.getErrorCellValue();
		case Cell.CELL_TYPE_FORMULA :
			return cell.getCellFormula();
		}
		return null;
	}

}

PropertyUtils.java

package com.dataworld.common.utils;
/*
 * Copyright 2004, 2005, 2006 Odysseus Software GmbH
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

import java.lang.reflect.Field;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;

/**
 * Property access utility methods.
 *
 * @author Christoph Beck
 */
public class PropertyUtils {
    private static HashMap descriptorCache = new HashMap();

    /**
     * Get map with property descriptors for the specified bean class
     */
    private static Map getPropertyDescriptors(Class clazz) {
        HashMap map = (HashMap)descriptorCache.get(clazz);
        if (map == null) {
            BeanInfo beanInfo = null;
            try {
                beanInfo = Introspector.getBeanInfo(clazz);
            } catch (IntrospectionException e) {
                return Collections.EMPTY_MAP;
            }
            PropertyDescriptor[] descriptors = beanInfo.getPropertyDescriptors();
            if (descriptors == null)
                descriptors = new PropertyDescriptor[0];
            map = new HashMap(descriptors.length);
            for (int i = 0; i < descriptors.length; i++)
                map.put(descriptors[i].getName(), descriptors[i]);
            descriptorCache.put(clazz, map);
        }
        return map;
    }

    /**
     * Get property names of the specified bean class
     */
    public static Iterator getPropertyNames(Class clazz) {
        return getPropertyDescriptors(clazz).keySet().iterator();
    }

    /**
     * Get specified property descriptor
     */
    public static PropertyDescriptor getPropertyDescriptor(Class clazz, String property) {
        return (PropertyDescriptor)getPropertyDescriptors(clazz).get(property);
    }

    /**
     * Get specified property value
     */
    public static Object getProperty(Object bean, String property) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        PropertyDescriptor descriptor = getPropertyDescriptor(bean.getClass(), property);
        if (descriptor == null)
            throw new NoSuchMethodException("Cannot find property " + bean.getClass().getName() + "." + property);
        Method method = descriptor.getReadMethod();
        if (method == null)
            throw new NoSuchMethodException("Cannot find getter for " + bean.getClass().getName() + "." + property);
        return method.invoke(bean, null);
    }


    private static Field getField(Object bean, String s) throws NoSuchFieldException {
        Field field = bean.getClass().getDeclaredField(s);
        field.setAccessible(true);
        return field;
    }

    /**
     * Get specified property value
     */
    public static Method getPropertyMethod(Object bean, String property) throws NoSuchMethodException {
        PropertyDescriptor descriptor = getPropertyDescriptor(bean.getClass(), property);
        if (descriptor == null)
            throw new NoSuchMethodException("Cannot find property " + bean.getClass().getName() + "." + property);
        Method method = descriptor.getReadMethod();
        if (method == null)
            throw new NoSuchMethodException("Cannot find getter for " + bean.getClass().getName() + "." + property);
        return method;
    }

    /**
     * Get specified nested property value
     */
    public static Object getNestedProperty(Object bean, String property) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        if (property.indexOf('.') > 0) {
            String[] path = property.split("\\.");
            for (int i = 0; i < path.length && bean != null; i++) {
                bean = getProperty(bean, path[i]);
            }
            return bean;
        } else {
            return getProperty(bean, property);
        }
    }

    /**
     * Get specified nested property value
     */
    public static Field getNestedField(Object bean, String property) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, NoSuchFieldException {
        Field field = null;
        if (property.indexOf('.') > 0) {
            String[] path = property.split("\\.");
            for (int i = 0; i < path.length && bean != null; i++) {
                field = getField(bean, path[i]);
                bean = getProperty(bean, path[i]);
            }
            return field;
        } else {
            return getField(bean, property);
        }
    }


    /**
     * Set specified property value
     */
    public static void setProperty(Object bean, String property, Object value) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        PropertyDescriptor descriptor = getPropertyDescriptor(bean.getClass(), property);
        if (descriptor == null)
            throw new NoSuchMethodException("Cannot find property " + bean.getClass().getName() + "." + property);
        Method method = descriptor.getWriteMethod();
        if (method == null)
            throw new NoSuchMethodException("Cannot find setter for " + bean.getClass().getName() + "." + property);
        method.invoke(bean, value);
    }

    /**
     * Set nested property given by property path, starting at specified
     * index. Dynamically create beans if necessary. Take care not to
     * leave the bean changed if an exception occurs.
     */
    private static void setNestedPropertyWithCreate(Object bean, String[] path, int start, Object value) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException {
        for (int i = start; i < path.length - 1; i++) {
            Object object = getProperty(bean, path[i]);
            if (object == null) {
                PropertyDescriptor descr =
                        getPropertyDescriptor(bean.getClass(), path[i]);
                object = descr.getPropertyType().newInstance();
                setNestedPropertyWithCreate(object, path, i + 1, value);
                setProperty(bean, path[i], object);
                return;
            }
            bean = object;
        }
        setProperty(bean, path[path.length - 1], value);
    }

    /**
     * Set specified nested property value
     */
    public static void setNestedProperty(Object bean, String property, Object value) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException {
        int lastDot = property.lastIndexOf('.');
        if (lastDot > 0) {
            setNestedPropertyWithCreate(bean, property.split("\\."), 0, value);
        } else {
            setProperty(bean, property, value);
        }
    }
}