Examples with XSSFSheet org.apache.poi.xssf.usermodel.XSSFSheet used on opensource projects

Example 21 with XSSFSheet

use of org.apache.poi.xssf.usermodel.XSSFSheet in project poi by apache.

the class TestCommentsTable method readWriteMultipleAuthors.

@Test
public void readWriteMultipleAuthors() {
    XSSFWorkbook workbook = XSSFTestDataSamples.openSampleWorkbook("WithMoreVariousData.xlsx");
    XSSFSheet sheet1 = workbook.getSheetAt(0);
    XSSFSheet sheet2 = workbook.getSheetAt(1);
    assertTrue(sheet1.hasComments());
    assertFalse(sheet2.hasComments());
    assertEquals("Nick Burch", sheet1.getRow(4).getCell(2).getCellComment().getAuthor());
    assertEquals("Nick Burch", sheet1.getRow(6).getCell(2).getCellComment().getAuthor());
    assertEquals("Torchbox", sheet1.getRow(12).getCell(2).getCellComment().getAuthor());
    // Save, and re-load the file
    workbook = XSSFTestDataSamples.writeOutAndReadBack(workbook);
    // Check we still have comments where we should do
    sheet1 = workbook.getSheetAt(0);
    assertNotNull(sheet1.getRow(4).getCell(2).getCellComment());
    assertNotNull(sheet1.getRow(6).getCell(2).getCellComment());
    assertNotNull(sheet1.getRow(12).getCell(2).getCellComment());
    // And check they still have the contents they should do
    assertEquals("Nick Burch", sheet1.getRow(4).getCell(2).getCellComment().getAuthor());
    assertEquals("Nick Burch", sheet1.getRow(6).getCell(2).getCellComment().getAuthor());
    assertEquals("Torchbox", sheet1.getRow(12).getCell(2).getCellComment().getAuthor());
// Todo - check text too, once bug fixed
}

Example 22 with XSSFSheet

use of org.apache.poi.xssf.usermodel.XSSFSheet in project poi by apache.

the class TestCommentsTable method writeRead.

@Test
public void writeRead() {
    XSSFWorkbook workbook = XSSFTestDataSamples.openSampleWorkbook("WithVariousData.xlsx");
    XSSFSheet sheet1 = workbook.getSheetAt(0);
    XSSFSheet sheet2 = workbook.getSheetAt(1);
    assertTrue(sheet1.hasComments());
    assertFalse(sheet2.hasComments());
    // Change on comment on sheet 1, and add another into
    //  sheet 2
    Row r5 = sheet1.getRow(4);
    Comment cc5 = r5.getCell(2).getCellComment();
    cc5.setAuthor("Apache POI");
    cc5.setString(new XSSFRichTextString("Hello!"));
    Row r2s2 = sheet2.createRow(2);
    Cell c1r2s2 = r2s2.createCell(1);
    assertNull(c1r2s2.getCellComment());
    Drawing<?> dg = sheet2.createDrawingPatriarch();
    Comment cc2 = dg.createCellComment(new XSSFClientAnchor());
    cc2.setAuthor("Also POI");
    cc2.setString(new XSSFRichTextString("A new comment"));
    c1r2s2.setCellComment(cc2);
    // Save, and re-load the file
    workbook = XSSFTestDataSamples.writeOutAndReadBack(workbook);
    // Check we still have comments where we should do
    sheet1 = workbook.getSheetAt(0);
    sheet2 = workbook.getSheetAt(1);
    assertNotNull(sheet1.getRow(4).getCell(2).getCellComment());
    assertNotNull(sheet1.getRow(6).getCell(2).getCellComment());
    assertNotNull(sheet2.getRow(2).getCell(1).getCellComment());
    // And check they still have the contents they should do
    assertEquals("Apache POI", sheet1.getRow(4).getCell(2).getCellComment().getAuthor());
    assertEquals("Nick Burch", sheet1.getRow(6).getCell(2).getCellComment().getAuthor());
    assertEquals("Also POI", sheet2.getRow(2).getCell(1).getCellComment().getAuthor());
    assertEquals("Hello!", sheet1.getRow(4).getCell(2).getCellComment().getString().getString());
}

Example 23 with XSSFSheet

use of org.apache.poi.xssf.usermodel.XSSFSheet in project poi by apache.

the class TestXSSFImportFromXML method testOptionalFields_Bugzilla_57890.

