locked
SpreadsheetLight: How to set foreground/background color & other issues RRS feed

  • 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 follows

    using 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









    • Edited by Sudip_inn Thursday, December 27, 2018 3:29 PM
    • Moved by CoolDadTx Thursday, December 27, 2018 6:55 PM Third party product
    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