Visit Our Facebook Page
Visit Our Google Plus Page
Visit Our Twitter Page
What's New
ASP.NET, Windows Forms and Silverlight SpreadsheetGear Samples
Purchase SpreadsheetGear
Download SpreadsheetGear

How To Use SpreadsheetGear 2012

Search How-To's:

(You can search for multiple words and place phrases in double-quotes)
 
Show All Show All    Hide All Hide All

How do I...

  • copy a DataTable or DataSet to an Excel workbook?
    See DataTable to Excel Workbook or DataSet to Excel Workbook with Formats and Formulas on the Excel Reporting Samples page.

    Note: Excel or an Excel compatible spreadsheet application must be installed on the client in order for the spreadsheet to display.
  • get a DataSet from a range of values in my workbook?

    // Create a workbook from an Excel file
    IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook("myfile.xls");

    // Get a DataSet from an existing defined name        
    DataSet dataSet = workbook.GetDataSet("mydefinedname",
        SpreadsheetGear.Data.GetDataFlags.FormattedText);
  • display a range of data in an ASP.NET DataGrid Control?
    See Excel to DataGrid on the DataGrid Samples page.
  • use the calculation engine from a web form?
    See the calculation samples on the Calculations Samples page.
  • use the spreadsheet control in a Windows Forms Application?
    SpreadsheetGear 2012 comes with the SpreadsheetGear Explorer, a helpful Windows Forms Application sample soluton for Visual Studio 2005 / 2008 / 2010 / 2012 / 2013. See SpreadsheetGear Explorer Sample Solutions for instructions on running the SpreadsheetGear Explorer.
  • disable or replace the Windows Forms WorkbookView control's context menu?

    // Disable the context menu
    workbookView1.ContextMenuStrip = null;

    // Replace the context menu
    workbookView1.ContextMenuStrip = myContextMenuStrip;
  • consolidate ranges using the IRange.Copy method?
    See Excel Workbook Consolidation on the DataGrid Samples page.
  • define my own custom functions?
    See Custom Functions on the Calculations Samples page.
  • include a chart in my spreadsheet reports?
    See the Excel Charting Samples page.

    Note: Excel or an Excel compatible spreadsheet application must be installed on the client in order for the spreadsheet to display.
  • change the source data range that my chart is linked to?
    See the Basic Chart sample on the Excel Charting Samples page.

    Note: Excel or an Excel compatible spreadsheet application must be installed on the client in order for the spreadsheet to display.
  • add a picture to my spreadsheet report?
    See Excel Report with Picture on the Excel Reporting Samples page.

    Note: Excel or an Excel compatible spreadsheet application must be installed on the client in order for the spreadsheet to display.
  • use outline levels to group and summarize a range?
    See Group, Outline and Summarize to Excel on the Excel Reporting Samples page.
  • read a workbook from a stream or write a workbook to a stream?

    public static IWorkbook OpenWorkbook(System.IO.Stream stream)
    {
        // Read a workbook from a stream and return it.
        return SpreadsheetGear.Factory.GetWorkbookSet()
            .Workbooks.OpenFromStream(stream);
    }

    public static void SaveWorkbook(System.IO.Stream stream)
    {
        // Create a workbook and write it to the stream.
        IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
        workbook.SaveToStream(
            stream, SpreadsheetGear.FileFormat.Excel8);
    }
  • add a hyperlink to my worksheet?

    // Create a workbook and get the first worksheet.
    IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
    IWorksheet worksheet = workbook.Worksheets["Sheet1"];

    // Add a hyperlink to cell A1.
    worksheet.Hyperlinks.Add(worksheet.Cells["A1"],
        @"http://www.spreadsheetgear.com",
        null, "My Screen Tip", "My Hyperlink Text");

    Note: Hyperlinks can be linked to web pages, email addresses, workbook files and references to ranges in the current workbook. Hyperlinks can also be created by using the HYPERLINK function.
  • add a new worksheet to my workbook?

    // Create a workbook.
    IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();

    // Get the first default worksheet, name it and add a formula.
    IWorksheet worksheet = workbook.Worksheets[0];
    worksheet.Name = "MyFirstSheet";
    worksheet.Cells["A1"].Value = 123.456;

    // Add a new 2nd worksheet, name it and add a formula.
    worksheet = workbook.Worksheets.Add();
    worksheet.Name = "MySecondSheet";
    worksheet.Cells["A1"].Formula = "=MyFirstSheet!A1 * 2";
  • get an IRange from a defined name?

    // Create a workbook.
    IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();

    // Get the first worksheet and name it
    IWorksheet mySheet = workbook.Worksheets[0];
    mySheet.Name = "MySheet";

    // Create a defined name which references A1.
    INames names = workbook.Names;
    names.Add("MyName", "=MySheet!$A$1");

    // Get the IRange for the defined name.
    IRange cell = names["MyName"].RefersToRange;
  • refer to an IRange when creating a formula?

    // Create a workbook and get the first worksheet
    IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
    IWorksheet worksheet = workbook.Worksheets["Sheet1"];

    // Get an IRange and set up random values
    IRange rangeValues = worksheet.Range["A1:A10"];
    rangeValues.Formula = "=RAND() * 10000";

    // Get an IRange and add a formula to sum the values
    IRange rangeFormula = worksheet.Range["A11"];
    rangeFormula.Formula = "=SUM(" + rangeValues.Address + ")";

    // Output the calculated value
    System.Console.WriteLine("Sum = " + rangeFormula.Text);
  • merge a range of cells?

    // Create a workbook and get the first worksheet
    IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
    IWorksheet worksheet = workbook.Worksheets["Sheet1"];

    // Merge a range of cells
    worksheet.Cells["A1:D2"].Merge();
  • set a border on a range of cells?

    // Create a workbook and get the first worksheet
    IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
    IWorksheet worksheet = workbook.Worksheets["Sheet1"];

    // Set the bottom border of a range of cells
    IRange range = worksheet.Cells["A1:D1"];
    IBorder border = range.Borders[SpreadsheetGear.BordersIndex.EdgeBottom];
    border.LineStyle = SpreadsheetGear.LineStyle.Continous;
    border.Color = SpreadsheetGear.Colors.Blue;
    border.Weight = SpreadsheetGear.BorderWeight.Thick;
  • set the Print Area and Print Titles for a worksheet?

    // Create a workbook and get the first worksheet PageSetup
    IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
    IWorksheet worksheet = workbook.Worksheets["Sheet1"];
    IPageSetup pageSetup = worksheet.PageSetup;

    // Set the Print Area
    pageSetup.PrintArea = "Sheet1!$B$2:$F$20";

    // Set the Print Titles
    pageSetup.PrintTitleRows = "Sheet1!$1:$1";
    pageSetup.PrintTitleColumns = "Sheet1!$A:$A";
  • set and retrieve cell values as arrays of values?

    // Create a workbook and get the first worksheet
    IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
    IWorksheet worksheet = workbook.Worksheets["Sheet1"];

    // Create a 10 row by 2 column array of values
    object[,] values = new object[10, 2];
    for (int i = 0; i < 10; i++)
    {
        values[i, 0] = "Row=" + i + " Col=0";
        values[i, 1] = "Row=" + i + " Col=1";
    }

    // Set the values in the worksheet
    // Notice the range "A1:B10" has to match the size of the array
    worksheet.Cells["A1:B10"].Value = values;

    // Get the values from the worksheet
    object[,] retVals = (object[,])worksheet.Cells["A1:B10"].Value;