Java对Excel的读写详解(POI)

<!-- 配置读取excel所需 -->
        <dependency>
            <groupId>dom4j</groupId>
            <artifactId>dom4j</artifactId>
            <version>1.6.1</version>
        </dependency>
        <dependency>
            <groupId>xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>2.3.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.geronimo.specs</groupId>
            <artifactId>geronimo-stax-api_1.0_spec</artifactId>
            <version>1.0.1</version>
        </dependency>
        <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi</artifactId>
          <version>3.7</version>
        </dependency>
        <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml-schemas</artifactId>
          <version>3.7</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.7</version>
        </dependency>

读取Excel

//excel文件批量导入短信
    @RequestMapping("/importToContent.ca")
    public void importToContent(HttpServletRequest request,HttpServletResponse response,@RequestParam("excelFile") MultipartFile file,@RequestParam("categoryId") String categoryId){
        try {
            scm.readExcelWriteData(file.getInputStream(),categoryId);
            this.initCpRegisterData(request);
        } catch (Exception e) {
            log.debug("batch upload smsContent Exception");
            e.printStackTrace();
        }
        out = this.getOut(response);
        out.print(200);
        //return new ModelAndView("/frames/so_Groupsms_mainpage");
    }

public int readExcelWriteData(InputStream inp,String categoryId) throws Exception{
        Workbook wb = WorkbookFactory.create(inp);
        int numberRow;
        //获取第一张表
        Sheet st = wb.getSheetAt(0);
        //获取行数   st.getLastRowNum();
        //存储读取的短信
        List smsContentList = new ArrayList();
        String smsCon = null;
        //遍历第一张表的所有行
        for(int i=0;i<=st.getLastRowNum();i++){
        	//第一行一般为title不读取
        	if(i!=0){
        		Row row = st.getRow(i);	//获取第一行数据
        		//遍历第一行所有的列(单元格)
        		for(int j=0;j<row.getLastCellNum();j++){
        			Cell cell = row.getCell(j);  //获取第一个单元格
        			//获取单元格类型
        			switch (cell.getCellType()) {
	                    case Cell.CELL_TYPE_STRING:		//字符串
	                    	System.out.println(cell.getRichStringCellValue().getString());
	                        smsCon = cell.getRichStringCellValue().getString();
	                        break;
	                    case Cell.CELL_TYPE_NUMERIC:	//数字
	                        if (DateUtil.isCellDateFormatted(cell)) {	//判断是否包含日期
	                            System.out.println(cell.getDateCellValue());
	                        } else {
	                            System.out.println(cell.getNumericCellValue());
	                        	smsCon = String.valueOf(cell.getNumericCellValue());
	                        }
	                        break;
	                    case Cell.CELL_TYPE_BOOLEAN:	//布尔
	                        //System.out.println(cell.getBooleanCellValue());
	                        break;
	                    case Cell.CELL_TYPE_FORMULA:	//公式
	                        //System.out.println(cell.getCellFormula());
	                        break;
	                    default:
	                        System.out.println();
	                }
        		}
        	}
        	if(smsCon!=null&&!smsCon.equals("")){
        		smsContentList.add(smsCon);
        	}
        }
        return smsContentDao.batchImportSmsContentByList(smsContentList,categoryId,st.getLastRowNum());
    }

导出Excel
跨第1行第1个到第2个单元格的操作为
sheet.addMergedRegion(new Region(0,(short)0,0,(short)1));

跨第1行第1个到第2行第1个单元格的操作为
sheet.addMergedRegion(new Region(0,(short)0,1,(short)0));

//请求处理controller
@RequestMapping("/outputExcelByActivity.ca")                      
    public void outputExcelByActivity(HttpServletRequest request,HttpServletResponse response,
                                      @RequestParam("ids") String ids) throws Exception{
        response.addHeader("Content-Disposition", "attachment;filename=" + "outputExcel.xls");
        response.setContentType("application/octet-stream");
        String[] nums = ids.split(",");
        Long[] ids1 = new Long[nums.length];
        for (int i = 0; i < nums.length; ++i) {
            ids1[i] = Long.valueOf(nums[i]);
        }
        List<MerchantOrder> list = activityManager.getOutputExcelOrder(ids1);
        OrderExcelHelper.export(response.getOutputStream(),list);
    }

