java解析xlsx文档

package nc.ui.gl.budget.excelutils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.swing.filechooser.FileSystemView;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFWorkbook;

public class Fileutils {
	private Workbook wb;
    private Sheet sheet;
    private Row row;
    //传入文档的路径即可
    public Fileutils(String filepath) {
        if(filepath==null){
            return;
        }
        String ext = filepath.substring(filepath.lastIndexOf("."));
        try {
            InputStream is = new FileInputStream(filepath);
            //判断传入文档格式
            if(".xls".equals(ext)){
                wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(ext)){
                wb = new XSSFWorkbook(is);
            }else{
                wb=null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public List<Map<String, Object>> readExcelContent() throws Exception {
		ArrayList<Map<String, Object>> ret = new ArrayList<Map<String, Object>>();
		try {
			if(wb==null){
	            throw new Exception("Workbook对象为空！");
	        }
			sheet = wb.getSheetAt(0);
	        // 得到总行数
	        int rowNum = sheet.getPhysicalNumberOfRows();
			//int rowNum = getRealRowNum(sheet);
	        row = sheet.getRow(0);
	        int colNum = row.getPhysicalNumberOfCells();
			if (rowNum <= 1) {
				throw new Exception("EXCEL文档不符合规范！");
			}
			ArrayList<HashMap<Integer, String>> alhs = new ArrayList<HashMap<Integer, String>>();
			for (int i = 0; i < rowNum; i++) {
				// 默认第一行为编码，取值字段对应
				if (i == 0) {
					for (int k = 0; k < colNum; k++) {
						String key = row.getCell(k)==null?"": row.getCell(k).getStringCellValue();
						HashMap<Integer, String> hm = new HashMap<Integer, String>();
						hm.put(k, key);
						alhs.add(hm);
					}
				} else {
					@SuppressWarnings("unchecked")
					HashMap<Integer, String>[] hms = alhs.toArray(new HashMap[alhs.size()]);
					Map<String, Object> tempMap = new HashMap<String, Object>();
					for (int h = 0; h < hms.length; h++) {
						String column = hms[h].get(h).trim();
						tempMap.put(column, getCellValue(sheet,h,i));
					}
					ret.add(tempMap);
				}
			}
			// ret = new ArrayList(hs);

		} finally {
			
		}

		return ret;
	}
	
	public static String getCellValue(Sheet ws,int col,int row){
		ws.getRow(row).getCell(col).setCellType(HSSFCell.CELL_TYPE_STRING);//设置常规格式,防止自动科学计数法
		String str=ws.getRow(row).getCell(col).toString();
		return str;
	}
	
	/**
     * 创建Excel文件
     * @param filepath filepath 文件全路径
     * @param sheetName 新Sheet页的名字
     * @param titles 表头
     * @param values 每行的单元格
     */
    public static boolean writeExcel(String filepath, String sheetName, List<String> titles,
            List<Map<String, Object>> values,int oriArrLen) throws IOException {
        boolean success = false;
        OutputStream outputStream = null;
        if (StringUtils.isBlank(filepath)) {
            throw new IllegalArgumentException("文件路径不能为空");
        } else {
            String suffiex = getSuffiex(filepath);
            if (StringUtils.isBlank(suffiex)) {
                throw new IllegalArgumentException("文件后缀不能为空");
            }
            Workbook workbook;
            if ("xls".equals(suffiex.toLowerCase())) {
                workbook = new HSSFWorkbook();
            } else {
                workbook = new XSSFWorkbook();
            }
            // 生成一个表格
            Sheet sheet;
            if (StringUtils.isBlank(sheetName)) {
                // name 为空则使用默认值
                sheet = workbook.createSheet();
            } else {
                sheet = workbook.createSheet(sheetName);
            }
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth((short) 15);
            // 生成样式
            //Map<String, CellStyle> styles = createStyles(workbook);
            // 创建标题行
            Row row = sheet.createRow(0);
            // 存储标题在Excel文件中的序号
            //Map<String, Integer> titleOrder = Maps.newHashMap();
            Map<String, Integer> titleOrder = new HashMap<String, Integer>();
            for (int i = 0; i < titles.size(); i++) {
                Cell cell = row.createCell(i);
                //cell.setCellStyle(styles.get("header"));
                String title = titles.get(i);
                cell.setCellValue(title);
                titleOrder.put(title, i);
            }
            // 写入正文
            Iterator<Map<String, Object>> iterator = values.iterator();
            // 行号
            int index = 1;
            while (iterator.hasNext()) {
                row = sheet.createRow(index);
                Map<String, Object> value = iterator.next();
                for (Map.Entry<String, Object> map : value.entrySet()) {
                    // 获取列名
                    String title = map.getKey();
                    // 根据列名获取序号
                    int i = titleOrder.get(title);
                    // 在指定序号处创建cell
                    Cell cell = row.createCell(i);
                    // 设置cell的样式
//                    if (index % 2 == 1) {
//                        cell.setCellStyle(styles.get("cellA"));
//                    } else {
//                        cell.setCellStyle(styles.get("cellB"));
//                    }
                    // 获取列的值
                    Object object = map.getValue();
                    // 判断object的类型
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    if (object instanceof Double) {
                        cell.setCellValue((Double) object);
                    } else if (object instanceof Date) {
                        String time = simpleDateFormat.format((Date) object);
                        cell.setCellValue(time);
                    } else if (object instanceof Calendar) {
                        Calendar calendar = (Calendar) object;
                        String time = simpleDateFormat.format(calendar.getTime());
                        cell.setCellValue(time);
                    } else if (object instanceof Boolean) {
                        cell.setCellValue((Boolean) object);
                    } else {
                        if (object != null) {
                            cell.setCellValue(object.toString());
                        }
                    }
                }
                index++;
            }
            Cell cell = row.createCell(values.size()+1);
            cell.setCellValue("成功条数:"+(oriArrLen-values.size()));
            try {
                outputStream = new FileOutputStream(filepath);
                workbook.write(outputStream);
                success = true;
            } finally {
                if (outputStream != null) {
                    outputStream.close();
                }
                if (workbook != null) {
                    //workbook.close();
                }
            }
            return success;
        }
    }
    
    /**
     * 获取后缀
     * @param filepath filepath 文件全路径
     */
    private static String getSuffiex(String filepath) {
        if (StringUtils.isBlank(filepath)) {
            return "";
        }
        int index = filepath.lastIndexOf(".");
        if (index == -1) {
            return "";
        }
        return filepath.substring(index + 1, filepath.length());
    }
    
    public static void main(String[] args) {
    	String url = "d:\\a.xls";
    	List<String> titles = new ArrayList<String>();
    	titles.add("id");
    	titles.add("name");
    	titles.add("错误原因");
    	List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
    	Map<String,Object> map = new HashMap<String, Object>();
    	map.put("id", "411411411411411");
    	map.put("name", "张三");
    	map.put("错误原因", "人员信息不匹配");
    	list.add(map);
    	Map<String,Object> map2 = new HashMap<String, Object>();
    	map2.put("id", "565656565656");
    	map2.put("name", "李四");
    	map2.put("错误原因", "身份证号不符合规则");
    	list.add(map2);
		//boolean a = writeExcel(url, "sheet1", titles, list);
		String a = FileSystemView.getFileSystemView().getHomeDirectory().getAbsolutePath();
		System.out.println(a);
	}
    
}