Java XSSFRow.createCell方法代码示例

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的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.XSSFRow; //导入方法依赖的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(XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> 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{
            XSSFCell oldCell = srcRow.getCell(j);   // ancienne cell  
        XSSFCell 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.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
@Test
   public void createXLSXModifyProtected() throws IOException {
System.out.println("createXLSXModifyProtected");

//creates sheet
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(0);
cell.setCellValue("Gravado");
sheet.protectSheet("54321");

//saves sheet
new File("target/.output-file/xlsx").mkdirs();
workbook.write(new FileOutputStream("target/.output-file/xlsx/ModifyProtected.xlsx"));

//read again and check
XSSFWorkbook workbook2 = new XSSFWorkbook(new FileInputStream("target/.output-file/xlsx/ModifyProtected.xlsx"));
Assert.assertEquals("Gravado", workbook2.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
   }
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
private void fillExtraFields(String tablename, Object id, XSSFRow row, LinkedHashSet<String> de, int startCol) throws SQLException {
	// ExtraFields
	if (id != null && de != null) {
		String sql = "Select * from " + MyDBI.delimitL("ExtraFields") + " WHERE " + MyDBI.delimitL("tablename") + "='" + tablename  + "' AND " + MyDBI.delimitL("id") + "=" + id;
		ResultSet rs2 = DBKernel.getResultSet(sql, false);
		if (rs2 != null && rs2.first()) {
			do {
				String s = rs2.getString("attribute");
				int j=0;
				for (String e : de) {
					if (s.equalsIgnoreCase(e)) {
						XSSFCell cell = row.getCell(startCol+j);
						if (cell == null) cell = row.createCell(startCol+j);
						cell.setCellValue(rs2.getString("value"));
						break;
					}
					j++;
				}
			} while (rs2.next());
		}	
	}		
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public void writeSheet(String key, Vector<String[]> sheetVector, XSSFWorkbook workbook){
	XSSFSheet worksheet = workbook.createSheet(key);
	int count=0;//keeps track of rows; one below the row int because of header row
	final Pattern NUMERIC = Pattern.compile("^\\d+.?\\d*$");
	//for each row, create the row in excel
	for (int row=0; row<sheetVector.size();row++){
		XSSFRow row1 = worksheet.createRow( count);
		count++;
		//for each col, write it to that row.
		for (int col=0; col<sheetVector.get(0).length;col++){
			XSSFCell cell = row1.createCell(col);
			String val = sheetVector.get(row)[col];
			if(val != null && !val.isEmpty() && NUMERIC.matcher(val).find()) {
				cell.setCellType(Cell.CELL_TYPE_NUMERIC);
				cell.setCellValue(Double.parseDouble(val));
				continue;
			}
			cell.setCellValue(sheetVector.get(row)[col]);
		}
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public void writeSheet(String key, Vector<String[]> sheetVector, XSSFWorkbook workbook) {
	XSSFSheet worksheet = workbook.createSheet(key);
	int count=0;//keeps track of rows; one below the row int because of header row
	final Pattern NUMERIC = Pattern.compile("^\\d+.?\\d*$");
	//for each row, create the row in excel
	for (int row=0; row<sheetVector.size();row++){
		XSSFRow row1 = worksheet.createRow( count);
		count++;
		//for each col, write it to that row.7
		for (int col=0; col<sheetVector.get(row).length;col++) {
			XSSFCell cell = row1.createCell(col);
			if(sheetVector.get(row)[col] != null) {
				String val = sheetVector.get(row)[col];
				//Check if entire value is numeric - if so, set cell type and parseDouble, else write normally
				if(val != null && !val.isEmpty() && NUMERIC.matcher(val).find()) {
					cell.setCellType(Cell.CELL_TYPE_NUMERIC);
					cell.setCellValue(Double.parseDouble(val));
				} else {
					cell.setCellValue(sheetVector.get(row)[col].replace("\"", ""));
				}
			}
		}
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public void writeSheet(String key, Vector<String[]> sheetVector, XSSFWorkbook workbook) {
	XSSFSheet worksheet = workbook.createSheet(key);
	int count=0;//keeps track of rows; one below the row int because of header row
	final Pattern NUMERIC = Pattern.compile("^\\d+.?\\d*$");
	//for each row, create the row in excel
	for (int row=0; row<sheetVector.size();row++){
		XSSFRow row1 = worksheet.createRow( count);
		count++;
		//for each col, write it to that row.7
		for (int col=0; col<sheetVector.get(row).length;col++){
			XSSFCell cell = row1.createCell(col);
			if(sheetVector.get(row)[col] != null) {
				String val = sheetVector.get(row)[col];
				//Check if entire value is numeric - if so, set cell type and parseDouble, else write normally
				if(val != null && !val.isEmpty() && NUMERIC.matcher(val).find()) {
					cell.setCellType(Cell.CELL_TYPE_NUMERIC);
					cell.setCellValue(Double.parseDouble(val));
				} else {
					cell.setCellValue(sheetVector.get(row)[col].replace("\"", ""));
				}
			}
		}
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
@Override
protected void printHeader(List<String> header, ByteArrayOutputStream out) {
	wb = new XSSFWorkbook();
       sheet = wb.createSheet("NextReports");

       XSSFRow headerRow = sheet.createRow(0);
       int col = 0;        
	if (header != null) {
		for (String s : header) {
			XSSFCell cell = headerRow.createCell(col);
			cell.setCellType(XSSFCell.CELL_TYPE_STRING);
			if (s == null) {
				s = "";
			}
			cell.setCellValue(wb.getCreationHelper().createRichTextString(s));
			col++;
		}
	}		
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public static XSSFWorkbook convertToXSSFWorkbook(String html)
{
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Sheet1");
    Document doc = Jsoup.parse(html);
    for (Element table : doc.select("table")) {
        int rownum = 0;
        for (Element row : table.select("tr")) {
            XSSFRow exlrow = sheet.createRow(rownum++);
            int cellnum = 0;
            for (Element tds : row.select("td")) {
                StringUtils.isNumeric("");
                XSSFCell cell = exlrow.createCell(cellnum++);
                cell.setCellValue(tds.text());
            }
        }
    }
    return workbook;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public static XSSFCell createCellIfNotPresent(XSSFRow row, int colIndex) {
	String message = "XSSFRow must not be null!";
	Objects.requireNonNull(row, () -> message);
	XSSFCell cell = row.getCell(colIndex);
	if (Objects.isNull(cell)) {
		cell = row.createCell(colIndex);
	}
	return cell;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public static XSSFCell createCellIfNotPresent(XSSFRow row,int colIndex){
	String message="XSSFRow must not be null!";
	Objects.requireNonNull(row, () -> message);
	XSSFCell cell=row.getCell(colIndex);
	if(Objects.isNull(cell)){
		cell=row.createCell(colIndex);
	}
	return cell;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public static XSSFCell asNumber(XSSFRow row, int columnIndex, Number value) {
    XSSFCell cell = row.createCell(columnIndex, Cell.CELL_TYPE_NUMERIC);
    if (value != null) {
        cell.setCellValue(value.doubleValue());
    }
    return cell;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public static XSSFCell asText(XSSFRow row, int columnIndex, Object value) {
    XSSFCell cell = row.createCell(columnIndex, Cell.CELL_TYPE_STRING);
    if (value != null) {
        cell.setCellValue((String) value);
    }
    return cell;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public static XSSFCell asDate(XSSFRow row, int columnIndex, LocalDate localDate) {
    XSSFCell cell = row.createCell(columnIndex, Cell.CELL_TYPE_STRING);
    if (localDate != null) {
        Date date = Date.from(localDate.atStartOfDay(ZoneId.systemDefault()).toInstant());
        cell.setCellValue(date);
    }
    return cell;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
private void shiftRange(int numRowsDown, int rowStart, int rowEnd, int colStart, int colEnd) throws Exception {
        // loop through from bottom row up to either SHIFT the whole row, or copy necessary columns, insert  new row below, insert the data from the cell and then delete all style and format from the original cell
        for(int j=rowEnd;j>rowStart;j--) {
            XSSFRow row = worksheet.getRow(j);
            XSSFRow newRow = worksheet.getRow(j+numRowsDown);
            if(row == null && newRow == null) 
                continue;
            else if (row == null) {
                worksheet.removeRow(newRow);
                continue;
            }
            if(newRow == null) {
                newRow = worksheet.createRow(j+numRowsDown);
            }
//            newRow = worksheet.getRow(j+numRowsDown);
            //if(row ==null) continue;
            for(int k=colStart;k<=colEnd;k++) {
                XSSFCell cell = row.getCell(k);
                if(cell != null) {
                    XSSFCell newCell = newRow.getCell(k)==null ? newRow.createCell(k) : newRow.getCell(k);
                    if(cell==null) continue;
                    if(cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
                        String calc = cell.getCellFormula();
                        newCell.setCellStyle(cell.getCellStyle());
                        cell.setCellType(XSSFCell.CELL_TYPE_BLANK);
                        this.setCellFormula(newCell, calc);
                    } else if(cell.getCellType()==XSSFCell.CELL_TYPE_BLANK) {
                        newCell.setCellType(XSSFCell.CELL_TYPE_BLANK);
                    } else {
                        newCell.copyCellFrom(cell, new CellCopyPolicy());
                        newCell.setCellStyle(cell.getCellStyle());
                    }
                }
            }
        }
    }