Hi,
I posted code for you, generate excel file for gridview. I dont know is there is a way to give password for protect a excel file, while generating. Eventhough i give a tip for it. Using XSLT you can generated excel file for a gridview, may have option for protect a excel file in XSLT. You can provide format in XSLT file. Try this tip.
This is XSLT sample file format for a gridview.
<xsl:stylesheet version="1.0" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="urn:my-scripts" xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<xsl:template match="NewPatients">
<workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office">
xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<documentproperties xmlns="urn:schemas-microsoft-com:office:office"></documentproperties>
<excelworkbook xmlns="urn:schemas-microsoft-com:office:excel"></excelworkbook>
<styles>
<style ss:id="Header" xmlns:ss="#unknown">
<alignment ss:horizontal="Center" ss:vertical="Bottom" ss:wraptext="1" />
<font ss:fontname="Arial" x:family="Swiss" ss:bold="1" xmlns:x="#unknown" />
</style>
<style ss:id="HeaderTop" xmlns:ss="#unknown">
<alignment ss:horizontal="Left" ss:vertical="Top" ss:wraptext="0" />
<font ss:fontname="Arial" x:family="Swiss" ss:bold="1" xmlns:x="#unknown" />
</style>
<style ss:id="HeaderYellow" xmlns:ss="#unknown">
<alignment ss:horizontal="Center" ss:vertical="Bottom" ss:wraptext="1" />
<borders>
<border ss:position="Bottom" ss:linestyle="Continuous" ss:weight="1" />
<border ss:position="Left" ss:linestyle="Continuous" ss:weight="1" />
<border ss:position="Right" ss:linestyle="Continuous" ss:weight="1" />
<border ss:position="Top" ss:linestyle="Continuous" ss:weight="1" />
</borders>
<font ss:fontname="MS Sans Serif" x:family="Swiss" ss:color="#FF0000" ss:bold="1" xmlns:x="#unknown" />
<interior ss:color="#FFFF00" ss:pattern="Solid" />
</style>
<style ss:id="Percentage" xmlns:ss="#unknown">
<numberformat ss:format="0\%" />
</style>
<style ss:id="HeaderStyle" xmlns:ss="#unknown">
<alignment ss:horizontal="Center" ss:vertical="Center" ss:wraptext="1" />
<borders>
<border ss:position="Bottom" ss:linestyle="Continuous" ss:weight="1" />
<border ss:position="Top" ss:linestyle="Continuous" ss:weight="1" />
</borders>
<font ss:fontname="Arial" x:family="Swiss" ss:size="9" ss:color="#000000" ss:bold="1" xmlns:x="#unknown" />
<interior ss:color="#D8D8D8" ss:pattern="Solid" />
<numberformat ss:format="@" />
</style>
<style ss:id="StringDataStyle" xmlns:ss="#unknown">
<alignment ss:horizontal="Left" ss:vertical="Top" ss:wraptext="1" />
<borders>
<border ss:position="Bottom" ss:linestyle="Continuous" ss:weight="1" />
<border ss:position="Top" ss:linestyle="Continuous" ss:weight="1" />
</borders>
<font ss:fontname="Arial" x:family="Swiss" ss:size="9" ss:color="#000000" xmlns:x="#unknown" />
<numberformat ss:format="@" />
</style>
<style ss:id="SpecificStringDataStyle" xmlns:ss="#unknown">
<alignment ss:horizontal="Right" ss:vertical="Top" ss:wraptext="1" />
<borders>
<border ss:position="Bottom" ss:linestyle="Continuous" ss:weight="1" />
<border ss:position="Top" ss:linestyle="Continuous" ss:weight="1" />
</borders>
<font ss:fontname="Arial" x:family="Swiss" ss:size="9" ss:color="#000000" xmlns:x="#unknown" />
<numberformat ss:format="@" />
</style>
</styles>
<worksheet ss:name="New Patients" xmlns:ss="#unknown">
<table xsl:version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<column ss:autofitwidth="0" ss:width="90" />
<column ss:autofitwidth="0" ss:width="110" />
<column ss:autofitwidth="0" ss:width="110" />
<column ss:autofitwidth="0" ss:width="70" />
<column ss:autofitwidth="0" ss:width="90" />
<column ss:autofitwidth="0" ss:width="130" />
<column ss:autofitwidth="0" ss:width="90" />
<column ss:autofitwidth="0" ss:width="90" />
<column ss:autofitwidth="0" ss:width="100" />
<column ss:autofitwidth="0" ss:width="100" />
<row>
<cell ss:styleid="HeaderTop">
<data ss:type="String">InfuScience - Clinical Progress Tracking</data>
</cell>
</row>
<row>
<cell ss:styleid="HeaderTop">
<data ss:type="String">New Patients</data>
</cell>
</row>
<row>
<cell ss:styleid="HeaderStyle">
<data ss:type="String">MRN</data>
</cell>
<cell ss:styleid="HeaderStyle">
<data ss:type="String">First Name</data>
</cell>
<cell ss:styleid="HeaderStyle">
<data ss:type="String">Last Name</data>
</cell>
<cell ss:styleid="HeaderStyle">
<data ss:type="String">Therapy</data>
</cell>
<cell ss:styleid="HeaderStyle">
<data ss:type="String">Sales Rep.</data>
</cell>
<cell ss:styleid="HeaderStyle">
<data ss:type="String">Physician</data>
</cell>
<cell ss:styleid="HeaderStyle">
<data ss:type="String">Status</data>
</cell>
<cell ss:styleid="HeaderStyle">
<data ss:type="String">Start Date</data>
</cell>
<cell ss:styleid="HeaderStyle">
<data ss:type="String">Tracking Count</data>
</cell>
<cell ss:styleid="HeaderStyle">
<data ss:type="String">Tracked Count</data>
</cell>
</row>
<xsl:for-each select="Patient">
<row>
<cell ss:styleid="StringDataStyle">
<data ss:type="String">
<xsl:value-of select="MRN" />
</data>
</cell>
<cell ss:styleid="StringDataStyle">
<data ss:type="String">
<xsl:value-of select="FirstName" />
</data>
</cell>
<cell ss:styleid="StringDataStyle">
<data ss:type="String">
<xsl:value-of select="LastName" />
</data>
</cell>
<cell ss:styleid="StringDataStyle">
<data ss:type="String">
<xsl:value-of select="Therapy" />
</data>
</cell>
<cell ss:styleid="StringDataStyle">
<data ss:type="String">
<xsl:value-of select="SalesRep" />
</data>
</cell>
<cell ss:styleid="StringDataStyle">
<data ss:type="String">
<xsl:value-of select="Physician" />
</data>
</cell>
<cell ss:styleid="StringDataStyle">
<data ss:type="String">
<xsl:value-of select="Status" />
</data>
</cell>
<cell ss:styleid="StringDataStyle">
<data ss:type="String">
<xsl:value-of select="StartDate" />
</data>
</cell>
<cell ss:styleid="SpecificStringDataStyle">
<data ss:type="String">
<xsl:value-of select="TrackingCount" />
</data>
</cell>
<cell ss:styleid="SpecificStringDataStyle">
<data ss:type="String">
<xsl:value-of select="TrackedCount" />
</data>
</cell>
</row>
</xsl:for-each>
</table>
<worksheetoptions xmlns="urn:schemas-microsoft-com:office:excel">
<selected />
<freezepanes />
<frozennosplit />
<splithorizontal>3</splithorizontal>
<toprowbottompane>3</toprowbottompane>
<activepane>2</activepane>
<panes>
<pane>
<number>3</number>
</pane>
<pane>
<number>2</number>
<activerow>2</activerow>
</pane>
</panes>
<protectobjects>False</protectobjects>
<protectscenarios>False</protectscenarios>
</worksheetoptions>
</worksheet>
</workbook>
</xsl:template>
</xsl:stylesheet>
and this is for read XSLT file and transform to excel,
public void ExportNewPatientsToExcel()
{
logger.Info("New Patients :: export to excel");
string fileDirectory = string.Empty;
if (Session[Constants.SESSION_FILE_DIRECTORY] != null)
fileDirectory = Session[Constants.SESSION_FILE_DIRECTORY].ToString();
else
{
logger.Error("New Patients::File Cache folder is not set.");
Response.Redirect(Constants.PAGE_ERROR);
}
HttpContext context = HttpContext.Current;
try
{
string xsltFileName = Context.Server.MapPath(Constants.NEW_PATIENTS_XSLT_FILE_NAME);
PatientCollection patientCollection = PatientBAO.GetNewPatients(ShowAllPatient);
if (patientCollection.Count > 0 && patientCollection != null)
{
string fileName = PatientBAO.GenerateNewPatientsAsExcel(fileDirectory, xsltFileName, patientCollection);
logger.Info("New Patients Excel version saved name :" + fileName);
string fileNamePart = fileName.Substring(fileName.LastIndexOf("\\") + 1);
fileNamePart = fileNamePart.Substring(fileNamePart.IndexOf("_") + 1);
context.Items.Add(Constants.ENABLE_CACHE_SZ, Constants.ENABLE_CACHE);
context.Response.ClearContent();
context.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileNamePart);
context.Response.ContentType = "application/octet-stream";
context.Response.TransmitFile(fileName);
}
else
{
ShowPopUp(Resources.Patient.RecordNotFoundToExportExcel);
logger.Error("New patients data not found for export to excel.");
}
}
catch (Exception exc)
{
logger.ErrorException("Error occured while export patient details to excel.", exc);
}
finally
{
context.Response.End();
}
}
Submit an article or tip