导出excel工具类，如果有问题，请大家给我说哈，样式可以自己调。这个工具类导出，只针对简单excel，不支持合并单元格之类。
public class ExportUtil {
/**
*
* @param fileName
* @param sheetNames 多个sheet
* @param titles 多个sheet 对应的多个titles
* @param datas 多个sheet的数据
* @param response 下载的话，返回流
*/
public static void export(String fileName, String[] sheetNames, List<String[]> titles,List<List<?>> datas,HttpServletResponse response){
if(fileName == null || fileName.trim().length() == 0 || sheetNames == null
|| sheetNames.length == 0 || titles == null || titles.size() == 0 || datas == null | datas.size() == 0)
throw new RuntimeException("the param is not null");
int sheetLength = sheetNames.length;
int titleLength = titles.size();
int dataLength = datas.size();
if(!(sheetLength == titleLength && titleLength == dataLength))
throw new RuntimeException("the sheetNames number must equal the titles number and the datas number ");
HSSFWorkbook wb = new HSSFWorkbook();
try {
for (int sheet = 0;sheet < sheetLength; sheet ++){
HSSFSheet hssfSheet = wb.createSheet(sheetNames[sheet]);
String[] currentTitles = titles.get(sheet);
HSSFRow row0 = hssfSheet.createRow(0);
for (int title = 0; title < currentTitles.length; title++){
HSSFCell cell = row0.createCell(title);
cell.setCellValue(currentTitles[title]);
cell.setCellStyle(getDefaultCellStyle(wb));
}
//开始写入数据
int rowNum = 1;
List<?> data = datas.get(sheet);
for (Object da : data) {
Class daC = da.getClass();
//所有字段
Field[] fields = daC.getDeclaredFields();
Class superclass = daC.getSuperclass();
Field[] declaredFields = null;
if(!superclass.getSimpleName().equals("Object")){
declaredFields = superclass.getDeclaredFields();
}
if(declaredFields != null){
int lenth1 = fields.length;
int lenth2 = declaredFields.length;
fields = Arrays.copyOf(fields, lenth1+ lenth2);
System.arraycopy(declaredFields, 0, fields, lenth1 , lenth2);
}
List<String> rowFileds = new ArrayList<>();
for (Field field : fields) {
field.setAccessible(true);
ExportSortAnnotation annotation = field.getAnnotation(ExportSortAnnotation.class);
if(annotation == null)
continue;
int sort = annotation.sort();
String value = "";
Object o = field.get(da);
if(o != null){
if(o instanceof Date){
Date d = (Date) o;
value = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.CHINA).format(d);
} else {
value = o.toString();
}
}
rowFileds.add(sort + "," + value);
}
Collections.sort(rowFileds);
HSSFRow row = hssfSheet.createRow(rowNum);
for (int rowFiled = 0; rowFiled < rowFileds.size(); rowFiled++) {
row.createCell(rowFiled).setCellValue(rowFileds.get(rowFiled).split(",")[1]);
}
rowNum++;
}
}
// buildExcelToProject(fileName,wb);
downloadExcel(fileName,wb,response);
} catch (Exception e){
e.printStackTrace();
}
}
//创建表头
public static void createTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] titles){
HSSFRow row = sheet.createRow(0);
for (int i = 0; i<titles.length ; i++) {
HSSFCell cell = row.createCell(i);
// HSSFCellStyle style = new h
cell.setCellValue(titles[i]);
}
}
//生成excel文件到本地
public static void buildExcelToProject(String filename, HSSFWorkbook workbook) throws Exception{
// FileOutputStream fos = new FileOutputStream(filename);
// workbook.write(fos);
// fos.flush();
// fos.close();
}
//浏览器下载excel
public static void downloadExcel(String filename, HSSFWorkbook workbook, HttpServletResponse response) throws Exception{
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename, "utf-8"));
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
public static void main(String[] args) {
String fileName = "测试文件";
String[] sheetNames = {"sheet1"};
String[] title = {"长度","名称","金额","生日"};
List<String[]> titles = new ArrayList<>(); titles.add(title);
List<List<?>> datas = new ArrayList<>();
List<ExportEntity1> data = new ArrayList<>();datas.add(data);
data.add(new ExportEntity1("名称", BigDecimal.ZERO,new Date(),1));
// export(fileName,sheetNames,titles,datas);
}
/**
* 水平居中、垂直居中
* 字体：宋体
* 字体大小：16号
* 加粗
* @param workbook
* @return
*/
public static CellStyle getStyle(HSSFWorkbook workbook) {
CellStyle cellstyle=workbook.createCellStyle();
cellstyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
Font font=workbook.createFont();//字体
font.setFontName("宋体");//字体
font.setFontHeightInPoints((short)16);//字号
font.setBold(true);//加粗
cellstyle.setFont(font);
setBorderStyle(cellstyle);
return cellstyle;
}
/**
* 获取默认的cell表格样式，加边框，水平居中，垂直居中
* @param workbook
* @return
*/
public static CellStyle getDefaultCellStyle(HSSFWorkbook workbook) {
CellStyle style=workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);//水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
setBorderStyle(style);
return style;
}
/**
* 边框样式
* @param style
*/
public static void setBorderStyle(CellStyle style) {
style.setBorderBottom(BorderStyle.THIN); //下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
}
/**
* 奇数行
* 背景颜色为黄色
* @param style
*/
public static void setCellStyleYellow(CellStyle style) {
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
/**
* 偶数行
* 背景颜色为LIME
* @param style
*/
public static void setCellStyleLime(CellStyle style) {
style.setFillForegroundColor(IndexedColors.LIME.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
/**
* 字体设置红色
* @param workbook
* @param style
*/
public static void setFontRedColor(HSSFWorkbook workbook,CellStyle style) {
Font font=workbook.createFont();//字体
font.setColor(IndexedColors.RED.getIndex());
style.setFont(font);
}
}因为反射获取类，不能判断title和字段的对应关系，所以需要写下面的注解
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface ExportSortAnnotation {
int sort() default 1;
}在传入的datas的具体类的字段上加上注解，以达到，title的字段和具体类的字段对应
@Data
public class ExportEntity1 {
@ExportSortAnnotation(sort = 2)
private String name;
@ExportSortAnnotation(sort = 3)
private BigDecimal amount;
@ExportSortAnnotation(sort = 4)
private Date birthday;
@ExportSortAnnotation(sort = 1)
private int length;
public ExportEntity1(String name, BigDecimal amount, Date birthday, int length) {
this.name = name;
this.amount = amount;
this.birthday = birthday;
this.length = length;
}
}然后测试方法，需要放到controller里面
)
@GetMapping("/exportUtil")
public void exportUtil(HttpServletResponse response) {
String fileName = "测试文件.xls";
String[] sheetNames = {"sheet1","sheet2"};
String[] title = {"长度","名称","金额","生日"};
String[] title2 = {"长度","名称","金额","生日"};
List<String[]> titles = new ArrayList<>(); titles.add(title); titles.add(title2);
List<List<?>> datas = new ArrayList<>();
List<ExportEntity1> data = new ArrayList<>();
data.add(new ExportEntity1("名称", BigDecimal.ZERO,new Date(),1));
List<ExportEntity1> data2 = new ArrayList<>();
data2.add(new ExportEntity1("名称2", new BigDecimal(10),new Date(),2));
datas.add(data);
datas.add(data2);
ExportUtil.export(fileName,sheetNames,titles,datas,response);
}效果