Functional background: Convenient for relevant personnel to analyze and process data.
Rendering:
Idea:
- How POI creates Sheet, Row, Cell, and traverses.
- The rows are fixed and the columns will continue to increase. The table header is set separately, and the rows traversed must be passed on, otherwise only the last piece of data will be displayed.
- Rows are traversed based on fields, and columns are traversed based on the number of data. The columns are traversed once, and the rows are traversed as many times as the number of fields (for example: 6 fields are traversed 6 times)
core code
// Get how many sheets there are in total.
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 + + ){
// generate multiple lines
Row row = sheet.createRow(i);
sheet.setColumnWidth(0, 18 * 256);
int column = 0;
//Write the column header name of each field
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);
}
\t\t\t
}
String filename = encodingFilename(sheetName);
out = new FileOutputStream(getAbsoluteFile(filename));
wb.write(out);
\t\t\t
/**
*Create cells
*/
public Cell createCell(Excel attr, Row row, int column)
{
//Create column
Cell cell = row.createCell(column);
//Write column information
cell.setCellValue(attr.name());
setDataValidation(attr, row, column);
cell.setCellStyle(styles.get("header"));
return cell;
}
\t
/**
* Fill in the list data
*/
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 + + ){
// Get the export object.
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];
//Set the private properties of the entity class to be accessible
field.setAccessible(true);
this.addCell(excel, row, vo, field, cell);
}
System.out.println(m);
}
}
\t
/**
* Add cells
*/
public Cell addCell(Excel attr, Row row, T vo, Field field, Cell cell)
{
//Cell cell = null;
try
{
//Set row height
row.setHeight((short) (attr.height() * 20));
//Decide whether to export based on the settings in Excel. In some cases, it needs to be left empty and the user is expected to fill in this column.
if (attr.isExport())
{
//Create cell
//cell = row.createCell(column);
cell.setCellStyle(styles.get("data"));
// Used to read properties in the object
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) & amp; & amp; StringUtils.isNotNull(value))
{
cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
}
else if (StringUtils.isNotEmpty(readConverterExp) & amp; & amp; StringUtils.isNotNull(value))
{
cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));
}
else if (StringUtils.isNotEmpty(dictType) & amp; & amp; StringUtils.isNotNull(value))
{
cell.setCellValue(convertDictByExp(Convert.toStr(value), dictType, separator));
}
else if (value instanceof BigDecimal & amp; & amp; -1 != attr.scale())
{
cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString());
}
// Generate pictures (image address collection)
else if (isImg & amp; & amp; 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 = "";
//Traverse the string
for (int i = 0; i < url.length(); i + + ) {
char charAt = url.charAt(i);
//Only process Chinese characters
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());
}
}
//Generate image (image address)
else if (isImgStr & amp; & amp; StringUtils.isNotNull(value) & amp; & amp; 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 = "";
//Traverse the string
for (int i = 0; i < url.length(); i + + ) {
char charAt = url.charAt(i);
//Only process Chinese characters
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
{
//Set column type
setCellVo(value, attr, cell);
}
}
}
catch (Exception e)
{
log.error("Export to Excel failed{}", e);
}
return cell;
}