Explore Public Snippets

=SUMIF(B2:B6;">0")/MAX(COUNTIF(B2:B6;">0");1)

# Italian version
=SOMMA.SE(B2:B6;">0")/MAX(CONTA.SE(B2:B6;">0");1)

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);

---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="");

/// <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);

/// <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;
}

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 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)));
}

/// <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;
}

#######
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

' 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")