import org.apache.poi.xssf.streaming.SXSSFCell; //导入依赖的package包/类
/**
 * Istanzia oggetto
 *
 * @param se segment engine
 */
public SegmentationExcelWriter(SegmentEngine se) {
    sheetCellIndex = new LinkedHashMap<>();
    wb = new SXSSFWorkbook();
    sheetResults = wb.createSheet("Results");
    headerResults = sheetResults.createRow(0);
    SXSSFCell fCell = headerResults.createCell(0);
    fCell.setCellValue("File Name");
    fCell = headerResults.createCell(1);
    fCell.setCellValue("Language");
    fCell = headerResults.createCell(2);
    fCell.setCellValue("Original Text");
    fCell = headerResults.createCell(3);
    fCell.setCellValue("KPI");
    int idxCell = 4;
    for (SegmentConfiguration sc : se.getPatternMatrix()) {
        idxCell = createExcelHeader(wb, headerResults, sheetCellIndex, idxCell, "", sc, sheetResults);
    }
    writeClassificationHeader(wb);
}
 

import org.apache.poi.xssf.streaming.SXSSFCell; //导入依赖的package包/类
@Override
public void writeLink(final String text, final String link)
    throws IOException {

  final SXSSFCell cell =
      (SXSSFCell) this.row.createCell(this.colCount++, CellType.FORMULA);
  // final Cell cell = this.row.createCell(this.colCount++);

  if (text != null) {

    if (link != null) {
      cell.setCellFormula("HYPERLINK(\""
          + link.replace("\"", "\"\"") + "\",\"" + text.replace("\"", "\"\"")
          + "\")");
      cell.setCellStyle(this.linkStyle);
    }

    cell.setCellValue(text);
  }
}
 

import org.apache.poi.xssf.streaming.SXSSFCell; //导入依赖的package包/类
/**
 * 生成标题行
 * 
 * @author nan.li
 * @param sheet
 * @param titles
 * @param object
 */
private static void generateTitleRow(SXSSFSheet sheet, String[] titles, CellStyle cellStyle)
{
    SXSSFRow row = sheet.createRow(0);
    for (int i = 0; i < titles.length; i++)
    {
        SXSSFCell cell = row.createCell(i);
        cell.setCellStyle(cellStyle);// 设置元素的风格
        cell.setCellValue(titles[i]);
    }
}
 

import org.apache.poi.xssf.streaming.SXSSFCell; //导入依赖的package包/类
private int createSegmentHeader(SXSSFRow headerResults, Map<String, Integer> cellIndex, int idxCell, SegmentConfiguration sc, String segmentName) {
    SXSSFCell scell = headerResults.createCell(idxCell);
    scell.setCellValue(segmentName);
    cellIndex.put("SEGMENT." + segmentName, idxCell);
    idxCell++;
    List<CaptureConfiguration> captureConfigurations = sc.getCaptureConfigurations();
    List<CaptureConfiguration> segmentCaptureConfigurations = sc.getSentenceCaptureConfigurations();
    idxCell = getCapturesHeader(headerResults, cellIndex, idxCell, segmentName, captureConfigurations, segmentCaptureConfigurations);
    return idxCell;
}
 

import org.apache.poi.xssf.streaming.SXSSFCell; //导入依赖的package包/类
public static SXSSFCell newSXSSFCell(SXSSFRow row, int index) {
	return (SXSSFCell) row.createCell(index);
}
 

