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.
Source
This is XSLT sample file format for a gridview.
="1.0"="utf-8"
<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">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1" />
<Font ss:FontName="Arial" x:Family="Swiss" ss:Bold="1" />
</Style>
<Style ss:ID="HeaderTop">
<Alignment ss:Horizontal='Left' ss:Vertical='Top' ss:WrapText='0' />
<Font ss:FontName='Arial' x:Family='Swiss' ss:Bold='1' />
</Style>
<Style ss:ID="HeaderYellow">
<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" />
<Interior ss:Color="#FFFF00" ss:Pattern="Solid" />
</Style>
<Style ss:ID="Percentage">
<NumberFormat ss:Format="0\%" />
</Style>
<Style ss:ID="HeaderStyle">
<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" />
<Interior ss:Color="#D8D8D8" ss:Pattern="Solid" />
<NumberFormat ss:Format="@" />
</Style>
<Style ss:ID="StringDataStyle">
<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" />
<NumberFormat ss:Format="@" />
</Style>
<Style ss:ID="SpecificStringDataStyle">
<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" />
<NumberFormat ss:Format="@" />
</Style>
</Styles>
<Worksheet ss:Name="New Patients">
<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();
}
}