首先我们要知道MS excel文件由什么组成，比如说sheet（一个工作表格），表格的行，单元格，单元格格式，单元格内容格式…这些都对应着poi里面的一个类。

一个excel表格：HSSFWorkbook wb = new HSSFWorkbook();

或者  XSSFWorkbook wb = new XSSFWorkbook();

一个工作表格：HSSFSheet sheet = wb.createSheet("表格");

一行：HSSFRow row = sheet.createRow(0);

一个单元格（cell）HSSFCell cell = row.createCell((short)0)

单元格格式（cellstyle）：HSSFCellStyle style = wb.createCellStyle()

单元格内容格式：HSSFDataFormat format= wb.createDataFormat();

读写xls和xlsx不同格式的excel文档时，HSSFWorkbook针对.xls，XSSFWorkbook针对.xlsx的格式。

PS：xlsx是Microsoft Office EXCEL 2007文档的扩展名。xlsx是Office2007使用的，是用新的基于XML的压缩文件格式取代了其目前专有的默认文件格式，在传统的文件名扩展名后面添加了字母x（即：docx取代doc、.xlsx取代xls等等），使其占用空间更小。

一、导入Excel表格

导入.xls格式的excel表格，读取、筛选其中的内容。将正确的内容存入系统中，提示用户错误导入的数目，并提供用户下载导入失败的条目。

