List of usage examples for org.apache.poi.xssf.usermodel XSSFCell setCellValue
@Override public void setCellValue(boolean value)
From source file:tan.jam.jsf.Shifting.java
public static void InsertRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); XSSFRow newRow = worksheet.getRow(destinationRowNum); XSSFRow sourceRow = worksheet.getRow(sourceRowNum); if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {// w w w . j a v a 2 s. c o m newRow = worksheet.createRow(destinationRowNum); } for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = sourceRow.getCell(i); XSSFCell newCell = newRow.createCell(i); if (oldCell == null) { newCell = null; continue; } XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); ; newCell.setCellStyle(newCellStyle); if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } newCell.setCellType(oldCell.getCellType()); switch (oldCell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA: newCell.setCellFormula("+" + "F" + destinationRowNum + "*G" + destinationRowNum); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: //newCell.setCellValue(oldCell.getNumericCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: newCell.setCellValue(""); break; } } for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } int inc = destinationRowNum + 1; worksheet.getRow(destinationRowNum).getCell(7).setCellFormula("+F" + inc + "*G" + inc); }
From source file:tan.jam.jsf.Shifting.java
private static void CopyData(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum, int Mov) { XSSFRow newRow = worksheet.getRow(destinationRowNum); XSSFRow sourceRow = worksheet.getRow(sourceRowNum); for (int i = sourceRow.getLastCellNum(); i > 8 + Mov; i--) { int d = i - 1; XSSFCell oldCell = sourceRow.getCell(d); XSSFCell newCell = newRow.createCell(i); if (oldCell == null) { newCell = null;//ww w . j a v a2 s .c o m continue; } XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); ; newCell.setCellStyle(newCellStyle); if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } newCell.setCellType(oldCell.getCellType()); switch (oldCell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } }
From source file:test.WriteXLSX.java
public static void main(String[] args) throws IOException { XSSFWorkbook workbook;/*from ww w.j av a2 s . co m*/ try (FileInputStream fis = new FileInputStream(new File("D:/desk/test.xlsx"))) { workbook = new XSSFWorkbook(fis); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row1 = sheet.createRow(0); XSSFCell r1c1 = row1.createCell(0); r1c1.setCellValue("Emd Id"); XSSFCell r1c2 = row1.createCell(1); r1c2.setCellValue("NAME"); XSSFCell r1c3 = row1.createCell(2); r1c3.setCellValue("AGE"); XSSFRow row2 = sheet.createRow(1); XSSFCell r2c1 = row2.createCell(0); r2c1.setCellValue("1"); XSSFCell r2c2 = row2.createCell(1); r2c2.setCellValue("Ram"); XSSFCell r2c3 = row2.createCell(2); r2c3.setCellValue("20"); XSSFRow row3 = sheet.createRow(2); XSSFCell r3c1 = row3.createCell(0); r3c1.setCellValue("2"); XSSFCell r3c2 = row3.createCell(1); r3c2.setCellValue("Shyam"); XSSFCell r3c3 = row3.createCell(2); r3c3.setCellValue("25"); } try (FileOutputStream fos = new FileOutputStream(new File("D:\\desk\\test2.xlsx"))) { workbook.write(fos); } System.out.println("Done"); }
From source file:tr.org.liderahenk.liderconsole.core.utils.SWTResourceManager.java
License:Open Source License
private static XSSFWorkbook createWorkbookFromTable(TableViewer tableViewer, String sheetName) { // Create workbook & sheet XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(sheetName == null ? "Sheet1" : sheetName); // Shade the background of the header row XSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setBorderTop(CellStyle.BORDER_THIN); headerStyle.setBorderBottom(CellStyle.BORDER_THIN); headerStyle.setBorderLeft(CellStyle.BORDER_THIN); headerStyle.setBorderRight(CellStyle.BORDER_THIN); headerStyle.setAlignment(HorizontalAlignment.CENTER); // Add header row Table table = tableViewer.getTable(); TableColumn[] columns = table.getColumns(); int rowIndex = 0; int cellIndex = 0; XSSFRow header = sheet.createRow((short) rowIndex++); for (TableColumn column : columns) { XSSFCell cell = header.createCell(cellIndex++); cell.setCellValue(column.getText()); cell.setCellStyle(headerStyle);/*from w w w .j a va2 s. co m*/ } // Add data rows TableItem[] items = tableViewer.getTable().getItems(); for (TableItem item : items) { // create a new row XSSFRow row = sheet.createRow((short) rowIndex++); cellIndex = 0; for (int i = 0; i < columns.length; i++) { // Create a new cell XSSFCell cell = row.createCell(cellIndex++); String text = item.getText(i); // Set the horizontal alignment (default to RIGHT) XSSFCellStyle cellStyle = wb.createCellStyle(); if (LiderCoreUtils.isInteger(text)) { cellStyle.setAlignment(HorizontalAlignment.RIGHT); } else if (LiderCoreUtils.isValidDate(text, ConfigProvider.getInstance().get(LiderConstants.CONFIG.DATE_FORMAT))) { cellStyle.setAlignment(HorizontalAlignment.CENTER); } else { cellStyle.setAlignment(HorizontalAlignment.LEFT); } cell.setCellStyle(cellStyle); // Set the cell's value cell.setCellValue(text); } } // Auto-fit the columns for (int i = 0; i < columns.length; i++) { sheet.autoSizeColumn((short) i); } return wb; }
From source file:uk.ac.bbsrc.tgac.miso.core.util.FormUtils.java
License:Open Source License
public static void createPlateExportForm(File outpath, JSONArray jsonArray) throws Exception { InputStream in = null;/*w w w . ja va2 s . co m*/ in = FormUtils.class.getResourceAsStream("/forms/ods/plate_input.xlsx"); if (in != null) { XSSFWorkbook oDoc = new XSSFWorkbook(in); XSSFSheet sheet = oDoc.getSheet("Input"); FileOutputStream fileOut = new FileOutputStream(outpath); int i = 4; for (JSONObject jsonObject : (Iterable<JSONObject>) jsonArray) { String sampleinwell = jsonObject.getString("value"); //"sampleid:wellid:samplealias:projectname" String sampleId = sampleinwell.split(":")[0]; String wellId = sampleinwell.split(":")[1]; String sampleAlias = sampleinwell.split(":")[2]; String projectName = sampleinwell.split(":")[3]; XSSFRow row = sheet.createRow(i); XSSFCell cellA = row.createCell(0); cellA.setCellValue(wellId); XSSFCell cellB = row.createCell(1); cellB.setCellValue(projectName); XSSFCell cellC = row.createCell(2); cellC.setCellValue(sampleAlias); i++; } oDoc.write(fileOut); fileOut.close(); } else { throw new IOException("Could not read from resource."); } }
From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java
License:Open Source License
public static void createSampleExportForm(File outpath, JSONArray jsonArray) throws Exception { InputStream in = null;//ww w. j a v a2 s.c om in = FormUtils.class.getResourceAsStream("/forms/ods/export_samples.xlsx"); if (in != null) { XSSFWorkbook oDoc = new XSSFWorkbook(in); XSSFSheet sheet = oDoc.getSheet("samples_export"); FileOutputStream fileOut = new FileOutputStream(outpath); int i = 5; for (JSONObject jsonObject : (Iterable<JSONObject>) jsonArray) { if ("sampleinwell".equals(jsonObject.getString("name"))) { String sampleinwell = jsonObject.getString("value"); // "sampleid:wellid:samplealias:projectname:projectalias:dnaOrRNA" String sampleId = sampleinwell.split(":")[0]; String wellId = sampleinwell.split(":")[1]; String sampleAlias = sampleinwell.split(":")[2]; String projectName = sampleinwell.split(":")[3]; String projectAlias = sampleinwell.split(":")[4]; String dnaOrRNA = sampleinwell.split(":")[5]; XSSFRow row = sheet.createRow(i); XSSFCell cellA = row.createCell(0); cellA.setCellValue(projectName); XSSFCell cellB = row.createCell(1); cellB.setCellValue(projectAlias); XSSFCell cellC = row.createCell(2); cellC.setCellValue(sampleId); XSSFCell cellD = row.createCell(3); cellD.setCellValue(sampleAlias); XSSFCell cellE = row.createCell(4); cellE.setCellValue(wellId); XSSFCell cellG = row.createCell(6); XSSFCell cellH = row.createCell(7); XSSFCell cellI = row.createCell(8); XSSFCell cellL = row.createCell(11); if ("R".equals(dnaOrRNA)) { cellG.setCellValue("NA"); cellL.setCellFormula("1000/H" + (i + 1)); } else if ("D".equals(dnaOrRNA)) { cellH.setCellValue("NA"); cellI.setCellValue("NA"); cellL.setCellFormula("1000/G" + (i + 1)); } XSSFCell cellM = row.createCell(12); cellM.setCellFormula("50-L" + (i + 1)); i++; } } oDoc.write(fileOut); fileOut.close(); } else { throw new IOException("Could not read from resource."); } }
From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java
License:Open Source License
public static void createLibraryPoolExportFormFromWeb(File outpath, JSONArray jsonArray, String indexFamily) throws Exception { InputStream in = null;// w ww.j av a 2s. c o m in = FormUtils.class.getResourceAsStream("/forms/ods/export_libraries_pools.xlsx"); if (in != null) { XSSFWorkbook oDoc = new XSSFWorkbook(in); XSSFSheet sheet = oDoc.getSheet("library_pool_export"); FileOutputStream fileOut = new FileOutputStream(outpath); XSSFRow row2 = sheet.getRow(1); int i = 6; for (JSONObject jsonObject : (Iterable<JSONObject>) jsonArray) { if ("paired".equals(jsonObject.getString("name"))) { XSSFCell row2cellA = row2.createCell(0); row2cellA.setCellValue(jsonObject.getString("value")); } else if ("platform".equals(jsonObject.getString("name"))) { XSSFCell row2cellB = row2.createCell(1); row2cellB.setCellValue(jsonObject.getString("value")); } else if ("type".equals(jsonObject.getString("name"))) { XSSFCell row2cellC = row2.createCell(2); row2cellC.setCellValue(jsonObject.getString("value")); } else if ("selection".equals(jsonObject.getString("name"))) { XSSFCell row2cellD = row2.createCell(3); row2cellD.setCellValue(jsonObject.getString("value")); } else if ("strategy".equals(jsonObject.getString("name"))) { XSSFCell row2cellE = row2.createCell(4); row2cellE.setCellValue(jsonObject.getString("value")); } if ("sampleinwell".equals(jsonObject.getString("name"))) { String sampleinwell = jsonObject.getString("value"); // "sampleid:wellid:samplealias:projectname:projectalias:dnaOrRNA" String sampleId = sampleinwell.split(":")[0]; String wellId = sampleinwell.split(":")[1]; String sampleAlias = sampleinwell.split(":")[2]; String projectName = sampleinwell.split(":")[3]; String projectAlias = sampleinwell.split(":")[4]; XSSFRow row = sheet.createRow(i); XSSFCell cellA = row.createCell(0); cellA.setCellValue(projectName); XSSFCell cellB = row.createCell(1); cellB.setCellValue(projectAlias); XSSFCell cellC = row.createCell(2); cellC.setCellValue(sampleId); XSSFCell cellD = row.createCell(3); cellD.setCellValue(sampleAlias); XSSFCell cellE = row.createCell(4); cellE.setCellValue(wellId); if (indexFamily != null) { XSSFCell cellJ = row.createCell(9); cellJ.setCellValue(indexFamily); } i++; } } oDoc.write(fileOut); fileOut.close(); } else { throw new IOException("Could not read from resource."); } }
From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java
License:Open Source License
public static void createBoxContentsSpreadsheet(File outpath, ArrayList<String> array) throws IOException { InputStream in = null;/*from w ww . j a va2 s. c o m*/ in = FormUtils.class.getResourceAsStream("/forms/ods/box_input.xlsx"); if (in != null) { XSSFWorkbook oDoc = new XSSFWorkbook(in); XSSFSheet sheet = oDoc.getSheet("Input"); FileOutputStream fileOut = new FileOutputStream(outpath); String boxInfo = array.remove(0); String boxName = boxInfo.split(":")[0]; String boxAlias = boxInfo.split(":")[1]; XSSFRow row1 = sheet.createRow(1); XSSFCell cellA = row1.createCell(0); cellA.setCellValue(boxName); XSSFCell cellB = row1.createCell(1); cellB.setCellValue(boxAlias); int i = 4; // start on row 4 of the sheet for (String item : array) { String position = item.split(":")[0]; String name = item.split(":")[1]; String alias = item.split(":")[2]; XSSFRow row = sheet.createRow(i); cellA = row.createCell(0); cellA.setCellValue(position); cellB = row.createCell(1); cellB.setCellValue(name); XSSFCell cellC = row.createCell(2); cellC.setCellValue(alias); i++; } oDoc.write(fileOut); fileOut.close(); } else { throw new IOException("Could not read from resource."); } }
From source file:uk.ac.ebi.generic.util.ExcelWorkBook.java
License:Apache License
public ExcelWorkBook(String[] titles, Object[][] tableData, String sheetTitle) throws Exception { this.wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); // create a new sheet XSSFSheet sheet = wb.createSheet(sheetTitle); XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true);//from w ww. j a v a 2 s .c o m sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //header row XSSFRow headerRow = sheet.createRow(0); //headerRow.setHeightInPoints(40); XSSFCell headerCell; for (int j = 0; j < titles.length; j++) { headerCell = headerRow.createCell(j); headerCell.setCellValue(titles[j]); //headerCell.setCellStyle(styles.get("header")); } // data rows // Create a row and put some cells in it. Rows are 0 based. // Then set value for that created cell for (int k = 0; k < tableData.length; k++) { XSSFRow row = sheet.createRow(k + 1); // data starts from row 1 for (int l = 0; l < tableData[k].length; l++) { XSSFCell cell = row.createCell(l); String cellStr = null; try { cellStr = tableData[k][l].toString(); } catch (Exception e) { cellStr = ""; } //System.out.println("cell " + l + ": " + cellStr); // make hyperlink in cell if ((cellStr.startsWith("http://") || cellStr.startsWith("https://")) && !cellStr.contains("|")) { //need to encode URI for this version of ExcelWorkBook cellStr = URIUtil.encodePath(cellStr, "UTF-8"); cellStr = cellStr.replace("%3F", "?"); // so that url link would work //System.out.println("cellStr: " + cellStr); XSSFHyperlink url_link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL); url_link.setAddress(cellStr); cell.setCellValue(cellStr); cell.setHyperlink(url_link); } else { cell.setCellValue(cellStr); } //System.out.println((String)tableData[k][l]); } } }
From source file:uk.ac.manchester.cs.owl.semspreadsheets.model.xssf.impl.WorkbookXSSFImpl.java
License:Open Source License
public void visit(SetCellValue setCellValue) { XSSFSheet xssfSheet = workbook.getSheet(setCellValue.getSheet().getName()); XSSFRow xssfRow = xssfSheet.getRow(setCellValue.getRow()); if (xssfRow == null && setCellValue.getNewValue() != null) { xssfRow = xssfSheet.createRow(setCellValue.getRow()); }//ww w . ja v a 2s . com XSSFCell xssfCell = xssfRow.getCell(setCellValue.getCol()); if (xssfCell == null && setCellValue.getNewValue() != null) { xssfCell = xssfRow.createCell(setCellValue.getCol()); } if (xssfCell != null) { if (setCellValue.getNewValue() != null) { xssfCell.setCellValue(new XSSFRichTextString(setCellValue.getNewValue().toString())); } else { xssfRow.removeCell(xssfCell); } } }