Apache POI操作Excel导出方法说明

Apache的POI组件是Java操作Microsoft Office办公套件的强大API，其中对Word，Excel和PowperPoint都有支持，当然使用较多的还是Excel，因为Word和PowerPoint用程序动态操作的应用较少。本文主要介绍一下Excel的操作方法。

HSSF－提供读写Microsoft ExcelXLS格式档案的功能。XSSF－提供读写Microsoft ExcelOOXML XLSX格式档案的功能。HWPF－提供读写Microsoft WordDOC格式档案的功能。HSLF－提供读写MicrosoftPowerPoint格式档案的功能。HDGF－提供读Microsoft Visio格式档案的功能。HPBF－提供读MicrosoftPublisher格式档案的功能。HSMF－提供读Microsoft Outlook格式档案的功能。

EXCEL单元格样式设置：

// 创建字体 HSSFFont font = wb.createFont(); // 设置字体为红色 font.setColor(HSSFFont.COLOR_RED); // 设置字体为粗体 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 创建单元格格式 HSSFCellStyle cellStyle= wb.createCellStyle(); // 设置字体 cellStyle.setFont(font); // 设置水平居中 cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 设置垂直靠下 cellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // 设置左边框为双线 cellStyle.setBorderLeft(CellStyle.BORDER_DOUBLE); // 设置背景色为蓝色 cellStyle.setFillBackgroundColor(new HSSFColor.BLUE().getIndex()); // 设置前景色为黄色 cellStyle.setFillForegroundColor(new HSSFColor.YELLOW().getIndex());

Excel导出：

第一种，利用JAVA反射导出：

POIExcelUtil 类：

importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CreationHelper;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;publicclassPOIExcelUtil{publicstaticfinalStringFILE_EXTENSION_XLS="xls";publicstaticfinalStringFILE_EXTENSION_XLSX="xlsx";/****@paramMap*<String,String>maps属性表，成员属性age为KEY，中文名称为VALUE*@paramList*<T>list需要导出的数据列表对象*@paramFile*file指定输出文件位置，只能导出excel以上版本**@returntrue导出成功false导出失败*/publicstatic<T>booleanexcelExport(Map<String,String>maps,List<T>list,Filefile){try{Workbookwb=null;Stringfilename=file.getName();Stringtype=filename.substring(filename.lastIndexOf(".")+1).toLowerCase();if(type.equals(FILE_EXTENSION_XLS)){wb=newHSSFWorkbook();}if(type.equals(FILE_EXTENSION_XLSX)){wb=newXSSFWorkbook();}CreationHelpercreateHelper=wb.getCreationHelper();Sheetsheet=wb.createSheet("sheet1");Set<String>sets=maps.keySet();Rowrow=sheet.createRow(0);inti=0;//定义表头for(Iterator<String>it=sets.iterator();it.hasNext();){Stringkey=it.next();Cellcell=row.createCell(i++);cell.setCellValue(createHelper.createRichTextString(maps.get(key)));}//填充表单内容System.out.println("--------------------100%");floatavg=list.size()/20f;intcount=1;for(intj=0;j<list.size();j++){Tp=list.get(j);ClassclassType=p.getClass();intindex=0;Rowrow1=sheet.createRow(j+1);for(Iterator<String>it=sets.iterator();it.hasNext();){Stringkey=it.next();StringfirstLetter=key.substring(0,1).toUpperCase();//获得和属性对应的getXXX()方法的名字StringgetMethodName="get"+firstLetter+key.substring(1);//获得和属性对应的getXXX()方法MethodgetMethod=classType.getMethod(getMethodName,newClass[]{});//调用原对象的getXXX()方法Objectvalue=getMethod.invoke(p,newObject[]{});Cellcell=row1.createCell(index++);cell.setCellValue(value.toString());}if(j>avg*count){count++;System.out.print("I");}if(count==20){System.out.print("I100%");count++;}}FileOutputStreamfileOut=newFileOutputStream(file);wb.write(fileOut);fileOut.close();}catch(IOExceptione){e.printStackTrace();returnfalse;}catch(SecurityExceptione){e.printStackTrace();returnfalse;}catch(NoSuchMethodExceptione){e.printStackTrace();returnfalse;}catch(IllegalArgumentExceptione){e.printStackTrace();returnfalse;}catch(IllegalAccessExceptione){e.printStackTrace();returnfalse;}catch(InvocationTargetExceptione){e.printStackTrace();returnfalse;}returntrue;}}

第二种：利用JAVA反射和Annotation导出

POIExcelAnnotation 类：

@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.FIELD)public @interface POIExcelAnnotation {public String titleName();}

POIExcelUtil 类：

import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.CreationHelper;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class POIExcelUtil {public static final String FILE_EXTENSION_XLS = "xls";public static final String FILE_EXTENSION_XLSX = "xlsx";/*** * @param sheetName sheet名称* @param pojoClass POJO对象类* @param list 导出数据列表* @param file file 指定输出文件* @return true 导出成功 false 导出失败*/public static <T> boolean excelAnnotationExport(String sheetName ,Class<T> pojoClass,List<T> list, File file) {try {Workbook wb = null;String filename = file.getName();String type = filename.substring(filename.lastIndexOf(".")+1).toLowerCase();if (type.equals(FILE_EXTENSION_XLS)) {wb = new HSSFWorkbook();}if (type.equals(FILE_EXTENSION_XLSX)) {wb = new XSSFWorkbook();}CreationHelper createHelper = wb.getCreationHelper();Sheet sheet = wb.createSheet(sheetName);// 标题 List<String> fieldTitle = new ArrayList<String>(); //方法列表，对应表头List<Method> methodObj = new ArrayList<Method>(); // 得到所有字段 Field fileds[] = pojoClass.getDeclaredFields(); // 遍历整个filed for (int i = 0; i < fileds.length; i++) { Field field = fileds[i]; POIExcelAnnotation annotation = field.getAnnotation(POIExcelAnnotation.class); // 如果设置了annottion if (annotation != null) { // 添加到标题 fieldTitle.add(annotation.titleName()); // 添加到需要导出的字段的方法 String fieldName = field.getName(); String firstLetter = fieldName.substring(0, 1).toUpperCase();// 获得和属性对应的getXXX()方法的名String getMethodName = "get" + firstLetter+ fieldName.substring(1);// 获得和属性对应的getXXX()方法Method getMethod = pojoClass.getMethod(getMethodName,new Class[] {}); methodObj.add(getMethod); } }//设置表头粗体Font font = wb.createFont();font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);CellStyle style = wb.createCellStyle();style.setFont(font);//填充表头内容Row row = sheet.createRow(0);for(int i=0;i<fieldTitle.size();i++){String title = fieldTitle.get(i);Cell cell = row.createCell(i);cell.setCellStyle(style);cell.setCellValue(createHelper.createRichTextString(title));}// 填充表单内容System.out.println("--------------------100%");float avg = list.size() / 20f;int count = 1;for (int j = 0; j < list.size(); j++) {T p = list.get(j);Row row1 = sheet.createRow(j+1); for (int k=0;k<methodObj.size();k++) {Method getMethod = methodObj.get(k);Object value = getMethod.invoke(p, new Object[] {});Cell cell = row1.createCell(k);cell.setCellValue(value.toString());}if (j > avg * count) {count++;System.out.print("I");}if (count == 20) {System.out.println("I100%");count++;}}FileOutputStream fileOut = new FileOutputStream(file);wb.write(fileOut);fileOut.close();} catch (IOException e) {e.printStackTrace();return false;} catch (SecurityException e) {e.printStackTrace();return false;} catch (NoSuchMethodException e) {e.printStackTrace();return false;} catch (IllegalArgumentException e) {e.printStackTrace();return false;} catch (IllegalAccessException e) {e.printStackTrace();return false;} catch (InvocationTargetException e) {e.printStackTrace();return false;}return true;}}

调用方法截取代码：

public static void main(String args[]){ List<JavaBean> demo = ****;Map<String,String>maps=newLinkedHashMap<String,String>();maps.put("uid","帐号");maps.put("cn","姓名");maps.put("dept","部门");maps.put("mail","邮箱");Propertiesprops=System.getProperties();StringUSER_HOME=props.getProperty("user.home");Filefile=newFile(USER_HOME+"/Desktop/excelExport.xlsx");POIExcelUtil.excelExport(maps,demo , file);}

Excel导入：