excel poi 自动换行,使用POI导出Excel自适应列宽的实现

使用POI导出Excel时，涉及到如何自适应列宽的问题。方法如下

/**

* 下载excel文件,内容使用MAP存放

*

* @param response

* @param headName

* @param tableHead

* @param tableBody

* @throws IOException

*/

public static void downloadExcelMap(HttpServletResponse response, String headName, List tableHead,

List> tableBody) throws IOException {

headName=StringUtils.replaceAllSpecial(headName);

// 1:创建一个workbook

HSSFWorkbook workbook = new HSSFWorkbook();

// 创建样式

HSSFCellStyle style = workbook.createCellStyle();

Font font = workbook.createFont();

font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗体

style.setFont(font);

style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中

style.setBorderTop((short) 1);

style.setBorderBottom((short) 1);

style.setBorderLeft((short) 1);

style.setBorderRight((short) 1);

style.setWrapText(true);

// 设置合计样式

HSSFCellStyle style1 = workbook.createCellStyle();

style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中

style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中

style1.setBorderTop((short) 1);

style1.setBorderBottom((short) 1);

style1.setBorderLeft((short) 1);

style1.setBorderRight((short) 1);

style.setWrapText(true);

HSSFSheet sheet = (HSSFSheet) workbook.createSheet(headName);

// 2：合并单元格，表头。并设置值

CellRangeAddress cra = new CellRangeAddress(0, 0, 0, tableHead.size() - 1);

sheet.addMergedRegion(cra);

HSSFRow row = sheet.createRow(0);

HSSFCell tableName = row.createCell(0);

tableName.setCellStyle(style);

tableName.setCellValue(headName);

//存储最大列宽

Map maxWidth = new HashMap();

// 3：设置表head

HSSFRow row1 = sheet.createRow(1);

for (int i = 0; i < tableHead.size(); i++) {

Cell createCell = row1.createCell(i);

createCell.setCellValue(tableHead.get(i));

createCell.setCellStyle(style);

maxWidth.put(i,createCell.getStringCellValue().getBytes().length * 256 + 200);

}

// 4：表格内容

for (int i = 0; i < tableBody.size(); i++) {

HSSFRow rows = sheet.createRow(i + 2);

int j = 0;

for (Map.Entry entry : tableBody.get(i).entrySet()) {

HSSFCell createCell = rows.createCell(j);

if(PropertyUtil.objectNotEmpty(entry.getValue())){

createCell.setCellValue(entry.getValue().toString());

}else{

createCell.setCellValue("");

}

int length = createCell.getStringCellValue().getBytes().length * 256 + 200;

//这里把宽度最大限制到15000

if (length>15000){

length = 15000;

}

maxWidth.put(j,Math.max(length,maxWidth.get(j)));

j++;

createCell.setCellStyle(style1);

}

}

// 列宽自适应

for (int i = 0; i < tableHead.size(); i++) {

sheet.setColumnWidth(i,maxWidth.get(i));

}

// 5：设置头

response.setHeader("Content-disposition",

"attachment; filename=" + new String(headName.getBytes("GB2312"), "ISO8859-1") + ".xls");

// 6：设置头类型

response.setContentType("application/vnd.ms-excel");

// 7：写出

OutputStream toClient = response.getOutputStream();

workbook.write(toClient);

toClient.flush();

toClient.close();

}

注意：以上代码有15000的限制，原因是Excel限制最大宽度为15000。所以，在这里我们除了这个宽度处理外，还需要设置单元格自动换行

style.setWrapText(true);