|
1
16
17 package info.jtrac.util;
18
19 import info.jtrac.domain.AbstractItem;
20 import info.jtrac.domain.Field;
21 import info.jtrac.domain.History;
22 import info.jtrac.domain.ItemSearch;
23 import java.util.Date  ;
24 import java.util.List ;
25 import java.util.Locale ;
26 import org.apache.poi.hssf.usermodel.HSSFCell;
27 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
28 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
29 import org.apache.poi.hssf.usermodel.HSSFFont;
30 import org.apache.poi.hssf.usermodel.HSSFRow;
31 import org.apache.poi.hssf.usermodel.HSSFSheet;
32 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
33 import org.springframework.context.MessageSource;
34
35
38 public class ExcelUtils {
39
40 private HSSFSheet sheet;
41 private List <AbstractItem> items;
42 private ItemSearch itemSearch;
43 private HSSFCellStyle csBold;
44 private HSSFCellStyle csDate;
45 private HSSFWorkbook wb;
46
47 public ExcelUtils(List items, ItemSearch itemSearch) {
48 this.wb = new HSSFWorkbook();
49 this.sheet = wb.createSheet("jtrac");
50 this.sheet.setDefaultColumnWidth((short) 12);
51
52 HSSFFont fBold = wb.createFont();
53 fBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
54 this.csBold = wb.createCellStyle();
55 this.csBold.setFont(fBold);
56
57 this.csDate = wb.createCellStyle();
58 this.csDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
59
60 this.items = items;
61 this.itemSearch = itemSearch;
62 }
63
64 private static String fmt(String key, MessageSource messageSource, Locale locale) {
65 try {
66 return messageSource.getMessage("item_list." + key, null, locale);
67 } catch (Exception e) {
68 return "???item_list." + key + "???";
69 }
70 }
71
72 private HSSFCell getCell(int row, int col) {
73 HSSFRow sheetRow = sheet.getRow(row);
74 if (sheetRow == null) {
75 sheetRow = sheet.createRow(row);
76 }
77 HSSFCell cell = sheetRow.getCell((short) col);
78 if (cell == null) {
79 cell = sheetRow.createCell((short) col);
80 }
81 return cell;
82 }
83
84 private void setHeader(int row, int col, String text) {
85 HSSFCell cell = getCell(row, col);
86 cell.setCellStyle(csBold);
87 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
88 cell.setCellValue(text);
89 }
90
91 private void setText(int row, int col, String text) {
92 HSSFCell cell = getCell(row, col);
93 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
94 cell.setCellValue(text);
95 }
96
97 private void setDate(int row, int col, Date date) {
98 if (date == null) {
99 return;
100 }
101 HSSFCell cell = getCell(row, col);
102 cell.setCellValue(date);
103 cell.setCellStyle(csDate);
104 }
105
106 private void setDouble(int row, int col, Double value) {
107 if (value == null) {
108 return;
109 }
110 HSSFCell cell = getCell(row, col);
111 cell.setCellValue(value);
112 }
113
114 public HSSFWorkbook exportToExcel(MessageSource ms, Locale loc) {
115
116 boolean showDetail = itemSearch.isShowDetail();
117 boolean showHistory = itemSearch.isShowHistory();
118 List <Field> fields = itemSearch.getFields();
119
120 int row = 0;
121 int col = 0;
122
123 setHeader(row, col++, fmt("id", ms, loc));
125 setHeader(row, col++, fmt("summary", ms, loc));
126
127 if (showDetail) {
128 setHeader(row, col++, fmt("detail", ms, loc));
129 }
130
131 setHeader(row, col++, fmt("loggedBy", ms, loc));
132 setHeader(row, col++, fmt("status", ms, loc));
133 setHeader(row, col++, fmt("assignedTo", ms, loc));
134
135 for(Field field : fields) {
136 setHeader(row, col++, field.getLabel());
137 }
138
139 setHeader(row, col++, fmt("timeStamp", ms, loc));
140
141 for(AbstractItem item : items) {
143 row++; col = 0;
144 setText(row, col++, item.getRefId());
146 setText(row, col++, item.getSummary());
147
148 if (showDetail) {
149 if (showHistory) {
150 History h = (History) item;
151 setText(row, col++, h.getComment());
152 } else {
153 setText(row, col++, item.getDetail());
154 }
155 }
156
157 setText(row, col++, item.getLoggedBy().getName());
158 setText(row, col++, item.getStatusValue());
159 setText(row, col++, ( item.getAssignedTo() == null ? "" : item.getAssignedTo().getName() ));
160
161 for(Field field : fields) {
162 if (field.getName().getType() == 4) { setDouble(row, col++, (Double ) item.getValue(field.getName()));
164 } else if (field.getName().getType() == 6) { setDate(row, col++, (Date ) item.getValue(field.getName()));
166 } else {
167 setText(row, col++, item.getCustomValue(field.getName()));
168 }
169 }
170
171 setDate(row, col++, item.getTimeStamp());
172 }
173 return wb;
174 }
175
176 }
177 |
Popular Tags |
Java API By Example, From Geeks To Geeks.