You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2015/05/04 14:19:24 UTC
[Bug 57885] New: Can you please add a copySheet method on
org.apache.poi.ss.util.SheetUtil class
https://bz.apache.org/bugzilla/show_bug.cgi?id=57885
Bug ID: 57885
Summary: Can you please add a copySheet method on
org.apache.poi.ss.util.SheetUtil class
Product: POI
Version: 3.12-dev
Hardware: PC
Status: NEW
Severity: enhancement
Priority: P2
Component: XSSF
Assignee: dev@poi.apache.org
Reporter: pulikkot@aol.com
Hi:
Can you please add a copySheet method on org.apache.poi.ss.util.SheetUtil
helper class?
It is great help on users to do the copy sheet process
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57885] Can you please add a copySheet method on
org.apache.poi.ss.util.SheetUtil class
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57885
--- Comment #5 from Prejith Pulikkottil <pu...@aol.com> ---
Created attachment 32756
--> https://bz.apache.org/bugzilla/attachment.cgi?id=32756&action=edit
Merged Cell border style issue
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57885] Can you please add a copySheet method on
org.apache.poi.ss.util.SheetUtil class
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57885
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Keywords| |PatchAvailable
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57885] Can you please add a copySheet method on
org.apache.poi.ss.util.SheetUtil class
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57885
--- Comment #7 from Prejith Pulikkottil <pu...@aol.com> ---
Created attachment 32826
--> https://bz.apache.org/bugzilla/attachment.cgi?id=32826&action=edit
copy a sheet from source to destination
I have written the code sheet copy process, added test case in the same class.
Current limitation is Font style was not able to copy, but I'm least
prioritized this issue.
Attached the code and different type of source sheet and destination sheet
thanks
Prejith George
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57885] Add a function to copy a sheet in
org.apache.poi.ss.util.SheetUtil class
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57885
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Summary|Can you please add a |Add a function to copy a
|copySheet method on |sheet in
|org.apache.poi.ss.util.Shee |org.apache.poi.ss.util.Shee
|tUtil class |tUtil class
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57885] Can you please add a copySheet method on
org.apache.poi.ss.util.SheetUtil class
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57885
--- Comment #6 from Prejith Pulikkottil <pu...@aol.com> ---
As suggested, I wrote a code snippet for copy sheet functionality from one work
book to another. See the code snippet and attached workbook
After the copy functionality, my output file was corrupted and showed the error
"Repaired Records: Format from /xl/styles.xml part (Styles)"
When I look at the reason on different poi forums, I can see the that it is the
issue with border style. And see the solution, unsetBorderId and unsetFillId
After applying the unset functions data was successfully copied without border
style
In nutshell, I can see some issues with border styling the XSSF type files
package eis.utils;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.Date;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
public class XlsxExcelUtil {
private static void removeRows(Sheet destSheet) {
if (null != destSheet) {
for (int i = destSheet.getFirstRowNum(); i <= destSheet.getLastRowNum();
i++) {
Row row = destSheet.getRow(i);
if (null != row) {
destSheet.removeRow(row);
}
}
}
}
private static void addRows(Sheet destSheet, int totalRowCount) {
if (null != destSheet) {
for (int i = 0; i <= totalRowCount; i++) {
destSheet.createRow(i);
}
}
}
private static void copyMergedRegion(Sheet srcSheet, Sheet destSheet) {
for (int i = 0; i < srcSheet.getNumMergedRegions(); i++) {
destSheet.addMergedRegion(srcSheet.getMergedRegion(i));
}
}
private static void copyRow(Row srcRow, Row destRow) {
destRow.setHeight(srcRow.getHeight());
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
Cell srcCell = srcRow.getCell(j);
if (srcCell != null) {
Cell destCell = destRow.createCell(j);
copyCell(srcCell, destCell);
}
}
}
private static void copyCell(Cell srcCell, Cell destCell) {
CellStyle cellStyleClazz = destCell.getSheet().getWorkbook()
.createCellStyle();
if (cellStyleClazz instanceof XSSFCellStyle) {
XSSFCellStyle newCellStyle = (XSSFCellStyle) cellStyleClazz;
newCellStyle.cloneStyleFrom(srcCell.getCellStyle());
newCellStyle.setDataFormat(srcCell.getCellStyle().getDataFormat());
// Issue with border style, so added this code
// newCellStyle.getCoreXf().unsetBorderId();
// newCellStyle.getCoreXf().unsetFillId();
// -------------
destCell.setCellStyle(newCellStyle);
}
if (srcCell.getCellType() == Cell.CELL_TYPE_BLANK) {
destCell.setCellType(Cell.CELL_TYPE_BLANK);
} else if (srcCell.getCellType() == Cell.CELL_TYPE_STRING) {
destCell.setCellValue(srcCell.getStringCellValue());
} else if (srcCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
destCell.setCellValue(srcCell.getNumericCellValue());
} else if (srcCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
destCell.setCellValue(srcCell.getBooleanCellValue());
} else if (srcCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
destCell.setCellFormula(srcCell.getCellFormula());
} else if (srcCell.getCellType() == Cell.CELL_TYPE_ERROR) {
destCell.setCellErrorValue(srcCell.getErrorCellValue());
}
}
/**
*
* Copy a sheet from one workbook to another workbook. The method supports
* only two similar type of workbooks Xlsx type
*
* @param srcSheet
* @param destSheet
*/
public static void copySheet(Sheet srcSheet, Sheet destSheet) {
removeRows(destSheet);
addRows(destSheet, srcSheet.getLastRowNum());
copyMergedRegion(srcSheet, destSheet);
for (int i = srcSheet.getFirstRowNum(); i <= srcSheet.getLastRowNum(); i++)
{
Row srcRow = srcSheet.getRow(i);
if (null == srcRow) {
destSheet.createRow(i);
} else {
Row destRow = destSheet.createRow(i);
copyRow(srcRow, destRow);
}
}
}
public void testXlsxSheetCopy() {
try {
File templateFile = new File("C:/TestXlsx/Template_V2.xlsx");
InputStream inputStream = new FileInputStream(templateFile);
Workbook merWorkBook = WorkbookFactory.create(inputStream);
inputStream.close();
Sheet destPdrSheet = merWorkBook.getSheet("PDR");
File pdrFile = new File("C:/TestXlsx/P23163.xlsx");
InputStream pdrInputStream = new FileInputStream(pdrFile);
Workbook pdrWorkBook = WorkbookFactory.create(pdrInputStream);
pdrInputStream.close();
Sheet srcPdrSheet = pdrWorkBook.getSheetAt(0);
XlsxExcelUtil.copySheet(srcPdrSheet, destPdrSheet);
ByteArrayOutputStream byteArrayOutputStream = new
ByteArrayOutputStream();
merWorkBook.setForceFormulaRecalculation(true);
merWorkBook.write(byteArrayOutputStream);
FileOutputStream resultFile = new FileOutputStream(new File(
"C:/TestXlsx/outputXlsxFile.xlsx"));
byteArrayOutputStream.writeTo(resultFile);
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
XlsxExcelUtil obj = new XlsxExcelUtil();
obj.testXlsxSheetCopy();
}
}
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57885] Can you please add a copySheet method on
org.apache.poi.ss.util.SheetUtil class
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57885
Nick Burch <ap...@gagravarr.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
--- Comment #1 from Nick Burch <ap...@gagravarr.org> ---
To copy a sheet within one workbook, you just need to call
workbook.cloneSheet(sheetIndex)
For any other kind of sheet copying, if you'd care to work up a patch to add
the missing functionality, we'd be delighted to review it and commit it if
appropriate!
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57885] Add a function to copy a sheet in
org.apache.poi.ss.util.SheetUtil class
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57885
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #8 from Javen O'Neal <on...@apache.org> ---
(In reply to Prejith Pulikkottil from comment #7)
> Created attachment 32826 [details]
> copy a sheet from source to destination
Is this code licensed under Apache software license 2.0?
Could you reformulate this as a patch against POI trunk?
https://poi.apache.org/guidelines.html#SubmittingPatches
SheetUtil.java:
https://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java?view=log
TestSheetUtil.java:
https://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/util/TestSheetUtil.java?view=log
Excel files: https://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57885] Can you please add a copySheet method on
org.apache.poi.ss.util.SheetUtil class
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57885
--- Comment #2 from Prejith Pulikkottil <pu...@aol.com> ---
How do I copy the cloned to sheet to another workbook? for example copy a sheet
from workbook1 and need to paste the copied one to another workbook2
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57885] Can you please add a copySheet method on
org.apache.poi.ss.util.SheetUtil class
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57885
--- Comment #4 from Nick Burch <ap...@gagravarr.org> ---
I don't think we can use the code from coderanch as I don't believe it's under
a compatible license
If you'd be able to write up (from scratch!) code to do the same things, we'd
love it as a contribution to the project! See
http://poi.apache.org/guidelines.html for a bit more on contributing
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57885] Can you please add a copySheet method on
org.apache.poi.ss.util.SheetUtil class
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57885
--- Comment #3 from Prejith Pulikkottil <pu...@aol.com> ---
http://www.coderanch.com/t/420958/open-source/Copying-sheet-excel-file-excel
Also I have tired to copy sheet based on below open source forum using poi-3.11
jars
copy XLS sheets is working fine (XLS to XLS) I was able to open the copied
sheet.
But XLSX sheets (XLSX to XLSX) giving me error while I opening the copied sheet
"Removed Records: Merge cells from /xl/worksheets/sheet5.xml part"
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org