Java HSSFRow.getLastCellNum方法代码示例

import org.apache.poi.hssf.usermodel.HSSFRow; //导入方法依赖的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.HSSFRow; //导入方法依赖的package包/类
public static Integer findMatchColumn(final HSSFRow row, final String str) {
    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
        final HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }

        if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
            continue;
        }

        final HSSFRichTextString cellValue = cell.getRichStringCellValue();

        if (cellValue.getString().matches(str)) {
            return Integer.valueOf(colNum);
        }
    }

    return null;
}
 

import org.apache.poi.hssf.usermodel.HSSFRow; //导入方法依赖的package包/类
public static Integer findColumn(final HSSFRow row, final String str) {
    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
        final HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }

        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            final HSSFRichTextString cellValue = cell.getRichStringCellValue();

            if (str.equals(cellValue.getString())) {
                return Integer.valueOf(colNum);
            }
        }
    }

    return null;
}
 

import org.apache.poi.hssf.usermodel.HSSFRow; //导入方法依赖的package包/类
public static void copyRow(final HSSFSheet oldSheet, final HSSFSheet newSheet, final int oldStartRowNum, final int oldEndRowNum, final int newStartRowNum) {
    final HSSFRow oldAboveRow = oldSheet.getRow(oldStartRowNum - 1);

    int newRowNum = newStartRowNum;

    for (int oldRowNum = oldStartRowNum; oldRowNum <= oldEndRowNum; oldRowNum++) {
        POIUtils.copyRow(oldSheet, newSheet, oldRowNum, newRowNum++);
    }

    final HSSFRow newTopRow = newSheet.getRow(newStartRowNum);

    if (oldAboveRow != null) {
        for (int colNum = newTopRow.getFirstCellNum(); colNum <= newTopRow.getLastCellNum(); colNum++) {
            final HSSFCell oldAboveCell = oldAboveRow.getCell(colNum);
            if (oldAboveCell != null) {
                final HSSFCell newTopCell = newTopRow.getCell(colNum);
                newTopCell.getCellStyle().setBorderTop(oldAboveCell.getCellStyle().getBorderBottom());
            }
        }
    }
}
 

import org.apache.poi.hssf.usermodel.HSSFRow; //导入方法依赖的package包/类
public static List<HSSFCellStyle> copyCellStyle(final HSSFWorkbook workbook, final HSSFRow row) {
    final List<HSSFCellStyle> cellStyleList = new ArrayList<HSSFCellStyle>();

    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {

        final HSSFCell cell = row.getCell(colNum);
        if (cell != null) {
            final HSSFCellStyle style = cell.getCellStyle();
            final HSSFCellStyle newCellStyle = copyCellStyle(workbook, style);
            cellStyleList.add(newCellStyle);
        } else {
            cellStyleList.add(null);
        }
    }

    return cellStyleList;
}
 

import org.apache.poi.hssf.usermodel.HSSFRow; //导入方法依赖的package包/类
public static Integer findColumn(HSSFRow row, String str) {
	for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
		HSSFCell cell = row.getCell(colNum);

		if (cell == null) {
			continue;
		}

		if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
			HSSFRichTextString cellValue = cell.getRichStringCellValue();

			if (str.equals(cellValue.getString())) {
				return Integer.valueOf(colNum);
			}
		}
	}

	return null;
}
 

import org.apache.poi.hssf.usermodel.HSSFRow; //导入方法依赖的package包/类
public static Integer findMatchColumn(HSSFRow row, String str) {
	for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
		HSSFCell cell = row.getCell(colNum);

		if (cell == null) {
			continue;
		}

		if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING) {
			continue;
		}

		HSSFRichTextString cellValue = cell.getRichStringCellValue();

		if (cellValue.getString().matches(str)) {
			return Integer.valueOf(colNum);
		}
	}

	return null;
}
 

