工具类

package com.dita.lotus.service.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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.XSSFWorkbook;

import com.dita.lotus.web.rest.vm.ordertakeout.ReqStockBySkuVM;



/** 
 * excel 处理工具类
 */
public class ExcelUtil {
    private static DecimalFormat df = new DecimalFormat("#.###");  
    
    public static void main(String[] args) throws Exception {
        //String fileName
        String filePath = "D:/t/b.xlsx";
        testExport(filePath);
        //testImport(filePath);
    }
    
    private static void testExport(String filePath) throws Exception{
        //String sheetName = "第一个表";
        String[] titles = {"skuNo","库存"};
        String[] fieldNames = {"skuNo","stockCount"};
        
        ReqStockBySkuVM stockInfo1 = new ReqStockBySkuVM("1234",BigDecimal.ZERO);
        ReqStockBySkuVM stockInfo2 = new ReqStockBySkuVM("abcd",BigDecimal.ONE);
        ReqStockBySkuVM stockInfo3 = new ReqStockBySkuVM("小明",BigDecimal.ONE);
        List<ReqStockBySkuVM> stockList = new ArrayList<>();
        stockList.add(stockInfo1);
        stockList.add(stockInfo2);
        stockList.add(stockInfo3);
        
        //Workbook wb = exportToWorkBook(sheetName,stockList, titles, fieldNames);
        //exportToFile(filePath, sheetName, stockList, titles, fieldNames);
        exportToFile(filePath, stockList, titles, fieldNames);
        
        //Workbook wb = exportToWorkBook(sheetName,stockList, titles, fieldNames);
        //OutputStream os = response.getOutputStream();
        //wb.write(os);
    }
    
    /**
     * excel导入
     * @param header    表头，如  ["id", "name", ... ]
     * @param filePath    文件物理地址
     * @return 
     */
    public static List<Map<String, Object>> importExcel(String filePath, String[] header) throws Exception {
        return importExcel(filePath,header,0);
    }
    
    public static List<Map<String, Object>> importExcel(String filePath, String[] header,int sheetNumber) throws Exception {
        return importExcel(filePath,new FileInputStream(filePath),header);
    }
    
    /**
     * excel导入
     * @param header    表头，如  ["id", "name", ... ]
     * @param filePath    文件物理地址
     * @return 
     */
    public static List<Map<String, Object>> importExcel(File file, String[] header) throws Exception {
        return importExcel(file,header,0);
    }
    
    public static List<Map<String, Object>> importExcel(File file, String[] header,int sheetNumber) throws Exception {
        return importExcel(file.getName(),new FileInputStream(file),header,sheetNumber,0);
    }
    
    public static List<Map<String, Object>> importExcel(String fileName,InputStream fis, String[] header) throws Exception {
        return importExcel(fileName,fis,header,0);
    }
    
    public static List<Map<String, Object>> importExcel(String fileName,InputStream fis, String[] header,int dataNumber) throws Exception {
        return importExcel(fileName,fis,header,0,dataNumber);
    }
    
    public static List<Map<String, Object>> importExcel(String fileName,InputStream fis, String[] header,int sheetNumber,int dataNumber) throws Exception {
        List<Map<String, Object>> list = new ArrayList<>();
        Map<String, Object> map = new HashMap<String, Object>();
        
        if(null == header) {
            throw new Exception("keys can not be null!");
        }
        
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
            throw new Exception("The file is not excel document!");
        }
        
        // 读取文件
        //FileInputStream fis = null;
        Workbook wookbook = null;
        try {
            //fis = new FileInputStream(file);
            
            if(fileName.endsWith(".xls")) {
                wookbook = new HSSFWorkbook(fis);
            } else if(fileName.endsWith(".xlsx")) {
                wookbook = new XSSFWorkbook(fis);
            }
            
            // 获取第一个工作表信息
            Sheet sheet = wookbook.getSheetAt(sheetNumber);
            //获得数据的总行数
            int totalRowNum = sheet.getLastRowNum();

            Row row = null;
            Cell cell = null;
            Object value = null;
            int dataStartRow = 1;
            if(dataNumber>0){
                dataStartRow = dataNumber-1;
            }
            // 遍历所有行
            for (int i = dataStartRow; i <= totalRowNum; i++) {
                // 清空数据，避免遍历时读取上一次遍历数据
                row = null;
                cell = null;
                value = null;
                map = new HashMap<String, Object>();
                
                row = sheet.getRow(i);
                if(null == row) continue;    // 若该行第一列为空，则默认认为该行就是空行
                
                // 遍历该行所有列
                for (short j = 0; j < header.length; j++) {
                    cell = row.getCell(j);
                    if(null == cell) continue;    // 为空时，下一列
                    
                    value = getCellValue(cell);
                    
                    map.put(header[j], value);
                }
                
                list.add(map);
            }
        } catch (Exception e) {
            throw new Exception("analysis excel exception!", e);
        } finally {
            if(null != fis) {
                fis.close();
            }
        }
        
