Apache POI SpreadSheet的一些简单应用（二）

snake_l

我用的版本是 poi-3.5,您可以去官方上下载。希望对大家能有些帮助:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
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.hssf.util.CellReference;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.CellRangeAddress;

public class XZou {
/**
* copy一个Excel中指定的sheet而后制作成另外一个Excel文件
* @param srcPath 源路径
* @param srcFileName 原文件名
* @param sheetNames 指定的sheet名称集合
* @return 新的文件对象
*/
public static File copySpecialSheet(String srcPath,String srcFileName,String[] sheetNames,String targetPath){
srcPath += "/";
File src = new File(srcPath + srcFileName);
try {
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(src)));//load src excel
int sheetCounts = wb.getNumberOfSheets();//总的sheet数量
List<Integer> xb = new ArrayList<Integer>();//下标值集合
int xiaBiao = 0;
for(int i = 0; i<sheetCounts; i++){
String sName = wb.getSheetName(i);
boolean boo = false;
for(String name: sheetNames){
if(name.equals(sName)){
boo = true;
break;
}
}
if(boo){
xiaBiao++;
}else{
xb.add(xiaBiao);
}
}
for(int flag: xb){
wb.removeSheetAt(flag);//删除指定的sheet的下标
}
OutputStream out = null;
try{
File targetFile = new File(targetPath + "/" + System.currentTimeMillis() + ".xls");//在targetPath下生成新的excel文件。
out = new FileOutputStream(targetFile);
/*
sheetCounts = wb.getNumberOfSheets();
for(int i = 0; i<sheetCounts; i++){
//wb.setSheetHidden(i, true);// 把所有的sheet工作表都给隐藏掉
wb.setSheetHidden(i,2);//把所有的sheet工作表都给隐藏掉,0=显示 1=隐藏 2=非常隐秘(我在Excel中找不到。但是程序找得到~_~)
}
*/
wb.write(out);
return targetFile;
}catch(Exception ex){
ex.printStackTrace();
throw new RuntimeException("生成新Excel文件失败:",ex);
}finally{
try{
if(out!=null)
out.close();
}catch(IOException ex){
ex.printStackTrace();
}
}
} catch (FileNotFoundException e) {
throw new RuntimeException("文件不存在" ,e);
} catch (IOException e) {
throw new RuntimeException("加载XLS文件出现异常:",e);
}
}
/**
* 打印一个sheet中的每个合并单元格区的位置
* @param sheet
*/
public static void sysRange(HSSFSheet sheet){
int count = sheet.getNumMergedRegions();//找到当前sheet单元格中共有多少个合并区域
for(int i = 0; i<count; i++){
CellRangeAddress range = sheet.getMergedRegion(i);//一个合并单元格代表 CellRangeAddress
System.out.println(range.formatAsString());//打印合并区域的字符串表示方法 如： B2:C4
System.out.println(range.getFirstRow() + "." + range.getLastRow() + ":"
+ range.getFirstColumn() + "." + range.getLastColumn() );//打印起始行、结束行、起始列、结束列
}
}
/**
* 根据一行找出有效的起始列号
* @param row
* @return
*/
public int getStartCell(HSSFRow row){
if(row==null){
throw new RuntimeException("无效行");
}
int start = 0;
for(int i = 0; i<row.getLastCellNum(); i++){
HSSFCell cell = row.getCell(i);
if(cell!=null){
if(cell.getCellType()!=HSSFCell.CELL_TYPE_BLANK){
start = i;
break;
}
}
}
return start;
}

