Java XSSFSheet类代码示例

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入依赖的package包/类
public static void writeXLSXFile() throws IOException {

			// @SuppressWarnings("resource")
			XSSFWorkbook wbObj = new XSSFWorkbook();
			XSSFSheet sheet = wbObj.createSheet(sheetName);
			for (int row = 0; row < tableData.size(); row++) {
				XSSFRow rowObj = sheet.createRow(row);
				rowData = tableData.get(row);
				for (int col = 0; col < rowData.size(); col++) {
					XSSFCell cell = rowObj.createCell(col);
					cell.setCellValue(rowData.get(col));
					logger.info("Writing " + row + " " + col + "  " + rowData.get(col));
				}
			}
			FileOutputStream fileOut = new FileOutputStream(excelFileName);
			wbObj.write(fileOut);
			wbObj.close();
			fileOut.flush();
			fileOut.close();
		}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入依赖的package包/类
public static ArrayList readXlsx(String path) throws IOException {
    XSSFWorkbook xwb = new XSSFWorkbook(path);
    XSSFSheet sheet = xwb.getSheetAt(0);
    XSSFRow row;
    String[] cell = new String[sheet.getPhysicalNumberOfRows() + 1];
    ArrayList<String> arrayList = new ArrayList<>();
    for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
        cell[i] = "";
        row = sheet.getRow(i);
        for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
            cell[i] += row.getCell(j).toString();
            cell[i] += " | ";
        }
        arrayList.add(cell[i]);
    }
    return arrayList;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入依赖的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.xssf.usermodel.XSSFSheet; //导入依赖的package包/类
