Java Code Examples for org.apache.poi.xssf.usermodel.XSSFWorkbook#getCreationHelper()

/**
 * 导出Excel的例子
 * 因为类上面注解是@Controller，此方法需要@ResponseBody注解；如果类是RestController，则不需要ResponseBody
 * @return
 * @throws Exception
 */
@ResponseBody
@GetMapping("/export")
public ResponseEntity<byte[]> exportExcel() throws Exception{
    logger.trace("exportExcel");
    HttpHeaders responseHeaders = new HttpHeaders();
    responseHeaders.setContentDispositionFormData("attachment",new String("导出的文件名.xlsx".getBytes(), "ISO8859-1"));
    responseHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);

    //中文文件名需要用iso8859-1编码
    InputStream templateIs = this.getClass().getResourceAsStream("/excel-templates/templet.xlsx");
    XSSFWorkbook workbook = new XSSFWorkbook(templateIs);
    XSSFSheet sheet = workbook.getSheetAt(0);

    List<SampleItem> list = getDataList();

    CellStyle cellStyle = workbook.createCellStyle();
    CreationHelper createHelper = workbook.getCreationHelper();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd"));

    for (int i=0; i<list.size(); i++) {
        SampleItem si = list.get(i);

        XSSFRow row = sheet.createRow(i + 1);

        Cell cell1 = row.createCell(0);
        cell1.setCellValue(si.getDate());
        cell1.setCellStyle(cellStyle);

        Cell cell2 = row.createCell(1);
        cell2.setCellValue(si.getName());

        Cell cell3 = row.createCell(2);
        cell3.setCellValue(si.getScore());
    }

    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    workbook.write(bos);
    workbook.close();
    return new ResponseEntity<byte[]>(bos.toByteArray(), responseHeaders, HttpStatus.OK);
}
 

@Test
public void test2() throws IOException {
	 //create a new workbook
	XSSFWorkbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    String imgPath = "D:\\Xiaoi\\logo\\logo.png";
    //add picture data to this workbook.
    InputStream is = new FileInputStream(imgPath);
    byte[] bytes = IOUtils.toByteArray(is);
    int pictureIdx = wb.addPicture(bytes, XSSFWorkbook.PICTURE_TYPE_PNG);
    is.close();

    CreationHelper helper = wb.getCreationHelper();

    //create sheet
    Sheet sheet = wb.createSheet();

    // Create the drawing patriarch.  This is the top level container for all shapes. 
    Drawing drawing = sheet.createDrawingPatriarch();

    //add a picture shape
    ClientAnchor anchor = helper.createClientAnchor();
    //set top-left corner of the picture,
    //subsequent call of Picture#resize() will operate relative to it
    anchor.setCol1(3);
    anchor.setRow1(2);
    anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
    Picture pict = drawing.createPicture(anchor, pictureIdx);

    //auto-size picture relative to its top-left corner
    pict.resize();

    //save workbook
    String file = "E:\\ConvertTester\\excel\\picture.xls";
    if(wb instanceof XSSFWorkbook) file += "x";
    try (OutputStream fileOut = new FileOutputStream(file)) {
        wb.write(fileOut);
    }
}
 

@Test
public void testExcelRendering() throws Exception {
  URL resource = getClass().getResource( "rich-text-sample1.prpt" );
  ResourceManager mgr = new ResourceManager();
  MasterReport report = (MasterReport) mgr.createDirectly( resource, MasterReport.class ).getResource();
  report.getReportConfiguration()
      .setConfigProperty( ClassicEngineCoreModule.COMPLEX_TEXT_CONFIG_OVERRIDE_KEY, "true" );
  report.getReportHeader().getElement( 0 ).getStyle().setStyleProperty( TextStyleKeys.DIRECTION, TextDirection.LTR );
  report.getReportHeader().getElement( 1 ).getStyle().setStyleProperty( TextStyleKeys.DIRECTION, TextDirection.RTL );
  report.getReportHeader().removeElement( 0 );
  report.getReportHeader().getStyle().setStyleProperty( ElementStyleKeys.BACKGROUND_COLOR, Color.YELLOW );
  report.getReportFooter().clear();

  LogicalPageBox logicalPageBox = DebugReportRunner.layoutPage( report, 0 );

  RenderNode second = MatchFactory.findElementByName( logicalPageBox, "second" );
  assertTrue( second instanceof RenderBox );

  ExcelOutputProcessorMetaData metaData =
      new ExcelOutputProcessorMetaData( ExcelOutputProcessorMetaData.PAGINATION_FULL );
  metaData.initialize( report.getConfiguration() );

  XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
  ExcelColorProducer colorProducer = new StaticExcelColorSupport();
  ExcelFontFactory ff = new ExcelFontFactory( hssfWorkbook, colorProducer );
  CreationHelper ch = hssfWorkbook.getCreationHelper();
  ExcelTextExtractor te = new ExcelTextExtractor( metaData, colorProducer, ch, ff );

  Object compute = te.compute( (RenderBox) second );
  assertTrue( compute instanceof RichTextString );
  XSSFRichTextString rt = (XSSFRichTextString) compute;
  assertEquals( 4, rt.numFormattingRuns() );
}
 

