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();
}
}