@RequiresPermissions("repair:repairItem:edit")	@RequestMapping(value = "importExcel")	public String repairItemInfoInport(MultipartFile repairItemExcel, HttpServletRequest request,RedirectAttributes redirectAttributes,Model model) throws InvalidFormatException, IOException {		CommonsMultipartResolver resolver = new CommonsMultipartResolver(request.getSession().getServletContext());		User user =UserUtils.getUser();				Community community = user.getCommunity();    			String communityId = community.getId();		Map
    
      result = new HashMap
     
      ();		List
      
       
      
     
    

操作Excel表格的功能类  ExcelReader.java 

import org.apache.poi.hssf.usermodel.*;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import java.io.IOException;import java.io.InputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;/** * 操作Excel表格的功能类 */public class ExcelReader {    private POIFSFileSystem fs;    private HSSFWorkbook wb;    private HSSFSheet sheet;    private HSSFRow row;    /**     * 读取Excel表格表头的内容     * @param     * @return String 表头内容的数组     */    public String[] readExcelTitle(InputStream is) {        try {            fs = new POIFSFileSystem(is);            //is.close();            wb = new HSSFWorkbook(fs);        } catch (IOException e) {            e.printStackTrace();        }        sheet = wb.getSheetAt(0);        row = sheet.getRow(0);        // 标题总列数        int colNum = row.getPhysicalNumberOfCells();        /*System.out.println("colNum:" + colNum);*/        String[] title = new String[colNum];        int i;        for ( i = 0; i < colNum; i++) {            //title[i] = getStringCellValue(row.getCell((short) i));            title[i] = getCellFormatValue(row.getCell((short) i));        }        return title;    }    /**     * 读取Excel数据内容     * @param     * @return Map 包含单元格数据内容的Map对象     */    /*public List
    
      readExcelContent(InputStream is) {        List
     
       content = new ArrayList<>();        String str = "";        try {            fs = new POIFSFileSystem(is);            wb = new HSSFWorkbook(fs);        } catch (IOException e) {            e.printStackTrace();        }        sheet = wb.getSheetAt(0);        // 得到总行数        int rowNum = sheet.getLastRowNum();        row = sheet.getRow(0);        int colNum = row.getPhysicalNumberOfCells();        // 正文内容应该从第二行开始,第一行为表头的标题        for (int i = 1; i <= rowNum; i++) {            String[] temp=new String[colNum];            row = sheet.getRow(i);            int j = 0;            while (j < colNum) {                // 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据                // 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean                // str += getStringCellValue(row.getCell((short) j)).trim() +                // "-";                temp[j]=getCellFormatValue(row.getCell( j)).trim();//                temp[j]=str;                j++;            }            content.add(temp);            str = "";        }        return content;    }*/    /**     * 读取Excel数据内容     * @param     * @return  List
      
       
      
     
    

导入Excel文件（支持“XLS”和“XLSX”格式）：  ImportExcel.java 

import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.util.Collections;import java.util.Comparator;import java.util.Date;import java.util.List;import org.apache.commons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;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;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.web.multipart.MultipartFile;import com.google.common.collect.Lists;import com.qwkj.zxl.common.utils.Reflections;import com.qwkj.zxl.common.utils.excel.annotation.ExcelField;import com.qwkj.zxl.modules.sys.utils.DictUtils;/** * 导入Excel文件（支持“XLS”和“XLSX”格式） * @author ThinkGem * @version 2013-03-10 */public class ImportExcel {		private static Logger log = LoggerFactory.getLogger(ImportExcel.class);				/**	 * 工作薄对象	 */	private Workbook wb;		/**	 * 工作表对象	 */	private Sheet sheet;		/**	 * 标题行号	 */	private int headerNum;		/**	 * 构造函数	 * @param path 导入文件，读取第一个工作表	 * @param headerNum 标题行号，数据行号=标题行号+1	 * @throws InvalidFormatException 	 * @throws IOException 	 */	public ImportExcel(String fileName, int headerNum) 			throws InvalidFormatException, IOException {		this(new File(fileName), headerNum);	}		/**	 * 构造函数	 * @param path 导入文件对象，读取第一个工作表	 * @param headerNum 标题行号，数据行号=标题行号+1	 * @throws InvalidFormatException 	 * @throws IOException 	 */	public ImportExcel(File file, int headerNum) 			throws InvalidFormatException, IOException {		this(file, headerNum, 0);	}	/**	 * 构造函数	 * @param path 导入文件	 * @param headerNum 标题行号，数据行号=标题行号+1	 * @param sheetIndex 工作表编号	 * @throws InvalidFormatException 	 * @throws IOException 	 */	public ImportExcel(String fileName, int headerNum, int sheetIndex) 			throws InvalidFormatException, IOException {		this(new File(fileName), headerNum, sheetIndex);	}		/**	 * 构造函数	 * @param path 导入文件对象	 * @param headerNum 标题行号，数据行号=标题行号+1	 * @param sheetIndex 工作表编号	 * @throws InvalidFormatException 	 * @throws IOException 	 */	public ImportExcel(File file, int headerNum, int sheetIndex) 			throws InvalidFormatException, IOException {		this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);	}		/**	 * 构造函数	 * @param file 导入文件对象	 * @param headerNum 标题行号，数据行号=标题行号+1	 * @param sheetIndex 工作表编号	 * @throws InvalidFormatException 	 * @throws IOException 	 */	public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex) 			throws InvalidFormatException, IOException {		this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);	}	/**	 * 构造函数	 * @param path 导入文件对象	 * @param headerNum 标题行号，数据行号=标题行号+1	 * @param sheetIndex 工作表编号	 * @throws InvalidFormatException 	 * @throws IOException 	 */	public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) 			throws InvalidFormatException, IOException {		if (StringUtils.isBlank(fileName)){			throw new RuntimeException("导入文档为空!");		}else if(fileName.toLowerCase().endsWith("xls")){    			this.wb = new HSSFWorkbook(is);            }else if(fileName.toLowerCase().endsWith("xlsx")){          	this.wb = new XSSFWorkbook(is);        }else{          	throw new RuntimeException("文档格式不正确!");        }  		if (this.wb.getNumberOfSheets()
    
      List
     
       getDataList(Class
      
        cls, int... groups) throws InstantiationException, IllegalAccessException{		List
       
         annotationList = Lists.newArrayList();		// Get annotation field 		Field[] fs = cls.getDeclaredFields();		for (Field f : fs){			ExcelField ef = f.getAnnotation(ExcelField.class);			if (ef != null && (ef.type()==0 || ef.type()==2)){				if (groups!=null && groups.length>0){					boolean inGroup = false;					for (int g : groups){						if (inGroup){							break;						}						for (int efg : ef.groups()){							if (g == efg){								inGroup = true;								annotationList.add(new Object[]{ef, f});								break;							}						}					}				}else{					annotationList.add(new Object[]{ef, f});				}			}		}		// Get annotation method		Method[] ms = cls.getDeclaredMethods();		for (Method m : ms){			ExcelField ef = m.getAnnotation(ExcelField.class);			if (ef != null && (ef.type()==0 || ef.type()==2)){				if (groups!=null && groups.length>0){					boolean inGroup = false;					for (int g : groups){						if (inGroup){							break;						}						for (int efg : ef.groups()){							if (g == efg){								inGroup = true;								annotationList.add(new Object[]{ef, m});								break;							}						}					}				}else{					annotationList.add(new Object[]{ef, m});				}			}		}		// Field sorting		Collections.sort(annotationList, new Comparator
        
         () {			public int compare(Object[] o1, Object[] o2) {				return new Integer(((ExcelField)o1[0]).sort()).compareTo(						new Integer(((ExcelField)o2[0]).sort()));			};		});		//log.debug("Import column count:"+annotationList.size());		// Get excel data		List
         
           dataList = Lists.newArrayList(); for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) { E e = (E)cls.newInstance(); int column = 0; Row row = this.getRow(i); StringBuilder sb = new StringBuilder(); for (Object[] os : annotationList){ Object val = this.getCellValue(row, column++); if (val != null){ ExcelField ef = (ExcelField)os[0]; // If is dict type, get dict value if (StringUtils.isNotBlank(ef.dictType())){ val = DictUtils.getDictValue(val.toString(), ef.dictType(), ""); //log.debug("Dictionary type value: ["+i+","+colunm+"] " + val); } // Get param type and type cast Class
           valType = Class.class; if (os[1] instanceof Field){ valType = ((Field)os[1]).getType(); }else if (os[1] instanceof Method){ Method method = ((Method)os[1]); if ("get".equals(method.getName().substring(0, 3))){ valType = method.getReturnType(); }else if("set".equals(method.getName().substring(0, 3))){ valType = ((Method)os[1]).getParameterTypes()[0]; } } //log.debug("Import value type: ["+i+","+column+"] " + valType); try { if (valType == String.class){ String s = String.valueOf(val.toString()); if(StringUtils.endsWith(s, ".0")){ val = StringUtils.substringBefore(s, ".0"); }else{ val = String.valueOf(val.toString()); } }else if (valType == Integer.class){ val = Double.valueOf(val.toString()).intValue(); }else if (valType == Long.class){ val = Double.valueOf(val.toString()).longValue(); }else if (valType == Double.class){ val = Double.valueOf(val.toString()); }else if (valType == Float.class){ val = Float.valueOf(val.toString()); }else if (valType == Date.class){ val = DateUtil.getJavaDate((Double)val); }else{ if (ef.fieldType() != Class.class){ val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString()); }else{ val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString()); } } } catch (Exception ex) { log.info("Get cell value ["+i+","+column+"] error: " + ex.toString()); val = null; } // set entity value if (os[1] instanceof Field){ Reflections.invokeSetter(e, ((Field)os[1]).getName(), val); }else if (os[1] instanceof Method){ String mthodName = ((Method)os[1]).getName(); if ("get".equals(mthodName.substring(0, 3))){ mthodName = "set"+StringUtils.substringAfter(mthodName, "get"); } Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val}); } } sb.append(val+", "); } dataList.add(e); log.debug("Read success: ["+i+"] "+sb.toString()); } return dataList; }// /**// * 导入测试// */// public static void main(String[] args) throws Throwable {// // ImportExcel ei = new ImportExcel("target/export.xlsx", 1);// // for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) {// Row row = ei.getRow(i);// for (int j = 0; j < ei.getLastCellNum(); j++) {// Object val = ei.getCellValue(row, j);// System.out.print(val+", ");// }// System.out.print("\n");// }// // }}
         
        
       
      
     
    

二、导出项目中存在的Excel模板

@RequiresPermissions("repair:repairItem:edit")@RequestMapping(value = "exportExcel")public void repairItemExportTemplateDown(HttpServletResponse response,HttpSession session){	try {		//读写xls和xlsx格式时，HSSFWorkbook针对xls，XSSFWorkbook针对xlsx		HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(session.getServletContext().getRealPath("/") + "excel\\导入模板.xls"));		//response.setHeader("Content-disposition", "filename= repairItem.xls");		//repairItemService.downRepairItemMould(header, response.getOutputStream());		response.setHeader("Content-Disposition", "attachment;filename=" + new String("导入模板.xls".getBytes(), "ISO-8859-1"));		wb.write(response.getOutputStream());		} catch (IOException e) {			e.printStackTrace();		}	}

三、系统页面中的列表内容以excel表格的形式导出

@RequestMapping("exportPayBillRecord")    public void exportPayBillRecord(PayBillrecord payBillrecord, HttpServletResponse response) {        List
    
      PayBillrecordLists = payBillrecordService.findList(payBillrecord);        List
     
      
     
    

/*** 写出普通的excel** @param response HttpServletResponse* @param fileName 写出文件的文件名,若没有添加文件类型后缀，则默认添加".xls"* @param headers  标题行内容* @param data     数据行内容（需要和标题行顺序一样，只支持各种普通类型）*/public static void writeExcel(HttpServletResponse response, String fileName, String[] headers, List
    
     
    

导出的实际效果图如下：

还封装了一下写出Excel的工具类：  WriteExcelUtil.java

package com.qwkj.zxl.common.utils.excel;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.hssf.util.CellRangeAddressList;import org.apache.poi.hssf.util.HSSFColor;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import java.util.Map;/** * 2016/3/9 * 写出Excel工具类 */public class WriteExcelUtil {    /**     * 写出带有选择框限制的excel     * @param response HttpServletResponse     * @param fileName 写出文件的文件名,若没有添加文件类型后缀，则默认添加".xls"     * @param headers  标题行内容     * @param data  数据行内容（需要和标题行顺序一样，只支持各种普通类型）     * @param maps  需要做选择框限制的列的集合  说明：1.map中key为"titleContent"中需存入，需要做选择框的列顶部标题内容  2.map中key为"data"中需存入，所能选择的数据集合     * @throws Exception     */    public static void writeExcelHaveSelectBox(HttpServletResponse response, String fileName, String[] headers, List