Answered by:
SpreadsheetLight: How to set foreground/background color & other issues

Question
-
1) i am using SpreadsheetLight library and i like to know how could i set row color red or yellow ?
2) also tell me how could i set color range wise say Range["A1:Z1"] ?
3) how to apply format cell range wise ?
sheet.Range[DataRangeCoordinate].NumberFormat = "#,##0.000;[Red](-#,##0.000);#,##0.000";
the above code is devexpress spreadsheet related. so how to do the same when working with SpreadsheetLight ?
4) how to iterate in all cell value with in For loop ?
when i am using dev express spreadsheet grid then i use below code to set back & fore color
sheet.Range["A1:Z1"].Font.Color = Color.IndianRed;
sheet.Range["A1:Z1"].Fill.BackgroundColor = Color.LightGray;
sheet.Range["A1:Z1"].Style.Font.Bold = true;5) How to set column width for all column ?
6) How to set autofit all columns ?
7) i am getting error when i am trying to create
CreateStyle
my code as followsusing DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Spreadsheet; using SpreadsheetLight; SLStyle style1 = sl.CreateStyle(); style.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.IndianRed, System.Drawing.Color.LightGray); sl.SetCellStyle(1, 0, style1);
i have installed latest version of OpenXml from Nuget.
please help me with code sample. thanks
Thursday, December 27, 2018 2:55 PM
Answers
-
Now issue fixed and working code added here.
Install-Package DocumentFormat.OpenXml -Version 2.8.1
private void button1_Click(object sender, EventArgs e) { Random rand = new Random(); System.Data.DataTable dt = new System.Data.DataTable(); dt.Columns.Add("Product", typeof(string)); dt.Columns.Add("IP Address", typeof(string)); dt.Columns.Add("Date (UTC)", typeof(DateTime)); dt.Columns.Add("Size (MB)", typeof(double)); dt.Columns.Add("Cost", typeof(decimal)); for (int i = 0; i < 20; ++i) { dt.Rows.Add(string.Format("Prod{0}", i+1), string.Format("{0}.{1}.{2}.{3}", rand.Next(256), rand.Next(256), rand.Next(256), rand.Next(256)), DateTime.UtcNow.AddDays(rand.NextDouble() * 20), decimal.Round((decimal)(rand.NextDouble() * 500 + 200), 4), decimal.Round((decimal)(rand.NextDouble() * 20 + 5), 2)); } SLThemeSettings stSettings = BuildTheme(); SLDocument sheet = new SLDocument(stSettings); sheet.ImportDataTable(1, 1, dt, true); //set col width sheet.SetColumnWidth(1, 5, 12); // This part sets the style, but you might be using a template file, // so the styles are probably already set. ////setting date format for column 4 SLStyle style = sheet.CreateStyle(); style.FormatCode = "MM/dd/yyyy"; sheet.SetColumnStyle(3, style); //fixed first row and 3 columns from left sheet.FreezePanes(1, 4); //// setting first row color & style SLStyle headerstyle = sheet.CreateStyle(); headerstyle.Font.Bold = true; headerstyle.Font.FontColor = System.Drawing.Color.IndianRed; headerstyle.Fill.SetPattern(PatternValues.Solid, SLThemeColorIndexValues.Light2Color, SLThemeColorIndexValues.Light2Color); sheet.SetRowStyle(1, headerstyle); //// setting first row color & style for red color section SLStyle redrowstyle = sheet.CreateStyle(); redrowstyle.Font.FontColor = System.Drawing.Color.Black; redrowstyle.Fill.SetPattern(PatternValues.Solid, SLThemeColorIndexValues.Accent1Color, SLThemeColorIndexValues.Accent1Color); sheet.SetCellStyle("A9", "E15", redrowstyle); //// setting first row color & style for yellow color section SLStyle yellowrowstyle = sheet.CreateStyle(); redrowstyle.Font.FontColor = System.Drawing.Color.Black; redrowstyle.Fill.SetPattern(PatternValues.Solid, SLThemeColorIndexValues.Accent3Color, SLThemeColorIndexValues.Accent3Color); sheet.SetCellStyle("A16", "E18", redrowstyle); //// setting a specifc cell color & style SLStyle cellstyle = sheet.CreateStyle(); cellstyle.Font.FontColor = System.Drawing.Color.Black; cellstyle.Fill.SetPattern(PatternValues.Solid, SLThemeColorIndexValues.Accent4Color, SLThemeColorIndexValues.Accent4Color); sheet.SetCellStyle("A19", cellstyle); //standard number format SLStyle standardstyle = new SLStyle(); standardstyle.FormatCode = "#,##0.000;[Red](-#,##0.000);#,##0.000"; sheet.SetCellStyle("D1", "D4", standardstyle); //CurrencySign number format SLStyle CurrencySignstyle = new SLStyle(); CurrencySignstyle.FormatCode = "$#,##0.000;[Red]$(-#,##0.000);$#,##0.000"; sheet.SetCellStyle("D5", "D6", CurrencySignstyle); //PercentageSign number format SLStyle PercentageSignstyle = new SLStyle(); PercentageSignstyle.FormatCode = "0.00%;[Red](-0.00%);0.00%"; sheet.SetCellStyle("D7", "D10", PercentageSignstyle); sheet.SaveAs("d:\\SpreadsheetLight.xlsx"); MessageBox.Show("Done"); }
private SLThemeSettings BuildTheme() { SLThemeSettings theme = new SLThemeSettings(); theme.ThemeName = "RDSColourTheme"; //theme.MajorLatinFont = "Impact"; //theme.MinorLatinFont = "Harrington"; // this is recommended to be pure white theme.Light1Color = System.Drawing.Color.White; // this is recommended to be pure black theme.Dark1Color = System.Drawing.Color.Black; theme.Light2Color = System.Drawing.Color.LightGray; theme.Dark2Color = System.Drawing.Color.IndianRed; theme.Accent1Color = System.Drawing.Color.Red; theme.Accent2Color = System.Drawing.Color.Tomato; theme.Accent3Color = System.Drawing.Color.Yellow; theme.Accent4Color = System.Drawing.Color.LawnGreen; theme.Accent5Color = System.Drawing.Color.DeepSkyBlue; theme.Accent6Color = System.Drawing.Color.DarkViolet; theme.Hyperlink = System.Drawing.Color.Blue; theme.FollowedHyperlinkColor = System.Drawing.Color.Purple; return theme; }
// read cell value from excel file in for loop
private void button2_Click(object sender, EventArgs e) { var sheet = new SLDocument(@"d:\SpreadsheetLight.xlsx"); SLWorksheetStatistics stats = sheet.GetWorksheetStatistics(); for (int row = 2; row < stats.EndRowIndex; row++) { for (int col = 1; col < stats.EndColumnIndex; col++) { // Get the first column of the row (SLS is a 1-based index) var value = sheet.GetCellValueAsString(row, col); MessageBox.Show(value); } } }
- Marked as answer by Sudip_inn Friday, December 28, 2018 2:38 PM
Friday, December 28, 2018 2:38 PM
All replies
-
This forum is for C#-specific questions only. Questions related to third party libraries need to be posted in their forums. We do not provide support for third-party libraries here.
Michael Taylor http://www.michaeltaylorp3.net
Thursday, December 27, 2018 6:54 PM -
Now issue fixed and working code added here.
Install-Package DocumentFormat.OpenXml -Version 2.8.1
private void button1_Click(object sender, EventArgs e) { Random rand = new Random(); System.Data.DataTable dt = new System.Data.DataTable(); dt.Columns.Add("Product", typeof(string)); dt.Columns.Add("IP Address", typeof(string)); dt.Columns.Add("Date (UTC)", typeof(DateTime)); dt.Columns.Add("Size (MB)", typeof(double)); dt.Columns.Add("Cost", typeof(decimal)); for (int i = 0; i < 20; ++i) { dt.Rows.Add(string.Format("Prod{0}", i+1), string.Format("{0}.{1}.{2}.{3}", rand.Next(256), rand.Next(256), rand.Next(256), rand.Next(256)), DateTime.UtcNow.AddDays(rand.NextDouble() * 20), decimal.Round((decimal)(rand.NextDouble() * 500 + 200), 4), decimal.Round((decimal)(rand.NextDouble() * 20 + 5), 2)); } SLThemeSettings stSettings = BuildTheme(); SLDocument sheet = new SLDocument(stSettings); sheet.ImportDataTable(1, 1, dt, true); //set col width sheet.SetColumnWidth(1, 5, 12); // This part sets the style, but you might be using a template file, // so the styles are probably already set. ////setting date format for column 4 SLStyle style = sheet.CreateStyle(); style.FormatCode = "MM/dd/yyyy"; sheet.SetColumnStyle(3, style); //fixed first row and 3 columns from left sheet.FreezePanes(1, 4); //// setting first row color & style SLStyle headerstyle = sheet.CreateStyle(); headerstyle.Font.Bold = true; headerstyle.Font.FontColor = System.Drawing.Color.IndianRed; headerstyle.Fill.SetPattern(PatternValues.Solid, SLThemeColorIndexValues.Light2Color, SLThemeColorIndexValues.Light2Color); sheet.SetRowStyle(1, headerstyle); //// setting first row color & style for red color section SLStyle redrowstyle = sheet.CreateStyle(); redrowstyle.Font.FontColor = System.Drawing.Color.Black; redrowstyle.Fill.SetPattern(PatternValues.Solid, SLThemeColorIndexValues.Accent1Color, SLThemeColorIndexValues.Accent1Color); sheet.SetCellStyle("A9", "E15", redrowstyle); //// setting first row color & style for yellow color section SLStyle yellowrowstyle = sheet.CreateStyle(); redrowstyle.Font.FontColor = System.Drawing.Color.Black; redrowstyle.Fill.SetPattern(PatternValues.Solid, SLThemeColorIndexValues.Accent3Color, SLThemeColorIndexValues.Accent3Color); sheet.SetCellStyle("A16", "E18", redrowstyle); //// setting a specifc cell color & style SLStyle cellstyle = sheet.CreateStyle(); cellstyle.Font.FontColor = System.Drawing.Color.Black; cellstyle.Fill.SetPattern(PatternValues.Solid, SLThemeColorIndexValues.Accent4Color, SLThemeColorIndexValues.Accent4Color); sheet.SetCellStyle("A19", cellstyle); //standard number format SLStyle standardstyle = new SLStyle(); standardstyle.FormatCode = "#,##0.000;[Red](-#,##0.000);#,##0.000"; sheet.SetCellStyle("D1", "D4", standardstyle); //CurrencySign number format SLStyle CurrencySignstyle = new SLStyle(); CurrencySignstyle.FormatCode = "$#,##0.000;[Red]$(-#,##0.000);$#,##0.000"; sheet.SetCellStyle("D5", "D6", CurrencySignstyle); //PercentageSign number format SLStyle PercentageSignstyle = new SLStyle(); PercentageSignstyle.FormatCode = "0.00%;[Red](-0.00%);0.00%"; sheet.SetCellStyle("D7", "D10", PercentageSignstyle); sheet.SaveAs("d:\\SpreadsheetLight.xlsx"); MessageBox.Show("Done"); }
private SLThemeSettings BuildTheme() { SLThemeSettings theme = new SLThemeSettings(); theme.ThemeName = "RDSColourTheme"; //theme.MajorLatinFont = "Impact"; //theme.MinorLatinFont = "Harrington"; // this is recommended to be pure white theme.Light1Color = System.Drawing.Color.White; // this is recommended to be pure black theme.Dark1Color = System.Drawing.Color.Black; theme.Light2Color = System.Drawing.Color.LightGray; theme.Dark2Color = System.Drawing.Color.IndianRed; theme.Accent1Color = System.Drawing.Color.Red; theme.Accent2Color = System.Drawing.Color.Tomato; theme.Accent3Color = System.Drawing.Color.Yellow; theme.Accent4Color = System.Drawing.Color.LawnGreen; theme.Accent5Color = System.Drawing.Color.DeepSkyBlue; theme.Accent6Color = System.Drawing.Color.DarkViolet; theme.Hyperlink = System.Drawing.Color.Blue; theme.FollowedHyperlinkColor = System.Drawing.Color.Purple; return theme; }
// read cell value from excel file in for loop
private void button2_Click(object sender, EventArgs e) { var sheet = new SLDocument(@"d:\SpreadsheetLight.xlsx"); SLWorksheetStatistics stats = sheet.GetWorksheetStatistics(); for (int row = 2; row < stats.EndRowIndex; row++) { for (int col = 1; col < stats.EndColumnIndex; col++) { // Get the first column of the row (SLS is a 1-based index) var value = sheet.GetCellValueAsString(row, col); MessageBox.Show(value); } } }
- Marked as answer by Sudip_inn Friday, December 28, 2018 2:38 PM
Friday, December 28, 2018 2:38 PM