PoiSheetUtility.java
2.59 KB
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
package com.bckefu.excel.util;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
/**
* @author caoliang1918@aliyun.com
* @Date 2017/11/5:23:52
*/
public class PoiSheetUtility {
public static void deleteColumn(Sheet sheet, int columnToDelete) {
int maxColumn = 0;
for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
Row row = sheet.getRow(r);
// if no row exists here; then nothing to do; next!
if (row == null) {
continue;
}
// if the row doesn't have this many columns then we are good; next!
int lastColumn = row.getLastCellNum();
if (lastColumn > maxColumn) {
maxColumn = lastColumn;
}
if (lastColumn < columnToDelete) {
continue;
}
for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
Cell oldCell = row.getCell(x - 1);
if (oldCell != null) {
row.removeCell(oldCell);
}
Cell nextCell = row.getCell(x);
if (nextCell != null) {
Cell newCell = row.createCell(x-1 , nextCell.getCellTypeEnum());
cloneCell(newCell, nextCell);
}
}
}
// Adjust the column widths
for (int c = 0; c < maxColumn; c++) {
sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
}
}
/*
* Takes an existing Cell and merges all the styles and forumla
* into the new one
*/
private static void cloneCell(Cell cNew, Cell cOld) {
cNew.setCellComment(cOld.getCellComment());
cNew.setCellStyle(cOld.getCellStyle());
switch (cNew.getCellTypeEnum()) {
case BOOLEAN: {
cNew.setCellValue(cOld.getBooleanCellValue());
break;
}
case NUMERIC: {
cNew.setCellValue(cOld.getNumericCellValue());
break;
}
case STRING: {
cNew.setCellValue(cOld.getStringCellValue());
break;
}
case ERROR: {
cNew.setCellValue(cOld.getErrorCellValue());
break;
}
case FORMULA: {
cNew.setCellFormula(cOld.getCellFormula());
break;
}
default:
cNew.setCellValue(cOld.getStringCellValue());
}
}
}