Saturday, 03 July 2010 10:17
EXCEL Pagesetup in C#, VB.NET
The sample demonstrates how to work with page setup 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.GridLinesVisible = false;
//Writes sample data
CreateSampleData(sheet);
//Sets page setup properties
sheet.PageSetup.Orientation = PageOrientationType.Landscape;
sheet.PageSetup.PaperSize = PaperSizeType.PaperA3;
//Sets page break
sheet.VPageBreaks.Add(sheet.Range["A5"]);
sheet.HPageBreaks.Add(sheet.Range["C6"]);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void CreateSampleData(Worksheet sheet)
{
//Country
sheet.Range["A1"].Value = "Country";
sheet.Range["A2"].Value = "Cuba";
sheet.Range["A3"].Value = "Mexico";
sheet.Range["A4"].Value = "France";
sheet.Range["A5"].Value = "German";
//Jun
sheet.Range["B1"].Value = "Jun";
sheet.Range["B2"].NumberValue = 6000;
sheet.Range["B3"].NumberValue = 8000;
sheet.Range["B4"].NumberValue = 9000;
sheet.Range["B5"].NumberValue = 8500;
//Aug
sheet.Range["C1"].Value = "Aug";
sheet.Range["C2"].NumberValue = 3000;
sheet.Range["C3"].NumberValue = 2000;
sheet.Range["C4"].NumberValue = 2300;
sheet.Range["C5"].NumberValue = 4200;
//Style
sheet.Range["A1:C1"].Style.Font.IsBold = true;
sheet.Range["A2:C2"].Style.KnownColor = ExcelColors.LightYellow;
sheet.Range["A3:C3"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["A4:C4"].Style.KnownColor = ExcelColors.LightOrange;
sheet.Range["A5:C5"].Style.KnownColor = ExcelColors.LightTurquoise;
//Border
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:C5"].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.GridLinesVisible = False
'Writes sample data
CreateSampleData(sheet)
'Sets page setup properties
sheet.PageSetup.Orientation = PageOrientationType.Landscape
sheet.PageSetup.PaperSize = PaperSizeType.PaperA3
'Sets page break
sheet.VPageBreaks.Add(sheet.Range("A5"))
sheet.HPageBreaks.Add(sheet.Range("C6"))
workbook.SaveToFile("Sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub
Private Sub CreateSampleData(ByVal sheet As Worksheet)
'Country
sheet.Range("A1").Value = "Country"
sheet.Range("A2").Value = "Cuba"
sheet.Range("A3").Value = "Mexico"
sheet.Range("A4").Value = "France"
sheet.Range("A5").Value = "German"
'Jun
sheet.Range("B1").Value = "Jun"
sheet.Range("B2").NumberValue = 6000
sheet.Range("B3").NumberValue = 8000
sheet.Range("B4").NumberValue = 9000
sheet.Range("B5").NumberValue = 8500
'Aug
sheet.Range("C1").Value = "Aug"
sheet.Range("C2").NumberValue = 3000
sheet.Range("C3").NumberValue = 2000
sheet.Range("C4").NumberValue = 2300
sheet.Range("C5").NumberValue = 4200
'Style
sheet.Range("A1:C1").Style.Font.IsBold = True
sheet.Range("A2:C2").Style.KnownColor = ExcelColors.LightYellow
sheet.Range("A3:C3").Style.KnownColor = ExcelColors.LightGreen1
sheet.Range("A4:C4").Style.KnownColor = ExcelColors.LightOrange
sheet.Range("A5:C5").Style.KnownColor = ExcelColors.LightTurquoise
'Border
sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:C5").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
Page Setup
Saturday, 03 July 2010 10:01
EXCEL Group in C#, VB.NET
The sample demonstrates how to create group 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.GridLinesVisible = false;
//Writes Sample data
CreateSampleData(sheet);
sheet.GroupByRows(1,5,false);
sheet.GroupByColumns(1,3,false);
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
}
private void CreateSampleData(Worksheet sheet)
{
//Country
sheet.Range["A1"].Value = "Country";
sheet.Range["A2"].Value = "Cuba";
sheet.Range["A3"].Value = "Mexico";
sheet.Range["A4"].Value = "France";
sheet.Range["A5"].Value = "German";
//Jun
sheet.Range["B1"].Value = "Jun";
sheet.Range["B2"].NumberValue = 6000;
sheet.Range["B3"].NumberValue = 8000;
sheet.Range["B4"].NumberValue = 9000;
sheet.Range["B5"].NumberValue = 8500;
//Aug
sheet.Range["C1"].Value = "Aug";
sheet.Range["C2"].NumberValue = 3000;
sheet.Range["C3"].NumberValue = 2000;
sheet.Range["C4"].NumberValue = 2300;
sheet.Range["C5"].NumberValue = 4200;
//Style
sheet.Range["A1:C1"].Style.Font.IsBold = true;
sheet.Range["A2:C2"].Style.KnownColor = ExcelColors.LightYellow;
sheet.Range["A3:C3"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["A4:C4"].Style.KnownColor = ExcelColors.LightOrange;
sheet.Range["A5:C5"].Style.KnownColor = ExcelColors.LightTurquoise;
//Border
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:C5"].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.GridLinesVisible = False
'Writes Sample data
CreateSampleData(sheet)
sheet.GroupByRows(1,5,False)
sheet.GroupByColumns(1,3,False)
workbook.SaveToFile("Sample.xls")
ExcelDocViewer(workbook.FileName)
End Sub
Private Sub CreateSampleData(ByVal sheet As Worksheet)
'Country
sheet.Range("A1").Value = "Country"
sheet.Range("A2").Value = "Cuba"
sheet.Range("A3").Value = "Mexico"
sheet.Range("A4").Value = "France"
sheet.Range("A5").Value = "German"
'Jun
sheet.Range("B1").Value = "Jun"
sheet.Range("B2").NumberValue = 6000
sheet.Range("B3").NumberValue = 8000
sheet.Range("B4").NumberValue = 9000
sheet.Range("B5").NumberValue = 8500
'Aug
sheet.Range("C1").Value = "Aug"
sheet.Range("C2").NumberValue = 3000
sheet.Range("C3").NumberValue = 2000
sheet.Range("C4").NumberValue = 2300
sheet.Range("C5").NumberValue = 4200
'Style
sheet.Range("A1:C1").Style.Font.IsBold = True
sheet.Range("A2:C2").Style.KnownColor = ExcelColors.LightYellow
sheet.Range("A3:C3").Style.KnownColor = ExcelColors.LightGreen1
sheet.Range("A4:C4").Style.KnownColor = ExcelColors.LightOrange
sheet.Range("A5:C5").Style.KnownColor = ExcelColors.LightTurquoise
'Border
sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 128)
sheet.Range("A1:C5").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
Group