java导出excel合并单元格

Java导出Excel合并单元格

千次阅读 2019-02-07 13:56:11

Java导出Excel合并单元格

                1、问题背景
     利用POI导出Excel表格，在导出的过程中涉及到双表头，即需要合并单元格

2、实现源码
/** *  * @Project:Report * @Title:MergeCell.java * @Package:com.you.excel * @Description: * @Author:YouHaiDong * @Date:2015年11月4日 下午2:36:46 * @Version: */package com.you.excel;import java.io.FileOutputStream;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRichTextString;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.hssf.util.Region;/** * <p>合并单元格</p> * @ClassName:MergeCell * @Description: * @Author:YouHaiDong * @Date:2015年11月4日 下午2:36:46 *  */public class MergeCell { /**  * 合并单元格  * @Title:MergeCell  * @Description:  * @param args  * @Date:2015年11月4日 下午2:36:46  * @return: void   * @throws Exception  */ @SuppressWarnings({ "resource", "deprecation" }) public static void main(String[] args) throws Exception  {  //创建workbook   HSSFWorkbook workbook = new HSSFWorkbook();   //创建sheet页     HSSFSheet sheet = workbook.createSheet("学生表");   //创建单元格  HSSFRow row = sheet.createRow(0);   HSSFCell c0 = row.createCell(0);   c0.setCellValue(new HSSFRichTextString("学号"));   HSSFCell c1 = row.createCell(1);   c1.setCellValue(new HSSFRichTextString("姓名"));   HSSFCell c2 = row.createCell(2);   c2.setCellValue(new HSSFRichTextString("性别"));   HSSFCell c3 = row.createCell(3);   c3.setCellValue(new HSSFRichTextString("年龄"));   HSSFCell c4 = row.createCell(4);   c4.setCellValue(new HSSFRichTextString("2015年分数"));   HSSFCell c5 = row.createCell(7);   c5.setCellValue(new HSSFRichTextString("2014年分数"));   HSSFRow row1 = sheet.createRow(1);   HSSFCell c6 = row1.createCell(4);   c6.setCellValue(new HSSFRichTextString("语文"));   HSSFCell c7 = row1.createCell(5);   c7.setCellValue(new HSSFRichTextString("数学"));   HSSFCell c8 = row1.createCell(6);   c8.setCellValue(new HSSFRichTextString("外语"));  HSSFCell c9 = row1.createCell(7);   c9.setCellValue(new HSSFRichTextString("语文"));   HSSFCell c10 = row1.createCell(8);   c10.setCellValue(new HSSFRichTextString("数学"));   HSSFCell c11 = row1.createCell(9);   c11.setCellValue(new HSSFRichTextString("外语"));    Region region1 = new Region(0, (short)0, 1, (short)0);   Region region2 = new Region(0, (short)1, 1, (short)1);   Region region3 = new Region(0, (short)2, 1, (short)2);   Region region4 = new Region(0, (short)3, 1, (short)3);   Region region5 = new Region(0, (short)4, 0, (short)6);   Region region6 = new Region(0, (short)7, 0, (short)9);   sheet.addMergedRegion(region1);   sheet.addMergedRegion(region2);   sheet.addMergedRegion(region3);   sheet.addMergedRegion(region4);   sheet.addMergedRegion(region5);   sheet.addMergedRegion(region6);     FileOutputStream stream = new FileOutputStream("d:/student.xls");   workbook.write(stream); }}

3、实现结果

            
再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！https://blog.csdn.net/jiangjunshow

收起

展开全文

java导出Excel合并单元格

万次阅读 多人点赞 2017-10-22 11:43:03

网上java导出excel表格并合并单元格的资料不完全，我整理了一份，并亲测能用，附截图。 ①java导出excel用到POI所有jar包，大家可以直接到下面地址下载点击打开链接 ②模拟数据类 package org; public class ...

网上java导出excel表格并合并单元格的资料不完全，我整理了一份，并亲测能用，附截图。
①java导出excel用到POI所有jar包，大家可以直接到下面地址下载点击打开链接
②模拟数据类

package org;

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


③java导出excel方法


package org;
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();
	            //1.1创建合并单元格对象
	            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);//起始行,结束行,起始列,结束列

	            //部项目经理部
	            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(15);
	            //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();
	        }
	}
	
    /**
     * 
     * @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(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;
    }
}




④main方法

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

	}
}


⑤截图

收起

展开全文

java导出excel合并单元格

千次阅读 2018-12-31 13:17:32

1、java导出excel用到POI所有jar包，大家可以直接到下面地址下载点击打开链接 2、导出excel的方法 package org; import java.io.FileOutputStream; import java.util.List; import org.apache.poi.hssf....

今天是2018最后一天了，废话就不多说了直接上干货吧！

1、java导出excel用到POI所有jar包 ，大家可以直接到下面地址下载点击打开链接

2、导出excel的方法

package org;
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();
	            //1.1创建合并单元格对象
	            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);//起始行,结束行,起始列,结束列
 
	            //部项目经理部
	            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(15);
	            //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();
	        }
	}
	
    /**
     * 
     * @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(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;
    }
}


3、main方法

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


4、实体类

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


5、效果图

收起

展开全文

java导出excel合并单元格_Java 导出Excel 合并Excel单元格

2021-02-12 18:15:04

/***导出Excel表格**@paramallList要导出的数据*@paramheadArrjson键值对*@paramtitleArrexcel标题*@paramtitleExcel名字*@parammergeColumn要合并的列*/publicvoidexportExcel(JSONArrayallList,String[]headA...

/**
* 导出Excel表格
*
* @param allList  要导出的数据
* @param headArr  json键值对
* @param titleArr excel标题
* @param title    Excel名字
* @param mergeColumn  要合并的列
*/
public void exportExcel(JSONArray allList, String[] headArr, String title, String[] titleArr,int[] mergeColumn, HttpServletResponse response) {
try {
//通过Response把数据以Excel格式保存
response.reset();
response.setContentType("application/msexcel;charset=UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=\""
+ new String((title + ".xls").getBytes("GBK"),
"ISO8859_1") + "\"");
OutputStream out = response.getOutputStream();
WritableWorkbook workbook = Workbook.createWorkbook(out);
WritableSheet sheet = workbook.createSheet(title, 0);
WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat wcf = new WritableCellFormat(wf);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf.setAlignment(Alignment.CENTRE);
//CellView cellView = new CellView();
for (int i = 0; i 
Label label = new Label(i, 0, titleArr[i],wcf);
sheet.addCell(label);
//                CellView cellView = new CellView();
//                cellView.setAutosize(true); //设置自动大小
//                sheet.setColumnView(i, cellView);//根据内容自动设置列宽
}
long s1 = System.nanoTime();
//格式化数据，垂直居中，和水平居中
WritableCellFormat cellFormat = new WritableCellFormat();
cellFormat.setAlignment(Alignment.CENTRE);
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
for (int i = 0; i 
for (int j = 0; j 
Label label = new Label(j, i + 1, JSONObject.fromObject(allList.get(i)).get(headArr[j]) == null ? "" : JSONObject.fromObject(allList.get(i)).get(headArr[j]).toString());
label.setCellFormat(cellFormat);
sheet.addCell(label);
//                    CellView cellView = new CellView();
//                    cellView.setAutosize(true); //设置自动大小
//                    sheet.setColumnView(i, cellView);//根据内容自动设置列宽
}
}
//前一行数据
String[] pre_RowData=new String[headArr.length];
//记住第一行数据
for(int i=0;i
pre_RowData[i]= sheet.getCell(i,1).getContents().toString();
}
//循环要合并得列
for(int i=0;i
int count=0;
//获取所有的行数，因为第一行是表头，所以从第二行开始，即j=1开始
for(int j=1;j
//从第二行开始，合并的时候，判断前一行是否相等，若相等，则这一列数据相等，才合并，若不相等，这一列数据相等也不合并
if(i>0&&j>1){
//判断这个单元格前一个单元格的值与这个单元格前一个单元格的上一个单元格的值是否相等
if(sheet.getCell(mergeColumn[i-1],j).getContents().equals(pre_RowData[i-1])){
//判断这个单元格的值与这个单元格的上一个单元格的值是否相等
if(sheet.getCell(mergeColumn[i],j).getContents().equals(pre_RowData[i])){
count++;
}else {
pre_RowData[i]=sheet.getCell(mergeColumn[i],j).getContents();
j--;
//合并单元格
sheet.mergeCells(mergeColumn[i],j-count+1,mergeColumn[i],j);
count=0;
}
}
//前两行单元格的值不相等时，则应合并以上本单元格中相等的单元格
else {
pre_RowData[i]=sheet.getCell(mergeColumn[i],j).getContents();
pre_RowData[i-1]=sheet.getCell(mergeColumn[i-1],j).getContents();
j--;
//合并单元格
sheet.mergeCells(mergeColumn[i],j-count+1,mergeColumn[i],j);
count=0;
}
//第一列到最后时，需要对以上相同的进行合并
if(j+1==sheet.getRows()){
sheet.mergeCells(mergeColumn[i],j-count+1,mergeColumn[i],j);
count=0;
//第一列比较结束，重新给pre_RowData赋值，以便对下一列进行比较时
pre_RowData[i]=sheet.getCell(i,1).getContents();
}
}else{
//判断这个单元格的值与这个单元格的上一个单元格的值是否相等
if(sheet.getCell(mergeColumn[i],j).getContents().equals(pre_RowData[i])){
count++;
}else {
pre_RowData[i]=sheet.getCell(mergeColumn[i],j).getContents();
j--;
//合并单元格
sheet.mergeCells(mergeColumn[i],j-count+1,mergeColumn[i],j);
count=0;
}
//第一列到最后时，需要对以上相同的进行合并
if(j+1==sheet.getRows()){
sheet.mergeCells(mergeColumn[i],j-count+1,mergeColumn[i],j);
count=0;
//重新给pre_RowData赋值，以便第二次比较时，比较前一单元格
pre_RowData[i]=sheet.getCell(i,1).getContents();
}
}
}
}
workbook.write();
workbook.close();
long s2 = System.nanoTime();
System.out.println("jxl write ......." + " rows to excel:" + (s2 - s1));
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

收起

展开全文

java导出Excel合并单元格&&POI 导出excel文件下拉框问题
2019-10-08 14:37:52
POI 导出excel文件下拉框问题 1.https://blog.csdn.net/sdaujsj1/article/details/81004209 java导出Excel合并单元格 2https://blog.csdn.net/datangxiajun/article/details/78308979
收起
java excel 导出单元格合并_java导出excel合并单元格.docx
2021-02-27 19:41:29
// 指定单元格垂直居中对齐 cellStyle.setWrapText(true); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle....
收起
java 生成excel 单元格合并_Java导出Excel合并单元格
2021-02-26 17:54:04
1、问题背景利用POI导出Excel表格，在导出的过程中涉及到双表头，即需要合并单元格2、实现源码/**** @Project:Report* @Title:MergeCell.java* @Package:com.you.excel* @Description:* @Author:YouHaiDong* @Date:...
收起
Java导出excel合并单元格边框消失问题
2020-12-21 11:17:05
合并单元格代码： CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 1, 0, 0); sheet.addMergedRegion(cellRangeAddress); //加入边框 setBorderStyle(BorderStyle.THIN, cellRangeAddress, sheet);...
收起
poi
java导出excel合并单元格_Springboot导出excel，合并单元格示例
2021-02-12 18:15:07
//创建poi导出数据对象 SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(); //创建sheet页 SXSSFSheet sheet = sxssfWorkbook.createSheet("开复工项目"); CellRangeAddress region1 = new CellRangeAddress(0, 1...
收起
java导出excel合并单元格及设置
千次阅读 2019-04-23 15:16:12
功能是导出表格 package checkout_excel; import java.io.FileOutputStream; import java.util.ArrayList; import java.util.List; public class MainOut { public static void main(String args[]){ //...
收起
java excel动态导出excel_java动态导出excel合并单元格
2021-02-27 19:42:56
标签：采用的是poi技术框架式 ssh 使用的是struts1public void outExcel(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse ...第一步，创建一个webbook，对应一个Excel文件...
收起
java导出excel 边框不全_POI 导出Excel合并单元格后部分边框不显示
2021-02-26 19:29:45
用户需要导出自定义表格，其中合并单元格样式遇到的问题，合并后只显示第一行第一列的边框，其他边框不显示，于是遍查百度，寻到一点思路①了解Excel绘制原理②了解绘制Excel顺序③绘制Excel单元格的三种方法先传...
收起
java excel 加边框_java poi导出Excel合并单元格并设置边框
2021-02-12 18:36:48
if (deptRowEndIndex-1 >deptRowIndex) {//合并单元格 CellRangeAddress cellRange = new CellRangeAddress(deptRowIndex, deptRowEndIndex-1, (short) 1, (short) 1); sheet.addMergedRegion(cellRange);//为合并...
收起
java poi导出Excel合并单元格并设置边框
千次阅读 2019-07-12 10:55:00
为合并单元格添加边框 RegionUtil.setBorderTop(1 , cellRange, sheet, workbook); RegionUtil.setBorderBottom( 1 , cellRange, sheet, workbook); RegionUtil.setBorderLeft( 1 , cellRange, sheet, workbook)...
收起
java excel 合并单元格_java实现数据的Excel导出（合并单元格、样式等）
2021-02-12 14:11:53
/****/package zhongdian.whh....import java.io.IOException;import java.io.UnsupportedEncodingException;import java.util.ArrayList;import java.util.List;import javax.servlet.http.HttpServletRes...
收起
java excel 列合并单元格_java导出excel，某几列合并单元格。
2021-02-25 18:42:13
如图：导出的功能，就不在这里做过多的解释了。如果最后一行是合计，或者其他的内容。例如：如上图所示。那么只要把此方法写在【合计】前方就好// 合并【学员姓名】之前相同的列表mergeContent(sheet);// 合计// ......
收起
导出excel合并单元格
2020-12-02 17:42:32
* 导出excel工具类(支持合并单元格) * * @author yulisao * @createDate 2020年11月16日 */ public class mergeCellUtil { private static final short FONT_SIZE_TITLE = 14; //标题字体大小 private ...
收起
java excel表格合并多列_java 通用的导出 excel 合并单元格格式(二)
2021-02-26 18:36:21
import java.io.BufferedReader;import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileNotF...
收起

Java导出Excel合并单元格

java导出Excel合并单元格

java导出excel合并单元格

java导出excel合并单元格_Java 导出Excel 合并Excel单元格

java导出Excel合并单元格&&POI 导出excel文件下拉框问题

java excel 导出单元格合并_java导出excel合并单元格.docx

java 生成excel 单元格合并_Java导出Excel合并单元格

Java导出excel合并单元格边框消失问题

java导出excel合并单元格_Springboot导出excel，合并单元格示例

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

java excel动态导出excel_java动态导出excel合并单元格

java导出excel 边框不全_POI 导出Excel合并单元格后部分边框不显示

java excel 加边框_java poi导出Excel合并单元格并设置边框

java poi导出Excel合并单元格并设置边框

java excel 合并单元格_java实现数据的Excel导出（合并单元格、样式等）

java excel 列合并单元格_java导出excel，某几列合并单元格。

导出excel合并单元格

java excel表格合并多列_java 通用的导出 excel 合并单元格格式(二)

java导出excel合并单元格

Java导出Excel合并单元格

java导出Excel合并单元格

java导出excel合并单元格

java导出excel合并单元格_Java 导出Excel 合并Excel单元格

java导出Excel合并单元格&&POI 导出excel文件下拉框问题

java excel 导出 单元格合并_java导出excel合并单元格.docx

java 生成excel 单元格合并_Java导出Excel合并单元格

Java导出excel合并单元格边框消失问题

java导出excel合并单元格_Springboot导出excel，合并单元格示例

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

java excel动态导出excel_java动态导出excel合并单元格

java导出excel 边框不全_POI 导出Excel合并单元格后部分边框不显示

java excel 加边框_java poi导出Excel合并单元格并设置边框

java poi导出Excel合并单元格并设置边框

java excel 合并单元格_java实现数据的Excel导出（合并单元格、样式等）

java excel 列合并单元格_java导出excel，某几列合并单元格。

导出excel合并单元格

java excel表格合并多列_java 通用的导出 excel 合并单元格格式(二)

java导出excel合并单元格

java excel 导出单元格合并_java导出excel合并单元格.docx