working on it ...

Filters

Explore Public Snippets

Sort by

Found 4,817 snippets matching: excel

    public by lbottaro  463517  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 sukhjinderkahlon  326317  7  5  0

    How to Work with Excel Files Using Apache POI

    This code will work as a utility to read and write excel files using Apache POI Library. 3.13
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    public class ExcelUtils {
    
    	private static HSSFSheet ExcelWSheet;
    	private static HSSFWorkbook ExcelWBook;
    	private static HSSFCell Cell;
    
    	private static HSSFRow Row;
    	
    	private static String filePath;
    	public static void setExcelFile(String Path,String SheetName) throws Exception {
    
    			try {
    
       			// Open the Excel file
    				filePath=Path;
    
    			FileInputStream ExcelFile = new FileInputStream(Path);
    
    			// Access the required test data sheet
    
    			ExcelWBook = new HSSFWorkbook(ExcelFile);
    
    			ExcelWSheet = ExcelWBook.getSheet(SheetName);
    			
    
    			} catch (Exception e){
    
    				throw (e);
    
    			}
    
    	}
    
    	//This method is to read the test data from the Excel cell, in this we are passing parameters as Row num and Col num
    
        public static String getCellData(int RowNum, int ColNum) throws Exception{
    
    			try{
    
      			Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
    
      			String CellData = Cell.getStringCellValue();
    
      			return CellData;
    
      			}catch (Exception e){
    
    				return"";
    
      			}
    
        }
    
    
        
      //This method is to write in the Excel cell, Row num and Col num are the parameters
    
    	public static void setCellData(String Result,  int RowNum, int ColNum) throws Exception	{
    
    			try{
    
      			Row  = ExcelWSheet.getRow(RowNum);
    
    			Cell = Row.getCell(ColNum, Row.RETURN_BLANK_AS_NULL);
    
    			if (Cell == null) {
    
    				Cell = Row.createCell(ColNum);
    
    				Cell.setCellValue(Result);
    
    				} else {
    
    					Cell.setCellValue(Result);
    
    				}
    
      // Constant variables Test Data path and Test Data file name
    
      				FileOutputStream fileOut = new FileOutputStream(filePath);
    
      				ExcelWBook.write(fileOut);
    
      				fileOut.flush();
    
    					fileOut.close();
    
    				}catch(Exception e){
    
    					throw (e);
    
    			}
    
    		}
    
    	
    }
              

    public by fabio.nosenzo  264124  8  6  0

    Refresh all pivot table excel vba macro

    With this snippet you can refresh all the pivot tables in excel workbook within a macro
    ThisWorkbook.RefreshAll

    public by fabio.nosenzo  215147  1  6  0

    Concatenate excel values with zero padding

    How to concatenate excel values with padding of the second number with correct number of zeros
    'padding with 3 zeros
    
    =IF(B2<10;A2&"000"&B2;A2&"00"&B2)

    public by sherazam  120635  0  5  0

    How to Set Line Spacing of a Paragraph in an Excel Shape or Textbox inside .NET Apps

    This technical tip shows how to Set Line Spacing of the Paragraph in a Shape or Textbox in .NET applications. You can set the line space of the paragraph, its space before and space after using the TextParagraph.LineSpace, TextParagraph.SpaceBefore and TextParagraph.SpaceAfter respectively. The following example shows how to Set Line Spacing of the
    // Enter here the actual content of the snippet.            
    
    //[C# Code]
     
    // For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
    // The path to the documents directory.
    string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
    
    // Create a workbook
    Workbook wb = new Workbook();
    
    // Access first worksheet
    Worksheet ws = wb.Worksheets[0];
    
    // Add text box inside the sheet
    ws.Shapes.AddTextBox(2, 0, 2, 0, 100, 200);
    
    // Access first shape which is a text box and set is text
    Shape shape = ws.Shapes[0];
    shape.Text = "Sign up for your free phone number.\nCall and text online for free.";
    
    // Acccess the first paragraph
    TextParagraph p = shape.TextBody.TextParagraphs[1];
    
    // Set the line space
    p.LineSpaceSizeType = LineSpaceSizeType.Points;
    p.LineSpace = 20;
    
    // Set the space after
    p.SpaceAfterSizeType = LineSpaceSizeType.Points;
    p.SpaceAfter = 10;
    
    // Set the space before
    p.SpaceBeforeSizeType = LineSpaceSizeType.Points;
    p.SpaceBefore = 10;
    
    // Save the workbook in xlsx format
    wb.Save(dataDir + "output_out_.xlsx", SaveFormat.Xlsx); 
    
    //[VB.NET Code]
    
    ' For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
    ' The path to the documents directory.
    Dim dataDir As String = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType)
    
    ' Create a workbook
    Dim wb As New Workbook()
    
    ' Access first worksheet
    Dim ws As Worksheet = wb.Worksheets(0)
    
    ' Add text box inside the sheet
    ws.Shapes.AddTextBox(2, 0, 2, 0, 100, 200)
    
    ' Access first shape which is a text box and set is text
    Dim shape As Shape = ws.Shapes(0)
    shape.Text = "Sign up for your free phone number." & vbLf & "Call and text online for free."
    
    ' Acccess the first paragraph
    Dim p As TextParagraph = shape.TextBody.TextParagraphs(1)
    
    ' Set the line space
    p.LineSpaceSizeType = LineSpaceSizeType.Points
    p.LineSpace = 20
    
    ' Set the space after
    p.SpaceAfterSizeType = LineSpaceSizeType.Points
    p.SpaceAfter = 10
    
    ' Set the space before
    p.SpaceBeforeSizeType = LineSpaceSizeType.Points
    p.SpaceBefore = 10
    
    ' Save the workbook in xlsx format
    wb.Save(dataDir & Convert.ToString("output_out_.xlsx"), SaveFormat.Xlsx) 
    
    

    public by zonaro  197153  0  8  0

    Sum (Excel port for VB.NET)

    Like in Excel, this method sum the numbers of a ParamArray
    Function Sum(ByVal ParamArray Nums As Decimal()) As Decimal
            Dim s = 0
            For Each i As Decimal In Nums
                s += i
            Next
            Return s
        End Function            

    public by fabio.nosenzo  6030  3  7  0

    Screen update disable in Excel VBA

    How to disable and enable screen update in Excel VBA, speeding up macro execution
    ' disable screen updating
    Application.ScreenUpdating = False
    
    ' enable screen updating
    Application.ScreenUpdating = True
    

    public by fabio.nosenzo  3291  0  6  1

    Replace Function MS Excel VBA

    Replace a string with another within a string
    ' Create a file path with date
    sPath = "C:\myfile - " & Date
    ' / char is not admitted
    ' replace / with -
    sPath = Replace(sPath, "/", "-")

    public by fabio.nosenzo  3370  0  6  0

    Day of the year excel formula

    How to calculate the day of the year
    If you want to know the day of today:
    =TODAY()-DATE(YEAR(TODAY()),1,0)
    
    If you want to know the day of a specific cell (e.g. A1):
    =A1()-DATE(YEAR(A1),1,0)

    public by fabio.nosenzo  2807  0  5  0

    Use MS Excel formulas in VBA

    General description of vba excel formula insertion
    ' general description
    ' Application.formulaname(formula parameters)
    
    ' example
    ' count cells with value = x
    
    Dim iCounter As Integer
    iCounter = Application.CountIf(Range("A1:D5"), "x")
    
    • Public Snippets
    • Channels Snippets