POI导出

最新推荐文章于 2022-12-30 10:19:30 发布

tikaber

最新推荐文章于 2022-12-30 10:19:30 发布

阅读量175

点赞数

本文链接：https://blog.csdn.net/tikaber/article/details/79114644

版权

package cc.platform.business.course.export;

import java.io.IOException;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/** 
* @ClassName: ExcelExportService 
* @Description: TODO(描述) 
* @author chihaibo
* @date 2018年1月16日 上午10:54:04 
* @version V1.0
*  
*/
public interface ExcelExportService {
    void excelExport(HttpServletRequest request, HttpServletResponse response,
            TableModel tableModel) throws IOException;
}

package cc.platform.business.course.export;

import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.stereotype.Service;

/** 
* @ClassName: ExcelExportorImpl 
* @Description: TODO(描述) 
* @date 2018年1月16日 上午10:54:24 
* @version V1.0
*  
*/
@Service
public class ExcelExportServiceImpl implements ExcelExportService {

	/* (非 Javadoc) 
	* <p>Title: excelExport</p> 
	* <p>Description: </p> 
	* @param request
	* @param response
	* @param tableModel
	* @throws IOException 
	* @see cc.platform.common.export.ExcelExportor#excelExport(javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse, cc.platform.common.export.TableModel) 
	*/
	@Override
	public void excelExport(HttpServletRequest request, HttpServletResponse response, TableModel tableModel)
			throws IOException {
        //创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
        //在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet(tableModel.getName());
        //在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow((int) 0);  
        sheet.setDefaultColumnWidth(20);
        //创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle(); 
        //创建一个居中格式  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        HSSFFont font = wb.createFont();  
        font.setFontHeightInPoints((short) 10);  
        font.setColor(HSSFColor.ROYAL_BLUE.index);  
        font.setBoldweight((short) 0.8);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
        style.setFont(font); //调用字体样式对象
        
        //背景色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);  
        for(int i=0;i<tableModel.getHeaderCount();i++){
            HSSFCell cell = row.createCell((short) i);
            cell.setCellValue(tableModel.getHeader(i));
            cell.setCellStyle(style);
        }

        List list = tableModel.getData();
        for (int i = 0; i < list.size(); i++)  
        {  
            row = sheet.createRow((int) i + 1);
            Map<String,Object> rd = (Map<String,Object>) tableModel.getData().get(i);
            //创建单元格，并设置值
            int m =0;
            for(Map.Entry<String,Object> entry: rd.entrySet()){
                if(entry.getValue() instanceof String){
                    row.createCell((short) m).setCellValue((String)entry.getValue());
                }
                if(entry.getValue() instanceof Date){
                    SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
                    row.createCell((short) m).setCellValue(sf.format(entry.getValue()));
                }
                if(entry.getValue() instanceof Long){
                    row.createCell((short) m).setCellValue(String.valueOf(entry.getValue()));
                }
                if(entry.getValue() instanceof Integer){
                    row.createCell((short) m).setCellValue(String.valueOf(entry.getValue()));
                }
                if(entry.getValue() instanceof BigDecimal){
                    row.createCell((short) m).setCellValue(String.valueOf(entry.getValue()));
                }
                m++;
            }
        }
         	// 将文件存到指定位置  
        	OutputStream toClient = null;
            try  
            {  
            	 String fileName = tableModel.getName() + ".xls";
            	 final String userAgent = request.getHeader("USER-AGENT");
            	 String finalFileName = null;
                 if(StringUtils.contains(userAgent, "MSIE")){//IE浏览器
                	 finalFileName = URLEncoder.encode(fileName,"UTF8");
                 }else if(StringUtils.contains(userAgent, "Mozilla")){//google,火狐浏览器
                	 finalFileName = new String(fileName.getBytes("gbk"), "iso8859-1");

                 }else{
                	 finalFileName = URLEncoder.encode(fileName,"UTF8");//其他浏览器
                 }
            	// 清空response  
                response.reset();  
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setCharacterEncoding("utf-8");
                response.setHeader("Content-Disposition", "attachment;filename=\""+ new String(finalFileName)+"\"");

                toClient = new BufferedOutputStream(response.getOutputStream());
                wb.write(toClient);  
                toClient.flush();  
                toClient.close();
            }  
            catch (Exception e)  
            {  
                e.printStackTrace();  
            }finally{  
                if(toClient != null){  
                    try {  
                    	toClient.close();  
                    } catch (IOException e) {  
                        e.printStackTrace();  
                    }  
                
                } 
            }
    	}

}

