POI - copie de style en XSSF entre deux Workbook
Bonjour
Je cherche a copier des styles entre deux workbook pour des fichiers XLSX en POI 3.13
J'ai essayé sans succes
Code:
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
|
1 test cloneStyleFrom
XSSFCellStyle newCellStyle = workbook.createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); //On clone le style
=> ne copie pas les background, ni les borders et quand il y a trop de style cela pose soucis
2 test. Retrouver style existant afin de ne pas les duppliquer, et de rechercher un style existant
private static XSSFCellStyle getSameCellStyle(XSSFCell oldCell, XSSFCell newCell, List<XSSFCellStyle> styleList) {
XSSFCellStyle styleToFind = oldCell.getCellStyle();
XSSFCellStyle currentCellStyle = null;
XSSFCellStyle returnCellStyle = null;
Iterator<XSSFCellStyle> iterator = styleList.iterator();
XSSFFont oldFont = null;
XSSFFont newFont = null;
while (iterator.hasNext() && returnCellStyle == null) {
try {
currentCellStyle = iterator.next();
if (currentCellStyle.getAlignment() != styleToFind.getAlignment()) {
System.out.println("getSameCellStyle() a");
continue;
}
if (currentCellStyle.getHidden() != styleToFind.getHidden()) {
System.out.println("getSameCellStyle() b");
continue;
}
if (currentCellStyle.getLocked() != styleToFind.getLocked()) {
System.out.println("getSameCellStyle() c");
continue;
}
if (currentCellStyle.getWrapText() != styleToFind.getWrapText()) {
System.out.println("getSameCellStyle() d");
continue;
}
if (currentCellStyle.getBorderBottom() != styleToFind.getBorderBottom()) {
System.out.println("getSameCellStyle() e");
continue;
}
if (currentCellStyle.getBorderLeft() != styleToFind.getBorderLeft()) {
System.out.println("getSameCellStyle() f");
continue;
}
if (currentCellStyle.getBorderRight() != styleToFind.getBorderRight()) {
System.out.println("getSameCellStyle() - g");
continue;
}
if (currentCellStyle.getBorderTop() != styleToFind.getBorderTop()) {
System.out.println("getSameCellStyle() - h");
continue;
}
if (currentCellStyle.getBottomBorderColor() != styleToFind.getBottomBorderColor()) {
System.out.println("getSameCellStyle() - i");
continue;
}
if (currentCellStyle.getFillBackgroundColor() != styleToFind.getFillBackgroundColor()) {
System.out.println("getSameCellStyle() - j");
continue;
}
if (currentCellStyle.getFillForegroundColor() != styleToFind.getFillForegroundColor()) {
System.out.println("getSameCellStyle() - k");
continue;
}
if (currentCellStyle.getFillPattern() != styleToFind.getFillPattern()) {
System.out.println("getSameCellStyle() - l");
continue;
}
if (currentCellStyle.getIndention() != styleToFind.getIndention()) {
System.out.println("getSameCellStyle() - m");
continue;
}
if (currentCellStyle.getLeftBorderColor() != styleToFind.getLeftBorderColor()) {
System.out.println("getSameCellStyle() - n");
continue;
}
if (currentCellStyle.getRightBorderColor() != styleToFind.getRightBorderColor()) {
System.out.println("getSameCellStyle() - o");
continue;
}
if (currentCellStyle.getRotation() != styleToFind.getRotation()) {
System.out.println("p");
continue;
}
if (currentCellStyle.getTopBorderColor() != styleToFind.getTopBorderColor()) {
System.out.println("getSameCellStyle() - q");
continue;
}
if (currentCellStyle.getVerticalAlignment() != styleToFind.getVerticalAlignment()) {
System.out.println("getSameCellStyle() - r");
continue;
}
oldFont = oldCell.getSheet().getWorkbook().getFontAt(oldCell.getCellStyle().getFontIndex());
newFont = newCell.getSheet().getWorkbook().getFontAt(currentCellStyle.getFontIndex());
if (newFont.getBoldweight() == oldFont.getBoldweight()) {
System.out.println("1");
continue;
}
if (newFont.getColor() == oldFont.getColor()) {
System.out.println("2");
continue;
}
if (newFont.getFontHeight() == oldFont.getFontHeight()) {
continue;
}
if (newFont.getFontName() == oldFont.getFontName()) {
continue;
}
if (newFont.getItalic() == oldFont.getItalic()) {
continue;
}
if (newFont.getStrikeout() == oldFont.getStrikeout()) {
continue;
}
if (newFont.getTypeOffset() == oldFont.getTypeOffset()) {
continue;
}
if (newFont.getUnderline() == oldFont.getUnderline()) {
continue;
}
if (newFont.getCharSet() == oldFont.getCharSet()) {
continue;
}
if (oldCell.getCellStyle().getDataFormatString().equals(currentCellStyle.getDataFormatString())) {
System.out.println("getSameCellStyle() - 20");
continue;
}
returnCellStyle = currentCellStyle;
}catch(Exception e1){
e1.printStackTrace();
}
}
return returnCellStyle;
}
=> les styles sont dupliqués,car impossible au niveau des Borders de retrouver un style déjà existant.
la methode sort à ces endroits
if (currentCellStyle.getBorderRight() != styleToFind.getBorderRight())
if (currentCellStyle.getAlignment() != styleToFind.getAlignment())
Bien sur lorsqu'un nouveau style est cree je prend les informations de l'ancien style
XSSFCellStyle newCellStyle = getSameCellStyle(oldCell, newCell, styleMap);
if (newCellStyle == null) {
newCellStyle.setAlignment(oldCell.getCellStyle().getAlignment());
newCellStyle.setHidden(oldCell.getCellStyle().getHidden());
newCellStyle.setLocked(oldCell.getCellStyle().getLocked());
newCellStyle.setWrapText(oldCell.getCellStyle().getWrapText());
newCellStyle.setBorderBottom(oldCell.getCellStyle().getBorderBottom());
newCellStyle.setBorderLeft(oldCell.getCellStyle().getBorderLeft());
newCellStyle.setBorderRight(oldCell.getCellStyle().getBorderRight());
newCellStyle.setBorderTop(oldCell.getCellStyle().getBorderTop());
//Le code est basé sur
//<a href="http://www.coderanch.com/t/420958/open-source/Copying-sheet-excel-file-excel" target="_blank">http://www.coderanch.com/t/420958/op...cel-file-excel</a> |
Auriez vous une autre idée
Merci d'avance
Phil
