org.apache.poi.hssf.usermodel.HSSFSheet Java Examples

public void setImage(HSSFWorkbook workbook, HSSFSheet sheet) {
		CellLocation cellLocation = POIUtils.findMatchCell(sheet, "\\"
				+ KEYWORD_ER + ".*");
System.out.println(cellLocation);
		if (cellLocation != null) {
			int width = -1;
			int height = -1;

			String value = POIUtils.getCellValue(sheet, cellLocation);

			int startIndex = value.indexOf("(");
			if (startIndex != -1) {
				int middleIndex = value.indexOf(",", startIndex + 1);
				if (middleIndex != -1) {
					width = Integer.parseInt(value.substring(startIndex + 1,
							middleIndex).trim());
					height = Integer.parseInt(value.substring(middleIndex + 1,
							value.length() - 1).trim());
				}
			}

			this.setImage(workbook, sheet, cellLocation, width, height);
		}
	}
 

/** 
    * 获取Excel2003图片 
    * @param sheet 当前sheet对象 
    * @param workbook 工作簿对象 
    * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData 
    */  
   @SuppressWarnings("unchecked")
public static Map<String, PictureData> getSheetPictrues03(HSSFSheet sheet, 
   		HSSFWorkbook workbook) {  
       Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>(); 
       List<HSSFPictureData> pictures = workbook.getAllPictures();  
       if (pictures.size() != 0) {  
           for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {  
               HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();  
               if (shape instanceof HSSFPicture) {  
                   HSSFPicture pic = (HSSFPicture) shape;  
                   int pictureIndex = pic.getPictureIndex() - 1;  
                   HSSFPictureData picData = pictures.get(pictureIndex);  
                   String picIndex = String.valueOf(anchor.getRow1()) + "_"  
                           + String.valueOf(anchor.getCol1());  
                   sheetIndexPicMap.put(picIndex, picData);  
               }
           }  
           return sheetIndexPicMap;  
       } else {  
           return (Map<String, PictureData>) sheetIndexPicMap.put(null, null);  
       }  
   }
 

public static int getIntCellValue(final HSSFSheet sheet, final int r, final int c) {
    final HSSFRow row = sheet.getRow(r);
    if (row == null) {
        return 0;
    }
    final HSSFCell cell = row.getCell(c);

    try {
        if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
            return 0;
        }
    } catch (final RuntimeException e) {
        System.err.println("Exception at sheet name:" + sheet.getSheetName() + ", row:" + (r + 1) + ", col:" + (c + 1));
        throw e;
    }

    return (int) cell.getNumericCellValue();
}
 

public static XSSFWorkbook merge(XSSFWorkbook source, HSSFSheet sheet) {
	XSSFWorkbook destinationWorkbook = source;
	XSSFSheet destinationSheet = destinationWorkbook.getSheetAt(0);

	List<CellStyle> styleMap = new ArrayList<CellStyle>();

	for (short i = 0; i < destinationWorkbook.getNumCellStyles(); i++) {
		styleMap.add(destinationWorkbook.getCellStyleAt(i));
	}

	copySheetSettings(destinationSheet, sheet);
	copySheet(styleMap, sheet, destinationSheet);
	copyPictures(destinationSheet, sheet);

	refreshFormula(destinationWorkbook);

	return destinationWorkbook;
}
 

@Override
public Map<Integer, GovernmentBodyAnnualSummary> getDataPerGovernmentBody(final String name) {
	final Map<Integer, GovernmentBodyAnnualSummary> map = new TreeMap<>();
	try {
		final HSSFWorkbook myWorkBook = createGovermentBodyWorkBook();

		for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) {
			final HSSFSheet mySheet = myWorkBook.getSheetAt(sheetNr);

			addDataForYearToMap(name, map, mySheet);
		}
		myWorkBook.close();
	} catch (

	final IOException e) {
		LOGGER.warn("Problem loading", e);
	}

	return map;
}
 

