首先，看一下期望达到的效果：
下面开始：
pom文件引入jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>代码：
package cn.service.impl.exceltest;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
/**
* Created with IntelliJ IDEA.
*
* @Auther: zp
* @Date: 2021/05/25/18:13
* @Description:
*/
public class MainOut {
public static void main(String args[]){
//模拟部分数据
List<WorkSheetDetail> detail = new ArrayList<WorkSheetDetail>();
List<WorkSheetDetail> onLineList = new ArrayList<WorkSheetDetail>();
WorkSheetDetail d1 =new WorkSheetDetail("浙江杭州超级无敌硬邦邦科技有限公司",23f,43,34,243f,54f,"34",1);
WorkSheetDetail d2 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34",1);
WorkSheetDetail d3 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34",1);
WorkSheetDetail d4 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34",1);
WorkSheetDetail d5 =new WorkSheetDetail("23",23f,43,34,243f,54f,"34",1);
detail.add(d1);
detail.add(d2);
detail.add(d3);
detail.add(d4);
detail.add(d5);
onLineList.add(d1);
onLineList.add(d2);
onLineList.add(d3);
ResParam resParam =new ResParam(1, 2, 3, 4, detail,onLineList);
try
{
FileOutputStream fout = new FileOutputStream("D:/excel/students.xls");
new cn.pinming.bidopening.bid.service.impl.exceltest.ExportExcel().getValue(resParam, fout);
fout.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
package cn.service.impl.exceltest;
/**
* Created with IntelliJ IDEA.
*
* @Auther: zp
* @Date: 2021/05/25/18:10
* @Description:
*/
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;
// 排行
private Integer rank;
public Integer getRank() {
return rank;
}
public void setRank(Integer rank) {
this.rank = rank;
}
public WorkSheetDetail(String workCtx, Float totalHumanDays, Integer gwnNum, Integer tmnNum, Float unitPrice,
Float unitAmount, String notes,Integer rank) {
super();
this.workCtx = workCtx;
this.totalHumanDays = totalHumanDays;
this.gwnNum = gwnNum;
this.tmnNum = tmnNum;
this.unitPrice = unitPrice;
this.unitAmount = unitAmount;
this.notes = notes;
this.rank = rank;
}
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 cn.service.impl.exceltest;
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.Arrays;
import java.util.List;
/**
* Created with IntelliJ IDEA.
*
* @Auther: zp
* @Date: 2021/05/25/18:10
* @Description:
*/
public class ExportExcel {
public void getValue(ResParam resParam, FileOutputStream fout) {
List<WorkSheetDetail> userList = resParam.getUserList();
List<WorkSheetDetail> onLineList = resParam.getOnLineList();
try {
//1.创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//1.1创建合并单元格对象
CellRangeAddress callRangeAddress1 = new CellRangeAddress(0, 0, 0, 8);//起始行,结束行,起始列,结束列
//项目名称与时间start
CellRangeAddress callRangeAddress20 = new CellRangeAddress(1, 1, 0, 2);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress21 = new CellRangeAddress(1, 1, 3, 4);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress22 = new CellRangeAddress(1, 1, 5, 7);//起始行,结束行,起始列,结束列
//项目名称与时间end
// 序号
CellRangeAddress callRangeAddress31 = new CellRangeAddress(2, 3, 0, 0);//起始行,结束行,起始列,结束列
// 投标单位
CellRangeAddress callRangeAddress32 = new CellRangeAddress(2, 3, 1, 1);//起始行,结束行,起始列,结束列
// 投标人承诺
CellRangeAddress callRangeAddress33 = new CellRangeAddress(2, 2, 2, 3);//起始行,结束行,起始列,结束列
// 资信标 没有两格报错
CellRangeAddress callRangeAddress34 = new CellRangeAddress(2, 3, 4, 4);//起始行,结束行,起始列,结束列
// 技术标
CellRangeAddress callRangeAddress35 = new CellRangeAddress(2, 3, 5, 5);//起始行,结束行,起始列,结束列
// 商务标
CellRangeAddress callRangeAddress36 = new CellRangeAddress(2, 3, 6, 6);//起始行,结束行,起始列,结束列
// 得分合计
CellRangeAddress callRangeAddress37 = new CellRangeAddress(2, 3, 7, 7);//起始行,结束行,起始列,结束列
// 最终排名
CellRangeAddress callRangeAddress38 = new CellRangeAddress(2, 3, 8, 8);//起始行,结束行,起始列,结束列
//预算造价 报价上限 评标标底价 D值
CellRangeAddress cellAddresses1 = new CellRangeAddress(userList.size() + 4, userList.size() + 4, 0, 1);//起始行,结束行,起始列,结束列
CellRangeAddress cellAddresses2 = new CellRangeAddress(userList.size() + 4, userList.size() + 4, 2, 3);//起始行,结束行,起始列,结束列
CellRangeAddress cellAddresses3 = new CellRangeAddress(userList.size() + 4, userList.size() + 4, 4, 6);//起始行,结束行,起始列,结束列
CellRangeAddress cellAddresses4 = new CellRangeAddress(userList.size() + 4, userList.size() + 4, 7, 8);//起始行,结束行,起始列,结束列
// 中标候选人名称 总报价（元） 工期（日历天） 中标排序 签字确认
// CellRangeAddress address1 = new CellRangeAddress(userList.size()+5,userList.size()+6,0,1);//起始行,结束行,起始列,结束列
// CellRangeAddress address2 = new CellRangeAddress(userList.size()+5,userList.size()+6,2,3);//起始行,结束行,起始列,结束列
// CellRangeAddress address3 = new CellRangeAddress(userList.size()+5,userList.size()+6,4,5);//起始行,结束行,起始列,结束列
// CellRangeAddress address4 = new CellRangeAddress(userList.size()+5,userList.size()+6,6,6);//起始行,结束行,起始列,结束列
// CellRangeAddress address5 = new CellRangeAddress(userList.size()+5,userList.size()+6,7,8);//起始行,结束行,起始列,结束列
//招标人代表 监督 代理
CellRangeAddress callRangeAddressPersion1 = new CellRangeAddress(userList.size() + 10, userList.size() + 10, 0, 2);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddressPersion2 = new CellRangeAddress(userList.size() + 10, userList.size() + 10, 3, 4);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddressPersion3 = new CellRangeAddress(userList.size() + 10, userList.size() + 10, 5, 7);//起始行,结束行,起始列,结束列
//样式配置start
// title
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("开标、评标、定标结果汇总表");
sheet.autoSizeColumn(1);
sheet.setColumnWidth(1, sheet.getColumnWidth(1) * 50 / 10);
//2.1加载合并单元格对象
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(callRangeAddress38);
sheet.addMergedRegion(callRangeAddressPersion1);
sheet.addMergedRegion(callRangeAddressPersion2);
sheet.addMergedRegion(callRangeAddressPersion3);
sheet.addMergedRegion(cellAddresses1);
sheet.addMergedRegion(cellAddresses2);
sheet.addMergedRegion(cellAddresses3);
sheet.addMergedRegion(cellAddresses4);
// sheet.addMergedRegion(address1);
// sheet.addMergedRegion(address2);
// sheet.addMergedRegion(address3);
// sheet.addMergedRegion(address4);
// sheet.addMergedRegion(address5);
//设置默认列宽
sheet.setDefaultColumnWidth(15);
//3.创建行
//3.1创建头标题行;并且设置头标题
HSSFRow rower = sheet.createRow(0);
HSSFCell celler = rower.createCell(0);
//加载单元格样式
celler.setCellStyle(erStyle);
celler.setCellValue("开标、评标、定标结果汇总表");
// 创建第二行
HSSFRow rowsan = sheet.createRow(1);
HSSFCell cellsan = rowsan.createCell(0);
HSSFCell cellsan1 = rowsan.createCell(3);
HSSFCell cellsan2 = rowsan.createCell(5);
//加载单元格样式
cellsan.setCellStyle(sanStyle);
cellsan.setCellValue("项目名称：xxxxxxxxx项目");
cellsan1.setCellStyle(sanStyle);
cellsan1.setCellValue("");
cellsan2.setCellStyle(sanStyle);
cellsan2.setCellValue("时间：2017年 10月 20日");
//3.2创建列标题;并且设置列标题
HSSFRow row2 = sheet.createRow(2);
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(3);
String[] titlefour = {"总报价（元）", "工期（日历天）"};
for (int i = 0; i < titlefour.length; i++) {
HSSFCell cell2 = rowfour.createCell(i + 2);
//加载单元格样式
cell2.setCellStyle(colStyle);
cell2.setCellValue(titlefour[i]);
}
// 创建预算造价
HSSFRow hssfRow = sheet.createRow(userList.size() + 4);
HSSFCell hssfCell = hssfRow.createCell(0);
HSSFCell hssfCell1 = hssfRow.createCell(2);
HSSFCell hssfCell2 = hssfRow.createCell(4);
HSSFCell hssfCell3 = hssfRow.createCell(7);
//加载单元格样式
hssfCell.setCellStyle(colStyle);
hssfCell.setCellValue("预算造价：" + resParam.getA());
hssfCell1.setCellStyle(colStyle);
hssfCell1.setCellValue("报价上限：" + resParam.getB());
hssfCell2.setCellStyle(colStyle);
hssfCell2.setCellValue("评标标底价：" + resParam.getC());
hssfCell3.setCellStyle(colStyle);
hssfCell3.setCellValue("D值：" + +resParam.getD());
// 创建中标候选人
HSSFRow sheetRow = sheet.createRow(userList.size() + 5);
HSSFCell sheetRowCell = sheetRow.createCell(0);
HSSFCell sheetRowCell1 = sheetRow.createCell(1);
HSSFCell sheetRowCell2 = sheetRow.createCell(2);
HSSFCell sheetRowCell3 = sheetRow.createCell(3);
HSSFCell sheetRowCell4 = sheetRow.createCell(4);
//加载单元格样式
sheetRowCell.setCellStyle(colStyle);
sheetRowCell.setCellValue("中标候选人名称");
sheetRowCell1.setCellStyle(colStyle);
sheetRowCell1.setCellValue("总报价（元）");
sheetRowCell2.setCellStyle(colStyle);
sheetRowCell2.setCellValue("工期（日历天）");
sheetRowCell3.setCellStyle(colStyle);
sheetRowCell3.setCellValue("中标排序");
sheetRowCell4.setCellStyle(colStyle);
sheetRowCell4.setCellValue("签字确认");
// 招标人代表
HSSFRow rowpersion = sheet.createRow(userList.size() + 9);
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("代理：");
//4.操作单元格;将用户列表写入excel
if (userList != null) {
int i = 1;
for (int j = 0; j < userList.size(); j++) {
//创建数据行,前面有两行,头标题行和列标题行
HSSFRow row3 = sheet.createRow(j + 4);
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).getUnitPrice());
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).getUnitAmount());
HSSFCell cell8 = row3.createCell(8);
cell8.setCellStyle(cellStyle);
cell8.setCellValue(userList.get(j).getRank());
}
}
//4.操作单元格;将用户列表写入excel
if (onLineList != null) {
int i = 1;
for (int j = 0; j < onLineList.size(); j++) {
//创建数据行,前面有两行,头标题行和列标题行
HSSFRow row3 = sheet.createRow(j + 6 + userList.size());
HSSFCell cell0 = row3.createCell(0);
cell0.setCellStyle(cellStyle);
cell0.setCellValue(i++);
HSSFCell cell1 = row3.createCell(1);
cell1.setCellStyle(cellStyle);
cell1.setCellValue(onLineList.get(j).getWorkCtx());
HSSFCell cell2 = row3.createCell(2);
cell2.setCellStyle(cellStyle);
cell2.setCellValue(onLineList.get(j).getTotalHumanDays());
HSSFCell cell3 = row3.createCell(3);
cell3.setCellStyle(cellStyle);
cell3.setCellValue(onLineList.get(j).getGwnNum());
HSSFCell cell4 = row3.createCell(4);
cell4.setCellStyle(cellStyle);
cell4.setCellValue(onLineList.get(j).getTmnNum());
HSSFCell cell5 = row3.createCell(5);
cell5.setCellStyle(cellStyle);
cell5.setCellValue(onLineList.get(j).getUnitPrice());
HSSFCell cell6 = row3.createCell(6);
cell6.setCellStyle(cellStyle);
cell6.setCellValue(onLineList.get(j).getUnitAmount());
HSSFCell cell7 = row3.createCell(7);
cell7.setCellStyle(cellStyle);
cell7.setCellValue(onLineList.get(j).getUnitAmount());
HSSFCell cell8 = row3.createCell(8);
cell8.setCellStyle(cellStyle);
cell8.setCellValue(onLineList.get(j).getRank());
}
}
//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);//垂直居中
//创建字体
HSSFFont font = workbook.createFont();
//是否加粗字体
if (flag) {
font.setBold(true);
}
font.setFontHeightInPoints(fontsize);
//加载字体
style.setFont(font);
return style;
}
}执行MainOut 的Main 方法即可得到和上方期望样式一样的表格。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
