C# 多个DataTable导入到Excel多张Sheet表

最新推荐文章于 2020-05-22 15:53:42 发布

原创

最新推荐文章于 2020-05-22 15:53:42 发布 · 9k 阅读

10 ·

CC 4.0 BY-SA版权

本文介绍了如何使用C#将多个DataTable导出到Excel的不同工作表中，需要引用Office组件的DLL，并确保Office已正确安装。通过创建TableToExcel类，传入包含多个DataTable的列表，每个DataTable的Name将作为Sheet表名称，如果Name为空则默认为Table1等。示例代码展示了如何组织数据并调用方法进行导出。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

这个要引用Office组件的几个DLL文件,条件是先安装Office,而且Office要正确安装才可以

正确安装Office组件:http://blog.youkuaiyun.com/wxm3630478/article/details/5888375

测试Office版本: Office2007

添加引用 -- Com组件(Tab页) -- Microsoft Excel 12.0 ObjectLibrary -- 因为要向Excel插入图片,所以用Com组件

如果引用 -- .Net组件(Tab页) -- Microsoft.Office.Interop.Excel 版本14.0.0.0 ,则插入图片的方法处会报错,删除即可(如果不需要插入图片)

代码可以参考下,这个是我以前写的一个,是按照公司导出到Excel的格式写的,可以抽取一部分用

你先把数据分好类,放到不同的DataTable中,然后
TableToExcel类有构造函数需要参数 List<System.Data.DataTable> 集合
这个DataTable集合就会到出到不同的Sheet表,Sheet表的名字就是DataTable的Name属性,
如果Name为空,则是Table1~n

//调用:
List<System.Data.DataTable> dts = new List<System.Data.DataTable>();
DataTable table1 = new DataTable();
table1.Name = "鞋类"
dts.Add(table1);
dts.Add(table2);
dts.Add(table3);
//..........
TableToExcel ex = new TableToExcel("C:\\test.xls",dts)
ex.ExportExcel()

