package com.thhy.general.utils;
|
|
import com.thhy.general.annotations.Excel;
|
import org.apache.poi.hssf.usermodel.*;
|
import org.apache.poi.hssf.util.HSSFColor;
|
import org.apache.poi.ss.usermodel.*;
|
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.io.OutputStream;
|
import java.lang.annotation.Annotation;
|
import java.lang.reflect.Field;
|
import java.lang.reflect.Method;
|
import java.net.URLEncoder;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
|
public class ExcelUtils {
|
public static HSSFWorkbook create(List list, Class clazz){
|
//声明一个工作簿
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
//生成一个表格，设置表格名称为"学生表"
|
HSSFSheet sheet = workbook.createSheet();
|
sheet.setDefaultColumnWidth(20);
|
|
|
Field[] fields = clazz.getDeclaredFields();
|
Field[] needs = new Field[fields.length];
|
List<Field> fieldList = new ArrayList<>();
|
for(Field f : fields){
|
Annotation[] annotations = f.getAnnotations();
|
for(Annotation annotation : annotations){
|
if(annotation.annotationType().getName().equals(Excel.class.getName())){
|
needs[getSort(f)] = f;
|
}
|
}
|
}
|
|
for(Field f : needs){
|
if(f!=null){
|
System.out.println(getSort(f)+"----"+f.getName());
|
fieldList.add(f);
|
}
|
}
|
|
//创建表头
|
HSSFRow rows = sheet.createRow(0);
|
rows.setHeight((short) 403);
|
for(int i =0;i<fieldList.size();i++){
|
HSSFCell cell = rows.createCell(i);
|
sheet.setColumnWidth(i,getWidth(fieldList.get(i))*1311);
|
cell.setCellStyle(HeaderStyle(workbook, HorizontalAlignment.CENTER, VerticalAlignment.CENTER,11,(short) -1, HSSFColor.HSSFColorPredefined.GREY_40_PERCENT.getIndex(),true));
|
cell.setCellValue(getTitle(fieldList.get(i)));
|
}
|
|
for(int i = 0;i<list.size();i++){
|
HSSFRow row = sheet.createRow(i+1);
|
row.setHeight((short) 375);
|
for(int j =0;j<fieldList.size();j++){
|
HSSFCell cell = row.createCell(j);
|
Field field = fieldList.get(j);
|
cell.setCellValue(getValue(field,list.get(i),i));
|
}
|
}
|
|
return workbook;
|
}
|
|
public static void downExcel(List list, Class clazz, HttpServletResponse response, String fileName){
|
HSSFWorkbook workbook = create(list,clazz);
|
OutputStream outputStream = null;
|
try {
|
fileName = fileName+".xls";
|
//设置编码、输出文件格式
|
response.setContentType("application/msexcel");
|
response.setCharacterEncoding("utf-8");
|
fileName = URLEncoder.encode(fileName,"UTF-8");
|
response.setHeader("Content-Disposition","attachment;filename="+fileName);
|
outputStream = response.getOutputStream();
|
workbook.write(outputStream);
|
//byte[] bytes = workbook.getBytes();
|
//outputStream.write(bytes);
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(outputStream!=null){
|
try {
|
outputStream.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
}
|
|
public static void downExcel(List list,List list1, HttpServletResponse response, String fileName){
|
HSSFWorkbook workbook = create(list,list1);
|
OutputStream outputStream = null;
|
try {
|
fileName = fileName+".xls";
|
//设置编码、输出文件格式
|
response.setContentType("application/msexcel");
|
response.setCharacterEncoding("utf-8");
|
fileName = URLEncoder.encode(fileName,"UTF-8");
|
response.setHeader("Content-Disposition","attachment;filename="+fileName);
|
outputStream = response.getOutputStream();
|
workbook.write(outputStream);
|
//byte[] bytes = workbook.getBytes();
|
//outputStream.write(bytes);
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(outputStream!=null){
|
try {
|
outputStream.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
}
|
|
public static void saveExcel(){
|
|
}
|
|
public static int getSort(Field f){
|
Excel excels = f.getAnnotation(Excel.class);
|
return excels.sort();
|
}
|
|
public static boolean getDict(Field f){
|
Excel excel = f.getAnnotation(Excel.class);
|
return excel.dict();
|
}
|
|
public static boolean getSeq(Field f){
|
Excel excel = f.getAnnotation(Excel.class);
|
return excel.seq();
|
}
|
|
public static int getWidth(Field f){
|
Excel excels = f.getAnnotation(Excel.class);
|
return excels.width();
|
}
|
|
public static String getTitle(Field f){
|
Excel excels = f.getAnnotation(Excel.class);
|
return excels.title();
|
}
|
|
public static String getFormat(Field f){
|
Excel excels = f.getAnnotation(Excel.class);
|
return excels.patter();
|
}
|
|
public static String getValue(Field f,Object o,int i){
|
String value = "";
|
f.setAccessible(true);
|
try {
|
if(f.getType().getName().equals(Date.class.getName())){
|
//Date类型
|
String format = getFormat(f);
|
SimpleDateFormat sdf = new SimpleDateFormat(format);
|
value = f.get(o)!=null?sdf.format(f.get(o)):"";
|
}else{
|
if(getDict(f)){
|
try {
|
Method method = o.getClass().getMethod("get"+f.getName().substring(0,1).toUpperCase()+f.getName().substring(1,f.getName().length()),null);
|
value = (String) method.invoke(o,null);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}else if(getSeq(f)){
|
value = (i+1)+"";
|
}else{
|
value = f.get(o)!=null?f.get(o).toString():"";
|
}
|
//value = f.get(o)!=null?f.get(o).toString():"";
|
}
|
} catch (IllegalAccessException e) {
|
e.printStackTrace();
|
}
|
return value;
|
}
|
|
public static HSSFCellStyle HeaderStyle(HSSFWorkbook workbook, HorizontalAlignment alignment, VerticalAlignment alignment1, int fontSize, short color, short backColor, boolean autotext){
|
HSSFCellStyle centerBlue = workbook.createCellStyle();
|
centerBlue.setAlignment(alignment);
|
centerBlue.setVerticalAlignment(alignment1);
|
HSSFFont centerBluefont = workbook.createFont();
|
centerBluefont.setFontHeightInPoints((short) fontSize);
|
if(color!=-1){
|
centerBluefont.setColor(color);
|
}
|
centerBlue.setFont(centerBluefont);
|
centerBlue.setWrapText(autotext);
|
if(backColor!=-1){
|
centerBlue.setFillForegroundColor(backColor);
|
centerBlue.setFillPattern(FillPatternType.SOLID_FOREGROUND);
|
}
|
centerBlue.setBorderBottom(BorderStyle.THIN);
|
centerBlue.setBorderLeft(BorderStyle.THIN);
|
centerBlue.setBorderTop(BorderStyle.THIN);
|
centerBlue.setBorderRight(BorderStyle.THIN);
|
centerBlue.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
|
centerBlue.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
|
centerBlue.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
|
centerBlue.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
|
return centerBlue;
|
}
|
|
public static HSSFWorkbook create(List<Map<String,Object>> list,List<TitleField> headerList){
|
//声明一个工作簿
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
|
//生成一个表格，设置表格名称为"学生表"
|
HSSFSheet sheet = workbook.createSheet();
|
sheet.setDefaultColumnWidth(20);
|
|
//创建表头
|
HSSFRow rows = sheet.createRow(0);
|
rows.setHeight((short) 403);
|
for(int i =0;i<headerList.size();i++){
|
HSSFCell cell = rows.createCell(i);
|
sheet.setColumnWidth(i,3*1311);
|
cell.setCellStyle(HeaderStyle(workbook, HorizontalAlignment.CENTER, VerticalAlignment.CENTER,11,(short) -1, HSSFColor.HSSFColorPredefined.GREY_40_PERCENT.getIndex(),true));
|
cell.setCellValue(headerList.get(i).getTitle());
|
}
|
|
for(int i = 0;i<list.size();i++){
|
HSSFRow row = sheet.createRow(i+1);
|
row.setHeight((short) 375);
|
for(int j =0;j<headerList.size();j++){
|
HSSFCell cell = row.createCell(j);
|
if("seq".equals(headerList.get(j).getField())){
|
cell.setCellValue(i+1);
|
}else{
|
cell.setCellValue(list.get(i).get(headerList.get(j).getField()).toString());
|
}
|
|
}
|
}
|
|
return workbook;
|
}
|
}
|
