????? 原理:复制Sheet页的数据，其实是读取Excel,写入Excel,写入时候复制下样式,设置下行高和列宽，然后合并单元格就可以了。
????? 下面的代码也是这样写的,下面的代码是用来复制不同Excel 2007的sheet页数据的,没有判断2个Excel是否相同。
????? 注意,(1)判断行的最大数量建议使用srcSheet.getLastRowNum();判断每行最大列数建议使用srcRow.getLastCellNum();
????? ?在使用中发现?在Excel中手动添加行和列之后,使用srcSheet.getPhysicalNumberOfRows()和srcRow.getPhysicalNumberOfCells()得到的数量不准确。
?
????? (2)注意单元格如果是数字格式的可能是日期，优先判断是否是日期
????? (3)Cell具体有那些类型可以看代码,如下所示
????? 
???? 代码缺点在文章最后。
???? 代码如下，我就不解释了。
?????
class="java" name="code">import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class 复制Sheet {
public static void main(String[] args) throws Exception {
复制Sheet t = new 复制Sheet();
t.copyWbSheet("f:/saveFile/temp/copysheet_123.xlsx",
"f:/saveFile/temp/result_" + System.currentTimeMillis()
+ ".xlsx", "1. 测试概况", null);
}
// destSheetName为null时候使用sheetName的值为destFileName的sheet名
public void copyWbSheet(String srcfileName, String destFileName,
String sheetName, String destSheetName) throws Exception {
File srcFile = new File(srcfileName);
if (!srcFile.exists()) {
return;
}
XSSFWorkbook srcwb = new XSSFWorkbook(new FileInputStream(srcfileName));
XSSFSheet srcSheet = null;
if (sheetName == null) {
srcSheet = srcwb.getSheetAt(0);
sheetName = srcwb.getSheetName(0);
} else {
srcSheet = srcwb.getSheet(sheetName);
}
if (srcSheet == null) {
return;
}
if (destSheetName == null) {
destSheetName = sheetName;
}
XSSFWorkbook destwb = new XSSFWorkbook();
XSSFSheet destSheet = null;
File destFile = new File(destFileName);
// 不存在则新建
if (!destFile.exists()) {
destFile.createNewFile();
} else {
destwb = new XSSFWorkbook(new FileInputStream(destFile));
}
destSheet = destwb.getSheet(destSheetName);
if (destSheet == null) {
destSheet = destwb.createSheet(destSheetName);
}
// 最大列数
int maxCellNum = copySheet(srcSheet, destSheet, srcwb, destwb);
// 设置列宽
setSheetWidth(srcSheet, destSheet, maxCellNum);
// 合并单元格
mergeSheetAllRegion(srcSheet, destSheet);
// 保存
saveFile(destwb, destFileName);
}
public void saveFile(XSSFWorkbook destwb, String destFileName) {
try {
FileOutputStream fileOutStream = new FileOutputStream(destFileName);
destwb.write(fileOutStream);
if (fileOutStream != null) {
fileOutStream.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
public int copySheet(XSSFSheet srcSheet, XSSFSheet destSheet,
XSSFWorkbook srcwb, XSSFWorkbook destwb) throws Exception {
int rowCount = srcSheet.getLastRowNum();// 总行数
int maxCellNum = 0;
// System.out.println("------total row=------" + rowCount + "---="+
// srcSheet.getPhysicalNumberOfRows());
XSSFRow srcRow = null, destRow = null;
//注意这里
for (int i = 0; i <=rowCount; i++) {
srcRow = srcSheet.getRow(i);
destRow = destSheet.getRow(i);
if (srcRow == null) {
continue;
}
// 最大列数
maxCellNum = maxCellNum < srcRow.getLastCellNum() ? srcRow
.getLastCellNum() : maxCellNum;
if (destRow == null) {
destRow = destSheet.createRow(i);
}
// 设置行高
destRow.setHeight(srcRow.getHeight());
// System.out.println("---------row=" + i + "---="+
// srcRow.getPhysicalNumberOfCells() +
// "----="+srcRow.getLastCellNum());
copySheetRow(srcRow, destRow, srcwb, destwb);
srcRow = null;
destRow = null;
}
srcRow = null;
destRow = null;
return maxCellNum;
}
private void copySheetRow(XSSFRow srcRow, XSSFRow destRow,
XSSFWorkbook srcwb, XSSFWorkbook destwb) {
int cellCount = srcRow.getLastCellNum();// 每行的总列数
XSSFCell srcCell = null, destCell = null;
XSSFCellStyle srcCellStyle = null, destCellStyle = null;
for (int j = 0; j < cellCount; j++) {// 遍历行单元格
srcCell = srcRow.getCell(j);
destCell = destRow.getCell(j);
if (destCell == null) {
destCell = destRow.createCell(j);
}
if (srcCell != null) {
srcCellStyle = srcCell.getCellStyle();// 原sheet页样式
destCellStyle = null;
destCellStyle = destCell.getCellStyle();
// 复制样式
destCellStyle.cloneStyleFrom(srcCellStyle);
// 处理单元格内容
switch (srcCell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
destCell.setCellValue(srcCell.getRichStringCellValue());
break;
// 这里判断是否是日期
case XSSFCell.CELL_TYPE_NUMERIC:
// 判断是否是日期格式
// 测试发现如果这里不新建样式,日期显示的是数字
if (DateUtil.isCellDateFormatted(srcCell)) {
// 新建样式
destCellStyle = destwb.createCellStyle();
// 复制样式
destCellStyle.cloneStyleFrom(srcCellStyle);
destCell.setCellStyle(destCellStyle);
destCell.setCellValue(srcCell.getDateCellValue());
} else {
destCell.setCellValue(srcCell.getNumericCellValue());
}
break;
case XSSFCell.CELL_TYPE_FORMULA:
destCell.setCellFormula(srcCell.getCellFormula());
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
destCell.setCellValue(srcCell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
destCell.setCellType(XSSFCell.CELL_TYPE_BLANK);
break;
case XSSFCell.CELL_TYPE_ERROR:
break;
default:
break;
}
}
}
srcCellStyle = null;
destCellStyle = null;
srcCell = null;
destCell = null;
}
public void mergeSheetAllRegion(XSSFSheet srcSheet, XSSFSheet destSheet) {
int num = srcSheet.getNumMergedRegions();
CellRangeAddress cellR = null;
for (int i = 0; i < num; i++) {
cellR = srcSheet.getMergedRegion(i);
destSheet.addMergedRegion(cellR);
}
}
public void setSheetWidth(XSSFSheet srcSheet, XSSFSheet destSheet,
int maxCellNum) {
for (int i = 0; i <= maxCellNum; i++) {
destSheet.setColumnWidth(i, srcSheet.getColumnWidth(i));
}
}
}
??? 结果为：
??? 
???? 复制后：
?????
?
?
?
??? 代码缺点:
??? 1)只复制了数据，对于什么标签,批注之类的没有复制过去，
??? 2)单元格颜色,边框样式全部丢失。
??? 3)原sheet页的超链接复制后丢失。
??? 4)虽然判断了数字是否是日期,不新建样式日期还是显示为数字。代码140行左右。
??? 5)很容易OOM,测试发现，写10行1437列数据很快，复制sheet数据时候马上OOM。
???
public class Excel实际列数 {
public static void main(String[] args) {
Excel实际列数 t = new Excel实际列数();
System.out.println(t.getTrueColumnNum("bcg"));
}
public int getTrueColumnNum(String address) {
address = address.replaceAll("[^a-zA-Z]", "").toLowerCase();
char[] adds = address.toCharArray();
int base = 1;
int total = 0;
for (int i = adds.length - 1; i >= 0; i--) {
total += (adds[i] - 'a' + 1) * base;
base = 26 * base;
}
return total;
}
}
??? 例子如下:
??? 
?
???? 全文完。
?
相关文章
