Java poi 通过Java程序控制excel表格

﹏ヽ暗。殇╰゛Y 2022-03-18 16:52 210阅读 0赞

public class WorkSheetDetail {
        //工作内容
        private String workCtx;
        // 用工人总数	工日数 = gwnNum+tmnNum
        private Float totalHumanDays;
        //普工用工数 1-4小时为半天，4-8小时为一天;120每天
        private Integer gwnNum;
        //技工用工数 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;
    	}
    	public String getWorkCtx() {
    		return workCtx;
    	}
    	public void setWorkCtx(String workCtx) {
    		this.workCtx = workCtx;
    	}
    	public Float getTotalHumanDays() {
    		return totalHumanDays;
    	}
    	public void setTotalHumanDays(Float totalHumanDays) {
    		this.totalHumanDays = totalHumanDays;
    	}
    	public Integer getGwnNum() {
    		return gwnNum;
    	}
    	public void setGwnNum(Integer gwnNum) {
    		this.gwnNum = gwnNum;
    	}
    	public Integer getTmnNum() {
    		return tmnNum;
    	}
    	public void setTmnNum(Integer tmnNum) {
    		this.tmnNum = tmnNum;
    	}
    	public Float getUnitPrice() {
    		return unitPrice;
    	}
    	public void setUnitPrice(Float unitPrice) {
    		this.unitPrice = unitPrice;
    	}
    	public Float getUnitAmount() {
    		return unitAmount;
    	}
    	public void setUnitAmount(Float unitAmount) {
    		this.unitAmount = unitAmount;
    	}
    	public String getNotes() {
    		return notes;
    	}
    	public void setNotes(String notes) {
    		this.notes = notes;
    	}
        
    }

import java.io.FileOutputStream;
    import java.util.List;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFHeader;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.util.CellRangeAddress;
    /*
     * 这个类主要作用是建立报表，并定义其形式，如单元格大小，字体，
     * 以及如何融合多行单元格等，如何向单元格中传输数据。
     * 
     */
    
    public class ExportExcel {
    	public void getValue(List<WorkSheetDetail> userList,FileOutputStream fout){
    		  try{
    	            //1.创建工作簿
    	            HSSFWorkbook workbook = new HSSFWorkbook();
    	           
    	            // 因为要设计报表，需要还能够一定的行列融合，用来放比较多的文字，或者说设计表头
    	             //起始行,结束行,起始列,结束列
    	            	            
    	            CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,7);// xxxx项目部
    	            CellRangeAddress callRangeAddress1 = new CellRangeAddress(1,1,0,7);// 派 工 单
    	            //班组与时间start
    	            CellRangeAddress callRangeAddress20 = new CellRangeAddress(2,2,0,2);// 协作单位：x施工一堆
    	            CellRangeAddress callRangeAddress21 = new CellRangeAddress(2,2,3,4);// 
    	            CellRangeAddress callRangeAddress22 = new CellRangeAddress(2,2,5,7);// 时间：2017年 10月 20日
    	            //班组与时间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);//￥ 78 元； 大写：柒拾捌元整
     
    	            //负责人
    	            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);// 
     
    	            //部项目经理部，报表中对于不同的数据设计不同的格式，如字体大小，颜色等。
    	            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);
    	            //内容样式
    	            HSSFCellStyle cellStyle = createCellStyle(workbook,(short)10,false,true);
    	            //2.创建工作表
    	            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(20);
    	            //3.创建行
    	            //3.1创建头标题行;并且设置头标题
    	            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("协作单位：x施工一堆");
    	            cellsan1.setCellStyle(sanStyle);
    	            cellsan1.setCellValue("");
    	            cellsan2.setCellStyle(sanStyle);
    	            cellsan2.setCellValue("时间：2017年 10月 20日");
    	            
    	            //3.2创建列标题;并且设置列标题
    	            HSSFRow row2 = sheet.createRow(3);
    	            String[] titles = {"序号","工作内容","用工总人数","工日数","","单价（元）","金额(元）","备注"};//""为占位字符串
    	            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 = {"普工用工数","技工用工数"};
    	            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++)
    	                {
    	                    //创建数据行,前面有两行,头标题行和列标题行
    	                    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("协作单位负责人：");
    	            cellpersion1.setCellStyle(sanStyle);
    	            cellpersion1.setCellValue("经办人：");
    	            cellpersion2.setCellStyle(sanStyle);
    	            cellpersion2.setCellValue("部门负责人：");
    	            
    	            
    	            
    	            //说明
    	            HSSFRow rowinfo = sheet.createRow(userList.size()+7);
    	            HSSFCell cellinfo = rowinfo.createCell(0);
    	            cellinfo.setCellStyle(sanStyle);
    	            cellinfo.setCellValue("说明：1、本标工单一式两联，第一联为派工人（工长）存根，第二联用作结算。");
    	            
    	            HSSFRow rowinfo1 = sheet.createRow(userList.size()+8);
    	            HSSFCell cellinfo1 = rowinfo1.createCell(0);
    	            cellinfo1.setCellStyle(sanStyle);
    	            cellinfo1.setCellValue("2、本标工单必须在用工当日签认，否则不予认可；三日内交合同处汇总。");
    	            
    	            HSSFRow rowinfo2 = sheet.createRow(userList.size()+9);
    	            HSSFCell cellinfo2 = rowinfo2.createCell(0);
    	            cellinfo2.setCellStyle(sanStyle);
    	            cellinfo2.setCellValue("3、工日数填写精确到半个工日。");
    	           
    	            
    	            //5.输出
    	            
    	            workbook.write(fout);
    //	            workbook.close();
    	            //out.close();
    	        }catch(Exception e)
    	        {
    	            e.printStackTrace();
    	        }
    	}
    	
        /**
         * 
         * 定义单元格样式，如字体，颜色等。
         * @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(HSSFCellStyle.ALIGN_CENTER);//水平居中
            }
           
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
            //创建字体
            HSSFFont font = workbook.createFont();
            //是否加粗字体
            if(flag){
                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            }
            font.setFontHeightInPoints(fontsize);
            //加载字体
            style.setFont(font);
            return style;
        }
    }

package org;
     
    import java.io.FileOutputStream;
    import java.util.ArrayList;
    import java.util.List;
     
    public class MainOut {
    	public static void main(String args[]){
    		//模拟部分数据
    		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();  
            }  
     
    	}
    }

运行结果：：

![Center][]

[https://www.cnblogs.com/wishwzp/p/5495076.html][https_www.cnblogs.com_wishwzp_p_5495076.html]

[https://blog.csdn.net/dcb\_ripple/article/details/65435181][https_blog.csdn.net_dcb_ripple_article_details_65435181]

[https://blog.csdn.net/m0\_37899388/article/details/72778348][https_blog.csdn.net_m0_37899388_article_details_72778348]

[Center]: /images/20220318/2f13e9670f604a5d89ed6265cbdef917.png
[https_www.cnblogs.com_wishwzp_p_5495076.html]: https://www.cnblogs.com/wishwzp/p/5495076.html
[https_blog.csdn.net_dcb_ripple_article_details_65435181]: https://blog.csdn.net/dcb_ripple/article/details/65435181
[https_blog.csdn.net_m0_37899388_article_details_72778348]: https://blog.csdn.net/m0_37899388/article/details/72778348