import org.apache.poi.hssf.usermodel.HSSFRow; //导入方法依赖的package包/类
public static void copyRow(HSSFSheet oldSheet, HSSFSheet newSheet,
		int oldStartRowNum, int oldEndRowNum, int newStartRowNum) {
	HSSFRow oldAboveRow = oldSheet.getRow(oldStartRowNum - 1);

	int newRowNum = newStartRowNum;

	for (int oldRowNum = oldStartRowNum; oldRowNum <= oldEndRowNum; oldRowNum++) {
		POIUtils.copyRow(oldSheet, newSheet, oldRowNum, newRowNum++);
	}

	HSSFRow newTopRow = newSheet.getRow(newStartRowNum);

	if (oldAboveRow != null) {
		for (int colNum = newTopRow.getFirstCellNum(); colNum <= newTopRow
				.getLastCellNum(); colNum++) {
			HSSFCell oldAboveCell = oldAboveRow.getCell(colNum);
			if (oldAboveCell != null) {
				HSSFCell newTopCell = newTopRow.getCell(colNum);
				newTopCell.getCellStyle().setBorderTop(
						oldAboveCell.getCellStyle().getBorderBottom());
			}
		}
	}
}
 

import org.apache.poi.hssf.usermodel.HSSFRow; //导入方法依赖的package包/类
public static List<HSSFCellStyle> copyCellStyle(HSSFWorkbook workbook,
		HSSFRow row) {
	List<HSSFCellStyle> cellStyleList = new ArrayList<HSSFCellStyle>();

	for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {

		HSSFCell cell = row.getCell(colNum);
		if (cell != null) {
			HSSFCellStyle style = cell.getCellStyle();
			HSSFCellStyle newCellStyle = copyCellStyle(workbook, style);
			cellStyleList.add(newCellStyle);
		} else {
			cellStyleList.add(null);
		}
	}

	return cellStyleList;
}
 

import org.apache.poi.hssf.usermodel.HSSFRow; //导入方法依赖的package包/类
public int parseGroupSpreadsheet(FormFile fileItem, Long groupingID, Map<String, Set<String>> groups)
    throws IOException {
POIFSFileSystem fs = new POIFSFileSystem(fileItem.getInputStream());
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);

int startRow = sheet.getFirstRowNum();
int endRow = sheet.getLastRowNum();
int skipped = 0;

for (int i = startRow + 1; i < (endRow + 1); i++) {
    HSSFRow row = sheet.getRow(i);
    String login = parseStringCell(row.getCell(0));
    if (login != null) {
	login = login.trim();
	if (login.length() > 0) {
	    String groupName = row.getLastCellNum() > 3 ? parseStringCell(row.getCell(3)) : null;
	    groupName = groupName != null ? groupName.trim() : null;
	    if (groupName == null || groupName.length() == 0) {
		skipped++;
		GroupingUploadAJAXAction.log.warn("Unable to add learner " + login
			+ " for group in related to grouping " + groupingID + " as group name is missing.");
	    } else {
		Set<String> users = groups.get(groupName);
		if (users == null) {
		    users = new HashSet<String>();
		    groups.put(groupName, users);
		}
		users.add(login);
	    }
	}
    }
}
return skipped;
   }
 

import org.apache.poi.hssf.usermodel.HSSFRow; //导入方法依赖的package包/类
public static void copySheets2CSV(HSSFSheet sheet, String csvfile) {
    int maxColumnNum = 0;

    try {
        FileWriter fw = new FileWriter(csvfile);

        String str = "";
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            HSSFRow srcRow = sheet.getRow(i);

            if (srcRow != null) {
                for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
                   if (j != srcRow.getLastCellNum()) {
                        str = str +srcRow.getCell(j).getStringCellValue()+ ",";
                    } else {
                        str = str +srcRow.getCell(j).getStringCellValue()+ "\r\n";
                    }

                }
                fw.append(str);
            }
            str = "";
        }

        fw.flush();
        fw.close();
    } catch (IOException ex) {

    }
    //Util.copyPictures(newSheet,sheet) ;
}
 

