java生成帶單元格合并的Excel

package com.ding;

import lombok.Data;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * java利用poi操作excel（包括合并單元格）
 * 本未例引用自：https://blog.csdn.net/datangxiajun/article/details/78308979
 */
public class MergeCellsDemo {
    public static void main( String[] args ) {
        //模擬部分數(shù)據(jù)
        List<WorkSheetDetail> detail = new ArrayList<WorkSheetDetail>();

        WorkSheetDetail d1 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");
        WorkSheetDetail d2 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");
        WorkSheetDetail d3 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");
        WorkSheetDetail d4 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");
        WorkSheetDetail d5 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34");
        detail.add(d1);
        detail.add(d2);
        detail.add(d3);
        detail.add(d4);
        detail.add(d5);
        try{
            FileOutputStream fout = new FileOutputStream("E:/students.xls");
            new ExportExcel().getValue(detail, fout);
            fout.close();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}

class ExportExcel {
    public void getValue(List<WorkSheetDetail> userList,FileOutputStream fout){
        try{
            //1.創(chuàng)建工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //1.1創(chuàng)建合并單元格對象
            CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,7);//起始行,結束行,起始列,結束列
            CellRangeAddress callRangeAddress1 = new CellRangeAddress(1,1,0,7);//起始行,結束行,起始列,結束列
            //班組與時間start
            CellRangeAddress callRangeAddress20 = new CellRangeAddress(2,2,0,2);//起始行,結束行,起始列,結束列
            CellRangeAddress callRangeAddress21 = new CellRangeAddress(2,2,3,4);//起始行,結束行,起始列,結束列
            CellRangeAddress callRangeAddress22 = new CellRangeAddress(2,2,5,7);//起始行,結束行,起始列,結束列
            //班組與時間end

            //標題
            CellRangeAddress callRangeAddress31 = new CellRangeAddress(3,4,0,0);//起始行,結束行,起始列,結束列
            CellRangeAddress callRangeAddress32 = new CellRangeAddress(3,4,1,1);//起始行,結束行,起始列,結束列
            CellRangeAddress callRangeAddress33 = new CellRangeAddress(3,4,2,2);//起始行,結束行,起始列,結束列
            CellRangeAddress callRangeAddress34 = new CellRangeAddress(3,3,3,4);//起始行,結束行,起始列,結束列
            CellRangeAddress callRangeAddress35 = new CellRangeAddress(3,4,5,5);//起始行,結束行,起始列,結束列
            CellRangeAddress callRangeAddress36 = new CellRangeAddress(3,4,6,6);//起始行,結束行,起始列,結束列
            CellRangeAddress callRangeAddress37 = new CellRangeAddress(3,4,7,7);//起始行,結束行,起始列,結束列

            //金額
            CellRangeAddress callRangeAddressnumber1 = new CellRangeAddress(userList.size()+5,userList.size()+5,0,2);//起始行,結束行,起始列,結束列
            CellRangeAddress callRangeAddressnumber2 = new CellRangeAddress(userList.size()+5,userList.size()+5,3,7);//起始行,結束行,起始列,結束列

            //負責人
            CellRangeAddress callRangeAddressPersion1 = new CellRangeAddress(userList.size()+6,userList.size()+6,0,2);//起始行,結束行,起始列,結束列
            CellRangeAddress callRangeAddressPersion2 = new CellRangeAddress(userList.size()+6,userList.size()+6,3,4);//起始行,結束行,起始列,結束列
            CellRangeAddress callRangeAddressPersion3 = new CellRangeAddress(userList.size()+6,userList.size()+6,5,7);//起始行,結束行,起始列,結束列

            //說明
            CellRangeAddress callRangeAddressinfo = new CellRangeAddress(userList.size()+7,userList.size()+7,0,7);//起始行,結束行,起始列,結束列
            CellRangeAddress callRangeAddressinfo1 = new CellRangeAddress(userList.size()+8,userList.size()+8,0,7);//起始行,結束行,起始列,結束列
            CellRangeAddress callRangeAddressinfo2 = new CellRangeAddress(userList.size()+9,userList.size()+9,0,7);//起始行,結束行,起始列,結束列

            //部項目經(jīng)理部
            HSSFCellStyle headStyle = createCellStyle(workbook,(short)10,false,true);
            //派工單
            HSSFCellStyle erStyle = createCellStyle(workbook,(short)13,true,true);
            //班組和時間
            HSSFCellStyle sanStyle = createCellStyle(workbook,(short)10,false,false);
            //標題樣式
            HSSFCellStyle colStyle = createCellStyle(workbook,(short)10,true,true);
            //內(nèi)容樣式
            HSSFCellStyle cellStyle = createCellStyle(workbook,(short)10,false,true);
            //2.創(chuàng)建工作表
            HSSFSheet sheet = workbook.createSheet("派單");
            //2.1加載合并單元格對象
            sheet.addMergedRegion(callRangeAddress);
            sheet.addMergedRegion(callRangeAddress1);
            sheet.addMergedRegion(callRangeAddress20);
            sheet.addMergedRegion(callRangeAddress21);
            sheet.addMergedRegion(callRangeAddress22);
            sheet.addMergedRegion(callRangeAddress31);
            sheet.addMergedRegion(callRangeAddress32);
            sheet.addMergedRegion(callRangeAddress33);
            sheet.addMergedRegion(callRangeAddress34);
            sheet.addMergedRegion(callRangeAddress35);
            sheet.addMergedRegion(callRangeAddress36);
            sheet.addMergedRegion(callRangeAddress37);
            sheet.addMergedRegion(callRangeAddressnumber1);
            sheet.addMergedRegion(callRangeAddressnumber2);
            sheet.addMergedRegion(callRangeAddressPersion1);
            sheet.addMergedRegion(callRangeAddressPersion2);
            sheet.addMergedRegion(callRangeAddressPersion3);
            sheet.addMergedRegion(callRangeAddressinfo);
            sheet.addMergedRegion(callRangeAddressinfo1);
            sheet.addMergedRegion(callRangeAddressinfo2);
            //設置默認列寬
            sheet.setDefaultColumnWidth(15);
            //3.創(chuàng)建行
            //3.1創(chuàng)建頭標題行;并且設置頭標題
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);
            //加載單元格樣式
            cell.setCellStyle(headStyle);
            cell.setCellValue("xxxx項目部");

            HSSFRow rower = sheet.createRow(1);
            HSSFCell celler = rower.createCell(0);
            //加載單元格樣式
            celler.setCellStyle(erStyle);
            celler.setCellValue("派 工 單");

            HSSFRow rowsan = sheet.createRow(2);
            HSSFCell cellsan = rowsan.createCell(0);
            HSSFCell cellsan1 = rowsan.createCell(3);
            HSSFCell cellsan2 = rowsan.createCell(5);
            //加載單元格樣式
            cellsan.setCellStyle(sanStyle);
            cellsan.setCellValue("協(xié)作單位：x施工一堆");
            cellsan1.setCellStyle(sanStyle);
            cellsan1.setCellValue("");
            cellsan2.setCellStyle(sanStyle);
            cellsan2.setCellValue("時間：2017年 10月 20日");

            //3.2創(chuàng)建列標題;并且設置列標題
            HSSFRow row2 = sheet.createRow(3);
            String[] titles = {"序號","工作內(nèi)容","用工總人數(shù)","工日數(shù)","","單價（元）","金額(元）","備注"};//""為占位字符串
            for(int i=0;i<titles.length;i++)
            {
                HSSFCell cell2 = row2.createCell(i);
                //加載單元格樣式
                cell2.setCellStyle(colStyle);
                cell2.setCellValue(titles[i]);
            }

            HSSFRow rowfour = sheet.createRow(4);
            String[] titlefour = {"普工用工數(shù)","技工用工數(shù)"};
            for(int i=0;i<titlefour.length;i++)
            {
                HSSFCell cell2 = rowfour.createCell(i+3);
                //加載單元格樣式
                cell2.setCellStyle(colStyle);
                cell2.setCellValue(titlefour[i]);
            }


            //4.操作單元格;將用戶列表寫入excel
            if(userList != null)
            {
                int i=1;
                for(int j=0;j<userList.size();j++)
                {
                    //創(chuàng)建數(shù)據(jù)行,前面有兩行,頭標題行和列標題行
                    HSSFRow row3 = sheet.createRow(j+5);
                    HSSFCell cell0 = row3.createCell(0);
                    cell0.setCellStyle(cellStyle);
                    cell0.setCellValue(i++);

                    HSSFCell cell1 = row3.createCell(1);
                    cell1.setCellStyle(cellStyle);
                    cell1.setCellValue(userList.get(j).getWorkCtx());

                    HSSFCell cell2 = row3.createCell(2);
                    cell2.setCellStyle(cellStyle);
                    cell2.setCellValue(userList.get(j).getTotalHumanDays());

                    HSSFCell cell3 = row3.createCell(3);
                    cell3.setCellStyle(cellStyle);
                    cell3.setCellValue(userList.get(j).getGwnNum());

                    HSSFCell cell4 = row3.createCell(4);
                    cell4.setCellStyle(cellStyle);
                    cell4.setCellValue(userList.get(j).getTmnNum());

                    HSSFCell cell5 = row3.createCell(5);
                    cell5.setCellStyle(cellStyle);
                    cell5.setCellValue(userList.get(j).getTotalHumanDays());

                    HSSFCell cell6 = row3.createCell(6);
                    cell6.setCellStyle(cellStyle);
                    cell6.setCellValue(userList.get(j).getUnitAmount());

                    HSSFCell cell7= row3.createCell(7);
                    cell7.setCellStyle(cellStyle);
                    cell7.setCellValue(userList.get(j).getUnitPrice());
                }
            }

            HSSFRow rownumber = sheet.createRow(userList.size()+5);
            HSSFCell cellnumber = rownumber.createCell(0);
            HSSFCell cellnumber1 = rownumber.createCell(3);
            //加載單元格樣式
            cellnumber.setCellStyle(sanStyle);
            cellnumber.setCellValue("金額合計(大寫)");
            cellnumber1.setCellStyle(sanStyle);
            cellnumber1.setCellValue("￥ 78 元； 大寫：柒拾捌元整");

            HSSFRow rowpersion = sheet.createRow(userList.size()+6);
            HSSFCell cellpersion = rowpersion.createCell(0);
            HSSFCell cellpersion1 = rowpersion.createCell(3);
            HSSFCell cellpersion2 = rowpersion.createCell(5);

            //加載單元格樣式
            cellpersion.setCellStyle(sanStyle);
            cellpersion.setCellValue("協(xié)作單位負責人：");
            cellpersion1.setCellStyle(sanStyle);
            cellpersion1.setCellValue("經(jīng)辦人：");
            cellpersion2.setCellStyle(sanStyle);
            cellpersion2.setCellValue("部門負責人：");

            HSSFRow rowinfo = sheet.createRow(userList.size()+7);
            HSSFCell cellinfo = rowinfo.createCell(0);
            cellinfo.setCellStyle(sanStyle);
            cellinfo.setCellValue("說明：1、本標工單一式兩聯(lián)，第一聯(lián)為派工人（工長）存根，第二聯(lián)用作結算。");

            HSSFRow rowinfo1 = sheet.createRow(userList.size()+8);
            HSSFCell cellinfo1 = rowinfo1.createCell(0);
            cellinfo1.setCellStyle(sanStyle);
            cellinfo1.setCellValue("2、本標工單必須在用工當日簽認，否則不予認可；三日內(nèi)交合同處匯總。");

            HSSFRow rowinfo2 = sheet.createRow(userList.size()+9);
            HSSFCell cellinfo2 = rowinfo2.createCell(0);
            cellinfo2.setCellStyle(sanStyle);
            cellinfo2.setCellValue("3、工日數(shù)填寫精確到半個工日。");
            //5.輸出
            workbook.write(fout);
//              workbook.close();
            //out.close();
        }catch(Exception e)
        {
            e.printStackTrace();
        }
    }
    /**
     *
     * @param workbook
     * @param fontsize
     * @return 單元格樣式
     */
    private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize,boolean flag,boolean flag1) {
        // TODO Auto-generated method stub
        HSSFCellStyle style = workbook.createCellStyle();
        //是否水平居中
        if(flag1){
            style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        }

        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //創(chuàng)建字體
        HSSFFont font = workbook.createFont();
        //是否加粗字體
        if(flag){
            font.setBold(true);
        }
        font.setFontHeightInPoints(fontsize);
        //加載字體
        style.setFont(font);
        return style;
    }
}

