/// <summary> /// !!! 正式开始提取数据 /// </summary> /// <param name="DataTableInWord">进行数据提取的word中的表格Table对象</param> /// <remarks>提取的基本思路:已有一个doc对象,并对其中的一个测点进行提取。</remarks> private void ExportData(Microsoft.Office.Interop.Word.Table DataTableInWord) { try { Microsoft.Office.Interop.Word.Range rgTable = DataTableInWord.Range; rgTable.Copy(); this.F_BufferSheet.UsedRange.Clear(); this.F_BufferSheet.Activate(); this.F_BufferSheet.UsedRange.Clear(); this.F_BufferSheet.Cells[1, 1].select(); this.F_BufferSheet.Paste(); //此文档中的每一个要提取的测点。 foreach (Microsoft.Office.Interop.Excel.Worksheet sheetExportTo in this.F_DicPointsInfo.Keys) { PointsInfoForExport PointInfo = this.F_DicPointsInfo.Item(sheetExportTo); // ------------ 从暂存工作表中将测点标签与对应的数据提取到目标工作表中 ---------- //搜索得到的第一个结果的range对象,如果没有搜索到,则返回nothing。 Microsoft.Office.Interop.Excel.Range rgNextPoint = default(Microsoft.Office.Interop.Excel.Range); rgNextPoint = this.F_BufferSheet.UsedRange.Find(What: ref PointInfo.PointTag, SearchOrder: ref PointInfo.SearchOrder, LookAt: ref XlLookAt.xlPart, LookIn: ref XlFindLookIn.xlValues, SearchDirection: ref XlSearchDirection.xlNext, MatchCase: false); if (rgNextPoint != null) { //当搜索到指定查找区域的末尾时,此方法将绕回到区域的开始位置继续搜索。 //发生绕回后,要停止搜索,可保存第一个找到的单元格地址,然后测试后面找到的每个单元格地址是否与其相同。 string firstAddress = rgNextPoint.Address; //提取数据并写入最终的工作表 do { sheetExportTo.Cells[PointInfo.RowNumToBeWritten, PointInfo.ColNumToBeWritten].Value = rgNextPoint.Value; sheetExportTo.Cells[PointInfo.RowNumToBeWritten, PointInfo.ColNumToBeWritten + 1].Value = rgNextPoint PointInfo.Offset[0, PointInfo.Offset].Value; PointInfo.RowNumToBeWritten++; rgNextPoint = this.F_BufferSheet.UsedRange.FindNext(rgNextPoint); } while (rgNextPoint != null && string.Compare(rgNextPoint.Address, firstAddress) != 0); } } } catch (Exception) { //数据提取出错 string strError = ""; this.F_ErrorList.Add(strError); } finally { } }
//在后台线程中执行操作 /// <summary> /// 在后台线程中执行操作 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> /// <remarks></remarks> public void StartToDoWork(object sender, System.ComponentModel.DoWorkEventArgs e) { //定义初始变量 ExportToWorksheet ExportToWorksheet = (ExportToWorksheet)e.Argument; string[] arrDocsPath = ExportToWorksheet.arrDocsPath; Microsoft.Office.Interop.Excel.Workbook WorkBook_ExportedTo = ExportToWorksheet.WorkBook_ExportedTo; bool blnParseDateFromFilePath = ExportToWorksheet.ParseDateFromFilePath; Microsoft.Office.Interop.Excel.Worksheet bufferSheet = ExportToWorksheet.BufferSheet; //一共要处理的工作表数(工作簿个数*每个工作簿中提取的工作表数),用来显示进度条的长度 int Count_Documents = this.ListBoxDocuments.Items.Count; // int percent = 0; //每一份数据所对应的进度条长度 float unit = 0; unit = (float)((double)(this.ProgressBar1.Maximum - this.ProgressBar1.Minimum) / Count_Documents); //报告进度 this.BkgWk_Extract.ReportProgress(percent, ""); //开始提取数据 for (short iDoc = 0; iDoc <= Count_Documents - 1; iDoc++) { string strDocPath = arrDocsPath[iDoc]; Microsoft.Office.Interop.Word.Document Doc = null; try { //下面有可能会出现文档打开出错 Doc = this.F_WordApp.Documents.Open(FileName: strDocPath, ReadOnly: true, Visible: false); // Microsoft.Office.Interop.Word.Table myTable = default(Microsoft.Office.Interop.Word.Table); short CountTables = (short)Doc.Tables.Count; if (CountTables > 0) { for (short iTable = 1; iTable <= CountTables; iTable++) { myTable = Doc.Tables[iTable]; // ------------- 正式开始提取数据 ------------- ExportData(DataTableInWord: myTable); // ------------- 正式开始提取数据 ------------- this.BkgWk_Extract.ReportProgress(System.Convert.ToInt32((iDoc + (double)iTable / CountTables) * unit), "正在提取文档:" + strDocPath); } //文档中的下一个表格Table对象 } } catch (Exception ex) { //文档打开出错 string strError = "Document文档:" + Doc.FullName + " 打开时出错。 " + "\r\n" + ex.Message; this.F_ErrorList.Add(strError); } finally { if (Doc != null) //说明工作簿顺利打开 { Doc.Close(SaveChanges: false); } this.BkgWk_Extract.ReportProgress(System.Convert.ToInt32((iDoc + 1) * unit), "正在提取文档:" + strDocPath); } //更新下一个文档的数据在对应的Excel工作表中所保存的列号 //以及表头信息 for (short iSheet = 0; iSheet <= F_DicPointsInfo.Count - 1; iSheet++) { Microsoft.Office.Interop.Excel.Worksheet sht = F_DicPointsInfo.Keys[iSheet] as Worksheet; PointsInfoForExport pointinfo = this.F_DicPointsInfo.Values(iSheet); //此工作簿所对应的表头的数据:工作簿的名称或者是工作簿中包含的日期信息 string ColumnTitle = GetColumnTitle(strDocPath, blnParseDateFromFilePath); sht.Cells[cstRowNum_ColumnTitle, pointinfo.ColNumToBeWritten].Value = ColumnTitle; // pointinfo.ColNumToBeWritten += pointinfo.ColumnsCountToBeAdd; pointinfo.RowNumToBeWritten = cstRowNum_FirstData; } } //Next Document下一个文档 }
private Dictionary <Worksheet, PointsInfoForExport> SearchPointsInfo(Microsoft.Office.Interop.Excel.Workbook wkbk) { Dictionary <Worksheet, PointsInfoForExport> listRangeInfo = new Dictionary <Worksheet, PointsInfoForExport>(); string strTestRange = ""; // //记录DataGridView控件中所有数据的数组 try { int RowsCount = this.MyDataGridView1.Rows.Count; for (int RowIndex = 0; RowIndex <= RowsCount - 2; RowIndex++) { DataGridViewRow RowObject = MyDataGridView1.Rows[RowIndex]; //要进行提取的测点标签 string strPointName = RowObject.Cells[0].Value.ToString(); //设置与测点标签对应的excel工作表对象,并为其命名 Worksheet sht = null; try { sht = wkbk.Worksheets.Item[strPointName] as Worksheet; } catch (Exception) { //表示工作簿中没有这一工作表 sht = wkbk.Worksheets.Add() as Worksheet; //为新创建的工作表命名 bool blnNameOk = false; var shtName = strPointName; do { try { sht.Name = shtName; blnNameOk = true; } catch (Exception) { //表示此名称已经在工作簿中被使用了 shtName = shtName + "1"; } } while (!blnNameOk); } //测点数据距离测点标签的偏移位置 byte Offset = byte.Parse(RowObject.Cells[1].Value.ToString()); //搜索的方向:按行或者是按列 Microsoft.Office.Interop.Excel.XlSearchOrder SearchDirection = default(Microsoft.Office.Interop.Excel.XlSearchOrder); DataGridViewComboBoxCell comboBox = (DataGridViewComboBoxCell)(RowObject.Cells[2]); if ((string)comboBox.Value == "按行") { SearchDirection = XlSearchOrder.xlByRows; } else if ((string)comboBox.Value == "按列") { SearchDirection = XlSearchOrder.xlByColumns; } else { MessageBox.Show("请先输入搜索方向", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return(default(Dictionary <Worksheet, PointsInfoForExport>)); } PointsInfoForExport RangeInfo = new PointsInfoForExport(strPointName, Offset, SearchDirection); listRangeInfo.Add(sht, RangeInfo); } } catch (Exception) { MessageBox.Show("数据的格式出错 : " + "\r\n" + strTestRange + ",请重新输入", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return(default(Dictionary <Worksheet, PointsInfoForExport>)); } return(listRangeInfo); }