protected void addImageToSheet(int colNumber, int rowNumber, HSSFSheet sheet, BufferedImage image, double reqImageWidthMM, double reqImageHeightMM, int resizeBehaviour) throws IOException {
      ClientAnchorDetail colClientAnchorDetail = fitImageToColumns(sheet, colNumber, reqImageWidthMM, resizeBehaviour);
      ClientAnchorDetail rowClientAnchorDetail = fitImageToRows(sheet, rowNumber, reqImageHeightMM, resizeBehaviour);

      HSSFClientAnchor anchor = new HSSFClientAnchor(0,
                                    0,
                                    colClientAnchorDetail.getInset(),
                                    rowClientAnchorDetail.getInset(),
                                    (short)colClientAnchorDetail.getFromIndex(),
                                    rowClientAnchorDetail.getFromIndex(),
                                    (short)colClientAnchorDetail.getToIndex(),
                                    rowClientAnchorDetail.getToIndex());

      anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);
      
      ByteArrayOutputStream bytes = new ByteArrayOutputStream();
ImageIO.write(image, "PNG", bytes);

      int index = sheet.getWorkbook().addPicture(bytes.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG);

      HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
      patriarch.createPicture(anchor, index);
  }
 

private void setIndexMatrix(HSSFWorkbook workbook, HSSFSheet sheet,
		ERTable table) {
	CellLocation logicalIndexCellLocation = POIUtils.findCell(sheet,
			KEYWORD_LOGICAL_INDEX_MATRIX);

	if (logicalIndexCellLocation != null) {
		if (this.logicalIndexMatrixCellStyle == null) {
			this.logicalIndexMatrixCellStyle = this.createMatrixCellStyle(
					workbook, sheet, logicalIndexCellLocation);
		}
		setIndexMatrix(workbook, sheet, table, logicalIndexCellLocation,
				this.logicalIndexMatrixCellStyle, true);
	}

	CellLocation physicalIndexCellLocation = POIUtils.findCell(sheet,
			KEYWORD_PHYSICAL_INDEX_MATRIX);

	if (physicalIndexCellLocation != null) {
		if (this.physicalIndexMatrixCellStyle == null) {
			this.physicalIndexMatrixCellStyle = this.createMatrixCellStyle(
					workbook, sheet, physicalIndexCellLocation);
		}
		setIndexMatrix(workbook, sheet, table, physicalIndexCellLocation,
				this.physicalIndexMatrixCellStyle, false);
	}
}
 

@Override
public void generate(final ProgressMonitor monitor, final HSSFWorkbook workbook, final int sheetNo, final boolean useLogicalNameAsSheetName, final Map<String, Integer> sheetNameMap, final Map<String, ObjectModel> sheetObjectMap, final ERDiagram diagram, final Map<String, LoopDefinition> loopDefinitionMap) throws InterruptedException {
    clear();

    for (final ERTable table : diagram.getDiagramContents().getContents().getTableSet()) {
        if (diagram.getCurrentCategory() != null && !diagram.getCurrentCategory().contains(table)) {
            continue;
        }

        for (final Index index : table.getIndexes()) {
            final String name = index.getName();

            final HSSFSheet newSheet = createNewSheet(workbook, sheetNo, name, sheetNameMap);

            final String sheetName = workbook.getSheetName(workbook.getSheetIndex(newSheet));
            monitor.subTaskWithCounter("[Index] " + sheetName);

            sheetObjectMap.put(sheetName, index);

            setIndexData(workbook, newSheet, index);
            monitor.worked(1);
        }
    }
}
 

void writeBody(HSSFSheet sheet){
		Set<String> keySet = getColumnJson().keySet();
		List<T> ts = getData();
		for (T t:ts) {
//			Class cls = t.getClass();
			int cellNumber = 0;//将cellNumber从0开始
			HSSFRow row = sheet.createRow(addRowNumber());//创建新的一行
			for(String key:keySet){
				try {
					HSSFCell cell = row.createCell(cellNumber++);
					Object value = getValueByKey(t, key);
					setCellValue(cell, value);
					pubMaxValue(key, value);
				} catch (Exception e) {
					throw new RuntimeException("writeBody", e);
				}
			}
		}
	}
 

public static CellLocation findCell(final HSSFSheet sheet, final String[] strs) {
    for (int rowNum = sheet.getFirstRowNum(); rowNum < sheet.getLastRowNum() + 1; rowNum++) {
        final HSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            continue;
        }

        for (int i = 0; i < strs.length; i++) {
            final Integer colNum = findColumn(row, strs[i]);

            if (colNum != null) {
                return new CellLocation(rowNum, colNum.shortValue());
            }
        }
    }

    return null;
}
 