@Data
class WorkSheetDetail {
    //工作內(nèi)容
    private String workCtx;
    // 用工人總數(shù)    工日數(shù) = gwnNum+tmnNum
    private Float totalHumanDays;
    //普工用工數(shù) 1-4小時為半天，4-8小時為一天;120每天
    private Integer gwnNum;
    //技工用工數(shù) 1-4小時為半天，4-8小時為一天；160每天
    private Integer tmnNum;
    // 單價（元）
    private Float unitPrice;
    // 金額(元） = gwnNum*120+tmnNum+160
    private Float unitAmount;
    // 備注
    private String notes;

    public WorkSheetDetail(String workCtx, Float totalHumanDays, Integer gwnNum, Integer tmnNum, Float unitPrice,
                           Float unitAmount, String notes) {
        super();
        this.workCtx = workCtx;
        this.totalHumanDays = totalHumanDays;
        this.gwnNum = gwnNum;
        this.tmnNum = tmnNum;
        this.unitPrice = unitPrice;
        this.unitAmount = unitAmount;
        this.notes = notes;
    }
}

相關pom依賴如下

        <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.4</version>
            <scope>provided</scope>
        </dependency>


        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.0</version>
        </dependency>
?著作權歸作者所有,轉載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內(nèi)容

  • Spring Boot 參考指南 介紹 轉載自:https://www.gitbook.com/book/qbgb...
    毛宇鵬閱讀 47,253評論 6贊 342
  • Spring Cloud為開發(fā)人員提供了快速構建分布式系統(tǒng)中一些常見模式的工具（例如配置管理，服務發(fā)現(xiàn)，斷路器，智...
    卡卡羅2017閱讀 136,506評論 19贊 139
  • 簡介 概述 Maven 是一個項目管理和整合工具 Maven 為開發(fā)者提供了一套完整的構建生命周期框架 Maven...
    閩越布衣閱讀 4,507評論 6贊 39
  • Maven編譯代碼的相關命令 第一、main目錄下的主代碼編寫完畢后，使用Maven進行編譯，在項目根目錄下運行命...
    加油小杜閱讀 1,436評論 0贊 2
  • 一 今天是五四青年節(jié)，收到了一條祝福：愿你出走半生，歸來仍是少年。我笑了。 2016年，孫衍的這碗雞湯《愿你出走半...
    Avanta閱讀 820評論 6贊 6

友情鏈接更多精彩內(nèi)容