1.需要下载一个用于导出的动态库:
Aspose.Cells.dll
动态库+代码下载链接:http://download.csdn.net/detail/shaoyiju/9397997
2.动态库使用方法
打开工程项目-->解决方案资源管理器-->引用-->右键“添加引用”-->Tab页“最近”,选择动态库确定后就可以使用啦
3.上码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;
using Aspose.Cells;
namespace ATMClient.GSPublic
{
/// <summary>
/// 这个类是将DataSet中的数据导出到Excel表,不需调用本地的EXCEL.EXE进程
/// </summary>
class Export2Eexel
{
private void Test()
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.TableName = "LogTable";
dt.Columns.Add("序号", typeof(string));
dt.Columns.Add("用户", typeof(string));
dt.Columns.Add("日期", typeof(string));
dt.Columns.Add("操作", typeof(string));
DataRow dr = dt.NewRow();
dr[0] = "1";
dr[1] = "admin";
dr[2] = "2015-11-22 16:30:00";
dr[3] = "登录";
dt.Rows.Add(dr);
ds.Tables.Add(dt);
ExportIntoExcel(ds);
return;
}
private void ExportIntoExcel(DataSet ds)
{
//打开保存对话框
Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
dlg.FileName = "ATM操作日志_" + DateTime.Now.ToString("yyyy-MM-dd_HHmmss");
dlg.Filter = "EXCEL文件(*.xls, *.xlsx)|*.xls;*.xlsx|所有文件(*.*)|*.*";
Nullable<bool> Res = dlg.ShowDialog();
if (Res.HasValue)
{
if (Res.Value == true)
{
string fileName = dlg.FileName;
try
{
List<int> columWidthList = new List<int>() { 10, 15, 30, 30 };
if (DataSetToExcel(fileName, "测试标题", ds, columWidthList))
{
System.Windows.MessageBox.Show("导出成功");
}
else
{
System.Windows.MessageBox.Show("导出失败");
}
}
catch (Exception ex)
{
string err = ex.Message;
return;
}
}
}
return;
}
public bool DataSetToExcel(string strFilePath,string reportTitleName, DataSet dataSet, List<int> columWidthList)
{
Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];
Cells cell = ws.Cells;
DataTable dataTable = dataSet.Tables[0];
int rowCount = dataTable.Rows.Count; //不包括字段名
int columnCount = dataTable.Columns.Count;
if (rowCount == 0)
{
return false;
}
//合并第一行单元格作为标题
Range range = cell.CreateRange(0, 0, 1, columnCount);
range.Merge();
cell["A1"].PutValue(reportTitleName);
//设置行高
cell.SetRowHeight(0, 20);
// 设置标题字体样式
Style style1 = wb.Styles[wb.Styles.Add()];
style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style1.Font.Name = "宋体";
style1.Font.IsBold = true; //设置粗体
style1.Font.Size = 12; //设置字体大小
// 设置内容字体样式
Style style2 = wb.Styles[wb.Styles.Add()];
style2.HorizontalAlignment = TextAlignmentType.Center;
style2.Font.Size = 10;
//给单元格关联样式
cell["A1"].SetStyle(style1); //报表名字 样式
//设置Execl列名
for (int i = 0; i < columnCount; i++)
{
cell[1, i].PutValue(dataTable.Columns[i].ColumnName);
cell[1, i].SetStyle(style2);
}
//设置单元格内容
int posStart = 2;
for (int r = 0; r < rowCount; r++)
{
for (int c = 0; c < columnCount; c++)
{
cell[r + posStart, c].PutValue(dataTable.Rows[r][c]);
cell[r + posStart, c].SetStyle(style2);
}
}
//设置列宽
for (int i = 0; i < columnCount; i++)
{
cell.SetColumnWidth(i, Convert.ToDouble(columWidthList[i].ToString()));
}
//保存excel表格
wb.Save(strFilePath);
return true;
}
}
}