Excel文件的导出与导入

当我们在执行查询操作时，很多情况下会得到一个结果集。如果我们想以Excel的形式保存在本地的话，就需要用到Excel的导出功能。 

ExcelExport.java

import  java.io. * ;
import  java.net.URLEncoder;
import  java.util. * ;
import  javax.servlet.http.HttpServletResponse;
import  org.apache.poi.hssf.usermodel. * ;

public   class  ExcelExport
... {

    public ExcelExport()
    ...{
        wb = new HSSFWorkbook();
    }

    public void createSheet(String sheetName)
        throws Exception
    ...{
        if(wb.getSheet(sheetName) != null)
        ...{
            throw new Exception((new StringBuilder()).append("Sheet name has exist: ").append(sheetName).append("!").toString());
        } else
        ...{
            wb.createSheet(sheetName);
            return;
        }
    }

    public void appendList(String sheetName, List data)
        throws Exception
    ...{
        if(data == null)
            throw new Exception((new StringBuilder()).append("Sheet data is null: ").append(sheetName).append("!").toString());
        if(data.size() > 65535)
            throw new Exception("Sheet data's size is too big, max size is 65535!");
        HSSFSheet sheet = getSheetOfName(sheetName);
        int lastRowNum = sheet.getLastRowNum();
        int rowNum = lastRowNum != 0 || sheet.getRow(0) != null ? lastRowNum + 1 : 0;
        for(int i = 0; i < data.size(); i++)
        ...{
            Map m = (Map)data.get(i);
            if(m == null)
                continue;
            if(m.size() > 255)
                throw new Exception("Sheet map's size is too big, max size is 255!");
            HSSFRow row = sheet.createRow((short)rowNum + i);
            Iterator values = m.values().iterator();
            for(int j = 0; j < m.size(); j++)
                row.createCell((short)j).setCellValue(new HSSFRichTextString(String.valueOf(values.next())));

        }

    }

    public void appendMap(String sheetName, Map data)
        throws Exception
    ...{
        if(data == null)
            throw new Exception((new StringBuilder()).append("Sheet data is null: ").append(sheetName).append("!").toString());
        if(data.size() > 255)
            throw new Exception("Sheet data's size is too big, max size is 255!");
        HSSFSheet sheet = getSheetOfName(sheetName);
        int lastRowNum = sheet.getLastRowNum();
        int rowNum = lastRowNum != 0 || sheet.getRow(0) != null ? lastRowNum + 1 : 0;
        HSSFRow row = sheet.createRow((short)rowNum);
        Iterator values = data.values().iterator();
        for(int i = 0; i < data.size(); i++)
            row.createCell((short)i).setCellValue(new HSSFRichTextString(String.valueOf(values.next())));

    }

    public void writeString(String sheetName, int rowNum, int colNum, String data)
        throws Exception
    ...{
        if(rowNum < 0 || rowNum > 32767)
            throw new Exception((new StringBuilder()).append("Sheet rowNum should be in 0-32767, but now is: ").append(rowNum).append("!").toString());
        if(colNum < 0 || colNum > 254)
        ...{
            throw new Exception((new StringBuilder()).append("Sheet colNum should be in 0-254, but now is: ").append(colNum).append("!").toString());
        } else
        ...{
            HSSFSheet sheet = getSheetOfName(sheetName);
            HSSFRow row = sheet.createRow((short)rowNum);
            row.createCell((short)colNum).setCellValue(new HSSFRichTextString(data));
            return;
        }
    }

    public void saveSheet(String filename)
        throws IOException
    ...{
        FileOutputStream fileOut = new FileOutputStream(filename);
        wb.write(fileOut);
        fileOut.close();
    }

    public void downExcelFile(String fileName, HttpServletResponse response)
        throws IOException
    ...{
        OutputStream ouputStream;
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String downloadName = URLEncoder.encode(fileName, "utf-8");
        response.setHeader("Content-Disposition", (new StringBuilder()).append("attachment;filename="").append(downloadName).append(""").toString());
        ouputStream = null;
        ouputStream = response.getOutputStream();
        wb.write(ouputStream);
        ouputStream.flush();
        if(ouputStream != null)
            ouputStream.close();
        break MISSING_BLOCK_LABEL_106;
        Exception exception;
        exception;
        if(ouputStream != null)
            ouputStream.close();
        throw exception;
    }

    private HSSFSheet getSheetOfName(String sheetName)
        throws Exception
    ...{
        HSSFSheet sheet = wb.getSheet(sheetName);
        if(sheet == null)
            throw new Exception((new StringBuilder()).append("Sheet name don't exist: ").append(sheetName).append("!").toString());
        else
            return sheet;
    }

    public static void main(String args[])
        throws Exception
    ...{
        ExcelExport ee = new ExcelExport();
        ee.createSheet("s1");
        ee.writeString("s1", 0, 0, "xxx");
        Map m = new LinkedHashMap();
        m.put("a", "111");
        m.put("b", "222");
        m.put("c", "333");
        m.put("d", "444");
        ee.appendMap("s1", m);
        ee.saveSheet("c:/ee.xls");
    }

    HSSFWorkbook wb;
}

 

