使用java poi 如何复制sheet1 里的第3行到第10行全部内容到sheet2
发布网友
发布时间：2022-04-29 22:32
共3个回答
热心网友
时间：2023-10-09 19:36
<%@ page language = "java" contentType="text/html; charset=GBK" %>
<%@ page language = "java" import = "java.util.*,org.apache.poi.hssf.usermodel.HSSFWorkbook,org.apache.poi.hssf.usermodel.HSSFSheet,org.apache.poi.hssf.usermodel.HSSFRow,org.apache.poi.hssf.usermodel.HSSFCell " %>
<%@ page import="java.sql.Connection" %>
<%@ page import="pandy.db.DBConnector" %>
<%@ page import="fish.search.dwcx" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.Statement" %>
<% if(session.getAttribute("dwyhzh")==null){
%>
<script>alert('登陆超时，请重新登陆');window.location='dwlogin.jsp';</script>
<%
} else {
Connection conn = DBConnector.getconecttion(); //得到一个数据连接
try {
String type = request.getParameter("type");
if(type==null)type="a";
String dwyhzh = (String) session.getAttribute("dwyhzh");
dwcx dwcx1 = new dwcx();
//int k = dwcx1.zgzs(conn, dwyhzh);
String sql="SELECT * FROM v_168_info WHERE dwyhzh = '" + dwyhzh + "' and zhzt!=2 ORDER BY zgyhzh asc";
if(type.equals("b")){
sql= "SELECT * FROM v_168_info WHERE dwyhzh = '" + dwyhzh + "' and zhzt!=2 and len(sfzhm)<1 ORDER BY zgyhzh asc";
}
if(type.equals("c")){
sql= "SELECT * FROM v_168_info WHERE dwyhzh = '" + dwyhzh + "' and zhzt!=2 and len(gjjkh)<1 ORDER BY zgyhzh asc";
}
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// java.text.DecimalFormat dff = new java.text.DecimalFormat(".##");
if(rs.next()) {
response.setContentType("application/vnd.ms-excel");//设置正确的输出类型
response.setHeader(" Content-Disposition ", " attachment; filename=\"" + dwyhzh + ".xls" + "\"");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
// 以下以写表头
// 表头为第一行
HSSFRow row = sheet.createRow((short) 0);
HSSFCell cell1 = row.createCell((short) 0);
HSSFCell cell2 = row.createCell((short) 1);
HSSFCell cell3 = row.createCell((short) 2);
HSSFCell cell4 = row.createCell((short) 3);
HSSFCell cell5 = row.createCell((short) 4);
HSSFCell cell6 = row.createCell((short) 5);
HSSFCell cell7 = row.createCell((short) 6);
HSSFCell cell8 = row.createCell((short) 7);
HSSFCell cell9 = row.createCell((short) 8);
HSSFCell cell10 = row.createCell((short) 9);
cell1.setEncoding((short) 1);
cell1.setCellType(1);
cell2.setEncoding((short) 1);
cell2.setCellType(1);
cell3.setEncoding((short) 1);
cell3.setCellType(1);
cell4.setEncoding((short) 1);
cell4.setCellType(1);
cell5.setEncoding((short) 1);
cell5.setCellType(1);
cell6.setEncoding((short) 1);
cell6.setCellType(1);
cell7.setEncoding((short) 1);
cell7.setCellType(1);
cell8.setEncoding((short) 1);
cell8.setCellType(1);
cell9.setEncoding((short) 1);
cell9.setCellType(1);
cell10.setEncoding((short) 1);
cell10.setCellType(1);
// 定义表头的内容
cell1.setCellValue(" 单位帐号 ");
cell2.setCellValue(" 职工帐号 ");
cell3.setCellValue(" 姓名 ");
cell4.setCellValue(" 身份证 ");
cell5.setCellValue(" 卡号 ");
cell6.setCellValue(" 工资 ");
cell7.setCellValue(" 个人缴存 ");
cell8.setCellValue(" 余额 ");
cell9.setCellValue(" 缴至月份 ");
cell10.setCellValue(" 缴存状态 ");
// sheet.setColumnWidth((short)1,(short)15);
sheet.setColumnWidth((short)0,(short)3000);
sheet.setColumnWidth((short)1,(short)3000);
sheet.setColumnWidth((short)3,(short)5000);
sheet.setColumnWidth((short)4,(short)5000);
sheet.setColumnWidth((short)8,(short)3000);
int i = 0;
String temp; String kkk="";
// rs.first();
while (rs.next()) {
// 定义数据从第二行开始
switch(rs.getInt("zhzt")){
case 0:kkk="停缴"; break;
case 1:kkk="缴交"; break;
case 2:kkk="销户"; break;
}
temp = rs.getString("zgwym");
row = sheet.createRow((short) i + 1);
cell1 = row.createCell((short) 0);
cell2 = row.createCell((short) 1);
cell3 = row.createCell((short) 2);
cell4 = row.createCell((short) 3);
cell5 = row.createCell((short) 4);
cell6 = row.createCell((short) 5);
cell7 = row.createCell((short) 6);
cell8 = row.createCell((short) 7);
cell9 = row.createCell((short) 8);
cell10 = row.createCell((short) 9);
cell1.setEncoding((short) 1);
cell1.setCellType(1);
cell2.setEncoding((short) 1);
cell2.setCellType(1);
cell3.setEncoding((short) 1);
cell3.setCellType(1);
cell4.setEncoding((short) 1);
cell4.setCellType(1);
cell5.setEncoding((short) 1);
cell5.setCellType(1);
cell6.setEncoding((short) 1);
cell6.setCellType(1);
cell7.setEncoding((short) 1);
cell7.setCellType(1);
cell8.setEncoding((short) 1);
cell8.setCellType(1);
cell9.setEncoding((short) 1);
cell9.setCellType(1);
cell10.setEncoding((short) 1);
cell10.setCellType(1);
// 填充内容
cell1.setCellValue(rs.getString("dwyhzh"));
cell2.setCellValue(rs.getString("zgyhzh"));
cell3.setCellValue(rs.getString("zgxm"));
cell4.setCellValue(rs.getString("sfzhm"));
cell5.setCellValue(rs.getString("gjjkh"));
cell6.setCellValue(rs.getDouble("ygz"));
cell7.setCellValue(rs.getDouble("zgyje"));
cell8.setCellValue(dwcx1.zhye(conn, temp));
cell9.setCellValue(rs.getString("fsrq").substring(0,10));
cell10.setCellValue(kkk);
i++;
}
wb.write(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
}else{
%><script>alert('没有职工相关信息');window.close();</script><%
} stmt.close();
rs.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBConnector.freecon(conn); //释放数据库连接
}
}
%>
热心网友
时间：2023-10-09 19:36
以下是我写的一段代码，支持多种类型的单元格（包括公式），完全可以满足你的要求，具体参数要在程序运行时输入。
---------------------------------------------------------------
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("输入需要操作的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
操作完成！
热心网友
时间：2023-10-09 19:37
