Saturday, 03 July 2010 06:54
EXCEL Read Comment in C#, VB.NET
The sample demonstrates how to read comment from an excel workbook.
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\..\..\..\..\Data\CommentSample.xls");
//Initialize worksheet
Worksheet sheet = workbook.Worksheets[0];
textBox1.Text = sheet.Range["A1"].Comment.Text;
richTextBox1.Rtf = sheet.Range["A2"].Comment.RichText.RtfText;
}
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim workbook As Workbook = New Workbook()
workbook.LoadFromFile("..\..\..\..\..\..\Data\CommentSample.xls")
'Initialize worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
textBox1.Text = sheet.Range("A1").Comment.Text
richTextBox1.Rtf = sheet.Range("A2").Comment.RichText.RtfText
End Sub
Published in
Comment
Saturday, 03 July 2010 02:51
EXCEL Write Hyperlinks in C#, VB.NET
The sample demonstrates how to write hyperlinks into spreadsheet.
private void ExcelDocViewer( string fileName )
{
try
{
System.Diagnostics.Process.Start(fileName);
}
catch{}
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Range["B2"].Text = "Home page";
HyperLink hylink1 = sheet.HyperLinks.Add(sheet.Range["B3"]);
hylink1.Type = HyperLinkType.Url;
hylink1.Address = @"http:\\www.e-iceblue.com";
sheet.Range["B5"].Text = "Support";
HyperLink hylink2 = sheet.HyperLinks.Add(sheet.Range["B6"]);
hylink2.Type = HyperLinkType.Url;
hylink2.Address = "mailto:support(at)e-iceblue.com";
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
Private Sub ExcelDocViewer(ByVal fileName As String)
Try
System.Diagnostics.Process.Start(fileName)
Catch
End Try
End Sub
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim workbook As Workbook = New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Range("B2").Text = "Home page"
Dim hylink1 As HyperLink = sheet.HyperLinks.Add(sheet.Range("B3"))
hylink1.Type = HyperLinkType.Url
hylink1.Address = "http:\\www.e-iceblue.com"
sheet.Range("B5").Text = "Support"
Dim hylink2 As HyperLink = sheet.HyperLinks.Add(sheet.Range("B6"))
hylink2.Type = HyperLinkType.Url
hylink2.Address = "mailto:support(at)e-iceblue.com"
workbook.SaveToFile("Sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub
Published in
Hyperlinks
Saturday, 03 July 2010 01:46
EXCEL Read Hyperlinks in C#, VB.NET
The sample demonstrates how to read hyperlinks from spreadsheet.
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\..\..\..\..\Data\HyperlinkSample.xls");
Worksheet sheet = workbook.Worksheets[0];
textBox1.Text = sheet.HyperLinks[0].Address;
textBox2.Text = sheet.HyperLinks[1].Address;
}
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim workbook As Workbook = New Workbook()
workbook.LoadFromFile("..\..\..\..\..\..\Data\HyperlinkSample.xls")
Dim sheet As Worksheet = workbook.Worksheets(0)
textBox1.Text = sheet.HyperLinks(0).Address
textBox2.Text = sheet.HyperLinks(1).Address
End Sub
Published in
Hyperlinks
Saturday, 03 July 2010 01:14
EXCEL Wite Formulas in C#, VB.NET
The sample demonstrates how to write formulas into spreadsheet..
private void ExcelDocViewer( string fileName )
{
try
{
System.Diagnostics.Process.Start(fileName);
}
catch{}
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
int currentRow = 1;
string currentFormula = string.Empty;
sheet.SetColumnWidth(1, 32);
sheet.SetColumnWidth(2, 16);
sheet.SetColumnWidth(3, 16);
sheet.Range[currentRow++,1].Value = "Examples of formulas :";
sheet.Range[++currentRow,1].Value = "Test data:";
CellRange range = sheet.Range["A1"];
range.Style.Font.IsBold = true;
range.Style.FillPattern = ExcelPatternType.Solid;
range.Style.KnownColor = ExcelColors.LightGreen1;
range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;
//test data
sheet.Range[currentRow,2].NumberValue = 7.3;
sheet.Range[currentRow, 3].NumberValue = 5; ;
sheet.Range[currentRow, 4].NumberValue = 8.2;
sheet.Range[currentRow, 5].NumberValue = 4;
sheet.Range[currentRow, 6].NumberValue = 3;
sheet.Range[currentRow, 7].NumberValue = 11.3;
sheet.Range[++currentRow, 1].Value = "Formulas"; ;
sheet.Range[currentRow, 2].Value = "Results";
range = sheet.Range[currentRow, 1, currentRow, 2];
range.Style.Font.IsBold = true;
range.Style.KnownColor = ExcelColors.LightGreen1;
range.Style.FillPattern = ExcelPatternType.Solid;
range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;
//str.
currentFormula = "=\"hello\"";
sheet.Range[++currentRow, 1].Text = "=\"hello\"";
sheet.Range[currentRow, 2].Formula = currentFormula;
sheet.Range[currentRow, 3].Formula = "=\"" + new string(new char[] { '\u4f60', '\u597d' }) + "\"";
//int.
currentFormula = "=300";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// float
currentFormula = "=3389.639421";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
//bool.
currentFormula = "=false";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
currentFormula = "=1+2+3+4+5-6-7+8-9";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
currentFormula = "=33*3/4-2+10";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// sheet reference
currentFormula = "=Sheet1!$B$3";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// sheet area reference
currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// Functions
currentFormula = "=Count(3,5,8,10,2,34)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
currentFormula = "=NOW()";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
sheet.Range[currentRow, 2].Style.NumberFormat = "yyyy-MM-DD";
currentFormula = "=SECOND(11)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MINUTE(12)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MONTH(9)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=DAY(10)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=TIME(4,5,7)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=DATE(6,4,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=RAND()";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=HOUR(12)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MOD(5,3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=WEEKDAY(3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=YEAR(23)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=NOT(true)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=OR(true)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=AND(TRUE)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=VALUE(30)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=LEN(\"world\")";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MID(\"world\",4,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=ROUND(7,3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SIGN(4)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=INT(200)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=ABS(-1.21)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=LN(15)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=EXP(20)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SQRT(40)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=PI()";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=COS(9)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SIN(45)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MAX(10,30)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MIN(5,7)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=AVERAGE(12,45)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SUM(18,29)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=IF(4,2,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SUBTOTAL(3,Sheet1!B2:E3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
Private Sub ExcelDocViewer(ByVal fileName As String)
Try
System.Diagnostics.Process.Start(fileName)
Catch
End Try
End Sub
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim workbook As Workbook = New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim currentRow As Integer = 1
Dim currentFormula As String = String.Empty
sheet.SetColumnWidth(1, 32)
sheet.SetColumnWidth(2, 16)
sheet.SetColumnWidth(3, 16)
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Value = "Examples of formulas :"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Value = "Test data:"
Dim range As CellRange = sheet.Range("A1")
range.Style.Font.IsBold = True
range.Style.FillPattern = ExcelPatternType.Solid
range.Style.KnownColor = ExcelColors.LightGreen1
range.Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Medium
'test data
sheet.Range(currentRow,2).NumberValue = 7.3
sheet.Range(currentRow, 3).NumberValue = 5
sheet.Range(currentRow, 4).NumberValue = 8.2
sheet.Range(currentRow, 5).NumberValue = 4
sheet.Range(currentRow, 6).NumberValue = 3
sheet.Range(currentRow, 7).NumberValue = 11.3
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Value = "Formulas"
sheet.Range(currentRow, 2).Value = "Results"
range = sheet.Range(currentRow, 1, currentRow, 2)
range.Style.Font.IsBold = True
range.Style.KnownColor = ExcelColors.LightGreen1
range.Style.FillPattern = ExcelPatternType.Solid
range.Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Medium
'str.
currentFormula = "=""hello"""
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = "=""hello"""
sheet.Range(currentRow, 2).Formula = currentFormula
sheet.Range(currentRow, 3).Formula = "=""" & ChrW(20320) & ChrW(22909) + """"
'int.
currentFormula = "=300"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' float
currentFormula = "=3389.639421"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
'bool.
currentFormula = "=false"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=1+2+3+4+5-6-7+8-9"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=33*3/4-2+10"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' sheet reference
currentFormula = "=Sheet1!$B$3"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' sheet area reference
currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
' Functions
currentFormula = "=Count(3,5,8,10,2,34)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=NOW()"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
sheet.Range(currentRow, 2).Style.NumberFormat = "yyyy-MM-DD"
currentFormula = "=SECOND(11)"
currentRow = currentRow + 1
sheet.Range(currentRow, 1).Text = currentFormula
'currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MINUTE(12)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MONTH(9)"
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=DAY(10)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=TIME(4,5,7)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=DATE(6,4,2)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=RAND()"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=HOUR(12)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MOD(5,3)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=WEEKDAY(3)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=YEAR(23)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=NOT(true)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=OR(true)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=AND(TRUE)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=VALUE(30)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=LEN(""world"")"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MID(""world"",4,2)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=ROUND(7,3)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SIGN(4)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=INT(200)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=ABS(-1.21)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=LN(15)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=EXP(20)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SQRT(40)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=PI()"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=COS(9)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SIN(45)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MAX(10,30)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=MIN(5,7)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=AVERAGE(12,45)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SUM(18,29)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=IF(4,2,2)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
currentFormula = "=SUBTOTAL(3,Sheet1!B2:E3)"
sheet.Range(currentRow, 1).Text = currentFormula
currentRow = currentRow + 1
sheet.Range(currentRow, 2).Formula = currentFormula
workbook.SaveToFile("Sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub
Published in
Formulas
Saturday, 03 July 2010 01:07
EXCEL Read Formulas in C#, VB.NET
The sample demonstrates how to read formulas from spreadsheet.
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\..\..\..\..\Data\ReadFormulasSmple.xls");
Worksheet sheet = workbook.Worksheets[0];
textBox1.Text = sheet.Range["C5"].Formula;
textBox2.Text = sheet.Range["C5"].FormulaNumberValue.ToString();
}
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim workbook As Workbook = New Workbook()
workbook.LoadFromFile("..\..\..\..\..\..\Data\ReadFormulasSmple.xls")
Dim sheet As Worksheet = workbook.Worksheets(0)
textBox1.Text = sheet.Range("C5").Formula
textBox2.Text = sheet.Range("C5").FormulaNumberValue.ToString()
End Sub
Published in
Formulas
Saturday, 03 July 2010 01:00
EXCEL Data Export in C#, VB.NET
The sample demonstrates how to export the data from spreadsheet to datatable.
using System;
using System.Data;
using System.Windows.Forms;
using Spire.Xls;
namespace ExcelSheetToDataTable
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\parts.xls",ExcelVersion.Version97to2003);
//Initialize worksheet
Worksheet sheet = workbook.Worksheets[0];
DataTable dataTable = sheet.ExportDataTable();
this.dataGridView1.DataSource = dataTable;
}
}
}
Imports System.Data
Imports System.Windows.Forms
Imports Spire.Xls
Namespace ExcelSheetToDataTable
Public Partial Class Form1
Inherits Form
Public Sub New()
InitializeComponent()
End Sub
Private Sub button1_Click(sender As Object, e As EventArgs)
Dim workbook As New Workbook()
workbook.LoadFromFile("..\..\parts.xls", ExcelVersion.Version97to2003)
'Initialize worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim dataTable As DataTable = sheet.ExportDataTable()
Me.dataGridView1.DataSource = dataTable
End Sub
End Class
End Namespace
Published in
DataTable
Saturday, 03 July 2010 00:50
EXCEL Data Import in C#, VB.NET
The sample demonstrates how to import the data from datatable to spreadsheet.
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initialize worksheet
Worksheet sheet = workbook.Worksheets[0];
sheet.InsertDataTable((DataTable)this.dataGrid1.DataSource,true,2,1,-1,-1);
//Sets body style
CellStyle oddStyle = workbook.Styles.Add("oddStyle");
oddStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
oddStyle.KnownColor = ExcelColors.LightGreen1;
CellStyle evenStyle = workbook.Styles.Add("evenStyle");
evenStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
evenStyle.KnownColor = ExcelColors.LightTurquoise;
foreach( CellRange range in sheet.AllocatedRange.Rows)
{
if (range.Row % 2 == 0)
range.CellStyleName = evenStyle.Name;
else
range.CellStyleName = oddStyle.Name;
}
//Sets header style
CellStyle styleHeader = sheet.Rows[0].Style;
styleHeader.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
styleHeader.VerticalAlignment = VerticalAlignType.Center;
styleHeader.KnownColor = ExcelColors.Green;
styleHeader.Font.KnownColor = ExcelColors.White;
styleHeader.Font.IsBold = true;
sheet.Columns[sheet.AllocatedRange.LastColumn - 1].Style.NumberFormat = "\"$\"#,##0";
sheet.Columns[sheet.AllocatedRange.LastColumn - 2].Style.NumberFormat = "\"$\"#,##0";
sheet.AllocatedRange.AutoFitColumns();
sheet.AllocatedRange.AutoFitRows();
sheet.Rows[0].RowHeight = 20;
workbook.SaveToFile("sample.xls");
ExcelDocViewer( workbook.FileName );
}
private void Form1_Load(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\..\..\..\..\Data\DataTableSample.xls");
//Initialize worksheet
Worksheet sheet = workbook.Worksheets[0];
this.dataGrid1.DataSource = sheet.ExportDataTable();
}
private void ExcelDocViewer( string fileName )
{
try
{
System.Diagnostics.Process.Start(fileName);
}
catch{}
}
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim workbook As Workbook = New Workbook()
'Initialize worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.InsertDataTable(CType(Me.dataGrid1.DataSource, DataTable),True,2,1,-1,-1)
'Sets body style
Dim oddStyle As CellStyle = workbook.Styles.Add("oddStyle")
oddStyle.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
oddStyle.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
oddStyle.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
oddStyle.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
oddStyle.KnownColor = ExcelColors.LightGreen1
Dim evenStyle As CellStyle = workbook.Styles.Add("evenStyle")
evenStyle.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
evenStyle.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
evenStyle.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
evenStyle.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
evenStyle.KnownColor = ExcelColors.LightTurquoise
For Each range As CellRange In sheet.AllocatedRange.Rows
If range.Row Mod 2 = 0 Then
range.CellStyleName = evenStyle.Name
Else
range.CellStyleName = oddStyle.Name
End If
Next range
'Sets header style
Dim styleHeader As CellStyle = sheet.Rows(0).Style
styleHeader.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
styleHeader.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
styleHeader.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
styleHeader.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
styleHeader.VerticalAlignment = VerticalAlignType.Center
styleHeader.KnownColor = ExcelColors.Green
styleHeader.Font.KnownColor = ExcelColors.White
styleHeader.Font.IsBold = True
sheet.Columns(sheet.AllocatedRange.LastColumn - 1).Style.NumberFormat = """$""#,##0"
sheet.Columns(sheet.AllocatedRange.LastColumn - 2).Style.NumberFormat = """$""#,##0"
sheet.AllocatedRange.AutoFitColumns()
sheet.AllocatedRange.AutoFitRows()
sheet.Rows(0).RowHeight = 20
workbook.SaveToFile("sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim workbook As Workbook = New Workbook()
workbook.LoadFromFile("..\..\..\..\..\..\Data\DataTableSample.xls")
'Initailize worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
Me.dataGrid1.DataSource = sheet.ExportDataTable()
End Sub
Private Sub ExcelDocViewer(ByVal fileName As String)
Try
System.Diagnostics.Process.Start(fileName)
Catch
End Try
End Sub
Published in
DataTable
Saturday, 03 July 2010 00:40
EXCEL Edit Chart Sheet in C#, VB.NET
The sample demonstrates how to edit chart worksheet in an excel workbook.
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initialize worksheet
workbook.LoadFromFile("../../../../../../Data/EditChartSample.xls",true);
Worksheet sheet = workbook.Worksheets[0];
//Writes chart data
CreateChartData(sheet);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void CreateChartData(Worksheet sheet)
{
//Jun
sheet.Range["F6"].NumberValue = 6000;
sheet.Range["F7"].NumberValue = 8000;
sheet.Range["F8"].NumberValue = 9000;
sheet.Range["F9"].NumberValue = 8500;
//Aug
sheet.Range["G6"].NumberValue = 4000;
sheet.Range["G7"].NumberValue = 7000;
sheet.Range["G8"].NumberValue = 2000;
sheet.Range["G9"].NumberValue = 5000;
sheet.Range["F6:F9"].Style.NumberFormat = "\"$\"#,##0";
sheet.Range["G6:G9"].Style.NumberFormat = "\"$\"#,##0";
}
private void ExcelDocViewer( string fileName )
{
try
{
System.Diagnostics.Process.Start(fileName);
}
catch{}
}
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim workbook As Workbook = New Workbook()
'Initialize worksheet
workbook.LoadFromFile("../../../../../../Data/EditChartSample.xls",True)
Dim sheet As Worksheet = workbook.Worksheets(0)
'Writes chart data
CreateChartData(sheet)
workbook.SaveToFile("Sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub
Private Sub CreateChartData(ByVal sheet As Worksheet)
'Jun
sheet.Range("F6").NumberValue = 6000
sheet.Range("F7").NumberValue = 8000
sheet.Range("F8").NumberValue = 9000
sheet.Range("F9").NumberValue = 8500
'Aug
sheet.Range("G6").NumberValue = 4000
sheet.Range("G7").NumberValue = 7000
sheet.Range("G8").NumberValue = 2000
sheet.Range("G9").NumberValue = 5000
sheet.Range("F6:F9").Style.NumberFormat = """$""#,##0"
sheet.Range("G6:G9").Style.NumberFormat = """$""#,##0"
End Sub
Private Sub ExcelDocViewer(ByVal fileName As String)
Try
System.Diagnostics.Process.Start(fileName)
Catch
End Try
End Sub
Published in
Charts
Saturday, 03 July 2010 00:30
EXCEL Pie Charts in C#, VB.NET
The sample demonstrates how to create a pie chart in an excel workbook.
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initialize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Add a new chart worsheet to workbook
Chart chart = null;
if (checkBox1.Checked)
{
chart = sheet.Charts.Add(ExcelChartType.Pie3D);
}
else
{
chart = sheet.Charts.Add(ExcelChartType.Pie);
}
CreateChartData(sheet);
CreateChart(sheet, chart);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void CreateChart(Worksheet sheet, Chart chart)
{
//Set region of chart data
chart.DataRange = sheet.Range["B2:B5"];
chart.SeriesDataFromRange = false;
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 9;
chart.BottomRow = 25;
//Chart title
chart.ChartTitle = "Sales by year";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
Charts.ChartSerie cs = chart.Series[0];
cs.CategoryLabels = sheet.Range["A2:A5"];
cs.Values = sheet.Range["B2:B5"];
cs.DataFormat.ShowActiveValue = true;
}
private void CreateChartData(Worksheet sheet)
{
//Country
sheet.Range["A1"].Value = "Year";
sheet.Range["A2"].Value = "2002";
sheet.Range["A3"].Value = "2003";
sheet.Range["A4"].Value = "2004";
sheet.Range["A5"].Value = "2005";
//Jun
sheet.Range["B1"].Value = "Sales";
sheet.Range["B2"].NumberValue = 4000;
sheet.Range["B3"].NumberValue = 6000;
sheet.Range["B4"].NumberValue = 7000;
sheet.Range["B5"].NumberValue = 8500;
//Style
sheet.Range["A1:B1"].Style.Font.IsBold = true;
sheet.Range["A2:B2"].Style.KnownColor = ExcelColors.LightYellow;
sheet.Range["A3:B3"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["A4:B4"].Style.KnownColor = ExcelColors.LightOrange;
sheet.Range["A5:B5"].Style.KnownColor = ExcelColors.LightTurquoise;
//Border
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";
}
private void ExcelDocViewer( string fileName )
{
try
{
System.Diagnostics.Process.Start(fileName);
}
catch{}
}
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim workbook As Workbook = New Workbook()
'Initialize worksheet
workbook.CreateEmptySheets(1)
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Name = "Chart data"
sheet.GridLinesVisible = False
'Add a new chart worsheet to workbook
Dim chart As Chart = Nothing
If checkBox1.Checked Then
chart = sheet.Charts.Add(ExcelChartType.Pie3D)
Else
chart = sheet.Charts.Add(ExcelChartType.Pie)
End If
CreateChartData(sheet)
CreateChart(sheet, chart)
workbook.SaveToFile("Sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub
Private Sub CreateChart(ByVal sheet As Worksheet, ByVal chart As Chart)
'Set region of chart data
chart.DataRange = sheet.Range("B2:B5")
chart.SeriesDataFromRange = False
'Set position of chart
chart.LeftColumn = 1
chart.TopRow = 6
chart.RightColumn = 9
chart.BottomRow = 25
'Chart title
chart.ChartTitle = "Sales by year"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12
Dim cs As Charts.ChartSerie = chart.Series(0)
cs.CategoryLabels = sheet.Range("A2:A5")
cs.Values = sheet.Range("B2:B5")
cs.DataFormat.ShowActiveValue = True
End Sub
Private Sub CreateChartData(ByVal sheet As Worksheet)
'Country
sheet.Range("A1").Value = "Year"
sheet.Range("A2").Value = "2002"
sheet.Range("A3").Value = "2003"
sheet.Range("A4").Value = "2004"
sheet.Range("A5").Value = "2005"
'Jun
sheet.Range("B1").Value = "Sales"
sheet.Range("B2").NumberValue = 4000
sheet.Range("B3").NumberValue = 6000
sheet.Range("B4").NumberValue = 7000
sheet.Range("B5").NumberValue = 8500
'Style
sheet.Range("A1:B1").Style.Font.IsBold = True
sheet.Range("A2:B2").Style.KnownColor = ExcelColors.LightYellow
sheet.Range("A3:B3").Style.KnownColor = ExcelColors.LightGreen1
sheet.Range("A4:B4").Style.KnownColor = ExcelColors.LightOrange
sheet.Range("A5:B5").Style.KnownColor = ExcelColors.LightTurquoise
'Border
sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:B5").Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
sheet.Range("B2:C5").Style.NumberFormat = """$""#,##0"
End Sub
Private Sub ExcelDocViewer(ByVal fileName As String)
Try
System.Diagnostics.Process.Start(fileName)
Catch
End Try
End Sub
Published in
Charts
Saturday, 03 July 2010 00:21
EXCEL Write Images in C#, VB.NET
The sample demonstrates how to write images to spreadsheet.
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Pictures.Add(1,1,@"..\..\..\..\..\..\Data\day.jpg");
workbook.SaveToFile("sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void ExcelDocViewer( string fileName )
{
try
{
System.Diagnostics.Process.Start(fileName);
}
catch{}
}
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click
Dim workbook As Workbook = New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Pictures.Add(1,1,"..\..\..\..\..\..\Data\day.jpg")
workbook.SaveToFile("sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub
Private Sub ExcelDocViewer(ByVal fileName As String)
Try
System.Diagnostics.Process.Start(fileName)
Catch
End Try
End Sub
Published in
Images