功能背景:方便相关人员,对数据进行分析和处理。
效果图:
思路:
- POI如何创建Sheet、Row、Cell,如何遍历。
- 行是固定的,列是会一直增加。表头进行单独设置,遍历出来的row、一定要传下去,不然会出现只显示最后那条数据。
- 行根据字段,进行遍历,列根据数据的个数进行遍历。列遍历一次,行要遍历字段个数那么多次(例:6个字段遍历6次)
核心代码
// 取出一共有多少个sheet.
double sheetNo = Math.ceil(list.size() / sheetSize);
List<Row> rowList = Lists.newLinkedList();
for (int index = 0; index <= sheetNo; index++)
{
createSheet(sheetNo, index);
//Row row = null;
for(int i = 0;i < fields.size();i++){
// 产生多行
Row row = sheet.createRow(i);
sheet.setColumnWidth(0, 18 * 256);
int column = 0;
// 写入各个字段的列头名称
Object[] os = fields.get(i);
Excel excel = (Excel) os[1];
this.createCell(excel, row, column);
rowList.add(row);
}
if (Type.EXPORT.equals(type))
{
fillExcelData(index,rowList);
}
}
String filename = encodingFilename(sheetName);
out = new FileOutputStream(getAbsoluteFile(filename));
wb.write(out);
/**
* 创建单元格
*/
public Cell createCell(Excel attr, Row row, int column)
{
// 创建列
Cell cell = row.createCell(column);
// 写入列信息
cell.setCellValue(attr.name());
setDataValidation(attr, row, column);
cell.setCellStyle(styles.get("header"));
return cell;
}
/**
* 填列表数据
*/
public void fillExcelData(int index,List<Row> rowList)
{
int startNo = index * sheetSize;//65536->0
int colNo = Math.min(startNo + sheetSize, list.size());
int endNo = fields.size();
for(int m = startNo; m < colNo; m++){
// 得到导出对象.
T vo = (T) list.get(m);
int rowNum = 0;
for (Object[] os : fields)
{
Row row = rowList.get(rowNum++);
Cell cell = row.createCell(m+1);
Field field = (Field) os[0];
Excel excel = (Excel) os[1];
// 设置实体类私有属性可访问
field.setAccessible(true);
this.addCell(excel, row, vo, field, cell);
}
System.out.println(m);
}
}
/**
* 添加单元格
*/
public Cell addCell(Excel attr, Row row, T vo, Field field, Cell cell)
{
//Cell cell = null;
try
{
// 设置行高
row.setHeight((short) (attr.height() * 20));
// 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
if (attr.isExport())
{
// 创建cell
//cell = row.createCell(column);
cell.setCellStyle(styles.get("data"));
// 用于读取对象中的属性
Object value = getTargetValue(vo, field, attr);
String dateFormat = attr.dateFormat();
String readConverterExp = attr.readConverterExp();
String separator = attr.separator();
String dictType = attr.dictType();
boolean isImg = attr.isImg();
boolean isImgStr = attr.isImgStr();
if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))
{
cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
}
else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))
{
cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));
}
else if (StringUtils.isNotEmpty(dictType) && StringUtils.isNotNull(value))
{
cell.setCellValue(convertDictByExp(Convert.toStr(value), dictType, separator));
}
else if (value instanceof BigDecimal && -1 != attr.scale())
{
cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString());
}
// 生成图片(图片地址集合)
else if (isImg && StringUtils.isNotNull(value))
{
List<String> urls = (List<String>) value;
for (String url : urls) {
String fileType = "JPEG";
int imgType = HSSFWorkbook.PICTURE_TYPE_JPEG;
if(url.indexOf(".png") > -1){
fileType = "PNG";
imgType = HSSFWorkbook.PICTURE_TYPE_PNG;
}
String resultURL = "";
//遍历字符串
for (int i = 0; i < url.length(); i++) {
char charAt = url.charAt(i);
//只对汉字处理
if (isChineseChar(charAt)) {
String encode = URLEncoder.encode(charAt+"","UTF-8");
resultURL+=encode;
}else {
resultURL+=charAt;
}
}
picture(wb,sheet, resultURL ,fileType,imgType,row.getRowNum(),cell.getColumnIndex());
}
}
// 生成图片(图片地址)
else if (isImgStr && StringUtils.isNotNull(value) && getRource((String)value))
{
String url = (String) value;
String fileType = "JPEG";
int imgType = HSSFWorkbook.PICTURE_TYPE_JPEG;
if(url.indexOf(".png") > -1){
fileType = "PNG";
imgType = HSSFWorkbook.PICTURE_TYPE_PNG;
}
String resultURL = "";
//遍历字符串
for (int i = 0; i < url.length(); i++) {
char charAt = url.charAt(i);
//只对汉字处理
if (isChineseChar(charAt)) {
String encode = URLEncoder.encode(charAt+"","UTF-8");
resultURL+=encode;
}else {
resultURL+=charAt;
}
}
picture(wb,sheet, resultURL ,fileType,imgType,row.getRowNum(),cell.getColumnIndex());
}
}
else
{
// 设置列类型
setCellVo(value, attr, cell);
}
}
}
catch (Exception e)
{
log.error("导出Excel失败{}", e);
}
return cell;
}