Hi, I know that it is possible to create Excel Workbooks/Worksheets using C#. I have a sample that I am working with. However, the example does not show all the functions that I need. Does anyone know if there is a guide/tutorial for such a task? Searches in this forum and google didn't produce anything for me.
Thanks!
Steve
p.s. if you are interested, here is the code. Pretty cool stuff.
Thanks!
Steve
p.s. if you are interested, here is the code. Pretty cool stuff.
Code:
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Reflection;
namespace TestExcel
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(200, 88);
this.button1.Name = "button1";
this.button1.TabIndex = 0;
this.button1.Text = "button1";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(292, 266);
this.Controls.Add(this.button1);
this.Name = "Form1";
this.Text = "Form1";
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;
try
{
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = true; //Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
oSheet = (Excel._Worksheet)oWB.ActiveSheet; //Add table headers going cell by cell.
oSheet.Cells[1, 1] = "First Name";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "Full Name";
oSheet.Cells[1, 4] = "Salary"; //Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "D1").Font.Bold = true;
oSheet.get_Range("A1", "D1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
// Create an array to multiple values at once.
string[,] saNames = new string[5,2];
saNames[ 0, 0] = "John";
saNames[ 0, 1] = "Smith";
saNames[ 1, 0] = "Tom";
saNames[ 1, 1] = "Brown";
saNames[ 2, 0] = "Sue";
saNames[ 2, 1] = "Thomas";//Excel.Application
saNames[ 3, 0] = "Jane";
saNames[ 3, 1] = "Jones";
saNames[ 4, 0] = "Adam";
saNames[ 4, 1] = "Johnson"; //Fill A2:B6 with an array of values (First and Last Names).
//EXCEL9 use:
oSheet.get_Range("A2", "B6").Value2 = saNames;
//EXCEL10 use:
//oSheet.get_Range("A2", "B6").set_Value( Missing.Value, saNames ); //Fill C2:C6 with a relative formula (=A2 & " " & B2).
oRng = oSheet.get_Range("C2", "C6");
oRng.Formula = "=A2 & \" \" & B2"; //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = oSheet.get_Range("D2", "D6");
oRng.Formula = "=RAND()*100000";
oRng.NumberFormat = "$0.00"; //AutoFit columns A:D.
oRng = oSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit(); //Manipulate a variable number of columns for Quarterly Sales Data.
DisplayQuarterlySales(oSheet); //Make sure Excel is visible and give the user control
//of Microsoft Excel's lifetime.
oXL.Visible = true;
oXL.UserControl = true;
}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source ); MessageBox.Show( errorMessage, "Error" );
}
}
private void DisplayQuarterlySales(Excel._Worksheet oWS)
{
Excel._Workbook oWB;
Excel.Series oSeries;
Excel.Range oResizeRange;
Excel._Chart oChart;
String sMsg;
int iNumQtrs; //Determine how many quarters to display data for.
for( iNumQtrs = 4; iNumQtrs >= 2; iNumQtrs--)
{
sMsg = "Enter sales data for ";
sMsg = String.Concat( sMsg, iNumQtrs );
sMsg = String.Concat( sMsg, " quarter(s)?"); DialogResult iRet = MessageBox.Show( sMsg, "Quarterly Sales?",
MessageBoxButtons.YesNo );
if (iRet == DialogResult.Yes)
break;
} sMsg = "Displaying data for ";
sMsg = String.Concat( sMsg, iNumQtrs );
sMsg = String.Concat( sMsg, " quarter(s)." );
MessageBox.Show( sMsg, "Quarterly Sales" ); //Starting at E1, fill headers for the number of columns selected.
oResizeRange = oWS.get_Range("E1", "E1").get_Resize( Missing.Value, iNumQtrs);
oResizeRange.Formula = "=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\""; //Change the Orientation and WrapText properties for the headers.
oResizeRange.Orientation = 38;
oResizeRange.WrapText = true; //Fill the interior color of the headers.
oResizeRange.Interior.ColorIndex = 36; //Fill the columns with a formula and apply a number format.
oResizeRange = oWS.get_Range("E2", "E6").get_Resize( Missing.Value, iNumQtrs);
oResizeRange.Formula = "=RAND()*100";
oResizeRange.NumberFormat = "$0.00"; //Apply borders to the Sales data and headers.
oResizeRange = oWS.get_Range("E1", "E6").get_Resize( Missing.Value, iNumQtrs);
oResizeRange.Borders.Weight = Excel.XlBorderWeight.xlThin; //Add a Totals formula for the sales data and apply a border.
oResizeRange = oWS.get_Range("E8", "E8").get_Resize( Missing.Value, iNumQtrs);
oResizeRange.Formula = "=SUM(E2:E6)";
oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).LineStyle
= Excel.XlLineStyle.xlDouble;
oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).Weight
= Excel.XlBorderWeight.xlThick; //Add a Chart for the selected data.
oWB = (Excel._Workbook)oWS.Parent;
oChart = (Excel._Chart)oWB.Charts.Add( Missing.Value, Missing.Value,
Missing.Value, Missing.Value ); //Use the ChartWizard to create a new chart from the selected data.
oResizeRange = oWS.get_Range("E2:E6", Missing.Value ).get_Resize(
Missing.Value, iNumQtrs);
oChart.ChartWizard( oResizeRange, Excel.XlChartType.xl3DColumn, Missing.Value,
Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value );
oSeries = (Excel.Series)oChart.SeriesCollection(1);
oSeries.XValues = oWS.get_Range("A2", "A6");
for( int iRet = 1; iRet <= iNumQtrs; iRet++)
{
oSeries = (Excel.Series)oChart.SeriesCollection(iRet);
String seriesName;
seriesName = "=\"Q";
seriesName = String.Concat( seriesName, iRet );
seriesName = String.Concat( seriesName, "\"" );
oSeries.Name = seriesName;
}
oChart.Location( Excel.XlChartLocation.xlLocationAsObject, oWS.Name ); //Move the chart so as not to cover your data.
oResizeRange = (Excel.Range)oWS.Rows.get_Item(10, Missing.Value );
oWS.Shapes.Item("Chart 1").Top = (float)(double)oResizeRange.Top;
oResizeRange = (Excel.Range)oWS.Columns.get_Item(2, Missing.Value );
oWS.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;
}
}
}
