第一步：添加对 org.in2bits.MyXls.dll程序集的引用。
#region 类型
public string headlines;
public string headlinesfont;
public string smalltitle;
public int titlefontsize;
public string titlefont;
public string title_l;
public string title_m;
public string title_r;
public int st_l, st_m, st_r;
public string tailline;
public string tailfont;
public string tail_l;
public string tail_m;
public string tail_r;
public int tl_l, tl_m, tl_r;
public int xm_cnt;
public string datafont;
public string cellnull;
public string sheetname;
public string title;
public string tail;
#endregion
/// <summary>
/// 导出
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="ClmName">标题，表头 列</param>
/// <param name="ClmType">数据类型</param>
/// <param name="FieldName">字段名</param>
/// <param name="Clmwidth">每一格宽度</param>
/// <param name="FileName">文件名</param>
/// <param name="FilePath">文件路径</param>
public void ExcelExport(DataTable dt, string[] ClmName, string[] ClmType, string[] FieldName, int[] Clmwidth, string FileName, string FilePath)
{
XlsDocument xls = new XlsDocument();
//指定文件名
xls.FileName = FileName + ".xls";
//添加工作表
Worksheet sheet;
if (sheetname == null || sheetname == "") sheet = xls.Workbook.Worksheets.Add("Sheet1");
else sheet = xls.Workbook.Worksheets.Add(sheetname);
#region 设置各数据列的大小
int i, clmct = ClmName.Length;
for (i = 0; i < clmct; i++)
{
ColumnInfo colInfo1 = new ColumnInfo(xls, sheet);
colInfo1.ColumnIndexStart = (ushort)i;
colInfo1.ColumnIndexEnd = (ushort)(i + 1);
colInfo1.Width = (ushort)(Clmwidth[i] * 256);
sheet.AddColumnInfo(colInfo1);
}
#endregion
Cells cells = sheet.Cells;
int Ln = 1;
#region 添加大标题
if (String.IsNullOrEmpty(headlines) == false)
{
//合并单元格
MergeArea maTitle = new MergeArea(Ln, Ln, 1, clmct);//1行，clmct列
sheet.AddMergeArea(maTitle);
//单元格属性
XF xfTitle = xls.NewXF();
//对齐方式
xfTitle.HorizontalAlignment = HorizontalAlignments.Centered;
xfTitle.VerticalAlignment = VerticalAlignments.Centered;
//字体
if (headlinesfont == null || headlinesfont == "") xfTitle.Font.FontName = "宋体";
else xfTitle.Font.FontName = headlinesfont;
xfTitle.Font.Height = 16 * 20;
xfTitle.Font.Bold = true;
cells.Add(Ln, 1, headlines, xfTitle);
Ln++;
}
#endregion
#region 添加小标题
if (String.IsNullOrEmpty(smalltitle) == false)
{
//合并单元格
MergeArea maTime1 = new MergeArea(Ln, Ln, 1, clmct);//1行
sheet.AddMergeArea(maTime1);
//单元格属性
XF xfTopBar = xls.NewXF();
xfTopBar.HorizontalAlignment = HorizontalAlignments.Centered;
xfTopBar.VerticalAlignment = VerticalAlignments.Centered;
if (String.IsNullOrEmpty(titlefont)) xfTopBar.Font.FontName = "宋体";
else xfTopBar.Font.FontName = titlefont;
if (String.IsNullOrEmpty(titlefontsize.ToString())) xfTopBar.Font.Height = 14 * 20;
else xfTopBar.Font.Height = Convert.ToUInt16(titlefontsize);
cells.Add(Ln, 1, smalltitle, xfTopBar);
Ln++;
}
#endregion
#region 添加参数标题： 左，中，右
if (String.IsNullOrEmpty(title_l) == false || String.IsNullOrEmpty(title_m) == false || String.IsNullOrEmpty(title_r) == false)
{
int w1 = 1;
if (String.IsNullOrEmpty(title_l) == false)
{
//合并单元格
int w2 = st_l;
MergeArea maTime21 = new MergeArea(Ln, Ln, w1, w2);//1行
sheet.AddMergeArea(maTime21);
//单元格属性
XF xfTopBar = xls.NewXF();
if (String.IsNullOrEmpty(titlefont)) xfTopBar.Font.FontName = "宋体";
else xfTopBar.Font.FontName = titlefont;
if (String.IsNullOrEmpty(titlefontsize.ToString())) xfTopBar.Font.Height = 14 * 20;
else xfTopBar.Font.Height = Convert.ToUInt16(titlefontsize);
cells.Add(Ln, 1, title_l, xfTopBar);
w1 = w2 + 1;
}
if (String.IsNullOrEmpty(title_m) == false)
{
//合并单元格
int w2 = w1 + st_m - 1;
MergeArea maTime22 = new MergeArea(Ln, Ln, w1, w2);//1行
sheet.AddMergeArea(maTime22);
//单元格属性
XF xfTopBar = xls.NewXF();
if (String.IsNullOrEmpty(titlefont)) xfTopBar.Font.FontName = "宋体";
else xfTopBar.Font.FontName = titlefont;
if (String.IsNullOrEmpty(titlefontsize.ToString())) xfTopBar.Font.Height = 14 * 20;
else xfTopBar.Font.Height = Convert.ToUInt16(titlefontsize);
cells.Add(Ln, w1, title_m, xfTopBar);
w1 = w2 + 1;
}
if (String.IsNullOrEmpty(title_r) == false)
{
//合并单元格
int w2 = w1 + st_r - 1;
MergeArea maTime23 = new MergeArea(Ln, Ln, w1, w2);//1行
sheet.AddMergeArea(maTime23);
//单元格属性
XF xfTopBar = xls.NewXF();
xfTopBar.HorizontalAlignment = HorizontalAlignments.Right;
if (String.IsNullOrEmpty(titlefont)) xfTopBar.Font.FontName = "宋体";
else xfTopBar.Font.FontName = titlefont;
if (titlefontsize == 0) xfTopBar.Font.Height = 14 * 20;
else xfTopBar.Font.Height = Convert.ToUInt16(titlefontsize);
cells.Add(Ln, w1, title_r, xfTopBar);
}
Ln++;
}
#endregion
#region 设置Excel数据列标题的格式
XF xfDataHead = xls.NewXF();
xfDataHead.HorizontalAlignment = HorizontalAlignments.Centered;
xfDataHead.VerticalAlignment = VerticalAlignments.Centered;
xfDataHead.Font.FontName = "宋体";
xfDataHead.Font.Bold = true;
xfDataHead.UseBorder = true;
xfDataHead.TextWrapRight = true;
xfDataHead.BottomLineStyle = 1;
xfDataHead.BottomLineColor = Colors.Black;
xfDataHead.TopLineStyle = 1;
xfDataHead.TopLineColor = Colors.Black;
xfDataHead.LeftLineStyle = 1;
xfDataHead.LeftLineColor = Colors.Black;
xfDataHead.RightLineStyle = 1;
xfDataHead.RightLineColor = Colors.Black;
#endregion
#region 添加列标题
for (i = 0; i < clmct; i++) cells.Add(Ln, i + 1, ClmName[i], xfDataHead);
Ln++;
#endregion
#region 设置各数据列的格式
XF xfData = xls.NewXF();
xfData.HorizontalAlignment = HorizontalAlignments.Centered;
xfData.Font.FontName = "宋体";
xfData.Font.Height = 8 * 20;
xfData.UseBorder = true;
xfData.BottomLineStyle = 1;
xfData.BottomLineColor = Colors.Black;
xfData.TopLineStyle = 1;
xfData.TopLineColor = Colors.Black;
xfData.LeftLineStyle = 1;
xfData.LeftLineColor = Colors.Black;
xfData.RightLineStyle = 1;
xfData.RightLineColor = Colors.Black;
xfData.TextWrapRight = true;
XF xfData2 = xls.NewXF();
xfData2.Font.FontName = "宋体";
xfData2.UseBorder = true;
xfData2.BottomLineStyle = 1;
xfData2.BottomLineColor = Colors.Black;
xfData2.TopLineStyle = 1;
xfData2.TopLineColor = Colors.Black;
xfData2.LeftLineStyle = 1;
xfData2.LeftLineColor = Colors.Black;
xfData2.RightLineStyle = 1;
xfData2.RightLineColor = Colors.Black;
xfData2.TextWrapRight = true;
#endregion
#region 填充数据
string dtnull = "";
if (String.IsNullOrEmpty(cellnull) == false) dtnull = cellnull;
foreach (DataRow dr in dt.Rows)
{
for (i = 0; i < clmct; i++)
{
if (ClmType[i].ToString() == "Double")
{
if (dr[FieldName[i]] == DBNull.Value) cells.Add(Ln, (ushort)(i + 1), dtnull, xfData2);
else cells.Add(Ln, i + 1, Convert.ToDouble(dr[FieldName[i]]), xfData2);
}
else
{
if (dr[FieldName[i]] == DBNull.Value) cells.Add(Ln, (ushort)(i + 1), dtnull, xfData);
else cells.Add(Ln, i + 1, dr[FieldName[i]].ToString(), xfData);
}
}
Ln++;
}
#endregion
#region 添加结尾行： 可细化左，中，右
if (String.IsNullOrEmpty(tail_l) == false || String.IsNullOrEmpty(tail_m) == false || String.IsNullOrEmpty(tail_r) == false)
{
int w1 = 1;
if (String.IsNullOrEmpty(tail_l) == false)
{
//合并单元格
int w2 = tl_l;
MergeArea maTime21 = new MergeArea(Ln, Ln, w1, w2);//1行
sheet.AddMergeArea(maTime21);
//单元格属性
XF xfTopBar = xls.NewXF();
if (String.IsNullOrEmpty(tailfont)) xfTopBar.Font.FontName = "宋体";
else xfTopBar.Font.FontName = tailfont;
cells.Add(Ln, 1, tail_l, xfTopBar);
w1 = w2 + 1;
}
if (String.IsNullOrEmpty(tail_m) == false)
{
//合并单元格
int w2 = w1 + tl_m - 1;
MergeArea maTime22 = new MergeArea(Ln, Ln, w1, w2);//1行
sheet.AddMergeArea(maTime22);
//单元格属性
XF xfTopBar = xls.NewXF();
if (String.IsNullOrEmpty(tailfont)) xfTopBar.Font.FontName = "宋体";
else xfTopBar.Font.FontName = tailfont;
cells.Add(Ln, w1, tail_m, xfTopBar);
w1 = w2 + 1;
}
if (String.IsNullOrEmpty(tail_r) == false)
{
//合并单元格
int w2 = w1 + tl_r - 1;
MergeArea maTime23 = new MergeArea(Ln, Ln, w1, w2);//1行
sheet.AddMergeArea(maTime23);
//单元格属性
XF xfTopBar = xls.NewXF();
xfTopBar.HorizontalAlignment = HorizontalAlignments.Right;
if (String.IsNullOrEmpty(tailfont)) xfTopBar.Font.FontName = "宋体";
else xfTopBar.Font.FontName = tailfont;
cells.Add(Ln, w1, tail_r, xfTopBar);
}
Ln++;
}
#endregion
//发送到客户端
xls.Save(FilePath, true);
xls.Send();
System.IO.File.Delete(FilePath + xls.FileName);
}
}
org.in2bits.MyXls.rar 引用组件 下载
浙公网安备 33010602011771号