Example usage for org.apache.poi.hssf.usermodel HSSFRow createCell

List of usage examples for org.apache.poi.hssf.usermodel HSSFRow createCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFRow
  6. createCell

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFRow createCell.

Prototype

@Override
public HSSFCell createCell(int column)

Source Link

Document

Use this to create new cells within the row and return it.

Usage

From source file:com.ssic.education.provider.controller.WaresController.java

@RequestMapping(value = "/excel")
@ResponseBody//from  ww w  .ja v a2 s.c om
public ModelAndView exportExcel(ProWaresDto proWaresDto, HttpServletRequest request,
        HttpServletResponse response) {
    SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME);
    if (info == null) {
        return null;
    }
    proWaresDto.setSupplierId(info.getSupplierId());
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy.M.d");
    Date date = new Date();
    String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
    HSSFSheet sheet;
    HSSFCell cell;
    response.setContentType("application/octet-stream");
    response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
    Workbook workbook = new HSSFWorkbook();
    sheet = (HSSFSheet) workbook.createSheet("");
    try {
        List<String> titles = new ArrayList<String>();
        titles.add("??");
        titles.add("???");
        titles.add("");
        titles.add("?");
        titles.add("?");
        //         titles.add("??");
        //         titles.add("???");
        //         titles.add("??");
        titles.add("?");
        titles.add("???");
        titles.add("");
        int len = titles.size();
        HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle(); // ?
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont headerFont = (HSSFFont) workbook.createFont(); // 
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerFont.setFontHeightInPoints((short) 11);
        headerStyle.setFont(headerFont);
        short width = 20, height = 25 * 20;
        sheet.setDefaultColumnWidth(width);
        HSSFRow sheetRow = sheet.createRow(0);
        for (int i = 0; i < len; i++) { // 
            String title = titles.get(i);
            cell = sheetRow.createCell(i);
            cell.setCellStyle(headerStyle);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(title);
        }
        sheet.getRow(0).setHeight(height);
        HSSFCellStyle contentStyle = (HSSFCellStyle) workbook.createCellStyle(); // ?
        contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        List<ProWaresDto> expList = waresService.findAllWares(proWaresDto, null);
        List<PageData> varList = new ArrayList<PageData>();
        if (!CollectionUtils.isEmpty(expList)) {
            for (int i = 0; i < expList.size(); i++) {
                PageData vpd = new PageData();
                vpd.put("var1", expList.get(i).getWaresName());
                vpd.put("var2", expList.get(i).getAmountUnit());
                vpd.put("var3", expList.get(i).getSpec());
                vpd.put("var4", ProductClass.getName(expList.get(i).getWaresType()));
                vpd.put("var5", expList.get(i).getManufacturer());
                //               vpd.put("var5", expList.get(i).getEnName());
                //               vpd.put("var6", expList.get(i).getBarCode());
                //               vpd.put("var7", expList.get(i).getCustomCode());
                vpd.put("var6", expList.get(i).getShelfLife());
                vpd.put("var7", expList.get(i).getUnit());
                vpd.put("var8", expList.get(i).getPlace());
                varList.add(vpd);
            }
        }
        for (int i = 0; i < varList.size(); i++) {
            HSSFRow row = sheet.createRow(i + 1);
            PageData vpd = varList.get(i);
            for (int j = 0; j < len; j++) {
                String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : "";
                cell = row.createCell(j);
                HSSFCellStyle cellStyle2 = (HSSFCellStyle) workbook.createCellStyle();
                HSSFDataFormat format = (HSSFDataFormat) workbook.createDataFormat();
                cellStyle2.setDataFormat(format.getFormat("@"));
                cell.setCellStyle(cellStyle2);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(varstr);

            }

        }
        OutputStream os = response.getOutputStream();
        workbook.write(os);
        os.flush();
        os.close();
    } catch (Exception e) {

    }
    return null;
}

From source file:com.sx.finance.OldYahooFinanceJFrame.java

