Java HSSFSheet類代碼示例

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
private void makeHeader ( final List<Field> columns, final HSSFSheet sheet )
{
    final Font font = sheet.getWorkbook ().createFont ();
    font.setFontName ( "Arial" );
    font.setBoldweight ( Font.BOLDWEIGHT_BOLD );
    font.setColor ( HSSFColor.WHITE.index );

    final CellStyle style = sheet.getWorkbook ().createCellStyle ();
    style.setFont ( font );
    style.setFillForegroundColor ( HSSFColor.BLACK.index );
    style.setFillPattern ( PatternFormatting.SOLID_FOREGROUND );

    final HSSFRow row = sheet.createRow ( 0 );

    for ( int i = 0; i < columns.size (); i++ )
    {
        final Field field = columns.get ( i );

        final HSSFCell cell = row.createCell ( i );
        cell.setCellValue ( field.getHeader () );
        cell.setCellStyle ( style );
    }
}
 

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
/**
 * Read the Excel 2003-2007
 * 
 * @param path
 *            the path of the Excel
 * @return
 * @throws IOException
 */
public static String readXls(String path) throws IOException {
	InputStream is = new FileInputStream(path);
	HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
	StringBuffer sb = new StringBuffer("");
	// Read the Sheet
	for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
		HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
		if (hssfSheet == null) {
			continue;
		}
		// Read the Row
		for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			if (hssfRow != null) {
				HSSFCell no = hssfRow.getCell(0);
				HSSFCell name = hssfRow.getCell(1);
				sb.append(no + ":" + name);
				sb.append(";");
			}
		}
	}
	return sb.toString().substring(0, sb.toString().length() - 1);
}
 

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
private void insertItemToSheet(String table, HSSFSheet sheet, ArrayList<String> columns) {
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    Cursor cursor = database.rawQuery("select * from " + table, null);
    cursor.moveToFirst();
    int n = 1;
    while (!cursor.isAfterLast()) {
        HSSFRow rowA = sheet.createRow(n);
        for (int j = 0; j < columns.size(); j++) {
            HSSFCell cellA = rowA.createCell(j);
            if (cursor.getType(j) == Cursor.FIELD_TYPE_BLOB) {
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) j, n, (short) (j + 1), n + 1);
                anchor.setAnchorType(3);
                patriarch.createPicture(anchor, workbook.addPicture(cursor.getBlob(j), HSSFWorkbook.PICTURE_TYPE_JPEG));
            } else {
                cellA.setCellValue(new HSSFRichTextString(cursor.getString(j)));
            }
        }
        n++;
        cursor.moveToNext();
    }
    cursor.close();
}
 

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
private static void setColumWidth(ExcelFileType type,ExportInfo exportInfo,
		List<Field> availableFields,Sheet sheet) {
	Map<Field, ExportFieldInfo> fieldInfoMap = exportInfo.getFieldInfoMap();
	for(int i = 0 ; i < availableFields.size() ; i++){
		Field field = availableFields.get(i);
		ExportFieldInfo excelFieldInfo = fieldInfoMap.get(field);
		if (excelFieldInfo.getWidth() != null) {
			sheet.setColumnWidth(i, excelFieldInfo.getWidth()*256);	
			continue;
		}
		if(excelFieldInfo.getAutoWidth() != null){
			if(ExcelFileType.XLS == type){
				HSSFSheet hSheet = (HSSFSheet) sheet;
				hSheet.autoSizeColumn(i);
			}else if(ExcelFileType.XLSX == type){
				XSSFSheet xSheet = (XSSFSheet) sheet;
				xSheet.autoSizeColumn(i);
			}
		}
	}
}
 

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
/**
 * 判斷單元格是否是合並的單格，如果是，獲取其合並的行數。
 */