/*----------------------------------------------------------------*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Drawing;
using System.Windows.Forms;

namespace WMS.UI
{
    /// <summary>
    /// WXM 2012-04-13创建
    /// </summary>
    public class TableToExcel
    {
        #region 变量
        Microsoft.Office.Interop.Excel.Application xlApp = null;
        Microsoft.Office.Interop.Excel.Workbooks wbs = null;
        Microsoft.Office.Interop.Excel.Workbook wb = null;

        private int _rowindex = 0;   //全局行索引(使用时加n行)
        private int _saveindex = 0;  //保存全局行索引(数据导出后还原全局行索引)
        //文本
        private string  _title = String.Empty;   //标题
        private string  _headerdtext = String.Empty;   //页眉,即标题的下一行
        private string _footertext = String.Empty;   //页脚,即最后一行

        //正文(列表)是否显示边框
        private bool _isalldisplayborder = true;
        //正文(列表)边框类型
        private BorderWeightType _borderweight = BorderWeightType.xlThin;

        //保存路径
        private string _savepath = String.Empty;
        //字体
        private System.Drawing.Font _titlefont = new System.Drawing.Font("宋体", 15);
        private System.Drawing.Font _headerfont = new System.Drawing.Font("宋体", 11);
        private System.Drawing.Font _footerfont = new System.Drawing.Font("宋体", 11);
        private System.Drawing.Font _bodyheaderfont = new System.Drawing.Font("宋体", 11);
        private System.Drawing.Font _bodyfont = new System.Drawing.Font("宋体", 11);

        //脚文本Align
        private TextAlign _drawfootertextalign = TextAlign.xlHAlignRight;

        //要导出的表集合
        private List<System.Data.DataTable> _tables = new List<System.Data.DataTable>();

        //设置列宽(_isbodydisplayborder为false)
        private Dictionary<string, float> _columnswidth = new Dictionary<string, float>();

        //设置列的边框,
        private Dictionary<string, BorderWeightType> _columnsborder = new Dictionary<string, BorderWeightType>();

        //保存Table导入到那个Sheet表(打印时可以判断sheet是否有数据,没有数据则不打印)
        private Dictionary<string, System.Data.DataTable> SheetTable = new Dictionary<string, System.Data.DataTable>();

        private bool _iswraptext = true;  //单元格是否自动换行
        private bool _isbodylistheader = true;  //是否显示正文列表标题
        private bool _isautoconverttext = true;  //是否自动转换成文本格式
        private bool _isTitleAppendSheetName = false;  //标题后面是否追加SheetName

        //条码文本
        private string _BarCodeText = String.Empty;

        //设置打印时页面边距(程序中和Excel中的边距单元不一样(Excel设置0.5大概有5-10px,所以默认5px))
        private PaddingF _pageMargin = new PaddingF(5);
        private bool _isPrintFooter = true;   //是否打印页脚(只跟打印有关)
        private string _expandColumnName = String.Empty;
        #endregion

        #region 构造方法
        public TableToExcel(System.Data.DataTable table)
        {
            _tables = new List<System.Data.DataTable>() { table };
        }

        public TableToExcel(List<System.Data.DataTable> tables)
        {
            _tables = tables;
        }

        public TableToExcel(string savepath, System.Data.DataTable table)
        {
            _savepath = savepath;
            _tables = new List<System.Data.DataTable>() { table };
        }

        public TableToExcel(string savepath,List<System.Data.DataTable> tables)
        {
            _savepath = savepath;
            _tables = tables;
        }

        public TableToExcel(string title, string savepath, System.Data.DataTable table)
        {
            _savepath = savepath;
            Title = title;
            _tables = new List<System.Data.DataTable>() { table };
        }

        public TableToExcel(string title, string savepath, List<System.Data.DataTable> tables)
        {
            _savepath = savepath;
            Title = title;
            _tables = tables;
        }
        #endregion

        #region 属性
        /// <summary>
        /// 行索引(表示从某行开始打印,如0表示从第一行开始)
        /// </summary>
        public virtual int RowIndex
        {
            get { return _rowindex; }
            set
            {
                _rowindex = _saveindex = value;
            }
        }

        /// <summary>
        /// 标题
        /// </summary>
        public virtual string Title
        {
            get { return _title; }
            set 
            { 
                _title = value;
                IsDrawTitle = !string.IsNullOrEmpty(value);
            }
        }

        /// <summary>
        /// 头文本
        /// </summary>
        public virtual string HeaderText
        {
            get { return _headerdtext; }
            set 
            {
                _headerdtext = value;
                IsDrawHeader = !string.IsNullOrEmpty(value);
            }
        }

        /// <summary>
        /// 脚文本
        /// </summary>
        public virtual string FooterText
        {
            get { return _footertext; }
            set 
            {
                _footertext = value;
                IsDrawFooter = !string.IsNullOrEmpty(value);
            }
        }

        /// <summary>
        /// 保存地址
        /// </summary>
        public virtual string SavePath
        {
            get { return _savepath; }
            set 
            { 
                _savepath = value;
            }
        }

        /// <summary>
        /// 标题字体
        /// </summary>
        public virtual System.Drawing.Font TitleFont
        {
            get { return _titlefont; }
            set { _titlefont = value; }
        }

        /// <summary>
        /// Header字体
        /// </summary>
        public virtual System.Drawing.Font HeaderFont
        {
            get { return _headerfont; }
            set { _headerfont = value; }
        }

        /// <summary>
        /// 页脚字体
        /// </summary>
        public virtual System.Drawing.Font FooterFont
        {
            get { return _footerfont; }
            set { _footerfont = value; }
        }

        /// <summary>
        /// 正文标题字体
        /// </summary>
        public virtual System.Drawing.Font BodyHeaderFont
        {
            get { return _bodyheaderfont; }
            set { _bodyheaderfont = value; }
        }

        /// <summary>
        /// 正文字体
        /// </summary>
        public virtual System.Drawing.Font BodyFont
        {
            get { return _bodyfont; }
            set { _bodyfont = value; }
        }

        /// <summary>
        /// 导出表集合
        /// </summary>
        public virtual List<System.Data.DataTable> Tables
        {
            get { return _tables; }
            set { _tables = value; }
        }

        /// <summary>
        /// 列宽集合(A:A 表示第一列,A:B表示第一二列)
        /// </summary>
        public virtual Dictionary<string, float> ColumnsWidth
        {
            get { return _columnswidth; }
            set { _columnswidth = value; }
        }

        /// <summary>
        /// 那些列显示边框
        /// </summary>
        public virtual Dictionary<string, BorderWeightType> ColumnsBorder
        {
            get { return _columnsborder; }
            set { _columnsborder = value; }
        }

        public virtual bool IsDrawTitle { get; set; }

        public virtual bool IsDrawHeader { get; set; }

        public virtual bool IsDrawFooter { get; set; }

        public virtual TextAlign DrawFooterTextAlign
        {
            get { return _drawfootertextalign; }
            set {