gopi h

gopi h

  • 2.1k
  • 7
  • 237

How to get the Data on specific row DocumentOpenXml Spreadsheet light

Jan 6 2022 6:22 AM

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

 


Answers (1)