C# 大數據導出word的假死報錯的處理方法

2020-01-24 03:33:15

字體：大中小

來源：轉載

供稿：網友

最近一個項目是一個基于winform的報告系統，根據一系列的查詢參數計算出結果，最終生成一個格式規范的word文檔，剛開始數據量不大（500行）數據以內，寫入速度還能接受，但最近遇到一個問題就是當出現大量的數據行的時候，寫入word的過程就變的非常慢，CPU直接拉到100%，本人機器配置已經算比較高的了，8G內存+i5CPU，依舊假死，該問題困擾了我幾天，也問了google很多次，基本上給出的答案都是word本身就比較慢這樣一類的答案，或者是非托管代碼的優化，慢也就算了，至少可以通過進度條來交互，假死到報錯，這個絕對是零容忍的。嘗試了很多種方法，包括將非托管代碼強制進行回收，多線程等方式，最終都失敗了，當然，黃天不負有心人最終總算是解決了問題了，我的數據量不算特別巨大，就4000多行寫入表中，廢話少說，直接貼代碼：

常規寫入word表格的方法：

復制代碼代碼如下:

private void LoadSectionWord()
        {
           //臨時目錄
            string filePath = Tools.CreateWordFileDir() + Utils.GetTimeString() + ".doc";
          //將二進制文件寫入到word文件
            sectionWordTools.ByteConvertWord(sectionWordTools.WordContentByTemplateIDAndTemplateTitle(templateId, sectionTitle), filePath);
            try
            {
              //wdc為winWordControl控件的實例
                wdC.LoadDocument(filePath);
                Microsoft.Office.Interop.Word.Document wd = (Microsoft.Office.Interop.Word.Document)wdC.document;
                Microsoft.Office.Interop.Word.Application wa = wd.Application;//
               //需要寫入Word的集合
                if (dicList.Count > 0)
                {
                    dicList = dicList.OrderBy(d => d.Key.AnalyteID).ToDictionary(i => i.Key, ii => ii.Value);
                    sectionWordTools.GotoBookMark(wa, "RepeatAnalysisResult");
                    wa.Selection.Copy();//復制模板第一個table
                    sectionWordTools.WordReplace("special matrix", wdg.Species.ToLower().Trim() + " " + wdg.Matrix.ToLower().Trim(), true, wd);
                    string analyteTitles = string.Empty;
                    int index = 0;

                    #region Replace Title
                    foreach (KeyValuePair<AnalyteReNameEntity, DataTable> d in dicList)
                    {

                        AnalyteReNameEntity key = d.Key;
                        if (dicList.Count > 2)
                        {
                            if (index.Equals(dicList.Count - 2))
                            {
                                analyteTitles += key.NewAnalyteName + " and ";
                            }
                            else
                            {
                                analyteTitles += key.NewAnalyteName + " , ";
                            }
                        }
                        else if (dicList.Count == 2)
                        {
                            analyteTitles += key.NewAnalyteName + " and ";
                        }
                        else
                        {
                            analyteTitles += key.NewAnalyteName;
                        }
                        index++;
                    }
                    analyteTitles = analyteTitles.Trim().TrimEnd('d').TrimEnd('n').TrimEnd('a').Trim().Trim(',');
                    sectionWordTools.WordReplace("for Abc000", "for " + analyteTitles, true, wd);
                    #endregion

                    int wordTableCount = 0;
                    foreach (KeyValuePair<AnalyteReNameEntity, DataTable> d in dicList)
                    {
                        AnalyteReNameEntity key = d.Key;
                        DataView dv = d.Value.DefaultView;
                        dv.Sort = "Custom ID";
                        DataTable dt = dv.ToTable();
                        #region 處理dt
                        if (dt.Columns["analyteid"] != null)
                        {
                            dt.Columns.Remove("analyteid");
                        } if (dt.Columns["id"] != null)
                        {
                            dt.Columns.Remove("id");
                        }
                        if (dt.Columns["reportid"] != null)
                        {
                            dt.Columns.Remove("reportid");
                        }
                        if (dt.Columns["studyid"] != null)
                        {
                            dt.Columns.Remove("studyid");
                        }
                        if (dt.Columns["analyteid"] != null)
                        {
                            dt.Columns.Remove("analyteid");
                        }
                        #endregion
                        //第一個WordTable
                        Microsoft.Office.Interop.Word.Table tb = wd.Tables[wd.Tables.Count];

                        #region 填充值
                        if (dt.Rows.Count > 0)
                        {
                            object beforerow = tb.Rows[2];
                            //表頭
                            for (int i = 1; i <= dt.Columns.Count; i++)
                            {
                                tb.Cell(1, i).Range.Text = dt.Columns[i - 1].ColumnName;
                            }
                            for (int k = 1; k <= dt.Rows.Count; k++)
                            {
                                //模板上默認有2行了，添加行數
                                if (k <= dt.Rows.Count - 2)
                                {
                                    tb.Rows.Add(ref beforerow);
                                }
                                for (int i = 1; i <= dt.Columns.Count; i++)
                                {
                                    tb.Cell(k + 1, i).Range.Text = dt.Rows[k - 1][dt.Columns[i - 1].ColumnName].ToString();
                                }
                            }
                        }
                        #endregion

sectionWordTools.WordReplace("Abc000", key.NewAnalyteName, true, wd);

                        #region 處理備注
                        string notStr = GetCurrentReassayReason(key.AnalyteID);
                        //notStr = "Reasons for Reassay:/r1). Confirmation Assay/r2). ALQ/rReasons for Reported Conc.:/r1). The only valid result is reported./r20. Reassay results selected according to procedure defined in study protocol";

                        sectionWordTools.WordReplace("Repeat analysis Tipis", notStr, true, wd);
                        #endregion
                        //根據內容調整表格
                        //tb.AutoFitBehavior(Microsoft.Office.Interop.Word.WdAutoFitBehavior.wdAutoFitContent);
                        //根據窗口調整表格
                        //tb.AutoFitBehavior(Microsoft.Office.Interop.Word.WdAutoFitBehavior.wdAutoFitWindow);
                        if (wordTableCount < dicList.Count - 1)
                        {
                            // wa.Selection.TypeParagraph();//回車
                            wd.Paragraphs.Last.Range.Select();
                            wa.Selection.Paste();
                        }
                        wordTableCount++;

                        #region 處理整體格式
                        object lefttopstr = tb.Cell(2, 1).Range.Start;
                        object leftbottomend = tb.Cell(tb.Rows.Count, tb.Columns.Count).Range.End;
                        wd.Range(ref lefttopstr, ref leftbottomend).Select();
                        sectionWordTools.AddBorderNoneLineStyle(wa, false, false, true, true, true, true, true, true);
                        #endregion

}

            }
            catch (Exception ex)
            {
                Tools.RecordErrorList(ex.Message, ex);
            }
        }

上面的代碼就是通過winwordControl控件加載word模板，一行一行向word表格中寫入數據，新的表格通過書簽復制后粘貼，然后繼續一行一行的寫入，數據量在500行以內的時候，速度還是可以接受的，當數據量增大之后，就很慢了

改進后的代碼，主要用到了分頁的思想，將集合或者datatable進行分頁，每頁處理50行數據，處理完了之后保存到臨時文件，釋放掉word資源，再打開臨時文件，接著上次的寫入位置繼續寫入。釋放word資源就是為了解決寫入數據量過大假死報錯的問題。希望對處理word的朋友有幫助

復制代碼代碼如下:

private void LoadSectionWord()
        {
            string filePath = Tools.CreateWordFileDir() + Utils.GetTimeString() + ".doc";
            //讀取模板
            sectionWordTools.ByteConvertWord(sectionWordTools.WordContentByTemplateIDAndTemplateTitle(templateId, sectionTitle), filePath);
            try
            {
                wdC.LoadDocument(filePath);
                Microsoft.Office.Interop.Word.Document wd = (Microsoft.Office.Interop.Word.Document)wdC.document;
                Microsoft.Office.Interop.Word.Application wa = wd.Application;
                sectionWordTools.GotoBookMark(wa, "TimeConcentrationData");
                wa.Selection.Copy();//復制模板第一個table
                string assayLLOQ = ComputerOfPostText.ComputerPostText.GetStudyAssayLLOQResults(this.studyId);
                sectionWordTools.WordReplace("0.0 ng/mL for Abc000", assayLLOQ, true, wd);

                //獲得分組，只有分組了的才統計，默認已經分好組，組可以調整
                List<string> groupList = commonBLL.GetAnalyteGroupList(this.reportId, COMMON_TYPE);
                List<List<AnalyteReNameEntity>> analyteGroupList = new List<List<AnalyteReNameEntity>>();

                #region 確定Table分組的數量
                foreach (string group in groupList)
                {
                    List<AnalyteReNameEntity> currentGroupList = commonBLL.GetAnalyteReNameList(this.reportId, this.studyId, group);
                    if (currentGroupList.Count > 0)
                    {
                        analyteGroupList.Add(currentGroupList);
                    }
                }
                #endregion

                if (analyteGroupList.Count > 0)
                {
                    int wordTableCount = 0;
                    foreach (List<AnalyteReNameEntity> currentGroupList in analyteGroupList)
                    {
                        //第一個WordTable
                        Microsoft.Office.Interop.Word.Table tb = wd.Tables[wd.Tables.Count];
                        string key = globalBLL.AnalyteAppendString(currentGroupList);
                        //需要寫入word的結果集
                        DataTable dt = new DataTable();

                        #region 構造word中的列頭
                        foreach (ReportColumnsEntity rc in rceList)
                        {
                            dt.Columns.Add(rc.ReportText, typeof(string));
                        }

                        foreach (AnalyteReNameEntity ar in currentGroupList)
                        {
                            dt.Columns.Add(ar.NewAnalyteName + " Concentration (ng/mL)", typeof(string));
                        }
                        dt.Columns.Add("Comments", typeof(string));
                        #endregion

                        #region 構造Word中的行的DataTable
                        for (int i = 0; i < currentGroupList.Count; i++)
                        {
                            DataRow[] rows = dtResult.Select(string.Format(" analyteid={0}", currentGroupList[i].AnalyteID));
                            if (i == 0)
                            {
                                #region i=0,填充包括列頭
                                for (int k = 0; k < rows.Length; k++)
                                {
                                    string conc = Utils.EffectiveNumber(rows[k]["concentration"].ToString(), "3");
                                    DataRow dr = dt.NewRow();
                                    foreach (ReportColumnsEntity rc in rceList)
                                    {
                                        if (rc.WatsonText.Equals("concentration"))
                                        {
                                            dr[rc.ReportText] = Utils.EffectiveNumber(rows[k][rc.WatsonText].ToString(), "3");
                                        }
                                        else
                                        {
                                            dr[rc.ReportText] = rows[k][rc.WatsonText].ToString();
                                        }

                                    }
                                    dr["Comments"] = "NA";
                                    dr[currentGroupList[i].NewAnalyteName + " Concentration (ng/mL)"] = conc;

                                    dt.Rows.Add(dr);
                                }
                                #endregion
                            }
                            else
                            {
                                for (int k = 0; k < rows.Length; k++)
                                {
                                    string conc = Utils.EffectiveNumber(rows[k]["concentration"].ToString(), "3");
                                    //對分析物濃度列重新賦值
                                    dt.Rows[k][currentGroupList[i].NewAnalyteName + " Concentration (ng/mL)"] = conc;
                                }
                            }

                        }
                        DataTable dtTemp = dt.Copy();
                        DataView dv = dt.DefaultView;
                        dv.Sort = "Subject ID";
                        dtTemp = dv.ToTable();
                        dt = dtTemp;
                        #endregion

                        #region 填充值
                        if (dt.Rows.Count > 0)
                        {

                            #region 根據列合并拆分單元格
                            object wordColumnsCount = dt.Columns.Count;
                            object rownum = 1;
                            for (int k = 0; k < 5; k++)
                            {
                                for (int i = 1; i < tb.Columns.Count; i++)
                                {

                                    tb.Cell(k, i).Range.Text = " ";
                                }
                            }
                            //先合并，再拆分 Selection.Cells.Merge
                            for (int i = 1; i <= 4; i++)
                            {
                                object start = tb.Cell(i, 1).Range.Start;
                                object end = tb.Cell(i, 6).Range.End;
                                wd.Range(ref start, ref end).Select();
                                wa.Selection.Cells.Merge();
                            }
                            for (int i = 1; i < 5; i++)
                            {
                                tb.Cell(i, 1).Split(ref rownum, ref wordColumnsCount);
                            }
                            #endregion

                            //關閉屏幕更新
                            wa.ScreenUpdating = false;
                            #region 表頭的填充
                            //表頭填充
                            for (int i = 1; i <= dt.Columns.Count; i++)
                            {
                                tb.Cell(1, i).Range.Text = dt.Columns[i - 1].ColumnName;
                            }
                            #endregion

#region 填充值

                            #region 分頁
                            object missing = System.Reflection.Missing.Value;
                            object saveOption = Microsoft.Office.Interop.Word.WdSaveOptions.wdDoNotSaveChanges; //解決normal.dot問題
                            object beforerow = tb.Rows[3];
                            int dtRowCounts = dt.Rows.Count;//10;//
                            int columnCount = dt.Columns.Count;
                            int pageSize = 50;
                            //分頁的總頁數
                            int totalPages = DataUtils.GetPageCounts(dtRowCounts, pageSize);
                            for (int index = 1; index <= totalPages; index++)
                            {
                                tb = wd.Tables[wd.Tables.Count];
                                beforerow = tb.Rows[3 + (index - 1) * pageSize];
                                DataTable pageDt = DataUtils.GetPagedTable(dt, index, pageSize);

                                #region 添加行和數據
                                for (int k = 2; k < pageDt.Rows.Count + 2; k++)
                                {

                                    //模板上已經有三行了，最后一頁少添加3行
                                    if (index.Equals(totalPages))
                                    {
                                        if (k < pageDt.Rows.Count - 3)
                                        {
                                            tb.Rows.Add(ref beforerow);
                                        }

                                    }
                                    else
                                    {
                                        tb.Rows.Add(ref beforerow);
                                    }
                                    //添加行的同時填充單元格的值，減少一次全循環
                                    for (int i = 1; i <= pageDt.Columns.Count; i++)
                                    {
                                        tb.Cell(k + (index - 1) * pageSize, i).Range.Text = pageDt.Rows[k - 2][pageDt.Columns[i - 1].ColumnName].ToString();
                                    }
                                }
                                #endregion
                                //填充完PageSize條數據，先保存再重新加載填充
                                object savePath = filePath;
                                wd.SaveAs(ref   savePath, ref   missing,
                ref   missing, ref   missing, ref   missing, ref   missing,
                ref   missing, ref   missing, ref   missing, ref   missing, ref   missing, ref missing, ref missing, ref missing, ref missing, ref missing);
                                wa.ScreenUpdating = true;
                                wd = null;
                                wa = null;
                                Thread.Sleep(10);
                                //重新加載
                                wdC.LoadDocument(filePath);
                                wd = (Microsoft.Office.Interop.Word.Document)wdC.document;
                                wa = wd.Application;
                                wa.ScreenUpdating = false;
                            }
                            #endregion

                            #endregion
                            //打開屏幕更新
                            //wa.ActiveDocument.ActiveWindow.WindowState = Microsoft.Office.Interop.Word.WdWindowState.wdWindowStateMaximize;
                            wa.ScreenUpdating = true;
                        }

#endregion

                        #region Paste Table
                        sectionWordTools.WordReplace("Abc000", key, true, wd);
                        tb = wd.Tables[wd.Tables.Count];
                        //根據內容調整表格
                        tb.AutoFitBehavior(Microsoft.Office.Interop.Word.WdAutoFitBehavior.wdAutoFitContent);
                        //根據窗口調整表格
                        tb.AutoFitBehavior(Microsoft.Office.Interop.Word.WdAutoFitBehavior.wdAutoFitWindow);
                        if (wordTableCount < analyteGroupList.Count - 1)
                        {
                            // wa.Selection.TypeParagraph();//回車
                            wd.Paragraphs.Last.Range.Select();
                            wa.Selection.Paste();
                        }
                        wordTableCount++;
                        #endregion
                    }
                }
            }
            catch (Exception ex)
            {
                Tools.RecordErrorList(ex.Message, ex);
            }
        }