public ByteArrayOutputStream export(ExcelData data) throws IOException {
Workbook workbook = new XSSFWorkbook();//xlsx
// Workbook workbook = new HSSFWorkbook();//xls
Sheet sheet = workbook.createSheet(ExcelConst.EXCEL_SHEET);
// 标头样式
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
// 填充表头
Row row = sheet.createRow(0);
Cell cell;
for (int i = 0; i < data.column(); i++) {
cell = row.createCell(i);
cell.setCellValue(data.getHead(i));
cell.setCellStyle(style);
}
// 超链接样式
CellStyle linkStyle = workbook.createCellStyle();
Font cellFont = workbook.createFont();
cellFont.setUnderline((byte) 1);
cellFont.setColor(HSSFColor.BLUE.index);
linkStyle.setFont(cellFont);
// 填充数据
Row newRow;
for (int i = 0; i < data.row(); i++) {
List<DataRow> dataRow = data.getRow(i);
newRow = sheet.createRow(i + 1);
for (int j = 0; j < dataRow.size(); j++) {
Object value = dataRow.get(j).getValue();
if (value instanceof Date) {
newRow.createCell(j).setCellValue(
new SimpleDateFormat(DateUtil.yyyyMMdd).format((Date) dataRow.get(j).getValue()));
} else if (StringUtil.isNotEmpty(dataRow.get(j).getLink())) {
Cell hyperCell = newRow.createCell(j);
String url = dataRow.get(j).getLink();
url = url.startsWith("http://") ? url : "http://" + url;
hyperCell.setCellType(CellType.STRING);
hyperCell.setCellStyle(linkStyle);
// hyperCell.setCellFormula("HYPERLINK(\"" + url + "\",\"" + url + "\")");
Hyperlink hyperlink = workbook.getCreationHelper().createHyperlink(HyperlinkType.URL);
hyperlink.setAddress(url);
hyperCell.setCellValue(url);
hyperCell.setHyperlink(hyperlink);
hyperCell.setCellValue(url);
} else {
Object dataValue = dataRow.get(j).getValue();
if(ObjectUtil.isNotEmpty(dataValue)){
newRow.createCell(j).setCellValue(dataValue.toString());
}else{
newRow.createCell(j).setCellValue("");
}
}
}
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook.write(os);
return os;
}
url超过255个字符会报不能设置为url格式
参考博客