POI 读写 Excel

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.usermodel.WorkbookFactory;

/**
 * 操作Excel表格的功能类
 */
public class ExcelReader {
    private Workbook wb;
    private Sheet sheet;
    private Row row;

    /**
     * 读取Excel表格表头的内容
     * @param InputStream
     * @return String 表头内容的数组
     * @throws InvalidFormatException 
     */
    public String[] readExcelTitle(InputStream is) throws InvalidFormatException {
        try {
        	wb = WorkbookFactory.create(is);  
        } 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];
        for (int i = 0; i < colNum; i++) {
            title[i] = getStringCellValue(row.getCell(i));
        }
        return title;
    }

    /**
     * 读取Excel数据内容
     * @param InputStream
     * @return Map 包含单元格数据内容的Map对象
     * @throws InvalidFormatException 
     */
    public List<String[]> readExcelContent(InputStream is) throws InvalidFormatException {
        try {
        	wb = WorkbookFactory.create(is);  
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        List<String[]> list = new ArrayList<String[]>();
        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);
            String content[] = new String[colNum];
            int j = 0;
            while (j < colNum) {
            	content[j] = this.getStringCellValue(row.getCell(j));
                j++;
            }
            list.add(content);
        }
        return list;
    }

    /**
     * 获取单元格数据内容为字符串类型的数据
     * 
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    private String getStringCellValue(Cell cell) {
        String strCell = "";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            strCell = cell.getStringCellValue().trim();
            break;
        case Cell.CELL_TYPE_NUMERIC:
        	if(DateUtil.isCellDateFormatted(cell)){  
        		strCell = sdf.format(cell.getDateCellValue());  
              }else{   
                strCell = String.valueOf(cell.getNumericCellValue());
              }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            strCell = String.valueOf(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            strCell = "";
            break;
        default:
            strCell = "";
            break;
        }
        return strCell;
    }
    
    /**
     * 写入内容
     * @param is
     * @param sheetName
     * @param list
     * @throws IOException 
     * @throws InvalidFormatException 
     */
    public void writeExcelContent(InputStream is,String sheetName,List<String[]> list,OutputStream os) throws Exception{
    	wb = WorkbookFactory.create(is);
    	sheet= wb.getSheetAt(0);
    	
    	CellStyle style = wb.createCellStyle();
        style.setBorderBottom(CellStyle.BORDER_THIN);  
        style.setBorderLeft(CellStyle.BORDER_THIN);  
        style.setBorderRight(CellStyle.BORDER_THIN);  
        style.setBorderTop(CellStyle.BORDER_THIN);  
        style.setAlignment(CellStyle.ALIGN_CENTER);
        
        Font font = wb.createFont();  
        font.setFontHeightInPoints((short) 9); 
        
        style.setFont(font);  
        
    	for(int i=0;i<list.size();i++){
    		String[] obj = list.get(i);
    		row = sheet.createRow(i+1);
    		for(int j=0;j<obj.length;j++){
    			Cell cell = row.createCell(j);
    			cell.setCellStyle(style);
    			cell.setCellValue(obj[j]);
    		}
    	}
    	wb.write(os);   
    	os.close();  
    }

    public static void main(String[] args) throws Exception{
        try {
            // 对读取Excel表格标题测试
            InputStream is = new FileInputStream("d:\\test.xlsx");
            ExcelReader excelReader = new ExcelReader();
            String[] title = excelReader.readExcelTitle(is);
            System.out.println("获得Excel表格的标题:");
            for (String s : title) {
                System.out.print(s + " ");
            }

            // 对读取Excel表格内容测试
            InputStream is2 = new FileInputStream("d:\\test.xlsx");
            List<String[]> list = excelReader.readExcelContent(is2);
            System.out.println("获得Excel表格的内容:");
            for (int i = 0; i < list.size(); i++) {
            	String[] s = list.get(i);
            	for(int j=0;j<s.length;j++){
            		System.out.print(s[j] + " ");
            	}
                System.out.println("\n");
            }

        } catch (FileNotFoundException e) {
            System.out.println("未找到指定路径的文件!");
            e.printStackTrace();
        }
    }
}

 

/**
	 * 
	 * @Title: export 
	 * @Description: 导出Excel
	 */
	public void export(Context context){
		HttpServletRequest request = context.request;
		HttpServletResponse response = context.response;
		String filePath = request.getSession().getServletContext().getRealPath("/template/detail.xlsx");
		OutputStream os = null;
		FileInputStream fis = null;
		try {
			response.reset();
			response.setCharacterEncoding("gb2312");
			
			String fileName = "转账明细.xlsx";
			String name = new String(fileName.getBytes("gb2312"), "ISO8859-1");
			response.setHeader( "Content-Disposition","attachment; filename=" + name);
			response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); 
			fis = new FileInputStream(filePath);
			os = response.getOutputStream();
			List<String[]> list = service.findAll();
			ExcelReader er = new ExcelReader();
			er.writeExcelContent(fis, "转账明细", list, os);
			os.flush();
			response.flushBuffer();
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			try {
				if (fis != null) fis.close();
				if (os != null) os.close();
			} catch (Exception e2) {
				logger.error(e2.getMessage(), e2);
			}
		}
	}