java操作execl

lgzbj2006

浏览: 28018 次
性别:
来自: 北京

最近访客更多访客>>

iytbihuf

yaoye119

844700118

Speter

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

转载:http://www.aijava.cn/651.html

java excel apache

Java 操作 Excel、Poi实现 (兼容Excel2003 2007)
投稿进阶篇围观1862次暂无评论编辑日期：2013-09-23 字体：大中小
一. Apache POI 简介（ http://poi.apache.org）
         Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft         Office格式档案读和写的功能。

结构：

       HSSF －提供读写Microsoft Excel格式档案的功能。

       XSSF －提供读写Microsoft Excel OOXML格式档案的功能。

       HWPF －提供读写Microsoft Word格式档案的功能。

       HSLF －提供读写Microsoft PowerPoint格式档案的功能。

       HDGF －提供读写Microsoft Visio格式档案的功能。

二、POI操作Excel
    步骤：

       1. 官方快速帮助：

               http://poi.apache.org/spreadsheet/quick-guide.html

       2、引用Jar包

       1> Maven 形式

       Maven查找地址：http://search.maven.org


<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>${poi.version}</version>
</dependency>
       3: 示例代码

java 判断excel中是否包含图片,兼容Excel2003、2007
package framework.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
* @Comment ： Excel 的简单操作 <br>
*/
public class ExcelUtil {

