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 = http://www.mamicode.com/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 = http://www.mamicode.com/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 }