c# 으로 datatable to xlsx 엑셀 파일 다운로드 하기

 

 

- aspx

 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Excels.aspx.cs" Inherits="Zeospace.DeskPlus.Base.Web.Util.Sample.Excels" %>

 

 

 

- aspx.cs

 

여기서 필요한 namespace 인 것은..

 

using OfficeOpenXml;
using System.Data;
using OfficeOpenXml.Style;
using System.Drawing;

 

ExcelPackage 라는 클래스를 사용하기 위해서 EPPlus.dll 를 사용해야 한다.

codeplex 사이트에 존재함. (http://epplus.codeplex.com/)

 

탭 내용 수정 불가하도록 패스워드 걸기위해서,   ws.Protection.SetPassword("패스워드값"); 도 가능함.

 

EPPlus_MakeXlsx.zip

 

 

using System;
using System.Data;
using System.Drawing;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.IO;

namespace Zeospace.DeskPlus.Base.Web.Util.Sample
{
    public partial class Excels : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataTable workTable = new DataTable();

            workTable.Columns.Add(new DataColumn("제목1", typeof(int)));
            workTable.Columns.Add(new DataColumn("제목2", typeof(int)));
            workTable.Columns.Add(new DataColumn("제목3", typeof(int)));

            DataRow workRow;

            for (int i = 1000; i <= 1009; i++)
            {
                workRow = workTable.NewRow();
                workRow.ItemArray = new object[] { i.ToString(), i.ToString(), i.ToString() };
                workTable.Rows.Add(workRow);
            }

            //CreateXlsx(workTable);
            ReadXlsx();
        }

        private void ReadXlsx()
        {
            DataTable tbl = new DataTable();

            string path = Server.MapPath("/deskpluseip/util/sample/exceldemo.xlsx");
            using (var pck = new OfficeOpenXml.ExcelPackage())
            {
                using (var stream = File.OpenRead(path))
                {
                    pck.Load(stream);
                }
                var ws = pck.Workbook.Worksheets[1];
               
                bool hasHeader = true; // adjust it accordingly( i've mentioned that this is a simple approach)
                foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                {
                    tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
                }
                var startRow = hasHeader ? 2 : 1;
                for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                {
                    var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                    var row = tbl.NewRow();
                    foreach (var cell in wsRow)
                    {
                        row[cell.Start.Column - 1] = cell.Text;
                    }
                    tbl.Rows.Add(row);
                }
            }

            foreach (DataRow rows in tbl.Rows)
            {
                string title1 = rows["제목1"].ToString();
                string title2 = rows["제목2"].ToString();
                string title3 = rows["제목3"].ToString();

                Response.Write("제목1" + ":" + title1);
                Response.Write("제목2" + ":" + title2);
                Response.Write("제목3" + ":" + title3);
                Response.Write("<br />");
            }

        }

        private void CreateXlsx(DataTable tbl)
        {
            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");

                //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                ws.Cells["A1"].LoadFromDataTable(tbl, true);

                //Format the header for column 1-3
                using (ExcelRange rng = ws.Cells["A1:C1"])
                {
                    rng.Style.Font.Bold = true;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));  //Set color to dark blue
                    rng.Style.Font.Color.SetColor(Color.White);
                }

                //Example how to Format Column 1 as numeric
                using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
                {
                    col.Style.Numberformat.Format = "#,##0.00";
                    col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                }

                ws.Protection.SetPassword("tlawodns");

                //Write it back to the client
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;  filename=ExcelDemo.xlsx");
                Response.BinaryWrite(pck.GetAsByteArray());
            }
        }
    }
}