导入Excel文件

import  java.io. * ;
import  java.util. * ;
import  org.apache.poi.hssf.usermodel. * ;
import  org.apache.poi.poifs.filesystem.POIFSFileSystem;

public   class  ExcelImport
... {

    public ExcelImport()
    ...{
    }

    public void readExcel(String filename)
        throws IOException
    ...{
        readExcel(((InputStream) (new FileInputStream(filename))));
    }

    public void readExcel(InputStream inputStream)
        throws IOException
    ...{
        try
        ...{
            POIFSFileSystem fs = new POIFSFileSystem(inputStream);
            wb = new HSSFWorkbook(fs);
        }
        catch(IOException e)
        ...{
            throw new IOException("This file is not valid excel document!");
        }
        sheetCount = wb.getNumberOfSheets();
    }

    public int getSheetCount()
    ...{
        return sheetCount;
    }

    public String getSheetName(int sheetIndex)
    ...{
        return wb.getSheetName(sheetIndex);
    }

    public int getSheetIndex(String sheetName)
    ...{
        return wb.getSheetIndex(sheetName);
    }

    public List getSheetValues(String sheetName)
        throws Exception
    ...{
        HSSFSheet sheet = wb.getSheet(sheetName);
        if(sheet == null)
            throw new Exception((new StringBuilder()).append("Sheet name don't exist: ").append(sheetName).append("!").toString());
        else
            return getSheetValues(sheet);
    }

    public List getSheetValues(int sheetIndex)
        throws Exception
    ...{
        if(sheetIndex > sheetCount - 1 || sheetIndex < 0)
            throw new Exception((new StringBuilder()).append("Sheet index don't exist: ").append(sheetIndex).append("!").toString());
        else
            return getSheetValues(wb.getSheetAt(sheetIndex));
    }

    private List getSheetValues(HSSFSheet sheet)
        throws IOException
    ...{
        if(sheet == null)
            return null;
        List sheetlist = new ArrayList();
        int rowCount = sheet.getLastRowNum() + 1;
        for(int i = 0; i < rowCount; i++)
        ...{
            Map m = new LinkedHashMap();
            HSSFRow row = sheet.getRow(i);
            if(row == null)
                continue;
            int colCount = row.getLastCellNum();
            for(short j = 0; j < colCount; j++)
            ...{
                HSSFCell cell = row.getCell(j);
                m.put(String.valueOf(j), getCellValue(cell));
            }

            sheetlist.add(m);
        }

        return sheetlist;
    }

    private Object getCellValue(HSSFCell cell)
    ...{
        if(cell == null)
            return "";
        switch(cell.getCellType())
        ...{
        case 0: // '