love fish大鵬一曰同風起，扶搖直上九萬里

公告

留言簿(15)

隨筆分類(493)

隨筆檔案(498)

相冊

MIX

閱讀排行榜

常用鏈接

統計

隨筆 - 500
文章 - 0
評論 - 155
引用 - 0

積分與排名

積分 - 527092
排名 - 91

friends

POI 復制excel一行到另外一個sheet頁(轉)

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

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;

public class RowCopy {

/**

* @param args

* @throws IOException

* @throws FileNotFoundException

*/

@SuppressWarnings("deprecation")

public static void main(String[] args) {

try {

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(

"exlsample.xls"));

HSSFWorkbook wb = new HSSFWorkbook(fs);

//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

");

} catch (Exception e) {

e.printStackTrace();

}

public static void copyRows(HSSFWorkbook wb, String pSourceSheetName,

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;

if ((pStartRow == -1) || (pEndRow == -1)) {

return;

}

sourceSheet = wb.getSheet(pSourceSheetName);

targetSheet = wb.getSheet(pTargetSheetName);

// 拷貝合并的單元格

for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) {

region = sourceSheet.getMergedRegionAt(i);

if ((region.getRowFrom() >= pStartRow)

&& (region.getRowTo() <= pEndRow)) {

targetRowFrom = region.getRowFrom() - pStartRow + pPosition;

targetRowTo = region.getRowTo() - pStartRow + pPosition;

region.setRowFrom(targetRowFrom);

region.setRowTo(targetRowTo);

targetSheet.addMergedRegion(region);

}

// 設置列寬

for (i = pStartRow; i <= pEndRow; i++) {

sourceRow = sourceSheet.getRow(i);

if (sourceRow != null) {

for (j = sourceRow.getLastCellNum(); j > sourceRow

.getFirstCellNum(); j--) {

targetSheet

.setColumnWidth(j, sourceSheet.getColumnWidth(j));

targetSheet.setColumnHidden(j, false);

}

break;

}

// 拷貝行并填充數據

for (; i <= pEndRow; i++) {

sourceRow = sourceSheet.getRow(i);

if (sourceRow == null) {

continue;

}

targetRow = targetSheet.createRow(i - pStartRow + pPosition);

targetRow.setHeight(sourceRow.getHeight());

for (j = sourceRow.getFirstCellNum(); j < sourceRow

.getPhysicalNumberOfCells(); j++) {

sourceCell = sourceRow.getCell(j);

if (sourceCell == null) {

continue;

}

targetCell = targetRow.createCell(j);

targetCell.setEncoding(sourceCell.getEncoding());

targetCell.setCellStyle(sourceCell.getCellStyle());

cType = sourceCell.getCellType();

targetCell.setCellType(cType);

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;

}

private static String parseFormula(String pPOIFormula) {

final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$

StringBuffer result = null;

int index;

result = new StringBuffer();

index = pPOIFormula.indexOf(cstReplaceString);

if (index >= 0) {

result.append(pPOIFormula.substring(0, index));

result.append(pPOIFormula.substring(index

+ cstReplaceString.length()));

} else {

result.append(pPOIFormula);

}

return result.toString();

}

posted on 2007-11-14 16:51 liaojiyong 閱讀(8317) 評論(1) 編輯收藏所屬分類: POI

# re: POI 復制excel一行到另外一個sheet頁(轉)[未登錄] 2011-12-26 16:02 zz

love fish大鵬一曰同風起，扶搖直上九萬里

導航

公告

留言簿(15)

隨筆分類(493)

隨筆檔案(498)

相冊

閱讀排行榜

常用鏈接

統計

積分與排名

friends

link

最新評論

POI 復制excel一行到另外一個sheet頁(轉)

評論


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: POI操作Excel文檔－中級篇（轉） POI操作Excel文檔－基礎篇（轉） POI Excel整理(轉) POI 復制excel一行到另外一個sheet頁(轉)