@Test
public void testOptionalFields_Bugzilla_57890() throws IOException, ParseException, XPathExpressionException, SAXException {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("57890.xlsx");
    String testXML = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>" + "<TestInfoRoot>" + "<TestData>" + "<Int>" + Integer.MIN_VALUE + "</Int>" + "<UnsignedInt>12345</UnsignedInt>" + "<double>1.0000123</double>" + "<Date>1991-03-14</Date>" + "</TestData>" + "</TestInfoRoot>";
    XSSFMap map = wb.getMapInfo().getXSSFMapByName("TestInfoRoot_Map");
    assertNotNull(map);
    XSSFImportFromXML importer = new XSSFImportFromXML(map);
    importer.importFromXML(testXML);
    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFRow rowHeadings = sheet.getRow(0);
    XSSFRow rowData = sheet.getRow(1);
    assertEquals("Date", rowHeadings.getCell(0).getStringCellValue());
    Date date = new SimpleDateFormat("yyyy-MM-dd", DateFormatSymbols.getInstance(Locale.ROOT)).parse("1991-3-14");
    assertEquals(date, rowData.getCell(0).getDateCellValue());
    assertEquals("Amount Int", rowHeadings.getCell(1).getStringCellValue());
    assertEquals(new Double(Integer.MIN_VALUE), rowData.getCell(1).getNumericCellValue(), 0);
    assertEquals("Amount Double", rowHeadings.getCell(2).getStringCellValue());
    assertEquals(1.0000123, rowData.getCell(2).getNumericCellValue(), 0);
    assertEquals("Amount UnsignedInt", rowHeadings.getCell(3).getStringCellValue());
    assertEquals(new Double(12345), rowData.getCell(3).getNumericCellValue(), 0);
    wb.close();
}

Example 24 with XSSFSheet

use of org.apache.poi.xssf.usermodel.XSSFSheet in project cubrid-manager by CUBRID.

the class ExportTableDataTask method createSheetWriter.

/**
	 * Create the instance of SpreadsheetWriter and based upon the given
	 * condition writing the header of a sheet
	 *
	 * @param workbook the instance of Workbook
	 * @param xlsxWriterhelper the instance of XlsxWriterHelper
	 * @param sheetNum the number of a sheet
	 * @param fileMap a map includes the temporary file and its name
	 * @param columnTitles the column title
	 * @param xssfRowNum the number of row
	 * @throws IOException the exception
	 * @return the instance of XlsxWriterHelper.SpreadsheetWriter
	 */
private XlsxWriterHelper.SpreadsheetWriter createSheetWriter(XSSFWorkbook workbook, XlsxWriterHelper xlsxWriterhelper, int sheetNum, Map<String, File> fileMap, List<String> columnTitles, int xssfRowNum) throws IOException {
    // FIXME move this logic to core module
    XSSFSheet sheet = workbook.createSheet("sheet" + sheetNum);
    String sheetRef = sheet.getPackagePart().getPartName().getName().substring(1);
    File tmp = File.createTempFile("sheet" + sheetNum, ".xml");
    fileMap.put(sheetRef, tmp);
    String charset = null;
    if (fileCharset == null || fileCharset.trim().length() == 0) {
        charset = "UTF-8";
    } else {
        charset = fileCharset;
    }
    OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(tmp), charset);
    XlsxWriterHelper.SpreadsheetWriter sheetWriter = new XlsxWriterHelper.SpreadsheetWriter(writer);
    sheetWriter.setCharset(charset);
    sheetWriter.beginSheet();
    if (isFirstRowAsColumnName && columnTitles != null) {
        sheetWriter.insertRow(xssfRowNum);
        int styleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(workbook).get("header")).getIndex();
        for (int index = 0; index < columnTitles.size(); index++) {
            sheetWriter.createCell(index, columnTitles.get(index), styleIndex);
        }
        sheetWriter.endRow();
    }
    return sheetWriter;
}

Example 25 with XSSFSheet

use of org.apache.poi.xssf.usermodel.XSSFSheet in project cubrid-manager by CUBRID.

the class ExportToXlsxHandler method createSheetWriter.

/**
	 * Create the instance of SpreadsheetWriter and based upon the given
	 * condition writing the header of a sheet
	 *
	 * @param workbook the instance of Workbook
	 * @param xlsxWriterhelper the instance of XlsxWriterHelper
	 * @param sheetNum the number of a sheet
	 * @param fileMap a map includes the temporary file and its name
	 * @param columnTitles the column title
	 * @param xssfRowNum the number of row
	 * @throws IOException the exception
	 * @return the instance of XlsxWriterHelper.SpreadsheetWriter
	 */
private XlsxWriterHelper.SpreadsheetWriter createSheetWriter(XSSFWorkbook workbook, XlsxWriterHelper xlsxWriterhelper, int sheetNum, Map<String, File> fileMap, List<String> columnTitles, int xssfRowNum) throws IOException {
    // FIXME move this logic to core module
    XSSFSheet sheet = workbook.createSheet("sheet" + sheetNum);
    String sheetRef = sheet.getPackagePart().getPartName().getName().substring(1);
    File tmp = File.createTempFile("sheet" + sheetNum, ".xml");
    fileMap.put(sheetRef, tmp);
    String charset = null;
    if (StringUtil.isEmpty(exportConfig.getFileCharset())) {
        charset = "UTF-8";
    } else {
        charset = exportConfig.getFileCharset();
    }
    OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(tmp), charset);
    XlsxWriterHelper.SpreadsheetWriter sheetWriter = new XlsxWriterHelper.SpreadsheetWriter(writer);
    sheetWriter.setCharset(charset);
    sheetWriter.beginSheet();
    if (exportConfig.isFirstRowAsColumnName() && columnTitles != null) {
        sheetWriter.insertRow(xssfRowNum);
        int styleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(workbook).get("header")).getIndex();
        for (int index = 0; index < columnTitles.size(); index++) {
            sheetWriter.createCell(index, columnTitles.get(index), styleIndex);
        }
        sheetWriter.endRow();
    }
    return sheetWriter;
}