java中对Excel的创建、样式修改

创建excel文件

public static String createExcel(ArrayList<String> list, ArrayList<ArrayList<String>> DataList, String dateStr) {
        // 创建一个excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建一个excel的sheet（表）
        HSSFSheet sheet = workbook.createSheet();

        // 创建第一行
        HSSFRow row0 = sheet.createRow(0);
        // 设置首行行高
        Row row = sheet.getRow(0);
        row.setHeightInPoints(30);
        // 创建第一列
        HSSFCell cellTitle = row0.createCell(0);
        // 合并单元格
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4 + (list.size() - 5) * 2);// 起始行号，终止行号，
        // 起始列号，终止列号
        sheet.addMergedRegion(cellRangeAddress);
        int j = 5;
        for (int i = 0; i < list.size() - 5; i++) {
            CellRangeAddress cellRangeAddress1 = new CellRangeAddress(1, 1, j + i, j + 1 + i);
            sheet.addMergedRegion(cellRangeAddress1);
            j++;
        }
        for (int i = 0; i < 5; i++) {
            CellRangeAddress cellRangeAddress3 = new CellRangeAddress(1, 2, i, i);
            sheet.addMergedRegion(cellRangeAddress3);
        }
        // 给第一个单元格赋值(标题行)
        cellTitle.setCellValue(dateStr + "考勤签到记录表");
        cellTitle.setCellStyle(getRow0Style(workbook));

        // 创建第二行(列名行)
        int t = 1;
        HSSFRow row1 = sheet.createRow(1);
        row1.setHeightInPoints(20);
        for (int i = 0; i < list.size(); i++) {
            HSSFCell cell1 = null;
            // 循环创建第二行各列
            if (i > 5) {
                cell1 = row1.createCell(i + t);
                t++;
            } else {
                cell1 = row1.createCell(i);
            }
            // 设置单元格数据类型
            cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
            HSSFRichTextString text = null;
            if (i < list.size()) {
                text = new HSSFRichTextString(list.get(i));
                // 给每列赋值
                // System.out.println(text+"____"+i+"_________"+list.size());
                cell1.setCellValue(text);
            }
        }
        // 给单元格添加样式
        for (int i = 0; i <= 4 + (list.size() - 5) * 2; i++) {
            HSSFCell cell = row1.getCell(i);
            if (cell == null) {
                cell = row1.createCell(i);
                cell.setCellValue("");
            }
            cell.setCellStyle(getStyle(workbook));
        }

        // 创建第三行（早上下午）
        HSSFRow row3 = sheet.createRow(2);
        row3.setHeightInPoints(15);

        for (int i = 5; i <= 4 + (list.size() - 5) * 2; i++) {
            HSSFCell cell = row3.createCell(i);
            // 设置单元格数据类型
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (i % 2 == 1) {
                cell.setCellValue("早");
            } else {
                cell.setCellValue("晚");
            }
        }
        // 给单元格添加样式
        for (int i = 0; i <= 4 + (list.size() - 5) * 2; i++) {
            HSSFCell cell = row3.getCell(i);
            if (cell == null) {
                cell = row3.createCell(i);
                cell.setCellValue("");
            }
            cell.setCellStyle(getStyle3(workbook));
        }

        // 创建数据行
        for (int k = 0; k < DataList.size(); k++) {
            HSSFRow row2 = sheet.createRow(3 + k);
            ArrayList<String> datArr = DataList.get(k);
            row2.setHeightInPoints(20);
            for (int i = 0; i < datArr.size(); i++) {
                // 循环创建第三行各列
                HSSFCell cell2 = row2.createCell(i);
                // 设置单元格样式
                cell2.setCellStyle(getStyle(workbook));
                // 给每一列赋值
                String str = datArr.get(i);
                cell2.setCellValue(str);
            }
        }
        sheet.autoSizeColumn((short) 0); // 调整第一列宽度
        sheet.autoSizeColumn((short) 1); // 调整第二列宽度
        sheet.autoSizeColumn((short) 2); // 调整第三列宽度
        try { // 创建输出流
            File file = new File("C:\Work\WeX5_3.8_BSBZB\model\UI2\excel\" + dateStr + ".xls");// official服务器path
            // File file = new
            // File("F:\Wex5Project\WeX5_3.8_1_beiSong\model\UI2\excel\" + dateStr +
            // ".xls");//my path
            FileOutputStream fileout = new FileOutputStream(file);
            // 将excel写入输出流
            workbook.write(fileout);
            fileout.flush();
            fileout.close();
            System.out.println("创建完成!");
        } catch (Exception e) {
            e.printStackTrace();
        }
        // String fiel = "http://192.168.1.156:8181/x5/UI2/excel/" + dateStr +
        // ".xls";//my path
        /*official服务器path北送*/
        String fiel = "http://111.198.48.184:8888/x5/UI2/excel/" + dateStr + ".xls";
        return fiel;
    }