package cc.platform.business.course.export;

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

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/** 
* @ClassName: ReflectUtils 
* @Description: TODO(描述) 
* @author chihaibo
* @date 2018年1月18日 下午1:18:09 
* @version V1.0
*  
*/
public class ReflectUtils {
    /** logger. */
    private static Logger logger = LoggerFactory.getLogger(ReflectUtils.class);

    /** protected constructor. */
    protected ReflectUtils() {
    }

    public static String getGetterMethodName(Object target, String fieldName)
            throws NoSuchMethodException {
        String methodName = "get" + StringUtils.capitalize(fieldName);

        try {
            target.getClass().getDeclaredMethod(methodName);
        } catch (NoSuchMethodException ex) {
            logger.trace(ex.getMessage(), ex);
            methodName = "is" + StringUtils.capitalize(fieldName);

            target.getClass().getDeclaredMethod(methodName);
        }

        return methodName;
    }

    public static String getSetterMethodName(String fieldName) {
        return "set" + StringUtils.capitalize(fieldName);
    }

   
    /** 
    * @Title: getMethodValue 
    * @Description: TODO(描述) 
    * @param @param target
    * @param @param methodName
    * @param @return
    * @param @throws NoSuchMethodException
    * @param @throws IllegalAccessException
    * @param @throws InvocationTargetException    设定文件 
    * @return Object    返回类型 
    * @throws 
    */
    public static Object getMethodValue(Object target, String methodName)
            throws NoSuchMethodException, IllegalAccessException,
            InvocationTargetException {
        Method method = target.getClass().getDeclaredMethod(methodName);

        return method.invoke(target);
    }

    /** 
    * @Title: setMethodValue 
    * @Description: TODO(描述) 
    * @param @param target
    * @param @param methodName
    * @param @param methodValue
    * @param @throws NoSuchMethodException
    * @param @throws IllegalAccessException
    * @param @throws InvocationTargetException    设定文件 
    * @return void    返回类型 
    * @throws 
    */
    public static void setMethodValue(Object target, String methodName,
            Object methodValue) throws NoSuchMethodException,
            IllegalAccessException, InvocationTargetException {
        Method method = target.getClass().getDeclaredMethod(methodName,
                methodValue.getClass());

        method.invoke(target, methodValue);
    }

    public static Object getFieldValue(Object target, String fieldName)
            throws NoSuchFieldException, IllegalAccessException {
        return getFieldValue(target, fieldName, true);
    }

    public static Object getFieldValue(Object target, String fieldName,
            boolean isForce) throws NoSuchFieldException,
            IllegalAccessException {
        Field field = target.getClass().getDeclaredField(fieldName);
        field.setAccessible(isForce);

        return field.get(target);
    }

    
    /** 
    * @Title: convertReflectionExceptionToUnchecked 
    * @Description: TODO(描述) 
    * @param @param e
    * @param @return    设定文件 
    * @return RuntimeException    返回类型 
    * @throws 
    */
    public static RuntimeException convertReflectionExceptionToUnchecked(
            Exception e) {
        if (e instanceof RuntimeException) {
            return (RuntimeException) e;
        } else if (e instanceof IllegalAccessException
                || e instanceof NoSuchMethodException
                || e instanceof NoSuchFieldException) {
            return new IllegalArgumentException("Reflection Exception.", e);
        } else if (e instanceof InvocationTargetException) {
            Throwable targetException = ((InvocationTargetException) e)
                    .getTargetException();

            if (targetException instanceof RuntimeException) {
                return (RuntimeException) targetException;
            } else {
                return new RuntimeException("Reflection Exception.",
                        targetException);
            }
        }

        return new RuntimeException("Unexpected Checked Exception.", e);
    }

    public static Class<?> getOriginalClass(Class<?> clz) {
        Class<?> superclass = clz;

        while (superclass.getName().indexOf("_$$_jvst") != -1) {
            superclass = superclass.getSuperclass();

            if (superclass == null) {
                return superclass;
            }
        }

        return superclass;
    }
}

package cc.platform.business.course.export;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

