导出excel
@GetMapping("/{hid}")
public void userLoginExport(HttpServletResponse response,@PathVariable("hid") Long hid) {
// 查询需要导出的数据
List<Map<String,String>> list = efileService.getExportDataByhid(hid);
if(list == null || list.size() <= 0){
throw new SelectNoFindException("导出失败,数据为空！",null);
}
// 创建工作薄
Properties prop = System.getProperties();
String os = prop.getProperty("os.name");
String filePath = "";
if(os.startsWith("Win")) {
filePath = ContentConstant.EFILE_WINDOWS;
}else{
filePath = ContentConstant.EFILE_LINUX;
}
// 在这里是直接读取的Excel模板,不用再创建表头
File file = new File(filePath + EfileConstant.EFILE_MODEL_NAME);
InputStream io = null;
HSSFWorkbook workbook = null;
try {
io = new FileInputStream(file);
workbook = new HSSFWorkbook(io);
}catch (Exception e){
e.printStackTrace();
throw new SelectNoFindException("导出失败，找不到默认模板！",e.getMessage());
}
workbook.setSheetName(0,EfileConstant.EXPORT_NAME);
// 读取excel中的内容
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFRow row = null;
HSSFCell cell = null;
// 设置模板样式，锁定内容
HSSFCellStyle locked = createStyle(workbook);
int rowNumber = 2;
String value = null;
for(Map<String,String> m : list){
if(m != null){
row = sheet.getRow(rowNumber++);
for(int i = 0,n = 1; i < EfileConstant.EXPORT_DEFAULT_PROPERTY.length;i++,n++){
value = null;
cell = row.createCell(n);
cell.setCellStyle(locked);
value = String.valueOf(m.get(EfileConstant.EXPORT_DEFAULT_PROPERTY[i]));
if(value != null && !value.equals("null")){
// 当等于性别时
if(EfileConstant.EXPORT_DEFAULT_PROPERTY[i].equals("sex")){
if(Integer.valueOf(value) == UserConstant.SEX_MAN ){
cell.setCellValue(UserConstant.SEX_MAN_ALIAS);
}else{
cell.setCellValue(UserConstant.SEX_WOMAN_ALIAS);
}
}else{
cell.setCellValue(value);
}
}
cell = null;
}
}
row = null;
}
//设置返回header
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+EfileConstant.EXPORT_NAME + System.currentTimeMillis() +".xls");
try{
workbook.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
创建Excel模板 // 创建工作薄
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作表sheet HSSFSheet sheet = workbook.createSheet(efileModel.getName()); // 创建单元格样式 HSSFCellStyle style = workbook.createCellStyle(); // 加粗 HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 单元格数据类型为字符串 style.setDataFormat(workbook.createDataFormat().getFormat("@")); style.setFont(font); // 文字水平居中 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 文字垂直居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 设置未锁定 style.setLocked(false); sheet.setDefaultColumnStyle(0, style); // 单元格宽度自适应 //sheet.autoSizeColumn(1, true); // 合并单元格cellRangAddress四个参数，第一个起始行，第二终止行，第三个起始列，第四个终止列 for(int z = 0; z < CELL_DEFAULT_LENTH ; z++){ // 设置指定列度 sheet.setColumnWidth(z, 256*15);
// 将该列的第一行和第二行单元格合并 sheet.addMergedRegion(new CellRangeAddress(0, 1, z, z)); }// 默认表头长度 int number = 4;
// 有两行表头，第一行需要将某两列的单元格合并，而第二行不需要合并 if(list_twoCell.size() > 0){ for(int y = 0; y < list_twoCell.size() ; y++){ sheet.addMergedRegion(new CellRangeAddress(0, 0, (number+y*2), (number+y*2+1))); } }// 创建表头（表头的高度） HSSFRow row1 = sheet.createRow(0); row1.setHeight((short) 750); HSSFRow row2 = sheet.createRow(1); row1.setHeight((short) 600); // 单元格 HSSFCell cell = null; HSSFCell cell2 = null;
// 笨方法，写固定的表头 cell = row1.createCell(0); cell.setCellValue("ID"); cell = row1.createCell(1); cell.setCellValue("姓名"); cell.setCellStyle(style); cell = row1.createCell(2); cell.setCellValue("性别"); cell.setCellStyle(style);cell = row1.createCell(3); cell.setCellValue("家长手机号"); cell.setCellStyle(style); cell = row1.createCell(4); cell.setCellValue("籍贯"); cell.setCellStyle(style);// 如果存在手机号，则记录手机号的表头索引 int phone_index = 0;// 如果存在籍贯，则记录籍贯的表头索引 int nativaPlace_index = 0;
// 动态设置某两列中第一行合并了两个单元格，而第二行未合并的表头
for(int n = 0,size = 0; n < twoCellLens; n = n+2,size++){ cell = row1.createCell(n + number); cell.setCellValue(list_twoCell.get(size)); cell.setCellStyle(style); cell2 = row2.createCell(n + number); cell2.setCellValue("右眼(OD)"); cell2.setCellStyle(style); cell2 = row2.createCell(n + 1 + number); cell2.setCellValue("左眼(OS)"); cell2.setCellStyle(style); }// 数据校验(手机号长度) lenthValidate(sheet,3,3,"11");//加载下拉列表内容 String[] textList = null; // 性别 setList(sheet, new String[]{UserConstant.SEX_MAN_ALIAS,UserConstant.SEX_WOMAN_ALIAS}, 2,2);// 籍贯 setList(sheet, RaceConstant.provinces, 4, 4);// 设置单元格数据格式为字符串 for (int i = 2; i < 10000; i++) { row1 = sheet.createRow(i); // 设置行高 row1.setHeightInPoints(25); for(int m = 0;m <number;m++){ cell = row1.createCell(m); cell.setCellStyle(style); } } // 隐藏第一列 sheet.setColumnHidden((short)0,true); // 创建文件 Properties prop = System.getProperties(); String os = prop.getProperty("os.name"); String filePath = ""; if(os.startsWith("Win")) { filePath = ContentConstant.EFILE_WINDOWS; }else{ filePath = ContentConstant.EFILE_LINUX; } String fileName = System.currentTimeMillis() +".xls"; File f = new File(filePath + fileName); FileOutputStream fout = null; try { f.createNewFile(); fout = new FileOutputStream(f); workbook.write(fout); }catch(IOException e){ e.printStackTrace(); throw new SelectNoFindException("生成筛查模板失败！",e.getMessage()); }finally { try { if(fout != null){ fout.close(); } if(workbook != null) { workbook.close(); } }catch(IOException e){ e.printStackTrace(); throw new SelectNoFindException("生成筛查模板失败！",e.getMessage()); } }
添加校验
/** * @Author holley * @Description 加载下拉列表内容 * @Date 2018/11/2 16:27 * @Param [sheet, textList, index] * @return void */ private void setList(HSSFSheet sheet, String[] textList, int firstCol,int lastCol) { //下拉列表 HSSFDataValidationHelper helper = new HSSFDataValidationHelper(sheet); DataValidationConstraint dvConstraint1 = helper.createExplicitListConstraint(textList); dvConstraint1.setExplicitListValues(textList); //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList addressList1 = new CellRangeAddressList( 2, 10000, firstCol,lastCol); //数据有效性对象 DataValidation dataValidation1 = helper.createValidation(dvConstraint1, addressList1); sheet.addValidationData(dataValidation1); } private void lenthValidate(HSSFSheet sheet,int firstCol,int lastCol,String limitLenth) { //DVConstraint constraint = DVConstraint.createCustomFormulaConstraint(""); DVConstraint constraint = DVConstraint.createNumericConstraint( DataValidationConstraint.ValidationType.TEXT_LENGTH, DataValidationConstraint.OperatorType.BETWEEN, "0", limitLenth); //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList( 2, 10000, firstCol,lastCol); //数据有效性对象 DataValidation validation = new HSSFDataValidation(regions,constraint); validation.createErrorBox("超出限制长度！", "格式错误，长度不能超过"+ limitLenth +"位"); sheet.addValidationData(validation); }
