poi设置单元格展示格式

需求：只展示千位及以上的数值，保留一位小数；
不是很复杂，直接上代码；

1. controller

    @Resource
    private ExcelTest2 excelTest2;

    @GetMapping("excel/test")
    public String excelTest(HttpServletResponse response) {
        excelTest2.export(response);
        System.out.println("hello world!");
        return "Hello World!";
    }

2. 实现

@Component
public class ExcelTest2 {
    XSSFWorkbook workBook = new XSSFWorkbook();
    XSSFSheet sheet = workBook.createSheet("sheet名称");

    public void export(HttpServletResponse response) {

        XSSFCellStyle lockstyle = workBook.createCellStyle();
        lockstyle.setLocked(true);
        lockstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        lockstyle.setFillForegroundColor(IndexedColors.RED.getIndex());

        //设置列格式，注释1
        for (int i = 0; i < 10; i++) {
            sheet.setColumnWidth(i, 4000); //设置宽度
        }
        int num = 987654321;
        int otherNum = -87654321;

        for (int i = 0; i < 10; i++) {
            XSSFRow row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                XSSFCell cell = row.createCell(j);

                XSSFCellStyle unlockstyle = workBook.createCellStyle();
                unlockstyle.setLocked(false);
                DataFormat format = workBook.createDataFormat();
                if (j == 0 || j == 1) {
                    unlockstyle.setDataFormat(format.getFormat("0,;[Red](0,)"));
                } else if (j == 2) {
                    unlockstyle.setDataFormat(format.getFormat("0.0,"));
                } else if (j == 3) {
                    unlockstyle.setDataFormat(format.getFormat("0,"));
                } else if (j == 4) {
                    unlockstyle.setDataFormat(format.getFormat("[Green]0.0,"));
                } else if (j == 5) {
                    unlockstyle.setDataFormat(format.getFormat("[Red]0.0,"));
                }  else if (j == 6) {
                    unlockstyle.setDataFormat(format.getFormat("00.0,"));
                } else {
                    unlockstyle.setDataFormat(format.getFormat("[Green]0,"));
                }
                cell.setCellStyle(unlockstyle);//默认是锁定状态；将所有单元格设置为：未锁定；然后再对需要上锁的单元格单独锁定


                if (i == 0) {//这里可以根据需要进行判断;我这就将第2列上锁了
                    cell.setCellValue(num);
                } else {
                    cell.setCellValue(otherNum);
                }
            }

        }
        //sheet添加保护，这个一定要否则光锁定还是可以编辑的
        sheet.enableLocking();
        CTSheetProtection sheetProtection = sheet.getCTWorksheet().getSheetProtection();
        sheetProtection.setSelectLockedCells(false);
        sheetProtection.setSelectUnlockedCells(false);
        sheetProtection.setFormatCells(true);
        sheetProtection.setFormatColumns(true);
        sheetProtection.setFormatRows(true);
        sheetProtection.setInsertColumns(true);
        sheetProtection.setInsertRows(false);
        sheetProtection.setInsertHyperlinks(true);
        sheetProtection.setDeleteColumns(true);
        sheetProtection.setDeleteRows(true);
        sheetProtection.setSort(false);
        sheetProtection.setAutoFilter(false);
        sheetProtection.setPivotTables(true);
        sheetProtection.setObjects(true);
        sheetProtection.setScenarios(true);

        try (OutputStream out = response.getOutputStream()) {
            workBook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}