java生成excel表格

1
2
3
4
5
6
7
8
9
10
//这里是定义的方法这里面的参数需要定义什么就传什么
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));
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
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);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
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();
}
}