working on it ...

Filters

Explore Public Snippets

Sort by

Found 4,606 snippets matching: cell

    public by lbottaro  463519  0  7  0

    Excel function to calculate the average of positive data in range

    This excel function calculate the average of a specific range of cells, regarding only positive values. Zero or less than zero value won't be used in average calculation. The italian version of this snippet can be used as well on OpenOffice 3.
    =SUMIF(B2:B6;">0")/MAX(COUNTIF(B2:B6;">0");1)
    
    # Italian version
    =SOMMA.SE(B2:B6;">0")/MAX(CONTA.SE(B2:B6;">0");1)

    public by lbottaro  4390  8  7  0

    How to bind a validation list to a cell of Excel file in Csharp

    This c# method allows to define a validation list of entries (as string, using comma [,] as separator character) to bind to a specific cell for a sheet in an xls file (Microsoft Excel compatible). The validation list appears as a dropdown menu, where the user can choose the content from.
    enum Result
    {
        Passed,
        Partially_Passed,
        Failed,
        Blocked,
        Not_Applicable,
        Not_Tested
    }
    
    /// <summary>
    /// This method creates a Excel validation list for the selected cell, and associate the list to the cell (dropdown combo box)
    /// </summary>
    /// <param name="ws">Excel worksheet</param>
    /// <param name="excel_CellToValidate">Cell to validate</param>
    /// <param name="listForValidation">List of data to use for validation</param>
    private void Excel_createCellValidationDropDownList(Worksheet ws, string excel_CellToValidate, string listForValidation)
    {
    	ws.get_Range(excel_CellToValidate, excel_CellToValidate).Cells.Validation.Delete();
    	ws.get_Range(excel_CellToValidate, excel_CellToValidate).Cells.Validation.Add(
    		XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertInformation, XlFormatConditionOperator.xlBetween,
    		listForValidation, Type.Missing);
    }
    
    //Example of usage
    string m_excelList = string.Join(",", Enum.GetNames(typeof(Result)));
    this.Excel_createCellValidationDropDownList(ws, "C1", m_excelList);

    public by p.kontalis  1604  0  5  0

    Move Cell Values from a Table Column to another

    ---Take a look of the join tables before the move
    SELECT * 
    FROM vtigercrm600.vtiger_accountbillads
    LEFT JOIN vtigercrm600.vtiger_accountshipads
    ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
    WHERE vtiger_accountshipads.ship_street !="" AND vtiger_accountbillads.bill_street=""
    ---Copy the Values
    UPDATE vtigercrm600.vtiger_accountbillads
    LEFT JOIN vtigercrm600.vtiger_accountshipads
    ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
    SET vtiger_accountbillads.bill_street=vtiger_accountshipads.ship_street
    WHERE vtiger_accountbillads.bill_street="" AND vtiger_accountshipads.ship_street != "";
    ---Erase the copied values  (or the values are the same in both cells)
    UPDATE vtigercrm600.vtiger_accountbillads
    LEFT JOIN vtigercrm600.vtiger_accountshipads
    ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
    SET vtiger_accountshipads.ship_street=""
    WHERE vtiger_accountbillads.bill_street=vtiger_accountshipads.ship_street;
    ---Validation of all transfers
    SELECT * #vtiger_accountbillads.accountaddressid,vtiger_accountbillads.bill_code,vtiger_accountshipads.ship_code
    FROM vtigercrm600.vtiger_accountbillads
    LEFT JOIN vtigercrm600.vtiger_accountshipads
    ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
    WHERE (vtiger_accountshipads.ship_city != "" AND vtiger_accountbillads.bill_city="")
    OR (vtiger_accountshipads.ship_code != "" AND vtiger_accountbillads.bill_code="")
    OR (vtiger_accountshipads.ship_country != "" AND vtiger_accountbillads.bill_country="")
    OR (vtiger_accountshipads.ship_state != "" AND vtiger_accountbillads.bill_state="")
    OR (vtiger_accountshipads.ship_pobox != "" AND vtiger_accountbillads.bill_pobox="")
    OR (vtiger_accountshipads.ship_street != "" AND vtiger_accountbillads.bill_street="");
    

    public by lbottaro  7613  12  6  0

    How to set a conditional formatting in a cell of Excel document in C#

    This c# code allows to set a conditional formatting for cells in a sheet for a Microsoft Excel document (xls). Conditional formatting allows to set some property to a cell based on the cell value.
    /// <summary>
    /// This method creates a Excel Conditional Formatting for cell
    /// </summary>
    /// <param name="ws">Worksheet to use</param>
    /// <param name="excelList_firstCell">First cell</param>
    /// <param name="excelList_lastCell">Last cell</param>
    /// <param name="condOperator">Operator to use for validation</param>
    /// <param name="condValidation">String to use for validation</param>
    /// <param name="c">Color to use when validating</param>
    private void Excel_createCellFormatValidation(Worksheet ws, string excelList_firstCell, string excelList_lastCell, XlFormatConditionOperator condOperator, string condValidation, Color c)
    {
    	FormatCondition cf = (FormatCondition)ws.get_Range(excelList_firstCell, excelList_lastCell).FormatConditions.Add(XlFormatConditionType.xlCellValue, condOperator, condValidation, Type.Missing);
    	cf.Interior.PatternColorIndex = Constants.xlAutomatic;
    	cf.Interior.Color = ColorTranslator.ToWin32(c);
    	cf.Font.Bold = true;
    }
    
    
    //Example of usage
    
    //cond_passed is a string which value must be present in the cell range to trigger the conditional formatting.
    string cond_passed = "SUCCESS";
    this.Excel_createCellFormatValidation(ws, "C1", "C19", XlFormatConditionOperator.xlEqual, cond_passed, Color.Green);
    

    public by lbottaro  5179  2  8  0

    How to create a filtered list in an Excel file in C#

    This c# example allows to insert a list filter into a sheet of a Excel xls file. This filter allows to select only the rows where the selected cell content matches the criteria. The method needs the top left and bottom right cell coordinates to generate the list.
    /// <summary>
    /// This method creates a Excel filtered list. 
    /// Only the cell content matching the filter criteria will be selected
    /// </summary>
    /// <param name="ws">Worksheet to use</param>
    /// <param name="excelList_firstCell">First cell of List (top left, eg A2)</param>
    /// <param name="excelList_lastCell">Last cell of List (bottom right, eg J22)</param>
    private void Excel_createFilteredList(Worksheet ws, string excelList_firstCell, string excelList_lastCell)
    {
    	ws.get_Range(excelList_firstCell, excelList_lastCell).AutoFilter(1, System.Reflection.Missing.Value, XlAutoFilterOperator.xlAnd, System.Reflection.Missing.Value, true);
    	ws.get_Range(excelList_firstCell, excelList_lastCell).Borders.LineStyle = XlLineStyle.xlContinuous;
    }

    public by fabio.nosenzo  3306  14  6  1

    Excel VBA Merge cells with same value

    Merge cells in different columns with the same value in an ordered list
    Sub merge_cells()
    Application.DisplayAlerts = False
        
        'row to be avaluated
        iRow = 1
        'number of columns to be evaluated
        iNumberOfColumns = 12
        'first Column to be evaluated
        iFirstColumn = 4
        For i = iFirstColumn + 1 To iNumberOfColumns + iFirstColumn + 1
            If Cells(iRow, i) <> Cells(iRow, i - 1) Then
            iLastRow = i - 1
            'get first column letter
            vArr = Split(Cells(1, iFirstColumn).Address(True, False), "$")
            sFirstColumn = vArr(0)
            'get last column letter
            vArr = Split(Cells(1, i - 1).Address(True, False), "$")
            sLastColumn = vArr(0)
            sRange = sFirstColumn & iRow & ":" & sLastColumn & iRow
            Range(sRange).Select
                With Selection
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                    .WrapText = True
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = False
                End With
            Selection.Merge
            iFirstColumn = i
            End If
        Next i
          
    Application.DisplayAlerts = True
    End Sub
    

    public by lbottaro  3304  1  5  0

    How to create a Grid view in java spring

    Aligns the first rows * cols components of parent in a grid. Each component is as big as the maximum preferred width and height of the components. The parent is made just big enough to fit them all. @param rows number of rows @param cols number of columns @param initialX x location to start the grid at @param initialY y location to start t
    public static void makeGrid(Container parent,
                                int rows, int cols,
                                int initialX, int initialY,
                                int xPad, int yPad) {
        SpringLayout layout;
        try {
            layout = (SpringLayout)parent.getLayout();
        } catch (ClassCastException exc) {
            System.err.println("The first argument to makeGrid must use SpringLayout.");
            return;
        }
    
        Spring xPadSpring = Spring.constant(xPad);
        Spring yPadSpring = Spring.constant(yPad);
        Spring initialXSpring = Spring.constant(initialX);
        Spring initialYSpring = Spring.constant(initialY);
        int max = rows * cols;
    
        //Calculate Springs that are the max of the width/height so that all
        //cells have the same size.
        Spring maxWidthSpring = layout.getConstraints(parent.getComponent(0)).
                                    getWidth();
        Spring maxHeightSpring = layout.getConstraints(parent.getComponent(0)).
                                    getWidth();
        for (int i = 1; i < max; i++) {
            SpringLayout.Constraints cons = layout.getConstraints(
                                            parent.getComponent(i));
    
            maxWidthSpring = Spring.max(maxWidthSpring, cons.getWidth());
            maxHeightSpring = Spring.max(maxHeightSpring, cons.getHeight());
        }
    
        //Apply the new width/height Spring. This forces all the
        //components to have the same size.
        for (int i = 0; i < max; i++) {
            SpringLayout.Constraints cons = layout.getConstraints(
                                            parent.getComponent(i));
    
            cons.setWidth(maxWidthSpring);
            cons.setHeight(maxHeightSpring);
        }
    
        //Then adjust the x/y constraints of all the cells so that they
        //are aligned in a grid.
        SpringLayout.Constraints lastCons = null;
        SpringLayout.Constraints lastRowCons = null;
        for (int i = 0; i < max; i++) {
            SpringLayout.Constraints cons = layout.getConstraints(
                                                 parent.getComponent(i));
            if (i % cols == 0) { //start of new row
                lastRowCons = lastCons;
                cons.setX(initialXSpring);
            } else { //x position depends on previous component
                cons.setX(Spring.sum(lastCons.getConstraint(SpringLayout.EAST),
                                     xPadSpring));
            }
    
            if (i / cols == 0) { //first row
                cons.setY(initialYSpring);
            } else { //y position depends on previous row
                cons.setY(Spring.sum(lastRowCons.getConstraint(SpringLayout.SOUTH),
                                     yPadSpring));
            }
            lastCons = cons;
        }
    
        //Set the parent's size.
        SpringLayout.Constraints pCons = layout.getConstraints(parent);
        pCons.setConstraint(SpringLayout.SOUTH,
                            Spring.sum(
                                Spring.constant(yPad),
                                lastCons.getConstraint(SpringLayout.SOUTH)));
        pCons.setConstraint(SpringLayout.EAST,
                            Spring.sum(
                                Spring.constant(xPad),
                                lastCons.getConstraint(SpringLayout.EAST)));
    }
    

    public by Geometry  1292  0  7  0

    AllowNestedProperties: Enables the display of recursively nested properties in the Windows.Forms' DataGridView control.

    Enables the display of recursively nested properties in the Windows.Forms' DataGridView control. The to enable nested properties. True to use nested properties, false otherwise. This method will register a custom cell formatting event in the DataGridView and retrieve any nested property specified in the column's DataPropertyName propert
    /// <summary>
    ///   Enables the display of recursively nested properties 
    ///   in the Windows.Forms' DataGridView control.
    /// </summary>
    /// 
    /// <param name="dataGridView">The <see cref="DataGridView"/> to enable nested properties.</param>
    /// <param name="value">True to use nested properties, false otherwise.</param>
    /// 
    /// <remarks>
    ///   This method will register a custom cell formatting event in the DataGridView and
    ///   retrieve any nested property specified in the column's DataPropertyName property
    ///   using reflection. This method is based on th idea by Antonio Bello, originally 
    ///   shared in:
    ///   
    ///    http://www.developer-corner.com/2007/07/datagridview-how-to-bind-nested-objects_18.html
    ///    
    /// </remarks>
    /// 
    public static void AllowNestedProperties(this DataGridView dataGridView, bool value)
    {
        if (value)
            dataGridView.CellFormatting += dataGridView1_CellFormatting;
        else
            dataGridView.CellFormatting -= dataGridView1_CellFormatting;
    }

    public by johansonkatherine  4470  0  6  1

    Split Excel Worksheets into New Workbooks and to any Image Format using Cloud API in Ruby

    Cloud Developers can now use ruby language to split excel workbook into single worksheets and save all or specific worksheets as new workbooks, TIFFs or other images format by using Aspose.Cells for Cloud API. To split workbooks, you need to upload the input Excel files to Aspose for Cloud or any supported third party storage and then send a POST r
    #######
    Section 1 ######
    app_sid = '####### Section 1 ######
    app_sid = '77******-1***-4***-a***-80**********'
    app_key = '*********************'
    Aspose::Cloud::Common::AsposeApp.new(app_sid, app_key)
    #build URI to split workbook
    str_uri = 'http://api.aspose.com/v1.1/cells/Sample.xlsx/split?format=png';
    #uncomment following line to split specific worksheets
    #str_uri = 'http://api.aspose.com/v1.1/cells/Sample.xlsx/split?from=2&to=3&format=tiff';
    #sign URI
    signed_uri = Aspose::Cloud::Common::Utils.sign(str_uri);
    #######
    End Section 1 ######
    #######
    Section 2 ######
    #Split spreadsheet file
    response_stream = RestClient.post(signed_uri, '', {:accept=>:json})
    #######
    End Section 2 #####
    
    #Download Split Files
    
    stream_hash = JSON.parse(response_stream)
    stream_hash['Result']['Documents'].each do |document|
    
           #Build and sign URI to download split files
    
    file_name = File.basename(document['link']['Href'])
    str_uri = 'http://api.aspose.com/v1.1/storage/file/' + file_name;             
    signed_uri = Aspose::Cloud::Common::Utils.sign(str_uri);
    
    file_name = File.basename(str_uri)
    
          #Download and save split files
    
    response_stream = RestClient.get(signed_uri, :accept => 'application/json')
          Aspose::Cloud::Common::Utils.save_file(response_stream, file_name)
    
    End
    

    public by fabio.nosenzo  2459  0  6  0

    Day of the week Excel Formula

    This formula returns the day of the week of a cell with a date value
    ' example with the following cell with date: A1
    
    'formula for day abbreviation (e.g. "Tue")
    =TEXT(A1,"ddd")
    
    'formula for full day (e.g. "Tuesday")
    =TEXT(A1,"dddd")
    • Public Snippets
    • Channels Snippets