Apache POI Excel_综合

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) {//创建一个空的workbookHSSFWorkbook workbook=new HSSFWorkbook();FileOutputStream outputStream=null;//创建输出流try {outputStream=new FileOutputStream(filename);//调用HSSFWorkbook中的类的write方法workbook.write(outputStream);} catch (FileNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();System.out.println(e.toString());}finally {try {outputStream.close();} catch (IOException e) {// TODO Auto-generated catch blocke.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 blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {try {inputStream.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}//读取后然后输出FileOutputStream outputStream=null;try {outputStream=new FileOutputStream(filename);workbook.write(outputStream);} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/** 创建Sheet通过传入的文件名* public HSSFSheet createSheet(java.lang.String sheetname)* public HSSFSheet createSheet()*/public void createSheet(String filename) {HSSFWorkbook workbook = new HSSFWorkbook();workbook.createSheet();//创建workbook的sheet0workbook.createSheet();//sheet1workbook.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 nameis 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();//创建空白的sheetSystem.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());}}//读取第一个sheetHSSFSheet 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);//写入trueHSSFCell cell2 = row.createCell((short)1);//2,2格Calendar cal = Calendar.getInstance();//Calendar类获取实例cell2.setCellValue(cal);//写入Calendar型对象calHSSFCell 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);//写入150HSSFCell cell5 = row.createCell((short)4);//2.5格cell5.setCellValue("hello");//写入helloHSSFRow 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的操作：