Java 在 POI 中复制 Excel 工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/848212/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copying Excel Worksheets in POI
提问by
Does anyone know of a means to copy a worksheet from one workbook to another using POI? The Workbook class has a cloneSheet method, but there doesn't seem to be able to insert a cloned sheet into a new workbook?
有谁知道使用 POI 将工作表从一个工作簿复制到另一个工作簿的方法？Workbook 类有一个 cloneSheet 方法，但似乎无法将克隆的工作表插入到新工作簿中？
If there isn't an API to do this easily, does anyone have the code to copy all of the data (styles, column widths, data, etc) from one sheet to another?
如果没有可以轻松完成此操作的 API，是否有人拥有将所有数据（样式、列宽、数据等）从一张工作表复制到另一张工作表的代码？
The jxls has methods to copy sheets, but they don't work when copying between workbooks.
jxls 有复制工作表的方法，但在工作簿之间复制时它们不起作用。
回答by Tony Qu
I created a workitem for NPOI: http://npoi.codeplex.com/WorkItem/View.aspx?WorkItemId=6057.
我为 NPOI 创建了一个工作项：http://npoi.codeplex.com/WorkItem/View.aspx?WorkItemId= 6057。
Update:The workitem is implemented in NPOI 2.0. You can download NPOI 2.0 from https://npoi.codeplex.com/releases/view/112932
更新：工作项在 NPOI 2.0 中实现。您可以从https://npoi.codeplex.com/releases/view/112932下载 NPOI 2.0
回答by Tony Qu
If you are using the Java POI library the best would be to load the Spreadsheet into memory,,, then create a new one and write each one of the records you want to copy... not the best way but acomplishes the copy function...
如果您使用的是 Java POI 库，最好将电子表格加载到内存中，然后创建一个新的电子表格并写入您要复制的每条记录……这不是最好的方法，而是完成了复制功能。 ..
回答by paulpooch
I put about a week of effort into doing this with POI (using the latest code on coderanch) - be warned that the code is flawed (there's an issue with using TreeSet where you need to replace that with a HashMap), but even after fixing that it crashes on formulas.
我花了大约一周的时间来使用 POI（使用 coderanch 上的最新代码）来做到这一点 - 请注意代码有缺陷（使用 TreeSet 存在问题，您需要将其替换为 HashMap），但即使在修复之后它会在公式上崩溃。
While it may be possible to do it's a scary proposition to have to rely on hacked up code.
虽然有可能做到，但不得不依赖黑客代码是一个可怕的提议。
Depending on your needs/budget you may want to consider biting the bullet and paying for aspose - http://www.aspose.com/doctest/java-components/aspose.cells-for-java/copy-move-worksheets-within-and-between-workbooks.html
根据您的需求/预算，您可能需要考虑咬紧牙关并为 aspose 付费 - http://www.aspose.com/doctest/java-components/aspose.cells-for-java/copy-move-worksheets-within -and-between-workbooks.html
It successfully copied sheets including formatting, formulas, & protection rules. I did 300 sheets in 130 seconds. (300 x 90kb workbooks, compiled into one 15mb workbook). The demo is free, it just puts an additional sheet into the workbook reminding you to buy a license.
它成功复制了工作表，包括格式、公式和保护规则。我在 130 秒内完成了 300 张。（300 x 90kb 工作簿，编译成一本 15mb 工作簿）。该演示是免费的，它只是在工作簿中添加了一张额外的表格，提醒您购买许可证。
回答by Leninkumar Koppoju
i have implemented some functionality with poi. please see the code for your reference.
我已经用 poi 实现了一些功能。请查看代码供您参考。
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
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;
public class ExcelReadAndWrite {
public static void main(String[] args) throws IOException {
ExcelReadAndWrite excel = new ExcelReadAndWrite();
excel.process("D:/LNN/My Workspace/POI/src/tables.xls");
}
public void process(String fileName) throws IOException {
BufferedInputStream bis = new BufferedInputStream(new FileInputStream(fileName));
HSSFWorkbook workbook = new HSSFWorkbook(bis);
HSSFWorkbook myWorkBook = new HSSFWorkbook();
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
HSSFSheet mySheet = null;
HSSFRow myRow = null;
HSSFCell myCell = null;
int sheets = workbook.getNumberOfSheets();
int fCell = 0;
int lCell = 0;
int fRow = 0;
int lRow = 0;
for (int iSheet = 0; iSheet < sheets; iSheet++) {
sheet = workbook.getSheetAt(iSheet);
if (sheet != null) {
mySheet = myWorkBook.createSheet(sheet.getSheetName());
fRow = sheet.getFirstRowNum();
lRow = sheet.getLastRowNum();
for (int iRow = fRow; iRow <= lRow; iRow++) {
row = sheet.getRow(iRow);
myRow = mySheet.createRow(iRow);
if (row != null) {
fCell = row.getFirstCellNum();
lCell = row.getLastCellNum();
for (int iCell = fCell; iCell < lCell; iCell++) {
cell = row.getCell(iCell);
myCell = myRow.createCell(iCell);
if (cell != null) {
myCell.setCellType(cell.getCellType());
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_BLANK:
myCell.setCellValue("");
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
myCell.setCellValue(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
myCell.setCellErrorValue(cell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
myCell.setCellFormula(cell.getCellFormula());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
myCell.setCellValue(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
myCell.setCellValue(cell.getStringCellValue());
break;
default:
myCell.setCellFormula(cell.getCellFormula());
}
}
}
}
}
}
}
bis.close();
BufferedOutputStream bos = new BufferedOutputStream(
new FileOutputStream("workbook.xls", true));
myWorkBook.write(bos);
bos.close();
}
}
回答by Faraz
This is my implementation of copying sheets from one workbook to another. This solution works for me. This code will work if the sheets don't have tables, etc. If the sheets contain simple text (String, boolean, int etc), formulas, this solution will work.
这是我将工作簿从一个工作簿复制到另一个工作簿的实现。这个解决方案对我有用。如果工作表没有表格等，此代码将起作用。如果工作表包含简单文本（字符串、布尔值、整数等）、公式，则此解决方案将起作用。
Workbook oldWB = new XSSFWorkbook(new FileInputStream("C:\input.xlsx"));
Workbook newWB = new XSSFWorkbook();
CellStyle newStyle = newWB.createCellStyle(); // Need this to copy over styles from old sheet to new sheet. Next step will be processed below
Row row;
Cell cell;
for (int i = 0; i < oldWB.getNumberOfSheets(); i++) {
XSSFSheet sheetFromOldWB = (XSSFSheet) oldWB.getSheetAt(i);
XSSFSheet sheetForNewWB = (XSSFSheet) newWB.createSheet(sheetFromOldWB.getSheetName());
for (int rowIndex = 0; rowIndex < sheetFromOldWB.getPhysicalNumberOfRows(); rowIndex++) {
row = sheetForNewWB.createRow(rowIndex); //create row in this new sheet
for (int colIndex = 0; colIndex < sheetFromOldWB.getRow(rowIndex).getPhysicalNumberOfCells(); colIndex++) {
cell = row.createCell(colIndex); //create cell in this row of this new sheet
Cell c = sheetFromOldWB.getRow(rowIndex).getCell(colIndex, Row.CREATE_NULL_AS_BLANK ); //get cell from old/original WB's sheet and when cell is null, return it as blank cells. And Blank cell will be returned as Blank cells. That will not change.
if (c.getCellType() == Cell.CELL_TYPE_BLANK){
System.out.println("This is BLANK " + ((XSSFCell) c).getReference());
}
else { //Below is where all the copying is happening. First It copies the styles of each cell and then it copies the content.
CellStyle origStyle = c.getCellStyle();
newStyle.cloneStyleFrom(origStyle);
cell.setCellStyle(newStyle);
switch (c.getCellTypeEnum()) {
case STRING:
cell.setCellValue(c.getRichStringCellValue().getString());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
cell.setCellValue(c.getDateCellValue());
} else {
cell.setCellValue(c.getNumericCellValue());
}
break;
case BOOLEAN:
cell.setCellValue(c.getBooleanCellValue());
break;
case FORMULA:
cell.setCellValue(c.getCellFormula());
break;
case BLANK:
cell.setCellValue("who");
break;
default:
System.out.println();
}
}
}
}
}
//Write over to the new file
FileOutputStream fileOut = new FileOutputStream("C:\output.xlsx");
newWB.write(fileOut);
oldWB.close();
newWB.close();
fileOut.close();
If your requirement is to copy full sheets without leaving or adding anything. I think The process of elimination works better and faster then the above code. And you don't have to worry about losing formulas, drawings, tables, styles, fonts, etc.
如果您的要求是在不留下或添加任何内容的情况下复印整张纸。我认为消除过程比上面的代码更好更快。而且您不必担心丢失公式、绘图、表格、样式、字体等。
XSSFWorkbook wb = new XSSFWorkbook("C:\abc.xlsx");
for (int i = wb.getNumberOfSheets() - 1; i >= 0; i--) {
if (!wb.getSheetName(i).contentEquals("January")) //This is a place holder. You will insert your logic here to get the sheets that you want.
wb.removeSheetAt(i); //Just remove the sheets that don't match your criteria in the if statement above
}
FileOutputStream out = new FileOutputStream(new File("C:\xyz.xlsx"));
wb.write(out);
out.close();
回答by Vajjala Vinay Rao
The best way is to open the file and load it. If you want any of the specific sheets from the source Excel file, then you just need to remove the sheet that does not match the intended Sheet.
最好的方法是打开文件并加载它。如果您想要源 Excel 文件中的任何特定工作表，则只需删除与预期工作表不匹配的工作表。
try this:
尝试这个：
FileInputStream fis=new FileInputStream("D:\SourceExcel.xls");
Workbook wb=WorkbookFactory.create(fis);
for (int i = wb.getNumberOfSheets() - 1; i >= 0; i--) {
if (!wb.getSheetName(i).contentEquals("SheetNameWhichwantToRetain")) //This is a place holder. You will insert your logic here to get the sheets that you want.
wb.removeSheetAt(i); //Just remove the sheets that don't match your criteria in the if statement above
}
FileOutputStream fos = new FileOutputStream(new File("D:\DestinationFileName.xls"));
wb.write(fos);
fos.close();
System.out.println("file is copied in a new file at destination :"+"D:\DestinationFileName.xls");
}
catch(Exception e){
e.printStackTrace();
}
