private final static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
/**
* @param list 导出的数据list
* @param clazz 导出的对象class
* @param fileName 导出的文件名称
* @param response reponse
* @param <T>
*/
public static <T> void exportExcel(List<T> list, Class<T> clazz, String fileName, HttpServletResponse response) {
SXSSFWorkbook workBook = getWorkBook(list, clazz);
try {
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8"));
OutputStream output = response.getOutputStream();
BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
bufferedOutPut.flush();
workBook.write(bufferedOutPut);
bufferedOutPut.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private static <T> SXSSFWorkbook getWorkBook(List<T> list, Class<T> clazz) {
SXSSFWorkbook wb = new SXSSFWorkbook(100);
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
row.setHeightInPoints(23);
Field[] declaredFields = getAllFields(clazz);
//添加序号
Cell cellOrder = row.createCell(0);
cellOrder.setCellValue("序号");
cellOrder.setCellStyle(titleStyle(wb));
sheet.setColumnWidth(0, 10 * 256);
//设置表头
for (Field field : declaredFields) {
Excel annotation = field.getAnnotation(Excel.class);
if (annotation != null) {
Cell cell = row.createCell(Integer.valueOf(annotation.orderNum()) );
cell.setCellValue(annotation.name());
cell.setCellStyle(titleStyle(wb));
//设置列的宽度
sheet.setColumnWidth(Integer.valueOf(annotation.orderNum()), (int)annotation.width() * 256);
}
}
// 遍历集合数据，产生数据行
Iterator<?> it = list.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
Cell cell = row.createCell(0);
cell.setCellStyle(orderStyle(wb));
cell.setCellValue(index);
row.setHeightInPoints(18);
T t = (T) it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = getAllFields(t.getClass());
for (Field field : fields) {
Excel annotation = field.getAnnotation(Excel.class);
if (annotation != null) {
cell = row.createCell(Integer.valueOf(annotation.orderNum()) );
cell.setCellStyle(contentStyle(wb));
String fieldName = field.getName();
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
try {
Method getMethod = t.getClass().getMethod(getMethodName,
new Class[]{});
Object value = getMethod.invoke(t, new Object[]{});
cell.setCellValue(null == value ? "" : String.valueOf(value));
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
return wb;
}
//设置标题样式
private static CellStyle titleStyle(SXSSFWorkbook wb) {
CellStyle cellStyle = contentStyle(wb);
//设置居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置单元格背景颜色
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//设置单元格填充样式(使用纯色背景颜色填充)
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置字体加粗
Font font = wb.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 12);
//设置字体
font.setFontName("宋体");
cellStyle.setFont(font);
return cellStyle;
}
//设置单元格样式
private static CellStyle contentStyle(SXSSFWorkbook wb) {
//给单元格设置样式
CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 11);
//设置字体
font.setFontName("宋体");
//给字体设置样式
cellStyle.setFont(font);
//字体设置边框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
//设置垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return cellStyle;
}
//设置单元格样式
private static CellStyle orderStyle(SXSSFWorkbook wb) {
CellStyle cellStyle = contentStyle(wb);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
return cellStyle;
}
private static <T> Field[] getAllFields(Class<T> clazz) {
Field[] declaredFields = clazz.getDeclaredFields();
Class superClass = clazz.getSuperclass();
if (!superClass.equals(Object.class)) {
Field[] superFields = superClass.getDeclaredFields();
int sonLength = declaredFields.length;
int superLength = superFields.length;
// 合并两个数组
Field[] newFields = new Field[sonLength + superLength];
System.arraycopy(declaredFields, 0, newFields, 0, sonLength);
System.arraycopy(superFields, 0, newFields, sonLength, superLength);
return newFields;
}
return declaredFields;
}
private static final String XLS = "xls";
private static final String XLSX = "xlsx";
/**
* 根据文件后缀名类型获取对应的工作簿对象
* @param inputStream 读取文件的输入流
* @param fileType 文件后缀名类型（xls或xlsx）
* @return 包含文件数据的工作簿对象
* @throws IOException
*/
public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
Workbook workbook = null;
if (fileType.equalsIgnoreCase(XLS)) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileType.equalsIgnoreCase(XLSX)) {
workbook = new XSSFWorkbook(inputStream);
}
return workbook;
}
//导入
public void add(MultipartFile file) {
String fileName = file.getOriginalFilename();
File file1 = new File(fileName);
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(2);
List<Bill> bills = ExcelImportUtil.importExcel(file1, Bill.class, params);
for (Bill bill : bills) {
billlMapper.insert(bill);
}
}
//导出
@RequestMapping("/exportExcelDc")
public void exportExcelDc(HttpServletResponse response) throws Exception {
List<Bill> list = billService.list();
ExcelUtils.exportExcel(list, Bill.class, "数据", response);
}
//另一种导出
@RequestMapping("/exportExcelDc1")
public void exportExcelDc1(HttpServletResponse response) throws Exception {
ExportParams params = new ExportParams();
params.setTitle("年度账单");
params.setSheetName("年度账单表");
params.setType(ExcelType.XSSF);
List<Bill> list = billService.list();
Workbook workbook = ExcelExportUtil.exportExcel(params, Bill.class, list);
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("测试" + ".xls", "UTF-8"));
OutputStream output = response.getOutputStream();
BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
bufferedOutPut.flush();
workbook.write(bufferedOutPut);
bufferedOutPut.close();
}
// 注解
@Excel(name = "序号",orderNum = "0")
