public void queryStudentInfoPolling(HttpServletResponse responese) throws IOException {
//新建工作簿
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
//新建工作表
XSSFSheet sheet = xssfWorkbook.createSheet("2020技术栈");
//指定合并开始行、合并结束行 合并开始列、合并结束列
CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, 0, 3);
sheet.addMergedRegion(rangeAddress);
XSSFRow row = sheet.createRow(0);//创建行,指定起始行号,从0开始
for(int i=0;i<4;i++){
//创建样式对象
CellStyle style = xssfWorkbook.createCellStyle();
if(i==0){
XSSFCell cell = row.createCell(0);//创建单元格,指定起始列号,从0开始
cell.setCellValue("2021年大数据开发文档");
//设置居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
//设置字体
XSSFFont font = xssfWorkbook.createFont();
//font.setFontName("黑体");
font.setFontHeightInPoints((short) 12);//设置字体大小
style.setFont(font);
cell.setCellStyle(style);
}else{
XSSFCell cell = row.createCell(i);
//设置边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//style.setWrapText(true);//自动换行
cell.setCellStyle(style);
}
}
//创建数据表头
XSSFRow row1 = sheet.createRow(1);//创建行,指定起始行号,从1开始
for(int j=0;j<4;j++){
XSSFCell cells = row1.createCell(j);//创建列
switch (j) {
case 0:
cells.setCellValue("姓名");
break;
case 1:
cells.setCellValue("性别");
break;
case 2:
cells.setCellValue("年龄");
break;
case 3:
cells.setCellValue("电话");
break;
}
CellStyle styles = xssfWorkbook.createCellStyle();
//设置居中
styles.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
styles.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
//设置边框
styles.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
styles.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
styles.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
styles.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//styles.setWrapText(true);//自动换行
//设置字体
XSSFFont fonts = xssfWorkbook.createFont();
styles.setFont(fonts);
//设置背景
styles.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//styles.setFillForegroundColor((short) 18);// 设置背景色
cells.setCellStyle(styles);
}
//创建数据表体
List<UserInfo> userList = new ArrayList();
UserInfo userInfo = new UserInfo();
userInfo.setName("张三");
userInfo.setSex("男");
userInfo.setAge("22");
userInfo.setTel("18392863010");
userList.add(userInfo);
for(int n=0;n<userList.size();n++){
UserInfo info = userList.get(n);
sheet.autoSizeColumn(1);
XSSFRow rown = sheet.createRow(n+2);
for(int m=0;m<4;m++){
//设置列宽
sheet.setColumnWidth(m, sheet.getColumnWidth(m) * 18/10);
XSSFCell celln = rown.createCell(m);//创建列
switch (m) {
case 0:
celln.setCellValue(info.getName());
break;
case 1:
celln.setCellValue(info.getSex());
break;
case 2:
celln.setCellValue(info.getAge());
break;
case 3:
celln.setCellValue(info.getTel());
break;
}
CellStyle stylesc = xssfWorkbook.createCellStyle();
//设置居中
stylesc.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
stylesc.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
//设置边框
stylesc.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
stylesc.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
stylesc.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
stylesc.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//stylesc.setWrapText(true);//自动换行
//设置字体
XSSFFont fonts = xssfWorkbook.createFont();
stylesc.setFont(fonts);
//设置背景
stylesc.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//stylesc.setFillForegroundColor((short) 18);// 设置背景色
celln.setCellStyle(stylesc);
}
}
//将excel的数据写入文件
ByteArrayOutputStream byteOutputStream = null;
byte[] bytes = null;
try {
byteOutputStream = new ByteArrayOutputStream();
xssfWorkbook.write(byteOutputStream);
bytes = byteOutputStream.toByteArray();
} catch (Exception e) {
e.printStackTrace();
} finally {
byteOutputStream.close();
}
ServletOutputStream outputStream = responese.getOutputStream();
try {
responese.reset();
responese.setHeader("Content-Disposition", "attachment; filename="+new String( "开发测试.xlsx".getBytes(), "ISO8859-1" ));//要保存的文件名
responese.setContentType("application/octet-stream; charset=utf-8");
outputStream.write(bytes);
outputStream.flush();
} finally {
if (outputStream != null) {
outputStream.close();
}
}
}