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.
- Create SharePoint sample by selecting the “VisualWebPart” from the Visual Studio installed template.
- 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); } } }
|
- 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