excel2javabeans

convert excel rows to javabeans and vice versa.

Convert Excel to Javabeans

// ... 
Workbook workbook = getClassPathWorkbook("member.xlsx");
ExcelToBeans excelToBeans = new ExcelToBeans(workbook);
List<BeanWithTitle> beans = excelToBeans.convert(BeanWithTitle.class);
// ...

public class BeanWithTitle extends ExcelRowRef implements ExcelRowIgnorable {
    @ExcelColTitle("会员姓名") String memberName;
    @ExcelColTitle("卡名称") String cardName;
    @ExcelColTitle("办卡价格") String cardPrice;
    @ExcelColTitle("性别") String sex;

    @Override public boolean ignoreRow() {
        return StringUtils.startsWith(memberName, "示例-");
    }
    
    // getters and setters ignored
}

Convert Javabeans to Excel

@Data @Builder
public class ExportFollowUserExcelRow {
    @ExcelColTitle("序号") private int seq;
    @ExcelColTitle("客户姓名") private String name;
    @ExcelColTitle("客户类型") private String grade;
    @ExcelColTitle("性别") private String gender;
    @ExcelColTitle("手机号码") private String mobile;
    @ExcelColTitle("建档时间") private String createTime;
    @ExcelColTitle("来源渠道") private String sources;
    @ExcelColTitle("跟进总数") private String followTotalNum;
    @ExcelColTitle("当前所属会籍") private String advisorName;
    @ExcelColTitle("最近跟进人") private String currentFollowName;
    @ExcelColTitle("最近跟进时间") private String currentFollowTime;
}

Workbook templateWorkbook = ExcelToBeansUtils.getClassPathWorkbook("assignment.xlsx");
BeansToExcel beansToExcel = new BeansToExcel(templateWorkbook);
List<ExportFollowUserExcelRow> members = Lists.newArrayList();
members.add(...);
members.add(...);
members.add(...);
members.add(...);

Workbook workbook = beansToExcel.create(members);
ExcelToBeansUtils.writeExcel(workbook, name);

Cell Image Support

Now the image in excel can be bound to bean field of type ImageData. The image's axis will be computed to match the related cell.

@Data
public class ImageBean {
    @ExcelColTitle("图片")
    private ImageData imageData;
    @ExcelColTitle("名字")
    private String name;
}

public void testImage() {
    Workbook val workbook = ExcelToBeansUtils.getClassPathWorkbook("images.xls");
    ExcelToBeans excelToBeans = new ExcelToBeans(workbook);
    List<ImageBean> beans = excelToBeans.convert(ImageBean.class);
}

List<String/Integer> bean fields support

@Data
public static class MultipleColumnsBeanWithTitle {
    @ExcelColTitle("会员姓名") String memberName; // for the first row, the value will be "张小凡"
    @ExcelColTitle("手机号") List<String> mobiles; // for the first row，the values will be: null, "18795952311", "18795952311", "18795952311"
    @ExcelColTitle("归属地") List<String> homeareas; // for the first row, the values will be: "南京", "北京", "上海", "广东"
}

Excel SpringMVC upload and download demo

/**
 * 从EXCEL中批量导入会员。
 */
@RequestMapping("/ImportMembers") @RestController
public class ImportMembersController {
    /**
     * 下载失败条目的EXCEL。
     *
     * @return RestResp
     */
    @RequestMapping("/downloadError") @SneakyThrows
    public RestResp downloadError(HttpServletResponse response) {
        byte[] workbook = ImportMembersHelper.redisExcel4ImportMemberError();
        if (workbook == null) {
            return RestResp.ok("当前没有失败条目");
        }

        ExcelDownloads.download(response, workbook, "导入错误" + WestId.next() + ".xlsx");
        return RestResp.ok("失败条目下载成功");
    }

    /**
     * 使用EXCEL 批量导入学员。
     *
     * @param file EXCEL文件
     * @return RestResp
     */
    @RequestMapping("/importMembers") @SneakyThrows
    public RestResp importMembers(@RequestParam("file") MultipartFile file) {
        @Cleanup val excelToBeans = new ExcelToBeans(file.getInputStream());
        val importedMembers = excelToBeans.convert(ImportedMember.class);
        // ...
    }

}

BeansToExcelOnTemplate

@Data @Builder
public class CepingResult {
    @ExcelCell(sheetName = true)
    private String sheetName;       // 表单名称

    @ExcelCell(value = "A2", replace = "XX")
    private String interviewCode;   // 面试编号

    @ExcelCell
    private String name;           // 身份证姓名
    @ExcelCell
    private String gender;         // 性别
    @ExcelCell
    private String age;            // 年龄

