Excel下载

准备工作
List hospitalOutBOS = medicalCareService.exportHospitalInfo(hospitalQryBO);
String filename = “医院信息”;
String[] title = null;
if(“HCV00002”.equals(hospitalQryBO.getCategory())){//医院
title = new String[]{“序号:id”,“编号:hospitalCode”,“名称:hospitalName”,“医疗机构性质:hospitalNatureName”,“国家/地区:hospitalNationName”,
“医院所在省:hospitalProvinceName”,“医院所在市:hospitalCityName”,“医院所在区:hospitalCountyName”,“医院签约状态:signStatus”,“创建日期:createTime”};
}else if(“HCV00001”.equals(hospitalQryBO.getCategory())){//集团
title = new String[]{“序号:id”,“编号:hospitalCode”,“名称:hospitalName”,“医院签约状态:signStatus”,“创建日期:createTime”};
}else{
throw new ValidationException(“参数异常”);
}
HSSFWorkbook wb = excelExportUtil.exportDataToExcelCom(response,filename,title,hospitalOutBOS);
response.setContentType(“application/vnd.ms-excel;charset=UTF-8”);
response.setCharacterEncoding(“UTF-8”);
try {
response.setHeader(“Content-Disposition”, “attachment;filename=”+
java.net.URLEncoder.encode(“医院信息数据导出.xls”,“UTF-8”));
} catch (UnsupportedEncodingException e) {
logger.info(“The Exception: {}”,e.getMessage());
}
try {
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
}catch(IOException e){
logger.info(“The Exception: {}”,e.getMessage());
}
通过反射编写公共方法exportDataToExcelCom
public HSSFWorkbook exportDataToExcelCom(HttpServletResponse response, String filename, String[] title, List<?> dataList) throws IOException {
//创建一个workbook,对应一个excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
if(null != dataList && dataList.size() > 0 && null != title && title.length > 0){
workbook = exportDataExcelCom(workbook,filename, title, dataList);
}
return workbook;
}

---

private HSSFWorkbook exportDataExcelCom(HSSFWorkbook workbook, String filename, String[] title, List<?> dataList) {
//在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = workbook.createSheet(filename);
//在sheet中添加表头第0行
HSSFRow row = sheet.createRow(0);
//创建样式
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);//设置水平居中
HSSFFont hssfFont = workbook.createFont();//生成字体
hssfFont.setBold(true);//设置字体加粗
hssfFont.setFontHeightInPoints((short) 10);//设置字体大小
hssfFont.setFontName(“微软雅黑”);
style.setFont(hssfFont);
//声明列对象
HSSFCell cell = null;
List plist = new ArrayList<>();
List tlist = new ArrayList<>();
for(String str : title){
String[] split = str.split("?;
tlist.add(split[0]);
plist.add(split[1]);
}
//创建标题
for (int i = 0; i < tlist.size(); i++) {
cell = row.createCell(i);
cell.setCellValue(tlist.get(i));
cell.setCellStyle(style);
}
// 设置内容样式
HSSFCellStyle alignCenterStyle = workbook.createCellStyle();
alignCenterStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
HSSFCellStyle alignRightStyle = workbook.createCellStyle();
alignRightStyle.setAlignment(HorizontalAlignment.RIGHT); // 设置水平居右
for(int i = 0; i < dataList.size(); i++) {
String[] str = getValues(dataList.get(i),plist);
int index = i+1;
str[0] = index+"";
row = sheet.createRow(index);
for(int j = 0; j < str.length; j++){
HSSFCell hcell = row.createCell(j);
hcell.setCellValue(str[j]);
hcell.setCellStyle(alignCenterStyle);
}
}
return workbook;
}

这边真正用到反射的东西
/**

• Description: 通过反射获取属性值
• @author:
• @date: 2019/8/2 16:32
• @param:
• @return:
  */
  private String[] getValues(Object ee, List plist) {
  Field[] fields = ee.getClass().getDeclaredFields();
  String[] values = new String[plist.size()];
  for(int j = 0; j < plist.size(); j++) {
  for (int i = 0; i < fields.length; i++) {
  Field field = fields[i];
  field.setAccessible(true);
  String name = field.getName();
  try {
  Object o = field.get(ee);
  if (Objects.equals(plist.get(j), name)) {
  if(null == o){
  values[j] = " ";
  }else {
  if (field.getType().getName().equalsIgnoreCase(“java.util.Date”)) {
  SimpleDateFormat sdf = new SimpleDateFormat(“yyyy-MM-dd”);
  String value = sdf.format(o);
  values[j] = value;
  }else{
  values[j] = o.toString();
  }
  }
  }
  } catch (IllegalAccessException e) {
  e.printStackTrace();
  }
  }
  }
  return values;
  }