    /**
     * 判断excel中含有图片
     *
     * @param is
     *            判断excel文件流
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static Boolean hasImageOfExcel(InputStream is) throws IOException, InvalidFormatException {

        // 图片数量（同名图片不记）
        Integer imgNumber = 0;
        //兼容Excel2003、2007 创建Workbook方式
        Workbook wb = WorkbookFactory.create(is);
        imgNumber = wb.getAllPictures().size();
        return imgNumber > 0 ? true : false;
    }

    public static void main(String[] args) throws Exception {
        String excelFilePath = "D://1.xlsx";
        System.out.println(hasImageOfExcel(new FileInputStream(excelFilePath)));
    }

}

java 读取 excel

package excel.poi;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.POITextExtractor;
import org.apache.poi.extractor.ExtractorFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.XmlException;
public class ReadExcel {
    /**
     * 读取office 2003 xls
     * @param filePath
     */
@SuppressWarnings({ "unchecked", "deprecation" })
public void loadXls(String filePath){
      try {
           InputStream input = new FileInputStream("D:\\资料\\文档一期\\xls\\082010 凤鸣轩书单.xls");
           POIFSFileSystem fs = new POIFSFileSystem(input);
           HSSFWorkbook wb = new HSSFWorkbook(fs);
           HSSFSheet sheet = wb.getSheetAt(0);
           // Iterate over each row in the sheet
           Iterator rows = sheet.rowIterator();
           while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();
            System.out.println("Row #" + row.getRowNum());
            // Iterate over each cell in the row and print out the cell"s
            // content
            Iterator cells = row.cellIterator();
            while (cells.hasNext()) {
             HSSFCell cell = (HSSFCell) cells.next();
             System.out.println("Cell #" + cell.getCellNum());
             switch (cell.getCellType()) {
             case HSSFCell.CELL_TYPE_NUMERIC:
              System.out.println(cell.getNumericCellValue());
              break;
             case HSSFCell.CELL_TYPE_STRING:
              System.out.println(cell.getStringCellValue());
              break;
             case HSSFCell.CELL_TYPE_BOOLEAN:
              System.out.println(cell.getBooleanCellValue());
              break;
             case HSSFCell.CELL_TYPE_FORMULA:
              System.out.println(cell.getCellFormula());
              break;
             default:
              System.out.println("unsuported sell type");
              break;
             }
            }
           }
          } catch (IOException ex) {
           ex.printStackTrace();
          }
}
/**
* 读取xlsx文本
* @param filePath
*/
public void loadXlsxText(String filePath){
     File inputFile = new File("D:\\test.xlsx");
     try {
        POITextExtractor extractor = ExtractorFactory.createExtractor(inputFile);
        System.out.println(extractor.getText());
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (OpenXML4JException e) {
        e.printStackTrace();
    } catch (XmlException e) {
        e.printStackTrace();
    }
}
/**
* 读取office 2007 xlsx
* @param filePath
*/
public void loadXlsx(String filePath){
     // 构造 XSSFWorkbook 对象，strPath 传入文件路径
    XSSFWorkbook xwb = null;
    try {
        xwb = new XSSFWorkbook("D:\\text.xlsx");
    } catch (IOException e) {
        System.out.println("读取文件出错");
        e.printStackTrace();
    }
     // 读取第一章表格内容
     XSSFSheet sheet = xwb.getSheetAt(0);
    // 定义 row、cell
     XSSFRow row;
     String cell;
     // 循环输出表格中的内容
     for (int i = sheet.getFirstRowNum()+1; i < sheet.getPhysicalNumberOfRows(); i++) {
         row = sheet.getRow(i);
         for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
            // 通过 row.getCell(j).toString() 获取单元格内容，
             if (j==1&&i!=0) {
                 cell = row.getCell(j).getDateCellValue().toLocaleString();
            }else {
                cell = row.getCell(j).toString();
            }
            /* //获取字体和背景颜色
            String rgbShort=row.getCell(j).getCellStyle().getFont().getCTFont().getColorArray()[0].xmlText();
            rgbShort=ReadExcel.substringBetween(rgbShort, "rgb=\"","\"/>");
            String rgbShort=row.getCell(j).getCellStyle().getFillBackgroundXSSFColor().getCTColor().toString();
            Color color=new Color(Color.BLUE.getRGB());
            System.out.print(cell +",index:"+rgbShort+" red:"+color.getRed()+" blue:"+color.getBlue()+"\t");   */
            System.out.print(cell +"\t");
        }
         System.out.println("");
     }
}
/**
* HSSF 写入excel xls 格式
* @param filePath
* @throws IOException
*/
public void writeXls(String filePath)throws IOException{
     //工作簿 23.
     HSSFWorkbook hssfworkbook=new HSSFWorkbook();
     //创建sheet页 25.
     HSSFSheet hssfsheet=hssfworkbook.createSheet();
     //sheet名称
     hssfworkbook.setSheetName(0,"研发部门");
     //取得第一行 29.
     HSSFRow hssfrow=hssfsheet.createRow(0);
     //创建第一个单元格并处理乱码 31.
     HSSFCell hssfcell_0=hssfrow.createCell((short)0);
     //hssfcell_0.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
     //对第一个单元格赋值 34.
     hssfcell_0.setCellValue("研发工程师1");
     //日期单元格格式处理
     HSSFCellStyle hssfcellstyle=hssfworkbook.createCellStyle();
     //m/d/yyh:mm 39.
     hssfcellstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
     //创建第二个单元格 41.
     HSSFCell hssfcell_1=hssfrow.createCell((short)1);
     hssfcell_1.setCellValue(new Date());
     hssfcell_1.setCellStyle(hssfcellstyle);
     hssfrow.createCell((short)2).setCellValue(true);
     hssfrow.createCell((short)3).setCellValue(122.00);
     //输出 49.
     FileOutputStream fileoutputstream=new FileOutputStream("d:\\exceltext.xls");
     hssfworkbook.write(fileoutputstream);
     fileoutputstream.close();
}
@SuppressWarnings("static-access")
public void writeXlsx(String filePath)throws IOException{
     //工作簿
     XSSFWorkbook hssfworkbook=new XSSFWorkbook();
    //获得CreationHelper对象,这个应该是一个帮助类
     XSSFCreationHelper helper=hssfworkbook.getCreationHelper();
     //创建sheet页
     XSSFSheet hssfsheet=hssfworkbook.createSheet();
     //设置sheet名称
     hssfworkbook.setSheetName(0,"我的测试sheet");
     //取得第一行
     XSSFRow firstRow=hssfsheet.createRow(0);
     //创建第一个单元格
     XSSFCell hssfcell_0=firstRow.createCell(0);
     //hssfcell_0.setEncoding(HSSFWorkbook.ENCODING_UTF_16);并处理乱码
     //对第一个单元格赋值
     hssfcell_0.setCellValue("名称");
     //创建第二个单元格
     XSSFCell hssfcell_1=firstRow.createCell(1);
     hssfcell_1.setCellValue("创建日期");
     //日期单元格格式处理
     XSSFCellStyle dateCellStyle=hssfworkbook.createCellStyle();
     //m/d/yyh:mm 设置日期格式
     dateCellStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd hh:mm:ss"));
     dateCellStyle=ReadExcel.setFillBackgroundColors(dateCellStyle, IndexedColors.BLACK.getIndex(), IndexedColors.YELLOW.getIndex(), dateCellStyle.SOLID_FOREGROUND);
     //设置其他标题
     firstRow.createCell(2).setCellValue("用户");
     firstRow.createCell(3).setCellValue("备注");

     //写入所有内容行
     for (int rowInt = 1; rowInt < 10; rowInt++) {
        XSSFRow row =hssfsheet.createRow(rowInt);
        XSSFCell cell_0=row.createCell(0);
        cell_0.setCellValue("刘伯恩");
        XSSFCell cell_1=row.createCell(1);
        cell_1.setCellValue(new Date());
        cell_1.setCellStyle(dateCellStyle);
        XSSFCell cell_2=row.createCell(2);
        cell_2.setCellValue("超级会员");
        XSSFCell cell_3=row.createCell(3);
        cell_3.setCellValue("这里是备注信息");

    }
     //输出 49.
     FileOutputStream fileoutputstream=new FileOutputStream("d:\\exceltext.xlsx");
     hssfworkbook.write(fileoutputstream);
     fileoutputstream.close();
}
    /**
     * 前景和背景填充的着色
     * @param cellStyle
     * @param bg IndexedColors.ORANGE.getIndex();
     * @param fg IndexedColors.ORANGE.getIndex();
     * @param fp CellStyle.SOLID_FOREGROUND
     * @return
     */
    public static XSSFCellStyle setFillBackgroundColors(XSSFCellStyle cellStyle,short bg,short fg,short fp){
        cellStyle.setFillBackgroundColor(bg);
        cellStyle.setFillForegroundColor(fg);
        cellStyle.setFillPattern(fp);
        return cellStyle;
    }
