We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

How to pull data from excel to Gauge using SharePoint server?

How to pull data from excel to Gauge using SharePoint server?               

Description:

By default we cannot read the data from excel file and use it in a gauge control. Here we have provide the solution to pull data from excel and using it in Gauge.

Solution:

We couldn’t read the excel file directly from the SharePoint server, so we have used the assembly “EPPlus” to read the excel data from the excel file. Hence we have downloaded the file and stored it in local machine, then processed reading data.

 

The following steps are needed to meet the requirement.

 

  1. Create SharePoint sample by selecting the “VisualWebPart” from the Visual Studio installed template.
  2. Select SharePoint 2010 and select “Deploy as a form solution”. While creating the sample select “VisualWebpartProject” and add the code mentioned below.

 

 

<%--Referring necessary assembly files--%>

  

       <%@ Register Assembly="Syncfusion.Gauge.Web,Version=XX.XXXX.X.XX, Culture=neutral, PublicKeyToken=3D67ED1F87D44C89" Namespace="Syncfusion.Web.UI.WebControls.Gauge" TagPrefix="syncfusion"%>

<%@ Register Assembly="Syncfusion.Shared.Web,Version=XX.XXXX.X.XX, Culture=neutral, PublicKeyToken=3D67ED1F87D44C89" Namespace="Syncfusion.Web.UI.WebControls.Shared" TagPrefix="syncfusion" %>

<%@ Register Assembly="WindowsBase, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"

    Namespace="System.Windows" TagPrefix="wb" %>

 

<%--Initial Gauge Render--%>

 

<div id="CircularGauge">

    <syncfusion:CircularGauge ID="CircularGauge1" runat="server">

    </syncfusion:CircularGauge>

</div>

 

 

 

 

 

 

 

 

 

  protected override void OnInit(EventArgs e)

        {

            base.OnInit(e);

            InitializeControl();

        }

 

        

        protected void Page_Load(object sender, EventArgs e)

        {

                string[] Attachmentlink = { "http://win-0dbj3cmmgc0:26484/sample/Gauge/Shared%20Documents/GaugeData.xlsx" };

                string commonlocation = @"C:\Users\labuser\Downloads";

                foreach (string url in Attachmentlink)

                {

 

                    string[] filename = url.Split('/');

                    WebClient client = new WebClient();

                    client.Credentials = new NetworkCredential("labuser", "Syncfusion!1");

                    System.IO.Directory.CreateDirectory(commonlocation);

                    client.DownloadFile(new Uri(url), commonlocation + "\\" + filename[filename.Length - 1]);

                    Console.WriteLine("DownloadCompleted");

 

                }

                var value = "";

                DataTable atable = new DataTable();

                string path = @"C:\Users\labuser\Downloads\GaugeData.xlsx";

                using (StreamReader stream = new StreamReader(path))

                {

                    OfficeOpenXml.ExcelPackage packet = new ExcelPackage(stream.BaseStream);

                    ExcelWorkbook workbook = packet.Workbook;

 

                    if (workbook == null)

                    {

                        throw new Exception("Excel file has no workbooks!");

                    }

                    else

                    {

                        if (workbook.Worksheets.Count == 0)

                        {

                            throw new Exception("Excel file has no worksheets!");

                        }

                        else

                        {

                            ExcelWorksheet sheet = workbook.Worksheets[1]; string val = "";

                            int columnindex = 1;

                            for (int rowindex = 1; rowindex <= sheet.Dimension.End.Row; rowindex++)

                            {

                                if (rowindex != 1)

                                {

                                    for (int i = 0; i < sheet.Dimension.End.Column; i++)

                                    {

                                        if (val == "")

                                        {

                                            val = (sheet.Cells[rowindex, columnindex].Value).ToString();

                                        }

                                        else

                                        {

                                            val = val + ":" + (sheet.Cells[rowindex, columnindex].Value).ToString();

                                        }

                                        columnindex++;

                                    }

                                }

                                value = val;

                            }

                            

                        }

 

                       

                    }

                }

                BindGauge(value);

        }

        public void BindGauge(string value1)

        {

 

<%--Binding Circular gauge Radius, height and width--%>

            string[] value = value1.Split(':');

            this.CircularGauge1.Radius = Convert.ToInt32(value[0]);

            this.CircularGauge1.Height = Unit.Pixel(Convert.ToInt32(value[1]));

            this.CircularGauge1.Width = Unit.Pixel(Convert.ToInt16(value[2]));

 

 

 

            CircularScale scale1 = new CircularScale();

 

            scale1.Minimum = Convert.ToInt32(value[3]);

 

            scale1.Maximum = Convert.ToInt32(value[4]);

 

            scale1.MinorIntervalValue = Convert.ToInt32(value[5]);

 

            scale1.MajorIntervalValue = Convert.ToInt32(value[6]);

 

            scale1.Location = new Point(50, 50);

 

 

            CircularGaugeTick tick1 = new CircularGaugeTick();

 

            tick1.TickStyle = TickStyle.MajorInterval;

 

            scale1.Ticks.Add(tick1);

 

 

 

            CircularGaugeTick tick2 = new CircularGaugeTick();

 

            tick2.TickStyle = TickStyle.MinorInterval;

 

            scale1.Ticks.Add(tick2);

 

 

 

            CircularGaugeLabel label1 = new CircularGaugeLabel();

 

            label1.LabelStyle = TickStyle.MajorInterval;

 

            scale1.Labels.Add(label1);

 

 

 

            CircularPointer pointer1 = new CircularPointer();

 

            pointer1.NeedleStyle = NeedleStyle.Arrow;

 

            pointer1.PointerLength = Convert.ToInt32(value[7]);

 

            pointer1.PointerWidth = Convert.ToInt32(value[8]);

 

            scale1.Pointers.Add(pointer1);

 

 

 

            this.CircularGauge1.Scales.Add(scale1);

        }

    }

}

 

 

 

 

 

  1. Now we were able to run the sample and render the control in the SharePoint server.

 

Sample reference could be downloaded from the following location.

 

Pulling_ExcelData_To_Gauge_Control

 

Article ID: Published Date: Last Revised Date: Platform: Control:
3082 01/04/2015 01/04/2015 ASP.NET Web Forms (Classic) Gauge
Tags:
Did you find this information helpful?
Add Comment
You must log in to leave a comment

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.