List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell
@Override public HSSFCell getCell(int cellnum)
From source file:cn.fql.template.poi.MergeInvoiceSheet.java
License:Open Source License
private static void writeEffortTotal() { HSSFSheet templateSheet = templateWbk.getSheet("Effort Total"); String lastProjectName = null; Date startDateValue = null;//from w ww . j a v a 2 s. c o m Date endDateValue = null; double totalDaysValue = 0; for (int i = 4; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } } else { HSSFCell projectNameCell = row.getCell((short) 1); HSSFCell startDateCell = row.getCell((short) 2); HSSFCell endDateCell = row.getCell((short) 3); HSSFCell totalDaysCell = row.getCell((short) 4); if (totalDaysCell == null) { break; } String newProjectName = projectNameCell.getStringCellValue(); Date _startDateValue = startDateCell.getDateCellValue(); Date _endDateValue = endDateCell.getDateCellValue(); double _totalDaysValue = totalDaysCell.getNumericCellValue(); if (lastProjectName == null) { lastProjectName = newProjectName; startDateValue = _startDateValue; endDateValue = _endDateValue; totalDaysValue = _totalDaysValue; } else { if (newProjectName.equals(lastProjectName)) { totalDaysValue += _totalDaysValue; templateSheet.getRow(i - 1).getCell((short) 4).setCellValue(totalDaysValue); if (startDateValue.compareTo(_startDateValue) > 0) { startDateValue = _startDateValue; templateSheet.getRow(i - 1).getCell((short) 2).setCellValue(startDateValue); } if (endDateValue.compareTo(_endDateValue) < 0) { endDateValue = _endDateValue; templateSheet.getRow(i - 1).getCell((short) 3).setCellValue(endDateValue); } templateSheet.removeRow(row); templateSheet.shiftRows(i + 1, 109, -1); i--; } else { lastProjectName = newProjectName; startDateValue = _startDateValue; endDateValue = _endDateValue; totalDaysValue = _totalDaysValue; } } } } }
From source file:cn.fql.template.poi.MergeInvoiceSheet.java
License:Open Source License
public static void writeEffortSummary() { HSSFSheet templateSheet = templateWbk.getSheet("Effort Summary"); String lastCellValue = null;//from w ww . ja v a2 s.c om double days = 0; int count = 1; for (int i = 6; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } } else { HSSFCell cell = row.getCell((short) 2); HSSFCell dayCell = row.getCell((short) 6); if (cell != null) { if (lastCellValue == null) { lastCellValue = cell.getStringCellValue(); count = 1; String dayStr = dayCell.getStringCellValue().trim(); days = new BigDecimal(dayStr).doubleValue(); templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count); templateSheet.getRow(i).getCell((short) 4).setCellValue(days); } else { String newCellValue = cell.getStringCellValue(); if ("Project Name".equals(newCellValue)) { continue; } if (newCellValue != null) { if (newCellValue.equals(lastCellValue)) { Region region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) 2); region.setColumnTo((short) 2); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) 3).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) 3); region.setColumnTo((short) 3); templateSheet.addMergedRegion(region); count++; templateSheet.getRow(i - count + 1).getCell((short) 3).setCellValue((double) count); region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) 4); region.setColumnTo((short) 4); templateSheet.addMergedRegion(region); String dayStr = dayCell.getStringCellValue().trim(); days += new BigDecimal(dayStr).doubleValue(); templateSheet.getRow(i - count + 1).getCell((short) 4).setCellValue(days); } else { lastCellValue = newCellValue; count = 1; templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count); String dayStr = dayCell.getStringCellValue().trim(); days = new BigDecimal(dayStr).doubleValue(); templateSheet.getRow(i).getCell((short) 4).setCellValue(days); } } else { lastCellValue = newCellValue; count = 1; templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count); String dayStr = dayCell.getStringCellValue().trim(); days = new BigDecimal(dayStr).doubleValue(); templateSheet.getRow(i).getCell((short) 4).setCellValue(days); } } } } } }
From source file:cn.fql.template.poi.MergeInvoiceSheet.java
License:Open Source License
public static void writeEffort(int index, String sheetName) { HSSFSheet templateSheet = templateWbk.getSheet(sheetName); String lastCellValue = null;/*from w ww .j av a2 s . co m*/ for (int i = 0; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } } else { HSSFCell cell = row.getCell((short) index); if (cell != null) { if (lastCellValue == null && cell.getStringCellValue() != null && !cell.getStringCellValue().trim().equals("")) { lastCellValue = cell.getStringCellValue(); } else { String newCellValue = cell.getStringCellValue(); if (newCellValue != null && !newCellValue.trim().equals("")) { if (newCellValue.equals(lastCellValue)) { Region region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) index); region.setColumnTo((short) index); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) index).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); } else { lastCellValue = newCellValue; } } else { lastCellValue = newCellValue; } } } } } }
From source file:cn.fql.template.poi.PoiTest.java
License:Open Source License
public static void writeEffort(int index, String sheetName) { HSSFSheet templateSheet = templateWbk.getSheet(sheetName); String lastCellValue = null;// w w w . j av a 2 s .c o m for (int i = 1118; i < 1232; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } } else { HSSFCell cell = row.getCell((short) index); if (lastCellValue == null) { lastCellValue = cell.getRichStringCellValue().getString(); } else { String newCellValue = cell.getRichStringCellValue().getString(); if (cell != null) { if (lastCellValue.equals(newCellValue)) { Region region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) index); region.setColumnTo((short) index); templateSheet.addMergedRegion(region); } else { lastCellValue = newCellValue; } } } } } }
From source file:cn.trymore.core.util.excel.PoiExcelParser.java
License:Open Source License
public Object getCell(int rowIdx, int columnIdx) { HSSFRow row = this.sheet.getRow(rowIdx); HSSFCell cell = null;/* w w w .j a v a2 s . c o m*/ if (row != null) { cell = row.getCell(columnIdx); if (isCellInConditionalFormat(this.cfms, cell) == null) { } } return cell; }
From source file:cn.vlabs.umt.common.xls.UserXLSParser.java
License:Apache License
private UserVO parseLine(HSSFSheet sheet, int row) { // ??Sheet/*from w w w . j av a2 s .c o m*/ HSSFRow rowline = sheet.getRow(row); // ?? UserVO u = new UserVO(); u.setUmtId(readCellValue(rowline.getCell((short) 0))); u.setTrueName(readCellValue(rowline.getCell((short) 1))); u.setPassword(readCellValue(rowline.getCell((short) 2))); u.setCstnetId(readCellValue(rowline.getCell((short) 3))); if (StringUtils.isEmpty(u.getCstnetId())) { u.setCstnetId(u.getUmtId()); } return u; }
From source file:cn.zlg.excel.newmodel.parser.ModelParser.java
public List<Model> parse(String file, int[] sheets) { final List<Model> result = New.arraylist(); ExcelReader.readXLS(file, sheets, new ExcelReadCallback() { @Override/* w w w . j ava2 s.c o m*/ public void readRow(int sheet, int rowNum, Object o) { if (o == null) { return; } HSSFRow row = (HSSFRow) o; Model m = new Model(); for (Entry<Integer, String> e : columnAttrsMap.entrySet()) { if (row.getCell(e.getKey()) != null) { m.set(e.getValue(), row.getCell(e.getKey()).getStringCellValue()); } } result.add(m); } }); return result; }
From source file:com.aan.girsang.client.ui.master.barang.BarangPanel.java
private void exportExcel(List<Barang> dataList) throws IOException { if (dataList != null && !dataList.isEmpty()) { HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet(); HSSFSheet worksheet = workBook.createSheet("Sheet 0"); // Nama Field Row judul = sheet.createRow((short) 0); Cell cell = judul.createCell((short) 0); cell.setCellValue("This is a test of merging"); HSSFRow headingRow = sheet.createRow((short) 2); headingRow.createCell((short) 0).setCellValue("ID"); headingRow.createCell((short) 1).setCellValue("BARCODE 1"); headingRow.createCell((short) 2).setCellValue("BARCODE 2"); headingRow.createCell((short) 3).setCellValue("NAMA BARANG"); headingRow.createCell((short) 4).setCellValue("GOLONGAN"); headingRow.createCell((short) 5).setCellValue("SAT. JUAL"); headingRow.createCell((short) 6).setCellValue("ST. TOKO"); headingRow.createCell((short) 7).setCellValue("ST. GUDANG"); headingRow.createCell((short) 8).setCellValue("SAT. BELI"); headingRow.createCell((short) 9).setCellValue("ISI PEM."); headingRow.createCell((short) 10).setCellValue("HRG PEM."); headingRow.createCell((short) 11).setCellValue("HRG NORMAL"); headingRow.createCell((short) 12).setCellValue("HRG MEMBER"); headingRow.createCell((short) 13).setCellValue("JUAL"); int panjang = headingRow.getLastCellNum() - 1; short rowNo = 3; sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) panjang //last column (0-based) ));/* w w w . j av a2 s . com*/ CellStyle styleData = workBook.createCellStyle(); styleData.setBorderBottom(CellStyle.BORDER_THIN); styleData.setBorderRight(CellStyle.BORDER_THIN); styleData.setBorderLeft(CellStyle.BORDER_THIN); for (Barang b : dataList) { HSSFRow row = sheet.createRow(rowNo); String jual; if (b.getJual() == true) { jual = "Jual"; } else { jual = "Tidak"; } row.createCell((short) 0).setCellValue(b.getPlu()); row.createCell((short) 1).setCellValue(b.getBarcode1()); row.createCell((short) 2).setCellValue(b.getBarcode2()); row.createCell((short) 3).setCellValue(b.getNamaBarang()); row.createCell((short) 4).setCellValue(b.getGolonganBarang().getGolonganBarang()); row.createCell((short) 5).setCellValue(b.getSatuan()); row.createCell((short) 6).setCellValue(b.getStokToko()); row.createCell((short) 7).setCellValue(b.getStokGudang()); row.createCell((short) 8).setCellValue(b.getSatuanPembelian()); row.createCell((short) 9).setCellValue(b.getIsiPembelian()); row.createCell((short) 10).setCellValue(TextComponentUtils.formatNumber(b.getHargaBeli())); row.createCell((short) 11).setCellValue(TextComponentUtils.formatNumber(b.getHargaNormal())); row.createCell((short) 12).setCellValue(TextComponentUtils.formatNumber(b.getHargaMember())); row.createCell((short) 13).setCellValue(jual); for (int i = 0; i <= 13; i++) { row.getCell((short) i).setCellStyle(styleData); } rowNo++; } for (int i = 0; i <= 13; i++) { sheet.autoSizeColumn(i); } Font font = workBook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); //style judul CellStyle styleTitle = workBook.createCellStyle(); styleTitle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION); styleTitle.setFont(font); judul.getCell(0).setCellStyle(styleTitle); //judul field CellStyle styleHeading = workBook.createCellStyle(); styleHeading.setFont(font); styleHeading.setAlignment(CellStyle.ALIGN_CENTER_SELECTION); styleHeading.setBorderBottom(CellStyle.BORDER_THIN); styleHeading.setBorderTop(CellStyle.BORDER_THIN); styleHeading.setBorderRight(CellStyle.BORDER_THIN); styleHeading.setBorderLeft(CellStyle.BORDER_THIN); for (int i = 0; i < headingRow.getLastCellNum(); i++) {//For each cell in the row headingRow.getCell(i).setCellStyle(styleHeading);//Set the style } String file = "D:/Student_detais.xls"; try { try (FileOutputStream fos = new FileOutputStream(file)) { workBook.write(fos); } JOptionPane.showMessageDialog(null, "Sukses"); } catch (FileNotFoundException e) { System.out.println("Invalid directory or file not found"); } catch (IOException e) { System.out.println("Error occurred while writting excel file to directory"); } } }
From source file:com.abacus.reports.ExcelBuilder.java
@Override protected void buildExcelDocument(Map<String, Object> map, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // get data model which is passed by the Spring container List headerlist = (List) map.get("header"); List<Object[]> data = (List) map.get("data"); String reportname = String.valueOf(map.get("report_name")); // create a new Excel sheet HSSFSheet sheet = workbook.createSheet(reportname); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment; filename=" + reportname + ".xls"); OutputStream outStream = response.getOutputStream(); sheet.setDefaultColumnWidth(30);//w w w . ja v a 2s . c om // create style for header cells CellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); HSSFPalette palette = workbook.getCustomPalette(); HSSFColor color = palette.findSimilarColor(152, 35, 17); short paindex = color.getIndex(); font.setFontName("Trebuchet MS"); style.setFillForegroundColor(paindex); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.WHITE.index); style.setFont(font); // create header row HSSFRow header = sheet.createRow(0); int row = 0; for (Object headerlist1 : headerlist) { header.createCell(row).setCellValue(String.valueOf(headerlist1)); header.getCell(row).setCellStyle(style); row++; } CellStyle style2 = workbook.createCellStyle(); HSSFFont font2 = workbook.createFont(); font2.setFontName("Trebuchet MS"); style2.setFont(font2); System.out.println("data.size(): " + data.size()); int rownum = 1; // create data rows for (int rowCount = 0; rowCount < data.size(); rowCount++) { HSSFRow aRow = sheet.createRow(rownum); Object[] value = data.get(rowCount); int col = 0; for (Object value1 : value) { HSSFCell cell = aRow.createCell(col++); cell.setCellStyle(style2); if (value1 instanceof java.lang.String) cell.setCellValue(String.valueOf(value1)); if (value1 instanceof java.lang.Integer) cell.setCellValue(Integer.parseInt(String.valueOf(value1))); if (value1 instanceof java.lang.Boolean) cell.setCellValue(Integer.parseInt(String.valueOf(value1))); if (value1 instanceof java.lang.Double) cell.setCellValue(Double.parseDouble(String.valueOf(value1))); if (value1 instanceof java.lang.Float) cell.setCellValue(Float.parseFloat(String.valueOf(value1))); } rownum++; } workbook.write(outStream); outStream.close(); }
From source file:com.accounting.accountMBean.DifferentAccReports.java
public void postProcessXLS(Object document) { String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate1"); ndc = new NepaliDateConverter(); HSSFWorkbook wb = (HSSFWorkbook) document; HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFCellStyle headerCellStyle1 = wb.createCellStyle(); HSSFCellStyle headerCellStyle2 = wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont1 = wb.createFont(); headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING); headerFont1.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle1.setFont(headerFont); headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT); HSSFSheet sheet = wb.getSheetAt(0);/*from w ww . j av a 2 s . co m*/ int noOfColumns = sheet.getRow(0).getLastCellNum(); Font headerFont3 = wb.createFont(); headerFont3.setBoldweight(Font.U_SINGLE); headerFont3.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle2.setFont(headerFont1); headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT); for (int i = 0; i < noOfColumns; i++) { sheet.autoSizeColumn(i); } sheet.shiftRows(0, sheet.getLastRowNum(), 4); HSSFRow firstRow = sheet.createRow(1); firstRow.createCell(0).setCellValue(PageName); firstRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow secondRow = sheet.createRow(0); secondRow.createCell(0).setCellValue(getLoggedInOffice().getName()); secondRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow thirdRow = sheet.createRow(3); String generatedDate = ndc.convertToNepaliDate(new Date()); SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a"); String time = sdf.format(new Date()); thirdRow.createCell(0) .setCellValue("Generated on:" + generatedDate + " " + time + " by:" + getLoggedInUser().getName()); thirdRow.getCell(0).setCellStyle(headerCellStyle2); HSSFRow fourthRow = sheet.createRow(2); fourthRow.createCell(0).setCellValue("To Date: " + startDateString); fourthRow.getCell(0).setCellStyle(headerCellStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 4)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 4)); // HSSFRow lastRow; // double totalDr = 0; // for (Row row : sheet) { // if (row.getRowNum() > 4) { // String cost = row.getCell(3).getStringCellValue(); // if (cost != null && !cost.isEmpty()) { // row.getCell(3).setCellType(HSSFCell.CELL_TYPE_BLANK); // row.getCell(3).setCellType(HSSFCell.CELL_TYPE_NUMERIC); // row.getCell(3).setCellValue(Double.parseDouble(cost.replace(",", ""))); // totalDr += Double.parseDouble(cost.replace(",", "")); // } // } // } }