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 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: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
Saturday, 03 July 2010 00:10
EXCEL Read Images in C#, VB.NET
The sample demonstrates how to read images from spreadsheet.
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\..\..\..\..\Data\ImageSample.xls");
Worksheet sheet = workbook.Worksheets[0];
ExcelPicture pic = sheet.Pictures[0];
using( Form frm1 = new Form())
{
PictureBox pic1 = new PictureBox();
pic1.Image = pic.Picture;
frm1.Width = pic.Picture.Width;
frm1.Height = pic.Picture.Height;
frm1.StartPosition = FormStartPosition.CenterParent;
pic1.Dock = DockStyle.Fill;
frm1.Controls.Add(pic1);
frm1.ShowDialog();
}
}
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()
workbook.LoadFromFile("..\..\..\..\..\..\Data\ImageSample.xls")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim pic As ExcelPicture = sheet.Pictures(0)
Dim frm1 As Form = New Form()
Try
Dim pic1 As PictureBox = New PictureBox()
pic1.Image = pic.Picture
frm1.Width = pic.Picture.Width
frm1.Height = pic.Picture.Height
frm1.StartPosition = FormStartPosition.CenterParent
pic1.Dock = DockStyle.Fill
frm1.Controls.Add(pic1)
frm1.ShowDialog()
Finally
CType(frm1, IDisposable).Dispose()
End Try
End Sub
Private Sub ExcelDocViewer(ByVal fileName As String)
Try
System.Diagnostics.Process.Start(fileName)
Catch
End Try
End Sub
Published in
Images
Saturday, 03 July 2010 00:00
Excel TextAlign in C#, VB.NET
The sample demonstrates how to set text alignment in an excel workbook.
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["B1"].Text = "Text Align";
sheet.Range["B1"].Style.Font.IsBold = true;
sheet.Range["B3"].Text = "Top";
sheet.Range["B3"].Style.VerticalAlignment = VerticalAlignType.Top;
sheet.Range["B4"].Text = "Center";
sheet.Range["B4"].Style.VerticalAlignment = VerticalAlignType.Center;
sheet.Range["B5"].Text = "Bottom";
sheet.Range["B5"].Style.VerticalAlignment = VerticalAlignType.Bottom;
sheet.Range["B6"].Text = "General";
sheet.Range["B6"].Style.HorizontalAlignment = HorizontalAlignType.General;
sheet.Range["B7"].Text = "Left";
sheet.Range["B7"].Style.HorizontalAlignment = HorizontalAlignType.Left;
sheet.Range["B8"].Text = "Center";
sheet.Range["B8"].Style.HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["B9"].Text = "Right";
sheet.Range["B9"].Style.HorizontalAlignment = HorizontalAlignType.Right;
sheet.Range["B10"].Text = "Rotation 90 degree";
sheet.Range["B10"].Style.Rotation = 90;
sheet.Range["B11"].Text = "Rotation 45 degree";
sheet.Range["B11"].Style.Rotation = 45;
sheet.AllocatedRange.AutoFitColumns();
sheet.Range["B3:B5"].RowHeight = 20;
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("B1").Text = "Text Align"
sheet.Range("B1").Style.Font.IsBold = True
sheet.Range("B3").Text = "Top"
sheet.Range("B3").Style.VerticalAlignment = VerticalAlignType.Top
sheet.Range("B4").Text = "Center"
sheet.Range("B4").Style.VerticalAlignment = VerticalAlignType.Center
sheet.Range("B5").Text = "Bottom"
sheet.Range("B5").Style.VerticalAlignment = VerticalAlignType.Bottom
sheet.Range("B6").Text = "General"
sheet.Range("B6").Style.HorizontalAlignment = HorizontalAlignType.General
sheet.Range("B7").Text = "Left"
sheet.Range("B7").Style.HorizontalAlignment = HorizontalAlignType.Left
sheet.Range("B8").Text = "Center"
sheet.Range("B8").Style.HorizontalAlignment = HorizontalAlignType.Center
sheet.Range("B9").Text = "Right"
sheet.Range("B9").Style.HorizontalAlignment = HorizontalAlignType.Right
sheet.Range("B10").Text = "Rotation 90 degree"
sheet.Range("B10").Style.Rotation = 90
sheet.Range("B11").Text = "Rotation 45 degree"
sheet.Range("B11").Style.Rotation = 45
sheet.AllocatedRange.AutoFitColumns()
sheet.Range("B3:B5").RowHeight = 20
workbook.SaveToFile("Sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub
Published in
Styles
Friday, 02 July 2010 23:49
EXCEL Number Format in C#, VB.NET
The sample demonstrates how to set number formatting in an excel workbook.
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["B1"].Text = "NUMBER FORMATTING";
sheet.Range["B1"].Style.Font.IsBold = true;
sheet.Range["B3"].Text = "0";
sheet.Range["C3"].NumberValue = 1234.5678;
sheet.Range["C3"].NumberFormat = "0";
sheet.Range["B4"].Text = "0.00";
sheet.Range["C4"].NumberValue = 1234.5678;
sheet.Range["C4"].NumberFormat = "0.00";
sheet.Range["B5"].Text = "#,##0.00";
sheet.Range["C5"].NumberValue = 1234.5678;
sheet.Range["C5"].NumberFormat = "#,##0.00";
sheet.Range["B6"].Text = "$#,##0.00";
sheet.Range["C6"].NumberValue = 1234.5678;
sheet.Range["C6"].NumberFormat = "$#,##0.00";
sheet.Range["B7"].Text = "0;[Red]-0";
sheet.Range["C7"].NumberValue = -1234.5678;
sheet.Range["C7"].NumberFormat = "0;[Red]-0";
sheet.Range["B8"].Text = "0.00;[Red]-0.00";
sheet.Range["C8"].NumberValue = -1234.5678;
sheet.Range["C8"].NumberFormat = "0.00;[Red]-0.00";
sheet.Range["B9"].Text = "#,##0;[Red]-#,##0";
sheet.Range["C9"].NumberValue = -1234.5678;
sheet.Range["C9"].NumberFormat = "#,##0;[Red]-#,##0";
sheet.Range["B10"].Text = "#,##0.00;[Red]-#,##0.000";
sheet.Range["C10"].NumberValue = -1234.5678;
sheet.Range["C10"].NumberFormat = "#,##0.00;[Red]-#,##0.00";
sheet.Range["B11"].Text = "0.00E+00";
sheet.Range["C11"].NumberValue = 1234.5678;
sheet.Range["C11"].NumberFormat = "0.00E+00";
sheet.Range["B12"].Text = "0.00%";
sheet.Range["C12"].NumberValue = 1234.5678;
sheet.Range["C12"].NumberFormat = "0.00%";
sheet.Range["B3:B12"].Style.KnownColor = ExcelColors.Gray25Percent;
sheet.AutoFitColumn(2);
sheet.AutoFitColumn(3);
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("B1").Text = "NUMBER FORMATTING"
sheet.Range("B1").Style.Font.IsBold = True
sheet.Range("B3").Text = "0"
sheet.Range("C3").NumberValue = 1234.5678
sheet.Range("C3").NumberFormat = "0"
sheet.Range("B4").Text = "0.00"
sheet.Range("C4").NumberValue = 1234.5678
sheet.Range("C4").NumberFormat = "0.00"
sheet.Range("B5").Text = "#,##0.00"
sheet.Range("C5").NumberValue = 1234.5678
sheet.Range("C5").NumberFormat = "#,##0.00"
sheet.Range("B6").Text = "$#,##0.00"
sheet.Range("C6").NumberValue = 1234.5678
sheet.Range("C6").NumberFormat = "$#,##0.00"
sheet.Range("B7").Text = "0;[Red]-0"
sheet.Range("C7").NumberValue = -1234.5678
sheet.Range("C7").NumberFormat = "0;[Red]-0"
sheet.Range("B8").Text = "0.00;[Red]-0.00"
sheet.Range("C8").NumberValue = -1234.5678
sheet.Range("C8").NumberFormat = "0.00;[Red]-0.00"
sheet.Range("B9").Text = "#,##0;[Red]-#,##0"
sheet.Range("C9").NumberValue = -1234.5678
sheet.Range("C9").NumberFormat = "#,##0;[Red]-#,##0"
sheet.Range("B10").Text = "#,##0.00;[Red]-#,##0.000"
sheet.Range("C10").NumberValue = -1234.5678
sheet.Range("C10").NumberFormat = "#,##0.00;[Red]-#,##0.00"
sheet.Range("B11").Text = "0.00E+00"
sheet.Range("C11").NumberValue = 1234.5678
sheet.Range("C11").NumberFormat = "0.00E+00"
sheet.Range("B12").Text = "0.00%"
sheet.Range("C12").NumberValue = 1234.5678
sheet.Range("C12").NumberFormat = "0.00%"
sheet.Range("B3:B12").Style.KnownColor = ExcelColors.Gray25Percent
sheet.AutoFitColumn(2)
sheet.AutoFitColumn(3)
workbook.SaveToFile("Sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub
Published in
Styles
Friday, 02 July 2010 23:37
EXCEL Font Styles in C#, VB.NET
The sample demonstrates how to set font formatting in an excel workbook.
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["B1"].Text = "Font setting";
sheet.Range["B1"].Style.Font.IsBold = true;
sheet.Range["B3"].Text = "Arial";
sheet.Range["B3"].Style.Font.FontName = "Arial";
sheet.Range["B4"].Text = "Large size";
sheet.Range["B4"].Style.Font.Size = 20;
sheet.Range["B5"].Text = "Bold";
sheet.Range["B5"].Style.Font.IsBold = true;
sheet.Range["B6"].Text = "Italic";
sheet.Range["B6"].Style.Font.IsItalic = true;
sheet.Range["B7"].Text = "Superscript";
sheet.Range["B7"].Style.Font.IsSuperscript = true;
sheet.Range["B8"].Text = "Colored";
sheet.Range["B8"].Style.Font.Color = Color.FromArgb(255,125,125);
sheet.Range["B9"].Text = "Underline";
sheet.Range["B9"].Style.Font.Underline = FontUnderlineType.Single;
sheet.AutoFitColumn(2);
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("B1").Text = "Font setting"
sheet.Range("B1").Style.Font.IsBold = True
sheet.Range("B3").Text = "Arial"
sheet.Range("B3").Style.Font.FontName = "Arial"
sheet.Range("B4").Text = "Large size"
sheet.Range("B4").Style.Font.Size = 20
sheet.Range("B5").Text = "Bold"
sheet.Range("B5").Style.Font.IsBold = True
sheet.Range("B6").Text = "Italic"
sheet.Range("B6").Style.Font.IsItalic = True
sheet.Range("B7").Text = "Superscript"
sheet.Range("B7").Style.Font.IsSuperscript = True
sheet.Range("B8").Text = "Colored"
sheet.Range("B8").Style.Font.Color = Color.FromArgb(255,125,125)
sheet.Range("B9").Text = "Underline"
sheet.Range("B9").Style.Font.Underline = FontUnderlineType.Single
sheet.AutoFitColumn(2)
workbook.SaveToFile("Sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub
Published in
Styles