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.
|
Query |
Answer |
|
public ActionResult GenerateExcelDocument(int SurveyNumber, SurveyDefinition survey)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
// Set the default application version as Excel 2016.
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
//Create a workbook with a worksheet.
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
//Access first worksheet from the workbook instance.
IWorksheet worksheet = workbook.Worksheets[0];
string CompanyName = survey.GetCompany(SurveyNumber);
IList< SurveyScoresByGroup_Result > group1 = db.SurveyScoresByGroup(1, SurveyNumber).ToList();
worksheet.ImportData(group1, 2, 1, true);
workbook.SaveAs("ReportGroup1.xlsx");
workbook.Close();
excelEngine.Dispose();
}
return View();
}
|
While saving the Excel file in ASP.NET MVC, SaveAs method should be used with HttpResponse and ExcelDownloadType as shown below:
workbook.SaveAs("ReportGroup1.xlsx", HttpContext.ApplicationInstance.Response,
ExcelDownloadType.Open);
|
|
public ActionResult GenerateExcelDocument(int SurveyNumber, SurveyDefinition survey)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
string CompanyName = survey.GetCompany(SurveyNumber);
IList<SurveyScoresByGroup_Result > group1 = db.SurveyScoresByGroup(1, SurveyNumber).ToList();
worksheet.ImportData(group1, 2, 1, true);
workbook.SaveAs("Sample.xlsx");
workbook.Close();
excelEngine.Dispose();
}
return View();
}
|
While opening the existing file, Server.MapPath(string path) method must be used to get the proper file path as shown below.
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(Server.MapPath("~/App_Data/Book1.xlsx"),ExcelOpenType.Automatic); |
The answer for the 1st Query works great thank you!
Now I need to add the charts to this. This will import data from business objects. I do not see an example of this for a chart in excel when getting the data from business objects. I can see where you get the data from a range and was able to trick it by adding the data like so but is there not a better way?
/....
AverageAnswerByGroup mod = new AverageAnswerByGroup();
mod.GroupNumber = 1;
mod.SurveyNumber = SurveyNumber;
IList<AverageGroupAnswer_Result> chart1 = mod.AverageGroupScores();
worksheet.ImportData(chart1, 7, 1, true);
IChartShape chart = worksheet.Charts.Add();
chart.ChartType = ExcelChartType.Bar_Clustered;
chart.DataRange = worksheet.Range["A7:B11"];
shown in ReportGroup1(8).xlsx
My next issue is that it is listing the text 'Average Answer' to the left of the chart when it should be at the bottom see ReportGroup1 (11).xlsx
Also how do you dynamically place charts and objects? Once it gets past the first item I will not know how many rows down it is using. It is possible to create a variable and hold the number of rows in each and just keep adding as it progresses but is there a better way? or do I need to create a separate worksheet for each section and then have the person combine the worksheets to create a single report?
Basically what you see in the attached HTML document is what I need to be able to export to Excel first and then also into a Power Point Presentation afterward, which brings up another question as I saw that it required the following assemblies
Syncfusion.Presentation.Base
Syncfusion.Compression.Base
Syncfusion.OfficeChart.Base
but nuget does not find Syncfusion.Presentation.Base the other two assemblies are already installed.
|
Query |
Answer | |
|
I can see where you get the data from a range and was able to trick it by adding the data like so but is there not a better way? |
You can get the newly added range from worksheet by UsedRange.LastRow and UsedRange.LastColumn properties. | |
|
Listing the text 'Average Answer' to the left of the chart when it should be at the bottom. |
Please refer the following code example to set “Average Answer” text to the chart bottom.
| |
|
Also how do you dynamically place charts and objects? Once it gets past the first item I will not know how many rows down it is using. It is possible to create a variable and hold the number of rows in each and just keep adding as it progresses but is there a better way? or do I need to create a separate worksheet for each section and then have the person combine the worksheets to create a single report?
|
You can get and set chart position using chart.TopRow and chart.BottomRow properties. Please refer the below UG documentation link for more details.
UG Link: https://help.syncfusion.com/file-formats/xlsio/working-with-charts#positioning-chart | |
|
Nuget does not find Syncfusion.Presentation.Base |
Please follow the below procedure to install Presentation nuget
Install Presentation assemblies with NuGet:
In Visual Studio, select Tools > NuGet Package Manager > Package Manager Console and execute the below command in console project.
Note: The number at the end of the NuGet package name represents .NET Framework version. In the above command "Syncfusion.Presentation.Base45" represents the Presentation libraries for .NET Framework 4.5 (Syncfusion. Presentation.Base35 and Syncfusion.Presentation.Base40 represents the libraries for .NET Framework 3.5 and 4.0 respectively).
|
| You can get the newly added range from worksheet by UsedRange.LastRow andUsedRange.LastColumn properties. | This is not what I am wanting. while it did answer #3 I am wanting to know how I can get the business logic into the chart See code below | |
| Chart.IsSeriesInRows = false; Chart.Legend.Position = ExcelLegendPosition.Bottom; | Thank You. That works great. Using the following chart.Legend.Delete(); I am able to not have a legend on the chart. | |
| You can get and set chart position using chart.TopRow and chart.BottomRow properties. | That is what I am doing already. Your answer about using UsedRange.LastRow was actually what i needed here I am now using the worksheet.UsedRange.LastRow and then adding 2 to it gives me one row space before placing the next item. | |
| Thank you adding the source I was able to have it install the assesmbly. |
public ActionResult GenerateExcelDocument(int SurveyNumber, SurveyDefinition survey)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
string CompanyName = survey.GetCompany(SurveyNumber);
// This is the number of questions for this group this will be used to calculate positioning
int numRows = survey.CountMatrixQuestions(SurveyNumber, 1);
// Set the default application version as Excel 2016.
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
// Create a workbook with a worksheet.
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
// Access first worksheet from the workbook instance.
//IWorksheet worksheet = workbook.Worksheets[0];
IWorksheet sheet1 = workbook.Worksheets.Create("Q1");
// Display list of data
IList<SurveyScoresByGroup_Result> group1 = db.SurveyScoresByGroup(1, SurveyNumber).ToList();
sheet1.ImportData(group1, 1, 1, true);
// Get last row #
int lastrow = Convert.ToInt16(sheet1.UsedRange.LastRow);
int rownum = lastrow + 2;
int b = rownum + numRows; // this value needs to add the number of group questions to number of rows already on the worksheet
AverageAnswerByGroup mod = new AverageAnswerByGroup();
mod.GroupNumber = 1;
mod.SurveyNumber = SurveyNumber;
//I do not actually want to display the underlined list of data .
//However, this works to trick it so we will pretend to display the data on the spreadsheet but will actually put chart of this data there instead
IList<AverageGroupAnswer_Result> chart1 = mod.AverageGroupScores();
sheet1.ImportData(chart1, rownum, 1, true);
// Replace above with chart
IChartShape chart = sheet1.Charts.Add();
chart.ChartType = ExcelChartType.Bar_Clustered;
string chartplacement = "A" + rownum.ToString() + ":B" + b.ToString();
chart.DataRange = sheet1.Range[chartplacement];
chart.PrimaryValueAxis.MinimumValue = 0;
chart.PrimaryValueAxis.MaximumValue = 5;
chart.PrimaryValueAxis.MinorUnit = .5;
chart.ChartTitle = "Average Answer";
chart.IsSeriesInRows = false;
chart.Legend.Position = ExcelLegendPosition.Bottom;
chart.Legend.Delete();
chart.TopRow = rownum;
chart.RightColumn = 15;
chart.LeftColumn = 1;
lastrow = Convert.ToInt16(sheet1.UsedRange.LastRow);
rownum = lastrow + 2;
AdditionalComments comments = new AdditionalComments();
comments.GroupNumber = 1;
comments.SurveyNumber = SurveyNumber;
IList<ListComments_Result> q1Comments = comments.Comments();
sheet1.ImportData(q1Comments, b*2, 1, true);
workbook.SaveAs("ReportGroup1.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog);
workbook.Close();
excelEngine.Dispose();
}
return View();
}
In reference to this page.
https://help.syncfusion.com/file-formats/xlsio/working-with-excel-worksheet#create-a-worksheet
How do I create my named worksheet as the 0 index worksheet?
right now I am doing this
IWorksheet sheet1 = workbook.Worksheets.Create("Q1");
which makes it the 1 index worksheet.
I know I can also create the first one without naming it like so
IWorksheet sheet1 = workbook.Worksheets[0];
I thought perhaps doing this IWorksheet sheet1 = workbook.Worksheets[0].Create("Q1"); might work but of course that throws an error so how do I name the worksheets and have them in correct index order from left to right?
|
Query |
Answer | |
|
I am wanting to know how I can get the business logic into the chart |
You can add the chart directly from business objects by using serie.EnteredDirectlyValues and serie.EnteredDirectlyCategoryLabels properties.
Please refer the following UG documentation link to create chart in XlsIO:
| |
|
Using the following chart.Legend.Delete(); I am able to not have a legend on the chart. |
In the sample which we have provided in our previous update, we have set the "Average Answer" text to chart legend to show it in chart bottom. If you use the chart.Legend.Delete() code, it will remove the chart legend from the chart and the text will not be shown in chart bottom.
However, you can show the text "Average Answer" to chart bottom by setting the text to chart.PrimaryValueAxis.Title property. Please refer the following code example to set title to value axis.
Code snippet:
| |
|
That is what I am doing already. Your answer about using UsedRange.LastRow was actually what i needed here I am now using the worksheet.UsedRange.LastRow and then adding 2 to it gives me one row space before placing the next item. |
We have prepared a sample to achieve your requirement which can be downloaded from below. Sample Link: http://www.syncfusion.com/downloads/support/directtrac/general/SAMPLE~1-1927640795.ZIP | |
|
How do I create my named worksheet as the 0 index worksheet?
|
You can create named worksheet for 0th index by creating workbook with worksheet names. Please refer the following code example to achieve this.
Code snippet:
| |
|
Thank you adding the source I was able to have it install the assesmbly. |
We are glad that the problem is resolved at your side. |
If I add some text to the top of Excel File then for some reason the last item in my chart will display the value but not the associated text. If the code that adds the text is not there then it displays the chart with no issue . i have tried adjusting the rows manually but no luck
Report12 shows the chart displaying properely
Report13 shows the chart missing text for last item
