none
creat line& column chart in excel RRS feed

  • Question

  • hi

    I want to draw a diagram inside the Excel page
    1-A diagram that has columns that can be divided into two parts: vertical = mony and horizontal = type
    2-The other plan is the same lines as the previous method below the scheme of columns

    horizontal = type  1- credit ,2 debit , 3 difference

    column 1 color reed credit

    column 2 color green debit

    column 3 color yellow

    vertical = mony value come from ==> credit=sum e ,debit==>sum f ,difference==>sum g

      For I = 2 To excelWorksheet.UsedRange.Rows.Count
                    If String.IsNullOrEmpty(excelWorksheet.Range("e" & I).Value) = False AndAlso String.IsNullOrEmpty(excelWorksheet.Range("f" & I).Value) = False Then
                        excelWorksheet.Range("g" & I).Value = "=e" & I & "-f" & I
                    End If
                Next

    Friday, October 12, 2018 8:01 PM

All replies

  • The following should be a decent start.

    http://vb.net-informations.com/excel-2007/vb.net_excel_create_chart.htm

    Also if working with .xlsx consider looking a SpreadSheetLight (free library installed via NuGet)

    http://spreadsheetlight.com/

    The following is what's available in SpreadSheetLight


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, October 12, 2018 9:15 PM
    Moderator
  • hi

    Sorry to delay reply
    My problem is not in drawing the chart but in choosing specific columns header and rows total

     
     

    Saturday, October 13, 2018 2:53 PM
  • my code draw chart

    row difference

        For I = 2 To excelWorksheet.UsedRange.Rows.Count
                        If String.IsNullOrEmpty(excelWorksheet.Range("b" & I).Value) = False AndAlso String.IsNullOrEmpty(excelWorksheet.Range("c" & I).Value) = False Then
                            excelWorksheet.Range("d" & I).Value = "=b" & I & "-c" & I
                        End If
                    Next

    column total

      excelWorksheet.Range("b" & lRow + 1).Value = "=SUM(b2:b" & lRow & ")"
                excelWorksheet.Range("c" & lRow + 1).Value = "=SUM(c2:c" & lRow & ")"
                excelWorksheet.Range("d" & lRow + 1).Value = "=SUM(d2:d" & lRow & ")"
                excelWorksheet.Range("a" & lRow + 1).Value = "total"

    ful code

     Dim oChart As Excel.Chart
                    Dim MyCharts As Excel.ChartObjects
                    Dim MyCharts1 As Excel.ChartObject
                    MyCharts = excelWorksheet.ChartObjects
                    MyCharts1 = MyCharts.Add(250, 30, 400, 250)
                    oChart = MyCharts1.Chart
                 
                    With oChart
                        'set data range for chart
                        Dim chartRange As Excel.Range
                        chartRange = excelWorksheet.Range("b1", "d3")
                        .SetSourceData(chartRange)
                        'set how you want to draw chart i.e column wise or row wise
                        .PlotBy = Excel.XlRowCol.xlColumns
                        'set data lables for bars
                        .ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)
                        'set legend to be displayed or not
                        .HasLegend = True
                        'set legend location
                        .Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight
                        'select chart type
    end with



    • Edited by ahmeddc Saturday, October 13, 2018 3:00 PM
    Saturday, October 13, 2018 2:55 PM
  • Hi,

    Sorry for late reply,do you want to use the first column as the title of the X axis?

    try to modify the code

     .PlotBy = Excel.XlRowCol.xlColumns

    to

    .PlotBy = Excel1.XlRowCol.xlRows

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 17, 2018 3:14 AM