apache POI 操作 Excel

参考文档:    http://poi.apache.org/ 

 

用apache POI 操作 Excel有几个关键的地方：

 

[1]读文件流

这个问题是一个IO问题

    InputStream in = new FileInputStream("/tmp/aaa.xls");  

 

[2]如何取得Excel的操作对象

这个也就相当于，Excel的工作区，在这个里面你可以取得当前excel文件的相关信息

    POIFSFileSystem poifs = new POIFSFileSystem(fis);   

    HSSFWorkbook wb = new HSSFWorkbook(poifs);  

HSSFWorkbook 对象，是我们最想得到的对象。

以后的所有操作都是从这里开始的。

 

[3]如何取得sheet的数目

    wb.getNumberOfSheets()  

 

[4]如何根据index取得sheet对象

    HSSFSheet sheet = wb.getSheetAt(0);  

有了Sheet就相当于取得了一张表一样。

 

[5]如何取得有效的行数

    int rowcount = sheet.getLastRowNum();  

 

[6]如何根据index取得行对象

    HSSFRow row = sheet.getRow(i);  

有了行对象，就可以取得每一个单元对象

 

[7]如何知道一个行有多少个单元

    colcount = row.getLastCellNum();  

 

[8]如何取得一个单元对象

    HSSFCell cell = row.getCell(j);  

 

 

[9]如何取得单元的值此处仅以字符串为例

    if(cell!=null){   

       System.out.println("cell is: "+cell.getStringCellValue());   

    }  

 

[10]图片设置，可以通过图片的坐标和所覆盖的单元格来定位。

        int x1 = 0;

int y1 = 0;

int x2 = 800;

int y2 = 255;

// 前四个参数x1,y1,x2,y2 是图片以所在单元格位基础的坐标

// 后四个参数代表作在的行和列的单元格,从0开始

HSSFClientAnchor anchor2 = new HSSFClientAnchor(x1, y1, x2, y2,(short) 6, 1, (short) 6, 1);

 

 [11] 单元格格式CellType

 

     CellStyle cellStyle = wb.createCellStyle();

 

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.io.File;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;

public class ImportExcel {
    public static void importexcel() {
	File f = new File("resources/测试.xls");
	if (f.exists()) {
	    // read
            System.out.println(f.getAbsolutePath()+"  is existed");
	    try {
		// 输入文件
		InputStream fis = new FileInputStream(f);
		// 转换输入流
		POIFSFileSystem poifs = new POIFSFileSystem(fis);
		// 转换成hss格式对象
		HSSFWorkbook wb = new HSSFWorkbook(poifs);
		//记录所有行
		List retList = new ArrayList();

		// 打印sheet数
		System.out.println("sheet number : " + wb.getNumberOfSheets());
		// 取得第一个sheet
		HSSFSheet s = wb.getSheetAt(0);
		System.out.println("sheet obj is : " + s);

		// 遍历所有的sheet
		for (int h = 0; h < wb.getNumberOfSheets(); ++h) {
		    //记录一行的所有值
		    List list = new ArrayList();
		    HSSFSheet sheet = wb.getSheetAt(h);
		    // 获取有效行数
		    int rowcount = sheet.getLastRowNum();
		    rowcount++;
		    System.out.print("-----sheet[" + h + "]: row count = "
			    + rowcount);
		    int colcount = 0;
		    // 遍历行
		    for (int i = 0; i < rowcount; ++i) {
			// 获取当前行
			HSSFRow row = sheet.getRow(i);
			// row
			if (row == null)
			    continue; // without the row, break and continue;
			
			//if (colcount == 0) { 
			
			    // colunm count set to column of
			    // 获取有效的单元格数
			    colcount = row.getLastCellNum();
			    // 打印有效单元格式，即列数
			    System.out.println(", column count = " + colcount);
			    // 建立一个数组，用来保存字段值
			    String[] fieldValue = new String[colcount];
			    for (short j = 0; j < colcount; ++j) { // column
				// 获取单元格
				HSSFCell cell = row.getCell(j);
				// fieldValue[j] = getCellStringValue(cell);
				if (cell != null) {
				    // 打印单元格的值
				    System.out.println("cell: "
					    + cell.getStringCellValue());
				}
				// System.out.println("cell is : "
				// +cell.getCellComment());
			    }
			    list.add(fieldValue);
			    
			//}
			    
			retList.add(list);
		    }
		}
	    } catch (FileNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	    } catch (IOException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	    }
	}else{
	    System.out.println(f.getAbsolutePath()+"  is not exist");
	}
    }
    
