Thursday, 01 July 2010 14:22
EXCEL Interior in C#, VB.NET
The sample demonstrates how to write excel 2007 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]; workbook.Version = ExcelVersion.Version2007; int maxColor = Enum.GetValues(typeof(ExcelColors)).Length; Random random = new Random((int)System.DateTime.Now.Ticks); for (int i = 2; i < 40; i++) { ExcelColors backKnownColor = (ExcelColors)(random.Next(1,maxColor / 2)); sheet.Range["A1"].Text = "Color Name"; sheet.Range["B1"].Text = "Red"; sheet.Range["C1"].Text = "Green"; sheet.Range["D1"].Text = "Blue"; sheet.Range["E1:K1"].Merge(); sheet.Range["E1:K1"].Text = "Gradient"; sheet.Range["A1:K1"].Style.Font.IsBold = true; sheet.Range["A1:K1"].Style.Font.Size = 11; string colorName = backKnownColor.ToString(); sheet.Range[string.Format("A{0}",i)].Text = colorName; sheet.Range[string.Format("B{0}", i)].Text = workbook.GetPaletteColor(backKnownColor).R.ToString(); sheet.Range[string.Format("C{0}", i)].Text = workbook.GetPaletteColor(backKnownColor).G.ToString(); sheet.Range[string.Format("D{0}", i)].Text = workbook.GetPaletteColor(backKnownColor).B.ToString(); sheet.Range[string.Format("E{0}:K{0}",i)].Merge(); sheet.Range[string.Format("E{0}:K{0}", i)].Text = colorName; sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.FillPattern = ExcelPatternType.Gradient; sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.BackKnownColor = backKnownColor; sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.ForeKnownColor = ExcelColors.White; sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.GradientStyle = GradientStyleType.Vertical; sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.GradientVariant = GradientVariantsType.ShadingVariants1; } sheet.AutoFitColumn(1); workbook.SaveToFile("Sample.xlsx"); 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 New Workbook() Dim sheet As Worksheet = workbook.Worksheets(0) workbook.Version = ExcelVersion.Version2007 Dim maxColor As Integer = System.Enum.GetValues(GetType(ExcelColors)).Length Dim random As New Random() For i As Integer = 2 To 39 Dim backKnownColor As ExcelColors = CType(random.Next(1,maxColor \ 2), ExcelColors) sheet.Range("A1").Text = "Color Name" sheet.Range("B1").Text = "Red" sheet.Range("C1").Text = "Green" sheet.Range("D1").Text = "Blue" sheet.Range("E1:K1").Merge() sheet.Range("E1:K1").Text = "Gradient" sheet.Range("A1:K1").Style.Font.IsBold = True sheet.Range("A1:K1").Style.Font.Size = 11 Dim colorName As String = backKnownColor.ToString() sheet.Range(String.Format("A{0}",i)).Text = colorName sheet.Range(String.Format("B{0}", i)).Text = workbook.GetPaletteColor(backKnownColor).R.ToString() sheet.Range(String.Format("C{0}", i)).Text = workbook.GetPaletteColor(backKnownColor).G.ToString() sheet.Range(String.Format("D{0}", i)).Text = workbook.GetPaletteColor(backKnownColor).B.ToString() sheet.Range(String.Format("E{0}:K{0}",i)).Merge() sheet.Range(String.Format("E{0}:K{0}", i)).Text = colorName sheet.Range(String.Format("E{0}:K{0}", i)).Style.Interior.FillPattern = ExcelPatternType.Gradient sheet.Range(String.Format("E{0}:K{0}", i)).Style.Interior.Gradient.BackKnownColor = backKnownColor sheet.Range(String.Format("E{0}:K{0}", i)).Style.Interior.Gradient.ForeKnownColor = ExcelColors.White sheet.Range(String.Format("E{0}:K{0}", i)).Style.Interior.Gradient.GradientStyle = GradientStyleType.Vertical sheet.Range(String.Format("E{0}:K{0}", i)).Style.Interior.Gradient.GradientVariant = GradientVariantsType.ShadingVariants1 Next i sheet.AutoFitColumn(1) workbook.SaveToFile("Sample.xlsx") ExcelDocViewer(workbook.FileName) End Sub
Published in
Excel2007