import org.apache.poi.xssf.streaming.SXSSFCell; //导入依赖的package包/类
@Override
public void write(Object newDAO) throws OfficeWriterException {
	SpreadSheetCellDAO sscd = MSExcelWriter.checkSpreadSheetCellDAO(newDAO);
	String safeSheetName=WorkbookUtil.createSafeSheetName(sscd.getSheetName());
	SXSSFSheet currentSheet=this.currentWorkbook.getSheet(safeSheetName);
	if (currentSheet==null) {// create sheet if it does not exist yet
		currentSheet=this.currentWorkbook.createSheet(safeSheetName);
		if (!(safeSheetName.equals(sscd.getSheetName()))) {
			LOG.warn("Sheetname modified from \""+sscd.getSheetName()+"\" to \""+safeSheetName+"\" to correspond to Excel conventions.");
		}
		// create drawing anchor (needed for comments...)
		this.mappedDrawings.put(safeSheetName,currentSheet.createDrawingPatriarch());
	}
	// check if cell exist
	CellAddress currentCA = new CellAddress(sscd.getAddress());
	SXSSFRow currentRow = currentSheet.getRow(currentCA.getRow());
	if (currentRow==null) { // row does not exist? => create it
		currentRow=currentSheet.createRow(currentCA.getRow());
	}
	SXSSFCell currentCell = currentRow.getCell(currentCA.getColumn());
	if ((currentCell!=null)) { // cell already exists and no template loaded ? => throw exception
		throw new OfficeWriterException("Invalid cell specification: cell already exists at "+currentCA);
	}
	// create cell if no template is loaded or cell not available in template
		currentCell=currentRow.createCell(currentCA.getColumn());		
	// set the values accordingly
	if (!("".equals(sscd.getFormula()))) { // if formula exists then use formula
		currentCell.setCellFormula(sscd.getFormula());
		
	} else {	
	// else use formattedValue
		currentCell.setCellValue(sscd.getFormattedValue());
	}
	// set comment
	if ((sscd.getComment()!=null) && (!("".equals(sscd.getComment())))) {
		/** the following operations are necessary to create comments **/
		/** Define size of the comment window **/
		    ClientAnchor anchor = this.currentWorkbook.getCreationHelper().createClientAnchor();
    		    anchor.setCol1(currentCell.getColumnIndex());
    		    anchor.setCol2(currentCell.getColumnIndex()+this.howc.getCommentWidth());
    		    anchor.setRow1(currentRow.getRowNum());
    		    anchor.setRow2(currentRow.getRowNum()+this.howc.getCommentHeight());
		/** create comment **/
		   Comment currentComment = mappedDrawings.get(safeSheetName).createCellComment(anchor);
    		    currentComment.setString(this.currentWorkbook.getCreationHelper().createRichTextString(sscd.getComment()));
    		    currentComment.setAuthor(this.howc.getCommentAuthor());
		    currentCell.setCellComment(currentComment);

	}
	}
 

import org.apache.poi.xssf.streaming.SXSSFCell; //导入依赖的package包/类
/**
 * [Flow #-2] 액셀 파일 수정 : 엑샐 파일 내 셀의 내용을 변경하고 저장할 수 있음
 */
@Test
public void testModifyCellContents() throws Exception {
    
    try {
    	String content = "Use \n with word wrap on to create a new line";
    	short rownum = 2;
    	int cellnum = 2;
    	
    	log.debug("testModifyCellContents start....");

    	StringBuffer sb = new StringBuffer();
    	sb.append(fileLocation).append("/").append("testModifyCellContents.xlsx");

    	if (!EgovFileUtil.isExistsFile(sb.toString())) {
    		XSSFWorkbook wbT = new XSSFWorkbook();
     	wbT.createSheet();
	
         // 엑셀 파일 생성
         excelService.createXSSFWorkbook(wbT, sb.toString());
    	}
    	
        // 엑셀 파일 로드
        XSSFWorkbook wb = excelService.loadXSSFWorkbook(sb.toString());
        log.debug("testModifyCellContents after loadWorkbook....");
        
        XSSFSheet sheet = wb.getSheetAt(0);
        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();
        
        cs.setFont( f2 );
        //Word Wrap MUST be turned on
        cs.setWrapText( true );
        
        XSSFRow row = sheet.createRow( rownum );
        row.setHeight( (short) 0x349 );
        XSSFCell cellx = row.createCell( cellnum );
        cellx.setCellType( SXSSFCell.CELL_TYPE_STRING );
        cellx.setCellValue( new XSSFRichTextString(content) );
        cellx.setCellStyle( cs );
        
        sheet.setColumnWidth( 20, (int) ( ( 50 * 8 ) / ( (double) 1 / 20 ) ) );
        
        //excelService.writeWorkbook(wb);
        
        FileOutputStream outx = new FileOutputStream(sb.toString());
        wb.write(outx);
        outx.close();
        
        // 엑셀 파일 로드
        Workbook wb1 = excelService.loadXSSFWorkbook(sb.toString());
        
        Sheet sheet1 = wb1.getSheetAt(0);
        Row row1 = sheet1.getRow(rownum);
        Cell cell1 = row1.getCell( cellnum );
        
        // 수정된 셀의 내용 점검
        log.debug("cell ###" + cell1.getRichStringCellValue() + "###");
        log.debug("cont ###" + content + "###");
        
        assertNotSame("TEST", cell1.getRichStringCellValue().toString());
        assertEquals(content, cell1.getRichStringCellValue().toString());
        
    } catch (Exception e) {
    	log.error(e.toString());
    	throw new Exception(e);
    } finally {
    	log.debug("testModifyCellContents end....");
    }
}