private static void setDataValidationList(int j, int k, int l, int m, String data, HSSFSheet sheet) {
	// 设置下拉列表的内容
	String[] textlist = data.split(",");
	// 加载下拉列表内容
	DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
	// 设置数据有效性加载在哪个单元格上。
	
	// 四个参数分别是：起始行、终止行、起始列、终止列
	CellRangeAddressList regions = new CellRangeAddressList(j, k, l, m);
	/*   CellRangeAddressList regions = new CellRangeAddressList(
	      6,5, 6,5);*/
	// 数据有效性对象
	HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
	data_validation_list.setSuppressDropDownArrow(false);
	sheet.addValidationData(data_validation_list);
}
 

@Override
protected void setHeaders(final HSSFSheet sheet) {
    addHeaderCell(sheet, getHeaderInBundle("processNumber"), 0);
    addHeaderCell(sheet, getHeaderInBundle("studentNumber"), 1);
    addHeaderCell(sheet, getHeaderInBundle("studentName"), 2);
    addHeaderCell(sheet, getHeaderInBundle("dateOfBirth"), 3);
    addHeaderCell(sheet, getHeaderInBundle("identification"), 4);
    addHeaderCell(sheet, getHeaderInBundle("idDocumentType"), 5);
    addHeaderCell(sheet, getHeaderInBundle("phdProgram"), 6);
    addHeaderCell(sheet, getHeaderInBundle("focusArea"), 7);
    addHeaderCell(sheet, getHeaderInBundle("whenStartStudies"), 8);
    addHeaderCell(sheet, getHeaderInBundle("currentState"), 9);
    addHeaderCell(sheet, getHeaderInBundle("stateDate"), 10);
    addHeaderCell(sheet, getHeaderInBundle("migrated"), 11);
    addHeaderCell(sheet, getHeaderInBundle("studentEmail"), 12);
    addHeaderCell(sheet, getHeaderInBundle("discussionDate"), 13);
    addHeaderCell(sheet, getHeaderInBundle("grade"), 14);
    addHeaderCell(sheet, getHeaderInBundle("caseThesisState"), 15);
    addHeaderCell(sheet, getHeaderInBundle("caseCatState"), 16);
    addHeaderCell(sheet, getHeaderInBundle("syllabusState"), 17);
    addHeaderCell(sheet, getHeaderInBundle("applicationProcessState"), 18);
}
 

