.NET实现DataSet转Excel
来源:互联网 发布:苏州相城淘宝运营培训 编辑:程序博客网 时间:2024/05/17 09:18
转:http://www.mzwu.com/article.asp?id=2187
这边我们借助一个组件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 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");
}
{
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/
- .NET实现DataSet转Excel
- asp.net Dataset导出excel
- 实现dataset与excel转换
- 实现dataset与excel转换
- 【转】DataSet导出EXCEL
- Excel转DataSet应用
- (C#)asp.net(DataSet)导出Excel文件
- ASP.NET C# IList/dataset导出excel
- asp.net DataSet 转换成 excel 表格
- 在asp.net中实现dataset与excel的相互导入导出
- ASP.NET(C#) DataSet数据导出到Excel
- 将Dataset数据导出到Excel中(ASP.NET/C#)
- 将Dataset数据导出到Excel中(ASP.NET/C#)
- .net导入Excel文件返回DataSet数据集
- Excel转DataSet的一个例子
- winform下Dataset转Excel文件
- [C#]网格数据导出到Excel,Excel转DataSet
- 在ASP.NET中将dataset数据保存到excel,GridView导出到excel
- c#下载文件
- 使用ADO的诸多常见疑难杂症
- Hibernate执行原生SQL别名导致的一个Bug
- MFC下文件打开和保存操作
- 当年明月
- .NET实现DataSet转Excel
- 计数器
- 处理mysql代码编译问题
- 如何进行Web应用的安全测试和输入校验
- 人类的心灵——赢的力量
- VC运行时库
- Don't Work for Money
- ASP.NET MVC 之Controller & Action (2)
- UTF-8 是什么东东?