1. 首页
  2. 文章中心
  3. 相关功能实现

java生成excel表格

71 阅读 0 评论
我是靠谱客的博主 怕孤独鸵鸟，最近开发中收集的这篇文章主要介绍java生成excel表格，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

首先说下用java生成excel表格时候，需要准备的jar包：poi-ooxml-3.15.jar，这里都是测试数据，所以我只讲思想还有步骤，剩下的就看自己举一反三了。

//这里是定义的方法这里面的参数需要定义什么就传什么
public void test(String a,String b) throws IOException {
XSSFWorkbook book = new XSSFWorkbook();
//这个是封装的方法这里的参数都是我随便起的
test1(book,String a,String b);
String realFilePathName = targetDirPath + File.separator + fileName;
//最后再把文件生成输出
book.write(new FileOutputStream(realFilePathName));
}

这里设置的内容数据统一都用数字来代替

private void test1(XSSFWorkbook book, String a,Sting b)
Sheet sheet = book.createSheet(fileName);
Drawing drawing = sheet.createDrawingPatriarch(); //画图器
// ---------- 设置表头格式 -----------
XSSFCellStyle reportNameStyle = book.createCellStyle();
XSSFFont reportNameFont = book.createFont();
reportNameFont.setFontName("宋体");
reportNameFont.setFontHeightInPoints((short) 12);// 字号
reportNameFont.setBold(true);// 加粗
reportNameStyle.setFont(reportNameFont);
reportNameStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
reportNameStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
reportNameStyle.setWrapText(true);
reportNameStyle.setBorderBottom(BorderStyle.THIN);
reportNameStyle.setBorderLeft(BorderStyle.THIN);
reportNameStyle.setBorderRight(BorderStyle.THIN);
reportNameStyle.setBorderTop(BorderStyle.THIN);
// ---------- 设置单元格长度 -----------
Cell cell = null;
// 在所在的行设置所在的单元格（相当于列，初始从0开始,对应的就是A列）
int width = 256*16;
for (int i=0; i<16; i++) {
sheet.setColumnWidth(i, width);
}
sheet.setColumnWidth(2, 256*4);
sheet.setColumnWidth(6, 256*22);
// ---------- 设置表头 -----------
Row row = sheet.createRow(0);
// 设置相应的行（初始从0开始）
row.setHeight((short) 600);
// 写入相关数据到设置的行列中去。
for (int i=0; i<16; i++) {
cell = row.createCell(i);
cell.setCellStyle(reportNameStyle);
}
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));
cell = row.getCell(0);
cell.setCellValue("testA");
XSSFCellStyle leftStyle = getXssfCellStyle(book,XSSFFont.BOLDWEIGHT_NORMAL,XSSFCellStyle.ALIGN_LEFT);
cell.setCellStyle(leftStyle);
fillExcelLogo(book,watermarkCompanyLogo,sheet,row.getCell(10),row,6);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 10, 15));
// ---------- 设置表体格式 -----------
XSSFCellStyle reportStyle = book.createCellStyle();
XSSFFont reportFont = book.createFont();
reportFont.setFontName("黑体");
reportFont.setFontHeightInPoints((short) 8);// 字号
reportStyle.setFont(reportFont);
reportStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
reportStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
reportStyle.setWrapText(true);
reportStyle.setBorderBottom(BorderStyle.THIN);
reportStyle.setBorderLeft(BorderStyle.THIN);
reportStyle.setBorderRight(BorderStyle.THIN);
reportStyle.setBorderTop(BorderStyle.THIN);
// ---------- 设置第二行 -----------
row = sheet.createRow(1);
row.setHeight((short) 460);
for (int i=0; i<16; i++) {
cell = row.createCell(i);
cell.setCellStyle(reportStyle);
}
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
cell = row.getCell(0);
cell.setCellValue("1");
sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 8));
cell = row.getCell(2);
cell.setCellValue("2");
cell = row.getCell(9);
cell.setCellValue("3");
sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 11));
cell = row.getCell(10);
cell.setCellValue("4");
cell = row.getCell(12);
cell.setCellValue("5");
sheet.addMergedRegion(new CellRangeAddress(1, 1, 13, 15));
cell = row.getCell(13);
cell.setCellValue(startD + "-" + endD);
cell.setCellStyle(reportStyle);
// ---------- 设置第三行列头格式 -----------
XSSFCellStyle reportStyleHead = book.createCellStyle();
reportStyleHead.setFont(reportFont);
reportStyleHead.setAlignment(HorizontalAlignment.CENTER);// 左右居中
reportStyleHead.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
reportStyleHead.setWrapText(true);
reportStyleHead.setBorderBottom(BorderStyle.THIN);
reportStyleHead.setBorderLeft(BorderStyle.THIN);
reportStyleHead.setBorderRight(BorderStyle.THIN);
reportStyleHead.setBorderTop(BorderStyle.THIN);
reportStyleHead.setFillPattern(FillPatternType.SOLID_FOREGROUND);
reportStyleHead.setFillForegroundColor(new XSSFColor(new java.awt.Color(189,215,238)));
// ---------- 设置第三行（列头） -----------
row = sheet.createRow(2);
row.setHeight((short) 500);
for (int i=0; i<16; i++) {
cell = row.createCell(i);
cell.setCellStyle(reportStyleHead);
}
cell = row.getCell(0);
cell.setCellValue("1");
cell = row.getCell(1);
cell.setCellValue("2");
sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 3));
cell = row.getCell(2);
cell.setCellValue("3");
cell = row.getCell(4);
cell.setCellValue("4");
sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 6));
cell = row.getCell(5);
cell.setCellValue("5");
sheet.addMergedRegion(new CellRangeAddress(2, 2, 7, 8));
cell = row.getCell(7);
cell.setCellValue("6");
cell = row.getCell(9);
cell.setCellValue("7");
cell = row.getCell(10);
cell.setCellValue("8");
cell = row.getCell(11);
cell.setCellValue("9");
cell = row.getCell(12);
cell.setCellValue("10");
cell = row.getCell(13);
cell.setCellValue("11");
cell = row.getCell(14);
cell.setCellValue("12");
cell = row.getCell(15);
cell.setCellValue("13");
// ---------- 设置第四行 -----------
int workerNo = 1;
Double totalSalary = 0d;
Double calculateSalary = 0d;
Double personPay=0d;
Double grouppPay=0d;
for (User user : list) {
int rowNo = workerNo+2;
row = sheet.createRow(rowNo);
row.setHeight((short) 500);
for (int i=0; i<16; i++) {
cell = row.createCell(i);
cell.setCellStyle(reportStyle);
}
cell = row.getCell(0);
cell.setCellValue(workerNo);
cell = row.getCell(1);
cell.setCellValue("1");
sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 2, 3));
cell = row.getCell(2);
cell.setCellValue("2");
cell = row.getCell(4);
cell.setCellValue("3");
sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 5, 6));
cell = row.getCell(5);
cell.setCellValue("4");
sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 7, 8));
cell = row.getCell(7);
cell.setCellValue("5");
cell = row.getCell(9);
cell.setCellValue("6");
cell = row.getCell(10);
cell.setCellValue("7");
cell = row.getCell(11);
cell.setCellValue("8");
cell = row.getCell(12);
cell.setCellValue("9");
cell = row.getCell(13);
cell.setCellValue("10");
cell = row.getCell(14);
//这是插入图片的方法
fillExcelLogo(book,signUrl,sheet,row.getCell(15),row,15);
workerNo++;
}
// ---------- 设置第五行 -----------
row = sheet.createRow(workerNo+2);
row.setHeight((short) 500);
for (int i=11; i<16; i++) {
cell = row.createCell(i);
cell.setCellStyle(reportStyle);
}
XSSFCellStyle allMiddle = book.createCellStyle();
allMiddle.setFont(reportFont);
allMiddle.setAlignment(HorizontalAlignment.RIGHT);// 右对齐
allMiddle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
allMiddle.setWrapText(true);
allMiddle.setBorderBottom(BorderStyle.THIN);
allMiddle.setBorderLeft(BorderStyle.THIN);
allMiddle.setBorderRight(BorderStyle.THIN);
allMiddle.setBorderTop(BorderStyle.THIN);
for (int i=0; i<11; i++) {
cell = row.createCell(i);
cell.setCellStyle(allMiddle);
}
sheet.addMergedRegion(new CellRangeAddress(workerNo+2, workerNo+2, 0, 10));
cell = row.getCell(0);
cell.setCellValue("13");
cell = row.getCell(11);
cell.setCellValue(calculateSalary);
cell = row.getCell(12);
cell.setCellValue(calculateSalary);
cell = row.getCell(13);
cell.setCellValue(calculateSalary);
cell = row.getCell(14);
cell.setCellValue(calculateSalary);
//这里是做了动态扩展行的
// ---------- 设置第六行 -----------
XSSFCellStyle firstStart = book.createCellStyle();
firstStart.setFont(reportFont);
firstStart.setAlignment(HorizontalAlignment.RIGHT);// 右对齐
firstStart.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
firstStart.setWrapText(true);
firstStart.setBorderBottom(BorderStyle.THIN);
firstStart.setBorderLeft(BorderStyle.THIN);
firstStart.setBorderRight(BorderStyle.THIN);
firstStart.setBorderTop(BorderStyle.THIN);
int index=3;
int size = list.size();
//获取取余前除了多少次
int a=size/3;
//获取取余的个数，单独再扩展一行
int count=(size%3);
if(count!=0){
count=1;
}
//总共需要扩展行
int totalCount=a+count;
int countA=0;
//创建好相对应的行以及单元格
for(int i=0;i<totalCount;i++) {
row = sheet.createRow(workerNo + 3 + i);
//提前合并单元格
sheet.addMergedRegion(new CellRangeAddress(workerNo + 3 + i, workerNo + 3 + i, 0, 1));
sheet.addMergedRegion(new CellRangeAddress(workerNo + 3 + i, workerNo + 3 + i, 2, 4));
sheet.addMergedRegion(new CellRangeAddress(workerNo + 3 + i, workerNo + 3 + i, 5, 6));
sheet.addMergedRegion(new CellRangeAddress(workerNo + 3 + i, workerNo + 3 + i, 7, 9));
sheet.addMergedRegion(new CellRangeAddress(workerNo + 3 + i, workerNo + 3 + i, 10, 11));
sheet.addMergedRegion(new CellRangeAddress(workerNo + 3 + i, workerNo + 3 + i, 12, 15));
row.setHeight((short) 600);
for (int c = 0; c < 16; c++) {
cell = row.createCell(c);
cell.setCellStyle(firstStart);
}
List<User> newList=new ArrayList<>();
i=i+2*i;
if(i+index>size){
//获取余数
int h=(size%3);
//最后一个数如果没有达到三的话则截取i+余数防止截取报错。
newList = list.subList(i, i+h);
}else {
newList = list.subList(i, i + index);
}
cell = row.getCell(0);
cell.setCellValue(newList.get(0).getA()+"内容：");
fillExcelLogo(book, newList.get(0).getSignUrl(), sheet, row.getCell(2), row, 2);
cell = row.getCell(5);
if(newList.size()>1) {
cell.setCellValue(newList.get(1).getA() + "内容：");
fillExcelLogo(book, newList.get(1).getSignUrl(), sheet, row.getCell(7), row, 7);
}
if(newList.size()>2){
cell = row.getCell(10);
cell.setCellValue(newList.get(2).getA()+"内容：");
fillExcelLogo(book, newList.get(2).getSignUrl(), sheet, row.getCell(12), row, 12);
}
i=i-(2*countA);
countA++;
}
row = sheet.createRow(workerNo+4+totalCount);
for (int i=0; i<16; i++) {
cell = row.createCell(i);
cell.setCellStyle(lastStyle);
}
sheet.addMergedRegion(new CellRangeAddress(workerNo+4+totalCount, workerNo+4+totalCount, 0, 15));
cell = row.getCell(0);
cell.setCellValue("制表时间：" +date + "（北京时间）
导出人：" + name);
}

