POI Excel设置列宽

参考文章: https://blog.csdn.net/lipinganq/article/details/78090553

poi版本: 3.17

Excel列宽单位概述

POI中Sheet列宽是通过字符个数来确定的，列宽单位为一个字符宽度的1/256  每列可以显示的最大字符数为255 

// 设置默认列宽，width为字符个数
sheet.setDefaultColumnWidth(int width)

// 设置第columnIndex+1列的列宽，单位为字符宽度的1/256
sheet.setColumnWidth(int columnIndex, int width)
// 设置第5列的列宽为20个字符宽度
sheet.setColumnWidth(4, 20*256)

实际代码    注意 ,第一列的index为0 , 即 sheet.setColumnWidth(0,40*256);,此时设置的第一列

@RequestMapping(value = "/exportJournalExcle")
	public void exportJournalExcle(PoliceJournalDTO policeJournalDTO, HttpServletRequest request,
			HttpServletResponse response) {
		try {
			LoginBean lb = this.getLoginBean();
			String accountId = lb.getAccountID();
			String realName = lb.getRealName();
 
			policeJournalDTO.setName(realName);
			policeJournalDTO.setPoliceAlarm(accountId);
			// 导出excle的集合数据
			List listExcleJournal = policeJournalSer.listOfPoliceJournalExcle(policeJournalDTO);
 
			// 生成excle文件
			HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
			HSSFSheet sheet = hssfWorkbook.createSheet("日志");
			
			//设置默认的列宽为10个字符 
			sheet.setDefaultColumnWidth(10);
//			sheet.setColumnWidth(2, 12*256);
//			sheet.setColumnWidth(3, 12*256);
			
			//设置4,5,11列为20个字符 
			sheet.setColumnWidth(4, 20*256);
			sheet.setColumnWidth(5, 20*256);
//			sheet.setColumnWidth(6, 12*256);
			sheet.setColumnWidth(11, 20*256);
			
			// 表头
			HSSFRow headRow = sheet.createRow(0);
			headRow.createCell(0).setCellValue("姓名");
			headRow.createCell(1).setCellValue("");
			headRow.createCell(2).setCellValue("");
			headRow.createCell(3).setCellValue("");
			headRow.createCell(4).setCellValue("");
			headRow.createCell(5).setCellValue("");
			headRow.createCell(6).setCellValue("");
			headRow.createCell(7).setCellValue("");
			headRow.createCell(8).setCellValue("");
			headRow.createCell(9).setCellValue("");
			headRow.createCell(10).setCellValue("");
			headRow.createCell(11).setCellValue("");
 
			if (listExcleJournal != null && listExcleJournal.size() > 0) {
				for (PoliceJournalRDTO journalRDTO : listExcleJournal) {
					HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
					if (!StringUtils.isBlank(journalRDTO.getRealName())) {
						dataRow.createCell(0).setCellValue(journalRDTO.getRealName());
					}
					if (!StringUtils.isBlank(journalRDTO.getAccountID())) {
						dataRow.createCell(1).setCellValue(journalRDTO.getAccountID());
					}
					// 日期格式转为字符串输出
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					if (journalRDTO.getJournalTime() != null) {
						String journalTime = sdf.format(journalRDTO.getJournalTime());
						dataRow.createCell(2).setCellValue(journalTime);
					}
					if (journalRDTO.getEntryTime() != null) {
						String entryTime = sdf.format(journalRDTO.getEntryTime());
						dataRow.createCell(3).setCellValue(entryTime);
					}
					if (!StringUtils.isBlank(journalRDTO.getWorkType())) {
						dataRow.createCell(4).setCellValue(journalRDTO.getWorkType());
					}
					if (!StringUtils.isBlank(journalRDTO.getJobContent())) {
						dataRow.createCell(5).setCellValue(journalRDTO.getJobContent());
					}
					
					}
				}
			}
			// 下载文件,设置两个头,一个流
			// 下载导出,设置头信息
			response.setContentType("application/vnd.ms-excel");
			// 文件名
			String filename = "日志.xls";
			// 浏览器的名称
			String agent = request.getHeader("user-agent");
			filename = FileUtils.encodeDownloadFilename(filename, agent);
			response.setHeader("Content-Disposition", "attachment;filename=" + filename);
			OutputStream outputStream;
			outputStream = response.getOutputStream();
			hssfWorkbook.write(outputStream);
			outputStream.close();
			hssfWorkbook.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

 

导出的效果如下图, 可以看到设置了额外的列宽的, 宽度增加了.