Java操作数据到Excle

关于java 操作数据写到excle

不说废话 直接上代码

/**
 * 生成Excel
 */
public void zxExprotExcelXLSX() {
    XSSFWorkbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("0");
    for (int i = 0; i < 16; i++) {
        sheet.setColumnWidth(i, 3300);
    }
    /**
     * 单元格 样式
     */
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    /*cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
    cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
    cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
    cellStyle.setRightBorderColor(HSSFColor.BLACK.index);*/
    cellStyle.setAlignment(HorizontalAlignment .CENTER); // 水平居中
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 上下居中

    /**
     * 标题样式 样式
     */
    XSSFFont titleFont = wb.createFont();
    titleFont.setFontHeight(24);
    titleFont.setBold(true);
    CellStyle titleCellStyle = wb.createCellStyle();
    titleCellStyle.setBorderTop(BorderStyle.THIN);
    titleCellStyle.setBorderBottom(BorderStyle.THIN);
    titleCellStyle.setBorderLeft(BorderStyle.THIN);
    titleCellStyle.setBorderRight(BorderStyle.THIN);
    /*titleCellStyle.setTopBorderColor(HSSFColor.BLACK.index);
    titleCellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
    titleCellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
    titleCellStyle.setRightBorderColor(HSSFColor.BLACK.index);*/
    titleCellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
    titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 上下居中
    titleCellStyle.setFont(titleFont);

    /**
     * 主 标题 在这里插入主标题
     */
    Row titleRow;
    Cell titleCell;
    sheet.addMergedRegion(new CellRangeAddress((short) 0, (short) 2, (short) 0, (short) 16));
    for (int i = 0; i <= 2; i++) {
        titleRow = sheet.createRow(i);
        for (int j = 0; j < 9; j++) {
            titleCell = titleRow.createCell(j);
            titleCell.setCellType(CellType.STRING);
            titleCell.setCellStyle(titleCellStyle);
            titleCell.setCellValue(" 附表4 区域声环境监测记录表");
        }
    }

    // 这里是自定义设置行 
    CellRangeAddress region = new CellRangeAddress(3, 3, 0, 16);
    sheet.addMergedRegion(region);


    Row rowLabeladd = sheet.createRow(3);
    Cell cellLabeladda;

    //CellRangeAddress cellLabeladda = new CellRangeAddress(3, 3,1,9);
	//这里准备去数据库查询
    String xxx="xxxxxx";
    HashMap<String, String> map = new HashMap<>();
    map.put("x'x",x'x);
    Map<String, Object> info = manualInfoMapper.info(map);

    cellLabeladda = rowLabeladd.createCell(0);
    cellLabeladda.setCellType(CellType.STRING);
    cellLabeladda.setCellStyle(cellStyle);
    //这里拼接数据
    cellLabeladda.setCellValue("*****："+             stationName);
    CellStyle cellStyle1 = cellLabeladda.getCellStyle();
    cellStyle1.setAlignment(HorizontalAlignment.LEFT);


   //这里4 代表第四行 16 代表创建16个单元格 自定义显示内容 
    CellRangeAddress region2 = new CellRangeAddress(4, 4, 0, 16);
    sheet.addMergedRegion(region2);


    Row rowLabeladd4 = sheet.createRow(4);
    Cell cellLabeladda4;

    //CellRangeAddress cellLabeladda = new CellRangeAddress(3, 3,1,9);

    cellLabeladda4 = rowLabeladd4.createCell(0);
    cellLabeladda4.setCellType(CellType.STRING);
    cellLabeladda4.setCellStyle(cellStyle);
    StringBuffer sb = new StringBuffer("");
	//这里设置自定义列
    String f4[] = {"***：","               ***: ","                      *****:","                ******:","            *******："};


    String f5[] = {instrument,calibrationInstrument,calibration1,calibration,condition};
    String f5[] = {"50","32","24","45","34"};
    for (int i = 0; i < f4.length; i++) {
        sb.append(f4[i]).append(f5[i]);
    }


    cellLabeladda4.setCellValue(sb.toString());
    CellStyle cellStyle4 = cellLabeladda4.getCellStyle();
    cellStyle4.setAlignment(HorizontalAlignment.LEFT);



    /**
     * 列 标题 在这里插入标题
     */
     //这里是固定列 列是可以写死的
    String arr[] = {"1","2","3","4","5","6","7",......};



    Row rowLabel;
    Cell cellLabel;
	//这里填充数据 基数为5 代表从第五行开始填入数据 基数由自己决定
    for (int i = 5; i < 6; i++) {
        rowLabel = sheet.createRow(i);
        for (int a=0;a<arr.length;a++) {
            cellLabel = rowLabel.createCell(a);
            cellLabel.setCellType(CellType.STRING);
            cellLabel.setCellStyle(cellStyle);
            cellLabel.setCellValue(arr[a]);
        }
    }

    /**
     * 列 数据 在这里插入数据
     */
    List<DataNoiseArea> area = manualInfoMapper.area(map);
    List<Kyd> k=new ArrayList<>();
    String[] a=new String[16];
    SimpleDateFormat df = new SimpleDateFormat( "yyyy-MM-dd HH:00:00")
    //这里为列添加值
		String[] f= {"A","B","C","D",.....};
            Row rowCheck;
            Cell cellCheck;
            for (int i = 5; i < area.size() + 5; i++) {
                 rowCheck = sheet.createRow((i + 1));
                for (int j = 0; j< k.size(); j++) {
                cellCheck = rowCheck.createCell(j);
                cellCheck.setCellType(CellType.STRING);
                cellCheck.setCellStyle(cellStyle);
                cellCheck.setCellValue(f[j]);
                }
             }
    /**
     * 页脚
     */
    setExcelFooterName("测试", 0, wb);



    /**
     * 进行导出C:\Users\Administrator\Desktop
     */
    exportOutPutExcel("C:\\Users\\DELL\\Desktop\\YYYY.xlsx", wb);

}

/**
 * 设置Excel页脚
 */
public void setExcelFooterName(String customExcelFooterName, int setExcelFooterNumber, XSSFWorkbook wb) {
    wb.setSheetName(setExcelFooterNumber, customExcelFooterName);
}

/**
 * 输出流 导出Excel到桌面
 */
public void exportOutPutExcel(String exportPositionPath, XSSFWorkbook wb) {
    try {
        File file = new File(exportPositionPath);
        FileOutputStream fileOutputStream = new FileOutputStream(file);
        wb.write(fileOutputStream);
        fileOutputStream.close();
    } catch (IOException e) {
        System.err.println(e.getMessage());
    }
}

这是最简单的 可以改一下直接掉方法测试 ， 欢迎大家的评论
感兴趣可以加java 讨论群：1048705807