working on it ...

Filters

Explore Public Snippets

Sort by

Found 1,417 snippets matching: cells

    public by lbottaro  463534  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 fabio.nosenzo  3312  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  4405  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  1613  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  7639  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  3314  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 johansonkatherine  4480  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 msdn  1433  0  6  0

    GetRandomCellOrdering: Returns all cells in a collection in random order.

    Returns all cells in a collection in random order. The puzzle state. The collection of cells.
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Threading;
    using System.Threading.Tasks;
    using System.Drawing;
    using Microsoft.ParallelComputingPlatform.ParallelExtensions.Samples.Sudoku.Techniques;
    using Microsoft.ParallelComputingPlatform.ParallelExtensions.Samples.Sudoku.Utilities;
    using System.Collections.Concurrent;
    using System.Linq;
    
    /// <summary>Returns all cells in a collection in random order.</summary>
    /// <param name="state">The puzzle state.</param>
    /// <returns>The collection of cells.</returns>
    private static Point [] GetRandomCellOrdering(PuzzleState state)
    {
    	// Create the collection
    	Point [] points = new Point[state.GridSize*state.GridSize];
    
    	// Find all cells
    	int count=0;
    	for (int i = 0; i < state.GridSize; i++)
    	{
    		for (int j = 0; j < state.GridSize; j++)
    		{
    			points[count++] = new Point(i,j);
    		}
    	}
    
    	// Randomize their order
    	for(int i=0; i<points.Length-1; i++)
    	{
    		int swapPos = RandomHelper.GetRandomNumber(i, points.Length-1);
    		Point temp = points[swapPos];
    		points[swapPos] = points[i];
    		points[i] = temp;
    	}
    
    	// Return the randomized collection
    	return points;
    }

    public by msdn  2722  0  6  0

    AddTableCellsToTableRow: Adds TableCell elements to xamlTableRowElement.

    Adds TableCell elements to xamlTableRowElement. XmlElement representing Xaml TableRow element to which the converted cells should be added XmlElement representing the child of tr or tbody element from which we should start adding td elements properties of the current html tr element to which cells are to be added XmlElement re
    public const string Xaml_TableRow = "TableRow";
    public const string Xaml_TableCell = "TableCell";
    // ---------------------------------------------------------------------
    //
    // Private Fields
    //
    // ---------------------------------------------------------------------
    static string _xamlNamespace = "http://schemas.microsoft.com/winfx/2006/xaml/presentation";
    public const string Xaml_TableCell_ColumnSpan = "ColumnSpan";
    
    /// <summary>
    /// Adds TableCell elements to xamlTableRowElement.
    /// </summary>
    /// <param name="xamlTableRowElement">
    /// XmlElement representing Xaml TableRow element to which the converted cells should be added
    /// </param>
    /// <param name="htmlTDStartNode">
    /// XmlElement representing the child of tr or tbody element from which we should start adding td elements
    /// </param>
    /// <param name="currentProperties">
    /// properties of the current html tr element to which cells are to be added
    /// </param>
    /// <returns>
    /// XmlElement representing the current position of the iterator among the children of the parent Html tbody/tr element
    /// </returns>
    private static XmlNode AddTableCellsToTableRow(XmlElement xamlTableRowElement, XmlNode htmlTDStartNode, Hashtable currentProperties, ArrayList columnStarts, ArrayList activeRowSpans, CssStylesheet stylesheet, List<XmlElement> sourceContext)
    {
        // parameter validation
        Debug.Assert(xamlTableRowElement.LocalName == Xaml_TableRow);
        Debug.Assert(currentProperties != null);
        if (columnStarts != null)
        {
            Debug.Assert(activeRowSpans.Count == columnStarts.Count);
        }
    
        XmlNode htmlChildNode = htmlTDStartNode;
        double columnStart = 0;
        double columnWidth = 0;
        int columnIndex = 0;
        int columnSpan = 0;
    
        while (htmlChildNode != null && htmlChildNode.LocalName.ToLower() != "tr" && htmlChildNode.LocalName.ToLower() != "tbody" && htmlChildNode.LocalName.ToLower() != "thead" && htmlChildNode.LocalName.ToLower() != "tfoot")
        {
            if (htmlChildNode.LocalName.ToLower() == "td" || htmlChildNode.LocalName.ToLower() == "th")
            {
                XmlElement xamlTableCellElement = xamlTableRowElement.OwnerDocument.CreateElement(null, Xaml_TableCell, _xamlNamespace);
    
                sourceContext.Add((XmlElement)htmlChildNode);
    
                Hashtable tdElementLocalProperties;
                Hashtable tdElementCurrentProperties = GetElementProperties((XmlElement)htmlChildNode, currentProperties, out tdElementLocalProperties, stylesheet, sourceContext);
    
                // TODO: determine if localProperties can be used instead of htmlChildNode in this call, and if they can,
                // make necessary changes and use them instead.
                ApplyPropertiesToTableCellElement((XmlElement)htmlChildNode, xamlTableCellElement);
    
                if (columnStarts != null)
                {
                    Debug.Assert(columnIndex < columnStarts.Count - 1);
                    while (columnIndex < activeRowSpans.Count && (int)activeRowSpans[columnIndex] > 0)
                    {
                        activeRowSpans[columnIndex] = (int)activeRowSpans[columnIndex] - 1;
                        Debug.Assert((int)activeRowSpans[columnIndex] >= 0);
                        columnIndex++;
                    }
                    Debug.Assert(columnIndex < columnStarts.Count - 1);
                    columnStart = (double)columnStarts[columnIndex];
                    columnWidth = GetColumnWidth((XmlElement)htmlChildNode);
                    columnSpan = CalculateColumnSpan(columnIndex, columnWidth, columnStarts);
                    int rowSpan = GetRowSpan((XmlElement)htmlChildNode);
    
                    // Column cannot have no span
                    Debug.Assert(columnSpan > 0);
                    Debug.Assert(columnIndex + columnSpan < columnStarts.Count);
    
                    xamlTableCellElement.SetAttribute(Xaml_TableCell_ColumnSpan, columnSpan.ToString());
    
                    // Apply row span
                    for (int spannedColumnIndex = columnIndex; spannedColumnIndex < columnIndex + columnSpan; spannedColumnIndex++)
                    {
                        Debug.Assert(spannedColumnIndex < activeRowSpans.Count);
                        activeRowSpans[spannedColumnIndex] = (rowSpan - 1);
                        Debug.Assert((int)activeRowSpans[spannedColumnIndex] >= 0);
                    }
    
                    columnIndex = columnIndex + columnSpan;
                }
    
                AddDataToTableCell(xamlTableCellElement, htmlChildNode.FirstChild, tdElementCurrentProperties, stylesheet, sourceContext);
                if (xamlTableCellElement.HasChildNodes)
                {
                    xamlTableRowElement.AppendChild(xamlTableCellElement);
                }
    
                Debug.Assert(sourceContext.Count > 0 && sourceContext[sourceContext.Count - 1] == htmlChildNode);
                sourceContext.RemoveAt(sourceContext.Count - 1);
    
                htmlChildNode = htmlChildNode.NextSibling;
            }
            else
            {
                // Not td element. Ignore it.
                // TODO: Consider better recovery
                htmlChildNode = htmlChildNode.NextSibling;
            }
        }
        return htmlChildNode;
    }

    public by msdn  1410  0  6  0

    MergeTwoCells

    // Given a document name, a worksheet name, and the names of two adjacent cells, merges the two cells. // When two cells are merged, only the content from one cell is preserved: // the upper-left cell for left-to-right languages or the upper-right cell for right-to-left languages.
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using System.Text.RegularExpressions;
    
    // Given a document name, a worksheet name, and the names of two adjacent cells, merges the two cells.
    // When two cells are merged, only the content from one cell is preserved:
    // the upper-left cell for left-to-right languages or the upper-right cell for right-to-left languages.
    private static void MergeTwoCells(string docName, string sheetName, string cell1Name, string cell2Name)
    {
        // Open the document for editing.
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
        {
            Worksheet worksheet = GetWorksheet(document, sheetName);
            if (worksheet == null || string.IsNullOrEmpty(cell1Name) || string.IsNullOrEmpty(cell2Name))
            {
                return;
            }
    
            // Verify if the specified cells exist, and if they do not exist, create them.
            CreateSpreadsheetCellIfNotExist(worksheet, cell1Name);
            CreateSpreadsheetCellIfNotExist(worksheet, cell2Name);
    
            MergeCells mergeCells;
            if (worksheet.Elements<MergeCells>().Count() > 0)
            {
                mergeCells = worksheet.Elements<MergeCells>().First();
            }
            else
            {
                mergeCells = new MergeCells();
    
                // Insert a MergeCells object into the specified position.
                if (worksheet.Elements<CustomSheetView>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<CustomSheetView>().First());
                }
                else if (worksheet.Elements<DataConsolidate>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<DataConsolidate>().First());
                }
                else if (worksheet.Elements<SortState>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SortState>().First());
                }
                else if (worksheet.Elements<AutoFilter>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<AutoFilter>().First());
                }
                else if (worksheet.Elements<Scenarios>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<Scenarios>().First());
                }
                else if (worksheet.Elements<ProtectedRanges>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<ProtectedRanges>().First());
                }
                else if (worksheet.Elements<SheetProtection>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetProtection>().First());
                }
                else if (worksheet.Elements<SheetCalculationProperties>().Count() > 0)
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetCalculationProperties>().First());
                }
                else
                {
                    worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
                }
            }
    
            // Create the merged cell and append it to the MergeCells collection.
            MergeCell mergeCell = new MergeCell() { Reference = new StringValue(cell1Name + ":" + cell2Name) };
            mergeCells.Append(mergeCell);
    
            worksheet.Save();
        }
    }
    • Public Snippets
    • Channels Snippets