/**
* 根据一行找出有效的结尾列,空白列不算、但是在合并区域时算
* @param row
* @return
*/
public int getLastCell(HSSFRow row){
if(row==null){
throw new RuntimeException("无效行");
}
HSSFSheet sheet = row.getSheet();
int merged = sheet.getNumMergedRegions();//获取单元格区域数
int end = 0;
for(int x = row.getLastCellNum() - 1; x>=0; x--){
HSSFCell cell = row.getCell(x);//获取列
if(cell==null)
continue;
if(cell.getCellType()==HSSFCell.CELL_TYPE_BLANK){//空白单元格
int rowNumber = cell.getRowIndex();
int cellNumber = cell.getColumnIndex();
boolean flag = false;
for(int i = 0; i<merged; i++){
CellRangeAddress rane = sheet.getMergedRegion(i);
if(rowNumber>=rane.getFirstRow()&&rowNumber<=rane.getLastRow()){//确立在行里面
if(cellNumber>=rane.getFirstColumn()&&cellNumber<=rane.getLastColumn()){//确立在列里面
flag = true;
break;
}
}

}
if(flag){//说明当前单元格是空白单元格并且在合并区域中.可以认定为有效结束列
end = x;
break;
}
}else{//不为空白单元格
end = x;
break;
}
}

return end;
}

/**
* 判断一行是否为空行或指定的前几列
* @param row
* @return
*/
public boolean checkRowIsNull(HSSFRow row,int ...cd){
boolean boo = true;
if(row==null){
return boo;
}
int length = 0;
int j = 0;
if(cd.length==0){
j = 0;
length = row.getLastCellNum();
}else{
j = 1;
length = cd[0];
}
for(; j<length; j++){
HSSFCell cell = row.getCell(j);
if(cell!=null){
if(cell.getCellType()!=HSSFCell.CELL_TYPE_BLANK){
if(cell.getCellType()==HSSFCell.CELL_TYPE_STRING){
if(!cell.getRichStringCellValue().toString().trim().equals("")){
boo = false;
break;
}
}else{
boo = false;
}
}
}
}
return boo;
}
/**
* 根据单元格坐标得出相应的值 如：B3
* @param cellRowCode :坐标
* @param sheet ：工作表
* @return 返回值
*/
public Serializable getValueByCellCode(String cellRowCode,HSSFSheet sheet){
String thisSheetName = sheet.getSheetName();
CellReference ref =  new CellReference(cellRowCode);
int xy[] = {ref.getRow(),ref.getCol()};
HSSFCell cell = sheet.getRow(xy[1]-1).getCell(xy[0]);
switch (cell.getCellType()){
case HSSFCell.CELL_TYPE_BLANK:
return "";
case HSSFCell.CELL_TYPE_NUMERIC:
return (cell.getNumericCellValue());
case HSSFCell.CELL_TYPE_STRING:
return cell.getRichStringCellValue().toString() ;
case HSSFCell.CELL_TYPE_FORMULA:
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet.getWorkbook());   
try{
evaluator.evaluateFormulaCell(cell);//检测公式有效性
}catch(java.lang.IllegalArgumentException ex){
throw new RuntimeException("错误的单元格["+thisSheetName+"->"+cellRowCode+"]");
}
if(evaluator.evaluateFormulaCell(cell)==HSSFCell.CELL_TYPE_ERROR){
throw new RuntimeException("错误的单元格["+thisSheetName+"->"+cellRowCode+"]");
}
if(evaluator.evaluateFormulaCell(cell)==HSSFCell.CELL_TYPE_NUMERIC){
return cell.getNumericCellValue();
}else if(evaluator.evaluateFormulaCell(cell)==HSSFCell.CELL_TYPE_STRING){
return cell.getRichStringCellValue().toString();
}
case HSSFCell.CELL_TYPE_BOOLEAN:
return (cell.getBooleanCellValue());
default:
System.out.print("*");
break;
}
return null;
}
public static void main(String[] args) throws Exception {
//copySpecialSheet("c:/","test.xls",new String[]{"Sheet1","Sheet2"},"d:/");//把c盘下的test.xls文件中的Sheet1和Sheet2工作表拷贝出来然后生成一个新的Excel放到d盘下

HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream("c:/test.xls")));
HSSFSheet sheet = wb.getSheet("测试");//得到名字叫 测试 的一个工作表。
sysRange(sheet);//

//.....
}
}