poi3.9 操作excel 2007

原文：http://www.cnblogs.com/yangzhilong/p/4052063.html
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

/**
 * 基于XSSF and SAX (Event API)
 * 读取excel的第一个Sheet的内容
 * @author yzl
 *
 */
public class ReadExcelUtils {
    private int headCount = 0;
    private List<List<String>> list = new ArrayList<List<String>>();
    private static final Log log = LogFactory.getLog(ReadExcelUtils.class);
    
    /**
     * 通过文件流构建DOM进行解析
     * @param ins
     * @param headRowCount   跳过读取的表头的行数
     * @return
     * @throws InvalidFormatException
     * @throws IOException
     */
    public  List<List<String>> processDOMReadSheet(InputStream ins,int headRowCount) throws InvalidFormatException, IOException {
        Workbook workbook = WorkbookFactory.create(ins);
        return this.processDOMRead(workbook, headRowCount);
    }
    
    /**
     * 采用DOM的形式进行解析
     * @param filename
     * @param headRowCount   跳过读取的表头的行数
     * @return
     * @throws IOException 
     * @throws InvalidFormatException 
     * @throws Exception
     */
    public  List<List<String>> processDOMReadSheet(String filename,int headRowCount) throws InvalidFormatException, IOException {
        Workbook workbook = WorkbookFactory.create(new File(filename));
        return this.processDOMRead(workbook, headRowCount);
    }

    /**
     * 采用SAX进行解析
     * @param filename
     * @param headRowCount
     * @return
     * @throws OpenXML4JException 
     * @throws IOException 
     * @throws SAXException 
     * @throws Exception
     */
    public List<List<String>> processSAXReadSheet(String filename,int headRowCount) throws IOException, OpenXML4JException, SAXException   {
        headCount = headRowCount;
        
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader( pkg );
        SharedStringsTable sst = r.getSharedStringsTable();
        XMLReader parser = fetchSheetParser(sst);

        Iterator<InputStream> sheets = r.getSheetsData();
        InputStream sheet = sheets.next();
        InputSource sheetSource = new InputSource(sheet);
        parser.parse(sheetSource);
        sheet.close();
        
        log.debug("时间:"+DateUtils.getNowTime()+",共读取了execl的记录数为 :"+list.size());
        
        return list;
    }

