First, take a look at the desired results:
Here we go:
Pom files introduce JAR packages
<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 >Copy the codeCode:
package cn.bid.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>(); D1 =new WorkSheetDetail(" zhejiang Hangzhou Super hard Bang Technology Co., LTD. ",23f,43,34, 243F, 54F,"34",1); WorkSheetDetail d2 = new WorkSheetDetail (43,34,243 "23", 23 f, f, f, 54 "34", 1); WorkSheetDetail d3 = new WorkSheetDetail (43,34,243 "23", 23 f, f, f, 54 "34", 1); WorkSheetDetail d4 = new WorkSheetDetail (43,34,243 "23", 23 f, f, f, 54 "34", 1); WorkSheetDetail d5 = new WorkSheetDetail (43,34,243 "23", 23 f, f, f, 54 "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(); }}}Copy the codepackage 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 to 4 hours is half a day, 4 to 8 hours is a day; 120 private Integer gwnNum; // The number of artisans working 1 to 4 hours is half a day, and 4 to 8 hours is a day; 160 private Integer tmnNum; // private Float unitPrice; GwnNum *120+tmnNum+160 private Float unitAmount; // Remarks private String notes; // Ranking 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; }}Copy the codepackage 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;
}
}
Copy the codeExecute MainOut’s Main method to get the table as expected above.