C# 操作EXCEL(C#中的數據導出EXCEL)

2019-11-17 04:03:12

字體：大中小

來源：轉載

供稿：網友

今天開發項項目剛好要用到一個導出Excel的功能，在網上找了很久，就是沒有一個合適的，于是我自己寫了一個通用的類，希望對大家也有所幫助。

view plaincopy to clipboardPRint?
·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Web;
using Microsoft.Office.Interop.Excel;
/*
* 開發人員：小笨蛋
* 時間：2009年11月17日
* 功能：將數據導出Excel
*
*/
namespace XT.LiTree.Logic
{
    public class ExcelExport
    {
        /// <summary>
        /// 直接導出Excel
        /// </summary>
        /// <param name="ds">數據源DataSet</param>
        /// <param name="fileName">保存文件名(例如：E:/a.xls)</param>
        /// <returns></returns>
        public bool DoExport(DataSet ds, string fileName)
        {
            if (ds.Tables.Count == 0 || fileName == string.Empty)
            {
                return false;
            }
            application excel = new ApplicationClass();
            int rowindex = 1;
            int colindex = 0;
            Workbook work = excel.Workbooks.Add(true);
            //Worksheet sheet1 = (Worksheet)work.Worksheets[0];
            System.Data.DataTable table = ds.Tables[0];
            foreach (DataColumn col in table.Columns)
            {
                colindex++;
                excel.Cells[1, colindex] = col.ColumnName;
            }
            foreach (DataRow row in table.Rows)
            {
                rowindex++;
                colindex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colindex++;
                    excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();
                }
            }
            excel.Visible = false;
            //((Worksheet)work.Sheets[0]).Name = "sss";
            excel.ActiveWorkbook.SaveAs(fileName, XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAs accessMode.xlNoChange, null, null, null, null, null);
            excel.Quit();
            excel = null;
            GC.Collect();
            return true;
        }
        /// <summary>
        /// 直接導出Excel
        /// </summary>
        /// <param name="ds">數據源DataSet</param>
        /// <param name="columns">列名數組,允許為空(columns=null),為空則表使用默認數據庫列名 </param>
        /// <param name="fileName">保存文件名(例如：E:/a.xls)</param>
        /// <returns></returns>
        public bool DoExport(DataSet ds, string[] columns, string fileName)
        {
            if (ds.Tables.Count == 0 || fileName == string.Empty)
            {
                return false;
            }
            Application excel = new ApplicationClass();
            int rowindex = 1;
            int colindex = 0;
            Workbook work = excel.Workbooks.Add(true);
            //Worksheet sheet1 = (Worksheet)work.Worksheets[0];
            System.Data.DataTable table = ds.Tables[0];
            if (columns != null)
            {
                for (int i = 0; i < columns.Length; i++)
                {
                    colindex++;
                    if (columns[i] != null && columns[i] != "")
                    {
                        excel.Cells[1, colindex] = columns[i];
                    }
                    else
                    {
                        excel.Cells[1, colindex] = table.Columns[i].ColumnName;
                    }
                }
            }
            else
            {
                foreach (DataColumn col in table.Columns)
                {
                    colindex++;
                    excel.Cells[1, colindex] = col.ColumnName;
                }
            }
            foreach (DataRow row in table.Rows)
            {
                rowindex++;
                colindex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colindex++;
                    excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();
                }
            }
            excel.Visible = false;
            //((Worksheet)work.Sheets[0]).Name = "sss";
            excel.ActiveWorkbook.SaveAs(fileName, XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            excel.Quit();
            excel = null;
            GC.Collect();
            return true;
        }

