excel导出

import java.io.ByteArrayOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import ch.qos.logback.classic.Logger;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

@Service
public class ExportRepositoryService {
/**
* 日志
*/
private static Logger logger = (Logger) LoggerFactory
.getLogger(ExportRepositoryService.class);

/**
 * 导出的数据库操作
 */
@Autowired
ExportRepositoryMapper exportRepositoryMapper;

/**
 * 生成Excel表
 * @param list 生成的对象信息
 */
private ByteArrayOutputStream createExcel(List<Object> list) {
    //集合下标
    int listindex = 1;
    int listIndexEnd = 2;
    String table = (String) list.get(listindex);
    @SuppressWarnings("unchecked")
    List<String> data = (List<String>) list.get(listIndexEnd);
    //获取导出表的列名
    List<String> oldNames = exportRepositoryMapper.getColNames(table);
    List<String> names=new ArrayList<>();
    for(String name:oldNames){
        if(!names.contains(name)){
            names.add(name);
        }
    }
    names.remove("主键");
    names.remove(null);
    String[] strArray = new String[names.size()];
    String[] title = names.toArray(strArray);
    //信息存放的列表
    List<?> dataList = lookingInfo(table, data);
    try(
        //创建Excel工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        ByteArrayOutputStream stream= new ByteArrayOutputStream();
       ) {            //创建一个工作表sheet
        Sheet sheet = workbook.createSheet();
        //创建第一行
        Row row = sheet.createRow(0);
        //单元格
        Cell cell = null;
        //产生表格标题行
        for (int i = 0; i < title.length; i++) {
            String colName = title[i];
            boolean exist = colName.equals(TableNameConsts.EXPORT_ROUNDING);
            if (!exist) {
                //去除主键列
                cell = row.createCell(i);
                cell.setCellValue(colName);
            } else {
                cell = row.createCell(i);
                cell.setCellValue(TableNameConsts.EXPORT_SERIAL_NUMBER);
            }
        }
        //遍历集合数据,产生数据行
        fileDate(sheet, dataList);
        //将excel写入到流中
        workbook.write(stream);
        return stream;
    } catch (Exception e) {
        logger.error("出现异常使得工作簿写入信息不执行", e);
    }
    return null;
}

/**
 * 往工作表里添加数据
 * @param sheet 工作表
 * @param dataList 数据集合
 */
@SuppressWarnings("rawtypes")
private void fileDate(Sheet sheet, List<?> dataList) {
    Iterator<?> it = dataList.iterator();
    int index = 0;
    try {
        while (it.hasNext()) {
            index++;
            //创建新的一行
            Row row = sheet.createRow(index);
            Object t = it.next();
            //获取所有字段
            Class<? extends Object> tcls = t.getClass();
            Field[] fields = tcls.getDeclaredFields();
            //通过反射获取值
            Class[] classArgs = null;
            Object[] args = null;
            //获取
            for (int i = 0; i < fields.length; i++) {
                Cell cell = row.createCell(i);
                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get"
                        + fieldName.substring(0, 1).toUpperCase()
                        + fieldName.substring(1);
                boolean exist = fieldName
                        .equals(TableNameConsts.EXPORT_BEAN_ROUNDING);
                if (!exist) {
                    Method getMethod = tcls.getMethod(getMethodName,classArgs);
                    Object value = getMethod.invoke(t, args);
                    //将值转为String
                    String textValue = transformationMessage(value);
                    if (textValue != null) {
                        cell.setCellValue(textValue);
                    }
                } else {
                    cell.setCellValue("" + index);
                }
            }
        }
    } catch (NoSuchMethodException | SecurityException
            | IllegalAccessException | IllegalArgumentException
            | InvocationTargetException e) {
        logger.error("excel工作cell值错误", e);
    }
}

/**
 * 转换Object信息为String
 * @param value 传入的信息对象
 * @return String格式的信息
 */
private String transformationMessage(Object value) {
    String textValue = null;
    if (value instanceof Integer) {
        //是否为整数类型
        textValue = String.valueOf(value);
    } else if (value instanceof Date) { //日期类型变为字符串
        Date date = (Date) value;
        SimpleDateFormat sdf = new SimpleDateFormat(
                TableNameConsts.DATE_FORMAT);
        textValue = sdf.format(date);
    } else {
        //其余均为字符串
        textValue = value.toString();
    }
    return textValue;
}

/**
 * 获取信息
 * @param  text 接受的文本信息
 * @return 下载信息
 */
public ByteArrayOutputStream getDownLoadInFo(String text) {
    //将文本信息变为JSON对象
    JSONObject json = JSONObject.parseObject(text);
    //调用方法解析
    List<Object> list = analyInfo(json);
    if (CollectionUtils.isEmpty(list)) {
        return null;
    }
    return createExcel(list);
}

/**
 * 获取数据信息
 * @param table 表名
 * @param data 得到的指向信息集合
 * @return 对象数据集合
 */
private List<?> lookingInfo(String table, List<String> data) {
    List<?> listDate = null;
    //表名对应的方法名
    String methodName = TableNameConsts.METHOD_MAP.get(table);
    Method[] methods = exportRepositoryMapper.getClass().getMethods();
    for (int i = 0; i < methods.length; i++) {
        Method method = methods[i];
        if (method.getName().equalsIgnoreCase(methodName)) {
            try {
                //获取返回对象集合信息
                listDate = (List<?>) method.invoke(exportRepositoryMapper,
                    data);
                return listDate;
            } catch (IllegalAccessException | IllegalArgumentException
                    | InvocationTargetException e) {
                logger.error("获取对象集合信息强转错误", e);
            }
        }
    }
    return listDate;
}

/**
 * 解析获得的信息
 * @param json 待解析的导出文本信息
 * @return 解析的数据
 */
private List<Object> analyInfo(JSONObject json) {
    List<Object> listDate = new ArrayList<>();
    //资源库
    String library = json.getString(TableNameConsts.EXPORT_ZYK);
    //表
    String table = json.getString(TableNameConsts.EXPORT_TABLE);
    //表中需要导出的数据编号
    JSONArray datas = json.getJSONArray(TableNameConsts.EXPORT_DATA);
    //判断信息是否
    if (StringUtils.isNotEmpty(library) && StringUtils.isNotEmpty(table)
            && CollectionUtils.isNotEmpty(datas)) {
        List<String> data = new ArrayList<String>();
        for (int i = 0; i < datas.size(); i++) {
            data.add((String) datas.get(i));
        }
        String tableData = exportRepositoryMapper.getTable(library, table);
        listDate.add(library);
        listDate.add(tableData);
        listDate.add(data);
        return listDate;
    } else {
        return listDate;
    }

}

}