@Test
public void testFastExcelRendering() throws Exception {
  URL resource = getClass().getResource( "rich-text-sample1.prpt" );
  ResourceManager mgr = new ResourceManager();
  MasterReport report = (MasterReport) mgr.createDirectly( resource, MasterReport.class ).getResource();
  report.getReportConfiguration()
      .setConfigProperty( ClassicEngineCoreModule.COMPLEX_TEXT_CONFIG_OVERRIDE_KEY, "true" );
  report.getReportHeader().getElement( 0 ).getStyle().setStyleProperty( TextStyleKeys.DIRECTION, TextDirection.LTR );
  report.getReportHeader().getElement( 1 ).getStyle().setStyleProperty( TextStyleKeys.DIRECTION, TextDirection.RTL );
  report.getReportHeader().removeElement( 0 );
  report.getReportHeader().getStyle().setStyleProperty( ElementStyleKeys.BACKGROUND_COLOR, Color.YELLOW );
  report.getReportFooter().clear();

  ExpressionRuntime runtime =
      new GenericExpressionRuntime( new DefaultTableModel(), 0, new DefaultProcessingContext( report ) );

  RichTextStyleResolver resolver = new RichTextStyleResolver( runtime.getProcessingContext(), report );
  resolver.resolveRichTextStyle( report );

  XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
  ExcelColorProducer colorProducer = new StaticExcelColorSupport();
  ExcelFontFactory ff = new ExcelFontFactory( hssfWorkbook, colorProducer );
  CreationHelper ch = hssfWorkbook.getCreationHelper();
  FastExcelTextExtractor te = new FastExcelTextExtractor( colorProducer, ff, ch );

  Element element = report.getReportHeader().getElement( 0 );
  Object compute = te.compute( element, runtime );
  assertTrue( compute instanceof RichTextString );
  XSSFRichTextString rt = (XSSFRichTextString) compute;
  assertEquals( 4, rt.numFormattingRuns() );
}
 

/**
 * Construye la hoja de Excel
 *
 * @return Instancia de la clase XSSFWorkbook con los datos almacenados en
 * el buffer de datos
 */
private XSSFWorkbook buildSpreadsheet() {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();
    System.out.println("Building spreadsheet...");
    System.out.println("Buffer size: " + mainBuffer.size());
    for (int i = 0; i <= mainBuffer.get(0).size(); i++) {
        Row row = sheet.createRow(i);
        for (int j = 0; j < variableList.size(); j++) {
            Cell cell = row.createCell(j);
            if (i == 0) {
                cell.setCellValue((String) variableList.get(j));
            } else {
                Object value = classList.get(j);
                if ((value instanceof String) || (value.equals(String.class))) {
                    cell.setCellValue((String) mainBuffer.get(j).get(i - 1));
                } else if ((value instanceof Boolean) || (value.equals(Boolean.class))) {
                    cell.setCellValue((Boolean) mainBuffer.get(j).get(i - 1));
                } else if ((value instanceof Date) || (value.equals(Date.class))) {

                    CellStyle cellStyle = workbook.createCellStyle();
                    CreationHelper createHelper = workbook.getCreationHelper();
                    cellStyle.setDataFormat(
                            createHelper.createDataFormat().getFormat(dateStringFormat));
                    cell.setCellValue((Date) mainBuffer.get(j).get(i - 1));
                    cell.setCellStyle(cellStyle);

                } else if ((value instanceof Integer) || (value.equals(Integer.class))) {
                    cell.setCellValue((Integer) mainBuffer.get(j).get(i - 1));
                } else if ((value instanceof Long) || (value.equals(Long.class))) {
                    cell.setCellValue((Long) mainBuffer.get(j).get(i - 1));
                } else if ((value instanceof Float) || (value.equals(Float.class))) {
                    cell.setCellValue((Float) mainBuffer.get(j).get(i - 1));
                } else if ((value instanceof Double) || (value.equals(Double.class))) {
                    cell.setCellValue((Double) mainBuffer.get(j).get(i - 1));
                }
            }
        }
    }
    return workbook;
}
 

/**
 * Constructor
 * 
 * @param sakaiFacade -
 *            a SakaiFacade Object
 */
public EventWorksheet(SakaiFacade sakaiFacade) {
	this.sakaiFacade = sakaiFacade;

	wb = new XSSFWorkbook();


	styles = WorksheetStyleClass.createStyles(wb);

	dateFormat = new SimpleDateFormat("", rb.getLocale());
	dateFormat.setTimeZone(sakaiFacade.getTimeService().getLocalTimeZone());

	initTableThRow();

	createHelper = wb.getCreationHelper();
}
 

/**
 * Constructor
 * 
 * @param sakaiFacade -
 *            a SakaiFacade Object
 */
public EventWorksheet(SakaiFacade sakaiFacade) {
	this.sakaiFacade = sakaiFacade;

	wb = new XSSFWorkbook();


	styles = WorksheetStyleClass.createStyles(wb);

	dateFormat = new SimpleDateFormat("", rb.getLocale());
	dateFormat.setTimeZone(sakaiFacade.getTimeService().getLocalTimeZone());

	initTableThRow();

	createHelper = wb.getCreationHelper();
}