标题: .NET实现DataSet转Excel
作者: dnawo
日期: 2009-08-05 05:39 PM
分类: Web编程
内容: 这边我们借助一个组件MyXls来生成Excel，代码比较简单，下边的类实现将DataSet或DataTable转成Excel：
复制内容到剪贴板 程序代码using System;
using System.Data;
using System.Configuration;
using System.IO;
using org.in2bits.MyXls; //MyXls命名空间
/// <summary>
///DataToXSL 的摘要说明
/// </summary>
public class DataToXSL
{
#region DataTable 转 Excel
/// <summary>
/// 生成XSL
/// </summary>
/// <param name="table">DataTable对象</param>
/// <param name="path">保存路径(包含文件名)</param>
/// <returns></returns>
public static bool CreateXLS(DataTable table, string path)
{
return CreateXLS(table, path, true);
}
/// <summary>
/// 生成XSL
/// </summary>
/// <param name="table">DataTable对象</param>
/// <param name="path">保存路径(包含文件名)</param>
/// <param name="overwrite">是否覆盖</param>
/// <returns></returns>
public static bool CreateXLS(DataTable table, string path, bool overwrite)
{
if (File.Exists(path) && !overwrite)
return false;
try
{
//1.创建xls对象
XlsDocument xlsDoc = new XlsDocument();
xlsDoc.FileName = Path.GetFileName(path);
//2.创建表
string sheetName = string.IsNullOrEmpty(table.TableName) ? "Sheet1" : table.TableName;
Worksheet sheet = xlsDoc.Workbook.Worksheets.Add(sheetName);
//3.创建行列,注意cellRow,cellColumn都必须>=1
Cells cells = sheet.Cells;
//3.1 添加字段名
for (int col = 0; col < table.Columns.Count; col++)
{
Cell cell = cells.Add(1, col + 1, table.Columns[col].ColumnName);
cell.Font.Weight = FontWeight.Bold;
}
//3.2 添加记录
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
cells.Add(row + 2, col + 1, string.IsNullOrEmpty(table.Rows[row][col].ToString()) ? "-" : table.Rows[row][col].ToString());
}
}
//4.准备保存文件夹
if (!Directory.Exists(Path.GetDirectoryName(path)))
Directory.CreateDirectory(Path.GetDirectoryName(path));
//5.保存
xlsDoc.Save(Path.GetDirectoryName(path), overwrite);
}
catch
{
return false;
}
return true;
}
#endregion
#region DataSet 转 Excel
/// <summary>
/// 生成XSL
/// </summary>
/// <param name="ds">DataSet对象</param>
/// <param name="path">保存路径(包含文件名)</param>
/// <returns></returns>
public static bool CreateXLS(DataSet ds, string path)
{
return CreateXLS(ds, path, true);
}
/// <summary>
/// 生成XSL
/// </summary>
/// <param name="ds">DataSet对象</param>
/// <param name="path">保存路径(包含文件名)</param>
/// <param name="overwrite">是否覆盖</param>
/// <returns></returns>
public static bool CreateXLS(DataSet ds, string path, bool overwrite)
{
if (File.Exists(path) && !overwrite)
return false;
try
{
//1.创建xls对象
XlsDocument xlsDoc = new XlsDocument();
xlsDoc.FileName = Path.GetFileName(path);
for (int i = 0; i < ds.Tables.Count; i++)
{
//2.创建表
string sheetName = string.IsNullOrEmpty(ds.Tables[i].TableName) ? "Sheet" + i.ToString() : ds.Tables[i].TableName;
Worksheet sheet = xlsDoc.Workbook.Worksheets.Add(sheetName);
//3.创建行列,注意cellRow,cellColumn都必须>=1
Cells cells = sheet.Cells;
//3.1 添加字段名
for (int col = 0; col < ds.Tables[i].Columns.Count; col++)
{
Cell cell = cells.Add(1, col + 1, ds.Tables[i].Columns[col].ColumnName);
cell.Font.Weight = FontWeight.Bold;
}
//3.2 添加记录
for (int row = 0; row < ds.Tables[i].Rows.Count; row++)
{
for (int col = 0; col < ds.Tables[i].Columns.Count; col++)
{
cells.Add(row + 2, col + 1, string.IsNullOrEmpty(ds.Tables[i].Rows[row][col].ToString()) ? "-" : ds.Tables[i].Rows[row][col].ToString());
}
}
}
//4.准备保存文件夹
if (!Directory.Exists(Path.GetDirectoryName(path)))
Directory.CreateDirectory(Path.GetDirectoryName(path));
//5.保存
xlsDoc.Save(Path.GetDirectoryName(path), overwrite);
}
catch
{
return false;
}
return true;
}
#endregion
}
调用示例：
复制内容到剪贴板 程序代码using (SqlConnection conn = new SqlConnection("server=(local);database=Northwind;uid=sa;password=sa;"))
{
DataSet ds = new DataSet();
using (SqlDataAdapter adapter = new SqlDataAdapter("Select * FROM Customers", conn))
{
DataTable table = new DataTable("Customers");
adapter.Fill(table);
ds.Tables.Add(table);
}
using (SqlDataAdapter adapter = new SqlDataAdapter("Select * FROM Employees", conn))
{
DataTable table = new DataTable("Employees");
adapter.Fill(table);
ds.Tables.Add(table);
}
using (SqlDataAdapter adapter = new SqlDataAdapter("Select * FROM Categories", conn))
{
DataTable table = new DataTable("Categories");
adapter.Fill(table);
ds.Tables.Add(table);
}
DataToXSL.CreateXLS(ds, @"f:\Northwind.xls");
}
效果图：
经测试生成的Excel使用Excel2000、Excel2003均能正常打开！
MyXls官方站点：http://myxls.in2bits.org/
+ 查看当前日志评论 (0)
────────────
PJBlog3 v3.2.9.518 Inside.
Processed In 0.079 ms
你没有权限发表评论
────────────
PJBlog3 v3.2.9.518 Inside.
Processed In 0.079 ms
暂无评论
────────────
PJBlog3 v3.2.9.518 Inside.
Processed In 0.079 ms