Java Row類代碼示例

import org.apache.poi.ss.usermodel.Row; //導入依賴的package包/類
@RequestMapping(value = "/test2.xlsx", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
@ResponseBody
byte[] testDown() throws IOException, InvalidFormatException {
    Workbook workbook = new SXSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    for (int i = 0; i < 60000; i++) {
        Row newRow = sheet.createRow(i);
        for (int j = 0; j < 100; j++) {
            newRow.createCell(j).setCellValue("test" + Math.random());
        }
    }
    ByteArrayOutputStream os = new ByteArrayOutputStream();
    workbook.write(os);
    byte[] bytes = os.toByteArray();
    return bytes;
}
 

import org.apache.poi.ss.usermodel.Row; //導入依賴的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.ss.usermodel.Row; //導入依賴的package包/類
/** 創建不需要合並單元格的表頭
 * @param fieldInfoMap
 * @param headCellStyleMap
 * @param row
 * @param fieldList
 * @param cellNum
 */
private static void doCreateSheetSingleHeadRow(Map<Field, ExportFieldInfo> fieldInfoMap,
		Map<Field, CellStyle> headCellStyleMap, Row row, List<SortableField> fieldList, int cellNum) {
	
	Cell cell;
	String value;
	for(int i = 0 ; i < fieldList.size() ; i++ , cellNum++){
		Field field = fieldList.get(i).getField();
		value = fieldInfoMap.get(field).getHeadName();
		cell = row.createCell(cellNum);
		cell.setCellValue(value);
		if( headCellStyleMap != null && headCellStyleMap.get(field) != null ){
			cell.setCellStyle(headCellStyleMap.get(field));
		}
	}
}
 

import org.apache.poi.ss.usermodel.Row; //導入依賴的package包/類
public void print () {
	Sheet sheet =  getOrCreateSummary();
	Iterator<Row> rows = sheet.rowIterator();
	int index=0;
	while (rows.hasNext()) {
		Row row = (Row) rows.next();
		System.out.println("Row ---> " + index);
		Spliterator<Cell> cells = row.spliterator();
		cells.forEachRemaining(new Consumer<Cell> () {
			@Override
			public void accept(Cell cell) {
				System.out.print(cell.toString());
				System.out.print(";");
			}
		});
		System.out.println();
		index++;
	}
}
 

import org.apache.poi.ss.usermodel.Row; //導入依賴的package包/類
private List<String> getClasses(Row classRow) {
	//聲明一個list，用來存放所有的字段類型
	List<String> classList = new ArrayList<String>();
	//判斷這一行是否為空
	if (classRow != null) {
		//遍曆這一行的所有單元格
		for (int i = 0; i < classRow.getLastCellNum(); i++) {
			//獲取單元格
			Cell cell = classRow.getCell(i);
			//判斷單元格是否為空
			if (cell != null) {
				//將單元格的內容存放到list中
				classList.add(cell.getStringCellValue());
			}
		}
	}
	//返回所有的字段類型
	return classList;
}
 

import org.apache.poi.ss.usermodel.Row; //導入依賴的package包/類
private void setSheetData(SheetData data, String group) {
	data.setCurrentGroup(group);
	// start from 1
	data.setCurrentIndex(1);
	// get sheet
	Sheet vSheet = getWorkBook().getSheet(group);
	Assert.notNull(vSheet, "Can't get sheet with name: " + group);
	data.setSheet(vSheet);
	// get row number
	int vRowCount = vSheet.getLastRowNum() + 1;
	data.setRowCount(vRowCount);
	// get first row
	Row vRow = vSheet.getRow(0);
	Assert.notNull(vRow, "Invalid format: first row must be title");
	// get column number
	int vColumnCount = vRow.getLastCellNum();
	String[] vTitles = new String[vColumnCount];
	// read titles
	for (int i = 0; i < vColumnCount; ++i) {
		Cell vCell = vRow.getCell(i);
		vTitles[i] = vCell.getStringCellValue();
	}
	data.setTitles(vTitles);
}
 

import org.apache.poi.ss.usermodel.Row; //導入依賴的package包/類
public static void createWorkBook() throws IOException {
	// ����excel������
	final Workbook wb = new XSSFWorkbook();
	// ����sheet��ҳ��
	final Sheet sheet1 = wb.createSheet("Sheet_1");
	final Sheet sheet2 = wb.createSheet("Sheet_2");

	for (int i = 0; i < 20; i = i + 2) {
		final Row row1 = sheet1.createRow(i);
		final Row row2 = sheet2.createRow(i);

		for (int j = 0; j < 10; j++) {

			row1.createCell(j).setCellValue(j + "new");
			row2.createCell(j).setCellValue(j + "This is a string");

		}
	}
	// ����һ���ļ� ����Ϊworkbooks.xlsx
	final FileOutputStream fileOut = new FileOutputStream("d:\\workbooks.xlsx");
	// �����洴���Ĺ�����������ļ���
	wb.write(fileOut);
	// �ر������
	fileOut.close();
}
 

import org.apache.poi.ss.usermodel.Row; //導入依賴的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.ss.usermodel.Row; //導入依賴的package包/類
/**
 * 解析行
 * @param row
 * @return
 */
protected List<Object> parseRow(Row row) {
	List<Object> rowData = new ArrayList<Object>();

	for (int i = 0; i < row.getLastCellNum(); i++) {
		Cell cell = row.getCell(i);
		Object cellObj=null;
		if(cell!=null){
			cellObj = parseCell(cell);
		}
		rowData.add(cellObj);
	}
	/*// 迭代 一行的各個單元格
	Iterator<Cell> cellIterator = row.iterator();
	// 遍曆一行多列
	while (cellIterator.hasNext()) {
		Cell cell = cellIterator.next();
		Object cellObj = parseCell(cell);
		rowData.add(cellObj);
	}*/
	return rowData;
}
 

import org.apache.poi.ss.usermodel.Row; //導入依賴的package包/類
public <T> List<T> unmarshal(Class<T> type) {
    Workbook workbook = poijiWorkbook.workbook();
    Sheet sheet = workbook.getSheetAt(options.sheetIndex());

    int skip = options.skip();
    int maxPhysicalNumberOfRows = sheet.getPhysicalNumberOfRows() + 1 - skip;
    List<T> list = new ArrayList<>(maxPhysicalNumberOfRows);

    for (Row currentRow : sheet) {

        if (skip(currentRow, skip))
            continue;

        if (isRowEmpty(currentRow))
            continue;

        if (maxPhysicalNumberOfRows > list.size()) {
            T t = deserialize0(currentRow, type);
            list.add(t);
        }
    }

    return list;
}
 

import org.apache.poi.ss.usermodel.Row; //導入依賴的package包/類
/**
 * 按報表模板格式寫分頁頁眉標題
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-06-25
 * @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 writeTitlePageHeader(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 = io_RTotal.getTitlePageHeaderCount();
    int   v_ExcelRowIndex    = io_RTotal.getExcelRowIndex();

    copyMergedRegionsTitlePageHeader(i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板合並單元格
    copyImagesTitlePageHeader(       i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板複製圖片
    
    io_RSystemValue.setPageNo(io_RSystemValue.getPageNo() + 1);
    
    for (int v_RowNo=0; v_RowNo<v_TemplateRowCount; v_RowNo++)
    {
        int v_TemplateRowNo = i_RTemplate.getTitlePageHeaderBeginRow() + 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);
        
        copyRow(i_RTemplate ,v_TemplateRow ,i_DataWorkbook ,io_RTotal ,io_RSystemValue ,v_DataRow ,i_Datas);
    }
}
 

import org.apache.poi.ss.usermodel.Row; //導入依賴的package包/類
public static void main(String[] args) throws Throwable {
        SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Sheet sh = wb.createSheet();
        for (int rownum = 0; rownum < 1000; rownum++) {
            Row row = sh.createRow(rownum);
            Row row1 = sh.createRow(rownum);
            for (int cellnum = 0; cellnum < 10; cellnum++) {
                Cell cell = row.createCell(cellnum);
                String address = new CellReference(cell).formatAsString();
                cell.setCellValue(address);
            }

        }

        // Rows with rownum < 900 are flushed and not accessible
//        for (int rownum = 0; rownum < 103857; rownum++) {
//            Assert.assertNull(sh.getRow(rownum));
//        }
//
//        // ther last 100 rows are still in memory
//        for (int rownum = 103857; rownum < 104857; rownum++) {
//            Assert.assertNotNull(sh.getRow(rownum));
//        }

        File file = new File("C:\\Users\\FlyingHe\\Desktop", "datas.xlsx");
        FileOutputStream out = new FileOutputStream(file);
        wb.write(out);
        out.close();

        // dispose of temporary files backing this workbook on disk
        wb.dispose();
    }
 

import org.apache.poi.ss.usermodel.Row; //導入依賴的package包/類
private void writeRow(final Row row, final XMLStreamWriter out, final Map<String, String> columns) {
	try {
		int rowIndex = row.getRowNum();
		out.writeStartElement("row");
		final String rowNum = String.valueOf(rowIndex);
		out.writeAttribute("row", rowNum);
		int count = 0;
		Iterator<Cell> cellIterator = row.iterator();
		while (cellIterator.hasNext()) {
			Cell cell = cellIterator.next();
			int columnIndex = cell.getColumnIndex();
			if (this.exportEmptyCells) {
				while (count < columnIndex) {
					this.writeAnyCell(rowIndex, count, null, out, columns);
					count++;
				}
			}
			this.writeCell(cell, out, columns);
			count++;
		}
		out.writeEndElement();
	} catch (XMLStreamException e) {
		e.printStackTrace();
	}

}
 

import org.apache.poi.ss.usermodel.Row; //導入依賴的package包/類
/**
 * 按報表模板格式寫入合計（暫時不支持分頁功能）
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-03-18
 * @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 writeTotal(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_TemplateRowCountTotal = i_RTemplate.getRowCountTotal();
    int   v_ExcelRowIndex         = io_RTotal.getExcelRowIndex();
    
    copyMergedRegionsTotal(i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板合並單元格
    copyImagesTotal(       i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板複製圖片
    
    for (int v_RowNo=0; v_RowNo<v_TemplateRowCountTotal; v_RowNo++) 
    {
        int v_TemplateRowNo = i_RTemplate.getTotalBeginRow() + 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);
        io_RTotal.addRealDataCount(1);
        
        copyRow(i_RTemplate ,v_TemplateRow ,i_DataWorkbook ,io_RTotal ,io_RSystemValue ,v_DataRow ,i_Datas);
    }
}
 

import org.apache.poi.ss.usermodel.Row; //導入依賴的package包/類
public CommentWriter(OutputStream outputStream) throws IOException{
    this.outputStream = outputStream;

    this.workbook = new XSSFWorkbook();
    this.sheet = workbook.createSheet("Comments");

    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue("#");

    cell = row.createCell(1);
    cell.setCellValue("comment");

    cell = row.createCell(2);
    cell.setCellValue("timestamp");

    rowCount = 1;
}