[prev in list] [next in list] [prev in thread] [next in thread]
List: poi-user
Subject: sheet.shiftRows() on xslx problem with commented cells
From: pedraza <juliopedraza () hotmail ! com>
Date: 2011-10-10 10:30:21
Message-ID: 1318242621074-4887701.post () n5 ! nabble ! com
[Download RAW message or body]
Hi,
my application does insert rows on a XSLX with comments on some cells.
When I try to insert a new row by using 'sheet.shiftRows()' comments/xslx
get corrupted (that can be verified by opening xslx with WinRAR and viewing
xl/xomments1.xml)
Due to this, my app cannot work this way and I'm forced to add rows at the
end of the sheet.
======= CODE SAMPLE =======
/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
//package org.apache.poi.xssf.usermodel.examples;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.FileOutputStream;
import java.io.InputStream;
public class CellComments {
private static String FILENAME = "comments.xlsx";
private Workbook wb = null;
Sheet sheet = null;
public static void main(String[] args) throws IOException {
new CellComments();
}
public CellComments() {
openFile();
createComment1(1, 1, "B2", "B2");
saveFile();
openFile();
createComment1(2, 2, "C3", "C3");
saveFile();
openFile();
createComment1(3, 3, "D4", "D4");
saveFile();
openFile();
createComment1(4, 4, "E5", "E5");
saveFile();
openFile();
createComment2(5, 3, "D6", "D6");
saveFile();
openFile();
createComment2(6, 3, "D7", "D7");
saveFile();
openFile();
createComment2(7, 3, "D8", "D8");
saveFile();
//Try to insert a new line ROW=3
openFile();
this.sheet.shiftRows(2, this.sheet.getLastRowNum(), 1);
createComment2(2, 3, "D3", "D3");
saveFile();
}
public void createComment1(int row, int col, String value, String
commentText) {
CreationHelper factory = wb.getCreationHelper();
Cell cell1 = sheet.createRow(row).createCell(col);
cell1.setCellValue(value);
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = factory.createClientAnchor();
Comment comment1 = drawing.createCellComment(anchor);
RichTextString str1 = factory.createRichTextString(commentText);
comment1.setString(str1);
comment1.setAuthor("Apache POI");
cell1.setCellComment(comment1);
System.out.println("Comment1:" + row + ";" + col + ";" + value + ";"
+ commentText);
}
public void createComment2(int row, int col, String value, String
commentText) {
CreationHelper factory = wb.getCreationHelper();
Cell cell2 = sheet.createRow(row).createCell(col);
cell2.setCellValue(value);
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = factory.createClientAnchor();
Comment comment2 = drawing.createCellComment(anchor);
RichTextString str2 = factory.createRichTextString(commentText);
//apply custom font to the text in the comment
Font font = wb.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short)14);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setColor(IndexedColors.RED.getIndex());
str2.applyFont(font);
comment2.setString(str2);
comment2.setAuthor("Apache POI");
comment2.setColumn(col);
comment2.setRow(row);
System.out.println("Comment2:" + row + ";" + col + ";" + value + ";"
+ commentText);
}
public void openFile() {
File file = new File(FILENAME);
System.out.println("File " + FILENAME + ((file.exists())?" exists":" does
not exists"));
if (file.exists()) {
try {
InputStream is = new FileInputStream(file);
this.wb = WorkbookFactory.create(is);
is.close();
} catch(InvalidFormatException e) {
System.out.println("ERROR: file format not recognized");
} catch(IOException e) {
System.out.println("ERROR: cannot open excel file");
}
} else {
this.wb = new XSSFWorkbook();
}
try {
sheet = wb.getSheetAt(0);
} catch(IllegalArgumentException e ) {
sheet = wb.createSheet();
}
}
public void saveFile() {
FileOutputStream fos;
try {
fos = new FileOutputStream(FILENAME);
this.wb.write(fos);
if (fos != null) fos.close();
System.out.println("Excel file closed.");
} catch (Exception e) {
System.out.println("ERROR: cannot open excel file: " + e.getMessage());
}
}
}
======= xl/comments1.xml =======
<?xml version="1.0" encoding="UTF-8"?>
<comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<authors>
<author />
<author>Apache POI</author>
</authors>
<commentList>
<comment ref="B2" authorId="1">
<text>
<t>B2</t>
</text>
</comment>
<comment ref="C9" authorId="1">
<text>
<t>C3</t>
</text>
</comment>
<comment ref="D9" authorId="1">
<text>
<t>D4</t>
</text>
</comment>
<comment ref="E9" authorId="1">
<text>
<t>E5</t>
</text>
</comment>
<comment ref="D9" authorId="1">
<text>
<r>
<rPr>
*
<color indexed="10" />
<sz val="14.0" />
<rFont val="Arial" />
</rPr>
<t>D6</t>
</r>
</text>
</comment>
<comment ref="D9" authorId="1">
<text>
<r>
<rPr>
*
<color indexed="10" />
<sz val="14.0" />
<rFont val="Arial" />
</rPr>
<t>D7</t>
</r>
</text>
</comment>
<comment ref="D9" authorId="1">
<text>
<r>
<rPr>
*
<color indexed="10" />
<sz val="14.0" />
<rFont val="Arial" />
</rPr>
<t>D8</t>
</r>
</text>
</comment>
<comment ref="D3" authorId="1">
<text>
<r>
<rPr>
*
<color indexed="10" />
<sz val="14.0" />
<rFont val="Arial" />
</rPr>
<t>D3</t>
</r>
</text>
</comment>
</commentList>
</comments>
--
View this message in context: \
http://apache-poi.1045710.n5.nabble.com/sheet-shiftRows-on-xslx-problem-with-commented-cells-tp4887701p4887701.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic