下面列出了org.apache.poi.ss.usermodel.Sheet#getRow ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* 创建List之后的各个Cells
*
* @param styles
*/
private static void createListCells(Drawing patriarch, int index,
int cellNum, Object obj, List<ExcelExportEntity> excelParams,
Sheet sheet, Workbook workbook) throws Exception {
ExcelExportEntity entity;
Row row;
if (sheet.getRow(index) == null) {
row = sheet.createRow(index);
row.setHeight((short) 350);
} else {
row = sheet.getRow(index);
}
for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) {
entity = excelParams.get(k);
Object value = getCellValue(entity, obj);
if (entity.getType() != 2) {
createStringCell(row, cellNum++,
value == null ? "" : value.toString(), entity, workbook);
} else {
createImageCell(patriarch, entity, row, cellNum++,
value == null ? "" : value.toString(), obj, workbook);
}
}
}
/**
* @param reference
* @return the cell the reference points to
*/
private Cell getCellFromReference( String reference ) {
CellReference cellRef = new CellReference( reference );
String sheetName = cellRef.getSheetName();
Sheet sheet = data.sheet;
if ( !Utils.isEmpty( sheetName ) ) {
sheet = data.wb.getSheet( sheetName );
}
if ( sheet == null ) {
return null;
}
// reference is assumed to be absolute
Row xlsRow = sheet.getRow( cellRef.getRow() );
if ( xlsRow == null ) {
return null;
}
Cell styleCell = xlsRow.getCell( cellRef.getCol() );
return styleCell;
}
/**
* 按报表模板格式写标题
*
* @author ZhengWei(HY)
* @createDate 2017-03-17
* @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 writeTitle(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 = i_RTemplate.getRowCountTitle();
int v_ExcelRowIndex = io_RTotal.getExcelRowIndex();
copyMergedRegionsTitle(i_RTemplate ,i_DataSheet ,io_RTotal); // 按模板合并单元格
copyImagesTitle( i_RTemplate ,i_DataSheet ,io_RTotal); // 按模板复制图片
for (int v_RowNo=0; v_RowNo<v_TemplateRowCount; v_RowNo++)
{
int v_TemplateRowNo = i_RTemplate.getTitleBeginRow() + 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);
if ( v_TemplateRow != null ) // 模板空白行（无任何数据）时，可能返回NULL
{
copyRow(i_RTemplate ,v_TemplateRow ,i_DataWorkbook ,io_RTotal ,io_RSystemValue ,v_DataRow ,i_Datas);
}
}
}
/**
* 获取合并单元格的值
* @param sheet
* @param row
* @param column
* @return
*/
public static String getMergedRegionValue(Sheet sheet ,int row , int column){
int sheetMergeCount = sheet.getNumMergedRegions();
for(int i = 0 ; i < sheetMergeCount ; i++){
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell) ;
}
}
}
return null ;
}
/**
* @param reference
* @return the cell the reference points to
*/
private Cell getCellFromReference( String reference ) {
CellReference cellRef = new CellReference( reference );
String sheetName = cellRef.getSheetName();
Sheet sheet = meta.isStreamingData() ? data.xssfWorkbook.getSheet( data.realSheetname ) : data.sheet;
if ( !Utils.isEmpty( sheetName ) ) {
sheet = meta.isStreamingData() ? data.xssfWorkbook.getSheet( sheetName ) : data.wb.getSheet( sheetName );
}
if ( sheet == null ) {
return null;
}
// reference is assumed to be absolute
Row xlsRow = sheet.getRow( cellRef.getRow() );
if ( xlsRow == null ) {
return null;
}
Cell styleCell = xlsRow.getCell( cellRef.getCol() );
return styleCell;
}
@Test
public void poi0702() throws Exception {
Workbook workbook = WorkbookFactory.create(new FileInputStream("D:\\test\\t2.xlsx"));
workbook = WorkbookFactory.create(new File("D:\\test\\t2.xlsx"));
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
System.out.println(row.getCell(0).getNumericCellValue());
}
public T onRead(Sheet sheet, int sheetIndex) {
T dataModelInst = createDataModel();
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
for(RowStreamReader<T> reader : rowReaders) {
Row row = sheet.getRow(rowIndex);
if (reader.match(rowIndex)) {
reader.onRead(dataModelInst, sheet, sheetIndex, row, rowIndex);
}
}
}
return dataModelInst;
}
/**
* 获取表头元数据
*
* @param filePath
* @return 属性名称列表、字段类型、描述说明
*/
public static Args.Three<List<String>, List<String>, List<String>> getMetaData(String filePath, String sheetName) throws Exception {
Workbook workBook = getWorkBook(filePath);
if (workBook == null) {
return null;
}
Sheet sheet = workBook.getSheet(sheetName);
if (sheet == null) {
return null;
}
List<String> fieldList = new ArrayList<>();
List<String> typeList = new ArrayList<>();
List<String> descList = new ArrayList<>();
//前三行为元数据
for (int i = 0; i < 3; i++) {
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
int lastCellNum = row.getPhysicalNumberOfCells();
for (int j = 0; j < lastCellNum; j++) {
String value = row.getCell(j).toString();
switch (i) {
case 0:
fieldList.add(value);
break;
case 1:
typeList.add(value);
break;
default:
descList.add(value);
break;
}
}
}
workBook.close();
return Args.of(fieldList, typeList, descList);
}
@Test
public void testMultiRowEmptinessXlsx() throws BirtException, IOException {
debug = false;
InputStream inputStream = runAndRenderReport("Issue29.rptdesign", "xlsx");
assertNotNull(inputStream);
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
assertEquals( 1, workbook.getNumberOfSheets() );
Sheet sheet = workbook.getSheetAt(0);
assertEquals( 6, this.firstNullRow(sheet));
for( int i = 0; i < 4; ++i ) {
for( Cell cell : sheet.getRow(i) ) {
assertEquals( 0, cell.getCellStyle().getBorderTop() );
assertEquals( 0, cell.getCellStyle().getBorderLeft() );
assertEquals( 0, cell.getCellStyle().getBorderRight() );
assertEquals( 0, cell.getCellStyle().getBorderBottom() );
}
}
assertEquals( "Bibble", sheet.getRow(5).getCell(0).getStringCellValue() );
assertEquals( 24.0, sheet.getRow(0).getHeightInPoints(), 0.1 );
} finally {
inputStream.close();
}
}
@Override
public List<String> mapTitleRow(Sheet sheet){
try {
Row titleRow = sheet.getRow(0);
return ExcelUtils.getRowValues(titleRow);
} catch (Exception e) {
throw ExcelUtils.wrapAsUnCheckedException("mapTitleRow error" , e);
}
}
@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
SubjectType subjectType = SubjectTypeUtils.getOrCreate(AbstractONSImporter.PROVIDER, OaImporter.OaType.lsoa.name(), OaImporter.OaType.lsoa.datasourceSpec.getDescription());
// Loop over years
for (int sheetId = 0; sheetId < getWorkbook().getNumberOfSheets(); sheetId++){
Sheet sheet = getWorkbook().getSheetAt(sheetId);
int year;
try {
year = Integer.parseInt(sheet.getSheetName().substring(sheet.getSheetName().length()-4, sheet.getSheetName().length()));
}catch (NumberFormatException e){
// Sheetname does not end in a year
continue;
}
// Create extractors for each timed value
List<TimedValueExtractor> timedValueExtractors = new ArrayList<>();
RowCellExtractor subjectExtractor = new RowCellExtractor(0, CellType.STRING);
ConstantExtractor timestampExtractor = new ConstantExtractor(String.valueOf(year));
// Get the attribute label row and create TimedValueExtractors
Row attributeLabelRow = sheet.getRow(5);
for (int columnId = 0; columnId < attributeLabelRow.getLastCellNum(); columnId++){
RowCellExtractor tmpAttributeLabelExtractor = new RowCellExtractor(columnId, CellType.STRING);
tmpAttributeLabelExtractor.setRow(attributeLabelRow);
Attribute attribute = AttributeUtils.getByProviderAndLabel(getProvider(), tmpAttributeLabelExtractor.extract());
if (attribute != null){
ConstantExtractor attributeExtractor = new ConstantExtractor(attribute.getLabel());
RowCellExtractor valueExtractor = new RowCellExtractor(columnId, CellType.NUMERIC);
timedValueExtractors.add(new TimedValueExtractor(getProvider(), subjectType, subjectExtractor, attributeExtractor, timestampExtractor, valueExtractor));
}
}
// Extract timed values
excelUtils.extractAndSaveTimedValues(sheet, this, timedValueExtractors);
}
getWorkbook().close();
}
@Test
public void givenFormualCell_whenGetCellStringValueForFormula_thenReturnOriginalFormulatring() throws IOException {
Workbook workbook = new XSSFWorkbook(fileLocation);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
ExcelCellFormatter formatter = new ExcelCellFormatter();
assertEquals("3", formatter.getCellStringValueWithFormula(row.getCell(FORMULA_CELL_INDEX), workbook));
workbook.close();
}
/**
* Given a sheet, this method deletes a column from a sheet and moves
* all the columns to the right of it to the left one cell.
*
* Note, this method will not update any formula references.
*
* @param sheet
* @param column
*/
public static void deleteColumn( Sheet sheet, int columnToDelete ){
int maxColumn = 0;
for ( int r=0; r < sheet.getLastRowNum()+1; r++ ){
Row row = sheet.getRow( r );
// if no row exists here; then nothing to do; next!
if ( row == null )
continue;
int lastColumn = row.getLastCellNum();
if ( lastColumn > maxColumn )
maxColumn = lastColumn;
// if the row doesn't have this many columns then we are good; next!
if ( lastColumn < columnToDelete )
continue;
for ( int x=columnToDelete+1; x < lastColumn + 1; x++ ){
Cell oldCell = row.getCell(x-1);
if ( oldCell != null )
row.removeCell( oldCell );
Cell nextCell = row.getCell( x );
if ( nextCell != null ){
Cell newCell = row.createCell( x-1, nextCell.getCellType() );
cloneCell(newCell, nextCell);
}
}
}
// Adjust the column widths
for ( int c=0; c < maxColumn; c++ ){
sheet.setColumnWidth( c, sheet.getColumnWidth(c+1) );
}
}
@Override
public List<String> mapTitleRow(Sheet sheet) {
try {
Row titleRow = sheet.getRow(getTitleRowIndex());
return ExcelUtils.getRowValues(titleRow);
} catch (Exception e) {
throw ExcelUtils.wrapAsUnCheckedException("mapTitleRow error" , e);
}
}
@Test
public void saveMapsTest_UsingGeneratedHeader_File() throws InvalidFormatException, IOException, ParseException {
Map<String, Object> record = new LinkedHashMap<>();
record.put("primIntProp", 1);
record.put("intObjProp", 100);
record.put("strProp", "some string");
record.put("dateProp", "2000-01-01 00:00:00");
Collection<Map<String, Object>> records = Arrays.asList(record);
File outputFile = createFile("saveMapsTest_UsingGeneratedHeader_File");
// save it
Ssio.saveMaps(Arrays.asList("primIntProp", "intObjProp", "strProp", "dateProp"), records, outputFile);
// then parse it
byte[] spreadsheet = FileUtils.readFileToByteArray(outputFile);
Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));
/*** do assertions ***/
Sheet sheet = workbook.getSheetAt(0);
Row headerRow = sheet.getRow(0);
Row dataRow = sheet.getRow(1);
List<String> headerCells = getAllCells(headerRow).stream().map(c -> c.getStringCellValue()).collect(Collectors.toList());
List<Object> dataCells = getAllCells(dataRow).stream().map(c -> getStringOrDateValue(c)).collect(Collectors.toList());
Map<String, Object> keyValueMap = new LinkedHashMap<>();
for (int i = 0; i < headerCells.size(); i++) {
keyValueMap.put(headerCells.get(i), dataCells.get(i));
}
Assert.assertEquals(4, keyValueMap.size());
Assert.assertEquals("1", keyValueMap.get("Prim Int Prop"));
Assert.assertEquals("100", keyValueMap.get("Int Obj Prop"));
Assert.assertEquals("some string", keyValueMap.get("Str Prop"));
Assert.assertEquals("2000-01-01 00:00:00", keyValueMap.get("Date Prop"));
}
public Object getCellValueByFetchingLastCachedValue(String fileLocation, String cellLocation) throws IOException {
Object cellValue = new Object();
FileInputStream inputStream = new FileInputStream(new File(fileLocation));
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
CellAddress cellAddress = new CellAddress(cellLocation);
Row row = sheet.getRow(cellAddress.getRow());
Cell cell = row.getCell(cellAddress.getColumn());
if (cell.getCellType() == CellType.FORMULA) {
switch (cell.getCachedFormulaResultType()) {
case BOOLEAN:
cellValue = cell.getBooleanCellValue();
break;
case NUMERIC:
cellValue = cell.getNumericCellValue();
break;
case STRING:
cellValue = cell.getStringCellValue();
break;
default:
cellValue = null;
}
}
workbook.close();
return cellValue;
}
protected Cell getCell(Sheet sheet, int row, int col) {
Row sheetRow = sheet.getRow(row);
if (sheetRow == null) {
sheetRow = sheet.createRow(row);
}
Cell cell = sheetRow.getCell(col);
if (cell == null) {
cell = sheetRow.createCell(col);
}
return cell;
}
@Test
public void test355() throws IOException, InvalidFormatException {
File file = TestFileUtil.readFile("dataformat" + File.separator + "dataformat.xlsx");
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
DataFormatter d = new DataFormatter(Locale.CHINA);
for (int i = 0; i < xssfSheet.getLastRowNum(); i++) {
Row row = xssfSheet.getRow(i);
System.out.println(d.formatCellValue(row.getCell(0)));
}
}
@Test
public void saveTest_UsingGeneratedHeader_File() throws InvalidFormatException, IOException, ParseException {
HeaderUtilsTestRecord record = new HeaderUtilsTestRecord();
record.setPrimIntProp(1);
record.setIntObjProp(100);
record.setStrProp("some string");
record.setDateProp("2000-01-01 00:00:00");
record.setWriteOnlyProp("would not be saved");
Collection<HeaderUtilsTestRecord> records = Arrays.asList(record);
File outputFile = createFile("saveTest_UsingGeneratedHeader_File");
// save it
Ssio.save(HeaderUtilsTestRecord.class, records, outputFile);
// then parse it
byte[] spreadsheet = FileUtils.readFileToByteArray(outputFile);
Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));
/*** do assertions ***/
Sheet sheet = workbook.getSheetAt(0);
Row headerRow = sheet.getRow(0);
Row dataRow = sheet.getRow(1);
List<String> headerCells = getAllCells(headerRow).stream().map(c -> c.getStringCellValue()).collect(Collectors.toList());
List<Object> dataCells = getAllCells(dataRow).stream().map(c -> getStringOrDateValue(c)).collect(Collectors.toList());
Map<String, Object> keyValueMap = new LinkedHashMap<>();
for (int i = 0; i < headerCells.size(); i++) {
keyValueMap.put(headerCells.get(i), dataCells.get(i));
}
Assert.assertEquals(6, keyValueMap.size());
Assert.assertEquals("1", keyValueMap.get("Prim Int Prop"));
Assert.assertEquals("100", keyValueMap.get("Int Obj Prop"));
Assert.assertEquals("some string", keyValueMap.get("Str Prop"));
Assert.assertTrue(keyValueMap.containsKey("Date Prop"));
Assert.assertEquals("2000-01-01 00:00:00", keyValueMap.get("Date Prop Str"));
Assert.assertNull(keyValueMap.get("Read Only Prop"));
}
private void processSpreadsheetUpload(Workbook wb, List<Map<QName,String>> users)
throws IOException
{
if (wb.getNumberOfSheets() > 1)
{
logger.info("Uploaded Excel file has " + wb.getNumberOfSheets() +
" sheets, ignoring all except the first one");
}
int firstRow = 0;
Sheet s = wb.getSheetAt(0);
DataFormatter df = new DataFormatter();
String[][] data = new String[s.getLastRowNum()+1][];
// If there is a heading freezepane row, skip it
PaneInformation pane = s.getPaneInformation();
if (pane != null && pane.isFreezePane() && pane.getHorizontalSplitTopRow() > 0)
{
firstRow = pane.getHorizontalSplitTopRow();
logger.debug("Skipping excel freeze header of " + firstRow + " rows");
}
// Process each row in turn, getting columns up to our limit
for (int row=firstRow; row <= s.getLastRowNum(); row++)
{
Row r = s.getRow(row);
if (r != null)
{
String[] d = new String[COLUMNS.length];
for (int cn=0; cn<COLUMNS.length; cn++)
{
Cell cell = r.getCell(cn);
if (cell != null && cell.getCellType() != CellType.BLANK)
{
d[cn] = df.formatCellValue(cell);
}
}
data[row] = d;
}
}
// Handle the contents
processSpreadsheetUpload(data, users);
}