作者:乔山办公网日期:
返回目录:excel表格制作
首先要读取xlsx文件,创建一个基百于硬盘的FileItem工厂 ,DiskFileItemFactory
用poi读文件内容,获取到表格的行,不知道有没有直接复制某度行的方法,但最笨的知方法,可以读取每一个单元格,再新增一行道,将数据一次插入
HSSFSheet sheet = workbook.getSheetAt(0);先创建版一个sheet对象
HSSFRow row =sheet.getRow(0);以第一行为例
short lenth=row.getLastCellNum() ; 获取列数
遍历列 ,用row.getCell() 获取列值
创建新行 HSSFRow newRow = sheet.createRow(sheet.getLastRowNum()+1);
再遍历赋值 HSSFCell cell = newRow.createCell(i);
之前只做过poi的导入导出,所权以没有实测,你可以尝试一下
行、列,分别轮循 ,,,,,,,,另一个sheet中添加 。。。。。。。。。。。。。。
POI 这样的框架,创建新的SHEET、再拷贝
~~~~~~~~
你这个问题还真有深度,我也觉得POI应该提供些复制行或复制列的接口。还好他提供的基本接口比较全面,我们可以自己动手写代码来实现。
以下是我写的一段代码,支持多种类型的单元格(包括公式),完全可以满足你的要求,具体参数要在程序运行时输入。
---------------------------------------------------------------
package poi_sheet_copy;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStreamReader;
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 {
public static void main(String[] args) {
try {
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
System.out.print("输入需要操e79fa5e98193e4b893e5b19e362作的excel文件路径:");
String filePath = br.readLine();
System.out.print("输入需要操作的来源sheet名称:");
String sourceSheetName = br.readLine();
System.out.print("输入来源的起始行号(从0开始):");
int startRow = Integer.parseInt(br.readLine());
System.out.print("输入来源的终止行号(从0开始):");
int endRow = Integer.parseInt(br.readLine());
System.out.print("输入需要操作的目标sheet名称:");
String targetSheetName = br.readLine();
System.out.print("输入目标的行号偏移量:");
int position = Integer.parseInt(br.readLine());
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));
HSSFWorkbook wb = new HSSFWorkbook(fs);
copyRows(wb, sourceSheetName, targetSheetName, startRow, endRow, position);
FileOutputStream fileOut = new FileOutputStream(filePath);
wb.write(fileOut);
fileOut.flush();
fileOut.close();
System.out.println("操作完成!");
} 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;
if ((pStartRow == -1) || (pEndRow == -1)) {
return;
}
sourceSheet = wb.getSheet(pSourceSheetName);
targetSheet = wb.getSheet(pTargetSheetName);
// 拷贝合并的单元格
for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
region = sourceSheet.getMergedRegionAt(i);
if ((region.getRowFrom() >= pStartRow) && (region.getRowTo() <= pEndRow)) {
int targetRowFrom = region.getRowFrom() - pStartRow + pPosition;
int targetRowTo = region.getRowTo() - pStartRow + pPosition;
region.setRowFrom(targetRowFrom);
region.setRowTo(targetRowTo);
targetSheet.addMergedRegion(region);
}
}
// 拷贝行并填充数据
for (int i = 0; i <= pEndRow; i++) {
sourceRow = sourceSheet.getRow(i);
if (sourceRow == null) {
continue;
}
targetRow = targetSheet.createRow(i - pStartRow + pPosition);
targetRow.setHeight(sourceRow.getHeight());
for (short 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());
int 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:
targetCell.setCellFormula(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.getStringCellValue());
//System.out.println("--------TYPE_STRING:" + targetCell.getStringCellValue());
break;
}
}
}
}
}
---------------------------------------------------------------
以下是运行时的例子,需要输入些参数。
输入需要操作的excel文件路径:D:\test.xls
输入需要操作的来源sheet名称:sheet1
输入来源的起始行号(从0开始):2
输入来源的终止行号(从0开始):9
输入需要操作的目标sheet名称:sheet2
输入目标的行号偏移量:0
操作完成!