private static int getMergerCellRegionRow(HSSFSheet sheet, int cellRow, int cellCol) throws Throwable {
	int retVal = 0;
	int sheetMergerCount = sheet.getNumMergedRegions();
	for (int i = 0; i < sheetMergerCount; i++) {
		CellRangeAddress cra = sheet.getMergedRegion(i);
		int firstRow = cra.getFirstRow(); // 合並單元格CELL起始行
		int firstCol = cra.getFirstColumn(); // 合並單元格CELL起始列
		int lastRow = cra.getLastRow(); // 合並單元格CELL結束行
		int lastCol = cra.getLastColumn(); // 合並單元格CELL結束列
		if (cellRow >= firstRow && cellRow <= lastRow) { // 判斷該單元格是否是在合並單元格中
			if (cellCol >= firstCol && cellCol <= lastCol) {
				retVal = lastRow - firstRow + 1; // 得到合並的行數
				break;
			}
		}
	}
	return retVal;
}
 

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
/**
 * 獲取合並單元格式
 *
 * @param sheet
 * @param row
 * @param columnFrom
 * @param columnTo
 * @return
 */
private static void setRegionBorder(HSSFSheet sheet, int row, int columnFrom, int columnTo) {
    CellRangeAddress region = new CellRangeAddress(row, row, columnFrom, columnTo);
    sheet.addMergedRegion(region);
    final short border = CellStyle.BORDER_THIN;
    HSSFWorkbook wb = sheet.getWorkbook();
    RegionUtil.setBorderBottom(border, region, sheet, wb);
    RegionUtil.setBorderTop(border, region, sheet, wb);
    RegionUtil.setBorderLeft(border, region, sheet, wb);
    RegionUtil.setBorderRight(border, region, sheet, wb);
    RegionUtil.setBottomBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
    RegionUtil.setTopBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
    RegionUtil.setLeftBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
    RegionUtil.setRightBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
}
 

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
public static void writeXLSFile() throws IOException {

			HSSFWorkbook wbObj = new HSSFWorkbook();
			HSSFSheet sheet = wbObj.createSheet(sheetName);

			for (int row = 0; row < tableData.size(); row++) {
				HSSFRow rowObj = sheet.createRow(row);
				rowData = tableData.get(row);
				for (int col = 0; col < rowData.size(); col++) {
					HSSFCell cellObj = rowObj.createCell(col);
					cellObj.setCellValue(rowData.get(col));
				}
			}

			FileOutputStream fileOut = new FileOutputStream(excelFileName);
			wbObj.write(fileOut);
			wbObj.close();
			fileOut.flush();
			fileOut.close();
		}
 

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
/**
 * @param newSheet the sheet to create from the copy.
 * @param sheet the sheet to copy.
 * @param copyStyle true copy the style.
 */
public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle) {
    int maxColumnNum = 0;
    Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;
    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
        HSSFRow srcRow = sheet.getRow(i);
        HSSFRow destRow = newSheet.createRow(i);
        if (srcRow != null) {
            Util.copyRow(sheet, newSheet, srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
    }
   //Util.copyPictures(newSheet,sheet) ;
}
 

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
public Map<String, BoundaryPoint> parse(InputStream is) throws IOException {
    Map<String, BoundaryPoint> boundaryPoints = new HashMap<>();
    HSSFWorkbook workbook = new HSSFWorkbook(is);
    HSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    rowIterator.next();
    rowIterator.next();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Cell boundaryPointNameCell = row.getCell(13);
        Cell borderFromCell = row.getCell(14);
        Cell borderToCell = row.getCell(15);
        String boundaryPointName = boundaryPointNameCell.getStringCellValue();
        if (boundaryPointName.equals("-")) {
            continue;
        }
        Country borderFrom = toCountry(borderFromCell.getStringCellValue());
        Country borderTo = toCountry(borderToCell.getStringCellValue());
        boundaryPoints.put(boundaryPointName, new BoundaryPoint(boundaryPointName, borderFrom, borderTo));
    }
    return boundaryPoints;
}
 

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
private HSSFWorkbook createWorkbook() {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();

    // Add dummy lines
    sheet.createRow(0).createCell(0).setCellValue("First dummy row");
    sheet.createRow(1).createCell(0).setCellValue("Second dummy row");

    HSSFRow row = sheet.createRow(2);
    row.createCell(13).setCellValue("BoundaryPoint FR-BE");
    row.createCell(14).setCellValue("France");
    row.createCell(15).setCellValue("Belgium");

    return workbook;
}
 

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
/**
 * 判斷單元格在不在合並單元格範圍內，如果是，獲取其合並的列數。
 */
