Java SXSSFSheet类代码示例

import org.apache.poi.xssf.streaming.SXSSFSheet; //导入依赖的package包/类
/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * @param sheet 要设置的sheet.
 * @param textlist 下拉框显示的内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol   开始列
 * @param endCol  结束列
 * @return 设置好的sheet.
 */
public static SXSSFSheet setHSSFValidation(SXSSFSheet sheet,
										  String[] textlist, int firstRow, int endRow, int firstCol,
										  int endCol) {
	DataValidationHelper validationHelper = sheet.getDataValidationHelper();
	// 加载下拉列表内容
	DataValidationConstraint explicitListConstraint = validationHelper.createExplicitListConstraint(textlist);
	// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
	CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow, firstCol, endCol);
	// 数据有效性对象
	DataValidation validation = validationHelper.createValidation(explicitListConstraint, regions);
	validation.setSuppressDropDownArrow(true);
	validation.createErrorBox("tip","请从下拉列表选取");
	//错误警告框
	validation.setShowErrorBox(true);
	sheet.addValidationData(validation);
	return sheet;
}
 

import org.apache.poi.xssf.streaming.SXSSFSheet; //导入依赖的package包/类
@Override
public void autoSizeColumns() {
    boolean layoutChanged = false;

    // for streaming implementation, only tracked columns can be autosized!
    if (poiSheet instanceof SXSSFSheet) {
        SXSSFSheet sxssfSheet = (SXSSFSheet) poiSheet;
        for (int j = 0; j < getColumnCount(); j++) {
            if (sxssfSheet.isColumnTrackedForAutoSizing(j)) {
                poiSheet.autoSizeColumn(j);
                layoutChanged = true;
            }
        }
    } else {
        for (int j = 0; j < getColumnCount(); j++) {
            poiSheet.autoSizeColumn(j);
            layoutChanged = true;
        }
    }

    // inform listeners
    if (layoutChanged) {
        firePropertyChange(PROPERTY_LAYOUT_CHANGED, null, null);
    }
}
 

import org.apache.poi.xssf.streaming.SXSSFSheet; //导入依赖的package包/类
private static void writeClassificationHeader(SXSSFWorkbook workbook) {
    SXSSFSheet sheet = workbook.createSheet("Bayes");
    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("File");
    header.createCell(1).setCellValue("Text");
    header.createCell(2).setCellValue("1st Level1");
    header.createCell(3).setCellValue("1st Score1");
    header.createCell(4).setCellValue("1st Level2");
    header.createCell(5).setCellValue("1st Score2");
    header.createCell(6).setCellValue("1st Level3");
    header.createCell(7).setCellValue("1st Score3");
    header.createCell(8).setCellValue("1st Level4");
    header.createCell(9).setCellValue("1st Score4");
    header.createCell(10).setCellValue("1st Level5");
    header.createCell(11).setCellValue("1st Score5");
    header.createCell(12).setCellValue("1st Level6");
    header.createCell(13).setCellValue("1st Score7");
    header.createCell(14).setCellValue("2nd Level1");
    header.createCell(15).setCellValue("2nd Score1");
    header.createCell(16).setCellValue("2nd Level2");
    header.createCell(17).setCellValue("2nd Score2");
    header.createCell(18).setCellValue("2nd Level3");
    header.createCell(19).setCellValue("2nd Score3");
    header.createCell(20).setCellValue("2nd Level4");
    header.createCell(21).setCellValue("2nd Score4");
    header.createCell(22).setCellValue("2nd Level5");
    header.createCell(23).setCellValue("2nd Score5");
    header.createCell(24).setCellValue("2nd Level6");
    header.createCell(25).setCellValue("2nd Score6");
}
 

import org.apache.poi.xssf.streaming.SXSSFSheet; //导入依赖的package包/类
public static SXSSFSheet newSXSSFSheet(SXSSFWorkbook wb,String sheetName) {
	return (SXSSFSheet) wb.createSheet(sheetName);
}
 

