Explore Public Snippets
Found 4,606 snippets matching: cell
public by lbottaro 463519 0 7 0
Excel function to calculate the average of positive data in range
=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
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="");
public by lbottaro 7613 12 6 0
How to set a conditional formatting in a cell of Excel document in C#
/// <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#
/// <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
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
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.
/// <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
####### 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
' 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")