@SuppressWarnings("resource")
private List<Map<String,Object>> parseExcel(InputStream stream) throws Exception {
	List<Map<String,Object>> mapList=new ArrayList<Map<String,Object>>();
	XSSFWorkbook wb = new XSSFWorkbook(stream);
	for (int i = 0; i < wb.getNumberOfSheets(); i++) {
		XSSFSheet sheet = wb.getSheetAt(i);
		if (sheet == null) {
			continue;
		}
		String name = sheet.getSheetName();
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("name",name);
		map.put("data", buildVariables(sheet));
		mapList.add(map);
	}
	return mapList;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入依赖的package包/类
/**
 * Método que se encarga de leer el libro de excel
 * cuya ruta recibimos en el constructor y devuelve una lista 
 * de ciudadanos
 * @return lista de Usuarios de la base de datos
 */
public List<CitizenDB> readExcelFile(){
	List<CitizenDB> citizens = new ArrayList<CitizenDB>(); 
	// para cada una de las hojas presentes en el documento de excel
	for(int i=0;i < workbook.getNumberOfSheets();i++){
		XSSFSheet sheet = this.workbook.getSheetAt(i);
		Iterator<Row> rowIterator = sheet.iterator();
		Row row;
		int counter = 0;
		//para cada fila de la hoja
		while(rowIterator.hasNext()){
			row = rowIterator.next();
			if (counter > 0) { //omitimos la cabecera (hay que mirar si hay un metodo de la API)
				Iterator<Cell> cellIterator = row.cellIterator();
				int j = 0;
				CitizenDB user = new CitizenDB();
				while (cellIterator.hasNext()) 	
					this.insertCitizenField(user, j++, cellIterator.next());	
				user.setPassword(new GenerationPassword().passwordGenerator());
				citizens.add(user);
			}
			counter++;
		}
	}
	return citizens;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入依赖的package包/类
/**
 * Exports a single sheet to a file
 *
 * @param sheet
 * @throws FactoryConfigurationError
 * @throws XMLStreamException
 * @throws UnsupportedEncodingException
 * @throws FileNotFoundException
 */
private void export(final XSSFSheet sheet, final XMLStreamWriter out)
		throws UnsupportedEncodingException, XMLStreamException, FactoryConfigurationError, FileNotFoundException {
	boolean isFirst = true;
	final Map<String, String> columns = new HashMap<String, String>();
	final String sheetName = sheet.getSheetName();
	System.out.print(sheetName);
	out.writeStartElement("sheet");
	out.writeAttribute("name", sheetName);
	Iterator<Row> rowIterator = sheet.rowIterator();
	while (rowIterator.hasNext()) {
		Row row = rowIterator.next();
		if (isFirst) {
			isFirst = false;
			this.writeFirstRow(row, out, columns);
		} else {
			this.writeRow(row, out, columns);
		}
	}
	out.writeEndElement();
	System.out.println("..");
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入依赖的package包/类
/**
 * Parses an inputstream containin xlsx into an outputStream containing XML
 * 
 * @param inputStream
 *            the source
 * @param outputStream
 *            the result
 * @throws IOException
 * @throws XMLStreamException
 */
public void parse(final InputStream inputStream, final OutputStream outputStream)
		throws IOException, XMLStreamException {
	XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
	XMLStreamWriter out = this.getXMLWriter(outputStream);
	out.writeStartDocument();
	out.writeStartElement("workbook");
	int sheetCount = workbook.getNumberOfSheets();
	for (int i = 0; i < sheetCount; i++) {
		final XSSFSheet sheet = workbook.getSheetAt(i);
		try {
			this.export(sheet, out);
		} catch (UnsupportedEncodingException | FileNotFoundException | XMLStreamException
				| FactoryConfigurationError e) {
			e.printStackTrace();
		}
	}
	out.writeEndElement();
	out.writeEndDocument();
	out.close();
	workbook.close();
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入依赖的package包/类
private Span getSpan(XSSFSheet 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);
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入依赖的package包/类
private boolean isMergedRegion(XSSFSheet 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();
		int lastRow = range.getLastRow();
		if (row > firstRow && row < lastRow) {
			if (column > firstColumn && column < lastColumn) {
				return true;
			}
		}
	}
	return false;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入依赖的package包/类
/**
 * 获取Excel2007图片
 * 
 * @param sheet
 *            当前sheet对象
 * @param workbook
 *            工作簿对象
 * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
 */
public static Map<String, PictureData> getSheetPictrues07(XSSFSheet sheet,
                                                          XSSFWorkbook workbook) {
    Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
    for (POIXMLDocumentPart dr : sheet.getRelations()) {
        if (dr instanceof XSSFDrawing) {
            XSSFDrawing drawing = (XSSFDrawing) dr;
            List<XSSFShape> shapes = drawing.getShapes();
            for (XSSFShape shape : shapes) {
                XSSFPicture pic = (XSSFPicture) shape;
                XSSFClientAnchor anchor = pic.getPreferredSize();
                CTMarker ctMarker = anchor.getFrom();
                String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
                sheetIndexPicMap.put(picIndex, pic.getPictureData());
            }
        }
    }
    return sheetIndexPicMap;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入依赖的package包/类
public static void main(String[] args) {
        //Create Workbook instance holding reference to .xlsx file
        try (FileInputStream file = new FileInputStream(
                new File("Sample.xlsx"))) {
            //Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook(file);

            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);

            //Iterate through each rows one by one
//            Iterator<Row> rowIterator = sheet.iterator();
            for(Row row : sheet) {
                for (Cell cell : row) {
                    //Check the cell type and format accordingly
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            out.print(cell.getNumericCellValue() + "\t");
                            break;
                        case Cell.CELL_TYPE_STRING:
                            out.print(cell.getStringCellValue() + "\t");
                            break;
                    }
                }
                out.println();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入依赖的package包/类
List<ClassifiableText> xlsxToClassifiableTexts(File xlsxFile, int sheetNumber) throws IOException, EmptySheetException {
  if (xlsxFile == null ||
      sheetNumber < 1) {
    throw new IllegalArgumentException();
  }

  try (XSSFWorkbook excelFile = new XSSFWorkbook(new FileInputStream(xlsxFile))) {
    XSSFSheet sheet = excelFile.getSheetAt(sheetNumber - 1);

    // at least two rows
    if (sheet.getLastRowNum() > 0) {
      return getClassifiableTexts(sheet);
    } else {
      throw new EmptySheetException("Excel sheet (#" + sheetNumber + ") is empty");
    }
  } catch (IllegalArgumentException e) {
    throw new EmptySheetException("Excel sheet (#" + sheetNumber + ") is not found");
  }
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入依赖的package包/类
private List<ClassifiableText> getClassifiableTexts(XSSFSheet sheet) {
  List<Characteristic> characteristics = getCharacteristics(sheet);
  List<ClassifiableText> classifiableTexts = new ArrayList<>();

  // start from second row
  for (int i = 1; i <= sheet.getLastRowNum(); i++) {
    Map<Characteristic, CharacteristicValue> characteristicsValues = getCharacteristicsValues(sheet.getRow(i), characteristics);

    // exclude empty rows
    if (!sheet.getRow(i).getCell(0).getStringCellValue().equals("")) {
      classifiableTexts.add(new ClassifiableText(sheet.getRow(i).getCell(0).getStringCellValue(), characteristicsValues));
    }
  }

  return classifiableTexts;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入依赖的package包/类
public static XSSFRow createWageringHeaderRow(XSSFSheet sheet, int row, int col) {
    XSSFRow headerRow = sheet.createRow(row++);

    col = seedCols(col, headerRow);

    List<String> columns = Arrays.asList("winPayoff", "placePayoff",
            "showPayoff", "totalWpsPool", "doublePayoff", "doublePool", "exactaPayoff",
            "exactaPool", "trifectaPayoff", "trifectaPool", "superfectaPayoff",
            "superfectaPool", "pick3Payoff", "pick3Pool", "pick4Payoff", "pick4Pool",
            "pick5Payoff", "pick5Pool");
    for (String column : columns) {
        CellUtil.createCell(headerRow, col++, column);
    }

    return headerRow;
}
 

import org.apache.poi.xssf.usermodel.XSSFSheet; //导入依赖的package包/类
@SuppressWarnings("deprecation")
>>>>>>> fe2012a7f8558d8df36b789847bdc41c788d6eaf
	public static void createMergedRegionIfNotPresent(XSSFSheet sheet,int firstRow, int lastRow, int firstCol, int lastCol){
		String message="XSSFSheet must not be null!";
		Objects.requireNonNull(sheet, () -> message);
		sheet.addMergedRegion(new CellRangeAddress(firstRow,  lastRow,  firstCol,  lastCol)); 
	}