import org.apache.poi.xssf.streaming.SXSSFSheet; //导入依赖的package包/类
/**
 * 设定单元格宽度 (手动/自动)
 * @param sheet 工作薄对象
 * @param index 单元格索引
 * @param width 指定宽度,-1为自适应
 * @param value 自适应需要单元格内容进行计算
 */
public static void setColumnWidth(SXSSFSheet sheet,int index, short width, String value) {
	if (width == -1 && value != null && !"".equals(value)) {
		sheet.setColumnWidth(index, (short) (value.length() * 250));
	} else {
		width = width == -1 ? 200 : width;
		sheet.setColumnWidth(index, (short) (width * 35.7));
	}
}
 

import org.apache.poi.xssf.streaming.SXSSFSheet; //导入依赖的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.SXSSFSheet; //导入依赖的package包/类
@Override
public void autoSizeColumn(int j) {
    // for streaming implementation, only tracked columns can be autosized!
    if (poiSheet instanceof SXSSFSheet) {
        SXSSFSheet sxssfSheet = (SXSSFSheet) poiSheet;
        if (!sxssfSheet.isColumnTrackedForAutoSizing(j)) {
            return;
        }
    }

    poiSheet.autoSizeColumn(j);
    firePropertyChange(PROPERTY_LAYOUT_CHANGED, null, null);
}
 

import org.apache.poi.xssf.streaming.SXSSFSheet; //导入依赖的package包/类
public void setCurrentSheetByName(String name, int lineNum){
	SXSSFSheet sheet = wb.getSheet(name);
	if(sheet==null){
		throw new NullPointerException(String.format("no sheet named [%s]", name));
	}else{
		super.currentSheet=sheet;
		super.currentRowIndex=lineNum;
	}
}
 

import org.apache.poi.xssf.streaming.SXSSFSheet; //导入依赖的package包/类
private void flushSheet(final int currentCount, final Sheet sheet)
        throws IOException {
    if (sheet instanceof SXSSFSheet) {
        if (currentCount % SXSSF_FLUSH_COUNT == 0) {
            ((SXSSFSheet) sheet).flushRows(0);
        }
    }
}
 

import org.apache.poi.xssf.streaming.SXSSFSheet; //导入依赖的package包/类
private void flushSheet(Sheet sheet) {
	if (!useStreaming)
		return;
	if (!(sheet instanceof SXSSFSheet))
		return;
	SXSSFSheet s = (SXSSFSheet) sheet;
	try {
		log.trace("flush rows of sheet {}", sheet.getSheetName());
		s.flushRows();
	} catch (Exception e) {
		log.error("failed to flush rows of streamed sheet", e);
	}
}
 

import org.apache.poi.xssf.streaming.SXSSFSheet; //导入依赖的package包/类
private int getLastRowNum(Sheet sheet) {
    final int lastRowNum = sheet.getLastRowNum();
    if (lastRowNum == 0 && sheet instanceof SXSSFSheet) {
        // streaming sheets have bad behaviour in this scenario - since no
        // rows are in cache, it will return 0!
        DataSet ds = _dataContext.query().from(sheet.getSheetName()).selectCount().execute();
        ds.next();
        final Number count = (Number) ds.getRow().getValue(0);
        final int columnNameLineNumber = _configuration.getColumnNameLineNumber();
        int oneBasedResult = count.intValue()
                + (columnNameLineNumber == ExcelConfiguration.NO_COLUMN_NAME_LINE ? 0 : columnNameLineNumber);
        return oneBasedResult - 1;
    }
    return lastRowNum;
}
 

