I am using DocumentOpenXml Spreadsheet light for pie chart and I am not able to fetch last row for pie chart calculation .
sl.AddWorksheet("Summary 1-Property Type Graph and Data");
string strHeader2 = "Summary on Total number of Property type audited on " + MonthName + " to " + Year + "";
sl.SetCellValue(1, 1, strHeader2);
int rPropertyFile1 = 2;
int nProperty1 = dtPrpertyTypeGraph.Columns.Count;
for (int col = 0; col < nProperty1; col++)
{
sl.SetCellValue(rPropertyFile1, col + 1, dtPrpertyTypeGraph.Columns[col].ColumnName);
SLStyle style = sl.CreateStyle();
style.Border.BottomBorder.Color = System.Drawing.Color.Black;
style.Border.LeftBorder.Color = System.Drawing.Color.Black;
style.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.SetTopBorder(BorderStyleValues.Thin, SLThemeColorIndexValues.Dark1Color);
//style.SetDiagonalBorder(BorderStyleValues.MediumDashDotDot, SLThemeColorIndexValues.Accent3Color, 0.2);
style.Border.DiagonalUp = true;
style.Border.DiagonalDown = true;
sl.SetCellStyle(rPropertyFile1, col + 1, style);
}
int rPropertyFile2 = 1;
rPropertyFile2 = rPropertyFile1 + 1;
int nPropertyFile2 = dtPrpertyTypeGraph.Rows.Count - 1;
int nPropertyFile3 = dtPrpertyTypeGraph.Columns.Count;
//int nLoanFileGraph = dtLoanFilesGraph.Rows.Count - 1;
//int nLoanFileGraph1 = dtLoanFilesGraph.Columns.Count;
Random rand1 = new Random();
int colcount1 = 0;
for (int row = 0; row <= nPropertyFile2; row++)
{
int i1 = 1;
for (int col = 0; col < nPropertyFile3; col++)
{
if (col > 0)
{
sl.SetCellValue(rPropertyFile2, col + 1, Convert.ToInt32(dtPrpertyTypeGraph.Rows[row][col].ToString()));
SLStyle style = sl.CreateStyle();
style.Border.BottomBorder.Color = System.Drawing.Color.Black;
style.Border.LeftBorder.Color = System.Drawing.Color.Black;
style.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.SetTopBorder(BorderStyleValues.Thin, SLThemeColorIndexValues.Dark1Color);
style.SetBottomBorder(BorderStyleValues.Thin, SLThemeColorIndexValues.Dark1Color);
//style.SetDiagonalBorder(BorderStyleValues.MediumDashDotDot, SLThemeColorIndexValues.Accent3Color, 0.2);
style.Border.DiagonalUp = true;
style.Border.DiagonalDown = true;
sl.SetCellStyle(rPropertyFile2, col + 1, style);
}
else
{
sl.SetCellValue(rPropertyFile2, col + 1, dtPrpertyTypeGraph.Rows[row][col].ToString());
SLStyle style = sl.CreateStyle();
style.Border.BottomBorder.Color = System.Drawing.Color.Black;
style.Border.LeftBorder.Color = System.Drawing.Color.Black;
style.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
style.SetTopBorder(BorderStyleValues.Thin, SLThemeColorIndexValues.Dark1Color);
style.SetBottomBorder(BorderStyleValues.Thin, SLThemeColorIndexValues.Dark1Color);
//style.SetDiagonalBorder(BorderStyleValues.MediumDashDotDot, SLThemeColorIndexValues.Accent3Color, 0.2);
style.Border.DiagonalUp = true;
style.Border.DiagonalDown = true;
sl.SetCellStyle(rPropertyFile2, col + 1, style);
}
//graph.Cell(rLoanFile2, col + 1).Style.Border.SetOutsideBorder(XLBorderStyleValues.Thin);
//graph.Cell(rLoanFile2, col + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
//graph.Cell(rLoanFile2, col + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
//colcount++;
}
rPropertyFile2 = rPropertyFile2 + 1;
}
string columnName1 = "";
while (nPropertyFile3 > 0)
{
int modulo = (nPropertyFile3 - 1) % 26;
columnName1 = Convert.ToChar('A' + modulo) + columnName1;
nPropertyFile3 = (nPropertyFile3 - modulo) / 26;
}
String strEndProperty;
strEndProperty = columnName1 + (rPropertyFile2 - 2);
SLChart chartProperty = sl.CreateChart("A2", strEndProperty);
//chart.SetChartPosition(7, 1, 22, 8.5);
chartProperty.SetChartType(SLPieChartType.Pie3D);
chartProperty.SetChartPosition(rPropertyFile2 + 3, 1, rPropertyFile2 + 15, 8.5);
//SLDataSeriesOptions dso1;
sl.InsertChart(chartProperty);