1 import java.io.ByteArrayInputStream;
2 import java.io.ByteArrayOutputStream;
3 import java.io.InputStream;
4 import java.io.UnsupportedEncodingException;
5 import java.lang.reflect.Array;
6 import java.lang.reflect.InvocationTargetException;
7 import java.lang.reflect.Method;
8 import java.text.SimpleDateFormat;
9 import java.util.List;
10 import java.util.regex.Matcher;
11 import java.util.regex.Pattern;
12
13 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
14 import org.apache.poi.ss.usermodel.Cell;
15 import org.apache.poi.ss.usermodel.CellStyle;
16 import org.apache.poi.ss.usermodel.Font;
17 import org.apache.poi.ss.usermodel.IndexedColors;
18 import org.apache.poi.ss.usermodel.Row;
19 import org.apache.poi.ss.usermodel.Sheet;
20 import org.apache.poi.ss.usermodel.Workbook;
21
22 import com.gzbugu.action.ActionBase;
23 import com.gzbugu.domain.BusiObservePlan;
24
25 /**
26 * @author ylh
27 */
28 public class ExcelAction extends ActionBase{
29
30 private List downExcelAttrsList;
31 private String fileName;
32
33 /**
34 * 导出Excel公共方法
35 * 使用action模板配置文件：
36 <action name="自定义" class="自定义" method="自定义">
37 <result name="success" type="chain">
38 <param name="actionName">getDownloadExcel</param>
39 <param name="downExcelAttrsList">${downExcelAttrsList}</param>
40 </result>
41 </action>
42 * 必须的参数downExcelAttrsList，必须是有setter,getter方法的属性，其包括参数顺序如下：
43 * @param valueList 必须，通过hql查询数据库后返回的对象List,支持关联查询,在属性前加上对象名: {"BusiObservePlan.planType,0:个人计划,1:部门月度计划",...}
44 * @param sheetName 必须，Excel的sheet的名字，
45 * @param beanPropertyNames 必须，对象中需要被输出的值,如果是状态值需要被替换的，则如此填写： {"propertyName,0:个人计划,1:部门月度计划", ...}
46 * @param titleNames 必须，对应上面属性的名字，用来做Excel的表头
47 * @param fileName 可选，生成的excel名称，如果没有，则默认是sheetName
48 */
49 public InputStream getDownloadExcel(){
50 final List list = (List)downExcelAttrsList.get(0);
51 final String sheetName = (String)downExcelAttrsList.get(1);
52 final String[] beanPropertyNames = (String[])downExcelAttrsList.get(2);
53 final String[] titleNames = (String[])downExcelAttrsList.get(3);
54 if(downExcelAttrsList.size()>=5) {
55 fileName = (String)downExcelAttrsList.get(4);
56 }else{
57 fileName = sheetName;
58 }
59 if(!fileName.contains(".xls")){
60 fileName = fileName + ".xls";
61 }
62 InputStream is = null;
63 try {
64 is = this.createExcelFile(list, sheetName, beanPropertyNames, titleNames);
65 } catch (Exception e1) {
66 e1.printStackTrace();
67 }
68 try {
69 fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
70 } catch (UnsupportedEncodingException e) {
71 e.printStackTrace();
72 }
73 if(null==is) System.out.print("shit...");
74 return is;
75 }
76
77 /**
78 * 生成Excel表
79 */
80 private InputStream createExcelFile(List valueList, String sheetName, String[] beanPropertyNames, String[] titleNames) throws Exception{
81 Workbook wb = new HSSFWorkbook();
82 Sheet sheet = wb.createSheet(sheetName);
83 //单元格默认宽度为20
84 sheet.setDefaultColumnWidth(20);
85 Cell cell;
86
87 //表头
88 Row headerRow = sheet.createRow(0);
89 headerRow.setHeightInPoints(18f);
90 for (int i = 0; i < titleNames.length; i++) {
91 cell = headerRow.createCell(i);
92 cell.setCellValue(titleNames[i]);
93 cell.setCellStyle(this.getHeaderCellStyle(wb));
94 }
95
96 //freeze the first row
97 sheet.createFreezePane(0, 1);
98
99 Row row;
100 int rownum = 1, listSize = valueList.size(), beanPropertyNamesLength = beanPropertyNames.length;
101 for (int i = 0; i < listSize; i++, rownum++) {
102 row = sheet.createRow(rownum);
103 Object currentObj = valueList.get(i);
104 for ( int j=0; j < beanPropertyNamesLength; j++ ) {
105 cell = row.createCell(j);
106 cell.setCellStyle(this.getContentCellStyle(wb));
107 Object value = this.getPropertyValue(currentObj, beanPropertyNames[j]);
108 this.getCellSetValue(cell, value);
109 }
110 }
111
112 //将输出流转化为输入流
113 ByteArrayOutputStream out = new ByteArrayOutputStream();
114 wb.write(out);
115 return new ByteArrayInputStream(out.toByteArray());
116 }
117
118 /**
119 * 设置单元格值
120 * @param cell
121 * @param value
122 */
123 private void getCellSetValue(Cell cell, Object value){
124 String type = value.getClass().toString().toLowerCase();
125 if(type.endsWith("integer")){
126 cell.setCellValue((Integer)value);
127 }else if(type.endsWith("double")){
128 cell.setCellValue((Double)value);
129 }else if(type.endsWith("timestamp")){
130 cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm").format(value).toString());
131 }else{
132 String val = (String)value;
133 Pattern pattern = Pattern.compile("<\w*\s*/?>");
134 Matcher matcher = pattern.matcher(val);
135 String v = matcher.replaceAll("");
136 //将结束符号替换为：。
137 pattern = Pattern.compile("</\w*\s*/?>");
138 matcher = pattern.matcher(v);
139 v = matcher.replaceAll("。");
140 cell.setCellValue(v);
141 }
142 }
143
144 /**
145 * 获得bean对象中对应属性的值
146 * @param obj
147 * @param propertyName
148 * @return
149 */
150 private Object getPropertyValue(Object obj,String beanPropertyName){
151 final String[] property = beanPropertyName.split(",");
152 final String[] beanNameAndPropertyName = property[0].split("\.");
153 final String beanName = beanNameAndPropertyName[0].toLowerCase();
154 final String propertyName = beanNameAndPropertyName[1];
155 Object value = "";
156 Method met = null;
157
158 //关联查询
159 if(obj.getClass().isArray()){
160 int objLength = Array.getLength(obj);
161 Object[] currentObjectArray = (Object[])obj;
162 for(int j=0;j<objLength;j++){
163 Object currentObject = currentObjectArray[j];
164 String currentObjectBeanName = currentObject.getClass().getSimpleName().toLowerCase();
165 if(currentObjectBeanName.equals(beanName)){
166 try {
167 met = currentObject.getClass().getMethod(this.getterMethodName(propertyName));
168 } catch (SecurityException e) {
169 e.printStackTrace();
170 } catch (NoSuchMethodException e) {
171 e.printStackTrace();
172 }
173 try {
174 value = met.invoke(currentObject);
175 } catch (IllegalArgumentException e) {
176 e.printStackTrace();
177 } catch (IllegalAccessException e) {
178 e.printStackTrace();
179 } catch (InvocationTargetException e) {
180 e.printStackTrace();
181 }
182 }
183 }
184 }else{
185 //属性的形式为： 对象.属性
186 if(beanNameAndPropertyName.length>1){
187 try {
188 met = obj.getClass().getMethod(this.getterMethodName(propertyName));
189 } catch (SecurityException e1) {
190 e1.printStackTrace();
191 } catch (NoSuchMethodException e1) {
192 e1.printStackTrace();
193 }
194 try {
195 value = met.invoke(obj);
196 } catch (IllegalArgumentException e) {
197 e.printStackTrace();
198 } catch (IllegalAccessException e) {
199 e.printStackTrace();
200 } catch (InvocationTargetException e) {
201 e.printStackTrace();
202 }
203 }else{
204 //属性的形式为： 属性
205 try {
206 met = obj.getClass().getMethod(this.getterMethodName(property[0]));
207 } catch (SecurityException e) {
208 e.printStackTrace();
209 } catch (NoSuchMethodException e) {
210 e.printStackTrace();
211 }
212 try {
213 value = met.invoke(obj);
214 } catch (IllegalArgumentException e) {
215 e.printStackTrace();
216 } catch (IllegalAccessException e) {
217 e.printStackTrace();
218 } catch (InvocationTargetException e) {
219 e.printStackTrace();
220 }
221 }
222 }
223
224 //状态值替换
225 if(property.length>1){
226 value = this.replaceValue(property, value);
227 }
228
229 return value;
230 }
231
232 /**
233 * 根据内容来替换对应的状态值
234 * @param propertyContent
235 * @param value
236 * @return
237 */
238 private Object replaceValue(String[] propertyContent, Object value){
239 int len = propertyContent.length;
240 String name = value.getClass().getSimpleName().toLowerCase();
241 for(int i=1;i<len;i++){
242 String[] statusValueAndReplaceValue = propertyContent[i].split(":");
243 if("integer".equals(name)&&Integer.parseInt(statusValueAndReplaceValue[0])==(Integer)value){
244 value = statusValueAndReplaceValue[1];
245 break;
246 }
247 }
248 return value;
249 }
250
251 /**
252 * 根据属性名字获得对应的bean对象的getter名字
253 * @param beanPropertyName bean对象的属性名字
254 * @return
255 */
256 private String getterMethodName(String beanPropertyName){
257 String name = "get"+beanPropertyName.substring(0, 1).toUpperCase()+beanPropertyName.substring(1);
258 return name;
259 }
260
261 /**
262 * 表头样式
263 * @param wb
264 * @return
265 */
266 private CellStyle getHeaderCellStyle(Workbook wb){
267 Font headerFont = wb.createFont();
268 headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
269 CellStyle style = createBorderedStyle(wb);
270 style.setAlignment(CellStyle.ALIGN_CENTER);
271 style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
272 style.setFillPattern(CellStyle.SOLID_FOREGROUND);
273 style.setFont(headerFont);
274 return style;
275 }
276
277 /**
278 * 单元格边框样式
279 * @param wb
280 * @return
281 */
282 private CellStyle createBorderedStyle(Workbook wb){
283 CellStyle style = wb.createCellStyle();
284 style.setBorderRight(CellStyle.BORDER_THIN);
285 style.setRightBorderColor(IndexedColors.BLACK.getIndex());
286 style.setBorderBottom(CellStyle.BORDER_THIN);
287 style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
288 style.setBorderLeft(CellStyle.BORDER_THIN);
289 style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
290 style.setBorderTop(CellStyle.BORDER_THIN);
291 style.setTopBorderColor(IndexedColors.BLACK.getIndex());
292 return style;
293 }
294
295 /**
296 * 内容部分单元格样式
297 * @param wb
298 * @return
299 */
300 private CellStyle getContentCellStyle(Workbook wb){
301 CellStyle style = createBorderedStyle(wb);
302 style.setAlignment(CellStyle.ALIGN_CENTER);
303 return style;
304 }
305
306 public List getDownExcelAttrsList() {
307 return downExcelAttrsList;
308 }
309
310 public void setDownExcelAttrsList(List downExcelAttrsList) {
311 this.downExcelAttrsList = downExcelAttrsList;
312 }
313
314 public String getFileName() {
315 return fileName;
316 }
317
318 public void setFileName(String fileName) {
319 this.fileName = fileName;
320 }
321 }
