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);
}
}
}
