POI:
POI简介
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
HSSF概况
HSSF 是Horrible SpreadSheet Format的缩写,通过HSSF,你可以用纯Java代码来读取、写入、修改Excel文件。HSSF 为读取操作提供了两类API:usermodel和eventusermodel,即“用户模型”和“事件-用户模型”。
POI EXCEL文档结构类
HSSFWorkbook excel文档对象:
HSSFWorkbook有五个构造器
-
HSSFWorkbook()
-
HSSFWorkbook(java.io.InputStream s)
-
HSSFWorkbook(java.io.InputStream s, boolean preserveNodes)
-
HSSFWorkbook(POIFSFileSystem fs)
-
HSSFWorkbook(POIFSFileSystem fs, boolean preserveNodes)
-
HSSFSheet excel的sheet HSSFRow excel的行
-
HSSFCell excel的单元格 HSSFFont excel字体
-
HSSFName 名称 HSSFDataFormat 日期格式
-
HSSFHeader sheet头
-
HSSFFooter sheet尾
-
HSSFCellStyle cell样式
-
HSSFDateUtil 日期
-
HSSFPrintSetup 打印
-
HSSFErrorConstants 错误信息表
EXCEL常用操作方法
1、 得到Excel常用对象
POIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls"));
//得到Excel工作簿对象
HSSFWorkbook wb = new HSSFWorkbook(fs);
//得到Excel工作表对象
HSSFSheet sheet = wb.getSheetAt(0);
//得到Excel工作表的行
HSSFRow row = sheet.getRow(i);
//得到Excel工作表指定行的单元格
HSSFCell cell = row.getCell((short) j);
cellStyle = cell.getCellStyle();//得到单元格样式
2、建立Excel常用对象
HSSFWorkbook wb = new HSSFWorkbook();//创建Excel工作簿对象
HSSFSheet sheet = wb.createSheet("new sheet");//创建Excel工作表对象
HSSFRow row = sheet.createRow((short)0); //创建Excel工作表的行
cellStyle = wb.createCellStyle();//创建单元格样式
row.createCell((short)0).setCellStyle(cellStyle); //创建Excel工作表指定行的单元格
row.createCell((short)0).setCellValue(1); //设置Excel工作表的值
3、设置sheet名称和单元格内容
wb.setSheetName(1, "第一张工作表",HSSFCell.ENCODING_UTF_16);
cell.setEncoding((short) 1);
cell.setCellValue("单元格内容");
4、取得sheet的数目
wb.getNumberOfSheets()
5、 根据index取得sheet对象
HSSFSheet sheet = wb.getSheetAt(0);
6、取得有效的行数
int rowcount = sheet.getLastRowNum();
7、取得一行的有效单元格个数
row.getLastCellNum();
8、单元格值类型读写
cell.setCellType(HSSFCell.CELL_TYPE_STRING); //设置单元格为STRING类型
cell.getNumericCellValue();//读取为数值类型的单元格内容
9、设置列宽、行高
sheet.setColumnWidth((short)column,(short)width);
row.setHeight((short)height);
10、添加区域,合并单元格
Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo
,(short)columnTo);//合并从第rowFrom行columnFrom列
sheet.addMergedRegion(region);// 到rowTo行columnTo的区域
//得到所有区域
sheet.getNumMergedRegions()
11、保存Excel文件
FileOutputStream fileOut = new FileOutputStream(path);
wb.write(fileOut);
12、根据单元格不同属性返回字符串数值
public String getCellStringValue(HSSFCell cell) {
String cellValue = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING://字符串类型
cellValue = cell.getStringCellValue();
if(cellValue.trim().equals("")||cellValue.trim().length()<=0)
cellValue=" ";
break;
case HSSFCell.CELL_TYPE_NUMERIC: //数值类型
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA: //公式
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
cellValue=" ";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
break;
case HSSFCell.CELL_TYPE_ERROR:
break;
default:
break;
}
return cellValue;
}
13、常用单元格边框格式
HSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框
style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
14、设置字体和内容位置
HSSFFont f = wb.createFont();
f.setFontHeightInPoints((short) 11);//字号
f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗
style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
style.setRotation(short rotation);//单元格内容的旋转的角度
HSSFDataFormat df = wb.createDataFormat();
style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式
cell.setCellFormula(string);//给单元格设公式
style.setRotation(short rotation);//单元格内容的旋转的角度
15、插入图片
//先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));
ImageIO.write(bufferImg,"jpg",byteArrayOut);
//读进一个excel模版
FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");
fs = new POIFSFileSystem(fos);
//创建一个工作薄
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);
patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
16、调整工作表位置
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("format sheet");
HSSFPrintSetup ps = sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight((short)1);
ps.setFitWidth((short)1);
17、设置打印区域
HSSFSheet sheet = wb.createSheet("Sheet1");
wb.setPrintArea(0, "$A$1:$C$2");
18、标注脚注
HSSFSheet sheet = wb.createSheet("format sheet");
HSSFFooter footer = sheet.getFooter()
footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
19、在工作单中清空行数据,调整行位置
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("row sheet");
// Create various cells and rows for spreadsheet.
// Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)
sheet.shiftRows(5, 10, -5);
20、选中指定的工作表
HSSFSheet sheet = wb.createSheet("row sheet");
heet.setSelected(true);
21、工作表的放大缩小
HSSFSheet sheet1 = wb.createSheet("new sheet");
sheet1.setZoom(1,2); // 50 percent magnification
22、头注和脚注
23、自定义颜色
HSSFCellStyle style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.LIME.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.RED.index);
style.setFont(font);
cell.setCellStyle(style);
24、填充和颜色设置
HSSFCellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.AQUA.index);
style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("X");
style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.ORANGE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cell.setCellStyle(style);
25、强行刷新单元格公式
HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);
private static void updateFormula(Workbook wb,Sheet s,int row){
Row r=s.getRow(row);
Cell c=null;
FormulaEcaluator eval=null;
if(wb instanceof HSSFWorkbook)
eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);
else if(wb instanceof XSSFWorkbook)
eval=new XSSFFormulaEvaluator((XSSFWorkbook) wb);
for(int i=r.getFirstCellNum();i<r.getLastCellNum();i++){
c=r.getCell(i);
if(c.getCellType()==Cell.CELL_TYPE_FORMULA)
eval.evaluateFormulaCell(c);
}
}
说明:FormulaEvaluator提供了evaluateFormulaCell(Cell cell)方法,计算公式保存结果,但不改变公式。而evaluateInCell(Cell cell) 方法是计算公式,并将原公式替换为计算结果,也就是说该单元格的类型不在是Cell.CELL_TYPE_FORMULA而是Cell.CELL_TYPE_NUMBERIC。HSSFFormulaEvaluator提供了静态方法evaluateAllFormu
laCells(HSSFWorkbook wb) ,计算一个Excel文件的所有公式,用起来很方便。
public void test() throws FileNotFoundException, IOException {
String filepath="E:\\Java全集\\Java基础视频\\上海传智播客\\黑马32框架\\项目一:物流BOS系统(58-71天)\\BOS-day05\\BOS-day05\\资料\\分区导入测试数据.xls";
System.out.println(filepath);
File file=new File(filepath);
//包装一个Excel文件对象
HSSFWorkbook hworkbook=new HSSFWorkbook(new FileInputStream(file));
//读取文件中的第一个Sheet标签页
HSSFSheet hssfSheet=hworkbook.getSheetAt(0);
//遍历标签页的所有行
for (Row row: hssfSheet) {
//跳过标题
if (row.getRowNum()==0) {
continue;
}
//换行
System.out.println();
//遍历单元格
for (Cell cell : row) {
String value=cell.getStringCellValue();
System.out.println(value);
}
}
}
测试代码:
package com.leo.bos.test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;
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;
/**
* poi测试
* @author leoi555
*
*/
public class POITest1 {
/**
* HSSFWorkbook类,创建excel表空的excel:
*/
public void createExcel(String filename) {
//创建一个空的workbook
HSSFWorkbook workbook=new HSSFWorkbook();
FileOutputStream outputStream=null;//创建输出流
try {
outputStream=new FileOutputStream(filename);
//调用HSSFWorkbook中的类的write方法
workbook.write(outputStream);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println(e.toString());
}finally {
try {
outputStream.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 用POIFSFileSystem类读取excel表格
* 然后向另一个excel表中写入
* POIFSFileSystem()类
Constructor, intended for writing
POIFSFileSystem(java.io.InputStream stream)
Create a POIFSFileSystem from an InputStream
*/
public void readExcel(String filepath,String filename) {
FileInputStream inputStream=null;
HSSFWorkbook workbook=null;
try {
inputStream=new FileInputStream(filepath);
//创建带有输入流的类的对象
POIFSFileSystem frFileSystem=new POIFSFileSystem(inputStream);
//创建HSSFWorkbook(POIFSFileSystem fs) ;
workbook=new HSSFWorkbook(frFileSystem);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
inputStream.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//读取后然后输出
FileOutputStream outputStream=null;
try {
outputStream=new FileOutputStream(filename);
workbook.write(outputStream);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/*
* 创建Sheet通过传入的文件名
* public HSSFSheet createSheet(java.lang.String sheetname)
* public HSSFSheet createSheet()
*/
public void createSheet(String filename) {
HSSFWorkbook workbook = new HSSFWorkbook();
workbook.createSheet();//创建workbook的sheet0
workbook.createSheet();//sheet1
workbook.createSheet("test");
FileOutputStream out = null;
try{
out = new FileOutputStream(filename);
workbook.write(out);
}catch(IOException e){
System.out.println(e.toString());
}finally{
try {
out.close();
System.out.println("创建成功");
}catch(IOException e){
System.out.println(e.toString());
}
}
}
/**
* 复制sheet
* public HSSFSheet cloneSheet(int sheetNum)
*/
public void copySheet(String filename) {
FileInputStream in = null;
HSSFWorkbook workbook = null;
try{
//先进型读取
in = new FileInputStream(filename);
POIFSFileSystem fs = new POIFSFileSystem(in);
workbook = new HSSFWorkbook(fs);
}catch(IOException e){
System.out.println(e.toString());
}finally{
try{
in.close();
//读取成功
System.out.println("读取成功");
}catch (IOException e){
System.out.println(e.toString());
}
}
//复制0和一sheet就是在这个excel中多加入一个空白页同时有前面的数据
workbook.cloneSheet(0);
workbook.cloneSheet(1);
FileOutputStream out = null;
try{
//然后在进行写入
out = new FileOutputStream(filename);
workbook.write(out);
}catch(IOException e){
System.out.println(e.toString());
}finally{
try {
out.close();
System.out.println("复制成功");
}catch(IOException e){
System.out.println(e.toString());
}
}
}
/**
* 删除sheet后然后在写到哪个文件中
* public void removeSheetAt(int index)
* public int getSheetIndex(java.lang.String name)
*更改sheet的名称
* public void setSheetName(int sheet, java.lang.String name)
set the sheet name. Will throw IllegalArgumentException if the name
is greater than 31 chars or contains /\?*[]
* @param args
*/
public void deleteSheet(String filename, String sheetname,String tofilename) {
FileInputStream in = null;
HSSFWorkbook workbook = null;
try{
//先进型读取
in = new FileInputStream(filename);
POIFSFileSystem fs = new POIFSFileSystem(in);
workbook = new HSSFWorkbook(fs);
}catch(IOException e){
System.out.println(e.toString());
}finally{
try{
in.close();
//读取成功
System.out.println("读取成功");
}catch (IOException e){
System.out.println(e.toString());
}
}
//更改sheet名字:
workbook.setSheetName(0, "修改1sheet");
int sheet=workbook.getSheetIndex(sheetname);
System.out.println("删除的页数:"+sheet);
workbook.removeSheetAt(sheet);
System.out.println("删除sheet页成功");
System.out.println("Sheet0 = " + workbook.getSheetIndex("Sheet0"));
System.out.println("test = " + workbook.getSheetIndex("test"));
// 然后再写回到文件;
FileOutputStream out = null;
try{
out = new FileOutputStream(tofilename);
workbook.write(out);
}catch(IOException e){
System.out.println(e.toString());
}finally{
try {
out.close();
}catch(IOException e){
System.out.println(e.toString());
}
}
}
/**
* HSSFRow类定义
用POI在工作表里作成一个行,可以用「HSSFRow」类,
protected HSSFRow()
protected HSSFRow(Workbook book, Sheet sheet, int rowNum)
Creates new HSSFRow from scratch.
protected HSSFRow(Workbook book, Sheet sheet, RowRecord record)
Creates an HSSFRow from a low level RowRecord object.
public int getFirstRowNum()
public int getLastRowNum()获得最后一行的序列号
public int getPhysicalNumberOfRows()实际存在的行的总数
* @param args
*/
public void createRow(String filename) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();//创建空白的sheet
System.out.println("创建行之前的状态:");
System.out.println("First:" + sheet.getFirstRowNum());//sheet.getFirstRowNum()获取sheet的第一行行号
System.out.println("Last:" + sheet.getLastRowNum());//getLastRowNum()获取sheet的最后行行号
System.out.println("Total:" + sheet.getPhysicalNumberOfRows() + "\n");// getPhysicalNumberOfRows()获取sheet的行总数
sheet.createRow(0);
System.out.println("创建第一行后的状态:");
System.out.println("First:" + sheet.getFirstRowNum());
System.out.println("Last:" + sheet.getLastRowNum());
System.out.println("Total:" + sheet.getPhysicalNumberOfRows() + "\n");
//创建行号为1的行,excel中的第2行
HSSFRow createRow = sheet.createRow(1);
////创建上面行的第一个单元格,第二个单元格书写其中的值
HSSFCell createCell = createRow.createCell(0);
createCell.setCellValue("单元格1:");
HSSFCell createCell2 = createRow.createCell(1);
createCell2.setCellValue("单元格2:");
FileOutputStream out = null;
try{
out = new FileOutputStream(filename);
workbook.write(out);
System.out.println("创建成功");
}catch(IOException e){
System.out.println(e.toString());
}finally{
try {
out.close();
}catch(IOException e){
System.out.println(e.toString());
}
}
}
/**
* 读取行
* public HSSFRow getRow(int rownum)
* //获取行
HSSFRow row2 = sheet.getRow(1);
//获得单元格
HSSFCell cell=row2.getCell(1);
//取得其中的值
System.out.println(cell.getStringCellValue());
*/
public void readRow(String filename) {
//先进行读取出这个excel文件
FileInputStream in = null;
HSSFWorkbook workbook = null;
try{
//先进型读取
in = new FileInputStream(filename);
POIFSFileSystem fs = new POIFSFileSystem(in);
workbook = new HSSFWorkbook(fs);
}catch(IOException e){
System.out.println(e.toString());
}finally{
try{
in.close();
//读取成功
System.out.println("读取成功");
}catch (IOException e){
System.out.println(e.toString());
}
}
//读取第一个sheet
HSSFSheet sheet=workbook.getSheetAt(0);
//获取行
HSSFRow row2 = sheet.getRow(1);
//获得单元格
HSSFCell cell=row2.getCell(1);
//取得其中的值
System.out.println(cell.getStringCellValue());
}
/**
* 通过向单元格中写入不同的数据
* @param args
*/
public void writeExcel(String filname) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = sheet.createRow(1);//创建第二行
HSSFCell cell1 = row.createCell((short)0);//2,1格
cell1.setCellValue(true);//写入true
HSSFCell cell2 = row.createCell((short)1);//2,2格
Calendar cal = Calendar.getInstance();//Calendar类获取实例
cell2.setCellValue(cal);//写入Calendar型对象cal
HSSFCell cell3 = row.createCell((short)2);//2,3格
Date date = new Date(); //日期型
cell3.setCellValue(date);//写入日期型
HSSFCell cell4 = row.createCell((short)3);//2,4格
cell4.setCellValue(150);//写入150
HSSFCell cell5 = row.createCell((short)4);//2.5格
cell5.setCellValue("hello");//写入hello
HSSFRow row2 = sheet.createRow(2);//第三行
HSSFCell cell6 = row2.createCell((short)0);//3,1格
// cell6.setCellErrorValue(HSSFErrorConstants.ERROR_NUM);//写入error型
FileOutputStream out = null;
try{
out = new FileOutputStream(filname);
workbook.write(out);
}catch(IOException e){
System.out.println(e.toString());
}finally{
try {
out.close();
System.out.println("写入类型值完毕!");
}catch(IOException e){
System.out.println(e.toString());
}
}
}
/**
*
* @param args
*/
public static void main(String[] args) {
POITest1 test1=new POITest1();
//test1.createExcel("test.xls");
//test1.readExcel("test.xls", "sample2.xls");
//test1.createSheet("test.xls");
//test1.copySheet("test.xls");
//test1.deleteSheet("test.xls", "Sheet1","test1.xls");
//test1.createRow("test2.xls");
//test1.readRow("test2.xls");
test1.writeExcel("test3.xls");
}
}
大多数只要是看文档基本上操作没什么问题,这只是基本的API的操作: