7646 |
13 Mar 19 |
nicklas |
1 |
package net.sf.basedb.util.excel; |
7646 |
13 Mar 19 |
nicklas |
2 |
|
7646 |
13 Mar 19 |
nicklas |
3 |
import java.util.Date; |
7646 |
13 Mar 19 |
nicklas |
4 |
|
7646 |
13 Mar 19 |
nicklas |
5 |
import org.apache.poi.ss.usermodel.Cell; |
7646 |
13 Mar 19 |
nicklas |
6 |
|
7646 |
13 Mar 19 |
nicklas |
7 |
/** |
7646 |
13 Mar 19 |
nicklas |
Represents a value and style format that should be written to |
7646 |
13 Mar 19 |
nicklas |
a cell in an excel worksheet. |
7646 |
13 Mar 19 |
nicklas |
10 |
|
7646 |
13 Mar 19 |
nicklas |
Note that Excel internally only supports String and Double values. |
7646 |
13 Mar 19 |
nicklas |
To get the intended result for integers, dates, times, etc. a |
7646 |
13 Mar 19 |
nicklas |
format string must be applied. |
7646 |
13 Mar 19 |
nicklas |
14 |
|
7646 |
13 Mar 19 |
nicklas |
@author nicklas |
7646 |
13 Mar 19 |
nicklas |
@since 3.15 |
7646 |
13 Mar 19 |
nicklas |
17 |
*/ |
7646 |
13 Mar 19 |
nicklas |
18 |
public abstract class ExcelValue<T> |
7646 |
13 Mar 19 |
nicklas |
19 |
{ |
7646 |
13 Mar 19 |
nicklas |
20 |
|
7649 |
14 Mar 19 |
nicklas |
// Some static cells |
7649 |
14 Mar 19 |
nicklas |
22 |
private static final ExcelValue<Void> EMPTY_CELL = new BlankValue<>(); |
7649 |
14 Mar 19 |
nicklas |
23 |
private static final ExcelValue<Boolean> TRUE = new BooleanValue(true); |
7649 |
14 Mar 19 |
nicklas |
24 |
private static final ExcelValue<Boolean> FALSE = new BooleanValue(false); |
7649 |
14 Mar 19 |
nicklas |
25 |
|
7646 |
13 Mar 19 |
nicklas |
26 |
/** |
7646 |
13 Mar 19 |
nicklas |
This creates a blank cell. |
7646 |
13 Mar 19 |
nicklas |
28 |
*/ |
7649 |
14 Mar 19 |
nicklas |
29 |
@SuppressWarnings("unchecked") |
7649 |
14 Mar 19 |
nicklas |
30 |
public static final <T> ExcelValue<T> emptyCell() |
7649 |
14 Mar 19 |
nicklas |
31 |
{ |
7649 |
14 Mar 19 |
nicklas |
32 |
return (ExcelValue<T>)EMPTY_CELL; |
7649 |
14 Mar 19 |
nicklas |
33 |
} |
7646 |
13 Mar 19 |
nicklas |
34 |
|
7646 |
13 Mar 19 |
nicklas |
35 |
/** |
7646 |
13 Mar 19 |
nicklas |
Create a string cell value. |
7646 |
13 Mar 19 |
nicklas |
37 |
*/ |
7646 |
13 Mar 19 |
nicklas |
38 |
public static ExcelValue<String> asString(String value) |
7646 |
13 Mar 19 |
nicklas |
39 |
{ |
7649 |
14 Mar 19 |
nicklas |
40 |
if (value == null) return emptyCell(); |
7646 |
13 Mar 19 |
nicklas |
41 |
return new StringValue(value); |
7646 |
13 Mar 19 |
nicklas |
42 |
} |
7646 |
13 Mar 19 |
nicklas |
43 |
|
7646 |
13 Mar 19 |
nicklas |
44 |
/** |
7646 |
13 Mar 19 |
nicklas |
Create a date cell value. The cell will be formatted according |
7646 |
13 Mar 19 |
nicklas |
to the specified format string which should be given as a Java-compatible |
7646 |
13 Mar 19 |
nicklas |
format string. If no format is specified "yyyy-MM-dd" is used. |
7646 |
13 Mar 19 |
nicklas |
48 |
*/ |
7646 |
13 Mar 19 |
nicklas |
49 |
public static ExcelValue<Date> asDate(Date value, String javaFormat) |
7646 |
13 Mar 19 |
nicklas |
50 |
{ |
7649 |
14 Mar 19 |
nicklas |
51 |
if (value == null) return emptyCell(); |
7646 |
13 Mar 19 |
nicklas |
52 |
if (javaFormat == null) javaFormat = "yyyy-MM-dd"; |
7646 |
13 Mar 19 |
nicklas |
53 |
return new DateValue(value, javaFormat); |
7646 |
13 Mar 19 |
nicklas |
54 |
} |
7646 |
13 Mar 19 |
nicklas |
55 |
|
7646 |
13 Mar 19 |
nicklas |
56 |
/** |
7646 |
13 Mar 19 |
nicklas |
Create a timestamp cell value. The cell will be formatted according |
7646 |
13 Mar 19 |
nicklas |
to the specified format string which should be given as a Java-compatible |
7646 |
13 Mar 19 |
nicklas |
format string. If no format is specified "yyyy-MM-dd HH:mm:ss" is used. |
7646 |
13 Mar 19 |
nicklas |
Note! There is really no difference between this metod and the {@link #asDate(Date, String)} |
7646 |
13 Mar 19 |
nicklas |
method except for the default format string. |
7646 |
13 Mar 19 |
nicklas |
62 |
*/ |
7646 |
13 Mar 19 |
nicklas |
63 |
public static ExcelValue<Date> asTimestamp(Date value, String javaFormat) |
7646 |
13 Mar 19 |
nicklas |
64 |
{ |
7649 |
14 Mar 19 |
nicklas |
65 |
if (value == null) return emptyCell(); |
7646 |
13 Mar 19 |
nicklas |
66 |
if (javaFormat == null) javaFormat = "yyyy-MM-dd HH:mm:ss"; |
7646 |
13 Mar 19 |
nicklas |
67 |
return new DateValue(value, javaFormat); |
7646 |
13 Mar 19 |
nicklas |
68 |
} |
7646 |
13 Mar 19 |
nicklas |
69 |
|
7647 |
13 Mar 19 |
nicklas |
70 |
/** |
7647 |
13 Mar 19 |
nicklas |
Create a numeric cell value without any decimals. |
7647 |
13 Mar 19 |
nicklas |
Same as calling {@link #asNumber(Number, int)} with |
7647 |
13 Mar 19 |
nicklas |
0 for the numDecimals parameter. |
7647 |
13 Mar 19 |
nicklas |
74 |
*/ |
7647 |
13 Mar 19 |
nicklas |
75 |
public static final ExcelValue<Number> asInt(Number value) |
7647 |
13 Mar 19 |
nicklas |
76 |
{ |
7649 |
14 Mar 19 |
nicklas |
77 |
if (value == null) return emptyCell(); |
7647 |
13 Mar 19 |
nicklas |
78 |
return new IntValue(value); |
7647 |
13 Mar 19 |
nicklas |
79 |
} |
7647 |
13 Mar 19 |
nicklas |
80 |
|
7647 |
13 Mar 19 |
nicklas |
81 |
/** |
7647 |
13 Mar 19 |
nicklas |
Create a numeric cell value and display a given number of |
7647 |
13 Mar 19 |
nicklas |
decimals. The numDecimals should be between 0 and 15, |
7647 |
13 Mar 19 |
nicklas |
or the "General" format will be used. |
7647 |
13 Mar 19 |
nicklas |
85 |
*/ |
7647 |
13 Mar 19 |
nicklas |
86 |
public static final ExcelValue<Number> asNumber(Number value, int numDecimals) |
7647 |
13 Mar 19 |
nicklas |
87 |
{ |
7649 |
14 Mar 19 |
nicklas |
88 |
if (value == null) return emptyCell(); |
7647 |
13 Mar 19 |
nicklas |
89 |
return new NumericValue(value, numberFormat(numDecimals)); |
7647 |
13 Mar 19 |
nicklas |
90 |
} |
7647 |
13 Mar 19 |
nicklas |
91 |
|
7647 |
13 Mar 19 |
nicklas |
92 |
/** |
7647 |
13 Mar 19 |
nicklas |
Create a numeric cell value with a custom numeric format. |
7647 |
13 Mar 19 |
nicklas |
94 |
*/ |
7647 |
13 Mar 19 |
nicklas |
95 |
public static final ExcelValue<Number> asNumber(Number value, String format) |
7647 |
13 Mar 19 |
nicklas |
96 |
{ |
7649 |
14 Mar 19 |
nicklas |
97 |
if (value == null) return emptyCell(); |
7647 |
13 Mar 19 |
nicklas |
98 |
return new NumericValue(value, format); |
7647 |
13 Mar 19 |
nicklas |
99 |
} |
7647 |
13 Mar 19 |
nicklas |
100 |
|
7647 |
13 Mar 19 |
nicklas |
101 |
/** |
7649 |
14 Mar 19 |
nicklas |
Create a numeric cell value with boolean format. |
7649 |
14 Mar 19 |
nicklas |
103 |
*/ |
7649 |
14 Mar 19 |
nicklas |
104 |
public static final ExcelValue<Boolean> asBoolean(Boolean value) |
7649 |
14 Mar 19 |
nicklas |
105 |
{ |
7649 |
14 Mar 19 |
nicklas |
106 |
if (value == null) return emptyCell(); |
7649 |
14 Mar 19 |
nicklas |
107 |
return value ? TRUE : FALSE; |
7649 |
14 Mar 19 |
nicklas |
108 |
} |
7649 |
14 Mar 19 |
nicklas |
109 |
|
7649 |
14 Mar 19 |
nicklas |
110 |
/** |
7647 |
13 Mar 19 |
nicklas |
Creates a format string for formatting numbers with the given number |
7647 |
13 Mar 19 |
nicklas |
of decimals (between 0 and 15). |
7647 |
13 Mar 19 |
nicklas |
113 |
*/ |
7647 |
13 Mar 19 |
nicklas |
114 |
public static String numberFormat(int numDecimals) |
7647 |
13 Mar 19 |
nicklas |
115 |
{ |
7647 |
13 Mar 19 |
nicklas |
116 |
if (numDecimals < 0 || numDecimals > 15) return null; |
7647 |
13 Mar 19 |
nicklas |
117 |
if (numDecimals == 0) return "0"; |
7647 |
13 Mar 19 |
nicklas |
118 |
StringBuilder sb = new StringBuilder(2+numDecimals); |
7647 |
13 Mar 19 |
nicklas |
119 |
sb.append("0."); |
7647 |
13 Mar 19 |
nicklas |
120 |
for (int i = 0; i < numDecimals; i++) |
7647 |
13 Mar 19 |
nicklas |
121 |
{ |
7647 |
13 Mar 19 |
nicklas |
122 |
sb.append("0"); |
7647 |
13 Mar 19 |
nicklas |
123 |
} |
7647 |
13 Mar 19 |
nicklas |
124 |
return sb.toString(); |
7647 |
13 Mar 19 |
nicklas |
125 |
} |
7647 |
13 Mar 19 |
nicklas |
126 |
|
7646 |
13 Mar 19 |
nicklas |
127 |
protected final T value; |
7646 |
13 Mar 19 |
nicklas |
128 |
protected final String format; |
7646 |
13 Mar 19 |
nicklas |
129 |
|
7651 |
14 Mar 19 |
nicklas |
130 |
protected ExcelValue(T value, String format) |
7646 |
13 Mar 19 |
nicklas |
131 |
{ |
7646 |
13 Mar 19 |
nicklas |
132 |
this.value = value; |
7646 |
13 Mar 19 |
nicklas |
133 |
this.format = format; |
7646 |
13 Mar 19 |
nicklas |
134 |
} |
7646 |
13 Mar 19 |
nicklas |
135 |
|
7646 |
13 Mar 19 |
nicklas |
136 |
/** |
7646 |
13 Mar 19 |
nicklas |
Get the stored value. |
7646 |
13 Mar 19 |
nicklas |
138 |
*/ |
7646 |
13 Mar 19 |
nicklas |
139 |
public T getValue() |
7646 |
13 Mar 19 |
nicklas |
140 |
{ |
7646 |
13 Mar 19 |
nicklas |
141 |
return value; |
7646 |
13 Mar 19 |
nicklas |
142 |
} |
7646 |
13 Mar 19 |
nicklas |
143 |
|
7646 |
13 Mar 19 |
nicklas |
144 |
/** |
7646 |
13 Mar 19 |
nicklas |
Get the format string. |
7646 |
13 Mar 19 |
nicklas |
146 |
*/ |
7646 |
13 Mar 19 |
nicklas |
147 |
public String getFormat() |
7646 |
13 Mar 19 |
nicklas |
148 |
{ |
7646 |
13 Mar 19 |
nicklas |
149 |
return format; |
7646 |
13 Mar 19 |
nicklas |
150 |
} |
7646 |
13 Mar 19 |
nicklas |
151 |
|
7646 |
13 Mar 19 |
nicklas |
152 |
/** |
7646 |
13 Mar 19 |
nicklas |
Write the value to the given cell and apply a format to it |
7646 |
13 Mar 19 |
nicklas |
with the help of the style creator. If no style creator is |
7646 |
13 Mar 19 |
nicklas |
given, the cell is not formatted. |
7646 |
13 Mar 19 |
nicklas |
156 |
*/ |
7646 |
13 Mar 19 |
nicklas |
157 |
public abstract void writeTo(Cell cell, CellStyleCreator styleCreator); |
7646 |
13 Mar 19 |
nicklas |
158 |
|
7646 |
13 Mar 19 |
nicklas |
159 |
/** |
7646 |
13 Mar 19 |
nicklas |
Represents a blank cell. |
7646 |
13 Mar 19 |
nicklas |
161 |
*/ |
7647 |
13 Mar 19 |
nicklas |
162 |
static class BlankValue<T> |
7647 |
13 Mar 19 |
nicklas |
163 |
extends ExcelValue<T> |
7646 |
13 Mar 19 |
nicklas |
164 |
{ |
7646 |
13 Mar 19 |
nicklas |
165 |
BlankValue() |
7646 |
13 Mar 19 |
nicklas |
166 |
{ |
7646 |
13 Mar 19 |
nicklas |
167 |
super(null, null); |
7646 |
13 Mar 19 |
nicklas |
168 |
} |
7646 |
13 Mar 19 |
nicklas |
169 |
@Override |
7646 |
13 Mar 19 |
nicklas |
170 |
public void writeTo(Cell cell, CellStyleCreator styleCreator) |
7646 |
13 Mar 19 |
nicklas |
171 |
{ |
7824 |
09 Jun 20 |
nicklas |
172 |
cell.setBlank(); |
7674 |
27 Mar 19 |
nicklas |
173 |
cell.setCellStyle(styleCreator.getBaseStyle()); |
7646 |
13 Mar 19 |
nicklas |
174 |
} |
7646 |
13 Mar 19 |
nicklas |
175 |
} |
7646 |
13 Mar 19 |
nicklas |
176 |
|
7646 |
13 Mar 19 |
nicklas |
177 |
/** |
7646 |
13 Mar 19 |
nicklas |
Represents a cell with a string value. |
7646 |
13 Mar 19 |
nicklas |
179 |
*/ |
7646 |
13 Mar 19 |
nicklas |
180 |
static class StringValue |
7646 |
13 Mar 19 |
nicklas |
181 |
extends ExcelValue<String> |
7646 |
13 Mar 19 |
nicklas |
182 |
{ |
7646 |
13 Mar 19 |
nicklas |
183 |
StringValue(String value) |
7646 |
13 Mar 19 |
nicklas |
184 |
{ |
7646 |
13 Mar 19 |
nicklas |
185 |
super(value, null); |
7646 |
13 Mar 19 |
nicklas |
186 |
} |
7646 |
13 Mar 19 |
nicklas |
187 |
|
7646 |
13 Mar 19 |
nicklas |
188 |
@Override |
7646 |
13 Mar 19 |
nicklas |
189 |
public void writeTo(Cell cell, CellStyleCreator styleCreator) |
7646 |
13 Mar 19 |
nicklas |
190 |
{ |
7646 |
13 Mar 19 |
nicklas |
191 |
cell.setCellValue(value); |
7674 |
27 Mar 19 |
nicklas |
192 |
cell.setCellStyle(styleCreator.getBaseStyle()); |
7646 |
13 Mar 19 |
nicklas |
193 |
} |
7646 |
13 Mar 19 |
nicklas |
194 |
} |
7646 |
13 Mar 19 |
nicklas |
195 |
|
7646 |
13 Mar 19 |
nicklas |
196 |
/** |
7646 |
13 Mar 19 |
nicklas |
Represents a cell with a date value. Note that date cells are |
7646 |
13 Mar 19 |
nicklas |
actually cells with a numeric value and a date-like format string. |
7646 |
13 Mar 19 |
nicklas |
199 |
*/ |
7646 |
13 Mar 19 |
nicklas |
200 |
static class DateValue |
7646 |
13 Mar 19 |
nicklas |
201 |
extends ExcelValue<Date> |
7646 |
13 Mar 19 |
nicklas |
202 |
{ |
7646 |
13 Mar 19 |
nicklas |
203 |
DateValue(Date value, String format) |
7646 |
13 Mar 19 |
nicklas |
204 |
{ |
7646 |
13 Mar 19 |
nicklas |
205 |
super(value, format); |
7646 |
13 Mar 19 |
nicklas |
206 |
} |
7646 |
13 Mar 19 |
nicklas |
207 |
|
7646 |
13 Mar 19 |
nicklas |
208 |
@Override |
7646 |
13 Mar 19 |
nicklas |
209 |
public void writeTo(Cell cell, CellStyleCreator styleCreator) |
7646 |
13 Mar 19 |
nicklas |
210 |
{ |
7646 |
13 Mar 19 |
nicklas |
211 |
cell.setCellValue(value); |
7646 |
13 Mar 19 |
nicklas |
212 |
if (format != null) |
7646 |
13 Mar 19 |
nicklas |
213 |
{ |
7646 |
13 Mar 19 |
nicklas |
214 |
cell.setCellStyle(styleCreator.getDateCellStyle(format)); |
7646 |
13 Mar 19 |
nicklas |
215 |
} |
7646 |
13 Mar 19 |
nicklas |
216 |
} |
7646 |
13 Mar 19 |
nicklas |
217 |
} |
7646 |
13 Mar 19 |
nicklas |
218 |
|
7647 |
13 Mar 19 |
nicklas |
219 |
/** |
7647 |
13 Mar 19 |
nicklas |
Represents a cell with a numeric value without decimals. |
7647 |
13 Mar 19 |
nicklas |
221 |
*/ |
7647 |
13 Mar 19 |
nicklas |
222 |
static class IntValue |
7647 |
13 Mar 19 |
nicklas |
223 |
extends ExcelValue<Number> |
7647 |
13 Mar 19 |
nicklas |
224 |
{ |
7647 |
13 Mar 19 |
nicklas |
225 |
IntValue(Number value) |
7647 |
13 Mar 19 |
nicklas |
226 |
{ |
7647 |
13 Mar 19 |
nicklas |
227 |
super(value, null); |
7647 |
13 Mar 19 |
nicklas |
228 |
} |
7647 |
13 Mar 19 |
nicklas |
229 |
|
7647 |
13 Mar 19 |
nicklas |
230 |
@Override |
7647 |
13 Mar 19 |
nicklas |
231 |
public void writeTo(Cell cell, CellStyleCreator styleCreator) |
7647 |
13 Mar 19 |
nicklas |
232 |
{ |
7649 |
14 Mar 19 |
nicklas |
233 |
cell.setCellValue(value.doubleValue()); |
7647 |
13 Mar 19 |
nicklas |
234 |
cell.setCellStyle(styleCreator.getIntCellStyle()); |
7649 |
14 Mar 19 |
nicklas |
235 |
} |
7647 |
13 Mar 19 |
nicklas |
236 |
} |
7647 |
13 Mar 19 |
nicklas |
237 |
|
7647 |
13 Mar 19 |
nicklas |
238 |
/** |
7647 |
13 Mar 19 |
nicklas |
Represents a cell with a numeric value. |
7647 |
13 Mar 19 |
nicklas |
240 |
*/ |
7647 |
13 Mar 19 |
nicklas |
241 |
static class NumericValue |
7647 |
13 Mar 19 |
nicklas |
242 |
extends ExcelValue<Number> |
7647 |
13 Mar 19 |
nicklas |
243 |
{ |
7647 |
13 Mar 19 |
nicklas |
244 |
NumericValue(Number value, String format) |
7647 |
13 Mar 19 |
nicklas |
245 |
{ |
7647 |
13 Mar 19 |
nicklas |
246 |
super(value, format); |
7647 |
13 Mar 19 |
nicklas |
247 |
} |
7647 |
13 Mar 19 |
nicklas |
248 |
|
7647 |
13 Mar 19 |
nicklas |
249 |
@Override |
7647 |
13 Mar 19 |
nicklas |
250 |
public void writeTo(Cell cell, CellStyleCreator styleCreator) |
7647 |
13 Mar 19 |
nicklas |
251 |
{ |
7649 |
14 Mar 19 |
nicklas |
252 |
cell.setCellValue(value.doubleValue()); |
7647 |
13 Mar 19 |
nicklas |
253 |
if (format != null) |
7647 |
13 Mar 19 |
nicklas |
254 |
{ |
7647 |
13 Mar 19 |
nicklas |
255 |
cell.setCellStyle(styleCreator.getCellStyle(format)); |
7647 |
13 Mar 19 |
nicklas |
256 |
} |
7647 |
13 Mar 19 |
nicklas |
257 |
} |
7647 |
13 Mar 19 |
nicklas |
258 |
} |
7647 |
13 Mar 19 |
nicklas |
259 |
|
7649 |
14 Mar 19 |
nicklas |
260 |
/** |
7649 |
14 Mar 19 |
nicklas |
Represents a cell with a numeric value. |
7649 |
14 Mar 19 |
nicklas |
262 |
*/ |
7649 |
14 Mar 19 |
nicklas |
263 |
static class BooleanValue |
7649 |
14 Mar 19 |
nicklas |
264 |
extends ExcelValue<Boolean> |
7649 |
14 Mar 19 |
nicklas |
265 |
{ |
7649 |
14 Mar 19 |
nicklas |
266 |
BooleanValue(boolean value) |
7649 |
14 Mar 19 |
nicklas |
267 |
{ |
7649 |
14 Mar 19 |
nicklas |
268 |
super(value, "BOOLEAN"); |
7649 |
14 Mar 19 |
nicklas |
269 |
} |
7649 |
14 Mar 19 |
nicklas |
270 |
|
7649 |
14 Mar 19 |
nicklas |
271 |
@Override |
7649 |
14 Mar 19 |
nicklas |
272 |
public void writeTo(Cell cell, CellStyleCreator styleCreator) |
7649 |
14 Mar 19 |
nicklas |
273 |
{ |
7649 |
14 Mar 19 |
nicklas |
274 |
cell.setCellValue(value ? 1 : 0); |
7649 |
14 Mar 19 |
nicklas |
275 |
cell.setCellStyle(styleCreator.getCellStyle(format)); |
7649 |
14 Mar 19 |
nicklas |
276 |
} |
7649 |
14 Mar 19 |
nicklas |
277 |
} |
7649 |
14 Mar 19 |
nicklas |
278 |
|
7646 |
13 Mar 19 |
nicklas |
279 |
} |