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