@Test
public void exportExcelWithStyle() {
	try {
		String filePath = TestUtil.DOC_PATH + File.separator
				+ Globals.EXPORT_PRODUCT;
		OutputStream os = new FileOutputStream(filePath);
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(Globals.SHEETNAME);
		HSSFRichTextString richString = new HSSFRichTextString(
				TestUtil.RICH_TEXT_STRING);
		HSSFFont font = wb.createFont();
		font.setColor(IndexedColors.BLUE.index);
		richString.applyFont(font);
		sheet.createRow(0).createCell(0).setCellValue(richString);
		wb.write(os);
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

/**
 * Creates a new Color Scale / Color Gradient formatting
 * 
 * @param sheet the sheet
 * 
 * @return a new Color Scale / Color Gradient formatting
 */
public static CFRule12Record createColorScale(HSSFSheet sheet) {
    int numPoints = 3;
    ExtendedColor[] colors = new ExtendedColor[numPoints];
    ColorGradientThreshold[] ts = new ColorGradientThreshold[numPoints];
    for (int i=0; i<ts.length; i++) {
        ts[i] = new ColorGradientThreshold();
        colors[i] = new ExtendedColor();
    }
    
    CFRule12Record r = new CFRule12Record(CONDITION_TYPE_COLOR_SCALE, 
                                          ComparisonOperator.NO_COMPARISON);
    ColorGradientFormatting cgf = r.createColorGradientFormatting();
    cgf.setNumControlPoints(numPoints);
    cgf.setThresholds(ts);
    cgf.setColors(colors);
    return r;
}
 

public static void readExcel(String filePth) throws Exception {
	InputStream is = new FileInputStream(filePth);
	//创建工作薄
	//XSSFWorkbook hwb = new XSSFWorkbook(is);
	HSSFWorkbook hwb = new HSSFWorkbook(new POIFSFileSystem(is));
	//得到sheet
	for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
		HSSFSheet sheet = hwb.getSheetAt(i);
		int rows = sheet.getPhysicalNumberOfRows();
		//遍历每一行
		for (int j = 0; j < rows; j++) {
			HSSFRow hr = sheet.getRow(j);
			Iterator<?> it = hr.iterator();
			while(it.hasNext()){
				String context = it.next().toString();
				System.out.println(context);
			}
		}
	}
	hwb.close();
}
 

public static void reader(String filePath) {
	try {
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow row = sheet.getRow(3);
		HSSFCell cell = row.getCell((short) 0);
		int type = cell.getCellType();
		String msg = getCellStringValue(cell);
		System.out.println(type + ":" + msg);
	} catch (IOException e) {
		e.printStackTrace();
	}
}
 

/**
 * Write protect Sheet by setting password works only for xls output at the moment
 */
protected void protectSheet( Sheet sheet, String password ) {
  if ( sheet instanceof HSSFSheet ) {
    // Write protect Sheet by setting password
    // works only for xls output at the moment
    sheet.protectSheet( password );
  }
}
 

/**
 * 替换Excel模板文件内容
 *
 * @param datas
 *            文档数据
 * @param sourceFilePath
 *            Excel模板文件路径
 * @param targetFilePath
 *            Excel生成文件路径
 */
public static boolean replaceModel(List<ExcelReplaceDataVO> datas,
		String sourceFilePath, String targetFilePath) {
	boolean bool = true;
	try {
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
				sourceFilePath));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		for (ExcelReplaceDataVO data : datas) {
			// 获取单元格内容
			HSSFRow row = sheet.getRow(data.getRow());
			HSSFCell cell = row.getCell((short) data.getColumn());
			String str = cell.getStringCellValue();
			// 替换单元格内容
			str = str.replace(data.getKey(), data.getValue());
			// 写入单元格内容
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(str);
		}
		// 输出文件
		FileOutputStream fileOut = new FileOutputStream(targetFilePath);
		wb.write(fileOut);
		fileOut.close();
	} catch (Exception e) {
		bool = false;
		e.printStackTrace();
	}
	return bool;
}
 

@Override
public void processWorkbook(HSSFWorkbook workbook, TopicMap topicMap) {
    int numberOfSheets = workbook.getNumberOfSheets();
    for(int i=0; i<numberOfSheets && !forceStop(); i++) {
        HSSFSheet sheet = workbook.getSheetAt(i);
        processSheet(sheet, topicMap);
    }
}
 

/**
 * 读取excel到JsonArray
 * @param sheet
 * @param map 列对应的column
 * @param rowNumber
 * @param array
 * @return 当前的rowNumber
 */
   private static int readSheetBody(HSSFSheet sheet, Map<Integer, String> map, int rowNumber, JSONArray array) {
	if (logger.isDebugEnabled()) {
		logger.debug("readSheetBody(HSSFSheet, Map<Integer,String>, int, JSONArray) - start"); //$NON-NLS-1$
	}

   	if(sheet!=null){
		int end = sheet.getLastRowNum();//获取最后一行
		for (; rowNumber<=end;rowNumber++) {
			HSSFRow row = sheet.getRow(rowNumber);
			if (row!=null) {
				JSONObject jsonObject = new JSONObject();
				Iterator<Cell> iterator = row.cellIterator();
				while (iterator.hasNext()) {
					Cell cell = iterator.next();
					if (cell!=null) {
						int cellIndex = cell.getColumnIndex();
						String key = map.get(cellIndex);
						String cellValue = getStringValue(cell);
						if (key!=null&&cellValue!=null&&!cellValue.equals("null")) {
							readSheetCell(jsonObject,key,cellValue);
						}
						
					}
				}
				array.add(jsonObject);
			}
		}
	}

	if (logger.isDebugEnabled()) {
		logger.debug("readSheetBody(HSSFSheet, Map<Integer,String>, int, JSONArray) - end"); //$NON-NLS-1$
	}
	return rowNumber;
}
 

