apache POI 操作 Excel

lishenghan

　　

参考文档:    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();
}
}
}