SpreadsheetGear - performance spreadsheet components
| | | |
Products
Downloads
Support
Company
How To Use SpreadsheetGear for .NET

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. output a spreadsheet to a client web browser?
5. use the calculation engine from a web form?
6. use the spreadsheet control in a Windows Forms Application?
7. disable or replace the WorkbookView control's context menu?
8. consolidate ranges using the IRange.Copy method?
9. define my own custom functions?
10. include a chart in my spreadsheet reports?
11. change the range that my chart series are linked to?
12. add a picture to my spreadsheet report?
13. use outline levels to group and summarize a range?
14. read a workbook from a stream or write a workbook to a stream?
15. add a hyperlink to my worksheet?
16. add a new worksheet to my workbook?
17. get an IRange from a defined name?
18. refer to an IRange when creating a formula?
19. merge a range of cells?
20. set a border on a range of cells?
21. set the Print Area and Print Titles for a worksheet?
22. 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 output a spreadsheet to a client web browser?
See Excel Report in Browser 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 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 for .NET 2007 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 Excel Report with Chart or DataTable to Excel Workbook with Chart 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 change the range that my chart series are linked to?
See Excel Report with Chart Linked to Defined Name 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 for .NET
2007 V3.1 Now Available
See What's New and
download the Licensed or
Evaluation version Now.
Spreadsheet Component
Industry Veterans
"For more than twenty years, we have been creating and leading the development of high performance 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
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
SpreadsheetGear for .NET
Robust & Comprehensive
We really love SpreadsheetGear for .NET. The comprehensive and robust set of worksheet functions ensures that our customers are able to fully leverage their Excel knowledge in our product.

Andreas Lipphardt
XLCubed Ltd
Hessen Germany
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
You Have an
Excellent Product
"You have an excellent product. I've finished my testing and was able to rewrite a program using VB.NET and your control. I wrote the previous version 4 years ago using VB6 which employed the Excel COM object. The old program generated about 2100 excel sheets in 8 hours. With your control it now takes 7 minutes!

I also wrote a web form in ASP.NET VB using your control. This was also previously a VB6 / Excel app. This one went from 36 minutes to less than a minute.

I'm having no trouble getting this purchase approved!"

Mark Hitchcock
Manager of Information Technology
Administrative Concepts, Inc.
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
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
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
Every Day that Goes By
I Am More Amazed
I must say that everyday that goes by I am more amazed with your component. It’s easy to use but still powerful. No more problems using Excel workbooks in the .NET environment.

You have spared me about 3 or 4 months of work with your excellent tool. I also must say that I’m using many other components from other companies and no one has proven to be as fast and effective as you in solving a support request.

Pedro Horta
IT Analyst / Programmer
OmnicomMediaGroup
Portugal
Performance, Flexibility
Priceless Support
"We started to use SpreadsheetGear for .NET almost two years ago when the performance of other controls was becoming problematic. We needed a control that could handle large workbooks with complex formulas to be able to apply Monte Carlo simulation to financial statements. Since that time SpreadsheetGear for .NET has become our tool of choice not only for the performance but also for the flexibility. But most of all, the technical support we receive from SpreadsheetGear LLC is priceless; it has always been fast and accurate with the vision to arrive at the desired solution."

Frédérick Faucher
Analyste Financier
Solutions Modex Inc.
Montréal, Canada
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
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
Reduced Time From
20 Minutes to 4 Seconds
"We integrated SpreadsheetGear for .NET with an existing application with about two days work and it reduced the time it takes to generate a critical daily report from 20 minutes to 4 seconds. Thanks for making my team look like miracle workers!"

Luke Melia, Software
Development Manager
Oxygen Media, New York
Excellent Product
And Service
"Thank you for all your help during the trial period.

Your excellent product and service is what convinced us to buy this component."

Etienne Demers
OceanLogics, Canada
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
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
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
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
Copyright © 2003-2008 SpreadsheetGear LLC. All Rights Reserved.
SpreadsheetGear and Spreadsheet Gear are trademarks 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.