<poi.version>4.1.2</poi.version>
public class ExcelsUtil {
private static final Logger logger = LoggerFactory.getLogger(ExcelsUtil.class);
/**
* 时间格式
*/
private static final String TIME_PATTERN = "yyyy-MM-dd HH:mm:ss";
/**
* 默认列的宽度
*/
private static final int COLUMN_WIDTH = 24;
/**
* 表头字体大小
*/
private static final int TITLE_FONT_SIZE = 11;
/**
* 表头单元格
**/
private static final int TITLE_CELL = 0;
/**
* 内容单元格
**/
private static final int CONTENT_CELL = 1;
/**
* 每个sheeet的最大数据数量
*/
private static final int MAX_LENGTH = 60000;
/**
* 导出
* 2003版本的xls
*
* @param title 表格标题名,文件名
* @param headers 表格头部标题中文集合
* @param headerZhWords 表格头部标题的字段名
* @param dataSet 需要显示的数据集合
*/
public static <T> void exportExcel(String title, String[] headers, String[] headerZhWords, Collection<T> dataSet,
HttpServletResponse response) throws UnsupportedEncodingException {
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", String.format("attachment;filename=%s.xls", URLEncoder.encode(title, "UTF-8")));
try (HSSFWorkbook workbook = new HSSFWorkbook();
ServletOutputStream out = response.getOutputStream()) {
int pageNum = 1;
int dataLength = dataSet.size();
List<T> dataAll = new ArrayList<>(dataSet);
List<T> dataPage;
//分sheet处理 每个sheet数据条数为MAX_LENGTH
while (dataLength >= 0) {
if (dataLength > MAX_LENGTH) {
dataPage = dataAll.subList((pageNum - 1) * MAX_LENGTH, pageNum * MAX_LENGTH);
} else {
dataPage = dataAll.subList((pageNum - 1) * MAX_LENGTH, dataAll.size());
}
// 生成一个sheet
HSSFSheet sheet = workbook.createSheet(String.format("%s -%s-", title, pageNum));
dataLength -= MAX_LENGTH;
pageNum++;
// 设置表格默认列宽度
sheet.setDefaultColumnWidth(COLUMN_WIDTH);
// 生成标题样式
HSSFCellStyle titleStyle = setCellStyle(workbook, TITLE_CELL);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
HSSFCell cellHeader;
for (int i = 0; i < headers.length; i++) {
cellHeader = row.createCell(i);
cellHeader.setCellStyle(titleStyle);
cellHeader.setCellValue(new HSSFRichTextString(headers[i]));
}
//填充内容
fillContent(headerZhWords, dataPage, workbook, sheet, 0);
}
workbook.write(out);
} catch (IOException e) {
logger.error("excel生成错误", e);
}
}
/**
* 设置样式
**/
private static HSSFCellStyle setCellStyle(HSSFWorkbook workbook, int cellType) {
HSSFCellStyle style = null;
//边框
BorderStyle borderStyle = BorderStyle.THIN;
short borderColor = HSSFColor.HSSFColorPredefined.BLACK.getIndex();
if (cellType == TITLE_CELL) {
style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(borderStyle);
style.setBorderLeft(borderStyle);
style.setBorderRight(borderStyle);
style.setBorderTop(borderStyle);
style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
style.setTopBorderColor(borderColor);
style.setLeftBorderColor(borderColor);
style.setRightBorderColor(borderColor);
style.setBottomBorderColor(borderColor);
//垂直居中
style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
style.setVerticalAlignment(VerticalAlignment.CENTER);
//自动换行
style.setWrapText(false);
// 生成标题字体
HSSFFont font = workbook.createFont();
font.setBold(true);
font.setFontName("宋体");
font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
font.setFontHeightInPoints((short) TITLE_FONT_SIZE);
// 把字体应用到当前的样式
style.setFont(font);
} else if (cellType == CONTENT_CELL) {
style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(borderStyle);
style.setBorderLeft(borderStyle);
style.setBorderRight(borderStyle);
style.setBorderTop(borderStyle);
style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
style.setWrapText(false);
// 生成内容字体
HSSFFont font = workbook.createFont();
font.setBold(false);
style.setFont(font);
}
return style;
}
/**
* 填充sheet内容
*
* @param headerWords 填充数据的字段
* @param dataSet 数据集
* @param workbook 工作上下文
* @param sheet sheet
* @param contentStartIndex 从第contentStartIndex+1行开始填充数据
*/
private static <T> void fillContent(String[] headerWords, Collection<T> dataSet, HSSFWorkbook workbook, HSSFSheet sheet,
Integer contentStartIndex) {
//内容样式
HSSFCellStyle contentStyle = setCellStyle(workbook, CONTENT_CELL);
SimpleDateFormat sdf = new SimpleDateFormat(TIME_PATTERN);
//字段名
String fieldName;
//get方法名称
String getMethodName;
//单元格
HSSFCell cell;
Method getMethod;
Object value = "";
HSSFRow row;
contentStartIndex = contentStartIndex == null ? 0 : contentStartIndex;
T t;
int index = 0;
Iterator<T> it = dataSet.iterator();
while (it.hasNext()) {
index++;
row = sheet.createRow(index + contentStartIndex);
t = it.next();
for (int i = 0; i < headerWords.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(contentStyle);
fieldName = headerWords[i];
if (t instanceof Map) {
//Map类型
value = ((Map<?, ?>) t).get(fieldName);
} else {
getMethodName = getBeanMethodName(fieldName);
//Bean类
try {
getMethod = t.getClass().getMethod(getMethodName);
value = getMethod.invoke(t);
} catch (SecurityException | NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
logger.error("excel生成错误", e);
}
}
if (null == value) {
cell.setCellValue("");
} else if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value instanceof Float || value instanceof Double) {
cell.setCellValue(String.valueOf(value));
} else if (value instanceof Long) {
cell.setCellValue((Long) value);
} else if (value instanceof Date) {
cell.setCellValue(sdf.format((Date) value));
} else {
cell.setCellValue(String.valueOf(value));
}
}
}
}
/**
* 获取bean的获取值的方法
*
* @param fieldName
* @return
*/
private static String getBeanMethodName(String fieldName) {
String cacheName = CommonConstant.CachePre.METHOD + fieldName;
String methodName = String.valueOf(CommonConstant.BaseCache.COMMON_CACHE.get(cacheName));
if (CommonConstant.BaseConstant.NULL.equals(methodName)) {
methodName = "get" + fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
CommonConstant.BaseCache.COMMON_CACHE.put(cacheName, methodName);
}
return methodName;
}
}
网友评论