http://blog.csdn.net/frognoble/archive/2007/03/14/1528827.aspx
package
cosmic.leon;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
import
java.io.FileInputStream;
import
java.io.FileNotFoundException;
import
java.io.FileOutputStream;
import
java.io.IOException;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
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.hssf.util.Region;
import
org.apache.poi.poifs.filesystem.POIFSFileSystem;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
public
class
RowCopy
...
{
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
/** *//**
* @param args
* @throws IOException
* @throws FileNotFoundException
*/
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
@SuppressWarnings("deprecation")
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public static void main(String[] args) ...{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
try ...{
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
"exlsample.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fs);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
//source ,target 为,源sheet 页和目标sheet页,
copyRows(wb, "source", "target", 3, 4, 20);
FileOutputStream fileOut = new FileOutputStream("exlsample.xls");
wb.write(fileOut);
fileOut.flush();
fileOut.close();
System.out.println("Operation finished......");
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
} catch (Exception e) ...{
e.printStackTrace();
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
public static void copyRows(HSSFWorkbook wb, String pSourceSheetName,
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
String pTargetSheetName, int pStartRow, int pEndRow, int pPosition) ...{
HSSFRow sourceRow = null;
HSSFRow targetRow = null;
HSSFCell sourceCell = null;
HSSFCell targetCell = null;
HSSFSheet sourceSheet = null;
HSSFSheet targetSheet = null;
Region region = null;
int cType;
int i;
short j;
int targetRowFrom;
int targetRowTo;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
if ((pStartRow == -1) || (pEndRow == -1)) ...{
return;
}
sourceSheet = wb.getSheet(pSourceSheetName);
targetSheet = wb.getSheet(pTargetSheetName);
// 拷贝合并的单元格
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) ...{
region = sourceSheet.getMergedRegionAt(i);
if ((region.getRowFrom() >= pStartRow)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
&& (region.getRowTo() <= pEndRow)) ...{
targetRowFrom = region.getRowFrom() - pStartRow + pPosition;
targetRowTo = region.getRowTo() - pStartRow + pPosition;
region.setRowFrom(targetRowFrom);
region.setRowTo(targetRowTo);
targetSheet.addMergedRegion(region);
}
}
// 设置列宽
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
for (i = pStartRow; i <= pEndRow; i++) ...{
sourceRow = sourceSheet.getRow(i);
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
if (sourceRow != null) ...{
for (j = sourceRow.getLastCellNum(); j > sourceRow
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
.getFirstCellNum(); j--) ...{
targetSheet
.setColumnWidth(j, sourceSheet.getColumnWidth(j));
targetSheet.setColumnHidden(j, false);
}
break;
}
}
// 拷贝行并填充数据
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
for (; i <= pEndRow; i++) ...{
sourceRow = sourceSheet.getRow(i);
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
if (sourceRow == null) ...{
continue;
}
targetRow = targetSheet.createRow(i - pStartRow + pPosition);
targetRow.setHeight(sourceRow.getHeight());
for (j = sourceRow.getFirstCellNum(); j < sourceRow
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
.getPhysicalNumberOfCells(); j++) ...{
sourceCell = sourceRow.getCell(j);
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
if (sourceCell == null) ...{
continue;
}
targetCell = targetRow.createCell(j);
targetCell.setEncoding(sourceCell.getEncoding());
targetCell.setCellStyle(sourceCell.getCellStyle());
cType = sourceCell.getCellType();
targetCell.setCellType(cType);
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
switch (cType) ...{
case HSSFCell.CELL_TYPE_BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
System.out.println("--------TYPE_BOOLEAN:"
+ targetCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
targetCell
.setCellErrorValue(sourceCell.getErrorCellValue());
System.out.println("--------TYPE_ERROR:"
+ targetCell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
// parseFormula这个函数的用途在后面说明
targetCell.setCellFormula(parseFormula(sourceCell
.getCellFormula()));
System.out.println("--------TYPE_FORMULA:"
+ targetCell.getCellFormula());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
targetCell.setCellValue(sourceCell.getNumericCellValue());
System.out.println("--------TYPE_NUMERIC:"
+ targetCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
targetCell
.setCellValue(sourceCell.getRichStringCellValue());
System.out.println("--------TYPE_STRING:" + i
+ targetCell.getRichStringCellValue());
break;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
private static String parseFormula(String pPOIFormula) ...{
final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
StringBuffer result = null;
int index;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
result = new StringBuffer();
index = pPOIFormula.indexOf(cstReplaceString);
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
if (index >= 0) ...{
result.append(pPOIFormula.substring(0, index));
result.append(pPOIFormula.substring(index
+ cstReplaceString.length()));
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
} else ...{
result.append(pPOIFormula);
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
return result.toString();
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
}
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)