        return list;
    }
    
    /**
     * 获取单元格值
     * @param row 获取的行
     * @param column 获取单元格列号
     * @return 单元格值
     */
    private static Object getCellValue(Cell cell){
        Object val = "";
        try{
            // 根据poi返回的类型，做相应的get处理
            if(Cell.CELL_TYPE_STRING == cell.getCellType()) {
                val = cell.getStringCellValue();
            } else if(Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                //val = cell.getNumericCellValue();
                val = df.format(cell.getNumericCellValue()); 
                if(DateUtil.isCellDateFormatted(cell)){
                    // 由于日期类型格式也被认为是数值型，此处判断是否是日期的格式，若时，则读取为日期类型
                    /*if(cell.getCellStyle().getDataFormat() > 0)  {
                        val = cell.getDateCellValue();
                    }*/
                    val = cell.getDateCellValue();
                }
            } else if(Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
                val = cell.getBooleanCellValue();
            } else if(Cell.CELL_TYPE_BLANK == cell.getCellType()) {
                val = cell.getDateCellValue();
            }
        }catch (Exception e) {
            return val;
        }
        
        return val;
    }
    
    /**
     * excel导出
     * @param fileNamePath    导出的文件名称连路径
     * @param sheetName        导出的sheet名称
     * @param list            数据集合
     * @param titles        第一行表头
     * @param fieldNames    字段名称数组
     * @return
     */
    public static <T> File exportToFile(String fileNamePath, String sheetName, List<T> list, 
            String[] titles, String[] fieldNames) throws Exception {
        
        Workbook wb = exportToWorkBook(fileNamePath,sheetName,list,titles,fieldNames);
        
        File file = null;
        OutputStream os = null;
        file = new File(fileNamePath);
        try {
            os = new FileOutputStream(file);
            wb.write(os);
        } catch (Exception e) {
            throw new Exception("write excel file error!", e);
        } finally {
            if(null != os) {
                os.flush();
                os.close();
            }
        }
        
        
        return file;
    }
    
    public static <T> File exportToFile(String fileNamePath, List<T> list, 
            String[] titles, String[] fieldNames) throws Exception {
        return exportToFile(fileNamePath,"sheet1",list,titles,fieldNames);
    }

    /**
     * excel导出
     * @param sheetName        导出的sheet名称
     * @param list            数据集合
     * @param titles        第一行表头
     * @param fieldNames    字段名称数组
     * @return
     */
    public static <T> Workbook exportToWorkBook(String fileName, List<T> list, 
            String[] titles, String[] fieldNames) throws Exception {
        return exportToWorkBook(fileName,"sheet1",list,titles,fieldNames);
    }
    
    public static <T> Workbook exportToTemplateWorkBook(String fileName, List<T> list, 
            String[] titles, String[] fieldNames,String templateFilePath,InputStream templateFileInputStream,int startRow,int startColum) throws Exception {
        Workbook wb = null;
        //InputStream is = new FileInputStream(templateFilePath);
        if(templateFilePath.endsWith(".xls")) {
            wb = new HSSFWorkbook(templateFileInputStream);
        } else if(templateFilePath.endsWith(".xlsx")) {
            wb = new XSSFWorkbook(templateFileInputStream);
        }
        
        Sheet sheet = wb.getSheetAt(0);
        
        // 设置表头的说明
        int dataRow = startRow-1;
        if(titles!=null){
            Row topRow = sheet.getRow(startRow-1);
            CellStyle headerStyle = getHeaderStyle(wb);
            for(int i = 0; i < titles.length; i++){
                Cell topCell = topRow.createCell(i);
                topCell.setCellStyle(headerStyle);
                setCellGBKValue(topCell, titles[i]);
            }
            ++dataRow;
        }
        //Row row = sheet.getRow(startRow-1);
        
        String methodName = "";
        Method method = null;
        T t = null;
        Object ret = null;
        // 遍历生成数据行，通过反射获取字段的get方法
        for (int i = 0; i < list.size(); i++) {
            t = list.get(i);
            Row row = sheet.createRow(i+dataRow);
            Class<? extends Object> clazz = t.getClass();
            for(int j = 0; j < fieldNames.length; j++){
                methodName = "get" + capitalize(fieldNames[j]);
                try {
                    method = clazz.getDeclaredMethod(methodName);
                } catch (java.lang.NoSuchMethodException e) {    //    不存在该方法，查看父类是否存在。此处只支持一级父类，若想支持更多，建议使用while循环
                    if(null != clazz.getSuperclass()) {
                        method = clazz.getSuperclass().getDeclaredMethod(methodName);
                    }
                }
                if(null == method) {
                    throw new Exception(clazz.getName() + " don‘t have menthod --> " + methodName);
                }
                ret = method.invoke(t);
                setCellGBKValue(row.createCell(j), ret + "");
            }
        }
        
        
        return wb;
    }
    
    public static <T> Workbook exportToWorkBook(String fileName,String sheetName, List<T> list, 
            String[] titles, String[] fieldNames) throws Exception {
        
        Workbook wb = null;
        if(fileName.endsWith(".xls")) {
            wb = new HSSFWorkbook();
        } else if(fileName.endsWith(".xlsx")) {
            wb = new XSSFWorkbook();
        }
        
        Sheet sheet = null;
        
        // 对每个表生成一个新的sheet,并以表名命名
        if(sheetName == null){
            sheetName = "sheet1";
        }
        sheet = wb.createSheet(sheetName);
        
        
        // 设置表头的说明
        
        Row topRow = sheet.createRow(0);
        CellStyle headerStyle = getHeaderStyle(wb);
        for(int i = 0; i < titles.length; i++){
            Cell topCell = topRow.createCell(i);
            topCell.setCellStyle(headerStyle);
            setCellGBKValue(topCell, titles[i]);
        }
        
        String methodName = "";
        Method method = null;
        T t = null;
        Object ret = null;
        // 遍历生成数据行，通过反射获取字段的get方法
        for (int i = 0; i < list.size(); i++) {
            t = list.get(i);
            Row row = sheet.createRow(i+1);
            Class<? extends Object> clazz = t.getClass();
            for(int j = 0; j < fieldNames.length; j++){
                methodName = "get" + capitalize(fieldNames[j]);
                try {
                    method = clazz.getDeclaredMethod(methodName);
                } catch (java.lang.NoSuchMethodException e) {    //    不存在该方法，查看父类是否存在。此处只支持一级父类，若想支持更多，建议使用while循环
                    if(null != clazz.getSuperclass()) {
                        method = clazz.getSuperclass().getDeclaredMethod(methodName);
                    }
                }
                if(null == method) {
                    throw new Exception(clazz.getName() + " don‘t have menthod --> " + methodName);
                }
                ret = method.invoke(t);
                setCellGBKValue(row.createCell(j), ret + "");
            }
        }
        
        return wb;
    }
    
    private static void setCellGBKValue(Cell cell, String value) {
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        //cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue(value);
    }
    
    private static String capitalize(final String str) {
        int strLen;
        if (str == null || (strLen = str.length()) == 0) {
            return str;
        }

        final char firstChar = str.charAt(0);
        final char newChar = Character.toTitleCase(firstChar);
        if (firstChar == newChar) {
            // already capitalized
            return str;
        }

        char[] newChars = new char[strLen];
        newChars[0] = newChar;
        str.getChars(1,strLen, newChars, 1);
        return String.valueOf(newChars);
    }
    
    private static CellStyle getHeaderStyle(Workbook wb){
        CellStyle style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontHeightInPoints((short) 11);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        return style;
    }
    
}