C#Excel:获取行数和列数的正确方法 [英] C# Excel : Correct way to get Rows and Columns count

问题描述

我在使用C#Interop Excel时遇到大文件的有效范围

I have problem with C# Interop Excel get valid range with big file

https://www.dropbox.com/s/betci638b1faw8g/Demo%20Training%20Data.xlsx?dl=0

这是我的文件，但实际大小为1000，但我有49998

This is my file but real size is 1000 but I got 49998

我使用了标准代码

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(txtbTrainPath.Text);
Excel.Worksheet xlWorksheet = xlWorkbook.Worksheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;              
xlApp.Visible = true;
xlWorksheet.Columns[5].Delete();
xlWorksheet.Columns[3].Delete();              
rowCount = xlRange.Rows.Count;
colCount = xlRange.Columns.Count;

仅对于该文件无法正常工作，其他文件也可以正常工作.请帮我找出问题所在.如何只为有效大小调整工作表的大小.

Only for this file is not work correctly, other files works well. Please help me to find what is the problem. How to resize worksheet for only valid size.

推荐答案

我将使用这种方法来获取行和列"计数，该计数将返回不为空的单元格的结果.

I would use this approach to get the Rows and Columns count which will return the result of the cells which are not empty.

// Find the last real row
lastUsedRow = worksheet.Cells.Find("*",System.Reflection.Missing.Value, 
                               System.Reflection.Missing.Value, System.Reflection.Missing.Value, 
                               Excel.XlSearchOrder.xlByRows,Excel.XlSearchDirection.xlPrevious, 
                               false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Row;

// Find the last real column
lastUsedColumn = worksheet.Cells.Find("*", System.Reflection.Missing.Value, 
                               System.Reflection.Missing.Value,System.Reflection.Missing.Value, 
                               Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlPrevious, 
                               false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Column;

以下是完整的代码供您参考:

Here is the complete code for your reference:

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application xlApp     = null;
Excel.Workbook wb           = null;
Excel.Worksheet worksheet   = null;
int lastUsedRow             = 0;
int lastUsedColumn          = 0;
string srcFile              = @"Path to your XLSX file";

xlApp = new Excel.ApplicationClass();
xlApp.Visible = false;
wb = xlApp.Workbooks.Open(srcFile,
                               0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                               true, false, 0, true, false, false);

worksheet = (Excel.Worksheet)wb.Worksheets[1];
Excel.Range range

// Find the last real row
lastUsedRow = worksheet.Cells.Find("*",System.Reflection.Missing.Value, 
                               System.Reflection.Missing.Value, System.Reflection.Missing.Value, 
                               Excel.XlSearchOrder.xlByRows,Excel.XlSearchDirection.xlPrevious, 
                               false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Row;

// Find the last real column
lastUsedColumn = worksheet.Cells.Find("*", System.Reflection.Missing.Value, 
                               System.Reflection.Missing.Value,System.Reflection.Missing.Value, 
                               Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlPrevious, 
                               false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Column;

xlApp.Workbooks.Close();
xlApp.Quit();

Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(xlApp);

这篇关于C#Excel:获取行数和列数的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！