C#通过OLEDB将DataTable写入Excel文件中
2024-09-08 07:44:33
利用OLEDB将DataTable数据写入Excel文件中,如果数据量过多,执行效率很缓慢,大数据量不推荐使用此方法。
/// <summary>
/// 创建DataTable
/// </summary>
/// <returns></returns>
private DataTable CreateDataTable()
{
var dt = new DataTable();
dt.Columns.Add("Field1");
dt.Columns.Add("Field2");
return dt;
} /// <summary>
/// 根据文件后缀名判断Excel版本 链接字符串
/// 参数HDR的值:
/// HDR=Yes,这代表第一行是标题,不做为数据使用 ,如果用HDR=NO,则表示第一行不是标题,做为数据来使用。
/// 参数IMEX的值:
/// 当 IMEX = 0 时为“汇出模式”,这个模式开启的 Excel 档案只能用来做“写入”用途。
/// 当 IMEX = 1 时为“汇入模式”,这个模式开启的 Excel 档案只能用来做“读取”用途。
/// 当 IMEX = 2 时为“链接模式”,这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
/// </summary>
/// <param name="filepath">文件目录和文件名</param>
/// <param name="pagesize">每页记录数</param>
/// <returns></returns>
public static string GetExcelConnStr(string filepath, out int pagesize)
{
StringBuilder sb = new StringBuilder();
string extension = Path.GetExtension(filepath);
if (extension == ".xlsx")
{
pagesize = 1048575; //实际行数 1048576
sb.Append("Provider=Microsoft.Ace.OleDb.12.0;Data Source=");
sb.Append(filepath);
sb.Append(";Extended Properties='Excel 12.0;HDR=Yes;IMEX=2'");
}
else
{
pagesize = 65535; //实际行数 65536
sb.Append("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=");
sb.Append(filepath);
sb.Append(";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'");
}
return sb.ToString();
}
/// <summary>
/// 使用OLEDB导出Excel
/// Excel程序支持的文件类型不止一种。
/// 在excel早期版本中,默认的工作薄扩展名为".xls",这种格式的文件最多可以包含255个工作页(Worksheet),每个zhidao工作页中包含65535行(Row)和256列(Column)。
/// 自Office2007版本起,excel默认的工作薄扩展名为".xlsx",这种格式的文件中每个工作页包含1048576行(Row),16384列(Column)。
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="filepath">文件目录和文件名</param>
/// <param name="tablename">SHEET页名称</param>
/// <param name="pagesize">每页记录数</param>
public static void Export(DataTable dt, string filepath, string tablename, int pagesize = 0)
{
int pagecount = 0;
string connString = GetExcelConnStr(filepath, out pagecount);
if (pagesize > 0)
{
pagecount = pagesize;
} try
{
using (OleDbConnection con = new OleDbConnection(connString))
{
con.Open(); DataTable dtSheet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
var sheetCount = dtSheet.Rows.Count; //总记录数
var recordCount = dt.Rows.Count;
//列数
var columnCount = dt.Columns.Count; OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con; //开始分页
int page = (recordCount + pagecount - 1) / pagecount; //总页数
for (int i = 0; i < page; i++)
{
//新的Sheet名称
string tabname = tablename + (i + 1).ToString(); //获取已存在的表
if (sheetCount > 0)
{
int m = 0;
foreach (DataRow dr in dtSheet.Rows)
{
if (m == i)
{
tabname = dr["TABLE_NAME"].ToString(); cmd.CommandText = "DROP TABLE [" + tabname + "]";
cmd.ExecuteNonQuery();// 执行创建sheet的语句
}
m++;
}
} //建新sheet和表头
StringBuilder createSQL = new StringBuilder();
createSQL.Append("CREATE TABLE ").Append("[" + tabname + "]"); //每60000项建一页
createSQL.Append("(");
for (int j = 0; j < columnCount; j++)
{
createSQL.Append("[" + dt.Columns[j].ColumnName + "] text,");
}
createSQL = createSQL.Remove(createSQL.Length - 1, 1);
createSQL.Append(")"); cmd.CommandText = createSQL.ToString();
cmd.ExecuteNonQuery(); StringBuilder strfield = new StringBuilder();
for (int z = 0; z < columnCount; z++)
{
if (z > 0)
{
strfield.Append(",");
}
strfield.Append("[" + dt.Columns[z].ColumnName + "]");
} //准备逐条插入数据
for (int j = i * pagecount; j < (i + 1) * pagecount; j++)
{
if (i == 0 || j < recordCount)
{
StringBuilder insertSQL = new StringBuilder();
StringBuilder strvalue = new StringBuilder();
for (int z = 0; z < columnCount; z++)
{
if (z > 0)
{
strvalue.Append(",");
}
strvalue.Append("'" + dt.Rows[j][z].ToString() + "'");
} insertSQL.Append(" insert into [" + tabname + "]( ")
.Append(strfield.ToString())
.Append(") values (").Append(strvalue).Append(") "); cmd.CommandText = insertSQL.ToString();
cmd.ExecuteNonQuery();
}
}
} con.Close();
}
}
catch (Exception ex)
{
GC.Collect();
}
}
具体使用方法:
DataTable dt = CreateDataTable();
for (int i = 0; i < 70000; i++)
{
DataRow dr = dt.NewRow();
dr[0] = i;
dr[1] = "Value" + i.ToString();
dt.Rows.Add(dr);
}
string filepath = Server.MapPath("~/") + "数据_" + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xls";
Export(dt, filepath, "数据");
最新文章
- 【jQuery小实例】---3 凤凰网首页图片动态效果
- 让 select 的 option 标签支持事件监听(如复制操作)
- EntityFramework优缺点
- 神经网络(python源代码)
- 2016HUAS暑假集训训练2 K - Hero
- 搭建openvpn 未完成。。。
- iOS 知识点梳理
- [转]AngularJS: 使用Scope时的6个陷阱
- C++ 继承的访问权限
- iconv装换文件编码格式
- linux wc命令
- error: C2664: “zajiao::zajiao(const zajiao &;)”: 无法将参数 1 从“const char [12]”转换为“char *”
- 移动端图片放大滑动查看-插件photoswipe的使用
- 完整的站内搜索实战应用(Lucene.Net+盘古分词)
- SQL 中常用存储过程xp_cmdshell运行cmd命令
- ajax请求基于restFul的WebApi(post、get、delete、put)
- window安装ab压力测试并使用
- Python数据挖掘课程
- LeetCode--455--分发饼干
- C# 8.0、.NET Framework 4.8与NET Standard 2.1的一个说明