POI_将数据写入excel表

若想要新加sheet页，再创建一个就好

用到的pom:

<!--excel工具类-->
       <!--糊涂工具类工具类，可不添加-->
		<dependency>
			<groupId>cn.hutool</groupId>
			<artifactId>hutool-core</artifactId>
			<version>5.5.8</version>
		</dependency>
		<dependency>
			<groupId>cn.hutool</groupId>
			<artifactId>hutool-poi</artifactId>
			<version>5.5.8</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>4.1.2</version>
		</dependency>

public static void main(String[] args) throws IOException {
        Map<String, String> map = new HashMap<>();
        map.put("aa","AA");
        map.put("bb","BB");
        map.put("cc","CC");
        map.put("dd","DD");
        map.put("ee","EE");
        Set<String> keySet = map.keySet();

        //创建Excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        //设置字体
        HSSFCellStyle setBorder = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        CellStyle style = workbook.createCellStyle();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);
        setBorder.setFont(font);
        style.setFont(font);
        //设置字体居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //创建sheet页
        HSSFSheet firstSheet = workbook.createSheet("sheet1");
        //创建第一行 通常第一行作为 数据表头
        HSSFRow row = firstSheet.createRow(0);
        //设置 第一行的列数据
        String [] titles = new String[]{"付款主体","费用类别(摘要)","备注(描述)","收款对象类型","收款人姓名","金额(元)","付款渠道","付款方式","对接人"};
        for(int i=0; i<titles.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(style);
        }
        //插入1000条测试数据
        int i = 1;
        for (String key : keySet) {
            HSSFRow rowData = firstSheet.createRow(i);
            HSSFCell cell0 = rowData.createCell(0);
            cell0.setCellValue(key);
            HSSFCell cell1 = rowData.createCell(1);
            cell1.setCellValue("增益分成");
            HSSFCell cell2 = rowData.createCell(2);
            cell2.setCellValue("增益业主年度分成");
            HSSFCell cell3 = rowData.createCell(3);
            cell3.setCellValue("业主");
            HSSFCell cell4 = rowData.createCell(4);
            cell4.setCellValue("业主");
            HSSFCell cell5 = rowData.createCell(5);
            cell5.setCellValue(map.get(key));
            HSSFCell cell6 = rowData.createCell(6);
            cell6.setCellValue("财务系统");
            HSSFCell cell7 = rowData.createCell(7);
            cell7.setCellValue("银企直连");
            HSSFCell cell8 = rowData.createCell(8);
            cell8.setCellValue("康慧芳");
            i++;
        }

		//设置列宽自适应
        setColumnWidth(firstSheet);
        //创建文档 写入数据
        String excelPath = "G://test.xls";
        try {
            FileOutputStream stream = new FileOutputStream(excelPath);
            workbook.write(stream);
            stream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

//poi设置自适应列宽(根据第一行自适应列宽)
    private static void setColumnWidthByFirstRow(HSSFSheet sheet){
        //sheet的索引从0开始,获取sheet列数
        int maxColumn = sheet.getRow(0).getPhysicalNumberOfCells();
        //第二种
        for (int columnNum = 0; columnNum <= maxColumn; columnNum++){
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            HSSFRow currentRow = sheet.getRow(0);

            if (currentRow.getCell(columnNum) != null){
                HSSFCell currentCell = currentRow.getCell(columnNum);
                if (currentCell.getCellType() == CellType.STRING){
                    int length = (currentCell.getStringCellValue().getBytes(StandardCharsets.UTF_8).length + currentCell.toString().length()) / 2;
                    if (columnWidth < length) {
                        columnWidth = length;
                    }
                }
            }

            //将最长的length*256设为列宽
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }

    }

//poi设置自适应列宽(根据每一列的最长字符串来计算长度)
    private static void setColumnWidth(HSSFSheet sheet) {
        //sheet的索引从0开始,获取sheet列数
        int maxColumn = sheet.getRow(0).getPhysicalNumberOfCells();
        //第二种
        for (int columnNum = 0; columnNum <= maxColumn; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            // 遍历列的数据，获取这一列的最长字符串
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(columnNum) != null) {
                    HSSFCell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == CellType.STRING) {
                        //int length = currentCell.getStringCellValue().getBytes().length;
                        //上面是网上所有currentCell获取length答案，但试过后发现偏大，在一个文章下的评论区看到下面的计算,试了一下，列宽实现了自适应大小，还没搞懂原因
                        //length = (byte长度+string长度)/2，有了解的麻烦，麻烦留言解释一下，感谢！！
                        int length = (currentCell.getStringCellValue().getBytes(StandardCharsets.UTF_8).length + currentCell.toString().length()) / 2;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            //将最长的length*256设为列宽
            // sheet.setColumnWidth((short)列数,(short)(length*256));
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }