Examples of org.apache.poi.hssf.usermodel.HSSFSheet

org.apache.poi.hssf.usermodel.HSSFSheet
High level representation of a worksheet. @author Andrew C. Oliver (acoliver at apache dot org) @author Glen Stampoultzis (glens at apache.org) @author Libin Roman (romal at vistaportal.com) @author Shawn Laubach (slaubach at apache dot org) (Just a little) @author Jean-Pierre Paris (jean-pierre.paris at m4x dot org) (Just a little, too) @author Yegor Kozlov (yegor at apache.org) (Autosizing columns)

     * @param sheetName 入力データのシート名
     * @return 生成したシート
     * @throws SQLException データベースから入力データを取得するのに失敗した場合
     */
    private HSSFSheet createInputDataSheet(String sheetName) throws SQLException {
        HSSFSheet sheet = workbook.createSheet(sheetName);


        // カラム名を設定
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < columnInfos.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(columnInfos[i].getColumnName());
            cell.setCellStyle(titleStyle);
        }


        // DBのデータを設定
        PreparedStatement ps = null;
        ResultSet rs = null;


        String sql =
            "SELECT * FROM "
            + databaseName + "." + tableName
            + " limit 0, " + Constants.MAX_ROWS;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                row = sheet.createRow(row.getRowNum() + 1);
                for (int i = 0; i < columnInfos.length; i++) {
                    ColumnInfo info = columnInfos[i];
                    HSSFCell cell = row.createCell(i);
                    cell.setCellStyle(commonStyle);
                    switch (info.getDataType()) {
                    case CHAR:
                    case VARCHAR:
                        String str = rs.getString(info.getColumnName());
                        if (!rs.wasNull()) {
                            cell.setCellValue(str);
                        }
                        break;
                    case DATE:
                        Date date = rs.getDate(info.getColumnName());
                        if (!rs.wasNull()) {
                            cell.setCellValue(new java.util.Date(date.getTime()));
                            cell.setCellStyle(dateStyle);
                        }
                        break;
                    case DATETIME:
                    case TIMESTAMP:
                        Timestamp ts = rs.getTimestamp(info.getColumnName());
                        if (!rs.wasNull()) {
                            cell.setCellValue(new java.util.Date(ts.getTime()));
                            cell.setCellStyle(dateTimeStyle);
                        }
                        break;
                    case DECIMAL:
                        BigDecimal decimal = rs.getBigDecimal(info.getColumnName());
                        if (!rs.wasNull()) {
                            cell.setCellValue(decimal.toPlainString());
                        }
                        break;
                    case TINY_INT:
                    case SMALL_INT:
                    case INT:
                    case LONG:
                        long value = rs.getLong(info.getColumnName());
                        if (!rs.wasNull()) {
                            cell.setCellValue(Long.toString(value));
                        }
                        break;
                    default:
                        assert false;
                        break;
                    }
                }
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        // カラム幅の調整
        for (int i = 0; i < columnInfos.length; i++) {
            sheet.autoSizeColumn(i);
        }
        return sheet;
    }

View Full Code Here

    
    //Delete and recreate
    deleteCreateWorksheet(wb,logWorkSheetName);
    
    //Get our sheet
    HSSFSheet sheet= wb.getSheet(logWorkSheetName);
    
    //Start at A1, now work our way down , adding the logging output
    
    short currentRow =0;
    
    
     for (String s : loggedItems){
            
            // Create a row and put some cells in it. Rows are 0 based.
            HSSFRow row = sheet.createRow(currentRow);
            
            // Create a cell and put a value in it.
            HSSFCell cell = row.createCell((short)0);
            cell.setCellValue(s);

View Full Code Here

      wb.createSheet(worksheetName);
    } catch (IllegalArgumentException iae){
      
      
      //worksheet already exists , so remove it
      HSSFSheet sheet = wb.getSheet(worksheetName);
      
      int sheetIndex= wb.getSheetIndex(sheet); 
      
      wb.removeSheetAt(sheetIndex);

View Full Code Here


      // A Range that we will put the new cells into
      Range redRange = new Range(aNamedRage.getNameName());


      for (int thisCellinRange = 0; thisCellinRange < crefs.length; thisCellinRange++) {
        HSSFSheet sheet = wb.getSheet(crefs[thisCellinRange]
            .getSheetName());
        HSSFRow r = sheet.getRow(crefs[thisCellinRange].getRow());


        HSSFCell thisExcelCell = null;
        if (r != null) {
          thisExcelCell = r.getCell(crefs[thisCellinRange].getCol());
          // extract the cell contents based on cell type etc.

View Full Code Here

      // retrieve the cell at the named range and test its contents
      AreaReference aref = new AreaReference(aNamedCell.getReference());
      CellReference[] crefs = aref.getAllReferencedCells();


      for (int thisCellinRange = 0; thisCellinRange < crefs.length; thisCellinRange++) {
        HSSFSheet sheet = wb.getSheet(crefs[thisCellinRange]
            .getSheetName());


        HSSFRow r = sheet.getRow(crefs[thisCellinRange].getRow());


        // Get the cell that is referred to
        HSSFCell excelCell = null;
        if (r != null) {
          excelCell = r.getCell(crefs[thisCellinRange].getCol());

View Full Code Here

        return fileName;
    }


    public void performExport(DataExportModel model, DataExportInstructions instructions, ConnectionHandler connectionHandler) throws DataExportException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(model.getTableName());


        if (instructions.createHeader()) {
            HSSFRow headerRow = sheet.createRow(0);


            for (int columnIndex = 0; columnIndex < model.getColumnCount(); columnIndex++){
                String columnName = model.getColumnName(columnIndex);


                HSSFCell cell = headerRow.createCell(columnIndex);
                cell.setCellValue(columnName);


                HSSFCellStyle cellStyle = workbook.createCellStyle();
                HSSFFont tableHeadingFont = workbook.createFont();
                tableHeadingFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                cellStyle.setFont(tableHeadingFont);
                cell.setCellStyle(cellStyle);
            }
        }


        CellStyleCache cellStyleCache = new CellStyleCache(workbook, model.getProject());


        for (short rowIndex = 0; rowIndex < model.getRowCount(); rowIndex++) {
            HSSFRow row = sheet.createRow(rowIndex + 1);
            for (int columnIndex = 0; columnIndex < model.getColumnCount(); columnIndex++){
                HSSFCell cell = row.createCell(columnIndex);
                Object value = model.getValue(rowIndex, columnIndex);
                if (value != null) {
                    if (value instanceof Number) {
                        Number number = (Number) value;
                        double doubleValue = number.doubleValue();
                        cell.setCellValue(doubleValue);
                        cell.setCellStyle(
                                doubleValue % 1 == 0 ?
                                        cellStyleCache.getIntegerStyle() :
                                        cellStyleCache.getNumberStyle());


                    } else if (value instanceof Date) {
                        Date date = (Date) value;
                        boolean hasTime = hasTimeComponent(date);
                        cell.setCellValue(date);
                        cell.setCellStyle(hasTime ?
                                cellStyleCache.getDatetimeStyle() :
                                cellStyleCache.getDateStyle());
                    } else {
                        cell.setCellValue(value.toString());
                    }
                }
            }
        }


        for (int columnIndex=0; columnIndex < model.getColumnCount(); columnIndex++){
            sheet.autoSizeColumn(columnIndex);
        }


        File file = instructions.getFile();
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(file);

View Full Code Here

                    try {
                        POIFSFileSystem fs = new POIFSFileSystem(in);
                        HSSFWorkbook workbook = new HSSFWorkbook(fs);


                        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                            HSSFSheet sheet = workbook.getSheetAt(i);


                            Iterator rows = sheet.rowIterator();
                            while (rows.hasNext()) {
                                HSSFRow row = (HSSFRow) rows.next();


                                Iterator cells = row.cellIterator();
                                while (cells.hasNext()) {

View Full Code Here

      throw new ProTransException("Katalog ikke satt");
    }
    String infoString = "Genererer excel-fil...rad ";
    HSSFWorkbook wb = new HSSFWorkbook();


    HSSFSheet sheet;


    sheet = getSheet(wb, 10, new int[] { 7000, 1400, 1400, 1200, 1500,
        1500, 1900, 1300, 1000, 3000 });


    int currentRow = 0;
    HSSFRow row;


    CellStyle cellStyle = new CellStyle(wb);


    Set<Transport> transports = tableModels.keySet();
    List<Transport> sortedTransport = new ArrayList<Transport>(transports);
    Collections.sort(sortedTransport, new TransportComparator());
    TableModel tableModel;
    String dateString = "";


    for (Transport transport : sortedTransport) {
      tableModel = tableModels.get(transport);


      String transportFirm = getTransportFirmInfo(transport);
      dateString = getDateString(dateString, transport);


      // Overskrift
      row = sheet.createRow((short) currentRow++);


      createCell(row, cellStyle.getStyle12(), (short) 0, transport
          .getTransportName()
          + " - lasting: " + dateString);


      row = sheet.createRow((short) currentRow++);
      createCell(row, cellStyle.getStyle12(), (short) 0, "firma:"
          + transportFirm);


      String comment = transport.getTransportComment();
      if (comment != null && comment.length() > 0) {
        row = sheet.createRow((short) currentRow++);
        createCell(row, cellStyle.getStyle12(), (short) 0, "kommentar:"
            + comment);
      }


      row = sheet.createRow((short) currentRow++);


      int columnCount = tableModel.getColumnCount();
      int rowCount = tableModel.getRowCount();


      createColumnHeadings(row, cellStyle.getStyle12BorderBottomThick(),
          tableModel, (short) 0, columnCount - 1, 0, null);


      // Data
      int j;
      int k;
      for (j = currentRow; j < rowCount + currentRow; j++) {
        setLabelInfo(labelInfo, infoString, j);
        row = sheet.createRow((short) j);


        for (k = 0; k < columnCount; k++) {
          // kommentar
          if (k == 11) {
            // har kommentar
            if (tableModel.getValueAt(j - currentRow, k) != null) {
              // lager egen linje for kommentar
              row = sheet.createRow((short) j + 1);
              createCell(row, null, (short) 0, String
                  .valueOf(tableModel.getValueAt(j
                      - currentRow++, k)));


              j++;

View Full Code Here

      throw new ProTransException("Katalog ikke satt");
    }
    String infoString = "Genererer excel-fil...rad ";
    HSSFWorkbook wb = new HSSFWorkbook();


    HSSFSheet sheet;


    sheet = getSheet(wb, 9, new int[] { 7000, 1400, 1400, 1200, 1500, 1500,
        1900, 1300, 1000 });


    int currentRow = 0;
    HSSFRow row;
    Map<Integer, HSSFRow> rader = new HashMap<Integer, HSSFRow>();


    CellStyle cellStyle = new CellStyle(wb);


    short addColumn = -1;
    short weekAdd = -3;


    Set<Integer> weeks = tableModels.keySet();
    List<Integer> sortedWeeks = new ArrayList<Integer>(weeks);
    Collections.sort(sortedWeeks);
    for (Integer week : sortedWeeks) {
      addColumn++;
      weekAdd += 3;
      currentRow = 0;
      if (weekAdd > 0) {
        sheet.setColumnWidth((short) (weekAdd - 1), (short) 500);
      }
      sheet.setColumnWidth(weekAdd, (short) 7000);


      Map<Transport, TransportOverviewTableModel> models = tableModels
          .get(week);
      Set<Transport> transports = models.keySet();


      List<Transport> sortedTransport = new ArrayList<Transport>(
          transports);
      Collections.sort(sortedTransport, new TransportComparator());


      row = rader.get(currentRow);
      if (row == null) {
        row = sheet.createRow((short) currentRow);
        rader.put(currentRow, row);
      }
      currentRow++;
      createCell(row, cellStyle.getStyle14Bold(), weekAdd, "Uke: " + week);

View Full Code Here

    if (notVisibleColumns != null) {
      notVisibleColumnsSize = notVisibleColumns.size();
    }
    String infoString = "Genererer excel-fil...rad ";
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet;


    sheet = wb.createSheet("sheet");
    int currentRow = 0;
    HSSFRow row;
    HSSFCell cell;


    CellStyle cellStyle = new CellStyle(wb, (short) headFontSize);


    // Overskrift
    if (heading != null && heading.length() != 0) {
      row = sheet.createRow((short) currentRow++);
      createCell(row, cellStyle.getHeadingStyle(), (short) 0, heading);
    }


    // Kolonneoverskrift
    row = sheet.createRow((short) currentRow++);
    int columnCount = table.getColumnCount();
    int rowCount = table.getRowCount();


    // Skriver ut kolonneoverskrift
    createColumnHeadings(row, cellStyle.getHeadingStyle(),
        table.getModel(), 0, columnCount, 0, notVisibleColumns);


    String groupValue = "";
    List<ExcelGroupSum> formulaCells = new ArrayList<ExcelGroupSum>();
    String groupSumValue = "";
    ExcelGroupSum currentExcelGroupSum = null;


    // Data
    int j;
    int k;
    int l = currentRow;
    // G�r gjennom alle rader og kolonner
    for (j = currentRow; j < rowCount + currentRow; j++) {
      // dersom data skal grupperes
      if (groupColumn != null
          && !table.getValueAt(j - currentRow, groupColumn).equals(
              groupValue)) {
        // setter forrige grupperingssum
        if (currentExcelGroupSum != null) {
          currentExcelGroupSum.setToRow((short) (l));
          formulaCells.add(currentExcelGroupSum);
          groupSumValue = "";
          currentExcelGroupSum = null;
        }
        // henter grupperingsverdi og setter ny overskrift for
        // gruppering
        groupValue = (String) table.getValueAt(j - currentRow,
            groupColumn);
        row = sheet.createRow((short) l);
        createCell(row, cellStyle.getGroupStyle(), (short) 0,
            groupValue);
        sheet.addMergedRegion(new Region((short) l, (short) 0,
            (short) l,
            (short) (columnCount - notVisibleColumnsSize - 1)));
        l++;


      }
      setLabelInfo(labelInfo, infoString, j);


      row = sheet.createRow((short) l);
      l++;


      // g�r gjennom alle kolonner for rad
      for (k = 0; k < columnCount; k++) {
        // dersom kolonne skal v�re synlig
        if (notVisibleColumns == null || !notVisibleColumns.contains(k)) {
          // dersom kolonnebredde er satt
          if (colSize != null) {
            Integer columnSize = colSize.get(k);
            if (columnSize != null) {
              sheet.setColumnWidth((short) k, columnSize
                  .shortValue());
            }
          }
          cell = row.createCell((short) k);
          // dersom celle har verdi

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of org.apache.poi.hssf.usermodel.HSSFSheet

bad.robot.excel.DateCellTest

bg.smoc.web.servlet.ResultsServlet

com.asakusafw.testtools.templategen.ExcelBookBuilder

com.canoo.ant.table.ExcelPropertyTable

com.commander4j.util.JExcel

com.dtrules.compiler.excel.util.ImportRuleSets

com.jfinal.ext.interceptor.excel.ExcelExporter

com.vsked.test.TestExcel

com.vsked.util.FileOperate

er.excel.EGSimpleWorkbookHelper

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.