Aspose and .rdlc file

Aspose.Cells Product Family
1

Hi

We are doing a webapp using visual studio 2005 C#.

The objective of the webapp is to display/generate a report based on user’s entered criteria to an excel file. We used .rdlc file in visual studio to design the report with datasource and datatable.

The file is being generated and data are being shown on the excel file correctly. we even managed to put a password to the excel file, using aspose, before the user can open the report (user clicks the report’s link > prompt to Opne/Save the file > excel opens prompting for the password > PW correct report opens with the data.)

However, we want to apply some formating to the generated excel file like ‘freezepanes’, ‘merge’, ‘putvalue’, and also the generated excel file doesn’t display gridlines (cell borders) it white allover plus the black fonts of the report data.

we tried the codes that can be found here in this site but nothing seems to happen when we try it,

i can’t recall the exact codes but what we did is like this:

instatntiate workbook and worksheets (worksheet index = 0 to get the 1st sheet, we only have 1 sheet)
----this is where we put the codes for freezepanes, merge, putvalue, and IsGridlinesVisible = true)
then we open a temp file (filename_dummy.xls) to add the password
then we save the file (filename.xls)

i’m sorry if i can’t provide the exact codes as i am currently at home and the codes are at our office. but i thought if the password protection is working fine then what might be the reason the other commands won’t work?

thanks

2

Hi,

Thank you for considering Aspose.

Well, you are right, if password protection is working fine then other properties should also work fine. But I think we need to check your template file and your code to get to the exact problem / issue. Please share the code and template file, so we can figure out the problem soon.

Thank You & Best Regards,

3

Hi,

I think you need to correct your mentioned steps, please follow them as provided below.

1) instatntiate workbook
2) open a temp file (filename_dummy.xls)
3) instantiate worksheets (worksheet index = 0 to get the 1st sheet, we only have 1 sheet)
4) put the codes for freezepanes, merge, putvalue, and IsGridlinesVisible = true
5) add the file encryption password
6) save the file (filename.xls)

If you still find the issue, kindly share your code and template file as Nausherwan has suggested you.

Thank you.

4

Hi

Thanks for responding. :)

here's the part of our code where we generate and format the excel file:

public void ShowReport(byte[] renderedBytes,int reportParameterKey)
{
string parameterName = "";
using (Common com = new Common())
{
using (DBLibrary dbLib = new DBLibrary(Common.DATABASENAME_DB))
{
using (SqlCommand comm = dbLib.GetStoredProcCommand(PR_MY_STORED_PROCEDURE))
{
comm.Parameters.Add(SPR_PARAM_REPORTPARAMETERKEY, SqlDbType.Int).Value = reportParameterKey;
SqlDataReader rdr = dbLib.ExecuteReader(comm);
rdr.Read();
parameterName = rdr.GetString(1);
}

com.SaveExcelReport(renderedBytes,parameterName + "_dummy");

FormatProtectExcel(parameterName, reportParameterKey);

}
}

}


private void FormatProtectExcel(string parameterName, int reportParameterKey)
{
try
{
using (Common comm = new Common())
{
Workbook excel = new Workbook();
Worksheet worksheet = excel.Worksheets[0];

reportpath = FMConfiguration.GetAppSetting("ExcelReportPath").ToString() + parameterName;

excel.Open(reportpath + "_dummy.xls");
worksheet.IsGridlinesVisible = true;

worksheet.FreezePanes(9, 5, 9, 5);
excel.Password = FMConfiguration.GetAppSetting("ExcelPassword").ToString();
excel.Save(reportpath + ".xls", FileFormatType.Default);
this.parameterName = parameterName;

}

}
catch
{
throw;
}
}

*Update*

i've included this code and this worked for me for unmerge-ing some cells. We still have problems with "PutValue" and "IsGridlinesVisible" functions

Range range = excel.Worksheets[0].Cells.CreateRange("B4","F5");

range.UnMerge();

5

Hi,

Thank you for considering Aspose.

Well, your steps are not right (as Amjad has already suggested). After instantiating the workbook object you should open your workbook and the get the worksheet and perform your operations. I have updated your provided method as required,

private void FormatProtectExcel(string parameterName, int reportParameterKey)

{

try

{

using (Common comm = new Common())

{

Workbook excel = new Workbook();

reportpath = FMConfiguration.GetAppSetting("ExcelReportPath").ToString() + parameterName;

excel.Open(reportpath + "_dummy.xls");

Worksheet worksheet = excel.Worksheets[0];

worksheet.IsGridlinesVisible = true;

worksheet.FreezePanes(9, 5, 9, 5);

excel.Password = FMConfiguration.GetAppSetting("ExcelPassword").ToString();

excel.Save(reportpath + ".xls", FileFormatType.Default);

this.parameterName = parameterName;

}

}

catch

{

throw;

}

}

Please do let us know if you still face any problem, we will be happy to help you out.

Thank You & Best Regards,

6

wow...it makes sense. will try that later and will let you know what happens.

thanks so much