欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

导出大量数据,解决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", ""));
            }
        }