How to export my Grid to Excel with Password proteced

SADAM HUSSAIN

9y1k

I have a code but got error.plz help me

Excel.Application xlApp;

Excel.Workbook xlWorkBook;

Excel.Worksheet xlWorkSheet;

object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.Application();

xlWorkBook = xlApp.Workbooks.Add(misValue);

xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

int CCol = 1;

for (int j = 1; j <= dgcBankSetup.Columns.Count; j++)

{

xlWorkSheet.Cells[1, CCol] = dgcBankSetup.Columns[j].Header;

xlWorkSheet.Cells[1, CCol].ColumnWidth = dgcBankSetup.Columns[j].Width;

xlWorkSheet.Cells[1, CCol].Font.Bold = true;

CCol = CCol + 1;

}

foreach (DataRowView i in dgcBankSetup.ItemsSource)

{

DataRow DR = i.Row;

CCol = 1;

for (int j = 1; j <= dgcBankSetup.Columns.Count; j++)

{

xlWorkSheet.Cells[i, CCol].value = dgcBankSetup.rows

//gcBankSetup.SelectAllCells();

CCol = CCol + 1;

}

xlWorkSheet.Cells.Borders.LineStyle = 1;

xlWorkBook.SaveAs(SaveDialogBox.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

xlWorkBook.Close(true, misValue, misValue);

xlApp.Quit();

releaseObject(xlWorkSheet);

releaseObject(xlWorkBook);

releaseObject(xlApp);

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ayappan Alagesan9y ago
use cryptography dll for encrypt and decrypt the password during the export and import the data.
Manas Mohapatra9y ago
It is straight forward. As per your comment, my first answer is working.

Follow below link that will help you to export grid data to excel then append my first code to export grid code.

http://www.aspsnippets.com/Articles/Export-GridView-to-Excel-in-ASPNet-with-Formatting-using-C-and-VBNet.aspx
SADAM HUSSAIN9y ago
Yes , Its working but how to export my data grid to excel give me the suggestion

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int CCol = 1;
for (int j = 1; j <= dgcBankSetup.Columns.Count; j++)
{
xlWorkSheet.Cells[1, CCol] = dgcBankSetup.Columns[j].Header;
xlWorkSheet.Cells[1, CCol].ColumnWidth = dgcBankSetup.Columns[j].Width;
xlWorkSheet.Cells[1, CCol].Font.Bold = true;
CCol = CCol + 1;
}
foreach (DataRowView i in dgcBankSetup.ItemsSource)
{
DataRow DR = i.Row;
CCol = 1;
// I'm struck off this area plz give me the suggestions
for (int j = 1; j <= dgcBankSetup.Columns.Count; j++)
{
xlWorkSheet.Cells[i, CCol].value = dgcBankSetup.rows
//gcBankSetup.SelectAllCells();
CCol = CCol + 1;
}
}
xlWorkSheet.Cells.Borders.LineStyle = 1;
xlWorkBook.Password = "1234";
xlWorkBook.SaveAs(SaveDialogBox.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}

private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
Manas Mohapatra9y ago
Below code may help you:
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;

static class Module1
{
    public static string Filename;
    public static Excel.Application oexcel;
    public static Excel.Workbook obook;
    public static Excel.Worksheet osheet;
    public static void Main()
    {
        try {
            //File name and path, here i used abc file to be
            //stored in Bin directory in the sloution directory
            Filename = AppDomain.CurrentDomain.BaseDirectory + "abc.xls";
            //check if file already exists then delete
            //it to create a new file
            if (File.Exists(Filename)) {
                File.Delete(Filename);
            }
            if (!File.Exists(Filename)) {
                //create new excel application
                oexcel = Interaction.CreateObject("Excel.Application");
                oexcel.Application.DisplayAlerts = false;
                //add a new workbook
                obook = oexcel.Workbooks.Add;
                oexcel.Visible = true;

                Console.WriteLine("Generating Auto Report");
                osheet = oexcel.Worksheets(1);
                osheet.Name = "Test Sheet";
                osheet.Range("A1:G1").Merge();
                osheet.Range("A1").Value = "Implementing " + "Password Security on Excel " + "Workbook Using Studio.Net";
                osheet.Range("A1").Font.Color = Information.RGB(255, 255, 255);
                osheet.Range("A1").Interior.ColorIndex = 5;
                osheet.Range("A1").Font.Bold = true;


                //password variable and value
                string password = "abc";
                //if you are using excel 11 or above
                //then use the following code
                obook.Password = password;
                obook.SaveAs(Filename);
                //otherwise use the folowing one
                obook.SaveAs(Filename, password: password);
                //end application object and session
                osheet = null;
                obook.Close();
                obook = null;
                oexcel.Quit();
                oexcel = null;
            }
        } catch (Exception ex) {
            // Log error message
        } finally {
        }
    }
}
Note: You need to change code as per your logic.