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.

create Excel file

Thread ID:

Created:

Updated:

Platform:

Replies:

132025 Aug 8,2017 07:06 PM Aug 14,2017 07:34 AM ASP.NET MVC 7
loading
Tags: XlsIO
Miranda Johnson
Asked On August 8, 2017 07:06 PM

I am trying to create my charts in Excel file. I thought first i would create the basic excel file with a table of data. However I am unable to do so.
This is what I tried first
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();
}

which produced the following error message. "Exception Details:System.UnauthorizedAccessException: Access to the path 'C:\Program Files (x86)\IIS Express\ReportGroup1.xlsx' is denied. "
I did not want to save to server anyway but to prompt the user to save to client machine

I then tried this code shown below
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();
}
which produced this error "Exception Details:System.IO.FileNotFoundException: File Sample.xlsx could not be found. Please verify the file path."
I prefer to create the file and open it right away if possible but if not then it is acceptable to prompt the user to save which they can then open. Any help will be appreciated

Mohan Chandran [Syncfusion]
Replied On August 9, 2017 05:46 AM

Hi Miranda, 
 
We have analyzed your code snippet and found that this a usage level issue. Please find the details below. 
                                                                       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); 
 
We have shared a simple sample for your reference which can be downloaded from following location. 
 
Sample Link: 
 
Please let us know if you have any other concerns.  
 
Regards, 
Mohan Chandran. 
 


Miranda Johnson
Replied On August 9, 2017 07:29 PM

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.


Attachment: Big_Tex_Bank_Report_f69b0cb6.zip

Mohan Chandran [Syncfusion]
Replied On August 10, 2017 09:35 AM

 Hi Miranda, 
 
Please refer the below table for your queries. 
 
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. 
 
Chart.IsSeriesInRows = false; 
 
Chart.Legend.Position = ExcelLegendPosition.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.  
Install-package Syncfusion.Presentation.Base45 –source http://nuget.syncfusion.com/windows-forms/  
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).  
 
 
 
We have prepared a sample to achieve your requirement in XlsIO which can be downloaded from the following link. 
 
Regards, 
Mohan Chandran 


Miranda Johnson
Replied On August 10, 2017 06:10 PM


 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.   
Install-package Syncfusion.Presentation.Base45 –source http://nuget.syncfusion.com/windows-forms/  
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?


Mohan Chandran [Syncfusion]
Replied On August 11, 2017 08:35 AM

Hi Miranda, 
 
Please refer the below table for your queries. 
 
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: 
chart.PrimaryValueAxis.Title = "Average Answer"; 
 
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: 
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(new string[] {"Q1"}); 
 
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. 

Please let us know if you have any queries.
 
    
Regards, 
Mohan Chandran 


Miranda Johnson
Replied On August 11, 2017 03:11 PM

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


Attachment: report_16badb55.zip

Mohan Chandran [Syncfusion]
Replied On August 14, 2017 07:34 AM

Hi Miranda,  
 
Thank you for updating us. 
 
We have analyzed the shared excel documents and found that the chart size is different in both Excel documents “Report13” and “Report12” . Category axis text is displayed based on the chart size. So, we request you to adjust the chart size to view the category axis text properly by using chart.TopRow and chart.BottomRow properties. 
 
Please refer the following UG documentation link to position a chart in a worksheet. 
 
UG Documentation link: 
 
Regards,  
Mohan Chandran. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

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.

;