public static void main(String[] args) {
     ReadExcel readExcel =new ReadExcel();
    /* try {
            readExcel.writeXlsx("");
        } catch (IOException e) {
            e.printStackTrace();
        }*/
     readExcel.loadXls("");
}

}
xls写入图片

public static void main(String[] args) {
    FileOutputStream fileOut = null;
    BufferedImage bufferImg = null;
    BufferedImage bufferImg1 = null;
    try {

        // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        ByteArrayOutputStream byteArrayOut1 = new ByteArrayOutputStream();
        bufferImg = ImageIO.read(new File("C:/tmp/img/test.jpg"));
        bufferImg1 = ImageIO.read(new File("C:/tmp/img/test2.jpg"));
        ImageIO.write(bufferImg, "jpg", byteArrayOut);
        ImageIO.write(bufferImg1, "jpg", byteArrayOut1);

        // 创建一个工作薄
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet1 = wb.createSheet("new sheet");
        // HSSFRow row = sheet1.createRow(2);
        HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 0, 0, (short) 10, 10);
        HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 512, 255,(short) 2, 2, (short) 2, 3);
        anchor1.setAnchorType(2);
        // 插入图片
        patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        patriarch.createPicture(anchor1, wb.addPicture(byteArrayOut1.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

        fileOut = new FileOutputStream("C:/tmp/img/test2.xls");
        // 写入excel文件
        wb.write(fileOut);
        fileOut.close();

    } catch (IOException io) {
        io.printStackTrace();
        System.out.println("io erorr : " + io.getMessage());
    } finally {
        if (fileOut != null) {

            try {
                fileOut.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

分享到：