需求：从数据库中读取数据生成下面这个样式的表单
从上图中可以看出，
HSSFWorkbook实现完全可以
那么我们就用它来实现
// 创建一个新的工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个sheet页
HSSFSheet sheet1 = workbook.createSheet("sheet1");
String titel = "充电桩保修单";
创建合并单元格
CellRangeAddress tl = new CellRangeAddress(0 + index, 0 + index, 0, 7);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
将单元格设置如sheet中
sheet1.addMergedRegion(tl);
设置标题
Row rowTitle = sheet1.createRow(index);
Cell cellTitle = rowTitle.createCell(0);
cellTitle.setCellValue(titel); //设置标题
以上我们已经实现了简单的样例，依葫芦画照着画出需求图中的样子
看下面完整代码
完整代码
import entity.ChargingPieGuarantee;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
/**
* Created by lijinquan on 2016/10/14.
*/
public class TestExcel {
public static void main(String[] args) throws IOException {
List<ChargingPieGuarantee> cgLs = new ArrayList<ChargingPieGuarantee>();
ChargingPieGuarantee cg = new ChargingPieGuarantee();
cg.setOperator("运营商1"); // 运营商
cg.setOddNumbers("单号1");//单号
cg.setSite("站点1");//站点
cg.setNameofPile("桩名称1");//桩名称
cg.setWarrantyTime("保修时间1"); //保修时间
cg.setWarranties("保修人1"); //保修人
cg.setFault("故障来源1"); //故障来源
cg.setFaultDescription("故障描述1");//故障描述
cg.setProcessingResult("处理结果1");//处理结果
cg.setMajorPerson("主修人1"); //主修人
cg.setProcessingTime("2019-01-02"); //处理时间
cg.setInspector("检验人1"); //检验人
cg.setInspectionTime("2019-03-04"); //检验时间
ChargingPieGuarantee cg1 = new ChargingPieGuarantee();
cg1.setOperator("运营商2"); // 运营商
cg1.setOddNumbers("单号2");//单号
cg1.setSite("站点2");//站点
cg1.setNameofPile("桩名称2");//桩名称
cg1.setWarrantyTime("保修时间2"); //保修时间
cg1.setWarranties("保修人2"); //保修人
cg1.setFault("故障来源2"); //故障来源
cg1.setFaultDescription("故障描述2");//故障描述
cg1.setProcessingResult("处理结果2");//处理结果
cg1.setMajorPerson("主修人2"); //主修人
cg1.setProcessingTime("2019-02-03"); //处理时间
cg1.setInspector("检验人2"); //检验人
cg1.setInspectionTime("2019-05-06"); //检验时间
cgLs.add(cg);
cgLs.add(cg1);
creatChargingPieGuaranTee(cgLs);
}
private static void creatChargingPieGuaranTee(List<ChargingPieGuarantee> cgList) throws IOException {
// 创建一个新的工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个sheet页
HSSFSheet sheet1 = workbook.createSheet("sheet1");
String titel = "充电桩保修单";
// HSSFCellStyle style = workbook.createCellStyle();
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//
// Font fontTitle = workbook.createFont();
// fontTitle.setFontHeightInPoints((short) 18); //字体大小
// fontTitle.setColor(HSSFColor.BLACK.index); //字体颜色
// fontTitle.setFontName("宋体"); //字体
// fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体显示
if (cgList.size() < 1) {
System.out.println("无记录");
}
int index = 0;
for (ChargingPieGuarantee cg : cgList) {
//创建合并单元格 ---begin
CellRangeAddress tl = new CellRangeAddress(0 + index, 0 + index, 0, 7);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
CellRangeAddress region1 = new CellRangeAddress(index + 1, index + 1, 0, 5);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
CellRangeAddress region1_2 = new CellRangeAddress(index + 1, index + 1, 6, 7);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
CellRangeAddress region2 = new CellRangeAddress(index + 3, index + 3, 1, 7);// 起始行号，终止行号， 起始列号，终止列号
CellRangeAddress region3 = new CellRangeAddress(index + 4, index + 4, 1, 7);// 起始行号，终止行号， 起始列号，终止列号
CellRangeAddress region4 = new CellRangeAddress(index + 5, index + 5, 1, 7);// 起始行号，终止行号， 起始列号，终止列号
CellRangeAddress region6 = new CellRangeAddress(index + 6, index + 6, 1, 2);// 起始行号，终止行号， 起始列号，终止列号
CellRangeAddress region6_2 = new CellRangeAddress(index + 6, index + 6, 4, 7);// 起始行号，终止行号， 起始列号，终止列号
CellRangeAddress region7 = new CellRangeAddress(index + 7, index + 7, 1, 2);// 起始行号，终止行号， 起始列号，终止列号
CellRangeAddress region7_2 = new CellRangeAddress(index + 7, index + 7, 4, 7);// 起始行号，终止行号， 起始列号，终止列号
sheet1.addMergedRegion(tl);
sheet1.addMergedRegion(region1);
sheet1.addMergedRegion(region1_2);
sheet1.addMergedRegion(region2);
sheet1.addMergedRegion(region3);
sheet1.addMergedRegion(region4);
sheet1.addMergedRegion(region6);
sheet1.addMergedRegion(region6_2);
sheet1.addMergedRegion(region7);
sheet1.addMergedRegion(region7_2);
Row rowTitle = sheet1.createRow(index);
Cell cellTitle = rowTitle.createCell(0);
cellTitle.setCellValue(titel); //设置标题
HSSFRow row1 = sheet1.createRow(index + 1);
HSSFCell r1c0 = row1.createCell(0);
HSSFCell r1c1 = row1.createCell(6);
r1c0.setCellValue("运营商 : " + cg.getOperator());
r1c1.setCellValue("单号： " + cg.getOddNumbers());
HSSFRow row2 = sheet1.createRow(index + 2);
HSSFCell r2c0 = row2.createCell(0);
r2c0.setCellValue("站点");
HSSFCell r2c1 = row2.createCell(1);
r2c1.setCellValue(cg.getSite());
HSSFCell r2c2 = row2.createCell(2);
r2c2.setCellValue("桩名称");
HSSFCell r2c3 = row2.createCell(3);
r2c3.setCellValue(cg.getNameofPile());
HSSFCell r2c4 = row2.createCell(4);
r2c4.setCellValue("保修时间");
HSSFCell r2c5 = row2.createCell(5);
r2c5.setCellValue(cg.getWarrantyTime());
HSSFCell r2c6 = row2.createCell(6);
r2c6.setCellValue("报修人");
HSSFCell r2c7 = row2.createCell(7);
r2c7.setCellValue(cg.getWarranties());
HSSFRow row3 = sheet1.createRow(index + 3);
HSSFCell r3c0 = row3.createCell(0);
r3c0.setCellValue("故障来源");
HSSFCell r3c1 = row3.createCell(1);
r3c1.setCellValue(cg.getFault());
HSSFRow row4 = sheet1.createRow(index + 4);
HSSFCell r4c0 = row4.createCell(0);
r4c0.setCellValue("故障描述");
HSSFCell r4c1 = row4.createCell(1);
r4c1.setCellValue(cg.getFaultDescription());
//此处空一行 自行调整
HSSFRow row5 = sheet1.createRow(index + 5);
HSSFCell r5c0 = row5.createCell(0);
r5c0.setCellValue("处理结果");
HSSFCell r5c1 = row5.createCell(1);
r5c1.setCellValue(cg.getProcessingResult());
HSSFRow row6 = sheet1.createRow(index + 6);
HSSFCell r6c0 = row6.createCell(0);
r6c0.setCellValue("主修人");
HSSFCell r6c1 = row6.createCell(1);
r6c1.setCellValue(cg.getMajorPerson());
HSSFCell r6c2 = row6.createCell(3);
r6c2.setCellValue("处理时间");
HSSFCell r6c3 = row6.createCell(4);
r6c3.setCellValue(cg.getProcessingTime());
HSSFRow row7 = sheet1.createRow(index + 7);
HSSFCell r7c0 = row7.createCell(0);
r7c0.setCellValue("检验人");
HSSFCell r7c1 = row7.createCell(1);
r7c1.setCellValue(cg.getInspector());
HSSFCell r7c2 = row7.createCell(3);
r7c2.setCellValue("检验时间");
HSSFCell r7c3 = row7.createCell(4);
r7c3.setCellValue(cg.getInspectionTime());
index += 10;
}
if(index>10000){
// TODO
System.out.println("excel行数有限，行数用完后需要重新creat一个sheet");
}
FileOutputStream fileOutputStream = new FileOutputStream("E:\\test\\chargepio\\ChargingPieGuarantee.xlsx");
// 将workbook写入流中
workbook.write(fileOutputStream);
// 关流
fileOutputStream.close();
}
}
