use of org.apache.poi.xssf.usermodel.XSSFCell in project poi by apache.
the class XSSFSingleXmlCell method getReferencedCell.
/**
* Gets the XSSFCell referenced by the R attribute or creates a new one if cell doesn't exists
* @return the referenced XSSFCell, null if the cell reference is invalid
*/
public XSSFCell getReferencedCell() {
XSSFCell cell = null;
CellReference cellReference = new CellReference(singleXmlCell.getR());
XSSFRow row = parent.getXSSFSheet().getRow(cellReference.getRow());
if (row == null) {
row = parent.getXSSFSheet().createRow(cellReference.getRow());
}
cell = row.getCell(cellReference.getCol());
if (cell == null) {
cell = row.createCell(cellReference.getCol());
}
return cell;
}
use of org.apache.poi.xssf.usermodel.XSSFCell in project poi by apache.
the class TestThemesTable method testThemesTableColors.
@Test
public void testThemesTableColors() throws Exception {
// Load our two test workbooks
XSSFWorkbook simple = XSSFTestDataSamples.openSampleWorkbook(testFileSimple);
XSSFWorkbook complex = XSSFTestDataSamples.openSampleWorkbook(testFileComplex);
// Save and re-load them, to check for stability across that
XSSFWorkbook simpleRS = XSSFTestDataSamples.writeOutAndReadBack(simple);
XSSFWorkbook complexRS = XSSFTestDataSamples.writeOutAndReadBack(complex);
// Fetch fresh copies to test with
simple = XSSFTestDataSamples.openSampleWorkbook(testFileSimple);
complex = XSSFTestDataSamples.openSampleWorkbook(testFileComplex);
// Files and descriptions
Map<String, XSSFWorkbook> workbooks = new LinkedHashMap<String, XSSFWorkbook>();
workbooks.put(testFileSimple, simple);
workbooks.put("Re-Saved_" + testFileSimple, simpleRS);
workbooks.put(testFileComplex, complex);
workbooks.put("Re-Saved_" + testFileComplex, complexRS);
// Sanity check
assertEquals(rgbExpected.length, rgbExpected.length);
// For offline testing
boolean createFiles = false;
// for the theme-applied cells in Column A are correct
for (String whatWorkbook : workbooks.keySet()) {
XSSFWorkbook workbook = workbooks.get(whatWorkbook);
XSSFSheet sheet = workbook.getSheetAt(0);
int startRN = 0;
if (whatWorkbook.endsWith(testFileComplex))
startRN++;
for (int rn = startRN; rn < rgbExpected.length + startRN; rn++) {
XSSFRow row = sheet.getRow(rn);
assertNotNull("Missing row " + rn + " in " + whatWorkbook, row);
String ref = (new CellReference(rn, 0)).formatAsString();
XSSFCell cell = row.getCell(0);
assertNotNull("Missing cell " + ref + " in " + whatWorkbook, cell);
int expectedThemeIdx = rn - startRN;
ThemeElement themeElem = ThemeElement.byId(expectedThemeIdx);
assertEquals("Wrong theme at " + ref + " in " + whatWorkbook, themeElem.name.toLowerCase(Locale.ROOT), cell.getStringCellValue());
// Fonts are theme-based in their colours
XSSFFont font = cell.getCellStyle().getFont();
CTColor ctColor = font.getCTFont().getColorArray(0);
assertNotNull(ctColor);
assertEquals(true, ctColor.isSetTheme());
assertEquals(themeElem.idx, ctColor.getTheme());
// Get the colour, via the theme
XSSFColor color = font.getXSSFColor();
// Theme colours aren't tinted
assertEquals(false, color.hasTint());
// Check the RGB part (no tint)
assertEquals("Wrong theme colour " + themeElem.name + " on " + whatWorkbook, rgbExpected[expectedThemeIdx], Hex.encodeHexString(color.getRGB()));
long themeIdx = font.getCTFont().getColorArray(0).getTheme();
assertEquals("Wrong theme index " + expectedThemeIdx + " on " + whatWorkbook, expectedThemeIdx, themeIdx);
if (createFiles) {
XSSFCellStyle cs = row.getSheet().getWorkbook().createCellStyle();
cs.setFillForegroundColor(color);
cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
row.createCell(1).setCellStyle(cs);
}
}
if (createFiles) {
FileOutputStream fos = new FileOutputStream("Generated_" + whatWorkbook);
workbook.write(fos);
fos.close();
}
}
}
use of org.apache.poi.xssf.usermodel.XSSFCell in project poi by apache.
the class TestThemesTable method themedAndNonThemedColours.
/**
* Ensure that, for a file with themes, we can correctly
* read both the themed and non-themed colours back.
* Column A = Theme Foreground
* Column B = Theme Foreground
* Column C = Explicit Colour Foreground
* Column E = Explicit Colour Background, Black Foreground
* Column G = Conditional Formatting Backgrounds
*
* Note - Grey Row has an odd way of doing the styling...
*/
@Test
public void themedAndNonThemedColours() {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook(testFileComplex);
XSSFSheet sheet = wb.getSheetAt(0);
XSSFCellStyle style;
XSSFColor color;
XSSFCell cell;
String[] names = { "White", "Black", "Grey", "Dark Blue", "Blue", "Red", "Green" };
String[] explicitFHexes = { "FFFFFFFF", "FF000000", "FFC0C0C0", "FF002060", "FF0070C0", "FFFF0000", "FF00B050" };
String[] explicitBHexes = { "FFFFFFFF", "FF000000", "FFC0C0C0", "FF002060", "FF0000FF", "FFFF0000", "FF00FF00" };
assertEquals(7, names.length);
// Check the non-CF colours in Columns A, B, C and E
for (int rn = 1; rn < 8; rn++) {
int idx = rn - 1;
XSSFRow row = sheet.getRow(rn);
assertNotNull("Missing row " + rn, row);
// Theme cells come first
XSSFCell themeCell = row.getCell(0);
ThemeElement themeElem = ThemeElement.byId(idx);
assertCellContents(themeElem.name, themeCell);
// Sanity check names
assertCellContents(names[idx], row.getCell(1));
assertCellContents(names[idx], row.getCell(2));
assertCellContents(names[idx], row.getCell(4));
// Check the colours
// A: Theme Based, Foreground
style = themeCell.getCellStyle();
color = style.getFont().getXSSFColor();
assertEquals(true, color.isThemed());
assertEquals(idx, color.getTheme());
assertEquals(rgbExpected[idx], Hex.encodeHexString(color.getRGB()));
// B: Theme Based, Foreground
cell = row.getCell(1);
style = cell.getCellStyle();
color = style.getFont().getXSSFColor();
assertEquals(true, color.isThemed());
if (idx != 2) {
assertEquals(idx, color.getTheme());
assertEquals(rgbExpected[idx], Hex.encodeHexString(color.getRGB()));
} else {
assertEquals(1, color.getTheme());
assertEquals(0.50, color.getTint(), 0.001);
}
// C: Explicit, Foreground
cell = row.getCell(2);
style = cell.getCellStyle();
color = style.getFont().getXSSFColor();
assertEquals(false, color.isThemed());
assertEquals(explicitFHexes[idx], color.getARGBHex());
// E: Explicit Background, Foreground all Black
cell = row.getCell(4);
style = cell.getCellStyle();
color = style.getFont().getXSSFColor();
assertEquals(true, color.isThemed());
assertEquals("FF000000", color.getARGBHex());
color = style.getFillForegroundXSSFColor();
assertEquals(false, color.isThemed());
assertEquals(explicitBHexes[idx], color.getARGBHex());
color = style.getFillBackgroundColorColor();
assertEquals(false, color.isThemed());
assertEquals(null, color.getARGBHex());
}
// Check the CF colours
// TODO
}
use of org.apache.poi.xssf.usermodel.XSSFCell in project poi by apache.
the class TestSXSSFCell method testPreserveSpaces.
@Test
public void testPreserveSpaces() throws IOException {
String[] samplesWithSpaces = { " POI", "POI ", " POI ", "\nPOI", "\n\nPOI \n" };
for (String str : samplesWithSpaces) {
Workbook swb = _testDataProvider.createWorkbook();
Cell sCell = swb.createSheet().createRow(0).createCell(0);
sCell.setCellValue(str);
assertEquals(sCell.getStringCellValue(), str);
// read back as XSSF and check that xml:spaces="preserve" is set
XSSFWorkbook xwb = (XSSFWorkbook) _testDataProvider.writeOutAndReadBack(swb);
XSSFCell xCell = xwb.getSheetAt(0).getRow(0).getCell(0);
CTRst is = xCell.getCTCell().getIs();
XmlCursor c = is.newCursor();
c.toNextToken();
String t = c.getAttributeText(new QName("http://www.w3.org/XML/1998/namespace", "space"));
c.dispose();
assertEquals("expected xml:spaces=\"preserve\" \"" + str + "\"", "preserve", t);
xwb.close();
swb.close();
}
}
use of org.apache.poi.xssf.usermodel.XSSFCell in project poi by apache.
the class TestSXSSFWorkbookWithCustomZipEntrySource method customZipEntrySourceForWriteAndRead.
// write an encrypted workbook to disk, and encrypt any temporary files as well
@Test
public void customZipEntrySourceForWriteAndRead() throws IOException, GeneralSecurityException, InvalidFormatException {
SXSSFWorkbookWithCustomZipEntrySource workbook = new SXSSFWorkbookWithCustomZipEntrySource();
SXSSFSheet sheet1 = workbook.createSheet(sheetName);
SXSSFRow row1 = sheet1.createRow(1);
SXSSFCell cell1 = row1.createCell(1);
cell1.setCellValue(cellValue);
EncryptedTempData tempData = new EncryptedTempData();
workbook.write(tempData.getOutputStream());
workbook.close();
workbook.dispose();
ZipEntrySource zipEntrySource = AesZipFileZipEntrySource.createZipEntrySource(tempData.getInputStream());
tempData.dispose();
OPCPackage opc = OPCPackage.open(zipEntrySource);
XSSFWorkbook xwb = new XSSFWorkbook(opc);
zipEntrySource.close();
XSSFSheet xs1 = xwb.getSheetAt(0);
assertEquals(sheetName, xs1.getSheetName());
XSSFRow xr1 = xs1.getRow(1);
XSSFCell xc1 = xr1.getCell(1);
assertEquals(cellValue, xc1.getStringCellValue());
xwb.close();
opc.close();
}
Aggregations