    public static void main(String[] args) {
	ImportExcel.importexcel();
    }

}

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
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.util.IOUtils;

public class OutputExcel {

    public static void outputExcel() throws IOException {
	// 创建一个excel文件
	Workbook wb = new HSSFWorkbook();
	CreationHelper createHelper = wb.getCreationHelper();
	// 创建两个sheet
	Sheet sheet1 = wb.createSheet("sheet1");
	// Sheet sheet2 = wb.createSheet("second sheet");

	CellStyle cellStyle = wb.createCellStyle();
	cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(
		"mm/dd/yyyy h:mm:ss"));

	// 创建标题
	// 创建一行，从0开始
	Row row1 = sheet1.createRow(0);
	row1.createCell(0).setCellValue("测试1");
	row1.createCell(1).setCellValue("测试2");
	row1.createCell(2).setCellValue("测试3");
	row1.createCell(3).setCellValue("测试4");
	row1.createCell(4).setCellValue("测试5");
	row1.createCell(5).setCellValue("测试6");
	row1.createCell(6).setCellValue("图片");

	// 创建真正的值
	Row row = sheet1.createRow(1);

	// 创建两个图片数据
	InputStream is = new FileInputStream("resources/image1.jpg");
	InputStream is2 = new FileInputStream("resources/google.png");
	byte[] bytes = IOUtils.toByteArray(is);
	byte[] bytes2 = IOUtils.toByteArray(is2);
	int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
	int pictureIdx2 = wb.addPicture(bytes2, Workbook.PICTURE_TYPE_JPEG);
	is.close();
	is2.close();

	// 创建一个单元格，从0开始
	Cell cell = row.createCell(0);
	cell.setCellValue(1);

	// 或者在一行中写完
	row.createCell(1).setCellValue(1.2);
	row.createCell(2).setCellValue(
		createHelper.createRichTextString("This is a string"));
	row.createCell(3).setCellValue(true);
	row.createCell(4).setCellValue("测试");
	Cell cell5 = row.createCell(5);
	cell5.setCellValue(new Date());
	cell5.setCellStyle(cellStyle);
	Cell cell6 = row.createCell(6);
	// Create the drawing patriarch. This is the top level container for all
	// shapes.
	Drawing drawing = sheet1.createDrawingPatriarch();

	// add a picture shape
	ClientAnchor anchor = createHelper.createClientAnchor();
	int x1 = 0;
	int y1 = 0;
	int x2 = 800;
	int y2 = 255;
	// 前四个参数x1,y1,x2,y2 是图片以所在单元格位基础的坐标
	// 后四个参数代表作在的行和列的单元格,从0开始
	HSSFClientAnchor anchor2 = new HSSFClientAnchor(x1, y1, x2, y2,
		(short) 6, 1, (short) 6, 1);
	// set top-left corner of the picture,
	// subsequent call of Picture#resize() will operate relative to it
	// 设置图片左上角的位置

	// 添加第一张图片
	anchor.setCol1(6);
	anchor.setCol2(7);
	anchor.setRow1(1);
	anchor.setRow2(2);

	Picture pict = drawing.createPicture(anchor, pictureIdx);

	// 添加第二张图片

	anchor2.setAnchorType(2);
	Picture pict2 = drawing.createPicture(anchor2, pictureIdx2);

	// auto-size picture relative to its top-left corner
	// Picture.resize() works only for JPEG and PNG. Other formats are not
	// yet supported.
	pict.resize();

	// resize 上面设置的坐标就白设了
	// pict2.resize();

	FileOutputStream fileOut = new FileOutputStream("resources/output.xls");
	wb.write(fileOut);
	fileOut.close();
	//
	// Workbook wb = new XSSFWorkbook();
	// FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
	// wb.write(fileOut);
	// fileOut.close();

    }

    public static void main(String[] args) {
	try {
	    OutputExcel.outputExcel();
	} catch (IOException e) {
	    // TODO Auto-generated catch block
	    e.printStackTrace();
	}
    }
}