如何使用Excel RowIndex将特定的Excel行插入到DataGridView中 [英] How to insert specific excel row into datagridview using excel rowindex
问题描述
我正在使用以下方法使用小型c＃应用程序
在excel 工作簿中执行搜索
public void SearchExcelFiles（string FilePath）
{
string ConnStr = @ Provider = Microsoft.ACE.OLEDB.12.0; Data Source = + FilePath +;扩展属性= \ Excel 12.0 Xml; HDR = YES\;;
Microsoft.Office.Interop.Excel.Application oXL =新的Microsoft.Office.Interop.Excel.Application（）;
Microsoft.Office.Interop.Excel.Workbook oWB；
Microsoft.Office.Interop.Excel.Range currentFind = null;
Microsoft.Office.Interop.Excel.Range firstFind = null;
Thread.CurrentThread.CurrentCulture = new CultureInfo（ en-US）;
if（！GB_Search.Controls.OfType< TextBox>（）。Any（x =>！string.IsNullOrEmpty（x.Text））））
{
MessageBox。 Show（输入要搜索的文本）;
的回报；
}
oWB = oXL.Workbooks.Open（FilePath，//-文件名或FilePath
0，//-对象UpdateLinks
true， // ----对象ReadOnly
Type.Missing，// 5 // ---对象格式
，//-对象密码
，//- -object WriteResPassword
false，//-对象ReadOnlyRecommend
Excel.XlPlatform.xlWindows，//-object Origin
，//-object Delimiter
true，//-对象可编辑
false，//-对象通知
0，//-对象转换器
true，//-对象AddToMru
false，//-对象局部
false）； //-对象CorruptLoad;
//在每个Excel工作表中指定搜索范围
//Excel.Range oRng = oXL.get_Range（ A1， XFD1048576）;
Excel.Range xlCell =
xlWSheet.UsedRange.SpecialCells（Excel.XlCellType.xlCellTypeLastCell，Type.Missing）;
Excel.Range oRng = xlWSheet.get_Range（ A1，
xlWSheet.UsedRange.SpecialCells（Excel.XlCellType.xlCellTypeLastCell，Type.Missing））;
//循环搜索所有excel工作表（工作簿）
foreach（oWB.Worksheets中的Excel.Worksheet SheetID）
{//在所有文本框中循环搜索是否是否存在
foreach（GB_Search.Controls.OfType< TextBox>（）中的TextBox续）
{
if（！string.IsNullOrEmpty（cont.Text））
{
currentFind = oRng.Find（cont.Text，
Type.Missing，
Excel.XlFindLookIn.xlValues，
Excel.XlLookAt.xlPart，
Excel.XlSearchOrder.xlByRows ，
Excel.XlSearchDirection.xlNext，
false，
Type.Missing，
Type.Missing）；
while（currentFind！= null）
{
//跟踪找到的第一个范围。
if（firstFind == null）
{
firstFind = currentFind;
}
//如果当前地址与起始地址相同，则停止搜索
，否则if（currentFind.get_Address（true，true，Excel.XlReferenceStyle.xlR1C1，Type.Missing，Type.Missing ）== firstFind.get_Address（true，true，Excel.XlReferenceStyle.xlR1C1，Type.Missing，Type.Missing））
{
break;
}
//继续搜索下一个值
currentFind = oRng.FindNext（currentFind）;
MessageBox.Show（currentFind.get_Address（true，true，Excel.XlReferenceStyle.xlR1C1，Type.Missing，Type.Missing））; //用于测试
字符串CurrentAddress = currentFind.get_Address（true，true，Excel.XlReferenceStyle.xlR1C1，Type.Missing，Type.Missing）;
AddToDataGridView（CurrentAddress，SheetID.Name，ConnStr）; //找到匹配项后，将获取完整的行详细信息并将其填充到datagridview
}
//在查找下一个文本框值之前的空范围
firstFind = null;
currentFind = null;
}
//MessageBox.Show(\"Done control ... + cont.Name）; //〜测试目的
}
//MessageBox.Show(\"Done...sheet）; //〜测试目的
}
//MessageBox.Show(\"Done...wb）; //〜测试目的
oWB.Close（false，Type.Missing，Type.Missing）;
oWB =空；
oXL.Quit（）;
}
现在找到匹配项时，我就调用此方法 AddToDataGridView （）应该执行以下操作：
- 使用where子句（以某种方式）获取完整的行详细信息附加图像上显示的
rowid - 填充此行并将其添加到
datagridview
欢迎使用另一种方法
public void AddToDataGridView（字符串CurrentAddress，字符串SheetName，字符串ConnStr）
{
cmdtxt = @ select * from [ + SheetName + $ + CurrentAddress +]其中？??? ;
MessageBox.Show（cmdtxt）; //使用（OleDbConnection conn = new OleDbConnection（ConnStr））测试目的
{
OleDbDataAdapter DA = new OleDbDataAdapter（cmdtxt，conn）;
DA.Fill（dt）;
DGV_Data.DataSource = dt;
conn.Close（）;
}
}
右侧的文本框用作日志输出，以便在需要时进行测试。搜索结果返回后，用户可以使用组合框从每个工作表中选择结果。
该代码对给定工作簿中的字符串进行简单搜索。 。当用户在搜索文本框中键入内容并单击搜索按钮时，代码将打开给定的工作簿，在每个工作表中搜索目标字符串，并创建一个 DataTable 来保存成功找到的行。创建一个 DataSet 来保存每个工作表创建的不同的 DataTable ，因为每个工作表可能具有不同的列结构。
下面是更详细的说明，但是，我必须评论可能出现的一些Excel问题。
当前，代码将搜索目标字符串的任何子字符串。例如，如果您搜索 a，则返回的结果将包含其中包含 a的任何字符串... cat， bat等。您可能希望优化此操作。 Excel的查找方法可能不是最佳选择。 （以下更多内容）
使用Excels UsedRange 属性时，应注意，这可能会返回将出现为空。在几乎所有情况下，这种情况都会在单元格中进行某些格式化，但是该单元格为空并且不包含任何数据…… UsedRange 可能会因为格式而将该单元格包含在范围内。请注意这一点，当它返回这些显然的空单元格时，不要太快地声称 UsedRange 是有缺陷的。我在另一个答案中对此有解决方案。
最后，关于Excel和互操作…当前代码使用与您发布的代码相同的Excel 查找方法。如果数据集不是很大，这应该很好，但是，如果数据量很大（具有许多列和行的大型Excel工作表），则可能会导致性能问题。这是一个Excel和互操作性问题。在循环中使用时，诸如 UsedRange ， Find 之类的调用方法非常昂贵。重要的是，如果工作表很大，则可能需要考虑使用互操作而不使用其他实现。我了解有更好（免费）的第三方Excel库。
话虽如此，下面是上面表格的代码。
以下形式的全局变量：
A DataSet ds 存放 DataTables ；不言自明的路径 DefaultDirectory ; Excel 工作簿进行搜索，最后是Excel应用程序本身。加载后，将启动Excel应用程序，并等待用户选择工作簿。
DataSet ds = new DataSet（）;
string DefaultDirectory = @ D：\Test\;
工作簿工作簿；
Excel.Application excelApp;
public Form3（）{
InitializeComponent（）;
}
私人无效Form3_Load（对象发送者，EventArgs e）{
excelApp = new Excel.Application（）;
}
打开/选择工作簿按钮单击以选择一个工作簿，并使用 OpenFileDialog 允许用户选择要搜索的工作簿。一旦选定，全局变量 workbook 将打开并可供其他方法使用。
private void btnSelectWorkbook_Click（object sender，EventArgs e）{
DGV_Data.DataSource = null;
tbSearch.Text =;
cbWorksheetNames.Items.Clear（）;
textLog.Text =;
lblWorkbookName.Text =;
OpenFileDialog ofd = new OpenFileDialog（）;
ofd.Filter = Excel文件| * .xls; *。xlsx; *。xlsm;
ofd.InitialDirectory = DefaultDirectory;
if（ofd.ShowDialog（）== DialogResult.OK）{
string fileToOpen = ofd.FileName;
workbook = excelApp.Workbooks.Open（fileToOpen）;
lblWorkbookName.Text =工作簿： + fileToOpen;
}
}
在用户选择要搜索的工作簿并具有键入一些要搜索的目标文本…用户单击搜索按钮。首先，进行两次检查以确保要搜索一些文本，还检查是否有要打开的工作簿。如果没有要搜索的文本或工作簿没有打开，请向用户显示一条消息。并返回而不进行搜索。
如果有打开的工作簿和要搜索的文本，则全局 DataSet ds 被初始化，然后每个工作表的 DataTable 填充 ds 调用 GetRowsFromSearchStringFromAllWorksheets 。 。填充 DataSet 之后（更多信息）， DataGridView 的 DataSource 设置为 DatatSet 中的第一个 DataTable ；
private void btnSearch_Click（对象发送者，EventArgs e） {
if（string.IsNullOrEmpty（tbSearch.Text））{
MessageBox.Show（输入要搜索的文本）;
的回报；
}
if（workbook == null）{
MessageBox.Show（选择工作簿）;
的回报；
}
ds = new DataSet（）;
try {
ds = GetRowsFromSearchStringFromAllWorksheets（workbook，tbSearch.Text）;
DGV_Data.DataSource = ds.Tables [0];
FillComboBoxWithSheetNames（）;
cbWorksheetNames.SelectedIndex = 0;
gbResults.Text =‘’的搜索结果+ tbSearch.Text +’;
tbSearch.Text =;
}
catch（异常例外）{
MessageBox.Show（ Error： + ex.Message）;
}
}
GetRowsFromSearchStringFromAllWorksheets 方法（可能需要一个更好的名称）没有做太多。它循环遍历工作簿中的每个工作表，从工作表中创建 DataTable ，通过调用 FillTableWithSearchResults 填充数据表方法（如下），最后将 DataTable 添加到 DataSet 。 GetDTColumnsFromWorksheet 方法（如下）根据工作表中的标题行创建 DataTable 。工作表的第一行假定为标题行，并将其用作 DataTable 的列名。注意：当前，如果搜索没有结果返回，则将工作表 DataTable 仍添加到 DataSet 中。如果您只想添加包含结果的工作表，则我留下了调试代码进行修改。
private DataSet GetRowsFromSearchStringFromAllWorksheets（工作簿wb，字符串searchString）{
DataSet ds = new DataSet（）;
foreach（wb.Worksheets中的工作表currentWorksheet）{
System.Data.DataTable currentDT = GetDTColumnsFromWorksheet（currentWorksheet）;
//textLog.Text + =在工作表中搜索 + currentWorksheet.Name + Environment.NewLine;
FillTableWithSearchResults（currentWorksheet.UsedRange，searchString，currentDT）;
if（currentDT.Rows.Count> 0）{
textLog.Text + =在工作表中找到的匹配项 + currentWorksheet.Name + Environment.NewLine;
}
else {
textLog.Text + =在工作表中找不到匹配项 + currentWorksheet.Name + Environment.NewLine;
}
ds.Tables.Add（currentDT）;
}
return ds;
}
GetDTColumnsFromWorksheet 工作表并返回 DataTable 。返回的数据表将具有与工作表中 UsedRange所返回的列数相同的列数。添加了额外的一列，以显示在工作表中找到该单元格的位置。第一栏中的格式为RXXCXX。如果使用范围内列中的单元格没有值，则将使用字符串 ??? XX。
私有System.Data.DataTable GetDTColumnsFromWorksheet（Worksheet ws）{
//假设工作表的第1行包含一个行标题
//我们将使用它来命名 DataTable列
//并假设不存在带有值的剩余单元格
//不一定属于数据表
int missingColumnNameCount = 1;
UsedRange的范围= ws.UsedRange;
int numberOFColumns = usedRange.Columns.Count;
System.Data.DataTable dt =新的System.Data.DataTable（）;
dt.TableName = ws.Name;
string currentColumnHeader =;
范围行1；
//在前面的
中添加一个额外的列//此列将显示找到的项目在工作表中的位置（RXCX）
dt.Columns.Add（ CXRX，typeof（串））;
for（int i = 1; i< = numberOFColumns; i ++）{
row1 = usedRange [1，i];
if（row1.Value2！= null）{
currentColumnHeader = row1.Value2.ToString（）;
}
else {
//如果行没有值，则使用默认名称和索引器，以避免重复的列名
currentColumnHeader = ??? + missingColumnNameCount;
missingColumnNameCount ++;
}
dt.Columns.Add（currentColumnHeader，typeof（string））;
}
return dt;
}
FillTableWithSearchResults 方法需要搜索的范围，要搜索的字符串，最后是将成功搜索添加到的数据表。传入的 DataTable 已经创建并且列已初始化。
我不确定这是否是处理Excel Find / FindNext 方法的最佳方法。因此，我希望我是正确的。第一次在范围上使用查找时，它将返回找到的第一个与搜索内容匹配的单元格。重新调整的范围是找到的第一个项目的单元格地址。据我了解， FindNext 显然会返回NEXT找到的项目。问题是，当它找到LAST项目并搜索下一个项目时，它只是从头开始。因此，循环的停止条件是 NextFind 的单元格地址与第一个 Find 。这将要求我们保存第一个 查找的单元格地址。以下是解决此难题的一种方法。
创建两个范围：一个 startRange 来保存起始的第一个 查找，另一个 currentRange 来保存当前的找到的范围。首先，进行检查以确保要搜索的内容。如果要搜索至少一行，则从 first开始设置 startRange 查找。这是在使用 FindNext 时需要停止的单元格地址。如果找到至少一个项目，那么我们可以搜索下一个项目，并进入 FindNext 循环。只需将 currentRange 设置为 NextFind ，然后从其中添加 startRange 先 查找到数据表，然后最后进入 FindNext 循环，这样它将继续使用 FindNext 并将新行添加到数据表中，直到 currentRange 单元格地址等于 startingRange 单元格地址。这表示 FindNext 已循环回到开头，搜索已完成。 AddExcelRowToDataTable 将找到的行添加到数据表中（如下所示）。
注意：当前此代码允许重复的条目如果在同一行的多个列中找到了搜索的字符串。网格中每个行列中找到的每个搜索字符串都将有一个行条目。示例：如果在第4列，第6列和第8列中搜索了第5行的字符串，则将有一行针对R5C4，R5C6和R5C8。我没有对此进行过滤以删除重复的行。
private void FillTableWithSearchResults（Range usedRange，string searchString，System.Data.DataTable dt ）{
range currentRange;
if（usedRange.Rows.Count> 0）{
范围startRange = usedRange.Find（searchString）;
if（startRange！= null）{
currentRange = usedRange.FindNext（startRange）;
AddExcelRowToDataTable（usedRange，startRange，dt）;
字符串startAddress = startRange.get_Address（true，true，XlReferenceStyle.xlR1C1）;
while（！currentRange.get_Address（true，true，XlReferenceStyle.xlR1C1）.Equals（startAddress））{
AddExcelRowToDataTable（usedRange，currentRange，dt）;
currentRange = usedRange.FindNext（currentRange）;
}
}
}
}
AddExcelRowToDataTable 方法采用一个已使用的范围来获取数据，范围 row 会添加到第三个给定参数 DataTable 。再次有点骇人听闻，将进行检查以确保使用范围内的列数不超过数据表中的列数。获取行索引以指示要使用的范围中的哪一行要添加到数据表中。从给定的 DataTable <$创建一个 DataRow dr c $ c> dt 来确保列模式相同。第一列将是我们之前添加的列，以显示找到的项目的RXXCXX位置列。添加额外的列数据，然后遍历其余各列，将工作表值添加到 DataRow 。添加所有列值后，将 DataRow 添加到 DataTable 。
private void AddExcelRowToDataTable（Range usedRange，Range row，System.Data.DataTable dt）{
if（usedRange.Columns.Count> = dt.Columns.Count-1）{
int rowIndex = GetRowIndexOfFoundItem（row）;
if（rowIndex> = 0）{
DataRow dr = dt.NewRow（）;
//添加CXRX数据
dr [0] = row.get_Address（true，true，XlReferenceStyle.xlR1C1）;
for（int i = 1; i< = usedRange.Columns.Count; i ++）{
dr [i] = usedRange.Cells [rowIndex，i] .Value2;
}
dt.Rows.Add（dr）;
}
}
}
GetRowIndexOfFoundItem 接受一个单元格范围，并从单元格字符串RXXCXX地址返回（int）行索引。
private int GetRowIndexOfFoundItem（Range range）{
// hacky ...该字符串是RXXCX或RXXcXXX或RXXXXCXX的格式。
//我们想要R后面的XXX ...在'C'
上分割字符串//得到RXX..X，然后删除'R'并解析数字
字符串RCaddress = range.get_Address（true，true，XlReferenceStyle.xlR1C1）;
string [] split = RCaddress.Split（’C’）;
RCaddress = split [0] .Remove（0，1）;
int rowIndex = 0;
if（int.TryParse（RCaddress，out rowIndex））{
返回rowIndex;
}
else {
//无效的数字
返回-1；
}
}
在工作区名称后用工作表名称填充组合框的方法搜索已完成。
private void FillComboBoxWithSheetNames（）{
cbWorksheetNames.Items.Clear（）;
foreach（ds.Tables中的System.Data.DataTable dt）{
cbWorksheetNames.Items.Add（dt.TableName）;
}
}
组合框 SelectedIndexChnged 事件被连接起来，并使用组合框选定的索引来确定要在网格中显示哪个 DataTable 。
private void cbWorksheetNames_SelectedIndexChanged（object sender，EventArgs e）{
DGV_Data.DataSource = ds.Tables [cbWorksheetNames.SelectedIndex];
}
最后进行一些资源清理。
private void Form3_FormClosing（对象发送者，FormClosingEventArgs e）{
try {
if（workbook！= null）{
workbook.Close（ ）;
Marshal.ReleaseComObject（workbook）;
}
excelApp.Quit（）;
Marshal.ReleaseComObject（excelApp）;
}
catch（异常例外）{
MessageBox.Show（ Error： + ex.Message）;
}
}
很抱歉，答案很长，希望对您有所帮助
I am performing search within excel workbook using small c# application
using below method
public void SearchExcelFiles(string FilePath)
{
string ConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook oWB;
Microsoft.Office.Interop.Excel.Range currentFind = null;
Microsoft.Office.Interop.Excel.Range firstFind = null;
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
if (!GB_Search.Controls.OfType<TextBox>().Any(x => !string.IsNullOrEmpty(x.Text)))
{
MessageBox.Show("Enter text for search");
return;
}
oWB = oXL.Workbooks.Open(FilePath, //---Filename OR FilePath
0, //---object UpdateLinks
true, //---object ReadOnly
Type.Missing, //5//---object Format
"", //---object Password
"", //---object WriteResPassword
false, //---object ReadOnlyRecommend
Excel.XlPlatform.xlWindows, //---object Origin
"", //---object Delimiter
true, //---object Editable
false, //---object Notify
0, //---object Converter
true, //---object AddToMru
false, //---object Local
false); //---object CorruptLoad;
//specifying searching range within each excel sheet
//Excel.Range oRng = oXL.get_Range("A1", "XFD1048576");
Excel.Range xlCell =
xlWSheet.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range oRng = xlWSheet.get_Range("A1",
xlWSheet.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing));
//loop to search witin all excel sheets (workbook)
foreach (Excel.Worksheet SheetID in oWB.Worksheets)
{ //loop within all textboxs value to search if it is exist
foreach (TextBox cont in GB_Search.Controls.OfType<TextBox>())
{
if (!string.IsNullOrEmpty(cont.Text))
{
currentFind = oRng.Find(cont.Text,
Type.Missing,
Excel.XlFindLookIn.xlValues,
Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows,
Excel.XlSearchDirection.xlNext,
false,
Type.Missing,
Type.Missing);
while (currentFind != null)
{
//Keep track of the first range you find.
if (firstFind == null)
{
firstFind = currentFind;
}
//if current address is same as the starting address stop searching
else if (currentFind.get_Address(true, true, Excel.XlReferenceStyle.xlR1C1, Type.Missing, Type.Missing) == firstFind.get_Address(true, true, Excel.XlReferenceStyle.xlR1C1, Type.Missing, Type.Missing))
{
break;
}
//keep searching for next value
currentFind = oRng.FindNext(currentFind);
MessageBox.Show(currentFind.get_Address(true, true, Excel.XlReferenceStyle.xlR1C1, Type.Missing, Type.Missing)); // for test purpose
string CurrentAddress = currentFind.get_Address(true, true, Excel.XlReferenceStyle.xlR1C1, Type.Missing, Type.Missing);
AddToDataGridView(CurrentAddress, SheetID.Name, ConnStr); //when match found get full Row details and populate it to datagridview
}
//empty ranges before looking for the next textbox values
firstFind = null;
currentFind = null;
}
//MessageBox.Show("Done control..." + cont.Name); //~test Purpose
}
//MessageBox.Show("Done...sheet"); //~test Purpoes
}
//MessageBox.Show("Done...wb"); //~test Purpose
oWB.Close(false, Type.Missing, Type.Missing);
oWB = null;
oXL.Quit();
}
now when match found I am calling this method AddToDataGridView() which should do the following:
- Get full row details by using where clause (in somehow) the
rowidshown at attached image - populate this row and add to
datagridview
another way to this are welcomed
public void AddToDataGridView(string CurrentAddress, string SheetName, string ConnStr)
{
string cmdtxt = @"select * from [" + SheetName + "$" + CurrentAddress + "]Where ???? ";
MessageBox.Show(cmdtxt); // test purpose
using (OleDbConnection conn = new OleDbConnection(ConnStr))
{
OleDbDataAdapter DA = new OleDbDataAdapter(cmdtxt, conn);
DA.Fill(dt);
DGV_Data.DataSource = dt;
conn.Close();
}
}
example
----------------------------------------------------------
# | A | B | C | D |
----------------------------------------------------------
1 | A VALUE1 | B VALUE1 | C VALUE1 | D VALUE1 |
----------------------------------------------------------
2 | A VALUE2 | B VALUE2 | C VALUE2 | D VALUE2 |
----------------------------------------------------------
suppose that B VALUE2 is my searching match value... I want to get the rowid which is 2 in that case the query that row select * from [sheet_Name$] where rowid=2 then add it to datagridview how to do that ?
thanks alot
Without fully understanding, what the goal is here. The best I can sum up is to search for a string in an Excel workbook. You want the results of the search to be displayed in a DataGridView. From your comments, you stated that each worksheet may have a different column structure and you want the "complete" row that matched the search string in at least one of the cells in that row. Therefore, each worksheet search result may/will have a different column structure. I hope I understand this correctly.
If this is the case then I am hoping the code below may help. It is a simple form, that has a button to open/select an Excel workbook to search; a text box to allow the user to type the search string into; a combo box to hold the names of the worksheets in the selected workbook; a search button to initiate the search process; a textLog text box for debugging and finally a DataGridView to hold the search results. Throw in a few labels and it may look something like below after a search for "John" in the workbook "new293.xlsx".
The text box on the right is used as a log output for testing if needed. After the search results come back, the user can use the combo box to select the results from each worksheet.
The code does a simple search for a string in a given workbook. When the user types something into the search text box and clicks the search button, the code opens the given workbook, searches for the target string in each worksheet and creates a DataTable to hold the successfully found rows. A DataSet is created to hold the "different" DataTable’s created by each worksheet since each worksheet may have a different column structure.
Below is a more detail description, however, I must comment about some possible Excel issues that may arise.
Currently the code will search for any substring of the target string. Example, if you searched for "a", the returned results will contain ANY string with an "a" in it... "cat" "bat" etc. You may want to refine how this is done. Excels "Find" method may not be the best option. (more below)
When using Excels UsedRange property, it should be noted that this may return cells that will "appear" empty. In almost all cases when this happens, there is some formatting in the cell but the cell is empty and contains no data… UsedRange may include that cell in the range because of the formatting. Please be aware of this and do not be too quick to claim UsedRange is flawed when it returns these "apparently" empty cells. I have a solution for this in another answer.
Lastly, about Excel and interop… the current code uses the same Excel Find method as your posted code does. This should be fine if the dataset is not to large, however, if there is a large amount of data (large Excel worksheets with many columns and rows), this may become a performance problem. This is an Excel and interop issue. Calling methods like UsedRange, Find and others is VERY EXPENSIVE when used in a loop (which we are). Point being, if the worksheets are large, you may want to consider a different implementation without using interop. I understand there are better (free) third party Excel libraries.
With that said, below is the code to the form above.
Global variables in the form:
A DataSet ds to hold the DataTables; self-explanatory path DefaultDirectory; an Excel workbook to search and finally the Excel application itself. Upon load, the Excel app is started and waits for the user to select a workbook.
DataSet ds = new DataSet();
string DefaultDirectory = @"D:\Test\";
Workbook workbook;
Excel.Application excelApp;
public Form3() {
InitializeComponent();
}
private void Form3_Load(object sender, EventArgs e) {
excelApp = new Excel.Application();
}
The open/select workbook button click to select a workbook uses an OpenFileDialog to allow the user to select the workbook to search. Once selected, the global variable workbook is open and available to other methods.
private void btnSelectWorkbook_Click(object sender, EventArgs e) {
DGV_Data.DataSource = null;
tbSearch.Text = "";
cbWorksheetNames.Items.Clear();
textLog.Text = "";
lblWorkbookName.Text = "";
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
ofd.InitialDirectory = DefaultDirectory;
if (ofd.ShowDialog() == DialogResult.OK) {
string fileToOpen = ofd.FileName;
workbook = excelApp.Workbooks.Open(fileToOpen);
lblWorkbookName.Text = "Workbook: " + fileToOpen;
}
}
After the user selects a workbook to search, and has typed some target text to search for… the user clicks the "Search" button. First, two checks are made to make sure there is some text to search for and also check if there is a workbook open to search in. If there is no text to search for or a workbook is not open, display a message to the user and return without searching.
If there is an open workbook and text to search for, the global DataSet ds is initialized, then ds is filled with each worksheet’s DataTable by calling the GetRowsFromSearchStringFromAllWorksheets. . After the DataSet has been filled (more below) the DataGridView’s DataSource is set to the first DataTable in the DatatSet; the combo box is filled with the worksheet names and finally some labels are updated.
private void btnSearch_Click(object sender, EventArgs e) {
if (string.IsNullOrEmpty(tbSearch.Text)) {
MessageBox.Show("Enter text for search");
return;
}
if (workbook == null) {
MessageBox.Show("Select a workbook");
return;
}
ds = new DataSet();
try {
ds = GetRowsFromSearchStringFromAllWorksheets(workbook, tbSearch.Text);
DGV_Data.DataSource = ds.Tables[0];
FillComboBoxWithSheetNames();
cbWorksheetNames.SelectedIndex = 0;
gbResults.Text = "Search Results for '" + tbSearch.Text + "'";
tbSearch.Text = "";
}
catch (Exception ex) {
MessageBox.Show("Error: " + ex.Message);
}
}
The GetRowsFromSearchStringFromAllWorksheets method (which probably needs a better name) is not doing much. It loops through each worksheet in the workbook, creates a DataTable from the worksheet, fills the data table by calling the FillTableWithSearchResults method (below), then finally adds the DataTable to the DataSet. The GetDTColumnsFromWorksheet method (below) creates a DataTable based on what the header rows are in the worksheet. The first row in the worksheet is assumed to be header rows and uses these as column names for the DataTable. Note: currently, if the search returns with no results, the worksheet DataTable is STILL added to the DataSet. I left the debug code to modify if you only want to add worksheets that contained results.
private DataSet GetRowsFromSearchStringFromAllWorksheets(Workbook wb, string searchString) {
DataSet ds = new DataSet();
foreach (Worksheet currentWorksheet in wb.Worksheets) {
System.Data.DataTable currentDT = GetDTColumnsFromWorksheet(currentWorksheet);
//textLog.Text += "Searching in worksheet " + currentWorksheet.Name + Environment.NewLine;
FillTableWithSearchResults(currentWorksheet.UsedRange, searchString, currentDT);
if (currentDT.Rows.Count > 0) {
textLog.Text += "Matches found in worksheet " + currentWorksheet.Name + Environment.NewLine;
}
else {
textLog.Text += "No matches found in worksheet " + currentWorksheet.Name + Environment.NewLine;
}
ds.Tables.Add(currentDT);
}
return ds;
}
The GetDTColumnsFromWorksheet takes a worksheet and returns a DataTable. The data table returned will have the same number of columns as there are returned from "UsedRange` from the worksheet. An extra column is added to display where in the worksheet the cell is found. It is in an RXXCXX format in the first column. If a cell in a column in the used range has no value, the string "???XX" will be used. This is for cases where there is an empty column.
private System.Data.DataTable GetDTColumnsFromWorksheet(Worksheet ws) {
// this assumes that row 1 of the worksheet contains a row header
// we will use this to name the `DataTable` columns
// this also assumes there are no "lingering" cells with values
// that may not necessarily belong to the data table
int missingColumnNameCount = 1;
Range usedRange = ws.UsedRange;
int numberOFColumns = usedRange.Columns.Count;
System.Data.DataTable dt = new System.Data.DataTable();
dt.TableName = ws.Name;
string currentColumnHeader = "";
Range row1;
// add an extra column in the front
// this column will show where (RXCX) the found item is in the worksheet
dt.Columns.Add("CXRX", typeof(string));
for (int i = 1; i <= numberOFColumns; i++) {
row1 = usedRange[1, i];
if (row1.Value2 != null) {
currentColumnHeader = row1.Value2.ToString();
}
else {
// if the row has no value a default name and indexer to avoid duplicate column names
currentColumnHeader = "???" + missingColumnNameCount;
missingColumnNameCount++;
}
dt.Columns.Add(currentColumnHeader, typeof(string));
}
return dt;
}
The FillTableWithSearchResults method takes a range to search through, a string to search for and finally a data table to add the successful searches to. The DataTable passed in has already been created and the columns have been initialized.
I am not sure if this is the best way to handle Excels Find/FindNext methods. Therefore, I hope I have this correct. When the Find is used on a range for the first time, it returns the first found cell that matches what it is searching for. This retuned range is a cell address of the "first" found item. From my understanding, the FindNext will obviously return the NEXT found item. The issue is that when it finds the LAST item and searches for the next one, it simply starts over at the beginning. Therefore, the stopping condition for the loop would be when the cell address of the NextFind matches the cell address of the "first" Find. This will require that we save the "first" Find’s cell address. Below is one approach to this conundrum.
Create two ranges: one startRange to hold the starting "first" Find, another currentRange to hold the currently "found" range. First, a check is made to make sure there is something to search in. If there is at least one row to search in, the startRange is set from the "first" Find. This is the cell address we need to stop at when using FindNext. If there is at least one item found, then we can search for the next one and enter the FindNext loop. Simply set currentRange to the NextFind add the startRange from the "first" Findto the data table then finally enter the FindNext loop such that it will continue using FindNext and adding new rows to the data table until the currentRange cell address Equals the startingRange cell address. This indicates that FindNext has looped back to the beginning and the search is complete. The AddExcelRowToDataTable adds the found row to the data table (below).
Note: currently this code allows duplicate entries in cases where the searched for string is found in more than one column in the same row. There will be one row entry in the grid for every found search string in the rows columns. Example: if row 5 has the searched for string in columns 4, 6 and 8, there will be a row for R5C4, R5C6 and R5C8. I did not filter this to remove duplicate rows.
private void FillTableWithSearchResults(Range usedRange, string searchString, System.Data.DataTable dt) {
Range currentRange;
if (usedRange.Rows.Count > 0) {
Range startRange = usedRange.Find(searchString);
if (startRange != null) {
currentRange = usedRange.FindNext(startRange);
AddExcelRowToDataTable(usedRange, startRange, dt);
string startAddress = startRange.get_Address(true, true, XlReferenceStyle.xlR1C1);
while (!currentRange.get_Address(true, true, XlReferenceStyle.xlR1C1).Equals(startAddress)) {
AddExcelRowToDataTable(usedRange, currentRange, dt);
currentRange = usedRange.FindNext(currentRange);
}
}
}
}
The AddExcelRowToDataTable method takes a used range to get the data from, a range row to add to the third given parameter DataTable. Again a little hacky, a check is made to make sure that there are not more columns in the used range than there are in the data table. A row Index is obtained to indicate which row in the used range to add to the data table. A DataRow dr is created from the given DataTable dt to make sure the column schemas are the same. The first column is going to be the column we added earlier to display the RXXCXX location column of the found item. Add the extra column data then loop through the rest of the columns adding the worksheet values to the DataRow. After the all the column values have been added, the DataRow is added to the DataTable.
private void AddExcelRowToDataTable(Range usedRange, Range row, System.Data.DataTable dt) {
if (usedRange.Columns.Count >= dt.Columns.Count - 1) {
int rowIndex = GetRowIndexOfFoundItem(row);
if (rowIndex >= 0) {
DataRow dr = dt.NewRow();
// add the CXRX data
dr[0] = row.get_Address(true, true, XlReferenceStyle.xlR1C1);
for (int i = 1; i <= usedRange.Columns.Count; i++) {
dr[i] = usedRange.Cells[rowIndex, i].Value2;
}
dt.Rows.Add(dr);
}
}
}
The GetRowIndexOfFoundItem takes a cell range and returns the (int) row index from the cells string RXXCXX address.
private int GetRowIndexOfFoundItem(Range range) {
// hacky ... the string is a format of RXXCX or RXXcXXX or RXXXXCXX.
// we want the XXX after the R... split the string on 'C'
// to get RXX..X, then remove the 'R' and parse the number
string RCaddress = range.get_Address(true, true, XlReferenceStyle.xlR1C1);
string[] split = RCaddress.Split('C');
RCaddress = split[0].Remove(0, 1);
int rowIndex = 0;
if (int.TryParse(RCaddress, out rowIndex)) {
return rowIndex;
}
else {
// not valid number
return -1;
}
}
Method to fill the combo box with worksheet names after the search has finished.
private void FillComboBoxWithSheetNames() {
cbWorksheetNames.Items.Clear();
foreach (System.Data.DataTable dt in ds.Tables) {
cbWorksheetNames.Items.Add(dt.TableName);
}
}
The combo boxes SelectedIndexChnged event is wired up and uses the combo boxes selected index to determine which DataTable to display in the grid.
private void cbWorksheetNames_SelectedIndexChanged(object sender, EventArgs e) {
DGV_Data.DataSource = ds.Tables[cbWorksheetNames.SelectedIndex];
}
Finally some resource cleanup.
private void Form3_FormClosing(object sender, FormClosingEventArgs e) {
try {
if (workbook != null) {
workbook.Close();
Marshal.ReleaseComObject(workbook);
}
excelApp.Quit();
Marshal.ReleaseComObject(excelApp);
}
catch (Exception ex) {
MessageBox.Show("Error: " + ex.Message);
}
}
Sorry for the long winded answer, I hope it helps.
这篇关于如何使用Excel RowIndex将特定的Excel行插入到DataGridView中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
