当前位置:软件学习 > Excel >>

使用POI读写Excel文件

package com.jadyer.demo; 
 
import java.io.File; 
import java.io.FileOutputStream; 
import java.io.IOException; 
 
import org.apache.poi.hssf.usermodel.HSSFWorkbook; 
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.Row; 
import org.apache.poi.ss.usermodel.Sheet; 
import org.apache.poi.ss.usermodel.Workbook; 
import org.apache.poi.ss.usermodel.WorkbookFactory; 
 
/**
 * POI下载地址为http://poi.apache.org/
 * 这里要用到poi-3.9-20121203.jar和poi-ooxml-3.9-20121203.jar
 * @create Jul 9, 2013 7:54:34 PM
 * @author 玄玉<http://blog.csdn.net/jadyer>
 */ 
public class POIDemo { 
    public static void writeExcel() throws IOException{ 
        //创建一个Excel(or new XSSFWorkbook())  
        Workbook wb = new HSSFWorkbook(); 
        //创建表格  
        Sheet sheet = wb.createSheet("测试Sheet_01"); 
        //创建行  
        Row row = sheet.createRow(0); 
        //设置行高  
        row.setHeightInPoints(30); 
        //创建样式  
        CellStyle cs = wb.createCellStyle(); 
        cs.setAlignment(CellStyle.ALIGN_CENTER); 
        cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); 
        cs.setBorderBottom(CellStyle.BORDER_DOTTED);  
        cs.setBorderLeft(CellStyle.BORDER_THIN);  
        cs.setBorderRight(CellStyle.BORDER_THIN); 
        cs.setBorderTop(CellStyle.BORDER_THIN); 
        //创建单元格  
        Cell cell = row.createCell(0); 
        //设置单元格样式  
        cell.setCellStyle(cs); 
        //设置单元格的值  
        cell.setCellValue("序号"); 
        cell = row.createCell(1); 
        cell.setCellStyle(cs); 
        cell.setCellValue("用户"); 
        row = sheet.createRow(1); 
        cell = row.createCell(0); 
        cell.setCellValue("1"); 
        cell = row.createCell(1); 
        cell.setCellValue("张起灵"); 
        FileOutputStream fos = new FileOutputStream("D:/测试的Excel.xls"); 
        wb.write(fos); 
        if(null != fos){ 
            fos.close(); 
        } 
    } 
 
    public static void readExcel() throws InvalidFormatException, IOException{ 
        //老版本POI是使用这种方式创建Workbook的,新版本中可以使用WorkbookFactory,它能自动根据文档的类型打开一个Excel  
        //Workbook wb = new HSSFWorkbook(new FileInputStream("D:/5月业务定制对账文件汇总.xls"));  
        Workbook wb = WorkbookFactory.create(new File("D:/5月业务定制对账文件汇总.xls")); 
        //获取Excel中的某一个数据表..也可以通过Sheet名称来获取,即Workbook.getSheet("定制对账文件")  
        Sheet sheet = wb.getSheetAt(0); 
        Row row = null; 
        Cell cell = null; 
        //获取Excel的总行数:Sheet.getLastRowNum()+1(需要+1)  
        for(/*int i=0*/ int i=sheet.getFirstRowNum(); i<sheet.getLastRowNum()+1; i++){ 
            //获取数据表里面的某一行  
            row = sheet.getRow(i); 
            //获取Excel的总列数:Row.getLastCellNum()(不用+1)  
            for(/*int j=0*/ int j=row.getFirstCellNum(); j<row.getLastCellNum(); j++){ 
                //获取一行中的一个单元格  
                cell = row.getCell(j); 
                System.out.print(getCellValue(cell) + "----"); 
            } 
            //打印完一行的数据之后,再输入一个空行  
            System.out.println(); 
        } 
    } 
     
    /**
     * 使用for-each循环来读取Excel
     */ 
    public static void readExcelUseForeach() throws InvalidFormatException, IOException { 
        for(Row row : WorkbookFactory.create(new File("D:/5月业务定制对账文件汇总.xls")).getSheetAt(0)){ 
            for(Cell cell : row){ 
                System.out.print(getCellValue(cell) + "----"); 
            } 
            System.out.println(); 
        } 
    } 
     
    /**
     * 获取单元格内的数据值
     */ 
    private static String getCellValue(Cell cell){ 
        String str = null; 
        switch (cell.getCellType()) { 
        case Cell.CELL_TYPE_BLANK: 
            str = ""; break; 
        case Cell.CELL_TYPE_BOOLEAN: 
            str = String.valueOf(cell.getBooleanCellValue()); break; 
        case Cell.CELL_TYPE_FORMULA: 
            str = String.valueOf(cell.getCellFormula()); break; 
        case Cell.CELL_TYPE_NUMERIC: 
            str = String.valueOf(cell.getNumericCellValue()); break; 
        case Cell.CELL_TYPE_STRING: 
            str = cell.getStringCellValue(); break; 
        default: 
            str = null; 
            break; 
        } 
        return str; 
    } 
} 

 

补充:软件开发 , Java ,
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,