import org.apache.poi.xssf.streaming.SXSSFSheet; //导入依赖的package包/类
private void closeOutputFile() throws KettleException {
  try {

    // may have to write a footer here
    if ( meta.isFooterEnabled() ) {
      writeHeader();
    }

    // handle auto size for columns
    if ( meta.isAutoSizeColums() ) {

      // track all columns for autosizing if using streaming worksheet
      if (  data.sheet instanceof SXSSFSheet ) {
        ( (SXSSFSheet) data.sheet ).trackAllColumnsForAutoSizing();
      }

      if ( meta.getOutputFields() == null || meta.getOutputFields().length == 0 ) {
        for ( int i = 0; i < data.inputRowMeta.size(); i++ ) {
          data.sheet.autoSizeColumn( i + data.startingCol );
        }
      } else {
        for ( int i = 0; i < meta.getOutputFields().length; i++ ) {
          data.sheet.autoSizeColumn( i + data.startingCol );
        }
      }
    }
    // force recalculation of formulas if requested
    if ( meta.isForceFormulaRecalculation() ) {
      recalculateAllWorkbookFormulas();
    }

    BufferedOutputStreamWithCloseDetection out =
        new BufferedOutputStreamWithCloseDetection( KettleVFS.getOutputStream( data.file, false ) );
    data.wb.write( out );
    out.close();
  } catch ( IOException e ) {
    throw new KettleException( e );
  }
}
 

import org.apache.poi.xssf.streaming.SXSSFSheet; //导入依赖的package包/类
public static SXSSFRow newSXSSFRow(SXSSFSheet sheet,int index) {
	return (SXSSFRow) sheet.createRow(index);
}
 

import org.apache.poi.xssf.streaming.SXSSFSheet; //导入依赖的package包/类
/**
 * 复制模板工作表的打印区域到数据工作表中
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-03-17
 * @version     v1.0
 * 
 * @param i_FromSheet  源工作表
 * @param i_ToSheet    目标工作表
 */
