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(), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress);
/** * Adjusts the column width to fit the contents.<p> * * This process can be relatively slow on large sheets, so this should * normally only be called once per column, at the end of your * processing.<p> * * You can specify whether the content of merged cells should be considered or ignored. * Default is to ignore merged cells. * * @param column the column index * @param useMergedCells whether to use the contents of merged cells when calculating the width of the column */ @Override public void autoSizeColumn(int column, boolean useMergedCells) { double width = SheetUtil.getColumnWidth(this, column, useMergedCells); if (width != -1) { width *= 256; int maxColumnWidth = 255 * 256; // The maximum column width for an individual cell is 255 characters if (width > maxColumnWidth) { width = maxColumnWidth; } setColumnWidth(column, (int) (width)); } }
/** * Adjusts the column width to fit the contents.<p> * * This process can be relatively slow on large sheets, so this should * normally only be called once per column, at the end of your * processing. * * @param column the column index */ @Override public void autoSizeColumn(int column) { autoSizeColumn(column, false); }
/** * used internally to refresh the "first row" when the first row is removed. */ private int findFirstRow(int firstrow) { int rownum = firstrow + 1; HSSFRow r = getRow(rownum); while (r == null && rownum <= getLastRowNum()) { r = getRow(++rownum); } if (rownum > getLastRowNum()) return 0; return rownum; }
/** * Remove a row by its index * @param sheet a Excel sheet * @param rowIndex a 0 based index of removing row */ public static void removeRow(HSSFSheet sheet, int rowIndex) { int lastRowNum=sheet.getLastRowNum(); if(rowIndex>=0&&rowIndex<lastRowNum){ sheet.shiftRows(rowIndex+1,lastRowNum, -1); } if(rowIndex==lastRowNum){ HSSFRow removingRow=sheet.getRow(rowIndex); if(removingRow!=null){ sheet.removeRow(removingRow); } } }
private float getRowHeightInPoints(HSSFSheet sheet, int rowNum) { HSSFRow row = sheet.getRow(rowNum); if (row == null) { return sheet.getDefaultRowHeightInPoints(); } return row.getHeightInPoints(); }
public void setSuccessMessageInExcellFile(File uploadFile, HttpServletResponse response) { try { FileInputStream fileInputStream = new FileInputStream(uploadFile); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet excelSheet = workbook.getSheetAt(0); HSSFCell cell = excelSheet.createRow(0).createCell(0); cell.setCellValue("Imported"); fileInputStream.close(); FileOutputStream out = new FileOutputStream(uploadFile); workbook.write(out); out.close(); } catch(Exception ex) { ex.getCause().printStackTrace(); } }
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));
try(FileInputStream fileInputStream = new FileInputStream(uploadFile); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); FileOutputStream out = new FileOutputStream(uploadFile)) { HSSFSheet excelSheet = workbook.getSheetAt(0); HSSFCell cell = excelSheet.createRow(0).createCell(0); cell.setCellValue("Imported"); workbook.write(out); } catch(Exception ex) { ex.getCause().printStackTrace(); }
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{ FileOutputStream out = new FileOutputStream ("dateFormat.xls"); HSSFWorkbook hssfworkbook = new HSSFWorkbook(); HSSFSheet sheet = hssfworkbook.createSheet ("new sheet"); HSSFCellStyle cs = hssfworkbook.createCellStyle(); HSSFDataFormat df = hssfworkbook. createDataFormat(); cs.setDataFormat(df.getFormat("#,##0.0")); HSSFRow row = sheet.createRow((short)0); HSSFCell cell = row.createCell((short)0); cell.setCellValue(11111.1); cell.setCellStyle(cs); hssfworkbook.write(out); out.close(); }catch(Exception e){} }
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();
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(3).setCellValue("sankumarsingh@gmail.com"); FileOutputStream fileOut = new FileOutputStream(filename); workbook.write(fileOut); fileOut.close(); System.out.println("Your excel file has been generated!");
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; row = sheet.getRow(r); if(row != null) { for(int c = 0; c < cols; c++) { cell = row.getCell((short)c); if(cell != null) {
HSSFWorkbook myWorkBook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream("E:/Project/SpringHibernet/MultiplexTicketBookingNew/web/excelSheets/Country.xlsx"))); HSSFSheet mySheet = myWorkBook.getSheetAt(0); Iterator rowIter = mySheet.rowIterator(); System.out.println(mySheet.getRow(1).getCell(0));
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); HSSFFont font = wb.createFont(); font.setColor(HSSFColor.RED.index); 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(); HSSFCellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.BROWN.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFSheet sheet = workBook.createSheet(); int numRow = 20; int numCol = 20; for (int rowIndex = 0; rowIndex < numRow; rowIndex++) { HSSFRow row = sheet.createRow(rowIndex); for (int colIndex = 0; colIndex < numCol; colIndex++) { HSSFCell cell = row.createCell(colIndex); cell.setCellStyle(brownBG); } } FileOutputStream fos = new FileOutputStream("test.xls"); workBook.write(fos); fos.flush(); fos.close(); System.out.println("done");
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();
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); } }