1 package poi.excel;
2
3 import java.io.IOException;
4 import java.io.InputStream;
5 import java.io.OutputStream;
6 import java.lang.reflect.Field;
7 import java.lang.reflect.Method;
8 import java.util.ArrayList;
9 import java.util.List;
10
11 import javax.servlet.http.HttpServletRequest;
12
13 import org.apache.log4j.Logger;
14 import org.apache.poi.hssf.usermodel.HSSFCell;
15 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
16 import org.apache.poi.hssf.usermodel.HSSFFont;
17 import org.apache.poi.hssf.usermodel.HSSFRichTextString;
18 import org.apache.poi.hssf.usermodel.HSSFRow;
19 import org.apache.poi.hssf.usermodel.HSSFSheet;
20 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
21 import org.apache.poi.hssf.util.HSSFColor;
22 import org.apache.poi.ss.usermodel.Cell;
23 import org.apache.poi.ss.usermodel.Row;
24 import org.apache.poi.ss.usermodel.Sheet;
25 import org.apache.poi.ss.usermodel.Workbook;
26 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
27
28
29
30 /**
31 * @ClassName: ExcelUtil
32 * @Description: Excel导入导出工具类
33 * @author 周宣
34 * @date 2016-11-8 下午7:16:11
35 *
36 */
37 public class ExcelUtil {
38 private static final Logger logger = Logger.getLogger(ExcelUtil.class);
39
40 /**
41 * @Title: createWorkbook
42 * @Description: 判断excel文件后缀名，生成不同的workbook
43 * @param @param is
44 * @param @param excelFileName
45 * @param @return
46 * @param @throws IOException
47 * @return Workbook
48 * @throws
49 */
50 public Workbook createWorkbook(InputStream is,String excelFileName) throws IOException{
51 if (excelFileName.endsWith(".xls")) {
52 return new HSSFWorkbook(is);
53 }else if (excelFileName.endsWith(".xlsx")) {
54 return new XSSFWorkbook(is);
55 }
56 return null;
57 }
58
59 /**
60 * @Title: getSheet
61 * @Description: 根据sheet索引号获取对应的sheet
62 * @param @param workbook
63 * @param @param sheetIndex
64 * @param @return
65 * @return Sheet
66 * @throws
67 */
68 public Sheet getSheet(Workbook workbook,int sheetIndex){
69 return workbook.getSheetAt(0);
70 }
71
72 /**
73 * @Title: importDataFromExcel
74 * @Description: 将sheet中的数据保存到list中，
75 * 1、调用此方法时，vo的属性个数必须和excel文件每行数据的列数相同且一一对应，vo的所有属性都为String
76 * 2、在action调用此方法时，需声明
77 * private File excelFile;上传的文件
78 * private String excelFileName;原始文件的文件名
79 * 3、页面的file控件name需对应File的文件名
80 * @param @param vo javaBean
81 * @param @param is 输入流
82 * @param @param excelFileName
83 * @param @return
84 * @return List<Object>
85 * @throws
86 */
87 public List<Object> importDataFromExcel(Object vo,InputStream is,String excelFileName){
88 List<Object> list = new ArrayList<Object>();
89 try {
90 //创建工作簿
91 Workbook workbook = this.createWorkbook(is, excelFileName);
92 //创建工作表sheet
93 Sheet sheet = this.getSheet(workbook, 0);
94 //获取sheet中数据的行数
95 int rows = sheet.getPhysicalNumberOfRows();
96 //获取表头单元格个数
97 int cells = sheet.getRow(0).getPhysicalNumberOfCells();
98 //利用反射，给JavaBean的属性进行赋值
99 Field[] fields = vo.getClass().getDeclaredFields();
100 for (int i = 1; i < rows; i++) {//第一行为标题栏，从第二行开始取数据
101 Row row = sheet.getRow(i);
102 int index = 0;
103 while (index < cells) {
104 Cell cell = row.getCell(index);
105 if (null == cell) {
106 cell = row.createCell(index);
107 }
108 cell.setCellType(Cell.CELL_TYPE_STRING);
109 String value = null == cell.getStringCellValue()?"":cell.getStringCellValue();
110
111 Field field = fields[index];
112 String fieldName = field.getName();
113 String methodName = "set"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
114 Method setMethod = vo.getClass().getMethod(methodName, new Class[]{String.class});
115 setMethod.invoke(vo, new Object[]{value});
116 index++;
117 }
118 if (isHasValues(vo)) {//判断对象属性是否有值
119 list.add(vo);
120 vo.getClass().getConstructor(new Class[]{}).newInstance(new Object[]{});//重新创建一个vo对象
121 }
122
123 }
124 } catch (Exception e) {
125 logger.error(e);
126 }finally{
127 try {
128 is.close();//关闭流
129 } catch (Exception e2) {
130 logger.error(e2);
131 }
132 }
133 return list;
134
135 }
136
137 /**
138 * @Title: isHasValues
139 * @Description: 判断一个对象所有属性是否有值，如果一个属性有值(分空)，则返回true
140 * @param @param object
141 * @param @return
142 * @return boolean
143 * @throws
144 */
145 public boolean isHasValues(Object object){
146 Field[] fields = object.getClass().getDeclaredFields();
147 boolean flag = false;
148 for (int i = 0; i < fields.length; i++) {
149 String fieldName = fields[i].getName();
150 String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
151 Method getMethod;
152 try {
153 getMethod = object.getClass().getMethod(methodName);
154 Object obj = getMethod.invoke(object);
155 if (null != obj && "".equals(obj)) {
156 flag = true;
157 break;
158 }
159 } catch (Exception e) {
160 logger.error(e);
161 }
162
163 }
164 return flag;
165
166 }
167
168 public <T> void exportDataToExcel(List<T> list,String[] headers,String title,OutputStream os){
169 HSSFWorkbook workbook = new HSSFWorkbook();
170 //生成一个表格
171 HSSFSheet sheet = workbook.createSheet(title);
172 //设置表格默认列宽15个字节
173 sheet.setDefaultColumnWidth(15);
174 //生成一个样式
175 HSSFCellStyle style = this.getCellStyle(workbook);
176 //生成一个字体
177 HSSFFont font = this.getFont(workbook);
178 //把字体应用到当前样式
179 style.setFont(font);
180
181 //生成表格标题
182 HSSFRow row = sheet.createRow(0);
183 row.setHeight((short)300);
184 HSSFCell cell = null;
185
186 for (int i = 0; i < headers.length; i++) {
187 cell = row.createCell(i);
188 cell.setCellStyle(style);
189 HSSFRichTextString text = new HSSFRichTextString(headers[i]);
190 cell.setCellValue(text);
191 }
192
193 //将数据放入sheet中
194 for (int i = 0; i < list.size(); i++) {
195 row = sheet.createRow(i+1);
196 T t = list.get(i);
197 //利用反射，根据JavaBean属性的先后顺序，动态调用get方法得到属性的值
198 Field[] fields = t.getClass().getFields();
199 try {
200 for (int j = 0; j < fields.length; j++) {
201 cell = row.createCell(j);
202 Field field = fields[j];
203 String fieldName = field.getName();
204 String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
205 Method getMethod = t.getClass().getMethod(methodName,new Class[]{});
206 Object value = getMethod.invoke(t, new Object[]{});
207
208 if(null == value)
209 value ="";
210 cell.setCellValue(value.toString());
211
212 }
213 } catch (Exception e) {
214 logger.error(e);
215 }
216 }
217
218 try {
219 workbook.write(os);
220 } catch (Exception e) {
221 logger.error(e);
222 }finally{
223 try {
224 os.flush();
225 os.close();
226 } catch (IOException e) {
227 logger.error(e);
228 }
229 }
230
231 }
232
233 /**
234 * @Title: getCellStyle
235 * @Description: 获取单元格格式
236 * @param @param workbook
237 * @param @return
238 * @return HSSFCellStyle
239 * @throws
240 */
241 public HSSFCellStyle getCellStyle(HSSFWorkbook workbook){
242 HSSFCellStyle style = workbook.createCellStyle();
243 style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
244 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
245 style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
246 style.setBorderTop(HSSFCellStyle.BORDER_THIN);
247 style.setLeftBorderColor(HSSFCellStyle.BORDER_THIN);
248 style.setRightBorderColor(HSSFCellStyle.BORDER_THIN);
249 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
250
251 return style;
252 }
253
254 /**
255 * @Title: getFont
256 * @Description: 生成字体样式
257 * @param @param workbook
258 * @param @return
259 * @return HSSFFont
260 * @throws
261 */
262 public HSSFFont getFont(HSSFWorkbook workbook){
263 HSSFFont font = workbook.createFont();
264 font.setColor(HSSFColor.WHITE.index);
265 font.setFontHeightInPoints((short)12);
266 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
267 return font;
268 }
269
270 public boolean isIE(HttpServletRequest request){
271 return request.getHeader("USER-AGENT").toLowerCase().indexOf("msie")>0?true:false;
272 }
273 }
浙公网安备 33010602011771号