//import com.xhxg.core.util.ReflectUtils;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/** 
* @ClassName: TableModel 
* @Description: TODO(描述) 
* @author chihaibo
* @date 2018年1月18日 下午1:18:47 
* @version V1.0
*  
*/
public class TableModel {
    private static Logger logger = LoggerFactory.getLogger(TableModel.class);
    private String name;
    private List<String> headers = new ArrayList<String>();
    private List data;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void addHeaders(String... header) {
        if (header == null) {
            return;
        }

        for (String text : header) {
            if (text == null) {
                continue;
            }

            headers.add(text);
        }
    }
    
    public void addHeader(List<String>headerList) {
        if (headerList == null) {
            return;
        }
        for (int i=0; i<headerList.size(); i++) {
        	String text = headerList.get(i);
            if (text == null) {
                continue;
            }
            headers.add(text);
        }
    }


    public List getData() {
		return data;
	}

	public void setData(List data) {
		this.data = data;
	}

	public int getHeaderCount() {
        return headers.size();
    }

    public int getDataCount() {
        return data.size();
    }

    public String getHeader(int index) {
        return headers.get(index);
    }

    public String getValue(int i, int j) {
        String header = getHeader(j);
        Object object = data.get(i);

        if (object instanceof Map) {
            return this.getValueFromMap(object, header);
        } else {
            return this.getValueReflect(object, header);
        }
    }

    public String getValueReflect(Object instance, String fieldName) {
        try {
            String methodName = ReflectUtils.getGetterMethodName(instance,
                    fieldName);
            Object value = ReflectUtils.getMethodValue(instance, methodName);

            return (value == null) ? "" : value.toString();
        } catch (Exception ex) {
            logger.info("error", ex);

            return "";
        }
    }

    public String getValueFromMap(Object instance, String fieldName) {
        Map<String, Object> map = (Map<String, Object>) instance;
        Object value = map.get(fieldName);

        return (value == null) ? "" : value.toString();
    }
}

	@Override
	public void getExportCourseTeacher(HttpServletRequest request, HttpServletResponse response,
			TableModel tableModel, int schId) {
		StringBuffer sb = new StringBuffer();
		List<String>headerList = new ArrayList<>();
		List<TreeMap<String,Object>> resultList = getCourseBodyList(schId);
		List<TreeMap<String,Object>> resultTeaNameList = new ArrayList<>();;
		TreeMap<String,Object> resultMap = new TreeMap<>();
		//获取任课教师resultTeaNameList
		for (int i = 0; i < resultList.size()-1; i++) {
			resultMap.put("aClassify",resultList.get(i).get("aClassify"));
			resultMap.put("bGrade",resultList.get(i).get("bGrade"));
			resultMap.put("cLassName",resultList.get(i).get("cLassName"));
			//获取map-》resultList.get(i)的长度，去掉前三个（aClassify，bGrade，cLassName），再获取dWeekTime+eTeaName和的一半
			for (int j = 0; j < (resultList.get(i).size()-3)/2; j++) {
				if (resultList.get(i).get("eTeaName"+j)!=""&&resultList.get(i).get("eTeaName"+j)!=null) {
					resultMap.put("eTeaName"+j,resultList.get(i).get("eTeaName"+j));
				}else {
					resultMap.put("eTeaName"+j,"");
				}
			}
			resultTeaNameList.add(resultMap);
			resultMap = new TreeMap<>();
		}
		sb.append("学部/院系/专业" + ",").append("年级" + ",").append("班级" + ",");
		//取出课程名称，课程存放在resultList的最后一个map中
		Map<String,Object> t = resultList.get(resultList.size()-1);
		Set<String> s=t.keySet();
		Iterator<String> it=s.iterator();
		while(it.hasNext()){
		String key=(String) it.next();
		Object value=t.get(key);
		sb.append(value.toString()+",");
		}
		String[] sbSplit = sb.toString().split(",");
		for (int i = 0; i < sbSplit.length; i++) {
			headerList.add(sbSplit[i]);
		}
		 try {
			 tableModel.setName("任课教师-导入模板");
			 tableModel.addHeader(headerList);
			 tableModel.setData(resultTeaNameList);
				excelExportService.excelExport(request, response, tableModel);
		} catch (Exception e) {
		            e.printStackTrace();
		}
	}

tikaber

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
POI导出

package cc.platform.business.course.export;import java.io.IOException;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;/** * @ClassName: ExcelExportS...
复制链接

扫一扫

POI导出

“相关推荐”对你有帮助么？