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.HashMap; |
7674 |
27 Mar 19 |
nicklas |
4 |
import java.util.IdentityHashMap; |
7646 |
13 Mar 19 |
nicklas |
5 |
import java.util.Locale; |
7646 |
13 Mar 19 |
nicklas |
6 |
import java.util.Map; |
7646 |
13 Mar 19 |
nicklas |
7 |
|
7646 |
13 Mar 19 |
nicklas |
8 |
import org.apache.poi.ss.usermodel.CellStyle; |
7646 |
13 Mar 19 |
nicklas |
9 |
import org.apache.poi.ss.usermodel.DataFormat; |
7674 |
27 Mar 19 |
nicklas |
10 |
import org.apache.poi.ss.usermodel.Font; |
7646 |
13 Mar 19 |
nicklas |
11 |
import org.apache.poi.ss.usermodel.Workbook; |
7646 |
13 Mar 19 |
nicklas |
12 |
import org.apache.poi.ss.util.DateFormatConverter; |
7646 |
13 Mar 19 |
nicklas |
13 |
|
7646 |
13 Mar 19 |
nicklas |
14 |
/** |
7646 |
13 Mar 19 |
nicklas |
Helper class for keeping track of formatting styles that has |
7646 |
13 Mar 19 |
nicklas |
been created for given Excel worksheet. An instance of this |
7646 |
13 Mar 19 |
nicklas |
class is needed when calling {@link ExcelValue#writeTo(org.apache.poi.ss.usermodel.Cell, CellStyleCreator)}. |
7646 |
13 Mar 19 |
nicklas |
18 |
|
7646 |
13 Mar 19 |
nicklas |
@author nicklas |
7646 |
13 Mar 19 |
nicklas |
@since 3.15 |
7646 |
13 Mar 19 |
nicklas |
21 |
*/ |
7646 |
13 Mar 19 |
nicklas |
22 |
public class CellStyleCreator |
7646 |
13 Mar 19 |
nicklas |
23 |
{ |
7646 |
13 Mar 19 |
nicklas |
24 |
|
7646 |
13 Mar 19 |
nicklas |
25 |
/** |
7646 |
13 Mar 19 |
nicklas |
Converts a Java date format string to the corresponding Excel |
7646 |
13 Mar 19 |
nicklas |
date format string. |
7646 |
13 Mar 19 |
nicklas |
28 |
*/ |
7674 |
27 Mar 19 |
nicklas |
29 |
public static String convertJavaDateFormatToExcelFormat(String javaFormat) |
7646 |
13 Mar 19 |
nicklas |
30 |
{ |
7646 |
13 Mar 19 |
nicklas |
31 |
if (javaFormat == null) return null; |
7646 |
13 Mar 19 |
nicklas |
32 |
String excelFormat = DateFormatConverter.convert(Locale.getDefault(), javaFormat); |
7646 |
13 Mar 19 |
nicklas |
33 |
|
7646 |
13 Mar 19 |
nicklas |
// Get rid of Locale prefix since [$-xxxx] and suffix ;@ |
7646 |
13 Mar 19 |
nicklas |
35 |
String prefix = DateFormatConverter.getPrefixForLocale(Locale.getDefault()); |
7646 |
13 Mar 19 |
nicklas |
36 |
int clipStart = excelFormat.startsWith(prefix) ? prefix.length() : 0; |
7646 |
13 Mar 19 |
nicklas |
37 |
int clipEnd = excelFormat.endsWith(";@") ? 2 : 0; |
7646 |
13 Mar 19 |
nicklas |
38 |
if (clipStart > 0 || clipEnd > 0) |
7646 |
13 Mar 19 |
nicklas |
39 |
{ |
7646 |
13 Mar 19 |
nicklas |
40 |
excelFormat = excelFormat.substring(clipStart, excelFormat.length() - clipEnd); |
7646 |
13 Mar 19 |
nicklas |
41 |
} |
7646 |
13 Mar 19 |
nicklas |
42 |
return excelFormat; |
7646 |
13 Mar 19 |
nicklas |
43 |
} |
7674 |
27 Mar 19 |
nicklas |
44 |
|
7646 |
13 Mar 19 |
nicklas |
45 |
|
7674 |
27 Mar 19 |
nicklas |
46 |
private final Workbook workbook; |
7674 |
27 Mar 19 |
nicklas |
47 |
private final DataFormat dataFormat; |
7674 |
27 Mar 19 |
nicklas |
// Base style --> Format string --> Final cell style |
7674 |
27 Mar 19 |
nicklas |
49 |
private final Map<CellStyle, Map<String, CellStyle>> styles; |
7674 |
27 Mar 19 |
nicklas |
50 |
private CellStyle baseStyle; |
7674 |
27 Mar 19 |
nicklas |
51 |
|
7674 |
27 Mar 19 |
nicklas |
52 |
/** |
7674 |
27 Mar 19 |
nicklas |
Creates a new style creator for the given Excel workbook. |
7674 |
27 Mar 19 |
nicklas |
54 |
*/ |
7674 |
27 Mar 19 |
nicklas |
55 |
public CellStyleCreator(Workbook workbook) |
7674 |
27 Mar 19 |
nicklas |
56 |
{ |
7674 |
27 Mar 19 |
nicklas |
57 |
this.workbook = workbook; |
7674 |
27 Mar 19 |
nicklas |
58 |
this.dataFormat = workbook.createDataFormat(); |
7674 |
27 Mar 19 |
nicklas |
59 |
this.styles = new IdentityHashMap<>(); |
7674 |
27 Mar 19 |
nicklas |
60 |
} |
7647 |
13 Mar 19 |
nicklas |
61 |
|
7646 |
13 Mar 19 |
nicklas |
62 |
/** |
7674 |
27 Mar 19 |
nicklas |
Create a new cell style. |
7674 |
27 Mar 19 |
nicklas |
64 |
*/ |
7674 |
27 Mar 19 |
nicklas |
65 |
public CellStyle createStyle() |
7674 |
27 Mar 19 |
nicklas |
66 |
{ |
7674 |
27 Mar 19 |
nicklas |
67 |
return workbook.createCellStyle(); |
7674 |
27 Mar 19 |
nicklas |
68 |
} |
7674 |
27 Mar 19 |
nicklas |
69 |
|
7674 |
27 Mar 19 |
nicklas |
70 |
/** |
7674 |
27 Mar 19 |
nicklas |
Create a new font style. |
7674 |
27 Mar 19 |
nicklas |
72 |
*/ |
7674 |
27 Mar 19 |
nicklas |
73 |
public Font createFont() |
7674 |
27 Mar 19 |
nicklas |
74 |
{ |
7674 |
27 Mar 19 |
nicklas |
75 |
return workbook.createFont(); |
7674 |
27 Mar 19 |
nicklas |
76 |
} |
7674 |
27 Mar 19 |
nicklas |
77 |
|
7674 |
27 Mar 19 |
nicklas |
78 |
/** |
7674 |
27 Mar 19 |
nicklas |
Get the current base style for new cells. |
7674 |
27 Mar 19 |
nicklas |
80 |
*/ |
7674 |
27 Mar 19 |
nicklas |
81 |
public CellStyle getBaseStyle() |
7674 |
27 Mar 19 |
nicklas |
82 |
{ |
7674 |
27 Mar 19 |
nicklas |
83 |
return baseStyle; |
7674 |
27 Mar 19 |
nicklas |
84 |
} |
7674 |
27 Mar 19 |
nicklas |
85 |
|
7674 |
27 Mar 19 |
nicklas |
86 |
/** |
7674 |
27 Mar 19 |
nicklas |
Set a base style to use for new cells. When a base style has been set it used |
7674 |
27 Mar 19 |
nicklas |
as a template for all new styles that are created with {@link #getCellStyle(String)}. |
7674 |
27 Mar 19 |
nicklas |
This can be useful for setting different fonts, colors, etc. |
7674 |
27 Mar 19 |
nicklas |
90 |
*/ |
7674 |
27 Mar 19 |
nicklas |
91 |
public void setBaseStyle(CellStyle baseStyle) |
7674 |
27 Mar 19 |
nicklas |
92 |
{ |
7674 |
27 Mar 19 |
nicklas |
93 |
this.baseStyle = baseStyle; |
7674 |
27 Mar 19 |
nicklas |
94 |
} |
7674 |
27 Mar 19 |
nicklas |
95 |
|
7674 |
27 Mar 19 |
nicklas |
96 |
/** |
7647 |
13 Mar 19 |
nicklas |
Get the cell style for formatting numbers without decimals. |
7647 |
13 Mar 19 |
nicklas |
This is equivalent to {@link #getCellStyle(String)} with "0" |
7647 |
13 Mar 19 |
nicklas |
as parameter. |
7647 |
13 Mar 19 |
nicklas |
100 |
*/ |
7647 |
13 Mar 19 |
nicklas |
101 |
public CellStyle getIntCellStyle() |
7647 |
13 Mar 19 |
nicklas |
102 |
{ |
7674 |
27 Mar 19 |
nicklas |
103 |
return getCellStyle("0"); |
7647 |
13 Mar 19 |
nicklas |
104 |
} |
7647 |
13 Mar 19 |
nicklas |
105 |
|
7647 |
13 Mar 19 |
nicklas |
106 |
/** |
7646 |
13 Mar 19 |
nicklas |
Get the cell style for the given date format string. If a style |
7646 |
13 Mar 19 |
nicklas |
already exists it is re-used, otherwise a new style is created. |
7646 |
13 Mar 19 |
nicklas |
Note that if the format string is a date format string, it must be |
7646 |
13 Mar 19 |
nicklas |
in Java format. Use {@link #getCellStyle(String)} if the format |
7646 |
13 Mar 19 |
nicklas |
string is in Excel format. |
7646 |
13 Mar 19 |
nicklas |
@param javaFormat The Java format string |
7646 |
13 Mar 19 |
nicklas |
113 |
*/ |
7646 |
13 Mar 19 |
nicklas |
114 |
public CellStyle getDateCellStyle(String javaFormat) |
7646 |
13 Mar 19 |
nicklas |
115 |
{ |
7646 |
13 Mar 19 |
nicklas |
116 |
String format = convertJavaDateFormatToExcelFormat(javaFormat); |
7646 |
13 Mar 19 |
nicklas |
117 |
return getCellStyle(format); |
7646 |
13 Mar 19 |
nicklas |
118 |
} |
7646 |
13 Mar 19 |
nicklas |
119 |
|
7646 |
13 Mar 19 |
nicklas |
120 |
/** |
7646 |
13 Mar 19 |
nicklas |
Get the cell style for the given format string. If a style |
7646 |
13 Mar 19 |
nicklas |
already exists it is re-used, otherwise a new style is created. |
7646 |
13 Mar 19 |
nicklas |
Note that if the format string is a date format string, it must be |
7646 |
13 Mar 19 |
nicklas |
in Excel format. Use {@link #getDateCellStyle(String)} if the format |
7646 |
13 Mar 19 |
nicklas |
string is in Java format. |
7646 |
13 Mar 19 |
nicklas |
126 |
|
7646 |
13 Mar 19 |
nicklas |
@param format The format string |
7646 |
13 Mar 19 |
nicklas |
128 |
*/ |
7646 |
13 Mar 19 |
nicklas |
129 |
public CellStyle getCellStyle(String format) |
7646 |
13 Mar 19 |
nicklas |
130 |
{ |
7674 |
27 Mar 19 |
nicklas |
131 |
if (format == null) return baseStyle; |
7674 |
27 Mar 19 |
nicklas |
132 |
Map<String, CellStyle> formats = styles.get(baseStyle); |
7674 |
27 Mar 19 |
nicklas |
133 |
if (formats == null) |
7674 |
27 Mar 19 |
nicklas |
134 |
{ |
7674 |
27 Mar 19 |
nicklas |
135 |
formats = new HashMap<>(); |
7674 |
27 Mar 19 |
nicklas |
136 |
styles.put(baseStyle, formats); |
7674 |
27 Mar 19 |
nicklas |
137 |
} |
7674 |
27 Mar 19 |
nicklas |
138 |
CellStyle style = formats.get(format); |
7646 |
13 Mar 19 |
nicklas |
139 |
if (style == null) |
7646 |
13 Mar 19 |
nicklas |
140 |
{ |
7646 |
13 Mar 19 |
nicklas |
141 |
style = workbook.createCellStyle(); |
7674 |
27 Mar 19 |
nicklas |
142 |
if (baseStyle != null) style.cloneStyleFrom(baseStyle); |
7646 |
13 Mar 19 |
nicklas |
143 |
style.setDataFormat(dataFormat.getFormat(format)); |
7674 |
27 Mar 19 |
nicklas |
144 |
formats.put(format, style); |
7646 |
13 Mar 19 |
nicklas |
145 |
} |
7646 |
13 Mar 19 |
nicklas |
146 |
return style; |
7646 |
13 Mar 19 |
nicklas |
147 |
} |
7646 |
13 Mar 19 |
nicklas |
148 |
|
7646 |
13 Mar 19 |
nicklas |
149 |
} |