Java操作poi设置指定样式导出excel
结果如下
1、设置指定单元格样式
用到了以下poi包
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* 创建单元格样式
*
* @param workbook 工作簿对象，用于创建样式和字体
* @param fontSize 字体大小
* @param alignment 水平对齐方式
* @return CellStyle 设置好的单元格样式对象
*/
private static CellStyle creatCellStyle(Workbook workbook, int fontSize, HorizontalAlignment alignment) {
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(alignment);//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
//定义字体
Font font = workbook.createFont();
font.setFontName("宋体");//设置字体
font.setBold(true);//加粗
font.setFontHeightInPoints((short) fontSize);//设置字体大小
cellStyle.setFont(font);//设置单元格字体
cellStyle.setWrapText(true);//自动换行
//cellStyleCenter.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//设置背景色
//cellStyleCenter.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置填充模式
return cellStyle;
}
2、向指定单元格中插入二维码
用到了hutool工具类下QrCodeUtil
/**
* 将二维码插入到Excel工作表中
*
* @param workbook 当前的工作簿对象
* @param sheet 当前的工作表对象
* @param content 二维码的内容
* @param rowNum 二维码图片插入的起始行号
* @param colNum 二维码图片插入的起始列号
* @throws Exception 如果生成二维码或插入图片过程中发生错误，则抛出异常
*/
private static void insertQrCode(Workbook workbook, Sheet sheet, String content, int rowNum, int colNum) throws Exception {
byte[] imageData = QrCodeUtil.generatePng(content, 100, 100);//生成二维码图片
// 添加图片到workbook
int pictureIdx = workbook.addPicture(imageData, Workbook.PICTURE_TYPE_PNG);
// 创建一个绘图对象
Drawing<?> drawing = sheet.createDrawingPatriarch();
// 创建一个客户端锚点，指定图片的位置
ClientAnchor anchor = workbook.getCreationHelper().createClientAnchor();
anchor.setRow1(rowNum); // 图片开始插入的行
anchor.setCol1(colNum); // 图片开始插入的列
//anchor.setCol2(9); // 图片结束的列（可选）
//anchor.setRow2(0); // 图片结束的行（可选）
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
// 插入图片
Picture picture = drawing.createPicture(anchor, pictureIdx);
picture.resize(); // 调整图片大小以适应单元格（可选）
}
3、循环创建多个sheet页，写入数据
/**
* 07 版本 xlsx 文件写
*
* @param url
* @param list
* @return
* @throws Exception
*/
public static boolean writeExcelByPoiXHSSF(List<ProduceWorkOrder> list, String url) throws Exception {
// 处理文件后缀名 即 路径
String fileName = "测试.xlsx";
String path = "E:\\" + fileName;
// 创建表格
Workbook workbook = new XSSFWorkbook();
for (ProduceWorkOrder produceWorkOrder : list) {
Sheet sheet = workbook.createSheet(produceWorkOrder.getWorkOrderNo());// 创建工作表
Row row1 = sheet.createRow(0);// 创建行
// 设置列宽
sheet.setColumnWidth(0, 10 * 256);
sheet.setColumnWidth(1, 60 * 256);
sheet.setColumnWidth(2, 10 * 256);
sheet.setColumnWidth(3, 10 * 256);
sheet.setColumnWidth(4, 10 * 256);
sheet.setColumnWidth(5, 10 * 256);
sheet.setColumnWidth(6, 10 * 256);
sheet.setColumnWidth(7, 10 * 256);
sheet.setColumnWidth(8, 10 * 256);
sheet.setColumnWidth(9, 20 * 256);
row1.createCell(0).setCellValue(produceWorkOrder.getWorkOrderName());// 设置单元格内容
row1.setHeight((short) 1600);//设置行高 即80磅
// 设置行单元格样式
CellStyle cellStyleCenter = creatCellStyle(workbook, 14, HorizontalAlignment.CENTER);
for (int i = 0; i <= 9; i++) {
Cell cell = row1.getCell(i);
if (cell != null) {
cell.setCellStyle(cellStyleCenter);
}
}
// 合并单元格本行其他列的内容
// 创建一个合并区域，从第0列到最后一列（例如第9列）进行合并
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
// 添加二维码
insertQrCode(workbook, sheet, url + produceWorkOrder.getId(),0,9);
Row row2 = sheet.createRow(1);
row2.setHeight((short) 800);//设置行高
row2.createCell(0).setCellValue("项目名称");
row2.createCell(1).setCellValue(produceWorkOrder.getBuildingName());
row2.createCell(2).setCellValue("班组");
row2.createCell(3).setCellValue(produceWorkOrder.getProduceTeam());
row2.createCell(4).setCellValue("总包数");
row2.createCell(5).setCellValue("null");
row2.createCell(6).setCellValue("制单");
row2.createCell(7).setCellValue(produceWorkOrder.getCreateName());
row2.createCell(8).setCellValue("制单日期");
row2.createCell(9).setCellValue(produceWorkOrder.getCreateTime().format(DateTimeFormatter.ofPattern("yyyy年MM月dd日")));
CellStyle cellStyleLeft = creatCellStyle(workbook, 11, HorizontalAlignment.LEFT);
for (int i = 0; i <= 9; i++) {
Cell cell = row2.getCell(i);
if (cell != null) {
cell.setCellStyle(cellStyleLeft);
}
}
Row row3 = sheet.createRow(2);
row3.setHeight((short) 400);//设置行高
row3.createCell(0).setCellValue("序号");
row3.createCell(1).setCellValue("模板编号");
row3.createCell(2).setCellValue("规格尺寸");
row3.createCell(3);
row3.createCell(4);
row3.createCell(5);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 5));
row3.createCell(6).setCellValue("数量");
row3.createCell(7).setCellValue("面积m²");
row3.createCell(8).setCellValue("添加新版");
row3.createCell(9).setCellValue("备注");
CellStyle cellStyle = creatCellStyle(workbook, 11, HorizontalAlignment.CENTER);
for (int i = 0; i <= 9; i++) {
Cell cell = row3.getCell(i);
if (cell != null) {
cell.setCellStyle(cellStyle);
}
}
Row row4 = sheet.createRow(3);
row4.setHeight((short) 400);//设置行高
row4.createCell(2).setCellValue("板宽1");
row4.createCell(3).setCellValue("板宽2");
row4.createCell(4).setCellValue("板高1");
row4.createCell(5).setCellValue("板高2");
for (int i = 0; i <= 9; i++) {
Cell cell = row4.getCell(i);
if (cell == null) {
cell = row4.createCell(i);
}
cell.setCellStyle(cellStyle);
}
sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));
sheet.addMergedRegion(new CellRangeAddress(2, 3, 6, 6));
sheet.addMergedRegion(new CellRangeAddress(2, 3, 7, 7));
sheet.addMergedRegion(new CellRangeAddress(2, 3, 8, 8));
sheet.addMergedRegion(new CellRangeAddress(2, 3, 9, 9));
int collLen = produceWorkOrder.getDetailsList().size();
BigDecimal totalQuantity = new BigDecimal(0);
BigDecimal totalAcreage = new BigDecimal(0);
for (int i = 4; i < 4 + collLen; i++) {
Row temp = sheet.createRow(i);
temp.createCell(0).setCellValue(i - 3);
ProduceWorkOrderDetails detail = produceWorkOrder.getDetailsList().get(i - 4);
temp.createCell(1).setCellValue(detail.getTemplateNo());
temp.createCell(2).setCellValue(detail.getWidthA().toString());
temp.createCell(3).setCellValue(detail.getWidthB().toString());
temp.createCell(4).setCellValue(detail.getLengthA().toString());
temp.createCell(5).setCellValue(detail.getLengthB().toString());
temp.createCell(6).setCellValue(detail.getQuantity().toString());
temp.createCell(7).setCellValue(detail.getTempAcreage().toString());
temp.createCell(9).setCellValue(detail.getTempRemark());
temp.setHeight((short) 400);//设置行高
for (int w = 0; w <= 9; w++) {
Cell cell = temp.getCell(w);
if (cell == null) {
cell = temp.createCell(w);
}
cell.setCellStyle(cellStyle);
}
totalQuantity = totalQuantity.add(detail.getQuantity());
totalAcreage = totalAcreage.add(detail.getTempAcreage());
}
Row totalRow = sheet.createRow(4 + collLen);
totalRow.setHeight((short) 400);//设置行高
totalRow.createCell(0).setCellValue("合计");
totalRow.createCell(6).setCellValue(totalQuantity.toString());
totalRow.createCell(7).setCellValue(totalAcreage.toString());
for (int w = 0; w <= 9; w++) {
Cell cell = totalRow.getCell(w);
if (cell == null) {
cell = totalRow.createCell(w);
}
cell.setCellStyle(cellStyle);
}
Row lastTowRow = sheet.createRow(5 + collLen);
lastTowRow.setHeight((short) 400);//设置行高
lastTowRow.createCell(0).setCellValue("配料：" + "");
lastTowRow.createCell(3).setCellValue("改制：" + "");
lastTowRow.createCell(8).setCellValue("开新料：" + "");
for (int w = 0; w <= 9; w++) {
Cell cell = lastTowRow.getCell(w);
if (cell == null) {
cell = lastTowRow.createCell(w);
}
cell.setCellStyle(cellStyleLeft);
}
sheet.addMergedRegion(new CellRangeAddress(5 + collLen, 5 + collLen, 0, 2));
sheet.addMergedRegion(new CellRangeAddress(5 + collLen, 5 + collLen, 3, 7));
sheet.addMergedRegion(new CellRangeAddress(5 + collLen, 5 + collLen, 8, 9));
Row lastOneRow = sheet.createRow(6 + collLen);
lastOneRow.setHeight((short) 400);//设置行高
lastOneRow.createCell(0).setCellValue("角度锯：" + "");
lastOneRow.createCell(3).setCellValue("冲孔：" + "");
lastOneRow.createCell(8).setCellValue("焊接：" + "");
for (int w = 0; w <= 9; w++) {
Cell cell = lastOneRow.getCell(w);
if (cell == null) {
cell = lastOneRow.createCell(w);
}
cell.setCellStyle(cellStyleLeft);
}
sheet.addMergedRegion(new CellRangeAddress(6 + collLen, 6 + collLen, 0, 2));
sheet.addMergedRegion(new CellRangeAddress(6 + collLen, 6 + collLen, 3, 7));
sheet.addMergedRegion(new CellRangeAddress(6 + collLen, 6 + collLen, 8, 9));
}
// IO操作
FileOutputStream out = null;
try {
out = new FileOutputStream(path);
workbook.write(out);// 写文件
} catch (FileNotFoundException e) {
e.printStackTrace();
} finally {
out.close();
workbook.close();
}
return true;
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