    @ExcelCell("B4")
    private String position;       // 应聘职位
    @ExcelCell("E4")
    private String level;          // 推荐职级
    @ExcelCell("G4")
    private String annualSalary;   // 期望年薪

    @ExcelCell("C5")
    private double matchScore;     // 岗位匹配度
    @ExcelCell(value = "C6", maxLineLen = 40)
    private String matchComment;   // 岗位匹配度评语
}

@Cleanup val wb = ExcelToBeansUtils.getClassPathWorkbook("template.xlsx");
val beansToExcel = new BeansToExcelOnTemplate(wb.getSheet("templateName"));

@Cleanup val newWb = beansToExcel.create(bean);
PoiUtil.protectWorkbook(newWb, "123456");
PoiUtil.writeExcel(newWb, "exported.xlsx");

Sonarqube

travis encrypt a7fe683637d6e1f54e194817cc36e78936d4fe61

mvn clean install sonar:sonar -Dsonar.organization=bingoohuang-github -Dsonar.host.url=https://sonarqube.com -Dsonar.login=a7fe683637d6e1f54e194817cc36e78936d4fe61

Problems

Autosize column does not work on CentOS.

Maybe there is not relative fonts installed. Methods:

Create fonts folder:mkdir ~/.fonts
Copy fonts to the fold:scp /System/Library/Fonts/STHeiti\ Light.ttc yogaapp@test.ino01:./.fonts/
Install the fonts:fc-cache -f -v
查看字体: fc-list|grep SimSun

[betaoper@beta-hetong ~]$ fc-list|grep SimSun
/usr/share/fonts/winfonts/simsun.ttc: 宋体,SimSun:style=常规,Regular
/usr/share/fonts/winfonts/simsun.ttc: 新宋体,NSimSun:style=常规,Regular
/usr/share/fonts/winfonts/simsunb.ttf: SimSun\-ExtB:style=Regular,obyčejné

For all users available, just copy the fonts file to the /usr/share/fonts directory and then fc-cache -f -v.

Emoji output error

When writting emoji like 🦄女侠🌈💄💓 , the output excel content will show like ?女侠???, try to fix this with following dependency.

<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>3.0.0</version>
</dependency>

How to manually test excel downloads in Chrome console?

// execute the following javascript code in the console to download excel for testing
var fileName = "abc.xlsx"
var url = 'http://localhost:8090/yoga-system/MemberExportController/memberExport'
var request = new XMLHttpRequest()
request.open('POST', url, true)
request.setRequestHeader('Content-Type', 'application/json; charset=utf-8')
request.responseType = 'blob'
request.onload = function(e) {
    if (this.status === 200) {
        var blob = this.response;
        if(window.navigator.msSaveOrOpenBlob) {
            window.navigator.msSaveBlob(blob, fileName)
        } else {
            var downloadLink = window.document.createElement('a')
            var contentTypeHeader = request.getResponseHeader("Content-Type")
            downloadLink.href = window.URL.createObjectURL(new Blob([blob], {type: contentTypeHeader}))
            downloadLink.download = fileName
            document.body.appendChild(downloadLink)
            downloadLink.click()
            document.body.removeChild(downloadLink)
       }
   }
}
request.send(JSON.stringify({firstBlood:false,export:true}));

Ajax js

export const downloadBlobFile = (response) => {
  const blob = new Blob([response.data], {type: response.data.type})
  const contentDisposition = response.headers['content-disposition']
  let fileName = 'unknown'
  if (contentDisposition) {
    const fileNameMatch = contentDisposition.match(/filename="(.+)"/)
    if (fileNameMatch.length === 2) {
      fileName = decodeURIComponent(fileNameMatch[1])
    }
  }

  if (window.navigator.msSaveOrOpenBlob) {
    navigator.msSaveBlob(blob, fileName)
  } else {
    const link = document.createElement('a')
    link.href = window.URL.createObjectURL(blob)
    link.download = fileName
    link.click()
    window.URL.revokeObjectURL(link.href)
  }
}

import {downloadBlobFile} from 'utils'
this.$http.post('/BasicParametersController/exportPositions/', {}, {
  responseType: 'blob'
}).then((response) => {
  downloadBlobFile(response)
})

gpg

GPG_TTY=$(tty)
export GPG_TTY

set -gx GPG_TTY (tty)

mvn clean install -DskipTests -Dgpg.passphrase=slgsdmxl
mvn clean install -Dgpg.skip -DskipTests

TODO

Support SXSSF (Streaming Usermodel API) for very large spreadsheets have to be produced.

Warning

Avoid to upgrade poi-ooxml to 4.0.0. that will cause shift rows go failing. See Bug 62711 New: Calling shiftRows corrupts file in POI 4.0

bingoohuang/excel2javabeans