java导出excel合并单元格及设置

功能是导出表格

package checkout_excel;
 

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_1().getValue(detail, fout);
            fout.close();  
        }  
        catch (Exception e)  
        {  
            e.printStackTrace();  
        }  
 
	}
}
package checkout_excel;

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;
		}
	    
	}




package checkout_excel;

 
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;
import org.apache.poi.ss.util.RegionUtil;
public class ExportExcel_1 {
	public void getValue(List<WorkSheetDetail> userList,FileOutputStream fout){
		  try{
	            //1.创建工作簿
	            HSSFWorkbook workbook = new HSSFWorkbook();
	            //2.创建工作表
	            HSSFSheet sheet = workbook.createSheet("派单");
	            //1.1创建合并单元格对象
	            CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,6);//起始行,结束行,起始列,结束列
	            CellRangeAddress callRangeAddress1 = new CellRangeAddress(1,1,0,6);//起始行,结束行,起始列,结束列
// 
	            //部项目经理部
	            HSSFCellStyle headStyle = createCellStyle(workbook,(short)20,true,true,false);
	            //派工单
	            HSSFCellStyle erStyle = createCellStyle(workbook,(short)15,false,false,true);
	            //内容样式
	            HSSFCellStyle cellStyle = createCellStyle(workbook,(short)10,false,true,false);
	            
	            //设置默认列宽
	            sheet.setDefaultColumnWidth(15);
	            //2.1加载合并单元格对象
	            sheet.addMergedRegion(callRangeAddress);
	            sheet.addMergedRegion(callRangeAddress1);
	            //3.创建行
	            //3.1创建头标题行;并且设置头标题
	            HSSFRow row = sheet.createRow(0);
	            HSSFCell cell = row.createCell(0);
        		cell.setCellStyle(headStyle); //style为带边框的样式 上面有定义
        		cell.setCellValue("表里");
	            
	            HSSFRow rower = sheet.createRow(1);
	            HSSFCell celler = rower.createCell(0);
        		celler.setCellStyle(erStyle); //style为带边框的样式 上面有定义
            	celler.setCellValue("派 工 单");
	            
            	createCellsStyle(workbook,sheet,callRangeAddress,true);
            	createCellsStyle(workbook,sheet,callRangeAddress1,true);

	            //3.2创建列标题;并且设置列标题
	            HSSFRow row2 = sheet.createRow(2);
	            String[] titles = {"序号","部门","总数", "成功数", "未知数", "失败数", "成功率"};//""为占位字符串
	            for(int i=0;i<titles.length;i++)
	            {
	                HSSFCell cell2 = row2.createCell(i);
	                //加载单元格样式
	                cell2.setCellStyle(cellStyle);
	                cell2.setCellValue(titles[i]);
	            }
	            
	            
	            //4.操作单元格;将用户列表写入excel
	            if(userList != null)
	            {
	            	int i=1;
	                for(int j=0;j<userList.size();j++)
	                {
	                    //创建数据行,前面有两行,头标题行和列标题行
	                    HSSFRow row3 = sheet.createRow(j+3);
	                    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());
	                    
	                }
	            }
	            
	            //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,boolean flag2) {
        // TODO Auto-generated method stub
        HSSFCellStyle style = workbook.createCellStyle();
        
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  //上边框
        
        //是否水平居中
        if(flag1){
        	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        }
        if(flag2){
        	style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//靠右
        }
       
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        //创建字体
        HSSFFont font = workbook.createFont();
        //是否加粗字体
        if(flag){
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        return style;
    }
    
    /**
     * 
     * @param workbook
     * @param fontsize
     * @return 合并后单元格样式
     */
    private static void createCellsStyle(HSSFWorkbook workbook,HSSFSheet sheet ,CellRangeAddress callRangeAddress, boolean flag) {
        
        if (flag){
            RegionUtil.setBorderBottom(1, callRangeAddress, sheet, workbook); // 下边框
            RegionUtil.setBorderLeft(1, callRangeAddress, sheet, workbook); // 左边框
            RegionUtil.setBorderRight(1, callRangeAddress, sheet, workbook); // 右边框
            RegionUtil.setBorderTop(1, callRangeAddress, sheet, workbook); // 上边框
        }
        
    }

    
}


结果如下:

  • 0
    点赞
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值