导出EXCEL方法汇总

最新推荐文章于 2022-10-19 14:04:37 发布
最新推荐文章于 2022-10-19 14:04:37 发布
阅读量1.8k 收藏
点赞数
分类专栏： # WebForm 文章标签： excel string dataset object application exception
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/david_520042/article/details/7062271
版权

 方法一:

 public void DataBindTitleExcel(Page pPage, DataTable dt, string ExcelTitle, string strUserMsg)
    {
        HttpResponse response = pPage.Response;
        if (dt.Rows.Count == 0)
        {
            response.Write("<script>alert('对不起,没有可用于导出的数据!')</script>");
            response.End();
        }
        response.ContentEncoding = Encoding.GetEncoding("GB2312");
        response.ContentType = "application/ms-excel";
        response.AppendHeader("Content-Disposition", "attachment;filename=Export.xls");
        int count = dt.Columns.Count;
        StringBuilder builder = new StringBuilder();
        builder.Append("<html><head>\n");
        builder.Append("<meta http-equiv=\"Content-Language\" content=\"zh-cn\">\n");
        builder.Append("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">\n");
        builder.Append("</head>\n");
        builder.Append("<table border=1>");
        if (ExcelTitle != "")
        {
            string str = "<font size=4><b>" + ExcelTitle + "</b></font>";
            if (strUserMsg != "")
            {
                str = str + "（" + strUserMsg + "）";
            }
            builder.Append(string.Concat(new object[] { "<tr><td colspan=", count, ">", str, "</td></tr>" }));
        }
        builder.Append("<tr><td colspan=" + count + " valign=middle height=24>");
        builder.Append("查询时间：" + DateTime.Now.ToString("G") + "</td></tr>");
        builder.Append("<tr>\n");
        for (int i = 0; i < count; i++)
        {
            if (dt.Columns[i].Caption.ToString().ToLower() != "id")
            {
                builder.Append("<td bgcolor=#CCFFCC><b>" + dt.Columns[i].Caption.ToString() + "</b></td>\n");
            }
        }
        foreach (DataRow row in dt.Rows)
        {
            builder.Append("<tr>");
            for (int j = 0; j < count; j++)
            {
                if (dt.Columns[j].Caption.ToString().ToLower() != "id")
                {
                    builder.Append("<td style='vnd.ms-excel.numberformat:@'>" + row[j].ToString() + "</td>");
                }
            }
            builder.Append("</tr>\n");
        }
        builder.Append("</table>\n");
        response.Write(builder.ToString());
        response.End();
    }

方法二：

引用Interop.Excel.dll

撰写ExcelExportProvider.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Reflection;
using System.IO;

namespace TCGL.Web.util
{
    /// <summary>
    /// 标题：将 DataSet, DataTable 导出到 Excel
    /// 作者：肖小勇
    /// 日期：2009-10-23
    /// 描述：对之前做的导出 Excel 做调整以支持对 DataSet 及 DataTable 的导出；
    ///             DataSet     导出时可以指定需要导出的 DataTable
    ///             DataTable   导出时可以指定需要导出的 DataColumn 及自定义导出后的列名
    /// </summary>
    public class ExcelExportProvider
    {
        private static object missing = Type.Missing;

        #region " ExportToExcel "

        /// <summary>
        /// 将
        /// </summary>
        /// <param name="fDataSet"></param>
        /// <param name="fFileName"></param>
        public static void ExportToExcel(DataSet fDataSet, String fFileName)
        {
            List<DataTableExportOptions> options = new List<DataTableExportOptions>();

            foreach (DataTable dataTable in fDataSet.Tables)
                options.Add(new DataTableExportOptions(dataTable));

            ExportToExcel(options, fFileName);
        }

        public static void ExportToExcel(DataTable fDataTable, String fFileName)
        {
            ExportToExcel(new DataTableExportOptions(fDataTable), fFileName);
        }

        public static void ExportToExcel(DataTableExportOptions fOption, String fFileName)
        {
            ExportToExcel(new List<DataTableExportOptions>(new DataTableExportOptions[] { fOption }), fFileName);
        }

        /// <summary>
        /// 将 DataTable 导出到 Excel
        /// </summary>
        /// <param name="fOptions"></param>
        public static void ExportToExcel(List<DataTableExportOptions> fOptions, String fFileName)
        {
            if (fOptions == null || fOptions.Count == 0) return;

            try
            {
                if (File.Exists(fFileName))
                    File.Delete(fFileName);
            }
            catch
            {
                return;
            }

            Excel.Application application = new Excel.Application();
            application.Visible = false;
            application.UserControl = false;

            Excel.Workbook workBook = (Excel.Workbook)(application.Workbooks.Add(missing));

            try
            {
                #region " 根据需要导出的 DataTable 数量，预先增加不足的工作表或多余的工作表 "

                // 添除多余的工作表
                while (application.ActiveWorkbook.Sheets.Count > fOptions.Count)
                    ((Excel.Worksheet)application.ActiveWorkbook.Sheets[1]).Delete();
                // 添加工作表
                while (application.ActiveWorkbook.Sheets.Count < fOptions.Count)
                    application.Worksheets.Add(missing, missing, missing, missing);

                #endregion

                int sheetIndex = 1;
                List<String> sheetNames = new List<string>();
                foreach (DataTableExportOptions option in fOptions)
                {
                    #region " 处理在多个 DataTable 设置为相同的工作表名称的问题 "

                    if (sheetNames.Contains(option.WorkSheetName))
                    {
                        int i = 1;
                        while (true)
                        {
                            string newSheetName = option.WorkSheetName + i.ToString();
                            if (!sheetNames.Contains(newSheetName))
                            {
                                sheetNames.Add(newSheetName);
                                option.WorkSheetName = newSheetName;
                                break;
                            }
                            i++;
                        }
                    }
                    else
                    {
                        sheetNames.Add(option.WorkSheetName);
                    }

                    #endregion

                    ExportToExcel(application, workBook, (Excel.Worksheet)application.ActiveWorkbook.Sheets[sheetIndex], option);
                    sheetIndex++;
                }

                workBook.SaveAs(fFileName, missing, missing, missing, missing, missing
                    , Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
            }
            finally
            {
                application.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
                GC.Collect();
               
            }
        }

        /// <summary>
        /// 将 DataTable 导出到 Excel
        /// </summary>
        /// <param name="fApplication"></param>
        /// <param name="fWorkBook"></param>
        /// <param name="fOption"></param>
        private static void ExportToExcel(Excel.Application fApplication, Excel._Workbook fWorkBook, Excel.Worksheet worksheet, DataTableExportOptions fOption)
        {
            Excel.Range range;

            worksheet.Name = fOption.WorkSheetName;

            if (fOption.DataTable == null) return;

            int rowCount = fOption.DataTable.Rows.Count;
            int colCount = fOption.VisibleColumnOptions.Count;
            int colIndex = 0;
            int rowIndex = 0;

            #region " Set Header Values "

            object[,] colValues = new object[1, colCount];

            foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions)
            {
                if (!option.Visible) continue;
                colValues[0, colIndex] = option.Caption;
                colIndex++;
            }

            range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, 1));
            range.Value2 = colValues;

            #endregion

            #region " Header Style "

            range.Font.Bold = true;
            range.Font.Name = "Georgia";
            range.Font.Size = 10;
            range.RowHeight = 26;
            range.EntireColumn.AutoFit();

            #endregion

            #region " Set Row Values "

            object[,] rowValues = new object[rowCount, colCount];

            rowIndex = 0;

            foreach (DataRow dataRow in fOption.DataTable.Rows)
            {
                colIndex = 0;

                foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions)
                {
                    rowValues[rowIndex, colIndex] = dataRow[option.ColumnName];
                    colIndex++;
                }

                rowIndex++;
            }

            range = worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1));
            range.Value2 = rowValues;

            #region " Row Style "

            range.Font.Name = "Georgia";
            range.Font.Size = 9;
            range.RowHeight = 18;
            range.EntireColumn.AutoFit();
            //range.Borders.ColorIndex = 2;

            #endregion

            #endregion

            #region " Set Borders "

            range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, rowCount + 1));
            range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            range.Borders.Weight = Excel.XlBorderWeight.xlThin;
            range.Borders.Color = Color.Black.ToArgb();

            #endregion
        }

        #endregion

        #region " GetCellName "

        private static string GetExcelCellName(int fColIndex, int fRowIndex)
        {
            if (fColIndex <= 0 || fColIndex > 256)
            {
                throw new Exception("Excel 列索引数值超出范围(1-256)!");
            }
            else if (fColIndex <= 26)
            {
                return GetExcelCellName(fColIndex) + fRowIndex.ToString();
            }
            else
            {
                string retLetter = GetExcelCellName(fColIndex / 26);
                retLetter += GetExcelCellName(fColIndex % 26);
                retLetter += fRowIndex.ToString();
                return retLetter;
            }
        }

        private static string GetExcelCellName(int fColIndex)
        {
            int i = 1;

            foreach (string letter in Enum.GetNames(typeof(ExcelColumnLetters)))
            {
                if (i == fColIndex)
                    return letter;
                i++;
            }

            throw new Exception("Excel 列索引数值超出范围(1-256)!");
        }

        #endregion
    }

    #region " ExcelColumnLetters "

    public enum ExcelColumnLetters
    {
        A = 1, B = 2, C = 3, D = 4, E = 5, F = 6, G = 7, H = 8, I = 9, J = 10,
        K = 11, L = 12, M = 13, N = 14, O = 15, P = 16, Q = 17, R = 18, S = 19, T = 20,
        U = 21, V = 22, W = 23, X = 24, Y = 25, Z = 26
    }

    #endregion

    #region " DataColumnExportOptions "

    public class DataColumnExportOptions
    {
        private String fColumnName;
        private String fCaption;
        private Boolean fVisible;
        private DateTime fDatas;

        public String ColumnName
        {
            get { return fColumnName; }
            set { fColumnName = value; }
        }

        public String Caption
        {
            get { return fCaption; }
            set { fCaption = value; }
        }

        public Boolean Visible
        {
            get { return fVisible; }
            set { fVisible = value; }
        }

        public DateTime Datas
        {
            get { return fDatas; }
            set { fDatas = value; }
        }
        public DataColumnExportOptions(String fColumnName)
            : this(fColumnName, fColumnName)
        {

        }

        public DataColumnExportOptions(String fColumnName, String fCaption)
            : this(fColumnName, fCaption, true)
        {

        }

        public DataColumnExportOptions(String fColumnName, String fCaption, Boolean fVisible)
        {
            this.fColumnName = fColumnName;
            this.fCaption = fCaption;
            this.fVisible = fVisible;
        }

        public DataColumnExportOptions(String fColumnName, String fCaption, Boolean fVisible, DateTime fDatas)
        {
            this.fColumnName = fColumnName;
            this.fCaption = fCaption;
            this.fVisible = fVisible;
            this.fDatas = fDatas;
        }
    }

    #endregion

    #region " DataTableExportOptions "

    public class DataTableExportOptions
    {
        private DataTable fDataTable;
        private List<DataColumnExportOptions> fColumnOptions;
        private List<DataColumnExportOptions> fVisibleColumnOptions;
        private String fWorkSheetName;


        public DataTable DataTable
        {
            get { return fDataTable; }
            set { fDataTable = value; }
        }

        public List<DataColumnExportOptions> ColumnOptions
        {
            get { return fColumnOptions; }
            set { fColumnOptions = value; }
        }

        public String WorkSheetName
        {
            get { return fWorkSheetName; }
            set { fWorkSheetName = value; }
        }

        public List<DataColumnExportOptions> VisibleColumnOptions
        {
            get { return fVisibleColumnOptions; }
        }

        public DataTableExportOptions(DataTable fDataTable)
            : this(fDataTable, null)
        {

        }

        public DataTableExportOptions(DataTable fDataTable, List<DataColumnExportOptions> fColumnOptions)
            : this(fDataTable, fColumnOptions, null)
        {

        }

        public DataTableExportOptions(DataTable fDataTable, List<DataColumnExportOptions> fColumnOptions, String fWorkSheetName)
        {
            if (fDataTable == null) return;

            this.fDataTable = fDataTable;
            if (fColumnOptions == null)
            {
                this.fColumnOptions = new List<DataColumnExportOptions>();
                foreach (DataColumn dataColumn in fDataTable.Columns)
                    this.fColumnOptions.Add(new DataColumnExportOptions(dataColumn.ColumnName));
            }
            else
            {
                this.fColumnOptions = fColumnOptions;
            }

            if (String.IsNullOrEmpty(fWorkSheetName))
                this.fWorkSheetName = fDataTable.TableName;
            else
                this.fWorkSheetName = fWorkSheetName;

            fVisibleColumnOptions = new List<DataColumnExportOptions>();
            foreach (DataColumnExportOptions option in this.fColumnOptions)
            {
                if (option.Visible)
                    fVisibleColumnOptions.Add(option);
            }
        }
    }

    #endregion
}

