working on it ...

Filters

Explore Public Snippets

Sort by

Found 402 snippets matching: worksheet

    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  2597  2  6  2

    VBA Excel - Add worksheet with name

    How to add a worksheet at the and of a workbook with a specific name
    Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = "My New Worksheet"

    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 lbottaro  2248  1  6  0

    How to populate an Excel worksheet with data in CSharp

    This c# example shows how to add some data into a worksheet in a Microsoft Excel document (xls). Note the hyperlink pointing to the main sheet.
    /// <summary>
    /// Populate the Sheet
    /// </summary>
    /// <param name="ws">Worksheet</param>
    /// <param name="tc">SyncSSTestCase</param>
    private void populateXlsSheet(Worksheet ws, SyncSSTestCase tc)
    {
    	ws.Name = tc.SheetName;
    
    	int rowIndex = 1;
    	ws.get_Range("A" + rowIndex, "A" + rowIndex).Cells.Value2 = "Test case Name";
    	ws.get_Range("A" + rowIndex, "A" + rowIndex).Cells.ColumnWidth = 30;
    	ws.get_Range("B" + rowIndex, "B" + rowIndex).Cells.Value2 = "Purpose";
    	ws.get_Range("B" + rowIndex, "B" + rowIndex).Cells.ColumnWidth = 30;
    	ws.get_Range("C" + rowIndex, "C" + rowIndex).Cells.Value2 = "Area";
    	ws.get_Range("C" + rowIndex, "C" + rowIndex).Cells.ColumnWidth = 15;
    	ws.get_Range("D" + rowIndex, "D" + rowIndex).Cells.Value2 = "Entity";
    	ws.get_Range("E" + rowIndex, "D" + rowIndex).Cells.ColumnWidth = 15;
    	ws.get_Range("E" + rowIndex, "E" + rowIndex).Cells.Value2 = "Type";
    	ws.get_Range("E" + rowIndex, "E" + rowIndex).Cells.ColumnWidth = 15;
    	ws.get_Range("F" + rowIndex, "F" + rowIndex).Cells.Value2 = "Environment";
    	ws.get_Range("F" + rowIndex, "F" + rowIndex).Cells.ColumnWidth = 15;
    	ws.get_Range("G" + rowIndex, "G" + rowIndex).Cells.Value2 = "Actions";
    	ws.get_Range("G" + rowIndex, "G" + rowIndex).Cells.ColumnWidth = 30;
    	ws.get_Range("H" + rowIndex, "H" + rowIndex).Cells.Value2 = "Expected Behavior";
    	ws.get_Range("H" + rowIndex, "H" + rowIndex).Cells.ColumnWidth = 30;
    	ws.get_Range("I" + rowIndex, "I" + rowIndex).Cells.Value2 = "Comments";
    	ws.get_Range("I" + rowIndex, "I" + rowIndex).Cells.ColumnWidth = 20;
    
    	ws.get_Range("A" + rowIndex, "I" + rowIndex).Cells.Font.Color = ColorTranslator.ToWin32(Color.White);
    	ws.get_Range("A" + rowIndex, "I" + rowIndex).Cells.Interior.Color = ColorTranslator.ToWin32(Color.Gray);
    	ws.get_Range("A" + rowIndex, "I" + rowIndex).Cells.Font.Bold = true;
    
    	rowIndex++;
    
    	ws.get_Range("A" + rowIndex, "A" + rowIndex).Cells.Value2 = tc.FullName;
    	ws.get_Range("B" + rowIndex, "B" + rowIndex).Cells.Value2 = tc.Purpose;
    	ws.get_Range("C" + rowIndex, "C" + rowIndex).Cells.Value2 = tc.Area;
    	ws.get_Range("D" + rowIndex, "D" + rowIndex).Cells.Value2 = tc.Entity;
    	ws.get_Range("E" + rowIndex, "E" + rowIndex).Cells.Value2 = tc.Type;
    	ws.get_Range("F" + rowIndex, "F" + rowIndex).Cells.Value2 = tc.Environment;
    
    	string steps = string.Empty;
    	int stepscount = 1;
    	string envNewLine = Environment.NewLine;
    	foreach (XmlNode step in tc.ActionSteps)
    	{
    		if (stepscount == tc.ActionSteps.Count)
    		{
    			envNewLine = "";
    		}
    		steps += string.Format("Step {0}:{1}{2}", stepscount, step.InnerText, envNewLine);
    		stepscount++;
    	}
    
    	ws.get_Range("G" + rowIndex, "G" + rowIndex).Cells.Value2 = steps;
    	ws.get_Range("H" + rowIndex, "H" + rowIndex).Cells.Value2 = tc.ExpectedBehavior;
    	ws.get_Range("I" + rowIndex, "I" + rowIndex).Cells.Value2 = tc.Comment;
    
    	//Create link to Main Sheet Summary Report
    	rowIndex += 2;
    	ws.get_Range("A" + rowIndex, "A" + rowIndex).Cells.Value2 = string.Format("=HYPERLINK(\"#{0}!A1\",\"<< Go to " + c_mainsSheet_Name + "\")", c_mainsSheet_Name);
    
    }

    public by johansonkatherine  6337  1  8  5

    Convert PDF from Remote Server to other Format using Aspose.Pdf for Cloud API in PHP

    Aspose.PDF for Cloud API has released PHP SDK which allows PHP developers to convert PDF files uploaded to a remote server to other formats. You can pass URL and format parameters to specify the PDF’s URL and output format and use the PUT method of the convert resource to convert remote PDF files. You can download Aspose Cloud PHP SDK from the link
    use Aspose\Cloud\Common\AsposeApp;
    use Aspose\Cloud\Common\Product;
    use Aspose\Cloud\Pdf\Converter;
    
    AsposeApp::$appSID = '3395ba5c-***';
    AsposeApp::$appKey = 'e8dd1b0cd***';
    
    /* * Creating the object of Pdf Converter Class with a perameter $fileName */
    $Obj = new Converter('path/to/pdf/file.pdf');
    
    // Call convertByUrl method.
    $Obj->convertByUrl('http://url-to-pdf.com/file.pdf','tiff','outputfile.tiff');
    
    

    public by sherazam  2611  0  6  0

    How to Load Visible Sheets & Skip Invisible Sheet while Loading Workbook in Android Apps

    // Enter here the actual content of the snippet.   // The following code demonstrates how to load visible sheets only.
    
    File sdDir = Environment.getExternalStorageDirectory();
    String sdPath = sdDir.getCanonicalPath();
    
    String filePath = sdPath + "/Sample.out.xlsx"
    
    // Create a sample workbook
    // and put some data in first cell of all 3 sheets
    Workbook createWorkbook = new Workbook();
    createWorkbook.getWorksheets().get("Sheet1").getCells().get("A1").setValue("Aspose");
    createWorkbook.getWorksheets().add("Sheet2").getCells().get("A1").setValue("Aspose");
    createWorkbook.getWorksheets().add("Sheet3").getCells().get("A1").setValue("Aspose");
    // Keep Sheet3 invisible
    createWorkbook.getWorksheets().get("Sheet3").setVisible(false);
    createWorkbook.save(samplePath);
    
    // Load the sample workbook
    LoadDataOption loadDataOption = new LoadDataOption();
    loadDataOption.setOnlyVisibleWorksheet(true);
    LoadOptions loadOptions = new LoadOptions();
    loadOptions.setLoadDataAndFormatting(true);
    loadOptions.setLoadDataOptions(loadDataOption);
    
    Workbook loadWorkbook = new Workbook(samplePath, loadOptions);
    
    System.out.println("Sheet1: A1: " + loadWorkbook.getWorksheets().get("Sheet1").getCells().get("A1").getValue());
    System.out.println("Sheet2: A1: " + loadWorkbook.getWorksheets().get("Sheet2").getCells().get("A1").getValue());
    System.out.println("Sheet3: A1: " + loadWorkbook.getWorksheets().get("Sheet3").getCells().get("A1").getValue());
    
    System.out.println("Data is not loaded from invisible sheet");
     
             

    public by lbottaro  3768  6  7  2

    How to create an Excel file in Csharp

    This C# example creates an xls file (compatible with Microsoft Excel) and set some worksheets in it. You need to specify the CultureInfo to be compatible with the one set on your OS.
    using Microsoft.Office.Interop.Excel;
    
    /// <summary>
    /// Create and fill an XLS file
    /// </summary>
    private void createXls()
    {
    	string m_xls_file = "path of file...";
    	System.Threading.Thread.CurrentThread.CurrentCulture =
    System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
    
    	// Set up the XLS file to be filled with some data
    	ApplicationClass xlApp = new ApplicationClass();
    	try
    	{
    		xlApp = new ApplicationClass();
    	}
    	catch (Exception ex)
    	{	   
    		return;
    	}
    
    	Workbook xlWorkBook = null;
       
    	int numOfWkSheets = 10;
    	
    	Worksheet[] xlWorkSheet = new Worksheet[numOfWkSheets];
       
    	object misValue = Missing.Value;
    	
    	try
    	{
    		xlWorkBook = xlApp.Workbooks.Add(misValue);
    	}
    	catch (Exception ex)
    	{
    		return;
    	}
    	
    	int rowIndex = 1;
    	int firstRow = rowIndex;
    
    	for (int i = 0; i < numOfWkSheets ; i++)
    	{
    		xlWorkSheet[i] = (Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, xlWorkBook.Sheets[1 + i], Type.Missing, Type.Missing);
    	}
    
    	/*Delete the last 2 Sheets generated automatically by Excel app, called Sheet2 and Sheet3*/
    	((Worksheet)xlWorkBook.Application.Sheets[xlWorkBook.Worksheets.Count]).Delete();
    	((Worksheet)xlWorkBook.Application.Sheets[xlWorkBook.Worksheets.Count]).Delete();
    
    	//Set the main sheet as active Sheet
    	((Worksheet)xlWorkBook.Application.ActiveWorkbook.Sheets[1]).Select(Type.Missing);
    
    	//save the file
    	xlApp.DisplayAlerts = false;
    	xlWorkBook.SaveAs(m_xls_file, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    	xlWorkBook.Close(true, misValue, misValue);
    	xlApp.Quit();
    
    	//release all the involved sheets
    	for (int i = 0; i < numOfWkSheets ; i++)
    	{
    		releaseObject(xlWorkSheet[i]);
    	}
    
    	//release the workbook and application
    	releaseObject(xlWorkBook);
    	releaseObject(xlApp);
    }                        

    public by msdn  122949  3  6  0

    InsertCellInWorksheet

    // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. // If the cell already exists, returns it.
    using System.Linq;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    
    // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
    // If the cell already exists, returns it. 
    private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
    {
        Worksheet worksheet = worksheetPart.Worksheet;
        SheetData sheetData = worksheet.GetFirstChild<SheetData>();
        string cellReference = columnName + rowIndex;
    
        // If the worksheet does not contain a row with the specified row index, insert one.
        Row row;
        if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
        {
            row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
        }
        else
        {
            row = new Row() { RowIndex = rowIndex };
            sheetData.Append(row);
        }
    
        // If there is not a cell with the specified column name, insert one.  
        if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
        {
            return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
        }
        else
        {
            // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
            Cell refCell = null;
            foreach (Cell cell in row.Elements<Cell>())
            {
                if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                {
                    refCell = cell;
                    break;
                }
            }
    
            Cell newCell = new Cell() { CellReference = cellReference };
            row.InsertBefore(newCell, refCell);
    
            worksheet.Save();
            return newCell;
        }
    }

    public by msdn  39189  0  6  0

    GetColumnHeading

    // Given a document name, a worksheet name, and a cell name, gets the column of the cell and returns // the content of the first cell in that column.
    using System.Collections.Generic;
    using System.Linq;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using System.Text.RegularExpressions;
    
    // Given a document name, a worksheet name, and a cell name, gets the column of the cell and returns
    // the content of the first cell in that column.
    public static string GetColumnHeading(string docName, string worksheetName, string cellName)
    {
        // Open the document as read-only.
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, false))
        {
            IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);
            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.
                return null;
            }
    
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
    
            // Get the column name for the specified cell.
            string columnName = GetColumnName(cellName);
    
            // Get the cells in the specified column and order them by row.
            IEnumerable<Cell> cells = worksheetPart.Worksheet.Descendants<Cell>().Where(c => string.Compare(GetColumnName(c.CellReference.Value), columnName, true) == 0)
                .OrderBy(r => GetRowIndex(r.CellReference));
    
            if (cells.Count() == 0)
            {
                // The specified column does not exist.
                return null;
            }
    
            // Get the first cell in the column.
            Cell headCell = cells.First();
    
            // If the content of the first cell is stored as a shared string, get the text of the first cell
            // from the SharedStringTablePart and return it. Otherwise, return the string value of the cell.
            if (headCell.DataType != null && headCell.DataType.Value == CellValues.SharedString)
            {
                SharedStringTablePart shareStringPart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                SharedStringItem[] items = shareStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray();
                return items[int.Parse(headCell.CellValue.Text)].InnerText;
            }
            else
            {
                return headCell.CellValue.Text;
            }
        }
    }

    public by msdn  1405  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