public static Cell createCell(Row row, int colNum, CellStyle cellStyle, Object cellValue, Boolean isNum) { Cell cell = row.createCell(colNum); cell.setCellStyle(cellStyle); if (null != cellValue) { if (isNum) { cell.setCellValue(Double.parseDouble(cellValue.toString())); } else { cell.setCellValue(cellValue.toString()); } } return cell; }
Workbook wb = new HSSFWorkbook(); //Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow((short)0); // Create a cell and put a value in it. Cell cell = row.createCell(0); cell.setCellValue(1); // Or do it on one line. row.createCell(1).setCellValue(1.2); row.createCell(2).setCellValue( createHelper.createRichTextString("This is a string")); row.createCell(3).setCellValue(true); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
@Override protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Sheet sheet = workbook.createSheet("Test Sheet"); Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue("Test Value"); } };
Workbook wb = WorkbookFactory.create(new File("myFile.xls")); // Or .xlsx Sheet s = wb.getSheet(0); Row r1 = s.getRow(0); r1.createCell(4).setCellValue(4.5); r1.createCell(5).setCellValue("Hello"); FileOutputStream out = new FileOutputStream("newFile.xls"); // Or .xlsx wb.write(out); out.close();
Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet("sheetname"); jdbcTemplate.query("select * from xyz", new RowCallbackHandler() { public void processRow(ResultSet rs) throws SQLException { String columnName = rs.getMetaData().getColumnName(column); String columnValue = rs.getString(column); Row row = sheet.createRow(sheetRow); row.createCell(column).setCellValue(columnValue); } }); try (FileOutputStream out = new FileOutputStream(new File("export.xlsx"))) { workbook.write(out); }
wbk=new SXSSFWorkbook(500); sheet=vbk.createSheet("book 1"); try{ Row rowhead=sheet.createRow(0); rowhead.createCell(0).setCellValue("Hello"); ... <Here you fill the Excel with the resultset of your query to the AS400 file using JT400> ... FileOutputStream fileOut = new FileOutputStream(excelPath); wbk.write(fileOut); fileOut.flush(); fileOut.close(); }catch(Exception e){...}
Row xlsRow = stepData.sheet.createRow( 0 ); Cell cell = xlsRow.createCell( 5 ); CellStyle cellStyle = stepData.wb.createCellStyle(); cellStyle.setBorderRight( BorderStyle.THICK ); cellStyle.setFillPattern( FillPatternType.FINE_DOTS ); cell.setCellStyle( cellStyle ); cell = xlsRow.createCell( 6 ); cellStyle.setDataFormat( format.getFormat( "##0,000.0" ) ); cell.setCellStyle( cellStyle );
public void copyUpdatedCells(Sheet sheet) { RowColKey[] keys = new RowColKey[_sharedCellsByRowCol.size()]; _sharedCellsByRowCol.keySet().toArray(keys); Arrays.sort(keys); for (int i = 0; i < keys.length; i++) { RowColKey key = keys[i]; Row row = sheet.getRow(key.getRowIndex()); if (row == null) { row = sheet.createRow(key.getRowIndex()); } Cell destCell = row.getCell(key.getColumnIndex()); if (destCell == null) { destCell = row.createCell(key.getColumnIndex()); } ForkedEvaluationCell srcCell = _sharedCellsByRowCol.get(key); srcCell.copyValue(destCell); } }
final Cell cell = headerRow.createCell(i); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(metadata.get(i).getName()); Cell cell = row.createCell(c, excelType); writeExcelValue(cell, objects.get(c), type);
Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); CellStyle style; DataFormat format = wb.createDataFormat(); Row row; Cell cell; short rowNum = 0; short colNum = 0; row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("0.0")); cell.setCellStyle(style); row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("#,##0.0000")); cell.setCellStyle(style); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
File excelFile = new File("C:\\path\\of\\excel\\file\\excel.xlsx"); String cellNo = 3; //column number of "UserName" column String rowNo = 1; // row number String userName = "Test1"; // username fetched from textbox FileInputStream fis = new FileInputStream(excelFile); XSSFWorkbook workbook = (XSSFWorkbook) WorkbookFactory.create(fis); XSSFSheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(rowNo); row.createCell(cellNo).setCellValue(userName); fis.close(); FileOutputStream fos = new FileOutputStream(excelFile); workbook.write(fos); fos.close();
public static void main( String[] args ) throws IOException { try (Workbook wb = new XSSFWorkbook()) { // or new HSSFWorkbook() // register the add-in wb.addToolPack(new BloombergAddIn()); Sheet sheet = wb.createSheet(); Row row = sheet.createRow(0); row.createCell(0).setCellFormula("BDP(\"GOOG Equity\",\"CHG_PCT_YTD\")/100"); row.createCell(1).setCellFormula("BDH(\"goog us equity\",\"EBIT\",\"1/1/2005\",\"12/31/2009\",\"per=cy\",\"curr=USD\") "); row.createCell(2).setCellFormula("BDS(\"goog us equity\",\"top_20_holders_public_filings\") "); try (FileOutputStream out = new FileOutputStream("bloomberg-demo.xlsx")) { wb.write(out); } } } }
... ... int rowNum = 0; int colNum = 0; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("sheetname"); Row row = sheet.createRow(rowNum++); row.createCell(colNum++).setCellValue("your value"); ... ... FileOutputStream fileOut = new FileOutputStream("your excel (xlsx) file name"); workbook.write(fileOut); fileOut.close;
protected void addColumnValue(Row row, String value) { int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum(); Cell cell = row.createCell(cellIndex); cell.setCellValue(createRichTextString(value)); if (facetStyle != null) { cell.setCellStyle(facetStyle); } }
@Override protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Sheet sheet = workbook.createSheet("Test Sheet"); Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue("Test Value"); } };
Workbook wb = WorkbookFactory.create(new File("existing.xls")); CellStyle origStyle = wb.getCellStyleAt(1); // Or from a cell Workbook newWB = new XSSFWorkbook(); Sheet sheet = newWB.createSheet(); Row r1 = sheet.createRow(0); Cell c1 = r1.createCell(0); CellStyle newStyle = newWB.createCellStyle(); newStyle.cloneStyleFrom(origStyle); c1.setCellStyle(newStyle); newWB.write(new FileOutpuStream("new.xlsx"));
currentRow = sheet.createRow(rowIndex); currentCell = currentRow.createCell(cellIndex); setCellValue(currentCell, cellValue);
Row r = sheet.getRow(9); // 10-1 if (r == null) { // First cell in the row, create r = sheet.createRow(9); } Cell c = r.getCell(3); // 4-1 if (c == null) { // New cell c = r.createCell(3, Cell.CELL_TYPE_NUMERIC); } c.setCellValue(100);
import java.io.*; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import au.com.bytecode.opencsv.CSVReader; class Test { public static void main(String[] args) throws IOException { Workbook wb = new HSSFWorkbook(); CreationHelper helper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); CSVReader reader = new CSVReader(new FileReader("data.csv")); String[] line; int r = 0; while ((line = reader.readNext()) != null) { Row row = sheet.createRow((short) r++); for (int i = 0; i < line.length; i++) row.createCell(i) .setCellValue(helper.createRichTextString(line[i])); } // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close(); } }
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Row row = sheet.getRow(0); Cell cell2 = row.createCell(2); //set cell formula cell2.setCellFormula("SUM(A1:B1)+10"); //evaluate the formula evaluator.evaluateFormulaCell(cell2); //set the cell type to numeric cell2.setCellType(Cell.CELL_TYPE_NUMERIC); System.out.println(cell2.getNumericCellValue()); //write data to excel file FileOutputStream out = new FileOutputStream(new File("res/Book1.xlsx")); workbook.write(out); out.close(); System.out.println("Excel written successfully..");