Excel导入导出

//exlel读操作

MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
Iterator<String> iter = multipartRequest.getFileNames();
File fileFile = null;
while (iter.hasNext()) {
MultipartFile multipartFile = multipartRequest.getFile(iter.next());
String sourceName = multipartFile.getOriginalFilename();
String base = request.getSession().getServletContext().getRealPath("/");
File file = new File(base);
if(!file.exists()){
　　file.mkdirs();
}
String path=base + File.separator + sourceName;

File fileFile = new File(path);
multipartFile.transferTo(fileFile);

 

HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));

HSSFSheet sheet = wb.getSheetAt(0);

for(int j=0;j<sheet.getLastRowNum()+1;j++) {
           HSSFRow row = sheet.getRow(j);
            for(int i1=0; i1<row.getLastCellNum(); i1++) {
                     HSSFCell cell = row.getCell(i1);
                     System.out.println(cell.getRichStringCellValue());
                  }
             }

　　}

//exlel写操作

 // 第一步，创建一个webbook，对应一个Excel文件

HSSFWorkbook wb = new HSSFWorkbook();

// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet

HSSFSheet sheet = wb.createSheet(name);

// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short

HSSFRow row = sheet.createRow(0);

// 第四步，创建单元格，并设置值表头 设置表头居中

HSSFCellStyle style = wb.createCellStyle();

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

sheet.setColumnWidth(0, 10000);
        sheet.setColumnWidth(1, 7000);
        sheet.setColumnWidth(2, 4000);
        cell.setCellValue(TeacherConstant.TCITY);
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellValue(TeacherConstant.TNAME);  
        cell.setCellStyle(style);  
        cell = row.createCell(2);  
        cell.setCellValue(TeacherConstant.TPHONE);
        cell.setCellStyle(style);
        
        for (int i = 0; i < pmUsers.size(); i++) {  
            Map<String, Object> map = pmUsers.get(i);  
                row = sheet.createRow(i + 1);
                cell = row.createCell(0);  
                cell.setCellStyle(style);  
                cell.setCellValue(new HSSFRichTextString(map.get("TotalCityName") + ""));  
                
                cell = row.createCell(1);  
                cell.setCellStyle(style);  
                cell.setCellValue(new HSSFRichTextString(map.get("teacher_name") + ""));  
                
                cell = row.createCell(2);  
                cell.setCellStyle(style);  
                cell.setCellValue(new HSSFRichTextString(map.get("teacher_phone") + ""));  
        }
        
        // 第六步，将文件存到指定位置  
        FileOutputStream file = null;
        String fileAdd = "";
        if (pmUsers.size() > 0) {
            if (paramBean.getCityId() == null) {
                fileAdd = realPath + pmUsers.get(0).get("TotalCityName").toString().substring(0, 3) + TeacherConstant.TTABLE + TeacherConstant.XLS;
            } else if (paramBean.getCountyId() == null) {
                fileAdd = realPath + pmUsers.get(0).get("TotalCityName").toString().substring(0, 6) + TeacherConstant.TTABLE + TeacherConstant.XLS;
            } else {
                fileAdd = realPath + pmUsers.get(0).get("TotalCityName").toString().substring(0, 9) + TeacherConstant.TTABLE + TeacherConstant.XLS;
            }
        } else {
            return;
        }
        try  
        {
            file = new FileOutputStream(fileAdd);
            wb.write(file);  
        }  
        catch (Exception e)  
        {
            e.printStackTrace();  
        }
        finally {
            file.close();