在操作過程中，需要對資料進行統計分析和彙總，導出成excel格式
核心思想：将需要導出的資料以集合的形式查詢出來，并填充到具體的excel表中。進行for循環填充，注意：不要在循環中查資料庫
public void exportReportTeachers(String name,String sex,String username){
Teacher teacher=new Teacher();
teacher.setName(name);
teacher.setUsername(username);
teacher.setSex(sex);
List<Teacher> teachers = teacherDAO.getConditionList(teacher);
//生成一個xls檔案
HSSFWorkbook work =new HSSFWorkbook();
HSSFSheet sheet=work.createSheet("教師統計表");
CellRangeAddress cellRangeAddress=new CellRangeAddress(0, 0, 0,5);
sheet.addMergedRegion(cellRangeAddress);
// 設定單元格樣式
HSSFCellStyle cellStyle1 = work.createCellStyle();// 建立表格樣式
// 設定字型居中
cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 設定字型
HSSFFont font1 = work.createFont();
font1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font1.setColor(HSSFColor.BLACK.index);
font1.setFontHeight((short) 250);
font1.setFontName("宋體");
font1.setBoldweight((short) 13);
font1.setColor(HSSFColor.BLACK.index);
cellStyle1.setFont(font1);
// 垂直居中
cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 加邊框
cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下邊框
cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左邊框
cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上邊框
cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右邊框
HSSFRow headRow = sheet.createRow(0);
headRow.setHeight((short) (42 * 15.625));
for (int i = 0; i <= 5; i++) {
HSSFCell cell = headRow.createCell(i);
cell.setCellValue("教師統計表");
cell.setCellStyle(cellStyle1);
}
// 設定單元格樣式
HSSFCellStyle cellStyle = work.createCellStyle();// 建立表格樣式
// 設定字型
HSSFFont font = work.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.BLACK.index);
// font.setFontHeight((short)250);
font.setFontName("宋體");
font.setBoldweight((short) 10);
font.setColor(HSSFColor.BLACK.index);
cellStyle.setFont(font);
// 設定背景色
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
cellStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 加邊框
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下邊框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左邊框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上邊框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右邊框
// 設定垂直居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFRow row = sheet.createRow(1);
// 建立單元格
HSSFCell cell = row.createCell(0);
cell.setCellValue("序号");
cell.setCellStyle(cellStyle);
HSSFCell cell1 = row.createCell(1);
cell1.setCellValue("教師Id");
cell1.setCellStyle(cellStyle);
HSSFCell cell2 = row.createCell(2);
cell2.setCellValue("教師姓名");
cell2.setCellStyle(cellStyle);
HSSFCell cell3 = row.createCell(3);
cell3.setCellValue("登入使用者名");
cell3.setCellStyle(cellStyle);
HSSFCell cell4 = row.createCell(4);
cell4.setCellValue("性别");
cell4.setCellStyle(cellStyle);
HSSFCell cell5 = row.createCell(5);
cell5.setCellValue("所任課程");
cell5.setCellStyle(cellStyle);
// 設定單元格樣式
HSSFCellStyle valueCellStyle = work.createCellStyle();// 建立表格樣式
HSSFFont valueFont = work.createFont();
valueFont.setColor(HSSFColor.BLACK.index);
valueFont.setFontName("宋體");
valueCellStyle.setFont(valueFont);
// 設定自動換行
// valueCellStyle.setWrapText(true);
// 垂直居中
valueCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
valueCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 加邊框
valueCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下邊框
valueCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左邊框
valueCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上邊框
valueCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右邊框
int rowNum = 2;
for (int j = 0; j < teachers.size(); j++) {
// 一個教師對應多門課程
HSSFRow valueRow = sheet.createRow(rowNum);// 前面已經寫兩行了
HSSFCell valueCell = valueRow.createCell(0);
valueCell.setCellValue(j + 1);// 設定序号
valueCell.setCellStyle(valueCellStyle);
HSSFCell valueCell1 = valueRow.createCell(1);
valueCell1.setCellValue(teachers.get(j).getId());// 教師id
valueCell1.setCellStyle(valueCellStyle);
HSSFCell valueCell2 = valueRow.createCell(2);
valueCell2.setCellValue(teachers.get(j).getName());// 教師姓名
valueCell2.setCellStyle(valueCellStyle);
HSSFCell valueCell3 = valueRow.createCell(3);
valueCell3.setCellValue(teachers.get(j).getUsername());// 教師使用者名
valueCell3.setCellStyle(valueCellStyle);
HSSFCell valueCell4 = valueRow.createCell(4);
valueCell4.setCellValue(teachers.get(j).getSexStr());//性别
valueCell4.setCellStyle(valueCellStyle);
HSSFCell valueCell5 = valueRow.createCell(5);
valueCell5.setCellValue(teachers.get(j).getCourseNames());//課程名稱
valueCell5.setCellStyle(valueCellStyle);
//行号，起始列号 ，行号 ，終止列号
rowNum++;
}
// =================指定列寬======================
sheet.setColumnWidth(0, 5 * 256);// 設定列寬
sheet.setColumnWidth(1, 40 * 256);// 設定列寬
sheet.setColumnWidth(2, 13 * 256);// 設定列寬
sheet.setColumnWidth(3, 25 * 256);// 設定列寬
sheet.setColumnWidth(4, 8 * 256);// 設定列寬
sheet.setColumnWidth(5, 30 * 256);// 設定列寬
// 設定頁邊距
sheet.setMargin(HSSFSheet.BottomMargin, 0);
sheet.setMargin(HSSFSheet.LeftMargin, (double) 0.4);
sheet.setMargin(HSSFSheet.RightMargin, (double) 0.8);
sheet.setMargin(HSSFSheet.TopMargin, 0);
/*
* //設定列印為橫闆 HSSFPrintSetup ps = sheet.getPrintSetup(); //true為橫向
* false為縱向 ps.setLandscape(true);
*/
File dir = new File(AttachmentUtil.getTempDir());
if (!dir.exists()) {
dir.mkdirs();
}
try {
String fileName = "教師統計表";
File file = new File(dir.getAbsolutePath() + "/" + UUID.getUUID());
OutputStream out = new FileOutputStream(file);
work.write(out);
FileUtil.download(file, fileName + ".xls");
} catch (Exception e) {
new BusinessException("檔案傳輸出錯");
e.printStackTrace();
}
}
![前端面試之前要準備的那些事[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)