调用方法



 

厦门德仔
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
.net里导出excel表方法汇总
05-18
.net里导出excel表方法汇总
将 DataSet DataTable 导出 Excel
11-05 3450
源代码及示例下载地址：http://download.csdn.net/source/1794131 /// /// 标题：将 DataSet, DataTable 导出到 Excel/// 作者：肖小勇/// 日期：2009-10-23/// 描述：对之前做的导出 Excel 做调整以支持对 DataSet 及 DataTable 的导出；///
ExcelExport 代码导出（大数据量导出）
热门推荐
风的专栏
04-03 1万+
public class ExcelExport { public static void main(String[] args) throws Exception { String sheetName = "测试Excel格式"; String sheetTitle = "测试Excel格式"; List&lt;String&gt; co...
DevExpress.XtraExport.ExportXlsProvider实现Excel带入数据
weixin_30765577的博客
07-27 477
View Code 1 private void btnImportExcel_Click(object sender, EventArgs e) 2 { 3 if (this.gridView1.RowCount <= 0) 4 { 5 MessageBox.S...
JAVA导出Excel文件
qq_38254635的博客
10-19 1万+
JAVA生成Excel文件并导出 一：集成POI 1、Apache POI 2、easyexcel 二：Excel相关操作 1、基础Excel导出 2、压缩Zip导出 3、错误Excel导出 4、根据Html导出 5、根据模板导出 6、根据模板及列表导出 7、根据模板及列表扩充导出 8、根据模板导出，拆分多个sheet页 9、导入 三：Excel代码 1、工具类 2、测试Bean 3、测试Service 4、测试ServiceImpl 5、测试Controller
aspnet html导出excel,asp.net里导出excel表方法汇总
weixin_39852121的博客
06-20 372
1、由dataset生成public void CreateExcel(DataSet ds,string typeid,string FileName){HttpResponse resp;resp = Page.Response;resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");resp.AppendHeader...
asp.net导出excel数据的常见方法汇总
10-25
主要介绍了asp.net导出excel数据的常见方法,实例汇总了数据导出的常见的方法以及相关的注意事项,是非常实用的技巧,需要的朋友可以参考下
easyPoi模板导出Excel报表(xls 和xlsx 都支持)
02-07
easyPoi模板导出Excel报表(xls 和xlsx 都支持)
asp.net里导出excel表方法汇总
06-25
asp.net里导出excel表方法汇总asp.net里导出excel表方法汇总asp.net里导出excel表方法汇总asp.net里导出excel表方法汇总asp.net里导出excel表方法汇总
[Asp.Net]获取当前的URL和IP地址
德仔
06-04 8077
介绍一下ASP.NET取得当前页面的完整URL 的方放，icech做成了函数，直接用吧！ private string GetPath() { string strPath = "http://" + Request.ServerVariables["HTTP_HOST"] + Request.ServerVariables["PATH_INFO"] + "?" +
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This ma
德仔
12-15 6898
连接池暴蹦.... 在运行程序的时候: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size wa...
[asp.net]登录协同工作平台安全解决方案
德仔
05-09 6205
[摘要]公司领导说登录验证的安全性如何保证，建议采用UKEY验证类似网银解决。解决方案：前端页面： //加载皮肤 var setTheme = function () { Ext.net.DirectMethods.GetThemeUrl(cbTheme.getValue(), { succ
ListBox基本功能(ListBox1---ListBox换)
德仔
12-24 5413
虽然在之前有做过类似功能:但有些问题,这次就重写吧。 ListBox基本功能首先是列表项的添加,客户端实现代码添加在listbox实例化代码中间,例如： Text 若在服务器端实现,为避免每次加载时执行添加列表项,上述代码包含在下面代码中: if(!IsPostBack) { } WebForm页面上须添加2个listbox（listbox1和lixtbox2）和2个命令按钮,lis
[Asp.Net]QRCode生成二维码(续)-在二维码图片中心加Logo或图像
德仔
12-05 4848
<%@ WebHandler Language="C#" Class="GetQRCode" %> using System; using System.Web; using ThoughtWorks.QRCode.Codec; using ThoughtWorks.QRCode.Codec.Data; using ThoughtWorks.QRCode.Codec.Util; ...
[C#]在.NET调用加了SSL验证的WebService-根据验证过程远程证书无效
德仔
11-21 4658
在.NET调用加了SSL验证的WebService System.Net.WebException: 基础连接已经关闭: 未能为 SSL/TLS 安全通道建立信任关系。 —> System.Security.Authentication.AuthenticationException: 根据验证过程，远程证书无效。 你可以改进代码以询问用户证书无效。 不能恢复某些项目. 如下所示：Service
[Asp.Net]如何安装与部署asp.net网站
德仔
07-25 4630
如何给Asp.Net 网站项目打包 1、打开你的项目，在中用鼠标右击你的，选择-。 2、对话框中选择-。（注意：的存放路径。） 3、vs.net 的窗口左侧会显示。用鼠标右键选中，选择-，在弹出的中同时选中和。（注意：可根据自己需要选择。） 4、你的解决方案中会新增一个你的 Web 安装项目名称的工程，就是安装的项目。保存一下，有时会出错。 5、如果你用了crystal report，就
[Asp.Net]判断输入文字是否是数字
德仔
04-28 3901
asp.net判断输入文字是否是数字 方案一: /**//// /// 名称：IsNumberic /// 功能：判断输入的是否是数字 /// 参数：string oText：源文本 /// 返回值：　bool true:是　false:否 /// public bool IsNumberic(string oText) { try { int v
[Asp.Net]aspx页面转html
德仔
02-13 3893
在上次的基础上，我想到一个问题,就把已经完成的页面读取出来转换成html。 我在网上找了一下资料: 方案1：   ///       ///   传入URL返回网页的html代码     ///       ///   URL     ///       public   static    string   getUrltoHtml(str
原生table导出excel表格
最新发布
07-28
- *2* [前端table导出excel表格方法汇总](https://blog.csdn.net/yufengaotian/article/details/121439968)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

厦门德仔

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值