public final static void copyPrintSetup(Sheet i_FromSheet ,Sheet i_ToSheet) 
{
    PrintSetup v_FromPrintSetup = i_FromSheet.getPrintSetup();
    PrintSetup v_ToPrintSetup   = i_ToSheet  .getPrintSetup();
    
    v_ToPrintSetup.setCopies(       v_FromPrintSetup.getCopies());
    v_ToPrintSetup.setDraft(        v_FromPrintSetup.getDraft());          // 值为true时，表示用草稿品质打印
    v_ToPrintSetup.setFitHeight(    v_FromPrintSetup.getFitHeight());      // 设置页高
    v_ToPrintSetup.setFitWidth(     v_FromPrintSetup.getFitWidth());       // 设置页宽
    v_ToPrintSetup.setFooterMargin( v_FromPrintSetup.getFooterMargin());
    v_ToPrintSetup.setHeaderMargin( v_FromPrintSetup.getHeaderMargin());
    v_ToPrintSetup.setHResolution(  v_FromPrintSetup.getHResolution());
    v_ToPrintSetup.setLandscape(    v_FromPrintSetup.getLandscape());      // true，则表示页面方向为横向；否则为纵向
    v_ToPrintSetup.setLeftToRight(  v_FromPrintSetup.getLeftToRight());    // true表示“先行后列”；false表示“先列后行”
    v_ToPrintSetup.setNoColor(      v_FromPrintSetup.getNoColor());        // 值为true时，表示单色打印
    v_ToPrintSetup.setNoOrientation(v_FromPrintSetup.getNoOrientation()); 
    v_ToPrintSetup.setNotes(        v_FromPrintSetup.getNotes());          // 设置打印批注
    v_ToPrintSetup.setPageStart(    v_FromPrintSetup.getPageStart());      // 设置打印起始页码
    v_ToPrintSetup.setPaperSize(    v_FromPrintSetup.getPaperSize());      // 纸张类型 A4纸 HSSFPrintSetup.A4_PAPERSIZE
    v_ToPrintSetup.setScale(        v_FromPrintSetup.getScale());          // 缩放比例80%(设置为0-100之间的值)
    v_ToPrintSetup.setUsePage(      v_FromPrintSetup.getUsePage());        // 设置打印起始页码是否使用"自动"
    v_ToPrintSetup.setValidSettings(v_FromPrintSetup.getValidSettings());
    v_ToPrintSetup.setVResolution(  v_FromPrintSetup.getVResolution());
    
    // 设置打印参数
    if ( i_ToSheet instanceof HSSFSheet )
    {
        ((HSSFPrintSetup)v_ToPrintSetup).setOptions(((HSSFPrintSetup)v_FromPrintSetup).getOptions());
        
        i_ToSheet.setMargin(HSSFSheet.TopMargin     ,i_FromSheet.getMargin(HSSFSheet.TopMargin));     // 页边距（上）
        i_ToSheet.setMargin(HSSFSheet.BottomMargin  ,i_FromSheet.getMargin(HSSFSheet.BottomMargin));  // 页边距（下）
        i_ToSheet.setMargin(HSSFSheet.LeftMargin    ,i_FromSheet.getMargin(HSSFSheet.LeftMargin));    // 页边距（左）
        i_ToSheet.setMargin(HSSFSheet.RightMargin   ,i_FromSheet.getMargin(HSSFSheet.RightMargin));   // 页边距（右）
        i_ToSheet.setMargin(HSSFSheet.HeaderMargin  ,i_FromSheet.getMargin(HSSFSheet.HeaderMargin));  // 页眉
        i_ToSheet.setMargin(HSSFSheet.FooterMargin  ,i_FromSheet.getMargin(HSSFSheet.FooterMargin));  // 页脚
    }
    else if ( i_ToSheet instanceof SXSSFSheet )
    {
        ((XSSFPrintSetup)v_ToPrintSetup).setOrientation(((XSSFPrintSetup)v_FromPrintSetup).getOrientation());  // 设置方向 
        
        i_ToSheet.setMargin(SXSSFSheet.TopMargin    ,i_FromSheet.getMargin(SXSSFSheet.TopMargin));     // 页边距（上）
        i_ToSheet.setMargin(SXSSFSheet.BottomMargin ,i_FromSheet.getMargin(SXSSFSheet.BottomMargin));  // 页边距（下）
        i_ToSheet.setMargin(SXSSFSheet.LeftMargin   ,i_FromSheet.getMargin(SXSSFSheet.LeftMargin));    // 页边距（左）
        i_ToSheet.setMargin(SXSSFSheet.RightMargin  ,i_FromSheet.getMargin(SXSSFSheet.RightMargin));   // 页边距（右）
        i_ToSheet.setMargin(SXSSFSheet.HeaderMargin ,i_FromSheet.getMargin(SXSSFSheet.HeaderMargin));  // 页眉
        i_ToSheet.setMargin(SXSSFSheet.FooterMargin ,i_FromSheet.getMargin(SXSSFSheet.FooterMargin));  // 页脚
    }
    else if ( i_ToSheet instanceof XSSFSheet )
    {
        ((XSSFPrintSetup)v_ToPrintSetup).setOrientation(((XSSFPrintSetup)v_FromPrintSetup).getOrientation());  // 设置方向 
        
        i_ToSheet.setMargin(XSSFSheet.TopMargin     ,i_FromSheet.getMargin(XSSFSheet.TopMargin));     // 页边距（上）
        i_ToSheet.setMargin(XSSFSheet.BottomMargin  ,i_FromSheet.getMargin(XSSFSheet.BottomMargin));  // 页边距（下）
        i_ToSheet.setMargin(XSSFSheet.LeftMargin    ,i_FromSheet.getMargin(XSSFSheet.LeftMargin));    // 页边距（左）
        i_ToSheet.setMargin(XSSFSheet.RightMargin   ,i_FromSheet.getMargin(XSSFSheet.RightMargin));   // 页边距（右）
        i_ToSheet.setMargin(XSSFSheet.HeaderMargin  ,i_FromSheet.getMargin(XSSFSheet.HeaderMargin));  // 页眉
        i_ToSheet.setMargin(XSSFSheet.FooterMargin  ,i_FromSheet.getMargin(XSSFSheet.FooterMargin));  // 页脚
    }
    
    copyHeaderFooter(i_FromSheet.getHeader() ,i_ToSheet.getHeader());
    copyHeaderFooter(i_FromSheet.getFooter() ,i_ToSheet.getFooter());
}
 

import org.apache.poi.xssf.streaming.SXSSFSheet; //导入依赖的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);

	}
	}