public void writeExcelFile(ArrayList<List<Object>> listDataRaw) {
    // webbookExcel
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet
    HSSFSheet sheet = wb.createSheet("Data");
    // sheet0,poiExcelshort
    HSSFRow row = sheet.createRow((int) 0);
    //  /*from   w  w  w  .  ja va2s  . co m*/
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 

    for (int i = 0; i < title.length; i++) {
        HSSFCell cell = row.createCell(i);
        cell.setCellValue(title[i]);
        cell.setCellStyle(style);
    }

    for (int i = 0; i < listDataRaw.size(); i++) {
        row = sheet.createRow(i + 1);
        for (int j = 0; j < listDataRaw.get(i).size(); j++) {
            Object cellData = listDataRaw.get(i).get(j);
            if (cellData instanceof Long) {
                row.createCell(j).setCellValue(((Long) cellData).longValue());
            } else if (cellData instanceof Double) {
                row.createCell(j).setCellValue(((Double) cellData).doubleValue());
            } else if (cellData instanceof Date) {
                row.createCell(j).setCellValue((Date) cellData);
            } else if (cellData instanceof Integer) {
                row.createCell(j).setCellValue(((Integer) cellData).intValue());
            } else {
                row.createCell(j).setCellValue((String) cellData);
            }
        }
    }
    try {
        FileOutputStream fout = new FileOutputStream("D:/work/myprojects/yahooFinance/output/data.xls");
        wb.write(fout);
        fout.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:com.syncnapsis.utils.data.ExcelHelper.java

License:Open Source License

public static void main(String[] args) throws Exception {
    String fileName = "testdata.xls";
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("src/main/resources/" + fileName));

    HSSFSheet sheet = workbook.getSheet("Benutzer");
    HSSFRow row = sheet.getRow(0);
    Cell cell;// w w w  .  j a  va  2s .  c  o  m

    for (int i = 2; i < 256; i++) {
        try {
            cell = row.getCell(i);
            if (cell == null)
                cell = row.createCell(i);
            cell.setCellFormula("IF(ISBLANK(Benutzer!A" + (i - 1) + "),\"\",Benutzer!A" + (i - 1) + ")");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    workbook.write(new FileOutputStream("src/main/resources/new.xls"));
}

From source file:com.taobao.itest.dbunit.dataset.excel.XlsDataSetWriter.java

License:Open Source License

/**
 * Write the specified dataset to the specified Excel document.
 *//*from  w  w  w.  j av  a2  s .com*/
public void write(IDataSet dataSet, OutputStream out) throws IOException, DataSetException {
    logger.debug("write(dataSet={}, out={}) - start", dataSet, out);

    HSSFWorkbook workbook = new HSSFWorkbook();

    this.dateCellStyle = createDateCellStyle(workbook);

    int index = 0;
    ITableIterator iterator = dataSet.iterator();
    while (iterator.next()) {
        // create the table i.e. sheet
        ITable table = iterator.getTable();
        ITableMetaData metaData = table.getTableMetaData();
        HSSFSheet sheet = workbook.createSheet(metaData.getTableName());

        // write table metadata i.e. first row in sheet
        workbook.setSheetName(index, metaData.getTableName());

        HSSFRow headerRow = sheet.createRow(0);
        Column[] columns = metaData.getColumns();
        for (int j = 0; j < columns.length; j++) {
            Column column = columns[j];
            HSSFCell cell = headerRow.createCell(j);
            cell.setCellValue(new HSSFRichTextString(column.getColumnName()));
        }

        // write table data
        for (int j = 0; j < table.getRowCount(); j++) {
            HSSFRow row = sheet.createRow(j + 1);
            for (int k = 0; k < columns.length; k++) {
                Column column = columns[k];
                Object value = table.getValue(j, column.getColumnName());
                if (value != null) {
                    HSSFCell cell = row.createCell(k);
                    if (value instanceof Date) {
                        setDateCell(cell, (Date) value, workbook);
                    } else if (value instanceof BigDecimal) {
                        setNumericCell(cell, (BigDecimal) value, workbook);
                    } else if (value instanceof Long) {
                        setDateCell(cell, new Date(((Long) value).longValue()), workbook);
                    } else {
                        cell.setCellValue(new HSSFRichTextString(DataType.asString(value)));
                    }
                }
            }
        }

        index++;
    }

    // write xls document
    workbook.write(out);
    out.flush();
}

From source file:com.technoetic.xplanner.importer.spreadsheet.SpreadsheetStoryWriter.java

License:Open Source License

/**
 * Sets the cell value.//from www  .j  a v a 2s.  c o m
 *
 * @param row
 *            the row
 * @param col
 *            the col
 * @param date
 *            the date
 */
private void setCellValue(final HSSFRow row, final int col, final Date date) {
    final HSSFCell cell = row.createCell((short) col);
    cell.setCellValue(date);
}

From source file:com.technoetic.xplanner.importer.spreadsheet.SpreadsheetStoryWriter.java

License:Open Source License

/**
 * Sets the cell value.//w ww  .  j  a  v a 2s.co m
 *
 * @param row
 *            the row
 * @param col
 *            the col
 * @param value
 *            the value
 */
private void setCellValue(final HSSFRow row, final int col, final int value) {
    final HSSFCell cell = row.createCell((short) col);
    cell.setCellValue(value);
}

From source file:com.technoetic.xplanner.importer.spreadsheet.SpreadsheetStoryWriter.java

License:Open Source License

/**
 * Sets the cell value./*www.  jav a  2  s  .  c o  m*/
 *
 * @param row
 *            the row
 * @param col
 *            the col
 * @param value
 *            the value
 */
private void setCellValue(final HSSFRow row, final int col, final double value) {
    final HSSFCell cell = row.createCell((short) col);
    cell.setCellValue(value);
}

From source file:com.technoetic.xplanner.importer.spreadsheet.SpreadsheetStoryWriter.java

License:Open Source License

/**
 * Sets the cell value./*from   w  w w  .  ja  va  2 s.  co  m*/
 *
 * @param row
 *            the row
 * @param col
 *            the col
 * @param value
 *            the value
 */
private void setCellValue(final HSSFRow row, final int col, final String value) {
    final HSSFCell cell = row.createCell((short) col);
    cell.setCellValue(value);
}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

public void addRowWithFormat(String sheetName, int row, String[] value, String[] format) {
    try {/*from   www .  j  av a  2  s.  co  m*/
        String formatV = "";
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet worksheet = workbook.getSheet(sheetName);
        if (worksheet == null) {
            worksheet = workbook.createSheet(sheetName);
        }
        // index from 0,0... cell A1 is cell(0,0)
        HSSFRow row1 = worksheet.createRow(row);

        for (int col = 0; col < value.length; col++) {
            HSSFCell cellA1 = row1.createCell(col);
            cellA1.setCellValue(value[col]);
            if (format.length >= col) {
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(format[col]));
                //cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
                //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                cellA1.setCellStyle(cellStyle);
            }
        }
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    }

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

public void addRow(String sheetName, int row, String[] value) {
    try {/*from   ww  w . ja v  a  2 s .  com*/
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet worksheet = workbook.getSheet(sheetName);
        if (worksheet == null) {
            worksheet = workbook.createSheet(sheetName);
        }
        // index from 0,0... cell A1 is cell(0,0)
        HSSFRow row1 = worksheet.createRow(row);

        for (int col = 0; col < value.length; col++) {
            HSSFCell cellA1 = row1.createCell(col);
            cellA1.setCellValue(value[col]);
        }
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    }

}