public class OrderExcelHelper {
    public static void export(OutputStream os,List<MerchantOrder> list) throws IOException {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet worksheet = workbook.createSheet("雨果科技");
        formatHeader(worksheet);
        workbook.write(os);
    }
    private static void formatHeader(HSSFSheet worksheet) {
        String[] str = new String[]{"****科技有限公司","单位名称全称（发票抬头）：",
                "联系人：，电话：[phone]，邮件：[e-mail]",
        "地址：上海市，邮编：201100","订期：2012年报刊","报价人：张三","订单号：20120517"};
        for(int i=0;i<6;i++){
            HSSFRow row = worksheet.createRow((short) i);  //创建一个行
            if(i==0){
                worksheet.addMergedRegion(new Region(i,(short)0,i,(short)14));
                HSSFCell cellA1 = row.createCell((short) 0);
                cellA1.setCellValue("抬头***");
            }else if(i==1){
                worksheet.addMergedRegion(new Region(i,(short)0,i,(short)3));
                worksheet.addMergedRegion(new Region(i,(short)4,i,(short)14));
                HSSFCell cellA1 = row.createCell((short) 0);
                HSSFCell cellA2 = row.createCell((short) 4);
                cellA1.setCellValue("单位名称全称（发票抬头）：");
                cellA2.setCellValue("xxxx");
            }else if(i==2){
                for(int j=0;j<10;j++){
                    if(j==0){
                        HSSFCell cellA1 = row.createCell((short) 0);
                        cellA1.setCellValue("联系人：");
                    }else if(j==1){
                        HSSFCell cellA1 = row.createCell((short) 1);
                        cellA1.setCellValue("xxx");
                    }else if(j==2){
                        HSSFCell cellA1 = row.createCell((short) 2);
                        cellA1.setCellValue("性别：");
                    }else if(j==3){
                        HSSFCell cellA1 = row.createCell((short) 3);
                        cellA1.setCellValue("男");
                    }else if(j==4){
                        HSSFCell cellA1 = row.createCell((short) 4);
                        cellA1.setCellValue("电话：");
                    }else if(j==5){
                        worksheet.addMergedRegion(new Region(i,(short)5,i,(short)7));
                        HSSFCell cellA1 = row.createCell((short) 5);
                        cellA1.setCellValue("0931-7361342");
                    }else if(j==6){
                        HSSFCell cellA1 = row.createCell((short) 8);
                        cellA1.setCellValue("手机：");
                    }else if(j==7){
                        worksheet.addMergedRegion(new Region(i,(short)9,i,(short)10));
                        HSSFCell cellA1 = row.createCell((short) 9);
                        cellA1.setCellValue("18201815572");
                    }else if(j==8){
                        HSSFCell cellA1 = row.createCell((short) 11);
                        cellA1.setCellValue("Email");
                    }else if(j==9){
                        worksheet.addMergedRegion(new Region(i,(short)12,i,(short)14));
                        HSSFCell cellA1 = row.createCell((short) 12);
                        cellA1.setCellValue("[e-mail]");
                    }
                }
            }else if(i==3){
                HSSFCell cellA1 = row.createCell((short) 0);
                cellA1.setCellValue("地址：");
                worksheet.addMergedRegion(new Region(i,(short)1,i,(short)2));
                HSSFCell cellA2 = row.createCell((short) 1);
                cellA2.setCellValue("闵行区");
                HSSFCell cellA3 = row.createCell((short) 3);
                cellA3.setCellValue("邮编：");
                worksheet.addMergedRegion(new Region(i,(short)3,i,(short)14));
                HSSFCell cellA4 = row.createCell((short) 1);
                cellA1.setCellValue("730060");
            }else if(i==4){
                HSSFCell cellA1 = row.createCell((short) 0);
                cellA1.setCellValue("报价人：");
                HSSFCell cellA3 = row.createCell((short) 1);
                cellA3.setCellValue("张三");
                worksheet.addMergedRegion(new Region(i,(short)2,i,(short)14));
                HSSFCell cellA2 = row.createCell((short) 2);
                cellA2.setCellValue("***");
            }else if(i==5){
                worksheet.addMergedRegion(new Region(i,(short)0,i,(short)14));
            }
        }
    }
}

评论

2 楼 zcw_java 2012-06-27  

xuskyline 写道

谢谢 分享，研究研究

不客气，共同努力提高

1 楼 xuskyline 2012-06-18  

谢谢 分享，研究研究