import org.apache.poi.hssf.usermodel.HSSFRow; //导入方法依赖的package包/类
/**
 * @param srcSheet the sheet to copy.
 * @param destSheet the sheet to create.
 * @param srcRow the row to copy.
 * @param destRow the row to create.
 * @param styleMap -
 */
public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) {
    // manage a list of merged zone in order to not insert two times a merged zone  
    Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
    destRow.setHeight(srcRow.getHeight());
    // pour chaque row  
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
        if(j<0){
        }else{
        HSSFCell oldCell = srcRow.getCell(j);   // ancienne cell  
        HSSFCell newCell = destRow.getCell(j);  // new cell   
        if (oldCell != null) {
            if (newCell == null) {
                newCell = destRow.createCell(j);
            }
            // copy chaque cell  
            copyCell(oldCell, newCell, styleMap);
                  // copy les informations de fusion entre les cellules  
            //System.out.println("row num: " + srcRow.getRowNum() + " , col: " + (short)oldCell.getColumnIndex());  
            CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());

            if (mergedRegion != null) {
                //System.out.println("Selected merged region: " + mergedRegion.toString());  
                CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                //System.out.println("New merged region: " + newMergedRegion.toString());  
                CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                if (isNewMergedRegion(wrapper, mergedRegions)) {
                    mergedRegions.add(wrapper);
                    destSheet.addMergedRegion(wrapper.range);
                }
            }
        }
    }
    }

}
 

import org.apache.poi.hssf.usermodel.HSSFRow; //导入方法依赖的package包/类
/**
 * 获取上传文件（Excel）
 * 
 * @param inputStream
 *            输入流，导入的excel数据流
 * @return 返回列表，key为标题列名，value为单元格值
 */
public static List<Map<String, String>> getExcelListMap(InputStream inputStream) {
	try {
		POIFSFileSystem poiFs = new POIFSFileSystem(inputStream);
		HSSFWorkbook wb = new HSSFWorkbook(poiFs);
		HSSFSheet sheet = wb.getSheetAt(0);
		int lastRowNumber = sheet.getLastRowNum();

		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		Map<String, String> map = new HashMap<String, String>();
		int firstColsNum = 0;
		for (int i = 0; i <= lastRowNumber; i++) {
			if (sheet != null) {
				HSSFRow row = sheet.getRow(i);
				Map<String, String> tempMap = new HashMap<String, String>();
				if (row != null) {
					if (firstColsNum == 0) {
						firstColsNum = row.getLastCellNum();
					}
					int nullCount = 0;
					for (int k = 0; k < firstColsNum; k++) {
						if (i == 0) {
							map.put(String.valueOf(k), checkNull(getCellValue(row, k)).toUpperCase());
						} else {
							String cellValue = getCellValue(row, k);
							if (cellValue == null || "".equals(cellValue) || "null".equals(cellValue)) {
								nullCount++;
							}
							tempMap.put(map.get(String.valueOf(k)), checkNull(getCellValue(row, k)));
						}
					}
					if (tempMap.size() > 0 && nullCount != firstColsNum) {
						list.add(tempMap);
					}
				}
			}
		}
		return list;
	} catch (Exception e) {
		e.printStackTrace();
	}

	return null;
}
 

