C#导出Excel时按类型分Sheet 网上查了很多,都是按条数分Sheet,我是要按类型分Sheet,比如这个类型是一个村名字,要的结果就是这个村名字下面所有的数据在一个Sheet里面,会有多个村名字,不知道有谁做过这样的,求分享/分析。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 这个问题重点在分sheet,你既然可以根据条数来分sheet为什么就不能根据类型分sheet既然可以分sheet,并且内容都是你自己控制的,为什么不能做呢,原理都是一样的 用条数来分,是别人做的,我现在就是不知道要怎么分sheet来存数据 http://blog.csdn.net/happy09li/article/details/7431967 这个要引用Office组件的几个DLL文件,条件是先安装Office,而且Office要正确安装才可以然后用Excel.Application 写里面有工作薄,工作表 WorkBook,WorkSheets 发给代码给你参考下,这个是我以前写的一个,是按照公司导出到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.xml",dts)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> /// 汪湘明 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 { _drawfootertextalign = value; } } /// <summary> /// 是否显示数据列表标题 /// </summary> public virtual bool IsBodyListHeader { get { return _isbodylistheader; } set { _isbodylistheader = value; } } /// <summary> /// 正文(列表)是否显示边框 /// </summary> public virtual bool IsDispalyBorderAll { get { return _isalldisplayborder; } set { _isalldisplayborder = value; } } /// <summary> /// 是否自动转换成文本格式 /// </summary> public virtual bool IsAutoConvertText { get { return _isautoconverttext; } set { _isautoconverttext = value; } } /// <summary> /// 标题后面是否追加SheetName /// </summary> public virtual bool IsTitleAppendSheetName { get { return _isTitleAppendSheetName; } set { _isTitleAppendSheetName = value; } } /// <summary> /// 正文(列表)边框类型 /// </summary> public virtual BorderWeightType BorderWeight { get { return _borderweight; } set { _borderweight = value; } } /// <summary> /// 单元格是否自动适应宽度 /// </summary> public virtual bool IsAutoFit { get; set; } /// <summary> /// 单元格是否自动换行 /// </summary> public virtual bool IsWrapText { get { return _iswraptext; } set { _iswraptext = value; } } /// <summary> /// 条码文本(长度不能超过8位,超过8位请更新图片模版,否则打印出界) /// </summary> public string BarCodeText { get { return _BarCodeText; } set { _BarCodeText = value; if (!String.IsNullOrEmpty(value)) { IsDrawTitle = true; } else { IsDrawTitle = !string.IsNullOrEmpty(_title); } } } /// <summary> /// 设置打印时页面边距(程序中和Excel中的边距单元不一样(Excel设置0.5大概有10px,所以默认10px)) /// </summary> public PaddingF PageMargin { get { return _pageMargin; } set { _pageMargin = value; } } /// <summary> /// 是否打印页脚(只跟打印有关) /// </summary> public bool IsPrintFooter { get { return _isPrintFooter; } set { _isPrintFooter = value; } } /// <summary> /// 要扩大的列(打印时,如果设置了列宽自动适应,有可能总列宽缩小或放大,所以缩小或放大的宽度要算在ExpandColumnName列上) /// 格式 A:A B:B C:C /// </summary> public string ExpandColumnName { get { return _expandColumnName; } set { _expandColumnName = value; } } #endregion public void ExportExcel() { if (string.IsNullOrEmpty(_savepath)) { throw new Exception("保存路径不能为空!"); } try { Microsoft.Office.Interop.Excel.Workbook wb = GetExcelWorkbook(); //保存工作表 wb.SaveCopyAs(_savepath); } catch(Exception ex) { throw ex; } finally { Close(); } } http://blog.csdn.net/wxm3630478/article/details/8234806 C# 能不能自动删除代码 紧急求助,关于非托管DLL调用的参数说明 关于C#调用系统自带的media player的问题 Assembly.CreateInstance的问题 为什么Image中的图像不显示? 返回局部對象的疑惑 如何判断文件的访问权限? xml中怎么实现页面显示问题,100分,求问?? 高手请教,c#的续行符是什么? 请用C#编写一个冒泡排序算法,立即给分! 有关条码打印程序 请教一个TextBox只能输入数字的代码的疑问
既然可以分sheet,并且内容都是你自己控制的,为什么不能做呢,原理都是一样的
用条数来分,是别人做的,我现在就是不知道要怎么分sheet来存数据
里面有工作薄,工作表 WorkBook,WorkSheets 发给代码给你参考下,这个是我以前写的一个,是按照公司导出到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.xml",dts)
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>
/// 汪湘明 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
/// <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 { _drawfootertextalign = value; }
} /// <summary>
/// 是否显示数据列表标题
/// </summary>
public virtual bool IsBodyListHeader
{
get { return _isbodylistheader; }
set { _isbodylistheader = value; }
} /// <summary>
/// 正文(列表)是否显示边框
/// </summary>
public virtual bool IsDispalyBorderAll
{
get { return _isalldisplayborder; }
set { _isalldisplayborder = value; }
} /// <summary>
/// 是否自动转换成文本格式
/// </summary>
public virtual bool IsAutoConvertText
{
get { return _isautoconverttext; }
set
{
_isautoconverttext = value;
}
} /// <summary>
/// 标题后面是否追加SheetName
/// </summary>
public virtual bool IsTitleAppendSheetName
{
get { return _isTitleAppendSheetName; }
set
{
_isTitleAppendSheetName = value;
}
} /// <summary>
/// 正文(列表)边框类型
/// </summary>
public virtual BorderWeightType BorderWeight
{
get { return _borderweight; }
set { _borderweight = value; }
} /// <summary>
/// 单元格是否自动适应宽度
/// </summary>
public virtual bool IsAutoFit { get; set; } /// <summary>
/// 单元格是否自动换行
/// </summary>
public virtual bool IsWrapText
{
get { return _iswraptext; }
set { _iswraptext = value; }
} /// <summary>
/// 条码文本(长度不能超过8位,超过8位请更新图片模版,否则打印出界)
/// </summary>
public string BarCodeText
{
get { return _BarCodeText; }
set
{
_BarCodeText = value;
if (!String.IsNullOrEmpty(value))
{
IsDrawTitle = true;
}
else
{
IsDrawTitle = !string.IsNullOrEmpty(_title);
}
}
} /// <summary>
/// 设置打印时页面边距(程序中和Excel中的边距单元不一样(Excel设置0.5大概有10px,所以默认10px))
/// </summary>
public PaddingF PageMargin
{
get { return _pageMargin; }
set
{
_pageMargin = value;
}
} /// <summary>
/// 是否打印页脚(只跟打印有关)
/// </summary>
public bool IsPrintFooter
{
get { return _isPrintFooter; }
set
{
_isPrintFooter = value;
}
} /// <summary>
/// 要扩大的列(打印时,如果设置了列宽自动适应,有可能总列宽缩小或放大,所以缩小或放大的宽度要算在ExpandColumnName列上)
/// 格式 A:A B:B C:C
/// </summary>
public string ExpandColumnName
{
get { return _expandColumnName; }
set { _expandColumnName = value; }
}
#endregion public void ExportExcel()
{
if (string.IsNullOrEmpty(_savepath))
{
throw new Exception("保存路径不能为空!");
} try
{
Microsoft.Office.Interop.Excel.Workbook wb = GetExcelWorkbook();
//保存工作表
wb.SaveCopyAs(_savepath);
}
catch(Exception ex)
{
throw ex;
}
finally
{
Close();
}
}