1.创建表格（根据自己需求改变字段名和格式）
public Result exportExcel21() {
OutputStream out = null;
try {
this.sheet = wb.createSheet();
this.createFormat();
out = new FileOutputStream(getAbsoluteFile(fullfolderName));
wb.write(out);
return Result.succeed();
} catch (
Exception e) {
log.error("导出Excel异常{}", e.getMessage());
throw new CustomException("导出Excel失败，请联系网站管理员！");
} finally {
}
}
// private final int rowHeightIdCard = 450;
//固定列宽
private final double[] colWidths = {5.63, 21.25, 16.38, 10.25, 9.13, 16.13, 11.5, 27.13};
/**
* 设置列宽
*
* @param
* @return
*/
private void setColumnWidth() {
//比例 本来应该是256但不知道为何存在误差，此处根据误差比例进行调整
final int scale = 296;
for (int i = 0; i < colWidths.length; i++) {
sheet.setColumnWidth(i, (int) (scale * colWidths[i]));
}
}
/**
* 对单元格进行合并同时进行边框处理（避免合并单元格后部分单元格没有边框）
*
* @param
* @return
*/
private void setMergedBorder(CellStyle style, Row rows, int col1, int col2) {
for (int i = col1 + 1; i <= col2; i++) {
Cell hssfCell = rows.createCell(i);
hssfCell.setCellStyle(style);
hssfCell.setCellValue("");
}
}
/**
* 创建行元素.
*
* @param style 样式
* @param height 行高
* @param value 行显示的内容
* @param row1 起始行
* @param row2 结束行
* @param col1 起始列
* @param col2 结束列
*/
private void createRow(CellStyle style, int height, String value, int row1, int row2, int col1, int col2) {
sheet.addMergedRegion(new CellRangeAddress(row1, row2, col1, col2)); //设置从第row1行合并到第row2行，第col1列合并到col2列
Row rows = sheet.createRow(row1);//设置第几行
setMergedBorder(style, rows, col1, col2); //进行合并后边框处理
rows.setHeight((short) height); //设置行高
Cell cell = rows.createCell(col1); //设置内容开始的列
cell.setCellStyle(style); //设置样式
cell.setCellValue(value); //设置该行的值
}
/**
* 创建样式
*
* @param fontSize 字体大小
* @param align 水平位置 左右居中2 居右3 默认居左 垂直均为居中
* @param bold 是否加粗
* @return
*/
private CellStyle getStyle(int fontSize, int align, boolean bold, boolean border) {
Font font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) fontSize);// 字体大小
font.setBold(bold);
CellStyle style = wb.createCellStyle();
style.setFont(font); //设置字体
style.setWrapText(true);
switch (align) { // 居左1 居中2 居右3 默认居左
// case 1:style.setAlignment(HorizontalAlignment.LEFT);break;
case 2:
style.setAlignment(HorizontalAlignment.CENTER);
break;
case 3:
style.setAlignment(HorizontalAlignment.RIGHT);
break;
}
style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中1
if (border) {
style.setBorderRight(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setLocked(true);
}
return style;
}
/**
* 用设置表格格式生成固定表格，思路是一行一行进行建表
* 注意：
* 对于同一行中多个信息：&表示信息填写在同一格 /表示信息填写在不同格
*
* @param
* @param
*/
public void createFormat() throws IOException {
//测试DTO
CreatDTO creatDTO = new CreatDTO();
//设置列宽
setColumnWidth();
//表格大标题常用格式
int bigTitleFontSIze = 18;
CellStyle styleBigTitleCommon = getStyle(bigTitleFontSIze, 2, true, true);
//表格小标题常用格式
int smallTitleFontSize = 14;
CellStyle styleSmallTitleCommon = getStyle(smallTitleFontSize, 2, true, true);
//表格固定方框内常用格式
//字体设置
int textFontSize = 14;
CellStyle styleFixedCommon = getStyle(textFontSize, 2, true, true);
//表格填写方框内常用格式
CellStyle styleWriteCommon = getStyle(textFontSize, 2, true, true);
//当前行数（每次完成一行构建就++）
//开始行
int currentRow = 0;
/**
* 第一行：标题
*/
//开始列
int startColumn = 1;
//固定行高 分别表示标题行高，正文行高，“注~”栏行高
int titleRowHeight = 885;
createRow(styleBigTitleCommon, titleRowHeight, "Excel导出测试表", currentRow, currentRow, startColumn, startColumn + 6);
currentRow++;
/**
* 第二行：自然人信息
*/
int rowHeight = 815;
createRow(styleSmallTitleCommon, rowHeight, "自然人信息", currentRow, currentRow, startColumn, startColumn + 6);
currentRow++;
/**
* 第三行：名字/联系电话
*/
Row row3 = sheet.createRow(currentRow);
row3.setHeight((short) rowHeight);
//姓名
Cell cellName = row3.createCell(startColumn);
cellName.setCellStyle(styleFixedCommon);
cellName.setCellValue("姓名");
//姓名填写栏
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 1, startColumn + 3));
setMergedBorder(styleWriteCommon, row3, startColumn + 1, startColumn + 3);
Cell cellOfName = row3.createCell(startColumn + 1);
cellOfName.setCellStyle(styleWriteCommon);
cellOfName.setCellValue(creatDTO.getName());
//联系电话
row3.setHeight((short) rowHeight);
Cell cellMobileOFDrawer = row3.createCell(startColumn + 4);
cellMobileOFDrawer.setCellStyle(styleFixedCommon);
cellMobileOFDrawer.setCellValue("联系电话");
//联系电话填写栏
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 5, startColumn + 6));
setMergedBorder(styleWriteCommon, row3, startColumn + 5, startColumn + 6);
Cell cellOfMobileOfDrawer = row3.createCell(startColumn + 5);
cellOfMobileOfDrawer.setCellStyle(styleWriteCommon);
cellOfMobileOfDrawer.setCellValue(creatDTO.getMobileOfDrawer());
currentRow++;
/**
* 第四行：身份证号
*/
Row row4 = sheet.createRow(3);
row4.setHeight((short) rowHeight);
//身份证号
Cell cellIdNo = row4.createCell(startColumn);
cellIdNo.setCellStyle(styleFixedCommon);
cellIdNo.setCellValue("身份证号码");
//身份证号填写栏
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 1, startColumn + 6));
setMergedBorder(styleWriteCommon, row4, startColumn + 1, startColumn + 6);
Cell cellOfIdNo = row4.createCell(startColumn + 1);
cellOfIdNo.setCellStyle(styleWriteCommon);
cellOfIdNo.setCellValue(creatDTO.getIdNo());
currentRow++;
/**
* 第五行：购买方信息
*/
createRow(styleSmallTitleCommon, rowHeight, "购买方信息", currentRow, currentRow, startColumn, startColumn + 6);
currentRow++;
/**
* 第六行：公司名称/纳税人识别号
*/
Row row6 = sheet.createRow(currentRow);
row6.setHeight((short) rowHeight);
//公司名称
Cell cellCompanyName = row6.createCell(startColumn);
cellCompanyName.setCellStyle(styleFixedCommon);
cellCompanyName.setCellValue("公司名称");
//公司名称填写栏
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 1, startColumn + 3));
setMergedBorder(styleWriteCommon, row6, startColumn + 1, startColumn + 3);
Cell cellOfCompanyName = row6.createCell(startColumn + 1);
cellOfCompanyName.setCellStyle(styleWriteCommon);
cellOfCompanyName.setCellValue(creatDTO.getCompanyName());
//纳税人识别号
Cell cellIdentificationNumber = row6.createCell(startColumn + 4);
cellIdentificationNumber.setCellStyle(styleFixedCommon);
cellIdentificationNumber.setCellValue("纳税人识别号");
//纳税人识别号填写栏
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 5, startColumn + 6));
setMergedBorder(styleWriteCommon, row6, startColumn + 5, startColumn + 6);
Cell cellOfIdentificationNumber = row6.createCell(startColumn + 5);
cellOfIdentificationNumber.setCellStyle(styleWriteCommon);
cellOfIdentificationNumber.setCellValue(creatDTO.getIdentificationNumber());
currentRow++;
/**
* 第七行：地址&联系电话
*/
Row row7 = sheet.createRow(currentRow);
row7.setHeight((short) rowHeight);
//地址&联系电话
Cell cellAddressAndMobileOfHead = row7.createCell(startColumn);
cellAddressAndMobileOfHead.setCellStyle(styleFixedCommon);
cellAddressAndMobileOfHead.setCellValue("地址&联系电话");
//地址&联系电话填写栏
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 1, startColumn + 6));
setMergedBorder(styleWriteCommon, row7, startColumn + 1, startColumn + 6);
Cell cellOfAddressAndMobileOfHead = row7.createCell(startColumn + 1);
cellOfAddressAndMobileOfHead.setCellStyle(styleWriteCommon);
//间隔符号设置
String interval = " ";
cellOfAddressAndMobileOfHead.setCellValue(creatDTO.getAddress() + interval + creatDTO.getMobileOfHead());
currentRow++;
/**
* 第八行：开户行&银行账号
*/
Row row8 = sheet.createRow(currentRow);
row8.setHeight((short) rowHeight);
//开户行&银行账号
Cell cellBankNameAndBankAccount = row8.createCell(startColumn);
cellBankNameAndBankAccount.setCellStyle(styleFixedCommon);
cellBankNameAndBankAccount.setCellValue("开户行&银行账号");
//开户行&银行账号填写栏
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 1, startColumn + 6));
setMergedBorder(styleWriteCommon, row8, startColumn + 1, startColumn + 6);
Cell cellOfBankNameAndBankAccount = row8.createCell(startColumn + 1);
cellOfBankNameAndBankAccount.setCellStyle(styleWriteCommon);
cellOfBankNameAndBankAccount.setCellValue(creatDTO.getBankName() + interval + creatDTO.getBankAccount());
currentRow++;
/**
* 第九行+第十行 ~ 第N行+第N+1行：开票内容相关
* 注意：
* 此处命名统一以第9/10行为规范。
*/
//开票内容包含几行
for (CreatDTO.OrderItemPO itemPO : creatDTO.getOrderItems()) {
Row row9 = sheet.createRow(currentRow);
row9.setHeight((short) rowHeight);
//开票内容
Cell cellInvoiceContent = row9.createCell(startColumn);
cellInvoiceContent.setCellStyle(styleFixedCommon);
cellInvoiceContent.setCellValue("开票内容");
//开票内容填写栏
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 1, startColumn + 6));
setMergedBorder(styleWriteCommon, row9, startColumn + 1, startColumn + 6);
Cell cellOfInvoiceContent = row9.createCell(startColumn + 1);
cellOfInvoiceContent.setCellStyle(styleWriteCommon);
cellOfInvoiceContent.setCellValue(itemPO.getRemark());
currentRow++;
Row row10 = sheet.createRow(currentRow);
row10.setHeight((short) rowHeight);
//规格型号
Cell cellSpecs = row10.createCell(startColumn);
cellSpecs.setCellStyle(styleFixedCommon);
cellSpecs.setCellValue("规格型号：" + itemPO.getSpecs());
//计量单位
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 1, startColumn + 2));
setMergedBorder(styleWriteCommon, row10, startColumn + 1, startColumn + 2);
Cell cellUnit = row10.createCell(startColumn + 1);
cellUnit.setCellStyle(styleWriteCommon);
cellUnit.setCellValue("计量单位：" + itemPO.getUnit());
//数量
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 3, startColumn + 4));
setMergedBorder(styleWriteCommon, row10, startColumn + 3, startColumn + 4);
Cell cellCount = row10.createCell(startColumn + 3);
cellCount.setCellStyle(styleWriteCommon);
cellCount.setCellValue("数量：" + itemPO.getCount());
//开票金额
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 5, startColumn + 6));
setMergedBorder(styleWriteCommon, row10, startColumn + 5, startColumn + 6);
Cell cellInvoiceAmt = row10.createCell(startColumn + 5);
cellInvoiceAmt.setCellStyle(styleWriteCommon);
cellInvoiceAmt.setCellValue("开票金额：" + itemPO.getInvoiceAmt());
currentRow++;
}
/**
* 第N+2行：收款人/复核人
*/
Row row11 = sheet.createRow(currentRow);
row11.setHeight((short) rowHeight);
//收款人
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn, startColumn + 3));
setMergedBorder(styleWriteCommon, row11, startColumn, startColumn + 3);
Cell cellPayee = row11.createCell(startColumn);
cellPayee.setCellStyle(styleWriteCommon);
cellPayee.setCellValue("收款人：" + creatDTO.getName());
//复核人
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 4, startColumn + 6));
setMergedBorder(styleWriteCommon, row11, startColumn + 4, startColumn + +6);
Cell cellReviewer = row11.createCell(startColumn + 4);
cellReviewer.setCellStyle(styleWriteCommon);
cellReviewer.setCellValue("复核人：" + creatDTO.getName());
currentRow++;
/**
* 第N+3行：备注栏
*/
Row row12 = sheet.createRow(currentRow);
row12.setHeight((short) rowHeight);
//备注栏
Cell cellRemarks = row12.createCell(startColumn);
cellRemarks.setCellStyle(styleFixedCommon);
cellRemarks.setCellValue("备注栏");
//备注栏填写栏
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 1, startColumn + 6));
setMergedBorder(styleWriteCommon, row12, startColumn + 1, startColumn + 6);
Cell cellOfRemarks = row12.createCell(startColumn + 1);
cellOfRemarks.setCellStyle(styleWriteCommon);
cellOfRemarks.setCellValue(creatDTO.getRemark());
currentRow++;
/**
* 第N+4行：邮寄信息
*/
createRow(styleSmallTitleCommon, rowHeight, "邮寄信息", currentRow, currentRow, startColumn, startColumn + 6);
currentRow++;
/**
* 第N+5行：收件地址&联系人&电话
*/
Row row14 = sheet.createRow(currentRow);
row14.setHeight((short) rowHeight);
//收件地址&联系人&电话
Cell cellReceivedAddressContactsMobileOfContacts = row14.createCell(startColumn);
cellReceivedAddressContactsMobileOfContacts.setCellStyle(styleFixedCommon);
cellReceivedAddressContactsMobileOfContacts.setCellValue("收件地址&联系人&电话");
//收件地址&联系人&电话填写栏
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 1, startColumn + 6));
setMergedBorder(styleWriteCommon, row14, startColumn + 1, startColumn + 6);
Cell cellOfReceivedAddressContactsMobileOfContacts = row14.createCell(startColumn + 1);
cellOfReceivedAddressContactsMobileOfContacts.setCellStyle(styleWriteCommon);
cellOfReceivedAddressContactsMobileOfContacts.setCellValue(creatDTO.getReceivedAddress() + interval + creatDTO.getContacts() + interval + creatDTO.getMobileOfContacts());
currentRow++;
/**
* 第N+6行：发件联系人&电话
*/
Row row15 = sheet.createRow(currentRow);
row15.setHeight((short) rowHeight);
//发件联系人&电话
Cell cellSendContactsMobileSendContacts = row15.createCell(startColumn);
cellSendContactsMobileSendContacts.setCellStyle(styleFixedCommon);
cellSendContactsMobileSendContacts.setCellValue("发件联系人&电话");
//发件联系人&电话填写栏
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn + 1, startColumn + 6));
setMergedBorder(styleWriteCommon, row15, startColumn + 1, startColumn + 6);
Cell cellOfSendContactsMobileSendContacts = row15.createCell(startColumn + 1);
cellOfSendContactsMobileSendContacts.setCellStyle(styleWriteCommon);
cellOfSendContactsMobileSendContacts.setCellValue(creatDTO.getSendContacts() + interval + creatDTO.getMobileSendContacts());
currentRow++;
/**
* 第N+7行：注~
*/
Row row16 = sheet.createRow(currentRow);
int rowHeightMessage = 270;
row16.setHeight((short) rowHeightMessage);
CellStyle styleMessage = getStyle(11, 1, true, false);
sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, startColumn, startColumn + 3));
Cell cellMessage = row16.createCell(startColumn);
cellMessage.setCellStyle(styleMessage);
cellMessage.setCellValue("注：以上除备注栏和发件联系人外均为必填项");
currentRow++;
// /**
// * 隔一行
// */
// sheet.createRow(currentRow).setHeight((short) rowHeightMessage);
// currentRow++;
// /**
// * 身份证图片栏
// */
// for (int i = 0; i < 10; i++) {
// sheet.createRow(currentRow).setHeight((short) 450);
// currentRow++;
// }
// sheet.addMergedRegion(new CellRangeAddress(currentRow - 10, currentRow - 1, startColumn, startColumn + 3));
// sheet.addMergedRegion(new CellRangeAddress(currentRow - 10, currentRow - 1, startColumn + 4, startColumn + 6));
//
// ByteArrayOutputStream byteArrayOutFront = new ByteArrayOutputStream();
// BufferedImage bufferImgFront = ImageIO.read(new File("C:\\Users\\Yuri\\Desktop\\front.png"));
// ImageIO.write(bufferImgFront, "jpg", byteArrayOutFront);
//
// ByteArrayOutputStream byteArrayOutBack = new ByteArrayOutputStream();
// BufferedImage bufferImgBack = ImageIO.read(new File("C:\\Users\\Yuri\\Desktop\\back.jpg"));
// ImageIO.write(bufferImgBack, "jpg", byteArrayOutBack);
//
// Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
// //anchor主要用于设置图片的属性
// HSSFClientAnchor anchorFront = new HSSFClientAnchor(0, 0, 1000, 255, (short) startColumn, currentRow - 10, (short) (startColumn + 3), currentRow - 1);
// HSSFClientAnchor anchorBack = new HSSFClientAnchor(0, 0, 1000, 255, (short) (startColumn + 4), currentRow - 10, (short) (startColumn + 6), currentRow - 1);
// anchorFront.setAnchorType(ClientAnchor.AnchorType.byId(3));
// anchorBack.setAnchorType(ClientAnchor.AnchorType.byId(3));
// //插入图片
// drawingPatriarch.createPicture(anchorFront, wb.addPicture(byteArrayOutFront.toByteArray(),
// HSSFWorkbook.PICTURE_TYPE_EMF));
// drawingPatriarch.createPicture(anchorBack, wb.addPicture(byteArrayOutBack.toByteArray(),
// HSSFWorkbook.PICTURE_TYPE_EMF));
}2.填充实体类，可以不填，填写人手写填入
@Data
public class CreatDTO {
@ApiModelProperty(value = "开票人姓名")
private String name;
@ApiModelProperty(value = "开票人电话")
private String mobileOfDrawer;
@ApiModelProperty(value = "开票人身份证")
private String idNo;
@ApiModelProperty(value = "收货地址")
private String address;
@ApiModelProperty(value = "公司名称")
private String companyName;
@ApiModelProperty(value = "纳税人识别号")
private String identificationNumber;
@ApiModelProperty(value = "公司电话")
private String mobileOfHead;
@ApiModelProperty(value = "开户行名称")
private String bankName;
@ApiModelProperty(value = "公司账户")
private String bankAccount;
@ApiModelProperty(value = "开票内容")
private List<OrderItemPO> orderItems;
@ApiModelProperty(value = "联系人")
private String contacts;
@ApiModelProperty(value = "联系人电话")
private String mobileOfContacts;
@ApiModelProperty(value = "发件人")
private String sendContacts;
@ApiModelProperty(value = "发件人电话")
private String mobileSendContacts;
@ApiModelProperty(value = "项目名称")
private String projectName;
@ApiModelProperty(value = "项目地址")
private String projectAddress;
@ApiModelProperty(value = "收货地址")
private String receivedAddress;
@ApiModelProperty(value = "备注")
private String remark;
public CreatDTO() {
setName("明天");
setMobileOfDrawer("1529817555");
setIdNo("51033219990505152436");
setCompanyName("第一家");
setIdentificationNumber("12345678910");
setAddress("相城一号");
setMobileOfHead("0087541");
setBankName("中国银行");
setBankAccount("01511544");
setOrderItems(new ArrayList<>());
setProjectName("企业注册");
setProjectAddress("相城一号");
setReceivedAddress("相城");
setContacts("今天");
setMobileOfContacts("0706");
setSendContacts("今天");
setMobileSendContacts("9913");
setRemark("没有什么好备注就随便写写吧");
OrderItemPO itemPO = new OrderItemPO();
itemPO.setRemark("通知");
itemPO.setSpecs("SR");
itemPO.setUnit("平方度");
itemPO.setCount(BigDecimal.valueOf(9000));
itemPO.setInvoiceAmt(BigDecimal.valueOf(6554848));
getOrderItems().add(itemPO);
OrderItemPO itemPO2 = new OrderItemPO();
itemPO2.setRemark("是的");
itemPO2.setSpecs("SSS");
itemPO2.setUnit("公里");
itemPO2.setCount(BigDecimal.valueOf(4));
itemPO2.setInvoiceAmt(BigDecimal.valueOf(984545214));
getOrderItems().add(itemPO2);
}
@Data
public static class OrderItemPO {
@ApiModelProperty(value = "交易号")
private Long orderId;
@ApiModelProperty(value = "开票内容")
private String remark;
@ApiModelProperty(value = "规格")
private String specs;
@ApiModelProperty(value = "计量单位")
private String unit;
@ApiModelProperty(value = "数量")
private BigDecimal count;
@ApiModelProperty(value = "单价")
private BigDecimal unitPrice;
@ApiModelProperty(value = "开票金额")
private BigDecimal invoiceAmt;
}
}模板excel
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
