Java Sheet類代碼示例

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
/**
 * Writes the example set into a excel file with XLSX format. If you want to write it in XLS
 * format use {@link #write(ExampleSet, Charset, OutputStream)}.
 *
 * @param exampleSet
 *            the exampleSet to write
 * @param sheetName
 *            name of the excel sheet which will be created.
 * @param dateFormat
 *            a string which describes the format used for dates.
 * @param numberFormat
 *            a string which describes the format used for numbers.
 * @param outputStream
 *            the stream to write the file to
 * @param opProg
 *            increases the progress by the number of examples to provide a more detailed
 *            progress.
 */
public static void writeXLSX(ExampleSet exampleSet, String sheetName, String dateFormat, String numberFormat,
		OutputStream outputStream, OperatorProgress opProg) throws WriteException, IOException,
		ProcessStoppedException {
	// .xlsx files can only store up to 16384 columns, so throw error in case of more
	if (exampleSet.getAttributes().allSize() > 16384) {
		throw new IllegalArgumentException(I18N.getMessage(I18N.getErrorBundle(),
				"export.excel.excel_xlsx_file_exceeds_column_limit"));
	}

	try {
		XSSFWorkbook workbook = new XSSFWorkbook();

		Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(sheetName));
		dateFormat = dateFormat == null ? DEFAULT_DATE_FORMAT : dateFormat;

		numberFormat = numberFormat == null ? "#.0" : numberFormat;

		writeXLSXDataSheet(workbook, sheet, dateFormat, numberFormat, exampleSet, opProg);
		workbook.write(outputStream);
	} finally {
		outputStream.flush();
		outputStream.close();
	}
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
/**
 * 複製工作表相關參數
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-03-20
 * @version     v1.0
 *
 * @param i_FromSheet
 * @param i_ToSheet
 */
