导出大量数据,解决npoi导出内存溢出
程序员文章站
2022-07-13 13:16:31
...
/// <summary>
/// 使用OLEDB导出Excel
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="filepath">文件目录和文件名</param>
/// <param name="tablename">SHEET页名称</param>
/// <param name="pagecount">每页记录数</param>
public static void Export(DataTable dt, string filepath, string tablename, int pagecount,string UserID)
{
string docupath = System.Web.HttpContext.Current.Server.MapPath("~\\NPOI\\" + UserID + "\\");
//建立数据夹
if (!Directory.Exists(docupath))
{
Directory.CreateDirectory(docupath);
}
//else
//{
// File.Delete(docupath);
// Directory.CreateDirectory(docupath);
//}
//excel 2003格式
string name = docupath + filepath;
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + name + ";Extended Properties=Excel 8.0;";
//Excel 2007格式
//string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + name + ";Extended Properties=Excel 12.0 Xml;";
try
{
using (OleDbConnection con = new OleDbConnection(connString))
{
con.Open();
//开始分页
if (dt.Rows.Count > pagecount)
{
int page = dt.Rows.Count / pagecount + 1; //总页数
for (int i = 0; i < page; i++)
{
//建新sheet和表头
StringBuilder strSQL = new StringBuilder();
string tabname = tablename + i.ToString();
strSQL.Append("CREATE TABLE ").Append("[" + tabname + "]"); //每60000项建一页
strSQL.Append("(");
for (int j = 0; j < dt.Columns.Count; j++)
{
strSQL.Append("[" + dt.Columns[j].ColumnName + "] text,");
}
strSQL = strSQL.Remove(strSQL.Length - 1, 1);
strSQL.Append(")");
OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con);
cmd.ExecuteNonQuery();
//准备逐条插入数据
for (int j = i * pagecount; j < (i + 1) * pagecount; j++)
{
if (i == 0 || j < dt.Rows.Count)
{
StringBuilder tmp = new StringBuilder();
StringBuilder strfield = new StringBuilder();
StringBuilder strvalue = new StringBuilder();
for (int z = 0; z < dt.Columns.Count; z++)
{
strfield.Append("[" + dt.Columns[z].ColumnName + "]");
strvalue.Append("'" + dt.Rows[j][z].ToString() + "'");
if (z != dt.Columns.Count - 1)
{
strfield.Append(",");
strvalue.Append(",");
}
else
{
}
}
cmd.CommandText = tmp.Append(" insert into [" + tabname + "]( ")
.Append(strfield.ToString())
.Append(") values (").Append(strvalue).Append(")").ToString();
cmd.ExecuteNonQuery();
}
}
}
}
con.Close();
//no = count;
}
Console.WriteLine("OK");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
GC.Collect();
Table_Export_BIG(UserID);
}
/// <summary>
/// 根据userid找到服务器地址路径下文件夹压缩并下载下来
/// </summary>
/// <param name="操作人id_文件夹名字"></param>
public static void Table_Export_BIG(string UserID)
{
try
{
//DataTable dtout = DataAccess.GetTable("select top 1 * from IMN009");
#region 打包发送主目录;
string strInDirectoryPath = System.Web.HttpContext.Current.Server.MapPath("~\\NPOI\\" + UserID);//路径
//if (File.Exists(strInDirectoryPath))//必须判断要复制的文件是否存在
//{
// File.Copy(strInDirectoryPath, pSaveFilePath, true);//三个参数分别是源文件路径,存储路径,若存储路径有相同文件是否替换
//}
string strOutFilePath = System.Web.HttpContext.Current.Server.MapPath("~\\NPOI\\" + UserID + ".zip");
DownLoadFilesHelp.CompressDirectory(strInDirectoryPath, strOutFilePath, UserID);
//CopyDirectory(strInDirectoryPath, pSaveFilePath);
string fileName = UserID + ".zip";//客户端保存的文件名
string filePath = System.Web.HttpContext.Current.Server.MapPath("~\\NPOI\\" + UserID + ".zip");//路径
string file = System.Web.HttpContext.Current.Server.MapPath("~\\NPOI\\" + UserID);
FileInfo fileInfo = new FileInfo(strOutFilePath);
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.ClearContent();
System.Web.HttpContext.Current.Response.ClearHeaders();
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
System.Web.HttpContext.Current.Response.AddHeader("Content-Length", fileInfo.Length.ToString());
System.Web.HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");
System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
System.Web.HttpContext.Current.Response.WriteFile(fileInfo.FullName);
System.Web.HttpContext.Current.Response.Flush();
File.Delete(filePath);
DirectoryInfo dir = new DirectoryInfo(file);
if (dir.Exists)
{
DirectoryInfo[] childs = dir.GetDirectories();
foreach (DirectoryInfo child in childs)
{
child.Delete(true);
}
dir.Delete(true);
}
System.Web.HttpContext.Current.Response.End();
#endregion
//DataAccess.Despose();
}
catch (Exception ex)
{
//zAlert(ex.Message.ToString().Replace("\r\n", ""));
}
}