    private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
        XMLReader parser =
            XMLReaderFactory.createXMLReader(
                    "org.apache.xerces.parsers.SAXParser"
            );
        ContentHandler handler = new SheetHandler(sst);
        parser.setContentHandler(handler);
        return parser;
    }

    /** 
     * SAX 解析excel
     */
    private class SheetHandler extends DefaultHandler {
        private SharedStringsTable sst;
        private String lastContents;
        private boolean nextIsString;
        private boolean isNullCell;
        //读取行的索引
        private int rowIndex = 0;
        //是否重新开始了一行
        private boolean curRow = false;
        private List<String> rowContent;
        
        private SheetHandler(SharedStringsTable sst) {
            this.sst = sst;
        }
        
        public void startElement(String uri, String localName, String name,
                Attributes attributes) throws SAXException {
            //节点的类型
            //System.out.println("---------begin:" + name);
            if(name.equals("row")){
                rowIndex++;
            }
            //表头的行直接跳过
            if(rowIndex > headCount){
                curRow = true;
                // c => cell
                if(name.equals("c")) {
                    String cellType = attributes.getValue("t");
                    if(null == cellType){
                        isNullCell = true;
                    }else{
                        if(cellType.equals("s")) {
                            nextIsString = true;
                        } else {
                            nextIsString = false;
                        }
                        isNullCell = false;
                    }
                }
                // Clear contents cache
                lastContents = "";
            }
        }
        
        public void endElement(String uri, String localName, String name)
                throws SAXException {
            //System.out.println("-------end："+name);
            if(rowIndex > headCount){
                if(nextIsString) {
                    int idx = Integer.parseInt(lastContents);
                    lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
                    nextIsString = false;
                }
                if(name.equals("v")) {
                    //System.out.println(lastContents);
                    if(curRow){
                        //是新行则new一行的对象来保存一行的值
                        if(null==rowContent){
                            rowContent = new ArrayList<String>();
                        }
                        rowContent.add(lastContents);
                    }
                }else if(name.equals("c") && isNullCell){
                    if(curRow){
                        //是新行则new一行的对象来保存一行的值
                        if(null==rowContent){
                            rowContent = new ArrayList<String>();
                        }
                        rowContent.add(null);
                    }
                }
                
                isNullCell = false;

                if("row".equals(name)){
                    list.add(rowContent);
                    curRow = false;
                    rowContent = null;
                }
            }
            
        }

        public void characters(char[] ch, int start, int length)
                throws SAXException {
            lastContents += new String(ch, start, length);
        }
    }
    
    /**
     * DOM的形式解析execl
     * @param workbook
     * @param headRowCount
     * @return
     * @throws InvalidFormatException
     * @throws IOException
     */
    private List<List<String>> processDOMRead(Workbook workbook,int headRowCount) throws InvalidFormatException, IOException {
        headCount = headRowCount;
        
        Sheet sheet = workbook.getSheetAt(0);
        //行数
        int endRowIndex = sheet.getLastRowNum();
        
        Row row = null;
        List<String> rowList = null;
        
        for(int i=headCount; i<=endRowIndex; i++){
            rowList = new ArrayList<String>();
            row = sheet.getRow(i);
            for(int j=0; j<row.getLastCellNum();j++){
                if(null==row.getCell(j)){
                    rowList.add(null);
                    continue;
                }
                int dataType = row.getCell(j).getCellType();
                if(dataType == Cell.CELL_TYPE_NUMERIC){
                    DecimalFormat df = new DecimalFormat("0.####################");  
                    rowList.add(df.format(row.getCell(j).getNumericCellValue()));
                }else if(dataType == Cell.CELL_TYPE_BLANK){
                    rowList.add(null);
                }else if(dataType == Cell.CELL_TYPE_ERROR){
                    rowList.add(null);
                }else{
                    //这里的去空格根据自己的情况判断
                    String valString = row.getCell(j).getStringCellValue();
                    Pattern p = Pattern.compile("\\s*|\t|\r|\n");
                    Matcher m = p.matcher(valString);
                    valString = m.replaceAll("");
                    //去掉狗日的不知道是啥东西的空格
                    if(valString.indexOf(" ")!=-1){
                        valString = valString.substring(0, valString.indexOf(" "));
                    }
                    
                    rowList.add(valString);
                }
            }
            
            list.add(rowList);
        }
        log.debug("时间:"+DateUtils.getNowTime()+",共读取了execl的记录数为 :"+list.size());
        
        return list;
    }
    
    @SuppressWarnings("unused")
    public static void main(String[] args) throws Exception {
        ReadExcelUtils howto = new ReadExcelUtils();
        String fileName = "f:/test.xlsx";
        List<List<String>> list = howto.processSAXReadSheet(fileName,2);
        
        ReadExcelUtils h = new ReadExcelUtils();
        String fileName1 = "f:/test.xls";
        List<List<String>> result = h.processDOMReadSheet(fileName1,2);
    }
}

 导出

public HSSFWorkbook getCaseReport(Map<String,Object> map) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int sheetNum = 1;
		HSSFWorkbook book = (HSSFWorkbook)map.get("book");
		String createTimeBegin = (String)map.get("createTimeBegin");
		String createTimeEnd = (String)map.get("createTimeEnd");

		String updateTimeBegin = (String)map.get("updateTimeBegin");
		String updateTimeEnd = (String)map.get("updateTimeEnd");
		Case cas = (Case)map.get("cas");
		if(cas == null)
			cas = new Case();
		cas.setCreateTimeBegin(createTimeBegin);
		cas.setCreateTimeEnd(createTimeEnd);
		cas.setUpdateTimeBegin(updateTimeBegin);
		cas.setUpdateTimeEnd(updateTimeEnd);
		try {
			// 产生工作表对象
			HSSFSheet sheet = book.createSheet(sheetNum+"");
//			String sql = utilService.getCodeDesc("sql","case_report");
			StringBuffer sql = new StringBuffer("select * from hpcms_case where 1=1");
			HSSFCellStyle cellStyle = book.createCellStyle();//设置背景色的代码
			cellStyle.setFillBackgroundColor(HSSFColor.YELLOW.index);//再将想要设置背景色的单元格;
			cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			
			boolean first = true;
			conn = jdbcTemplate.getDataSource().getConnection();
			
			if(cas != null){
				if(StringUtils.isNotBlank(cas.getNumberId())){
					sql.append(" and (number_Id = '"+cas.getNumberId()+"' or opt_Case_Id =  '"+cas.getNumberId()+"')");
				}
				if(StringUtils.isNotBlank(cas.getEmail())){
					sql.append(" and email = '" +cas.getEmail() +"'");
				}
				if(StringUtils.isNotBlank(cas.getOwnerName())){
					sql.append(" and owner = (select id from hpcms_user where user_name ='"+cas.getOwnerName()+"')");
				}
				if(StringUtils.isNotBlank(cas.getSn())){
					sql.append(" and sn like '%"+cas.getSn()+"%'");
				}
				if(StringUtils.isNotBlank(cas.getCreateTimeBegin())){
					sql.append(" and to_char(create_date, 'yyyy-mm-dd') >= '"+cas.getCreateTimeBegin()+"'");
				}
				if(StringUtils.isNotBlank(cas.getCreateTimeEnd())){
					sql.append(" and to_char(create_date, 'yyyy-mm-dd') <= '"+cas.getCreateTimeEnd()+"'");
				}
				if(StringUtils.isNotBlank(cas.getUpdateTimeBegin())){
					sql.append(" and to_char(update_Date, 'yyyy-mm-dd') >= '"+cas.getUpdateTimeBegin()+"'  and status = 'Closed'");
				}
				if(StringUtils.isNotBlank(cas.getUpdateTimeEnd())){
					sql.append("  and to_char(update_Date, 'yyyy-mm-dd') <= '"+cas.getUpdateTimeEnd()+"'  and status = 'Closed'");
				}
				if(StringUtils.isNotBlank(cas.getCreatedName())){
					sql.append(" and created = (select id from hpcms_user where user_name ='"+cas.getCreatedName()+"')");
				}
				if(StringUtils.isNotBlank(cas.getStatus())){
					sql.append(" and status = '"+cas.getStatus()+"'");
				}
				if(StringUtils.isNotBlank(cas.getPhone1())){
					sql.append(" and (phone1 like '%"+cas.getPhone1()+"%' or phone2 like '%"+cas.getPhone1()+"%' or phone3 like '%"+cas.getPhone1()+"%')");
				}
				sql.append("and rownum < 65500"); 
			}
			log.info("sql > " + sql.toString());
//			log.info("beginDate > " + beginDate + " endDate" + endDate);
			pstmt = conn.prepareStatement(sql.toString());
//			pstmt.setString(1, beginDate);
//			pstmt.setString(2, endDate);
			rs = pstmt.executeQuery();
			int rowNum = 0;
			while(rs.next()){
				if(rowNum%Common.EXCEL03_MAXROW == 0){
					sheetNum++;
					sheet = book.createSheet(sheetNum+"");
				}
				
				HSSFRow row = sheet.createRow(rowNum++);// 创建一行
				HSSFRow row2 = null;
				if(first)
					row2 = sheet.createRow(rowNum++);// 创建一行
				ResultSetMetaData data = rs.getMetaData(); 
				for(int i = 2 ; i<= data.getColumnCount() ; i++){
					String columnName = data.getColumnName(i);
					String columnValue = rs.getString(i);
//					if("ID".equals(columnName))
//						continue;
					if("OWNER,CREATED,UPDATED".contains(columnName)){
						if(StringUtils.isNotBlank(columnValue)){
							User u = userMapper.getUserById(columnValue);
							if(u != null)
								columnValue = u.getUserName();
						}
					}
					if("EMAIL".equals(columnName)){
						if(StringUtils.isBlank(columnValue)){
							columnValue = "N/A";
						}
					}
					
					HSSFCell cell = row.createCell(i-2);// 创建一列
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					if(first){
						cell.setCellValue(columnName);
						cell.setCellStyle(cellStyle);
						
						HSSFCell cell2 = row2.createCell(i-2);// 创建一列
						cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell2.setCellValue(columnValue);
					}else{
						cell.setCellValue(columnValue);
					}
				}
				first = false;
					
			}; 
			
			
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally{
			if(rs != null)
				rs.close();
			if(pstmt != null)
				pstmt.close();
			if(conn != null)
				conn.close();
		}
		
		return book;
	}
	

 fOut = response.getOutputStream();

book.write(fOut);

fOut.flush();

 

book.dispose();  //删除硬盘上生成的临时文件