public final static void copySheet(Sheet i_FromSheet ,Sheet i_ToSheet)
{
    // 打印時顯示網格線
    i_ToSheet.setPrintGridlines(           i_FromSheet.isPrintGridlines());
    i_ToSheet.setPrintRowAndColumnHeadings(i_FromSheet.isPrintRowAndColumnHeadings());
    i_ToSheet.setFitToPage(                i_FromSheet.getFitToPage());
    
    // Sheet頁自適應頁麵大小
    i_ToSheet.setAutobreaks(               i_FromSheet.getAutobreaks());
    i_ToSheet.setDisplayZeros(             i_FromSheet.isDisplayZeros());
    i_ToSheet.setDisplayGuts(              i_FromSheet.getDisplayGuts());
    // 網格線
    i_ToSheet.setDisplayGridlines(         i_FromSheet.isDisplayGridlines());
    
    // 凍結線
    if ( i_FromSheet.getPaneInformation() != null )
    {
        i_ToSheet.createFreezePane(i_FromSheet.getPaneInformation().getVerticalSplitPosition()
                                  ,i_FromSheet.getPaneInformation().getHorizontalSplitPosition()
                                  ,i_FromSheet.getPaneInformation().getVerticalSplitLeftColumn()
                                  ,i_FromSheet.getPaneInformation().getHorizontalSplitTopRow());
    }
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
public void set(int sheetIndex, int x_index, int y_index, String value) {
    try {
        if (wb == null) throw new Exception("未打開文件");
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        Cell cell = null;
        row = sheet.getRow(x_index);
        if (row == null) {
            row = sheet.createRow(x_index);
        }
        cell = row.getCell(y_index);
        if (cell == null) {
            cell = row.createCell(y_index);
        }
        cell.setCellValue(value);
        save();
    } catch (Exception e) {
        logger.error(e.getMessage());
    }
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
/**
 * 合並行
 */
//TODO 暫時支持兩行表頭
private void mergeRows(Sheet sheet, CellStyle cellStyle, ExcelMeta excelMeta) {

    Row row = null;
    Cell cell = null;
    String[] lastRowVals = new String[excelMeta.getTitleColumnNum()];
    for (int r = 0; r < excelMeta.getTitleRowNum(); r++) {
        for (int c = 0; c < excelMeta.getTitleColumnNum(); c++) {
            row = sheet.getRow(r);
            cell = row.getCell(c);
            if (r == 0) {
                lastRowVals[c] = cell.getStringCellValue();
            } else {
                if (StringUtils.equals(lastRowVals[c], cell.getStringCellValue())) {
                    cell.setCellValue("");
                    sheet.addMergedRegion(new CellRangeAddress(0, r, c, c));
                    Cell nowCell = sheet.getRow(0).getCell(c);
                    nowCell.setCellStyle(cellStyle);
                }
            }

        }
    }

}
 

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
private void setSheetData(SheetData data, String group) {
	data.setCurrentGroup(group);
	// start from 1
	data.setCurrentIndex(1);
	// get sheet
	Sheet vSheet = getWorkBook().getSheet(group);
	Assert.notNull(vSheet, "Can't get sheet with name: " + group);
	data.setSheet(vSheet);
	// get row number
	int vRowCount = vSheet.getLastRowNum() + 1;
	data.setRowCount(vRowCount);
	// get first row
	Row vRow = vSheet.getRow(0);
	Assert.notNull(vRow, "Invalid format: first row must be title");
	// get column number
	int vColumnCount = vRow.getLastCellNum();
	String[] vTitles = new String[vColumnCount];
	// read titles
	for (int i = 0; i < vColumnCount; ++i) {
		Cell vCell = vRow.getCell(i);
		vTitles[i] = vCell.getStringCellValue();
	}
	data.setTitles(vTitles);
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
public static void createWorkBook() throws IOException {
	// ����excel������
	final Workbook wb = new XSSFWorkbook();
	// ����sheet��ҳ��
	final Sheet sheet1 = wb.createSheet("Sheet_1");
	final Sheet sheet2 = wb.createSheet("Sheet_2");

	for (int i = 0; i < 20; i = i + 2) {
		final Row row1 = sheet1.createRow(i);
		final Row row2 = sheet2.createRow(i);

		for (int j = 0; j < 10; j++) {

			row1.createCell(j).setCellValue(j + "new");
			row2.createCell(j).setCellValue(j + "This is a string");

		}
	}
	// ����һ���ļ� ����Ϊworkbooks.xlsx
	final FileOutputStream fileOut = new FileOutputStream("d:\\workbooks.xlsx");
	// �����洴���Ĺ�����������ļ���
	wb.write(fileOut);
	// �ر������
	fileOut.close();
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
private void formatCellDate(Sheet sheet, Cell cell, String format) {
	CellStyle style = wb.createCellStyle();
	CreationHelper createHelper = wb.getCreationHelper();
	style.setDataFormat(createHelper.createDataFormat().getFormat(format));
	cell.setCellStyle(style);
	 
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
protected Sheet createSheet(SXSSFWorkbook wb,Paper paper,String name){
	Sheet sheet = null;
	if(name==null){
		sheet=wb.createSheet();
	}else{			
		sheet=wb.createSheet(name);
	}
	PaperType paperType=paper.getPaperType();
	XSSFPrintSetup printSetup=(XSSFPrintSetup)sheet.getPrintSetup();
	Orientation orientation=paper.getOrientation();
	if(orientation.equals(Orientation.landscape)){
		printSetup.setOrientation(PrintOrientation.LANDSCAPE);			
	}
	setupPaper(paperType, printSetup);
	int leftMargin=paper.getLeftMargin();
	int rightMargin=paper.getRightMargin();
	int topMargin=paper.getTopMargin();
	int bottomMargin=paper.getBottomMargin();
	sheet.setMargin(Sheet.LeftMargin, UnitUtils.pointToInche(leftMargin));
	sheet.setMargin(Sheet.RightMargin, UnitUtils.pointToInche(rightMargin));
	sheet.setMargin(Sheet.TopMargin, UnitUtils.pointToInche(topMargin));
	sheet.setMargin(Sheet.BottomMargin, UnitUtils.pointToInche(bottomMargin));
	return sheet;
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
private int getStartRow(Sheet sheet) throws SheetParsingException {

		Iterator<Row> rit = sheet.iterator();
		while (rit.hasNext()) {
			Row row = rit.next();
			Cell cell = row.getCell(0);

			if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
				/*
				 * In my case first column is Sr.no. and Data starts where sr. no is 1 ,so this
				 * is start row index
				 */
				if (cell.getNumericCellValue() == 1.0)
					return row.getRowNum();
			}
		}
		throw new SheetParsingException("no start index found for data");
	}
 

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
@Test
public void test1() throws Exception {
    File file = folder.newFile("temp.xlsx");
    try (Workbook wb = new XSSFWorkbook()) {
        Sheet sheet1 = wb.createSheet("Sheet1");
        for (int row = 10; row <= 110; row++) {
            ExcelUtils.writeCell(sheet1, row, 20, "ISSUE-" + row);
        }
        try (OutputStream out = new FileOutputStream(file)) {
            wb.write(out);
        }
    }

    Context context = new MockContext();

    Read re = new Read(file.getAbsolutePath(), "Sheet1", "U");
    TextList list = re.execute(context, None.getInstance());
    assertNotNull(list);

    List<Text> texts = list.remaining(Hint.none());
    assertNotNull(texts);
    assertEquals(101, texts.size());
    assertEquals("ISSUE-10", texts.get(0).getText());
    assertEquals("ISSUE-110", texts.get(100).getText());
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
/**
 * 按報表模板格式寫入小計（暫時不支持分頁功能）
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-03-27
 * @version     v1.0
 *
 * @param i_DataWorkbook   數據工作薄
 * @param i_DataSheet      數據工作表
 * @param io_RTotal        將數據寫入Excel時的輔助統計信息
 * @param io_RSystemValue  係統變量信息
 * @param i_Datas          數據
 * @param i_RTemplate      報表模板對象
 */
public final static void writeSubtotal(RWorkbook i_DataWorkbook ,Sheet i_DataSheet ,RTotal io_RTotal ,RSystemValue io_RSystemValue, Object i_Datas ,RTemplate i_RTemplate) 
{
    Sheet v_TemplateSheet            = i_RTemplate.getTemplateSheet();
    int   v_TemplateRowCountSubtotal = i_RTemplate.getRowCountSubtotal();
    int   v_ExcelRowIndex            = io_RTotal.getExcelRowIndex();
    
    copyMergedRegionsSubtotal(i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板合並單元格
    copyImagesSubtotal(       i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板複製圖片
    
    for (int v_RowNo=0; v_RowNo<v_TemplateRowCountSubtotal; v_RowNo++) 
    {
        int v_TemplateRowNo = i_RTemplate.getSubtotalBeginRow() + v_RowNo;
        Row v_TemplateRow   = v_TemplateSheet.getRow(v_TemplateRowNo);
        
        int v_DataRowNo = v_RowNo + v_ExcelRowIndex;
        Row v_DataRow   = i_DataSheet.createRow(v_DataRowNo);
        io_RTotal.addExcelRowIndex(1);
        io_RTotal.addRealDataCount(1);
        
        copyRow(i_RTemplate ,v_TemplateRow ,i_DataWorkbook ,io_RTotal ,io_RSystemValue ,v_DataRow ,i_Datas);
    }
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
/**
 * 按報表模板格式寫分頁頁腳標題
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-06-25
 * @version     v1.0
 *
 * @param i_DataWorkbook   數據工作薄
 * @param i_DataSheet      數據工作表
 * @param io_RTotal        將數據寫入Excel時的輔助統計信息
 * @param io_RSystemValue  係統變量信息
 * @param i_Datas          數據
 * @param i_RTemplate      報表模板對象
 */
public final static void writeTitlePageFooter(RWorkbook i_DataWorkbook ,Sheet i_DataSheet ,RTotal io_RTotal ,RSystemValue io_RSystemValue ,Object i_Datas ,RTemplate i_RTemplate) 
{
    Sheet v_TemplateSheet    = i_RTemplate.getTemplateSheet();
    int   v_TemplateRowCount = io_RTotal.getTitlePageFooterCount();
    int   v_ExcelRowIndex    = io_RTotal.getExcelRowIndex();

    copyMergedRegionsTitlePageFooter(i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板合並單元格
    copyImagesTitlePageFooter(       i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板複製圖片
    
    for (int v_RowNo=0; v_RowNo<v_TemplateRowCount; v_RowNo++)
    {
        int v_TemplateRowNo = i_RTemplate.getTitlePageFooterBeginRow() + v_RowNo;
        Row v_TemplateRow   = v_TemplateSheet.getRow(v_TemplateRowNo);
        
        int v_DataRowNo = v_RowNo + v_ExcelRowIndex;
        Row v_DataRow   = i_DataSheet.createRow(v_DataRowNo);
        io_RTotal.addExcelRowIndex(1);
        
        copyRow(i_RTemplate ,v_TemplateRow ,i_DataWorkbook ,io_RTotal ,io_RSystemValue ,v_DataRow ,i_Datas);
    }
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
/**
 * Read the excel to get the Map of properties for supported locals
 * 
 * @param wb
 *            workbook which is the source
 * @param supportedLocales
 *            supported Locale Iterator
 * @param sheetName
 * @param defaultKeySet
 *            if this parameter is not null: if there is invalid key not
 *            in this set, TranslationImportException.KEY_NOT_FOUND will
 *            throw.
 * @return
 * @throws ValidationException
 * @throws TranslationImportException
 */
public static Map<String, Properties> readExcel(Workbook wb,
        Iterator<Locale> supportedLocales, String sheetName,
        Set<Object> defaultKeySet) throws ValidationException,
        TranslationImportException {
    Sheet sheet = null;
    try {
        sheet = wb
                .getSheet(getDefaultResourceBundle().getString(sheetName));
        if (sheet == null) {
            throw new TranslationImportException();
        }
    } catch (Exception e) {
        throw new TranslationImportException(
                TranslationImportException.Reason.SHEET_NAME_NOT_FOUND);
    }
    return readSheet(sheet, supportedLocales, sheetName, defaultKeySet);
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
/**
 * Read the sheet to get the Map of properties for supported locals
 * 
 * @param sheet
 * @param supportedLocales
 * @param sheetName
 * @param defaultKeySet
 *            if this parameter is not null: if there is invalid key not
 *            in this set, TranslationImportException.KEY_NOT_FOUND will
 *            throw.
 * @return 
 * @throws ValidationException
 * @throws TranslationImportException
 */
public static Map<String, Properties> readSheet(Sheet sheet,
        Iterator<Locale> supportedLocales, String sheetName,
        Set<Object> defaultKeySet) throws ValidationException,
        TranslationImportException {

    List<String> localeStringList = readFirstRow(sheet);

    // create a properties object for each supported locale and uploaded
    // locale
    Map<String, Properties> propertiesMap = initializePropertyMap(
            supportedLocales, localeStringList, sheetName);

    readRows(sheet, propertiesMap, localeStringList, defaultKeySet);

    return propertiesMap;
}
 

import org.apache.poi.ss.usermodel.Sheet; //導入依賴的package包/類
private static List<String> readFirstRow(Sheet sheet)
        throws TranslationImportException, ValidationException {
    List<String> localeStringList = new ArrayList<String>();
    Row row = sheet.getRow(0);
    if (row != null) {
        int colIdx = 1; // skip the first col it contains the keys
        String localeString = "";
        while (true) {
            localeString = getCellValue(row, colIdx++, true);
            if (localeString == null) {
                break;
            }
            if (StandardLanguage.isStandardLanguage(localeString,
                    StandardLanguage.COLUMN_HEADING_SUFFIX)) {
                localeStringList.add(localeString);
                continue;
            }
            validateLocale(localeString);
            localeString = localeString.toLowerCase();
            localeStringList.add(localeString);
        }
    }
    return localeStringList;
}