sqlserver数据库导出到Excel文件
View Code
1 #region 导出按钮
2 /// <summary>
3 /// 导出按钮
4 /// </summary>
5 /// <param name="sender"></param>
6 /// <param name="e"></param>
7 protected void btnExport_Click(object sender, EventArgs e)
8 {
9 try
10 {
11 DataTable dt = new DataTable();
12 dt.Columns.Add("用户名称", typeof(string));
13 dt.Columns.Add("真实名称", typeof(string));
14 dt.Columns.Add("省份名称", typeof(string));
15 dt.Columns.Add("单位", typeof(string));
16 dt.Columns.Add("课程名称", typeof(string));
17 dt.Columns.Add("在线学习时间", typeof(string));
18 dt.Columns.Add("在线学习次数", typeof(string));
19 dt.Columns.Add("提交作业数", typeof(string));
20 dt.Columns.Add("未提交作业数", typeof(string));
21 dt.Columns.Add("发帖数", typeof(string));
22 dt.Columns.Add("精华帖数", typeof(string));
23 dt.Columns.Add("课程总成绩", typeof(string));
24 foreach (DataRow row in ((DataTable)ViewState["dataSourse"]).Rows)//ViewState["DataSourse"]为获得要导出的datatable
25 {
26 DataRow dr = dt.NewRow();
27 dr["用户名称"] = row["用户名称"].ToString();
28 dr["真实名称"] = row["真实名称"].ToString();
29 dr["省份名称"] = row["省份名称"].ToString();
30 dr["单位"] = row["单位"].ToString();
31 dr["课程名称"] = row["课程名称"].ToString();
32 dr["在线学习时间"] = row["在线学习时间"].ToString();
33 dr["在线学习次数"] = row["在线学习次数"].ToString();
34 dr["提交作业数"] = row["提交作业数"].ToString();
35 dr["未提交作业数"] = row["未提交作业数"].ToString();
36 dr["发帖数"] = row["发帖数"].ToString();
37 dr["精华帖数"] = row["精华帖数"].ToString();
38 dr["课程总成绩"] = row["课程总成绩"].ToString();
39 dt.Rows.Add(dr);
40 }
41 string fileAndPath = Server.MapPath("../TempFolder/Template.xls"); //获得模板Excel文件的全路径
42 string path = Request.PhysicalApplicationPath + "TempFolder";//获得当前应用程序服务器的根目录的物理系统路径
43 string fileName = ExcelHelper.DataTableToExcel(fileAndPath, path, dt, ExcelEdition.Excel2003);//将datatable转换为Excel
44 if (string.IsNullOrEmpty(fileName))
45 {
46 this.ShowMessage("没有生成Excle文件。");
47 return;
48 }
49 if (!File.Exists(fileName))
50 throw new FileNotFoundException("文件不存在。");
51 //获得文件信息
52 FileInfo downloadFile = new FileInfo(fileName);
53
54 Response.Clear();
55 //设置返回流的头信息
56 Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(downloadFile.Name, System.Text.Encoding.UTF8));
57 Response.AddHeader("Content-Length", downloadFile.Length.ToString());
58 //设置文件内容类型
59 Response.ContentType = "application/x-excel";
60
61 //将指定的文件直接写入 HTTP 响应输出流，而不在内存中缓冲该文件。
62 //避免WriteFile输出时会占用服务器大量内存.效率低下,不能下载大文件的问题.
63 Response.TransmitFile(downloadFile.FullName);
64
65 Response.Flush();
66 File.Delete(fileName);
67 Response.End();
68 }
69 catch (Exception ex)
70 {
71 ShowMessage(ex.Message);
72 }
73 }
74 #endregion
datatableToExcel方法代码：
View Code
1 /// <summary>
2 /// 写入Excel文档(需有符合格式的文件模板)
3 /// </summary>
4 /// <remarks>
5 /// <param name="filePath">文件名称</param>
6 /// <p
7 aram name="dt">数据源</param>
8 /// <param name="edition">Excel版本信息</param>
9 public static string DataTableToExcel(string filePath, string outputDirectory, DataTable dt, ExcelEdition edition)
10 {
11 return DataTableToExcel(filePath, outputDirectory, "[sheet1$]", GetColumnNames(dt), dt, edition);
12 }
从载datatableToExcel方法代码：
View Code
1 /// <summary>
2 /// 写入Excel文档(需有符合格式的文件模板)
3 /// </summary>
4 /// <remarks>
5 /// </remarks>
6 /// <param name="filePath">文件名称</param>
7 /// <param name="sheetName">工作表名称</param>
8 /// <param name="columnNames">列名</param>
9 /// <param name="dt">数据源</param>
10 /// <param name="edition">Excel版本信息</param>
11 public static string DataTableToExcel(string filePath, string outputDirectory, string sheetName,
12 string columnNames, DataTable dt, ExcelEdition edition)
13 {
14 //参数检查
15 if (string.IsNullOrEmpty(filePath) || string.IsNullOrEmpty(sheetName) || string.IsNullOrEmpty(outputDirectory))
16 {
17 throw new ArgumentNullException("参数不能为空");
18 }
19 if (!Directory.Exists(outputDirectory)) throw new DirectoryNotFoundException("目录不存在");
20 //if (!File.Exists(filePath)) throw new FileNotFoundException("文件不存在");
21 if (File.Exists(filePath))
22 {
23 FileInfo fileInfo = new FileInfo(filePath);
24 if (fileInfo.Attributes.ToString().IndexOf("ReadOnly") != -1)
25 {
26 try
27 {
28 fileInfo.Attributes = FileAttributes.Normal;
29 }
30 catch (Exception)
31 {
32 throw new ArgumentException("改变文件只读属性时出错,请手动去掉模板文件的只读属性");
33 }
34
35 }
36 }
37 else
38 {
39 throw new FileNotFoundException("文件不存在");
40 }
41 if (null == dt) return null;
42
43 //根据模板生成文件
44 string destFileName = CopyFromTemplate(filePath, outputDirectory);
45
46 //根据Excel版本生成连接串
47 string connectionString = CreateConnection(destFileName, edition);
48
49 OleDbTransaction transaction = null;
50 OleDbCommand cmd = null;
51 OleDbConnection connection = null;
52 try
53 {
54 connection = new OleDbConnection(connectionString);
55
56 //创建工作区格式
57 CreateSheet(connection, sheetName, columnNames);
58
59 if (connection.State != ConnectionState.Open) connection.Open();
60 transaction = connection.BeginTransaction(); //开启事务
61
62 cmd = new OleDbCommand();
63 cmd.Connection = connection;
64 cmd.Transaction = transaction;
65
66 foreach (DataRow row in dt.Rows)
67 {
68 //插入记录
69 cmd.CommandText = GetInsertRecordString(dt, row, sheetName);
70 cmd.ExecuteNonQuery();
71 }
72 transaction.Commit(); //提交事务
73 }
74 catch (Exception ex)
75 {
76 transaction.Rollback(); //回滚事务
77 connection.Close();
78
79 if (File.Exists(destFileName))
80 File.Delete(destFileName); //删除Excel文件
81
82 //包装异常再次抛出
83 throw new ApplicationException("写入Excel失败");
84 //throw ExceptionManager.WrapExcepion(ex, "写入Excel失败，" + ex.Message, typeof(ExcelAccessException));
85 }
86 finally
87 {
88 cmd.Dispose();
89 transaction.Dispose();
90 connection.Dispose();
91 }
92 return destFileName;
93 }
获得datatable的列名称：
View Code
1 /// <summary>
2 /// 获得列名
3 /// </summary>
4 /// <param name="dt">数据源</param>
5 /// <returns>全部列名</returns>
6 private static string GetColumnNames(DataTable dt)
7 {
8 StringBuilder sb = new StringBuilder();
9 foreach (DataColumn column in dt.Columns)
10 {
11 sb.Append(string.Format("[{0}],", column.ColumnName));
12 }
13 string columnNames = sb.ToString().TrimEnd(',');
14 return columnNames;
15 }
16 #endregion
//根据模板创建副本：
CopyFromTemplate方法代码：
View Code
1 /// <summary>
2 /// 根据模板创建副本
3 /// </summary>
4 /// <param name="filePath">原始文件路径</param>
5 /// <param name="outputDirectory">输入路径</param>
6 /// <returns>新文件路径</returns>
7 private static string CopyFromTemplate(string filePath, string outputDirectory)
8 {
9 FileInfo file = new FileInfo(filePath);
10 string destFileName = Path.Combine(outputDirectory, file.Name.Replace(file.Extension, string.Empty) +
11 "-" + DateTime.Now.ToString("yyyy-MM-dd") + "(" + Guid.NewGuid() + ")" + file.Extension);
12 File.Copy(filePath, destFileName);
13
14 return destFileName;
15 }
//创建工作区
CreateSheet方法代码：
View Code
1 /// <summary>
2 /// 创建工作区
3 /// </summary>
4 /// <param name="connection">OleDB连接</param>
5 /// <param name="sheetName">工作区名称</param>
6 /// <param name="columnNames">列名</param>
7 private static void CreateSheet(OleDbConnection connection, string sheetName, string columnNames)
8 {
9 string createTableScript = GetCreateTableString(sheetName, columnNames.Split(','));
10
11 connection.Open();
12
13 using (OleDbCommand cmd = new OleDbCommand(createTableScript, connection))
14 {
15 cmd.ExecuteNonQuery();
16 }
17
18 connection.Close();
19 }
ExcelHelper类代码：
namespace Ifmsoft.Framework.Data
{
public enum ExcelEdition
{
Excel97,
Excel2000,
Excel2003,
Excel2007
}
public class ExcelHelper
{
private ExcelHelper() { }
/// <summary>
/// 获得Excel连接串
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="edition">Excel版本号</param>
/// <returns>Excel连接串</returns>
private static string CreateConnection(string filePath, ExcelEdition edition)
{
switch (edition)
{
case ExcelEdition.Excel97:
return " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + filePath +
";Extended Properties=Excel 5.0";
case ExcelEdition.Excel2000:
return " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + filePath +
";Extended Properties=Excel 8.0";
case ExcelEdition.Excel2003:
return " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + filePath +
";Extended Properties=Excel 8.0";
case ExcelEdition.Excel2007:
return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath +
";Extended Properties=\"Excel 12.0;HDR=YES\"";
default:
return " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + filePath +
";Extended Properties=Excel 8.0";
}
}
/// <summary>
/// 格式化工作表名称“[sheet1$]”
/// </summary>
/// <remarks>
/// 履 历：
/// NO 日期 版本 姓名 内容
/// 1 2008/10/06 V0L01 IFM)于鹏 初版
/// </remarks>
/// <param name="sheetName">工作表名称</param>
/// <returns></returns>
public static string FormatSheetName(string sheetName)
{
return "[" + sheetName + "$]";
}
/// <summary>
/// 读取Excel文件到Dataset
/// </summary>
/// <remarks>
/// 履 历：
/// NO 日期 版本 姓名 内容
/// 1 2008/10/06 V0L01 IFM)于鹏 初版
/// </remarks>
/// <param name="filePath">文件路径</param>
/// <param name="edition">Excel版本号</param>
/// <returns></returns>
public static DataSet ExecuteDataset(string filePath, ExcelEdition edition)
{
return ExecuteDataset(filePath, "[sheet1$]", edition);
}
/// <summary>
/// 读取Excel文件到Dataset
/// </summary>
/// <remarks>
/// 履 历：
/// NO 日期 版本 姓名 内容
/// 1 2008/10/06 V0L01 IFM)于鹏 初版
/// </remarks>
/// <param name="filePath">文件路径</param>
/// <param name="sheetName">工作表名</param>
/// <param name="edition">Excel版本号</param>
/// <returns></returns>
public static DataSet ExecuteDataset(string filePath, string sheetName, ExcelEdition edition)
{
if (string.IsNullOrEmpty(filePath) || string.IsNullOrEmpty(sheetName))
{
throw new ArgumentNullException("参数不能为空");
}
if (!File.Exists(filePath)) throw new FileNotFoundException("文件不存在");
string connectionString = CreateConnection(filePath, edition);
string commandText = " SELECT * FROM " + sheetName;
DataSet ds = new DataSet();
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
using (OleDbDataAdapter da = new OleDbDataAdapter(commandText, connection))
{
da.Fill(ds, sheetName);
connection.Close();
}
}
return ds;
}
/// <summary>
/// 读取Excel文件到DataTable
/// </summary>
/// <remarks>
/// 履 历：
/// NO 日期 版本 姓名 内容
/// 1 2008/10/06 V0L01 IFM)于鹏 初版
/// </remarks>
/// <param name="filePath">文件路径</param>
/// <param name="edition">Excel版本号</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string filePath, ExcelEdition edition)
{
return ExecuteDataTable(filePath, "[sheet1$]", edition);
}
/// <summary>
/// 读取Excel文件到DataTable
/// </summary>
/// <remarks>
/// 履 历：
/// NO 日期 版本 姓名 内容
/// 1 2008/10/06 V0L01 IFM)于鹏 初版
/// </remarks>
/// <param name="filePath">文件路径</param>
/// <param name="sheetName">工作表名</param>
/// <param name="edition">Excel版本号</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string filePath, string sheetName, ExcelEdition edition)
{
//ValidationManager.ArgumentNotNullOrEmpty(filePath, "filePath");
//ValidationManager.ArgumentNotNullOrEmpty(sheetName, "sheetName");
if (string.IsNullOrEmpty(filePath) || string.IsNullOrEmpty(sheetName))
{
throw new ArgumentNullException("参数不能为空");
}
if (!File.Exists(filePath)) throw new FileNotFoundException("文件不存在");
string connectionString = CreateConnection(filePath, edition);
string commandText = " SELECT * FROM " + sheetName;
DataTable dt = new DataTable();
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
using (OleDbDataAdapter da = new OleDbDataAdapter(commandText, connection))
{
da.Fill(dt);
connection.Close();
}
}
return dt;
}
/// <summary>
/// 写入Excel文档(需有符合格式的文件模板)
/// </summary>
/// <remarks>
/// 履 历：
/// NO 日期 版本 姓名 内容
/// 1 2008/10/06 V0L01 IFM)于鹏 初版
/// </remarks>
/// <param name="filePath">文件名称</param>
/// <param name="dt">数据源</param>
/// <param name="edition">Excel版本信息</param>
public static string DataTableToExcel(string filePath, string outputDirectory, DataTable dt, ExcelEdition edition)
{
return DataTableToExcel(filePath, outputDirectory, "[sheet1$]", GetColumnNames(dt), dt, edition);
}
/// <summary>
/// 写入Excel文档(需有符合格式的文件模板)
/// </summary>
/// <remarks>
/// 履 历：
/// NO 日期 版本 姓名 内容
/// 1 2008/10/06 V0L01 IFM)于鹏 初版
/// </remarks>
/// <param name="filePath">文件名称</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="dt">数据源</param>
/// <param name="edition">Excel版本信息</param>
public static string DataTableToExcel(string filePath, string outputDirectory, string sheetName,
DataTable dt, ExcelEdition edition)
{
return DataTableToExcel(filePath, outputDirectory, sheetName, GetColumnNames(dt), dt, edition);
}
/// <summary>
/// 写入Excel文档(需有符合格式的文件模板)
/// </summary>
/// <remarks>
/// 履 历：
/// NO 日期 版本 姓名 内容
/// 1 2008/10/06 V0L01 IFM)于鹏 初版
/// </remarks>
/// <param name="filePath">文件名称</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="columnNames">列名</param>
/// <param name="dt">数据源</param>
/// <param name="edition">Excel版本信息</param>
public static string DataTableToExcel(string filePath, string outputDirectory, string sheetName,
string columnNames, DataTable dt, ExcelEdition edition)
{
//参数检查
if (string.IsNullOrEmpty(filePath) || string.IsNullOrEmpty(sheetName) || string.IsNullOrEmpty(outputDirectory))
{
throw new ArgumentNullException("参数不能为空");
}
if (!Directory.Exists(outputDirectory)) throw new DirectoryNotFoundException("目录不存在");
//if (!File.Exists(filePath)) throw new FileNotFoundException("文件不存在");
if (File.Exists(filePath))
{
FileInfo fileInfo = new FileInfo(filePath);
if (fileInfo.Attributes.ToString().IndexOf("ReadOnly") != -1)
{
try
{
fileInfo.Attributes = FileAttributes.Normal;
}
catch (Exception)
{
throw new ArgumentException("改变文件只读属性时出错,请手动去掉模板文件的只读属性");
}
}
}
else
{
throw new FileNotFoundException("文件不存在");
}
if (null == dt) return null;
//根据模板生成文件
string destFileName = CopyFromTemplate(filePath, outputDirectory);
//根据Excel版本生成连接串
string connectionString = CreateConnection(destFileName, edition);
OleDbTransaction transaction = null;
OleDbCommand cmd = null;
OleDbConnection connection = null;
try
{
connection = new OleDbConnection(connectionString);
//创建工作区格式
CreateSheet(connection, sheetName, columnNames);
if (connection.State != ConnectionState.Open) connection.Open();
transaction = connection.BeginTransaction(); //开启事务
cmd = new OleDbCommand();
cmd.Connection = connection;
cmd.Transaction = transaction;
foreach (DataRow row in dt.Rows)
{
//插入记录
cmd.CommandText = GetInsertRecordString(dt, row, sheetName);
cmd.ExecuteNonQuery();
}
transaction.Commit(); //提交事务
}
catch (Exception ex)
{
transaction.Rollback(); //回滚事务
connection.Close();
if (File.Exists(destFileName))
File.Delete(destFileName); //删除Excel文件
//包装异常再次抛出
throw new ApplicationException("写入Excel失败");
//throw ExceptionManager.WrapExcepion(ex, "写入Excel失败，" + ex.Message, typeof(ExcelAccessException));
}
finally
{
cmd.Dispose();
transaction.Dispose();
connection.Dispose();
}
return destFileName;
}
///// <summary>
///// 写入Excel文档(需有符合格式的文件模板)
///// </summary>
///// <remarks>
///// 履 历：
///// NO 日期 版本 姓名 内容
///// 1 2008/10/06 V0L01 IFM)于鹏 初版
///// </remarks>
///// <param name="filePath">文件名称</param>
///// <param name="sheetName">工作表名称</param>
///// <param name="columnNames">列名</param>
///// <param name="dt">数据源</param>
///// <param name="edition">Excel版本信息</param>
//public static void DataTableToExcel(string filePath, string outputDirectory, string sheetName,
// string columnNames, DataTable dt, ExcelEdition edition)
//{
// //参数检查
// //ValidationManager.ArgumentNotNullOrEmpty(filePath, "filePath");
// //ValidationManager.ArgumentNotNullOrEmpty(outputDirectory, "outputDirectory");
// //ValidationManager.ArgumentNotNullOrEmpty(sheetName, "sheetName");
// if (!File.Exists(filePath)) throw new FileNotFoundException("文件不存在");
// if (!Directory.Exists(outputDirectory)) throw new DirectoryNotFoundException("目录不存在");
// if (null == dt) return;
// //根据模板生成文件
// string destFileName = CopyFromTemplate(filePath, outputDirectory);
// //根据Excel版本生成连接串
// string connectionString = CreateConnection(destFileName, edition);
// OleDbTransaction transaction = null;
// OleDbCommand cmd = null;
// OleDbConnection connection = null;
// try
// {
// connection = new OleDbConnection(connectionString);
// //创建工作区格式
// CreateSheet(connection, sheetName, columnNames);
// if (connection.State != ConnectionState.Open) connection.Open();
// transaction = connection.BeginTransaction(); //开启事务
// cmd = new OleDbCommand();
// cmd.Connection = connection;
// cmd.Transaction = transaction;
// foreach (DataRow row in dt.Rows)
// {
// //插入记录
// cmd.CommandText = GetInsertRecordString(dt, row, sheetName);
// cmd.ExecuteNonQuery();
// }
// transaction.Commit(); //提交事务
// }
// catch (Exception ex)
// {
// transaction.Rollback(); //回滚事务
// connection.Close();
// if (File.Exists(destFileName))
// File.Delete(destFileName); //删除Excel文件
// //包装异常再次抛出
// //throw ExceptionManager.WrapExcepion(ex, "写入Excel失败，" + ex.Message, typeof(ExcelAccessException));
// }
// finally
// {
// cmd.Dispose();
// transaction.Dispose();
// connection.Dispose();
// }
//}
/// <summary>
/// 获取 Excel 连接对象
/// （用于读取 Excel 2000 及更高版本文件）。
/// </summary>
/// <param name="fullPath">文件的完全路径。</param>
/// <param name="firstRowAsHeader">是否将第一行作为表头处理。</param>
/// <param name="modeValue">Excel 导入混合类型，通常使用 ImportMixedType.Text。</param>
/// <returns>Excel 连接对象。</returns>
public static OleDbConnection GetExcelConnection(string fullPath, bool firstRowAsHeader, ImportMixedType modeValue)
{
return GetExcelConnection(fullPath, firstRowAsHeader, modeValue, ExcelVersion.Excel8);
}
/// <summary>
/// 获取 Excel 连接对象。
/// </summary>
/// <param name="fullPath">文件的完全路径。</param>
/// <param name="firstRowAsHeader">是否将第一行作为表头处理。</param>
/// <param name="modeValue">Excel 导入混合类型，通常使用 ImportMixedType.Text。</param>
/// <param name="excelVersionNumber">Excel 文件版本号。</param>
/// <returns>Excel 连接对象。</returns>
public static OleDbConnection GetExcelConnection(string fullPath, bool firstRowAsHeader, ImportMixedType modeValue, ExcelVersion excelVersionNumber)
{
if (fullPath == null)
throw new ArgumentNullException("fullPath", "参数值不能为空引用。");
if (fullPath.Trim() == string.Empty)
throw new ArgumentException("参数值不能为空字符串。", "fullPath");
if (!File.Exists(fullPath))
throw new FileNotFoundException(string.Format("访问给定路径下的文件失败。文件路径：{0}", fullPath));
string hdrString = "No";
if (firstRowAsHeader)
{
hdrString = "Yes";
}
string excelVersionString = string.Empty;
switch (excelVersionNumber)
{
case ExcelVersion.Excel5:
excelVersionString = "Excel 5.0";
break;
case ExcelVersion.Excel8:
excelVersionString = "Excel 8.0";
break;
}
// 构造连接字符串
string connectionString = string.Format(@"
Provider=Microsoft.Jet.OLEDB.4.0;
Data Source={0};
Extended Properties=""{1};HDR={2};IMEX={3};""
", fullPath, excelVersionString, hdrString, modeValue);
return new OleDbConnection(connectionString);
#region Excel 连接串详解
//
// Connection String -- 连接字符串
//
// # 在书写连接字符串须十分仔细，因为如果连接串中存在错误，
// Jet Engine 不会给出恰当的错误细节。
//
// # Syntax 语法如下:
// Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<Full Path of Excel File>; Extended Properties="Excel 8.0; HDR=No; IMEX=1"
//
//
// Extended Properties -- 扩展属性
//
// # Excel <Excel 8.0/Excel 5.0>
// 指定 Excel Sheet 的版本。
// 对于 Excel 2000 及更高版本，应选用 Excel 8.0；
// 对于其他版本，应选用 Excel 5.0。
//
// # HDR= <Yes/No>
// 该属性用于为各列指定列头。
// 若该值为 Yes，则首行被作为列头处理；
// 若该值为 No，则由系统自动产生列头，形如：F1, F2 等。
//
// # IMEX= <0/1/2>
// IMEX 指 IMport EXport mode。该属性具有三种可能的值。
//
// * IMEX=0 和 IMEX=2 将导致 ImportMixedTypes 的值被忽略而使用 ‘Majority Types’ 的缺省值。
// 这种情况下，将获取前 8 行数据及其各列的数据类型。
//
// * IMEX=1 是将 ImportMixedTypes 值设置为 Text 类型的唯一方式。
// 这样，每样东东都被作为文本处理。
//
//
// 以上信息参考：
// http://www.codeproject.com/useritems/Excel_Connectivity.asp
//
#endregion Excel 连接串详解
}
/// <summary>
/// 读取给定连接给定表的内容至 DataTable。
/// </summary>
/// <param name="cn">给定连接</param>
/// <param name="sheetName">给定 WorkSheet 的名称</param>
/// <returns>包含给定 Sheet 数据的 DataTable</returns>
public static System.Data.DataTable ExecuteDataTable(OleDbConnection cn, string sheetName)
{
System.Data.DataTable dt = null;
if (sheetName == null)
throw new ArgumentNullException("sheetName", "参数值不能为空引用。");
if (sheetName.Trim() == string.Empty)
throw new ArgumentException("参数值不能为空字符串。", "sheetName");
string queryString = string.Format("SELECT * FROM [{0}$]", sheetName);
dt = new System.Data.DataTable(sheetName);
OleDbDataAdapter da = new OleDbDataAdapter(queryString, cn);
da.Fill(dt);
return dt;
}
#region 私有方法
/// <summary>
/// 根据模板创建副本
/// </summary>
/// <param name="filePath">原始文件路径</param>
/// <param name="outputDirectory">输入路径</param>
/// <returns>新文件路径</returns>
private static string CopyFromTemplate(string filePath, string outputDirectory)
{
FileInfo file = new FileInfo(filePath);
string destFileName = Path.Combine(outputDirectory, file.Name.Replace(file.Extension, string.Empty) +
"-" + DateTime.Now.ToString("yyyy-MM-dd") + "(" + Guid.NewGuid() + ")" + file.Extension);
File.Copy(filePath, destFileName);
return destFileName;
}
/// <summary>
/// 创建工作区
/// </summary>
/// <param name="connection">OleDB连接</param>
/// <param name="sheetName">工作区名称</param>
/// <param name="columnNames">列名</param>
private static void CreateSheet(OleDbConnection connection, string sheetName, string columnNames)
{
string createTableScript = GetCreateTableString(sheetName, columnNames.Split(','));
connection.Open();
using (OleDbCommand cmd = new OleDbCommand(createTableScript, connection))
{
cmd.ExecuteNonQuery();
}
connection.Close();
}
/// <summary>
/// 获得创建工作区格式脚本
/// </summary>
/// <param name="sheetName">工作区名称</param>
/// <param name="names">列名</param>
/// <returns>创建脚本</returns>
private static string GetCreateTableString(string sheetName, string[] names)
{
StringBuilder sb = new StringBuilder();
sb.Append(string.Format("CREATE TABLE {0} (", sheetName));
for (int i = 0; i < names.Length; i++)
{
if (i == names.Length - 1)
sb.Append(string.Format("{0} VARCHAR(100))", names[i]));
else
sb.Append(string.Format("{0} VARCHAR(100),", names[i]));
}
return sb.ToString();
}
/// <summary>
/// 获得插入记录脚本
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="row">当前数据行</param>
/// <returns>插入记录脚本</returns>
private static string GetInsertRecordString(DataTable dt, DataRow row, string sheetName)
{
StringBuilder sb = new StringBuilder();
sb.Append(string.Format("INSERT INTO {0} VALUES(", sheetName));
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i == dt.Columns.Count - 1)
sb.Append(string.Format("'{0}')", row[i].ToString().Replace("'", "''")));
else
sb.Append(string.Format("'{0}',", row[i].ToString().Replace("'", "''")));
}
return sb.ToString();
}
/// <summary>
/// 获得列名
/// </summary>
/// <param name="dt">数据源</param>
/// <returns>全部列名</returns>
private static string GetColumnNames(DataTable dt)
{
StringBuilder sb = new StringBuilder();
foreach (DataColumn column in dt.Columns)
{
sb.Append(string.Format("[{0}],", column.ColumnName));
}
string columnNames = sb.ToString().TrimEnd(',');
return columnNames;
}
#endregion
#region 枚举
/// <summary>
/// Excel 导入混合类型。
/// </summary>
public enum ImportMixedType
{
/// <summary>
/// 多值（数）类型 0，慎用此值！
/// </summary>
Majority0 = 0,
/// <summary>
/// 文本类型，通常使用该值。
/// </summary>
Text = 1,
/// <summary>
/// 多值（数）类型 2，慎用此值！
/// </summary>
Majority2 = 2
}
/// <summary>
/// Excel 文件版本。
/// 读取 Excel 2000 及更高版本文件时应选用 Excel8；
/// 读取其他版本文件时均选用 Excel5。
/// </summary>
public enum ExcelVersion
{
/// <summary>
/// 读取低于 Excel 2000 的版本文件时应选用此值。
/// </summary>
Excel5 = 0,
/// <summary>
/// 读取 Excel 2000 及更高版本文件时应选用此值。
/// </summary>
Excel8 = 1
}
#endregion 枚举
}
}
