HSSFRow newRow = worksheet.getRow(destinationRowNum); HSSFRow sourceRow = worksheet.getRow(sourceRowNum); worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else { newRow = worksheet.createRow(destinationRowNum); for (int i = 0; i < sourceRow.getLastCellNum(); i++) { HSSFCell oldCell = sourceRow.getCell(i); HSSFCell newCell = newRow.createCell(i); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); 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() )),
moveCommentsForRowShift(startRow, endRow, n); deleteOverwrittenHyperlinksForRowShift(startRow, endRow, n); HSSFRow row = getRow(rowNum); row2Replace.removeAllCells(); row2Replace.setHeight(row.getHeight()); row.setHeight((short) 0xff); for (Iterator<Cell> cells = row.cellIterator(); cells.hasNext(); ) { HSSFCell cell = (HSSFCell) cells.next(); HSSFHyperlink link = cell.getHyperlink(); row.removeCell(cell); CellValueRecordInterface cellRecord = cell.getCellValueRecord(); cellRecord.setRow(rowNum + n); row2Replace.createCellFromRecord(cellRecord); _sheet.addValueRecord(rowNum + n, cellRecord); row.removeAllCells(); recomputeFirstAndLastRowsForRowShift(startRow, endRow, n);
/** * add a row to the sheet * * @param addLow whether to add the row to the low level model - false if its already there */ private void addRow(HSSFRow row, boolean addLow) { _rows.put(Integer.valueOf(row.getRowNum()), row); if (addLow) { _sheet.addRow(row.getRowRecord()); } boolean firstRow = _rows.size() == 1; if (row.getRowNum() > getLastRowNum() || firstRow) { _lastrow = row.getRowNum(); } if (row.getRowNum() < getFirstRowNum() || firstRow) { _firstrow = row.getRowNum(); } }
/** * Create a new row within the sheet and return the high level representation * * @param rownum row number * @return High level HSSFRow object representing a row in the sheet * @see org.apache.poi.hssf.usermodel.HSSFRow * @see #removeRow(org.apache.poi.ss.usermodel.Row) */ @Override public HSSFRow createRow(int rownum) { HSSFRow row = new HSSFRow(_workbook, this, rownum); // new rows inherit default height from the sheet row.setHeight(getDefaultRowHeight()); row.getRowRecord().setBadFontHeight(false); addRow(row, true); return row; }
FileInputStream file = new FileInputStream(new File("/output.xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheet("Sheet1"); HSSFRow row = sheet.createRow((short) 0); HSSFCellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFRow row2 = sheet.createRow(2); HSSFCell cell = row2.createCell(1); cell.setCellValue("5"); HSSFRow row3 = sheet.createRow(3); HSSFCell cell2 = row2.createCell(1); cell2.setCellValue("5"); file.close(); FileOutputStream outFile =new FileOutputStream(new File("/output10.xls")); workbook.write(outFile); outFile.close();
HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("test sheet"); HSSFCellStyle currencyStyle = wb.createCellStyle(); HSSFDataFormat dataFormat = wb.createDataFormat(); currencyStyle.setDataFormat(dataFormat.getFormat("$#,##0.00.000")); HSSFRow row1 = sheet.createRow(2); row1.setHeight((short) (256*1.5)); HSSFCell cell1 = row1.createCell(1); cell1.setCellStyle(currencyStyle); cell1.setCellValue(Double.parseDouble("5")); // output $5,00,000 HSSFCell cell2 = row1.createCell(2); cell2.setCellStyle(currencyStyle); cell2.setCellValue(Double.parseDouble("499") / 100); // output $4,99,000 HSSFCell cell3 = row1.createCell(3); cell3.setCellStyle(currencyStyle); cell3.setCellValue(Double.parseDouble("123456789") / 100000); // output $1 234,56,789 FileOutputStream out = new FileOutputStream("currency_data_format.xls"); wb.write(out); out.close(); } catch (IOException e) { System.out.println(e.getMessage());
try { String filename = "C:/NewExcelFile.xls" ; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("FirstSheet"); HSSFRow rowhead = sheet.createRow((short)0); rowhead.createCell(0).setCellValue("No."); rowhead.createCell(1).setCellValue("Name"); rowhead.createCell(2).setCellValue("Address"); rowhead.createCell(3).setCellValue("Email"); HSSFRow row = sheet.createRow((short)1); row.createCell(0).setCellValue("1"); row.createCell(1).setCellValue("Sankumarsingh"); row.createCell(2).setCellValue("India"); row.createCell(3).setCellValue("sankumarsingh@gmail.com"); FileOutputStream fileOut = new FileOutputStream(filename); workbook.write(fileOut); fileOut.close(); System.out.println("Your excel file has been generated!");
public static void main(String[] args) throws IOException { try (FileInputStream fileIn = new FileInputStream("workbook.xls")) { POIFSFileSystem fs = new POIFSFileSystem(fileIn); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(2); if (row == null) row = sheet.createRow(2); HSSFCell cell = row.getCell(3); if (cell == null) cell = row.createCell(3); cell.setCellType(CellType.STRING); cell.setCellValue("a test"); // Write the output to a file try (FileOutputStream fileOut = new FileOutputStream("workbookout.xls")) { wb.write(fileOut); } } } }
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file)); HSSFSheet s = wb.getSheetAt(0); s.setActive(true); HSSFRow row = s.getRow(0); HSSFCell cell = row.getCell(0); cell.setAsActiveCell(); FileOutputStream out = new FileOutputStream(file);
try { FileInputStream fileInput = new FileInputStream("sample.xls"); BufferedInputStream bufferInput = new BufferedInputStream(fileInput); POIFSFileSystem poiFileSystem = new POIFSFileSystem(bufferInput); Biff8EncryptionKey.setCurrentUserPassword("password"); HSSFWorkbook workbook = new HSSFWorkbook(poiFileSystem, true); HSSFSheet sheet = workbook.getSheetAt(0); HSSFRow row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue("Hello World"); FileOutputStream fileOut = new FileOutputStream(fname); workbook.writeProtectWorkbook(Biff8EncryptionKey.getCurrentUserPassword(), ""); workbook.write(fileOut); } catch (Exception ex) { }
public static void main( String[] args ) throws IOException { try (HSSFWorkbook wb = new HSSFWorkbook()) { HSSFSheet s = wb.createSheet(); HSSFFont f2 = wb.createFont(); HSSFCellStyle cs = wb.createCellStyle(); cs.setFont(f2); // Word Wrap MUST be turned on cs.setWrapText(true); HSSFRow r = s.createRow(2); r.setHeight((short) 0x349); HSSFCell c = r.createCell(2); c.setCellType(CellType.STRING); c.setCellValue("Use \n with word wrap on to create a new line"); c.setCellStyle(cs); s.setColumnWidth(2, (int) ((50 * 8) / ((double) 1 / 20))); try (FileOutputStream fileOut = new FileOutputStream("workbook.xls")) { wb.write(fileOut); } } } }
HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet realSheet = workbook.createSheet("Sheet xls"); HSSFSheet hidden = workbook.createSheet("hidden"); for (int i = 0, length= countryName.length; i < length; i++) { String name = countryName[i]; HSSFRow row = hidden.createRow(i); HSSFCell cell = row.createCell(0); cell.setCellValue(name); } Name namedCell = workbook.createName(); namedCell.setNameName("hidden"); namedCell.setRefersToFormula("hidden!$A$1:$A$" + countryName.length); DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden"); CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0); HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint); workbook.setSheetHidden(1, true); realSheet.addValidationData(validation); FileOutputStream stream = new FileOutputStream("c:\\range.xls"); workbook.write(stream); stream.close();
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("foo.xls")); HSSFSheet s = wb.getSheetAt(0); HSSFRow r = s.getRow(0); System.out.println("Cell A1 is " + r.getCell(0));
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; rows = sheet.getPhysicalNumberOfRows(); row = sheet.getRow(i); if(row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if(tmp > cols) cols = tmp; if(row != null) { for(int c = 0; c < cols; c++) { cell = row.getCell((short)c); if(cell != null) {
HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFRow row = sheet.createRow((short) 0); HSSFCell cell = row.createCell((short) 0); cell.setCellValue("Default Palette"); HSSFCellStyle style = wb.createCellStyle(); style.setFillForegroundColor(HSSFColor.LIME.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFont(font); cell.setCellStyle(style); FileOutputStream out = new FileOutputStream("default_palette.xls"); wb.write(out); out.close(); cell.setCellValue("Modified Palette"); out = new FileOutputStream("modified_palette.xls"); wb.write(out); out.close();
HSSFWorkbook workBook = new HSSFWorkbook (); HSSFSheet sheet1 = workBook.createSheet(); HSSFRow row1 = sheet1.createRow(10); HSSFCell cell1 = row1.createCell(0); cell1.setCellValue("text: The new line which should be locked"); // SETTING INITIAL VALUE CellRangeAddressList displayNameCellRange = new CellRangeAddressList(displayNameCell.getRowIndex(),displayNameCell.getRowIndex(),displayNameCell.getColumnIndex(),displayNameCell.getColumnIndex()); FileOutputStream fileOut1 = new FileOutputStream("D:\\book.xls"); workBook.write(fileOut1); fileOut1.close();
InputStream inp; HSSFWorkbook wb = null; HSSFSheet sheet = null; HSSFRow hssf_DataRow; HSSFCell cell = null; inp = new FileInputStream(FileName); wb = new HSSFWorkbook(inp); sheet = wb.getSheetAt(1); hssf_DataRow = sheet.createRow((short) 1); cell = hssf_DataRow.createCell(1); HSSFFont font = (HSSFFont) sheet.getWorkbook().createFont(); HSSFCellStyle style = (HSSFCellStyle) cell.getCellStyle(); font.setBold(true); style.setFont(font); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); cell.setCellStyle(style);
public Integer Check4(int b) throws IOException { InputStream myxls = new FileInputStream("book.xls"); HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0); // first sheet HSSFRow row = sheet.getRow(1); // first row //HSSFCell cell0 = row.getCell((short)a); // first arg HSSFCell cell1 = row.getCell((short)b); // second arg String sell = cell1.toString(); cell1.setCellType(HSSFCell.CELL_TYPE_NUMERIC); System.out.println("smth "+ (int) Math.round(cell1.getNumericCellValue())); return (int) Math.round(cell1.getNumericCellValue()); }
public extractDataFromXls(String fileName, sheetName) throws FileNotFoundException { FileInputStream fileInputStream = new FileInputStream(fileName); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet(sheetName); HSSFRow row = worksheet.getRow(0); for (short i = 0; i < 5; i++) { HSSFCell cell = row.getCell(i); String value = cell.getStringCellValue(); System.out.println(value); } }