这里是插入图片的方法

private void fillExcelLogo(XSSFWorkbook book, String image, Sheet sheet1, Cell finalCell,Row row,int leftTopCellNum) throws IOException {
if (StringUtils.isBlank(image)) {
return;
}
Cell cell1 = null;
Cell cell2 = finalCell;
HttpURLConnection conn = null;
InputStream inputStream = null;
BufferedImage image = null;
try {
URL url = new URL(image);
conn = (HttpURLConnection) url.openConnection();
conn.setRequestMethod("GET");
conn.setConnectTimeout(20 * 1000);
ByteArrayOutputStream output = new ByteArrayOutputStream();
inputStream = conn.getInputStream();
IOUtils.copy(inputStream, output);
//
必须准备两份资源！！！
ByteArrayInputStream inputStreamPic = new ByteArrayInputStream(output.toByteArray());
ByteArrayInputStream imageStream = new ByteArrayInputStream(output.toByteArray());
image = ImageIO.read(imageStream);
int pictureIdx = book.addPicture(inputStreamPic, Workbook.PICTURE_TYPE_JPEG);
float initWith = 0;
float initHigh = 0;
float v = row.getHeightInPoints() / 72f;
//
1 inch = 72 point = 96 px
initHigh = v * 96f;
float highScaler = initHigh/(float) image.getHeight();
initWith = (float)image.getWidth() * highScaler;
int i1 = cell2.getColumnIndex() + 1 - leftTopCellNum;
int allColumWithInPx = 0;
for (int i = 0; i < i1; i++) {
allColumWithInPx = allColumWithInPx + Math.round(sheet1.getColumnWidthInPixels(row.getCell(leftTopCellNum+i).getColumnIndex()));
}
int columWidthInPx = allColumWithInPx / i1;
//
int columWidthInPx = Math.round(sheet1.getColumnWidthInPixels(cell2.getColumnIndex()));
int i = Math.round(initWith)/ columWidthInPx;
if (i > 0 && i < (finalCell.getColumnIndex() - leftTopCellNum)) {
cell1 = row.getCell(cell2.getColumnIndex() - i);
} else if (i >= (finalCell.getColumnIndex() - leftTopCellNum)) {
cell1 = row.getCell(leftTopCellNum);
} else {
cell1 = cell2;
}
CreationHelper helper = sheet1.getWorkbook().getCreationHelper();
ClientAnchor anchor = helper.createClientAnchor();
//
图像右上角所在单元格的图像右上角坐标
anchor.setDx2(Units.pixelToEMU(columWidthInPx));
anchor.setDy2(Units.toEMU(row.getHeightInPoints())); //
//
图像左下角所在单元格的图像左下角坐标
anchor.setDx1(0); // TODO: 2022/3/18 理应事实计算
anchor.setDy1(0);
//
图像左下角所处单元格与图像右上角所处单元格
anchor.setCol1(cell1.getColumnIndex());
anchor.setRow1(cell1.getRowIndex());
anchor.setCol2(cell2.getColumnIndex());
anchor.setRow2(cell2.getRowIndex());
//
边框自适应（拉动单元格时，图片追随）
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
//
最终插入图片
Drawing drawing = sheet1.createDrawingPatriarch();
drawing.createPicture(anchor, pictureIdx);
} catch (Exception e) {
e.printStackTrace();
} finally {
inputStream.close();
}
}

最后

以上就是怕孤独鸵鸟为你收集整理的java生成excel表格的全部内容，希望文章能够帮你解决java生成excel表格所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(47)

相关文章

java操作excel文件（创建）
java操作excel文件（创建）
PoiUtil.java 用于excel间sheet复制
PoiUtil.java 用于excel间sheet复制
Java怎么将数据写入ExcelJava怎么将数据写入Excel
Java怎么将数据写入ExcelJava怎么将数据写入Excel
将数据库内容导入WORD模板中相应位置的小程序1.DBUtil连接数据库：DAO层：
将数据库内容导入WORD模板中相应位置的小程序1.DBUtil连接数据库：DAO层：
java生成excel表格
java生成excel表格
Java 把数据写入execl一、创建项目二、写
Java 把数据写入execl一、创建项目二、写
poi-对于word的操作（一）
poi-对于word的操作（一）
java写入excel数据_Java-写入数据到Excel文件中_Demo（转载+完善）
java写入excel数据_Java-写入数据到Excel文件中_Demo（转载+完善）

评论列表共有 0 条评论

立即
投稿
返回
顶部