We help IT Professionals succeed at work.

graph related problem

28 Views
Last Modified: 2020-05-16
i am able to create a chart using excel in vb.net but i need to remove the gridlines from the image being saved. kindly help

Dim xlApp As Microsoft.Office.Interop.Excel.Application

        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook

        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet

        Dim misValue As Object = System.Reflection.Missing.Value
        Dim I As Integer = 0
        Dim errMsg As String = ""

        Try
            xlApp = New Microsoft.Office.Interop.Excel.Application

            xlWorkBook = xlApp.Workbooks.Add()

            xlWorkSheet = xlWorkBook.Sheets("Sheet1")



            'add data

            Dim bytes = System.Convert.FromBase64String(strCurveData)

            'STR1 = STR.Split(";")
            For I = 1 To bytes.Count
                xlWorkSheet.Range("A" & I).Value2 = bytes(I - 1)
            Next
            'create chart

            Dim chartPage As Microsoft.Office.Interop.Excel.Chart

            Dim xlCharts As Microsoft.Office.Interop.Excel.ChartObjects

            Dim myChart As Microsoft.Office.Interop.Excel.ChartObject

            Dim chartRange As Microsoft.Office.Interop.Excel.Range



            xlCharts = xlWorkSheet.ChartObjects
            myChart = xlCharts.Add(60, 10, 600, 300)

            chartPage = myChart.Chart

            Dim xlxAxis As Excel.Axis = chartPage.Axes(Excel.XlAxisType.xlCategory)
            'xlxAxis.MajorTickMark = Excel.XlTickMark.xlTickMarkNone
            xlxAxis.HasMajorGridlines = False
            xlxAxis.HasMinorGridlines = False
            'xlxAxis.HasDisplayUnitLabel = False

            chartPage.HasLegend = False
            chartPage.AutoScaling = True
            chartPage.SetBackgroundPicture("D:\Test.jpg")
            'chartPage.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)

            'chartRange = xlWorkSheet.Range("Sheet1!$1:$1")

            Dim rng = xlWorkSheet.Range("A1:A" & bytes.Count.ToString)

            chartPage.SetSourceData(Source:=rng)


            'chartPage.SetSourceData(Source:=chartRange)

            'chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterSmoothNoMarkers
            chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlArea

           
            chartPage.ChartWizard(Source:=rng)

            If System.IO.File.Exists(Application.StartupPath & "\" & strCurveName & ".JPG") Then
                System.IO.File.Delete(Application.StartupPath & "\" & strCurveName & ".JPG")
            End If
            chartPage.Export(Application.StartupPath & "\" & strCurveName & ".JPG")

            Dim _fileInfo As New IO.FileInfo(Application.StartupPath & "\" & strCurveName & ".JPG")
            Dim _NumBytes As Long = _fileInfo.Length
            Dim _FStream As New IO.FileStream(Application.StartupPath & "\" & strCurveName & ".JPG", IO.FileMode.Open, IO.FileAccess.Read)
            Dim _BinaryReader As New IO.BinaryReader(_FStream)
            CurveImg = _BinaryReader.ReadBytes(_NumBytes)
            _fileInfo = Nothing
            _NumBytes = 0
            _FStream.Close()
            _FStream.Dispose()
            _BinaryReader.Close()


            '        If System.IO.File.Exists("D:\" & strCurveName & ".JPG") Then
            'System.IO.File.Delete("D:\" & strCurveName & ".JPG")
            'End If


            xlWorkSheet.SaveAs(Application.StartupPath & "\vbexcel.xlsx")

            xlWorkBook.Close()

            xlApp.Quit()
            releaseObject(xlApp)

            releaseObject(xlWorkBook)

            releaseObject(xlWorkSheet)



            If System.IO.File.Exists(Application.StartupPath & "\vbexcel.xlsx") Then
                '    System.IO.File.Delete(Application.StartupPath & "\vbexcel.xlsx")
            End If
            If System.IO.File.Exists(Application.StartupPath & "\" & strCurveName & ".JPG") Then
                '   System.IO.File.Delete(Application.StartupPath & "\" & strCurveName & ".JPG")
            End If

            releaseObject(xlApp)

            releaseObject(xlWorkBook)

            releaseObject(xlWorkSheet)
        Catch ex As Exception
            errMsg = ex.ToString
Comment
Watch Question

√Čric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
I don't have the exact code for you but I will give you a trick.

Record a macro in Excel that does exactly what you want to do (remove gridline) and look at the generated code.
CERTIFIED EXPERT

Commented:
What's the example value of strCurveData ?

Author

Commented:
Ryan Chong

You caN USE ANY DATA BUT THE PROBLEM IS TO REMOVE THE GRID LINES FROM THE CHART AS PER THE ABOVE CODE.

THANKS IN ADVANCE
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.