        /// <summary>
        /// 直接導出Excel
        /// </summary>
        /// <param name="sql">SQL查詢語句</param>
        /// <param name="columns">列名數組</param>
        /// <param name="fileName">保存文件名(例如：E:/a.xls)</param>
        /// <returns></returns>
        public bool DoExport(string sql, string[] columns, string fileName)
        {
            Dao.DataBase db = new XT.LiTree.Dao.DataBase();
            DataSet ds = db.GetDS(sql);
            if (ds.Tables.Count == 0 || fileName == string.Empty)
            {
                return false;
            }
            Application excel = new ApplicationClass();
            int rowindex = 1;
            int colindex = 0;
            Workbook work = excel.Workbooks.Add(true);
            //Worksheet sheet1 = (Worksheet)work.Worksheets[0];
            System.Data.DataTable table = ds.Tables[0];
            if (columns != null)
            {
                for (int i = 0; i < columns.Length; i++)
                {
                    colindex++;
                    if (columns[i] != null && columns[i] != "")
                    {
                        excel.Cells[1, colindex] = columns[i];
                    }
                    else
                    {
                        excel.Cells[1, colindex] = table.Columns[i].ColumnName;
                    }
                }
            }
            else
            {
                foreach (DataColumn col in table.Columns)
                {
                    colindex++;
                    excel.Cells[1, colindex] = col.ColumnName;
                }
            }
            foreach (DataRow row in table.Rows)
            {
                rowindex++;
                colindex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colindex++;
                    excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();
                }
            }
            excel.Visible = false;
            //((Worksheet)work.Sheets[0]).Name = "sss";
            excel.ActiveWorkbook.SaveAs(fileName, XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            excel.Quit();
            excel = null;
            GC.Collect();
            return true;
        }
        /// <summary>
        /// 通過流導出Excel
        /// </summary>
        /// <param name="ds">數據源DataSet</param>
        /// <param name="fileName">保存文件名(例如：a.xls)</param>
        /// <returns></returns>
        public bool StreamExport(DataSet ds, string fileName)
        {
            if (ds.Tables.Count == 0 || fileName == string.Empty)
            {
                return false;
            }
            System.Data.DataTable dt = ds.Tables[0];
            StringBuilder content = new StringBuilder();
            StringBuilder strtitle = new StringBuilder();
            content.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>");
            content.Append("<head><title></title><meta http-equiv='Content-Type' content=/"text/html; charset=gb2312/"></head><body><table x:str cellspacing='0' rules='all' border='1' id='title1' style="border-collapse:collapse;" mce_style="border-collapse:collapse;">");
            content.Append("<tr>");
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                content.Append("<td>" + dt.Columns[j].ColumnName + "</td>");
            }
            content.Append("</tr>/n");
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                content.Append("<tr>");
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    content.Append("<td>" + dt.Rows[j][k].ToString() + "</td>");
                }
                content.Append("</tr>/n");
            }
            content.Append("</table></body></html>");
            content.Replace(" ", "");
            //fileContent = (byte[])System.Text.Encoding.Default.GetBytes(content.ToString());
            //System.Web.UI.WebControls.
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.Buffer = true;
            System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            System.Web.HttpContext.Current.Response.Charset = "GB2312";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);//HttpUtility.UrlEncode(fileName));
            //            HttpUtility.UrlEncode(fileName,Encoding.UTF8);
            //System.Web.HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.Default;
            System.Web.HttpContext.Current.Response.Write(content.ToString());
            System.Web.HttpContext.Current.Response.End();
            return true;
        }
        /// <summary>
        /// 通過流導出Excel
        /// </summary>
        /// <param name="ds">數據源DataSet</param>
        /// <param name="columns">列名數組,允許為空(columns=null),為空則表使用默認數據庫列名</param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public bool StreamExport(DataSet ds, string[] columns, string fileName)
        {
            if (ds.Tables.Count == 0 || fileName == string.Empty)
            {
                return false;
            }
            System.Data.DataTable dt = ds.Tables[0];
            StringBuilder content = new StringBuilder();
            StringBuilder strtitle = new StringBuilder();
            content.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>");
            content.Append("<head><title></title><meta http-equiv='Content-Type' content=/"text/html; charset=gb2312/"></head><body><table x:str cellspacing='0' rules='all' border='1' id='title1' style="border-collapse:collapse;" mce_style="border-collapse:collapse;">");
            content.Append("<tr>");
            if (columns != null)
            {
                for (int i = 0; i < columns.Length; i++)
                {
                    if (columns[i] != null && columns[i] != "")
                    {
                        content.Append("<td>" + columns[i] + "</td>");
                    }
                    else
                    {
                        content.Append("<td>" + dt.Columns[i].ColumnName + "</td>");
                    }
                }
            }
            else
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    content.Append("<td>" + dt.Columns[j].ColumnName + "</td>");
                }
            }
            content.Append("</tr>/n");
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                content.Append("<tr>");
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    content.Append("<td>" + dt.Rows[j][k].ToString() + "</td>");
                }
                content.Append("</tr>/n");
            }
            content.Append("</table></body></html>");
            content.Replace(" ", "");
            //fileContent = (byte[])System.Text.Encoding.Default.GetBytes(content.ToString());
            //System.Web.UI.WebControls.
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.Buffer = true;
            System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            System.Web.HttpContext.Current.Response.Charset = "GB2312";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);//HttpUtility.UrlEncode(fileName));
            //            HttpUtility.UrlEncode(fileName,Encoding.UTF8);
            //System.Web.HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.Default;
            System.Web.HttpContext.Current.Response.Write(content.ToString());
            System.Web.HttpContext.Current.Response.End();
            return true;
        }
        /// <summary>
        /// 通過流導出Excel
        /// </summary>
        /// <param name="ds">數據源DataSet</param>
        /// <param name="columns">列名數組,允許為空(columns=null),為空則表使用默認數據庫列名</param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public bool StreamExport(string sql, string[] columns, string fileName)
        {
            Dao.DataBase db = new XT.LiTree.Dao.DataBase();
            DataSet ds = db.GetDS(sql);
            if (ds.Tables.Count == 0 || fileName == string.Empty)
            {
                return false;
            }
            System.Data.DataTable dt = ds.Tables[0];
            StringBuilder content = new StringBuilder();
            StringBuilder strtitle = new StringBuilder();
            content.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>");
            content.Append("<head><title></title><meta http-equiv='Content-Type' content=/"text/html; charset=gb2312/"></head><body><table x:str cellspacing='0' rules='all' border='1' id='title1' style="border-collapse:collapse;" mce_style="border-collapse:collapse;">");
            content.Append("<tr>");
            if (columns != null)
            {
                for (int i = 0; i < columns.Length; i++)
                {
                    if (columns[i] != null && columns[i] != "")
                    {
                        content.Append("<td>" + columns[i] + "</td>");
                    }
                    else
                    {
                        content.Append("<td>" + dt.Columns[i].ColumnName + "</td>");
                    }
                }
            }
            else
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    content.Append("<td>" + dt.Columns[j].ColumnName + "</td>");
                }
            }
            content.Append("</tr>/n");
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                content.Append("<tr>");
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    content.Append("<td>" + dt.Rows[j][k].ToString() + "</td>");
                }
                content.Append("</tr>/n");
            }
            content.Append("</table></body></html>");
            content.Replace(" ", "");
            //fileContent = (byte[])System.Text.Encoding.Default.GetBytes(content.ToString());
            //System.Web.UI.WebControls.
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.Buffer = true;
            System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            System.Web.HttpContext.Current.Response.Charset = "GB2312";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);//HttpUtility.UrlEncode(fileName));
            //            HttpUtility.UrlEncode(fileName,Encoding.UTF8);
            //System.Web.HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.Default;
            System.Web.HttpContext.Current.Response.Write(content.ToString());
            System.Web.HttpContext.Current.Response.End();
            return true;
        }
    }
}

上一篇：ASP.NET - 使用 ASP.NET Web 服務器控件（一）

下一篇：C#委托,事件理解入門