SpreadsheetGear - performance spreadsheet components
| | | |
Products
Samples
Downloads
Support
Company
How To Use SpreadsheetGear 2009

How do I...
1. copy a DataTable or DataSet to an Excel workbook?
2. get a DataSet from a range of values in my workbook?
3. display a range of data in an ASP.NET DataGrid Control?
4. use the calculation engine from a web form?
5. use the spreadsheet control in a Windows Forms Application?
6. disable or replace the WorkbookView control's context menu?
7. consolidate ranges using the IRange.Copy method?
8. define my own custom functions?
9. include a chart in my spreadsheet reports?
10. change the source data range that my chart is linked to?
11. add a picture to my spreadsheet report?
12. use outline levels to group and summarize a range?
13. read a workbook from a stream or write a workbook to a stream?
14. add a hyperlink to my worksheet?
15. add a new worksheet to my workbook?
16. get an IRange from a defined name?
17. refer to an IRange when creating a formula?
18. merge a range of cells?
19. set a border on a range of cells?
20. set the Print Area and Print Titles for a worksheet?
21. set and retrieve cell values as arrays of values?


  
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.
  
How do I 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);
  
How do I display a range of data in an ASP.NET DataGrid Control?
See Excel to DataGrid on the DataGrid Samples page.
  
How do I use the calculation engine from a web form?
See the calculation samples on the Calculations Samples page.
  
How do I use the spreadsheet control in a Windows Forms Application?
SpreadsheetGear 2009 comes with the SpreadsheetGear Explorer, a helpful Windows Forms Application sample soluton for Visual Studio 2005 and Visual Studio 2008. See Windows Forms Spreadsheet Control Samples for instructions on running the SpreadsheetGear Explorer.
  
How do I disable or replace the WorkbookView control's context menu?
// Disable the context menu
workbookView1.ContextMenuStrip = null;

// Replace the context menu
workbookView1.ContextMenuStrip = myContextMenuStrip;
  
How do I consolidate ranges using the IRange.Copy method?
See Excel Workbook Consolidation on the DataGrid Samples page.
  
How do I define my own custom functions?
See Custom Functions on the Calculations Samples page.
  
How do I 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.
  
How do I 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.
  
How do I 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.
  
How do I use outline levels to group and summarize a range?
See Group, Outline and Summarize to Excel on the Excel Reporting Samples page.
  
How do I 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.XLS97);
}
  
How do I 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.
  
How do I 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";
  
How do I 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;
  
How do I 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);
  
How do I 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();
  
How do I 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 = System.Drawing.Color.Blue;
border.Weight = SpreadsheetGear.BorderWeight.Thick;
  
How do I 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";
  
How do I 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;
SpreadsheetGear 2009
Now Available
NEW!  93 Analysis Toolpak functions, CSV and text files, auto fill, goal seeking, data series, bubble charts, chart axis titles, gradients, rotated text and more.  Download the Licensed version or the FREE Evaluation Now.
Microsoft Chooses
SpreadsheetGear for .NET
"After carefully evaluating SpreadsheetGear, Excel Services, and other 3rd party options, we ultimately chose SpreadsheetGear for .NET because it is the best fit for MSN Money."

Chris Donohue
Program Manager
MSN Money
Spreadsheet Component
Industry Veterans
"Our key developers have over fifty years of combined experience developing high performance commercially available spreadsheet technology which is used by most of the Fortune 500."

Joe Erickson
Founder and CEO
SpreadsheetGear LLC
Over 100 Times Faster
Than Other Controls
"We just wanted you to know how thrilled we are by the performance we are seeing with SpreadsheetGear for .NET. A 6,000 KB Excel workbook is loading into the WorkbookView control in less than a second. This is over 100 times faster than other controls we’ve tested. That difference makes it possible for us to develop and release a viable product with the Microsoft .NET Framework."

Amy Tate
Chief .NET Architect
Applied OLAP, Inc.
Huntsville, Alabama
Vision and Ability for Advanced Spreadsheets
September 20, 2005 - "With SpreadsheetGear, we finally have a company that understands our requirements in regards to supporting the Microsoft Excel file format in the Microsoft .NET Framework. SpreadsheetGear has the vision and the ability to provide the advanced spreadsheet functionality our customers expect in our products."

Tim Tow
President
Applied OLAP, Inc.
Former Microsoft Excel MVP
My Boss Thinks
I'm a Genius
"Just wanted to give you a little good feedback on your product. I'm a programmer for a small company in Norway and we needed to create an Excel report for one of our customers. So I purchased a license for your wonderful product and it took me about 10 minutes to get the report from a DataTable into a finished Excel document!

My boss thinks I'm a genius, but hey; the credit should go to your product. At least half of it.

Cheers!"

Hans Olav Stjernholm
Chief Developer
ZapDance as, Norway
First Rate Support
Superior Control
"Your support is first rate. We always get an answer within a few minutes of any request or question. This support, combined with the exceptional speed and ease of use of your superior control enables me to create complex spreadsheet solutions that work in a few minutes instead of days. Keep up the excellent work!"

Greg Newman
WSFS Bank
Newark DE
SpreadsheetGear for .NET
A Pleasure to Work With
"SpreadsheetGear for .NET is truly a pleasure to work with. I was up and running within the first day. It is the best spreadsheet generator out on the market. Performance, Ease of Use, and Full Control of the generation process sold me. I no longer have to be concerned with memory leaks, hung processes, COM Interop and slow performance. Our users demand cosmetically appealing reports without additional work. I can build templates at will, with complete control to freeze panes, page setup, cell formats, etc., etc. The old days of pre-building templates are gone. I cannot think of anything this product cannot do."

