利用poi操作excel的问题 利用poi操作excel,拷贝行列,在拷贝完列的宽度之后,会发生部分列,被隐藏起来显示。请高手指点一下如何解决这个问题。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 for (int i = baseSheet.getFirstRowNum(); i <= baseSheet.getLastRowNum(); i++) { HSSFRow baseRow = baseSheet.getRow(i); if (baseRow != null) { for (short j = baseRow.getFirstCellNum(); j < baseRow.getLastCellNum(); j++) { targetSheet.setColumnWidth(j, baseSheet.getColumnWidth(j)); } break; }} 我估计是拷贝行列的时候,有的宽度没有设置。因为哪个cell单元格是空的,给跳过去了。poi里面有没有直接设置行列隐藏的方法啊 /** * 拷贝sheet * @param wb * @param baseSheetNum * 源sheet * @param targetSheetNum * 目标sheet */ public void copySheet(HSSFWorkbook wb, int baseSheetNum) { if (wb != null && wb.getSheetAt(baseSheetNum) != null) { HSSFSheet baseSheet = wb.getSheetAt(baseSheetNum); HSSFSheet targetSheet = wb.createSheet(wb .getSheetName(baseSheetNum) + "-1"); // 拷贝所有的行 for (int i = baseSheet.getFirstRowNum(); i <= baseSheet .getLastRowNum(); i++) { HSSFRow baseRow = baseSheet.getRow(i); HSSFRow targetRow = targetSheet.createRow(i); if (baseRow == null) { continue; } // 行高 targetRow.setHeight(baseRow.getHeight()); for (Short j = baseRow.getFirstCellNum(); j <= baseRow .getLastCellNum(); j++) { HSSFCell baseCell = baseRow.getCell(j); HSSFCell targetCell = targetRow.createCell(j); if (baseCell == null) { continue; } targetCell.setEncoding(baseCell.getEncoding()); targetCell.setCellStyle(baseCell.getCellStyle()); targetCell.setCellType(baseCell.getCellType()); int cellType = baseCell.getCellType(); switch (cellType) { case HSSFCell.CELL_TYPE_BOOLEAN: targetCell.setCellValue(baseCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: targetCell.setCellErrorValue(baseCell .getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: // parseFormula这个函数的用途在后面说明 targetCell.setCellFormula(parseFormula(baseCell .getCellFormula())); break; case HSSFCell.CELL_TYPE_NUMERIC: targetCell.setCellValue(baseCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_STRING: targetCell.setCellValue(baseCell.getStringCellValue()); break; } } } // 拷贝合并的行 for (int i = 0; i < baseSheet.getNumMergedRegions(); i++) { Region region = baseSheet.getMergedRegionAt(i); targetSheet.addMergedRegion(region); } // 设置列宽 for (int i = baseSheet.getFirstRowNum(); i <= baseSheet .getLastRowNum(); i++) { HSSFRow baseRow = baseSheet.getRow(i); if (baseRow != null) { for (short j = baseRow.getFirstCellNum(); j < baseRow .getLastCellNum(); j++) { targetSheet.setColumnWidth(j, baseSheet .getColumnWidth(j)); } break; } } } } /** * 拷贝sheet * @param wb * @param baseSheetNum * 源sheet * @param targetSheetNum * 目标sheet */public void copySheet(HSSFWorkbook wb, int baseSheetNum) { if (wb != null && wb.getSheetAt(baseSheetNum) != null) { HSSFSheet baseSheet = wb.getSheetAt(baseSheetNum); HSSFSheet targetSheet = wb.createSheet(wb.getSheetName(baseSheetNum)+ "-1"); // 拷贝所有的行 for (int i = baseSheet.getFirstRowNum(); i <= baseSheet .getLastRowNum(); i++) { HSSFRow baseRow = baseSheet.getRow(i); HSSFRow targetRow = targetSheet.createRow(i); if (baseRow == null) { continue; } // 行高 targetRow.setHeight(baseRow.getHeight()); for (Short j = baseRow.getFirstCellNum(); j <= baseRow .getLastCellNum(); j++) { HSSFCell baseCell = baseRow.getCell(j); HSSFCell targetCell = targetRow.createCell(j); if (baseCell == null) { continue; } targetCell.setEncoding(baseCell.getEncoding()); targetCell.setCellStyle(baseCell.getCellStyle()); targetCell.setCellType(baseCell.getCellType()); int cellType = baseCell.getCellType(); switch (cellType) { case HSSFCell.CELL_TYPE_BOOLEAN: break; case HSSFCell.CELL_TYPE_ERROR: break; case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_NUMERIC: break; case HSSFCell.CELL_TYPE_STRING: targetCell.setCellValue(baseCell.getStringCellValue()); break; } } } // 拷贝合并的行 for (int i = 0; i < baseSheet.getNumMergedRegions(); i++) { Region region = baseSheet.getMergedRegionAt(i); targetSheet.addMergedRegion(region); } // 设置列宽 for (int i = baseSheet.getFirstRowNum(); i <= baseSheet .getLastRowNum(); i++) { HSSFRow baseRow = baseSheet.getRow(i); if (baseRow != null) { for (short j = baseRow.getFirstCellNum(); j < baseRow .getLastCellNum(); j++) { targetSheet.setColumnWidth(j, baseSheet.getColumnWidth(j)); } break; } } } } 偶不知道你具体的应用但私下认为象这样拷贝不太好,反正都是sheet完全拷贝,建议用文件拷贝的方式,然后指定sheet保留,不拷贝的sheet删除掉 但是我的需求是这样的:根据一个sheet模板,生成多个sheet并且在每个sheet里面填充数据!另外你说的sheet完全拷贝怎么拷贝啊?poi有提供这个方法了?能不能把代码传一下? FileInputStream fileIn;FileOutputStream fileOut;POIFSFileSystem fs;fileIn = new FileInputStream(".\\rptTemplate\\rptTemplate1.xls");//读取模板fs = new POIFSFileSystem(fileIn); wb = new HSSFWorkbook(fs); sheet = wb.getSheetAt(0);//....得到了sheet模板,开始写数据,下边是输出fileOut = new FileOutputStream(destfile); wb.write(fileOut); fileOut.close(); 呵呵,你误会了我是根据一个workbook里面的一个sheet去生成这个workbool里面的多个sheet。就像这样:第一个sheet是关于员工信息的一个模板,然后我把所有员工的信息都显示在这个excel里面,每个员工对应一个sheet!!!! 晕,你自己选择一下得到哪个sheet模板啊,然后多生成几个不是就可以了,偶只是做个示例,你还想我完全给你写完??兄弟,你再仔细看看吧,这个代码扩展一下完全可以满足你 BufferedWriter的用法 用java怎么实现将远程服务器上的文件夹打包哪? 请教,我需要在java中执行创建数据库表的操作,如何实现 Swing的两个问题 程序中的一个问题 弱弱的问一句,如何回答别人提出的FAQ来提高自己的信誉分 请大侠来看看这段代码为什么不能出现圆 drugon(personal)先谢过,进来接分!!!!!!! 运行结果好诡异,不懂啊 请教ArrayList和Vertor在使用上的区别! 如何将IP地址转为整型 一个多线程判断是否都执行完成的问题
HSSFRow baseRow = baseSheet.getRow(i);
if (baseRow != null) {
for (short j = baseRow.getFirstCellNum(); j < baseRow.getLastCellNum(); j++) {
targetSheet.setColumnWidth(j, baseSheet.getColumnWidth(j));
}
break;
}
}
poi里面有没有直接设置行列隐藏的方法啊
* 拷贝sheet
* @param wb
* @param baseSheetNum
* 源sheet
* @param targetSheetNum
* 目标sheet
*/
public void copySheet(HSSFWorkbook wb, int baseSheetNum) {
if (wb != null && wb.getSheetAt(baseSheetNum) != null) {
HSSFSheet baseSheet = wb.getSheetAt(baseSheetNum);
HSSFSheet targetSheet = wb.createSheet(wb
.getSheetName(baseSheetNum)
+ "-1");
// 拷贝所有的行
for (int i = baseSheet.getFirstRowNum(); i <= baseSheet
.getLastRowNum(); i++) {
HSSFRow baseRow = baseSheet.getRow(i);
HSSFRow targetRow = targetSheet.createRow(i);
if (baseRow == null) {
continue;
}
// 行高
targetRow.setHeight(baseRow.getHeight());
for (Short j = baseRow.getFirstCellNum(); j <= baseRow
.getLastCellNum(); j++) {
HSSFCell baseCell = baseRow.getCell(j);
HSSFCell targetCell = targetRow.createCell(j);
if (baseCell == null) {
continue;
}
targetCell.setEncoding(baseCell.getEncoding());
targetCell.setCellStyle(baseCell.getCellStyle());
targetCell.setCellType(baseCell.getCellType());
int cellType = baseCell.getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_BOOLEAN:
targetCell.setCellValue(baseCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
targetCell.setCellErrorValue(baseCell
.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
// parseFormula这个函数的用途在后面说明
targetCell.setCellFormula(parseFormula(baseCell
.getCellFormula()));
break;
case HSSFCell.CELL_TYPE_NUMERIC:
targetCell.setCellValue(baseCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
targetCell.setCellValue(baseCell.getStringCellValue());
break;
}
}
}
// 拷贝合并的行
for (int i = 0; i < baseSheet.getNumMergedRegions(); i++) {
Region region = baseSheet.getMergedRegionAt(i);
targetSheet.addMergedRegion(region);
}
// 设置列宽
for (int i = baseSheet.getFirstRowNum(); i <= baseSheet
.getLastRowNum(); i++) {
HSSFRow baseRow = baseSheet.getRow(i);
if (baseRow != null) {
for (short j = baseRow.getFirstCellNum(); j < baseRow
.getLastCellNum(); j++) {
targetSheet.setColumnWidth(j, baseSheet
.getColumnWidth(j));
}
break;
}
}
}
}
* 拷贝sheet
* @param wb
* @param baseSheetNum
* 源sheet
* @param targetSheetNum
* 目标sheet
*/
public void copySheet(HSSFWorkbook wb, int baseSheetNum) {
if (wb != null && wb.getSheetAt(baseSheetNum) != null) {
HSSFSheet baseSheet = wb.getSheetAt(baseSheetNum);
HSSFSheet targetSheet =
wb.createSheet(wb.getSheetName(baseSheetNum)+ "-1");
// 拷贝所有的行
for (int i = baseSheet.getFirstRowNum(); i <= baseSheet
.getLastRowNum(); i++) {
HSSFRow baseRow = baseSheet.getRow(i);
HSSFRow targetRow = targetSheet.createRow(i);
if (baseRow == null) {
continue;
}
// 行高
targetRow.setHeight(baseRow.getHeight());
for (Short j = baseRow.getFirstCellNum(); j <= baseRow
.getLastCellNum(); j++) {
HSSFCell baseCell = baseRow.getCell(j);
HSSFCell targetCell = targetRow.createCell(j);
if (baseCell == null) {
continue;
}
targetCell.setEncoding(baseCell.getEncoding());
targetCell.setCellStyle(baseCell.getCellStyle());
targetCell.setCellType(baseCell.getCellType());
int cellType = baseCell.getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_BOOLEAN:
break;
case HSSFCell.CELL_TYPE_ERROR:
break;
case HSSFCell.CELL_TYPE_FORMULA:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
break;
case HSSFCell.CELL_TYPE_STRING:
targetCell.setCellValue(baseCell.getStringCellValue());
break;
}
}
}
// 拷贝合并的行
for (int i = 0; i < baseSheet.getNumMergedRegions(); i++) {
Region region = baseSheet.getMergedRegionAt(i);
targetSheet.addMergedRegion(region);
}
// 设置列宽
for (int i = baseSheet.getFirstRowNum(); i <= baseSheet
.getLastRowNum(); i++) {
HSSFRow baseRow = baseSheet.getRow(i);
if (baseRow != null) {
for (short j = baseRow.getFirstCellNum(); j < baseRow
.getLastCellNum(); j++) {
targetSheet.setColumnWidth(j, baseSheet.getColumnWidth(j));
}
break;
}
}
}
}
但私下认为象这样拷贝不太好,反正都是sheet完全拷贝,建议用文件拷贝的方式,然后指定sheet保留,不拷贝的sheet删除掉
另外你说的sheet完全拷贝怎么拷贝啊?poi有提供这个方法了?能不能把代码传一下?
FileOutputStream fileOut;
POIFSFileSystem fs;
fileIn = new FileInputStream(".\\rptTemplate\\rptTemplate1.xls");//读取模板
fs = new POIFSFileSystem(fileIn);
wb = new HSSFWorkbook(fs);
sheet = wb.getSheetAt(0);
//....得到了sheet模板,开始写数据,下边是输出
fileOut = new FileOutputStream(destfile);
wb.write(fileOut);
fileOut.close();