WorkBook 을 이용해서 GridView 엑셀로 내려받기

WorkBook 을 이용해서 GridView 엑셀로 내려받기

워크북(WorkBook)을 이용해서 ASP.NET의 그리드 뷰(GridView)의 내용을

엑셀(EXCEL) 파일로 다운받는는 방법.

기존 웹 HTML형식의 EXCEL 이 아니라

정통 EXCEL로 내려받는 방법이다.

EXCEL CLASS 들은 마샬링을 해줘야 프로그램 종료후에도

프로세스상에서 안남아 있는다.

마샬링 방법은 이전에 올린자료 참조

Introduction

This post shows that how you can export data from GridView to Excel and formatting Excel file in C#.

Follow the below process:

1. Add the reference below in your project:
 Collapse | Copy Code

Microsoft Office 12.0 Control Library

2. Now add a reference to the namespace in the page in which you want to use this functionality:
 Collapse | Copy Code

using Microsoft.Office.Interop.Excel;

3. In the Click event of the button (Export to Excel), call this function:
 Collapse | Copy Code

private void ExportToExcel()
{
    //First fetch all records from grid to dataset
    DataSet dset = new DataSet();
    dset.Tables.Add();
    //First Add Columns from gridview to excel
    for (int i = 0; i < gridView.Columns.Count; i++) //GridView is id of gridview
    {
        dset.Tables[0].Columns.Add(gridView.Columns[i].HeaderText);
    }
    //add rows to the table 
    System.Data.DataRow dr1;
    for (int i = 0; i < gridView.Rows.Count; i++)
    {
        dr1 = dset.Tables[0].NewRow(); //For Example There are only 3 columns into gridview
        System.Web.UI.WebControls.Label lblCCName = 
          (System.Web.UI.WebControls.Label)gridView.Rows[i].Cells[0].FindControl("lblCCName");
        System.Web.UI.WebControls.Label lblItemName = 
          (System.Web.UI.WebControls.Label)gridView.Rows[i].Cells[0].FindControl("lblItemName");
        System.Web.UI.WebControls.Label lblItemCode = 
          (System.Web.UI.WebControls.Label)gridView.Rows[i].Cells[0].FindControl("lblItemCode");
        dr1[0] = lblCCName.Text.ToString();
        dr1[1] = lblItemName.Text.ToString();
        dr1[2] = lblItemCode.Text.ToString(); 
        dset.Tables[0].Rows.Add(dr1); 
    }
    //below code is export dset to excel
    ApplicationClass excel = new ApplicationClass();
    Workbook wBook;
    Worksheet wSheet; 
    wBook = excel.Workbooks.Add(System.Reflection.Missing.Value);
    wSheet = (Worksheet)wBook.ActiveSheet;
    System.Data.DataTable dt = dset.Tables[0];
    System.Data.DataColumn dc = new DataColumn();
    int colIndex = 0;
    int rowIndex = 4;
    foreach (DataColumn dcol in dt.Columns)
    {
        colIndex = colIndex + 1;
        excel.Cells[5, colIndex] = dcol.ColumnName;
    }
    foreach (DataRow drow in dt.Rows) 
    {
        rowIndex = rowIndex + 1;
        colIndex = 0;
        foreach (DataColumn dcol in dt.Columns)
        {
            colIndex = colIndex + 1;
            excel.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];
        }
    }
    wSheet.Columns.AutoFit();
    // Server File Path Where you want to save excel file.
    String strFileName = Server.MapPath("~\\Images\\StockStatement.xls");
    Boolean blnFileOpen = false;
    try
    {
        System.IO.FileStream fileTemp = File.OpenWrite(strFileName);
        fileTemp.Close();
    }
    catch
    {
        blnFileOpen = false;
    }
    if (System.IO.File.Exists(strFileName))
    //It checks if file exists then it delete that file.
    {
        System.IO.File.Delete(strFileName);
    }  
} 
 
//For Saving excel file on Server
wBook.SaveAs(strFileName, XlFileFormat.xlExcel12, 
  System.Reflection.Missing.Value, System.Reflection.Missing.Value, 
  false, false, XlSaveAsAccessMode.xlShared, 
  XlSaveConflictResolution.xlLocalSessionChanges, false, 
  System.Reflection.Missing.Value, System.Reflection.Missing.Value, false);

4. Before saving, you can format the Excel data using the below code:
 Collapse | Copy Code

Range oRng;
wSheet.Cells[1, 2] = lblOffice1.Text;
wSheet.Cells[3, 2] = lblCostCenter1.Text;
wSheet.Cells[4, 1] = lblOfficeName1.Text;
wSheet.get_Range("B1", "B1").Font.Bold = true;
wSheet.get_Range("B1", "B1").Font.ColorIndex = 55;
wSheet.get_Range("B3", "B3").Font.ColorIndex = 55;
wSheet.get_Range("A4", "A4").Font.ColorIndex = 55;
wSheet.get_Range("B1", "E1").Merge(Type.Missing);
wSheet.get_Range("B3", "E3").Merge(Type.Missing);
wSheet.get_Range("B1", "B1").HorizontalAlignment = Constants.xlCenter;
wSheet.get_Range("B3", "B3").HorizontalAlignment = Constants.xlCenter;
wSheet.get_Range("B3", "B3").Font.Bold = true;
wSheet.get_Range("A4", "A4").Font.Bold = true;
wSheet.get_Range("A4", "A4").HorizontalAlignment = Constants.xlLeft;
wSheet.get_Range("A5", "P5").Font.Bold = true;
wSheet.get_Range("A5", "P5").Interior.ColorIndex = 43;
wSheet.Name = "Stock Statement";
 
//AutoFit columns
oRng = wSheet.get_Range("A1", "P1"); 
oRng.EntireColumn.AutoFit();

You can use the above code for exporting data to Excel using a GridView in C#. It saves the Excel file to server. You can use the download code for saving an Excel file from server to the client system.

Point of Interest

Using this code, you will get a proper Excel file and formatted. You can use formulae on columns. There should be full permissions to IIS user and Network Service User.

When you use the above code, if there are issues, then add below code in the web.config file:

 Collapse | Copy Code

<identity impersonate="true" />

If anybody has any issues with this topic, send me comments. I will provide solutions.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

출처 : http://www.codeproject.com/Tips/333627/Export-to-Excel-from-GridView-in-Csharp