public static String getCellValue(final HSSFSheet sheet, final CellLocation location) {
    final HSSFRow row = sheet.getRow(location.r);
    final HSSFCell cell = row.getCell(location.c);

    final HSSFRichTextString cellValue = cell.getRichStringCellValue();

    return cellValue.toString();
}
 

/**
 * {@inheritDoc}
 */
@Override
public void generate(final ProgressMonitor monitor, final HSSFWorkbook workbook, final int sheetNo, final boolean useLogicalNameAsSheetName, final Map<String, Integer> sheetNameMap, final Map<String, ObjectModel> sheetObjectMap, final ERDiagram diagram, final Map<String, LoopDefinition> loopDefinitionMap) throws InterruptedException {

    final HSSFSheet sheet = workbook.getSheetAt(sheetNo);

    setSheetListData(workbook, sheet, sheetObjectMap, diagram);
}
 

@SuppressWarnings("resource")
private void assertXlsOutputCorrect(byte[] output) throws Exception {
	HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(output));
	HSSFSheet sheet = workbook.getSheetAt(0);
	assertNotNull("Sheet should not be null", sheet);
	HSSFRow row = sheet.getRow(3);
	HSSFCell cell = row.getCell((short) 1);
	assertNotNull("Cell should not be null", cell);
	assertEquals("Cell content should be Dear Lord!", "Dear Lord!", cell.getRichStringCellValue().getString());
}
 

@Override
   public int getNumRows(MultipartFile fileItem) throws IOException {
HSSFSheet sheet = getSheet(fileItem);
int startRow = sheet.getFirstRowNum();
int endRow = sheet.getLastRowNum();
return endRow - startRow;
   }
 

@Override
   public boolean isRolesSpreadsheet(MultipartFile fileItem) throws IOException {
HSSFSheet sheet = getSheet(fileItem);
HSSFRow row = sheet.getRow(sheet.getFirstRowNum());
String string = parseStringCell(row.getCell(ImportService.ORGANISATION));
return (StringUtils.equals(string, "* organisation")) ? true : false;
   }
 

@Test
public void dumpXlsInFile() throws IOException {
    paramsXls.put("file", xlsTempFile.getAbsolutePath());

    try (CurlResponse curlResponse = createRequest(node, path, paramsXls).execute()) {
        assertAcknowledged(curlResponse, xlsTempFile);
        try (InputStream is = new FileInputStream(xlsTempFile)) {
            final POIFSFileSystem fs = new POIFSFileSystem(is);
            final HSSFWorkbook book = new HSSFWorkbook(fs);
            final HSSFSheet sheet = book.getSheetAt(0);
            assertEquals(docNumber, sheet.getLastRowNum());
        }
    }
}
 

private void prepareMapping(HSSFSheet sheetMapping) {
    if (sheetMapping == null) {
        return;
    }
    mMapping = new HashMap<String, String>();
    Iterator<Row> it = sheetMapping.rowIterator();
    while (it.hasNext()) {
        Row row = it.next();
        mMapping.put(row.getCell(0).getStringCellValue(), row.getCell(1).getStringCellValue());
    }
}
 

public static HSSFSheet createNewSheet(final HSSFWorkbook workbook, final int sheetNo, final String name, final Map<String, Integer> sheetNameMap) {
    final HSSFSheet sheet = workbook.cloneSheet(sheetNo);
    final int newSheetNo = workbook.getSheetIndex(sheet);

    workbook.setSheetName(newSheetNo, decideSheetName(name, sheetNameMap));

    return sheet;
}
 

public static void setCellValue(HSSFSheet sheet, CellLocation location,
		String value) {
	HSSFRow row = sheet.getRow(location.r);
	HSSFCell cell = row.getCell(location.c);

	HSSFRichTextString text = new HSSFRichTextString(value);
	cell.setCellValue(text);
}
 

public void processSheet(HSSFSheet sheet, TopicMap tm) {
    Iterator<Row> rowIterator = sheet.iterator();
    boolean isFirst = true;
    occurrenceTypes = new HashMap();
    while(rowIterator.hasNext() && !forceStop()) {
        Row row = rowIterator.next();
        if(isFirst && FIRST_ROW_CONTAINS_OCCURRENCE_TYPES) {
            processRowAsOccurrenceTypes(row, tm);
            isFirst = false;
        }
        else {
            processRow(row, tm);
        }
    }
}