这个要引用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 {