private static int getMergerCellRegionCol(HSSFSheet sheet, int cellRow, int cellCol) throws Throwable {
	int retVal = 0;
	int sheetMergerCount = sheet.getNumMergedRegions();
	for (int i = 0; i < sheetMergerCount; i++) {
		CellRangeAddress cra = sheet.getMergedRegion(i);
		int firstRow = cra.getFirstRow(); // 合並單元格CELL起始行
		int firstCol = cra.getFirstColumn(); // 合並單元格CELL起始列
		int lastRow = cra.getLastRow(); // 合並單元格CELL結束行
		int lastCol = cra.getLastColumn(); // 合並單元格CELL結束列
		if (cellRow >= firstRow && cellRow <= lastRow) { // 判斷該單元格是否是在合並單元格中
			if (cellCol >= firstCol && cellCol <= lastCol) {
				retVal = lastCol - firstCol + 1; // 得到合並的列數
				break;
			}
		}
	}
	return retVal;
}
 

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
/**
 * Creates XLS document from given list of {@link PollOption} objects.
 * 
 * @param results List of {@link PollOption} objects.
 * @return XLS document.
 */
public static HSSFWorkbook getXLS(List<PollOption> results) {
    HSSFWorkbook document = new HSSFWorkbook();
    HSSFSheet sheet = document.createSheet("Results");
    HSSFRow rowhead = sheet.createRow(0);
    rowhead.createCell(0).setCellValue("Ime opcije:");
    rowhead.createCell(1).setCellValue("Broj glasova:");

    for (int i = 0; i < results.size(); i++) {
        HSSFRow row = sheet.createRow(i + 1);
        row.createCell(0).setCellValue(results.get(i).getName());
        row.createCell(1).setCellValue(
                Double.valueOf(results.get(i).getVotes()));
    }
    return document;
}
 

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
/**
 * Creates XLS document based on given input parameters.
 * 
 * @param a Start number.
 * @param b End number.
 * @param n Last power.
 * @return Created XLS document.
 */
private HSSFWorkbook getXLS(Integer a, Integer b, Integer n) {
    HSSFWorkbook workbook = new HSSFWorkbook();

    for (int i = 1; i <= n; i++) {
        HSSFSheet sheet = workbook.createSheet(i + "-th power.");
        HSSFRow rowHead = sheet.createRow(0);
        rowHead.createCell(0).setCellValue("x");
        rowHead.createCell(1).setCellValue("x^" + i);

        int rowCounter = 1;
        for (int j = a; j <= b; j++) {
            HSSFRow row = sheet.createRow(rowCounter++);
            row.createCell(0).setCellValue(Double.valueOf(j));
            row.createCell(1).setCellValue(Math.pow(Double.valueOf(j), i));
        }
    }
    return workbook;
}
 

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
/**
 * Creates XLS document based from given list of bands.
 * 
 * @param results List of bands.
 * @return XLS document.
 */
public static HSSFWorkbook getXLS(List<Band> results) {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Voting results");
    HSSFRow rowHead = sheet.createRow(0);
    rowHead.createCell(0).setCellValue("Band name:");
    rowHead.createCell(1).setCellValue("Number of votes:");

    for (int i = 0, size = results.size(); i < size; i++) {
        HSSFRow row = sheet.createRow(i + 1);
        row.createCell(0).setCellValue(results.get(i).getName());
        row.createCell(1).setCellValue(
                Double.valueOf(results.get(i).getVotes()));
    }
    return workbook;
}
 

import org.apache.poi.hssf.usermodel.HSSFSheet; //導入依賴的package包/類
private Span getSpan(HSSFSheet sheet,int row ,int column){
	int sheetMergeCount = sheet.getNumMergedRegions(); 
	for (int i = 0; i < sheetMergeCount; i++) {
		CellRangeAddress range = sheet.getMergedRegion(i);
		int firstColumn = range.getFirstColumn();
		int lastColumn = range.getLastColumn();
		int firstRow = range.getFirstRow();
		if(row == firstRow && column==firstColumn){  
			int lastRow = range.getLastRow();
			int rowSpan=lastRow-firstRow;
			if(rowSpan>0){
				rowSpan++;
			}
			int colSpan=lastColumn-firstColumn;
			if(colSpan>0){
				colSpan++;
			}
			return new Span(rowSpan,colSpan);
		}
	}
	return new Span(0,0);
}