import org.apache.poi.hssf.usermodel.HSSFRow; //导入方法依赖的package包/类
private void xlsToHtml() throws Throwable {
	FileOutputStream output = new FileOutputStream(new File(htmlPath));
	StringBuffer htmlHeaderSB = new StringBuffer();
	htmlHeaderSB.append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' "
			+ "xmlns='http://www.w3.org/TR/REC-html40'>");
	htmlHeaderSB.append("<head><meta http-equiv=Content-Type content='text/html; charset=utf-8'><meta name=ProgId content=Excel.Sheet>"
			+ "</head><body>");
	output.write(htmlHeaderSB.toString().getBytes());
	HSSFSheet sheet;
	HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filePath)); // 获整个Excel
	for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
		if (workbook.getSheetAt(sheetIndex) != null) {
			sheet = workbook.getSheetAt(sheetIndex);// 获得不为空的这个sheet
			if (sheet != null) {
				int firstRowNum = sheet.getFirstRowNum(); // 第一行
				int lastRowNum = sheet.getLastRowNum(); // 最后一行
				// 构造Table
				output.write(("<table width=\"100%\" style=\"border:1px solid #000;border-width:1px 0 0 1px;margin:2px 0 2px 0;"
						+ "border-collapse:collapse;\">").getBytes());
				for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
					if (sheet.getRow(rowNum) != null) {// 如果行不为空，
						HSSFRow row = sheet.getRow(rowNum);
						short firstCellNum = row.getFirstCellNum(); // 该行的第一个单元格
						short lastCellNum = row.getLastCellNum(); // 该行的最后一个单元格
						int height = (int) (row.getHeight() / 15.625); // 行的高度
						output.write(("<tr height=\"" + height + "\" style=\"border:1px solid #000;border-width:0 1px 1px 0;"
								+ "margin:2px 0 2px 0;\">").getBytes());
						for (short cellNum = firstCellNum; cellNum <= lastCellNum; cellNum++) { // 循环该行的每一个单元格
							HSSFCell cell = row.getCell(cellNum);
							if (cell != null) {
								if (cell.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
									StringBuffer tdStyle = new StringBuffer("<td style=\"border:1px solid #000; border-width:0 1px 1px 0;"
											+ "margin:2px 0 2px 0; ");
									HSSFCellStyle cellStyle = cell.getCellStyle();
									HSSFPalette palette = workbook.getCustomPalette(); // 类HSSFPalette用于求颜色的国际标准形式
									HSSFColor hColor = palette.getColor(cellStyle.getFillForegroundColor());
									HSSFColor hColor2 = palette.getColor(cellStyle.getFont(workbook).getColor());
									String bgColor = convertToStardColor(hColor);// 背景颜色
									short boldWeight = cellStyle.getFont(workbook).getBoldweight(); // 字体粗细
									short fontHeight = (short) (cellStyle.getFont(workbook).getFontHeight() / 2); // 字体大小
									String fontColor = convertToStardColor(hColor2); // 字体颜色
									if (bgColor != null && !"".equals(bgColor.trim())) {
										tdStyle.append(" background-color:");
										tdStyle.append(bgColor);
										tdStyle.append("; ");
									}
									if (fontColor != null && !"".equals(fontColor.trim())) {
										tdStyle.append(" color:");
										tdStyle.append(fontColor);
										tdStyle.append("; ");
									}
									tdStyle.append(" font-weight:");
									tdStyle.append(boldWeight);
									tdStyle.append("; ");
									tdStyle.append(" font-size: ");
									tdStyle.append(fontHeight);
									tdStyle.append("%;");
									output.write((tdStyle + "\"").getBytes());

									int width = (int) (sheet.getColumnWidth(cellNum) / 35.7); //
									int cellRegionCol = getMergerCellRegionCol(sheet, rowNum, cellNum); // 合并的列（solspan）
									int cellRegionRow = getMergerCellRegionRow(sheet, rowNum, cellNum);// 合并的行（rowspan）
									String align = convertAlignToHtml(cellStyle.getAlignment()); //
									String vAlign = convertVerticalAlignToHtml(cellStyle.getVerticalAlignment());

									output.write((" align=\"" + align + "\" valign=\"" + vAlign + "\" width=\"" + width + "\" ").getBytes());
									output.write((" colspan=\"" + cellRegionCol + "\" rowspan=\"" + cellRegionRow + "\"").getBytes());
									output.write((">" + getCellValue(cell) + "</td>").getBytes());
								}
							}
						}
						output.write("</tr>".getBytes());
					}
				}
				output.write(("</table>").getBytes());
			}
		}
	}
	output.write(("</body></html>").getBytes());
	output.close();
}
 

import org.apache.poi.hssf.usermodel.HSSFRow; //导入方法依赖的package包/类
public static int getCellSize(HSSFRow sfRow){
	return sfRow.getLastCellNum();
}