//在后台线程中执行操作
/// <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.BackgroundWorker1.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: ref strDocPath, [ReadOnly] : true, Visible: false);
//在后台线程中执行操作
public void BackgroundWorker1_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
{
//定义初始变量
ExportToWorksheet ExportToWorksheet = (ExportToWorksheet)e.Argument;
string[] arrWkbk = ExportToWorksheet.arrWkbk;
List <ExportToWorksheet.RangeInfoForExport> listRangeInfo = ExportToWorksheet.listRangeInfo;
Microsoft.Office.Interop.Excel.Workbook WorkBook_ExportedTo = ExportToWorksheet.WorkBook_ExportedTo;
bool blnParseDateFromFilePath = ExportToWorksheet.ParseDateFromFilePath;
//一共要处理的工作表数(工作簿个数*每个工作簿中提取的工作表数)，用来显示进度条的长度
int Count_Workbooks = this.ListBoxDocuments.Items.Count;
int Count_RangesInOneWkbk = System.Convert.ToInt32(listRangeInfo.Count);
int Count_AllRanges = Count_Workbooks * Count_RangesInOneWkbk;
//
int percent = 0;
float unit = 0;
unit = (float)((double)(this.ProgressBar1.Maximum - this.ProgressBar1.Minimum) / Count_AllRanges);
//报告进度
this.BackgroundWorker1.ReportProgress(percent, "");
//开始提取数据
bool blnRangeFormatValidated = false;
for (short iWkbk = 0; iWkbk <= Count_Workbooks - 1; iWkbk++)
{
string strWkbkPath = arrWkbk[iWkbk];
Workbook wkbk = null;
try
{
//下面有可能会出现工作簿打开出错
wkbk = this.F_ExcelApp.Workbooks.Open(Filename: ref strWkbkPath, UpdateLinks: false, [ReadOnly] : true);
/// <summary>
/// 开始输出数据
/// </summary>
/// <remarks></remarks>
protected override void StartExportData()
{
// ------------- 提取每一个工作表与Range范围的格式 -------------
List <ExportToWorksheet.RangeInfoForExport> listRangeInfo = new List <ExportToWorksheet.RangeInfoForExport>();
//
string strTestRange = "";
//
//记录DataGridView控件中所有数据的数组
try
{
int RowsCount = MyDataGridView1.Rows.Count;
for (int RowIndex = 0; RowIndex <= RowsCount - 2; RowIndex++)
{
DataGridViewRow RowObject = MyDataGridView1.Rows[RowIndex];
//获取对应的Worksheet对象
string strSheetName = RowObject.Cells[0].Value.ToString();
Microsoft.Office.Interop.Excel.Worksheet ExportedSheet = GetExactWorksheet(F_WorkBook_ExportedTo, listSheetNameInWkbk, strSheetName);
//检查Range对象的格式是否正确()
strTestRange = RowObject.Cells[1].Value.ToString();
Range testRange = ExportedSheet.Range(strTestRange); //这一步可能出错：Range的格式不规范
//
int columnsCount = 0;
foreach (Range a in testRange.Areas)
{
//如果想引用相交区域（公共区域)，可以在多个区域间添加空格“ ”： 如Range("B1:B10 A4:D6 ").Select() '选中多个单元格区域的交集
columnsCount += a.Columns.Count;
}
ExportToWorksheet.RangeInfoForExport RangeInfo = new ExportToWorksheet.RangeInfoForExport(ExportedSheet, strTestRange, columnsCount);
listRangeInfo.Add(RangeInfo);
}
}
catch (Exception)
{
MessageBox.Show("定义区域范围的格式出错，出错的格式为 : " + "\r\n"
+ strTestRange + "，请重新输入", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
return;
}
// -----------------------------------
//是否要分析出提取数据的工作簿中的日期数据
bool blnParseDateFromFilePath = false;
if (this.ChkboxParseDate.Checked)
{
blnParseDateFromFilePath = true;
}
//不允许再更改提取日期的正则表达式
this.ChkboxParseDate.Checked = false;
//开始提取数据
ExportToWorksheet Export = new ExportToWorksheet(F_WorkBook_ExportedTo, arrDocPaths, listRangeInfo, blnParseDateFromFilePath);
this.BackgroundWorker1.RunWorkerAsync(Export);
}
/// <summary>
/// 开始输出数据
/// </summary>
/// <remarks></remarks>
protected override void StartExportData()
{
this.blnIsBeingExtracting = true;
//打开进行数据提取的Word程序
if (this.F_WordApp == null)
{
this.F_WordApp = new Microsoft.Office.Interop.Word.Application();
this.F_WordApp.DocumentBeforeClose += this.F_WordApp_DocumentBeforeClose;
this.F_WordApp.Visible = false;
}
List <string> listPointsTag = new List <string>();
this.F_BufferSheet = this.F_WorkBook_ExportedTo.Worksheets.Add();
//
// ------------- 提取每一个工作表与Range范围的格式 -------------并返回DataGridView中的所有数据
this.F_DicPointsInfo = SearchPointsInfo(this.F_WorkBook_ExportedTo);
if (F_DicPointsInfo == null)
{
return;
}
// -----------------------------------
//是否要分析出提取数据的工作簿中的日期数据
bool blnParseDateFromFilePath = false;
if (this.ChkboxParseDate.Checked)
{
blnParseDateFromFilePath = true;
}
//不允许再更改提取日期的正则表达式
this.ChkboxParseDate.Checked = false;
// ---------------------- 开始提取数据 ---------------------
ExportToWorksheet Export = new ExportToWorksheet(F_WorkBook_ExportedTo, arrDocPaths, blnParseDateFromFilePath, this.F_BufferSheet);
this.BackgroundWorker1.RunWorkerAsync(Export);
}
//在后台线程中执行操作
/// <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下一个文档
}
/// <summary>
/// 开始输出数据
/// </summary>
/// <remarks></remarks>
public void btnExport_Click(object sender, EventArgs e)
{
if (!this.BkgWk_Extract.IsBusy)
{
//
this.blnIsBeingExtracting = true;
//打开进行数据操作的Excel程序
if (this.F_ExcelApp == null)
{
this.F_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
this.F_ExcelApp.WorkbookBeforeClose += this.F_ExcelApp_WorkbookBeforeClose;
this.F_ExcelApp.DisplayAlerts = false;
//一般情况下，默认是隐藏的，如果原来是打开的，则手动隐藏
this.F_ExcelApp.Visible = false;
}
//打开进行数据提取的Word程序
if (this.F_WordApp == null)
{
this.F_WordApp = new Microsoft.Office.Interop.Word.Application();
this.F_WordApp.DocumentBeforeClose += this.F_WordApp_DocumentBeforeClose;
this.F_WordApp.Visible = false;
}
//初始化错误列表
this.F_ErrorList = new List <string>();
//
string strWorkBook_ExportedTo = this.txtbxSavePath.Text;
//---------- 打开保存数据的工作簿，并提取其中的所有工作表 ----------------
List <string> listPointsTag = new List <string>();
List <string> listSheetNameInWkbk = new List <string>();
try
{
if (File.Exists(strWorkBook_ExportedTo))
{
F_WorkBook_ExportedTo = this.F_ExcelApp.Workbooks.Open(Filename: strWorkBook_ExportedTo, UpdateLinks: false, ReadOnly: false);
}
else
{
F_WorkBook_ExportedTo = this.F_ExcelApp.Workbooks.Add();
F_WorkBook_ExportedTo.SaveAs(Filename: strWorkBook_ExportedTo, FileFormat:
Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, CreateBackup:
false);
}
this.F_BufferSheet = this.F_WorkBook_ExportedTo.Worksheets.Add() as Worksheet;
//
object AllSheets = F_WorkBook_ExportedTo.Worksheets;
foreach (Worksheet shtInWorkbook in (IEnumerable)AllSheets)
{
listSheetNameInWkbk.Add(shtInWorkbook.Name);
}
}
catch (Exception)
{
MessageBox.Show("保存数据的Word文档打开出错，请检查或者关闭此文档。", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// ------------- 提取每一个工作表与Range范围的格式 -------------并返回DataGridView中的所有数据
this.F_DicPointsInfo = SearchPointsInfo(this.F_WorkBook_ExportedTo);
if (F_DicPointsInfo == null)
{
return;
}
// -----------进行数据提取的Document对象数组------------------------
System.Windows.Forms.ListBox.ObjectCollection DocItems = this.ListBoxDocuments.Items;
int DocsCount = DocItems.Count;
//记录DataGridView控件中所有数据的数组
string[] arrDocsPath = new string[DocsCount - 1 + 1];
for (int i = 0; i <= DocsCount - 1; i++)
{
arrDocsPath[i] = DocItems[i].ToString();
}
// -----------------------------------
//是否要分析出提取数据的工作簿中的日期数据
bool blnParseDateFromFilePath = false;
if (this.ChkboxParseDate.Checked)
{
blnParseDateFromFilePath = true;
}
//不允许再更改提取日期的正则表达式
this.ChkboxParseDate.Checked = false;
// ---------------------- 开始提取数据 ---------------------
ExportToWorksheet Export = new ExportToWorksheet(F_WorkBook_ExportedTo, arrDocsPath, blnParseDateFromFilePath, this.F_BufferSheet);
this.BkgWk_Extract.RunWorkerAsync(Export);
}
}