Todd Dickard
TA Billing Systems Mgr.
TravelCenters of America
Creating Excel Reports
Has Never Been Easier
"I really am happy with SpreadsheetGear for .NET. It works fantastic, very smooth to handle. Creating Excel Reports has never been easier for me!"

Wolfgang Kamir
Softwaretailor
Baden, Lower Austria
Fantastic -- From Excel
To ASP.NET Dashboard
in a Single Afternoon
"Fantastic evaluation -- I started with a customer’s Excel workbook with a dashboard containing variable cells and a chart. Using SpreadsheetGear, and your Dashboard from 1693 Analytics sample as a template, I created my own ASP.NET page to display our client’s dashboard, and update based on the selection of parameters which get plugged into the variable cells. And I got this working in a single afternoon."

Andy Hofer
Directory of Development
NST Systems, Inc.
Stamford, CT
SpreadsheetGear 2009
In a League by Itself
"SpreadsheetGear 2009 is Fantastic! I hate to think of NOT having SpreadsheetGear for even one day. These new capabilities just propelled this control way-way past any competition, of which you have none IMHO.

As a programmer, I respect the amount of work, etc., that goes into a product that works well and as advertised. We have been using SpreadsheetGear since 2006 and it has saved us countless hours, headaches and $$$. We have had no recurring crashes since we removed all Office COM objects and replaced them with SpreadsheetGear. It seems that everything runs a lot smoother and much faster.

SpreadsheetGear is in a league all by itself."

Greg Newman
Senior Software Engineer
WSFS Cash Connect
Above and Beyond Support, Exceptional
"Although there was an initial issue related to date formatting associated with a cultural problem, I found that SpreadsheetGear provided myself, and ultimately the bank, with above and beyond support, providing us with the necessary fix for the issue within 2 days.  This is nothing less than exceptional, and I for one would like to thank SpreadsheetGear for their tenacity, enthusiasm and dedication to their product."

David Lashley
IT Development Manager
Commercial Bank of Qatar
SpreadsheetGear - The
Best I Have Encountered
"The sample code you provided has worked very well indeed. Many thanks for your help. As a developer of bespoke software I can honestly say that of all the components my company has ever used, SpreadsheetGear is the best that I have encountered."

Peter Rose, Director
TEKenable, Ltd.
Dublin, Ireland
SpreadsheetGear Kills
The Competition
"I must say that in terms of structure and completeness, SpreadsheetGear kills the competition."

Mark Keogh, Technical Lead
BCC AdSystems
Sydney, Australia
I'm Impressed With the
Ease of Use and Speed
"I'm impressed with the ease of use and speed of SpreadsheetGear for .NET in rendering large workbooks from a web page. Congrats for your work!"

José M. Marcenaro
Tercer Planeta
Buenos Aires, Argentina
Unmatched Speed
and Ease of Coding
Make it a Winner
SpreadsheetGear is a great product. The speed is unmatched and the ease of coding makes it a winner.

Steve Cruickshank
Redwood Software
SpreadsheetGear Joins
Microsoft VSIP Program
August 23, 2005 - "We welcome SpreadsheetGear to the Microsoft Visual Studio Industry Partner Program. The improved ability to create, modify, calculate, read and write Microsoft Excel (versions 97 and higher) workbooks will allow our mutual customers to create a broad range of solutions in a rich and familiar form."

Nick Abbott
Group Manager
.NET Developer Product Marketing Group
Microsoft Corp.

Microsoft VSIP Program
Fantastically Powerful
Very Easy To Use
"SpreadsheetGear for .NET is a fantastically powerful spreadsheet component which is very easy to use and expand. We delivered our mandatory customer requirements well before time so we had plenty of time to implement the nice-to-have requirements nobody ever manages to deliver."

Simon Black
Software Architect
Nokia
SpreadsheetGear for .NET
So Much Easier
"I have one big problem with Spreadsheetgear for .NET, my billable hours are going to go down because your product is so much easier to use than a web grid. Yikes!

I sure wish I had found your product a couple of years ago. And converting my webgrid code over to SpreadsheetGear for .NET is going very quickly because all of the logic is already coded and I just have to write to the spreadsheet.

I really like not having to insert columns and rows when I need them. I really like your product so far."

Bruce Hemmerich
Manager - Business and Technical Solutions
KeyChainData, LLC
ASP.NET and
SpreadsheetGear
A Match Made in Heaven
"ASP.NET and Microsoft Excel is a dangerous combination. ASP.NET and SpreadsheetGear is a match made in heaven. When you need your web or Windows app to interact with Excel files with lightening speed and no COM crashes, SpreadsheetGear for .NET is what you use. This product truly separates the men from the boys in the spreadsheet control marketplace."

Robbe Morris
Microsoft MVP - C#
Co-founder of EggHeadCafe.com and former Gartner Sr. Software Engineer
Your Quick Responses...
You Continue to Impress
"Your quick responses to queries are one of the original reasons why we opted for your product, and you continue to impress us with the speed at which questions and issues are dealt with."

Justin Blackwell
Volume Design Ltd.
Berkshire, United Kingdom
Copyright © 2003-2009 SpreadsheetGear LLC. All Rights Reserved.
SpreadsheetGear® is a registered trademark and Spreadsheet Gear is a trademark of SpreadsheetGear LLC.
Microsoft, Microsoft Excel, Visual Studio and the Visual Studio logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.