Самый быстрый способ удаления пустых строк и столбцов из файлов Excel с помощью Interop
У меня есть много файлов Excel, которые содержат данные, и это содержит пустые строки и пустые столбцы. как показано ниже
Я пытаюсь удалить пустые строки и столбцы из Excel с помощью взаимодействия. Я создаю простое приложение winform и использовал следующий код, и он отлично работает.
Dim lstFiles As New List(Of String)
lstFiles.AddRange(IO.Directory.GetFiles(m_strFolderPath, "*.xls", IO.SearchOption.AllDirectories))
Dim m_XlApp = New Excel.Application
Dim m_xlWrkbs As Excel.Workbooks = m_XlApp.Workbooks
Dim m_xlWrkb As Excel.Workbook
For Each strFile As String In lstFiles
m_xlWrkb = m_xlWrkbs.Open(strFile)
Dim m_XlWrkSheet As Excel.Worksheet = m_xlWrkb.Worksheets(1)
Dim intRow As Integer = 1
While intRow <= m_XlWrkSheet.UsedRange.Rows.Count
If m_XlApp.WorksheetFunction.CountA(m_XlWrkSheet.Cells(intRow, 1).EntireRow) = 0 Then
m_XlWrkSheet.Cells(intRow, 1).EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp)
Else
intRow += 1
End If
End While
Dim intCol As Integer = 1
While intCol <= m_XlWrkSheet.UsedRange.Columns.Count
If m_XlApp.WorksheetFunction.CountA(m_XlWrkSheet.Cells(1, intCol).EntireColumn) = 0 Then
m_XlWrkSheet.Cells(1, intCol).EntireColumn.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft)
Else
intCol += 1
End If
End While
Next
m_xlWrkb.Save()
m_xlWrkb.Close(SaveChanges:=True)
Marshal.ReleaseComObject(m_xlWrkb)
Marshal.ReleaseComObject(m_xlWrkbs)
m_XlApp.Quit()
Marshal.ReleaseComObject(m_XlApp)
Но при очистке больших файлов Excel требуется много времени. Есть предложения по оптимизации этого кода? или другой способ очистить файлы Excel быстрее? Есть ли функция, которая может удалять пустые строки в один клик?
У меня нет проблем, если ответы используют C#
РЕДАКТИРОВАТЬ:
Я загрузил образец файла Sample File. Но не все файлы имеют одинаковую структуру.
5 ответов
Я обнаружил, что циклический просмотр листа Excel может занять некоторое время, если лист большой. Поэтому мое решение пыталось избежать зацикливания на листе. Чтобы избежать зацикливания на листе, я сделал массив двухмерных объектов из ячеек, возвращаемых из usedRange с:
Excel.Range targetCells = worksheet.UsedRange;
object[,] allValues = (object[,])targetCells.Cells.Value;
Это массив, который я перебираю, чтобы получить индексы пустых строк и столбцов. Я делаю 2 списка int, один сохраняет индексы строк для удаления, другой сохраняет индексы столбцов для удаления.
List<int> emptyRows = GetEmptyRows(allValues, totalRows, totalCols);
List<int> emptyCols = GetEmptyCols(allValues, totalRows, totalCols);
Эти списки будут отсортированы сверху вниз, чтобы упростить удаление строк снизу вверх и удаление столбцов справа налево. Затем просто переберите каждый список и удалите соответствующую строку / столбец.
DeleteRows(emptyRows, worksheet);
DeleteCols(emptyCols, worksheet);
Наконец, после удаления всех пустых строк и столбцов, я сохраняю файл под новым именем.
Надеюсь это поможет.
РЕДАКТИРОВАТЬ:
Устранена проблема с использованием UsedRange, так что если в верхней части таблицы есть пустые строки, эти строки теперь будут удалены. Также это удалит все пустые столбцы слева от начальных данных. Это позволяет индексированию работать должным образом, даже если перед запуском данных есть пустые строки или столбцы. Это было достигнуто путем взятия адреса первой ячейки в UsedRange, это будет адрес вида "$A$1:$D$4". Это позволит использовать смещение, если пустые строки вверху и пустые столбцы слева останутся и не будут удалены. В этом случае я просто удаляю их. Чтобы получить количество строк, удаляемых сверху, можно рассчитать по первому адресу "$A$4", где "4" - это строка, в которой появляются первые данные. Поэтому нам нужно удалить 3 верхних ряда. Адрес столбца имеет форму "A", "AB" или даже "AAD", это потребовало некоторого перевода и благодаря тому, как преобразовать номер столбца (например, 127) в столбец Excel (например, AA), я смог определить, сколько столбцов слева необходимо удалить.
class Program {
static void Main(string[] args) {
Excel.Application excel = new Excel.Application();
string originalPath = @"H:\ExcelTestFolder\Book1_Test.xls";
Excel.Workbook workbook = excel.Workbooks.Open(originalPath);
Excel.Worksheet worksheet = workbook.Worksheets["Sheet1"];
Excel.Range usedRange = worksheet.UsedRange;
RemoveEmptyTopRowsAndLeftCols(worksheet, usedRange);
DeleteEmptyRowsCols(worksheet);
string newPath = @"H:\ExcelTestFolder\Book1_Test_Removed.xls";
workbook.SaveAs(newPath, Excel.XlSaveAsAccessMode.xlNoChange);
workbook.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
Console.WriteLine("Finished removing empty rows and columns - Press any key to exit");
Console.ReadKey();
}
private static void DeleteEmptyRowsCols(Excel.Worksheet worksheet) {
Excel.Range targetCells = worksheet.UsedRange;
object[,] allValues = (object[,])targetCells.Cells.Value;
int totalRows = targetCells.Rows.Count;
int totalCols = targetCells.Columns.Count;
List<int> emptyRows = GetEmptyRows(allValues, totalRows, totalCols);
List<int> emptyCols = GetEmptyCols(allValues, totalRows, totalCols);
// now we have a list of the empty rows and columns we need to delete
DeleteRows(emptyRows, worksheet);
DeleteCols(emptyCols, worksheet);
}
private static void DeleteRows(List<int> rowsToDelete, Excel.Worksheet worksheet) {
// the rows are sorted high to low - so index's wont shift
foreach (int rowIndex in rowsToDelete) {
worksheet.Rows[rowIndex].Delete();
}
}
private static void DeleteCols(List<int> colsToDelete, Excel.Worksheet worksheet) {
// the cols are sorted high to low - so index's wont shift
foreach (int colIndex in colsToDelete) {
worksheet.Columns[colIndex].Delete();
}
}
private static List<int> GetEmptyRows(object[,] allValues, int totalRows, int totalCols) {
List<int> emptyRows = new List<int>();
for (int i = 1; i < totalRows; i++) {
if (IsRowEmpty(allValues, i, totalCols)) {
emptyRows.Add(i);
}
}
// sort the list from high to low
return emptyRows.OrderByDescending(x => x).ToList();
}
private static List<int> GetEmptyCols(object[,] allValues, int totalRows, int totalCols) {
List<int> emptyCols = new List<int>();
for (int i = 1; i < totalCols; i++) {
if (IsColumnEmpty(allValues, i, totalRows)) {
emptyCols.Add(i);
}
}
// sort the list from high to low
return emptyCols.OrderByDescending(x => x).ToList();
}
private static bool IsColumnEmpty(object[,] allValues, int colIndex, int totalRows) {
for (int i = 1; i < totalRows; i++) {
if (allValues[i, colIndex] != null) {
return false;
}
}
return true;
}
private static bool IsRowEmpty(object[,] allValues, int rowIndex, int totalCols) {
for (int i = 1; i < totalCols; i++) {
if (allValues[rowIndex, i] != null) {
return false;
}
}
return true;
}
private static void RemoveEmptyTopRowsAndLeftCols(Excel.Worksheet worksheet, Excel.Range usedRange) {
string addressString = usedRange.Address.ToString();
int rowsToDelete = GetNumberOfTopRowsToDelete(addressString);
DeleteTopEmptyRows(worksheet, rowsToDelete);
int colsToDelete = GetNumberOfLeftColsToDelte(addressString);
DeleteLeftEmptyColumns(worksheet, colsToDelete);
}
private static void DeleteTopEmptyRows(Excel.Worksheet worksheet, int startRow) {
for (int i = 0; i < startRow - 1; i++) {
worksheet.Rows[1].Delete();
}
}
private static void DeleteLeftEmptyColumns(Excel.Worksheet worksheet, int colCount) {
for (int i = 0; i < colCount - 1; i++) {
worksheet.Columns[1].Delete();
}
}
private static int GetNumberOfTopRowsToDelete(string address) {
string[] splitArray = address.Split(':');
string firstIndex = splitArray[0];
splitArray = firstIndex.Split('$');
string value = splitArray[2];
int returnValue = -1;
if ((int.TryParse(value, out returnValue)) && (returnValue >= 0))
return returnValue;
return returnValue;
}
private static int GetNumberOfLeftColsToDelte(string address) {
string[] splitArray = address.Split(':');
string firstindex = splitArray[0];
splitArray = firstindex.Split('$');
string value = splitArray[1];
return ParseColHeaderToIndex(value);
}
private static int ParseColHeaderToIndex(string colAdress) {
int[] digits = new int[colAdress.Length];
for (int i = 0; i < colAdress.Length; ++i) {
digits[i] = Convert.ToInt32(colAdress[i]) - 64;
}
int mul = 1; int res = 0;
for (int pos = digits.Length - 1; pos >= 0; --pos) {
res += digits[pos] * mul;
mul *= 26;
}
return res;
}
}
РЕДАКТИРОВАТЬ 2: Для тестирования я сделал метод, который проходит через таблицу и сравнил ее с моим кодом, который проходит через массив объектов. Это показывает существенную разницу.
Метод зацикливания на листе и удаления пустых строк и столбцов.
enum RowOrCol { Row, Column };
private static void ConventionalRemoveEmptyRowsCols(Excel.Worksheet worksheet) {
Excel.Range usedRange = worksheet.UsedRange;
int totalRows = usedRange.Rows.Count;
int totalCols = usedRange.Columns.Count;
RemoveEmpty(usedRange, RowOrCol.Row);
RemoveEmpty(usedRange, RowOrCol.Column);
}
private static void RemoveEmpty(Excel.Range usedRange, RowOrCol rowOrCol) {
int count;
Excel.Range curRange;
if (rowOrCol == RowOrCol.Column)
count = usedRange.Columns.Count;
else
count = usedRange.Rows.Count;
for (int i = count; i > 0; i--) {
bool isEmpty = true;
if (rowOrCol == RowOrCol.Column)
curRange = usedRange.Columns[i];
else
curRange = usedRange.Rows[i];
foreach (Excel.Range cell in curRange.Cells) {
if (cell.Value != null) {
isEmpty = false;
break; // we can exit this loop since the range is not empty
}
else {
// Cell value is null contiue checking
}
} // end loop thru each cell in this range (row or column)
if (isEmpty) {
curRange.Delete();
}
}
}
Затем основной для тестирования / синхронизации двух методов.
enum RowOrCol { Row, Column };
static void Main(string[] args)
{
Excel.Application excel = new Excel.Application();
string originalPath = @"H:\ExcelTestFolder\Book1_Test.xls";
Excel.Workbook workbook = excel.Workbooks.Open(originalPath);
Excel.Worksheet worksheet = workbook.Worksheets["Sheet1"];
Excel.Range usedRange = worksheet.UsedRange;
// Start test for looping thru each excel worksheet
Stopwatch sw = new Stopwatch();
Console.WriteLine("Start stopwatch to loop thru WORKSHEET...");
sw.Start();
ConventionalRemoveEmptyRowsCols(worksheet);
sw.Stop();
Console.WriteLine("It took a total of: " + sw.Elapsed.Milliseconds + " Miliseconds to remove empty rows and columns...");
string newPath = @"H:\ExcelTestFolder\Book1_Test_RemovedLoopThruWorksheet.xls";
workbook.SaveAs(newPath, Excel.XlSaveAsAccessMode.xlNoChange);
workbook.Close();
Console.WriteLine("");
// Start test for looping thru object array
workbook = excel.Workbooks.Open(originalPath);
worksheet = workbook.Worksheets["Sheet1"];
usedRange = worksheet.UsedRange;
Console.WriteLine("Start stopwatch to loop thru object array...");
sw = new Stopwatch();
sw.Start();
DeleteEmptyRowsCols(worksheet);
sw.Stop();
// display results from second test
Console.WriteLine("It took a total of: " + sw.Elapsed.Milliseconds + " Miliseconds to remove empty rows and columns...");
string newPath2 = @"H:\ExcelTestFolder\Book1_Test_RemovedLoopThruArray.xls";
workbook.SaveAs(newPath2, Excel.XlSaveAsAccessMode.xlNoChange);
workbook.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
Console.WriteLine("");
Console.WriteLine("Finished testing methods - Press any key to exit");
Console.ReadKey();
}
РЕДАКТИРОВАТЬ 3 В соответствии с запросом OP... Я обновил и изменил код в соответствии с кодом OP. С этим я нашел некоторые интересные результаты. Увидеть ниже.
Я изменил код, чтобы он соответствовал функциям, которые вы используете, т. Е.… FullRow и CountA. Код ниже я обнаружил, что он ужасно преформируется. Выполняя некоторые тесты, я обнаружил, что приведенный ниже код находился во времени выполнения более 800 миллисекунд. Однако одно тонкое изменение имело огромное значение.
На линии:
while (rowIndex <= worksheet.UsedRange.Rows.Count)
Это сильно тормозит. Если вы создадите переменную диапазона для UsedRang и не будете использовать regrabbibg с каждой итерацией цикла while, это будет иметь огромное значение. Итак... когда я изменяю цикл while на...
Excel.Range usedRange = worksheet.UsedRange;
int rowIndex = 1;
while (rowIndex <= usedRange.Rows.Count)
and
while (colIndex <= usedRange.Columns.Count)
Это выполнено очень близко к моему решению массива объектов. Я не опубликовал результаты, так как вы можете использовать приведенный ниже код и изменить цикл while, чтобы захватить UsedRange с каждой итерацией или использовать переменную usedRange для проверки этого.
private static void RemoveEmptyRowsCols3(Excel.Worksheet worksheet) {
//Excel.Range usedRange = worksheet.UsedRange; // <- using this variable makes the while loop much faster
int rowIndex = 1;
// delete empty rows
//while (rowIndex <= usedRange.Rows.Count) // <- changing this one line makes a huge difference - not grabbibg the UsedRange with each iteration...
while (rowIndex <= worksheet.UsedRange.Rows.Count) {
if (excel.WorksheetFunction.CountA(worksheet.Cells[rowIndex, 1].EntireRow) == 0) {
worksheet.Cells[rowIndex, 1].EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
else {
rowIndex++;
}
}
// delete empty columns
int colIndex = 1;
// while (colIndex <= usedRange.Columns.Count) // <- change here also
while (colIndex <= worksheet.UsedRange.Columns.Count) {
if (excel.WorksheetFunction.CountA(worksheet.Cells[1, colIndex].EntireColumn) == 0) {
worksheet.Cells[1, colIndex].EntireColumn.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
}
else {
colIndex++;
}
}
}
ОБНОВЛЕНИЕ Hadi
Вы можете изменить DeleteCols а также DeleteRows Функция для повышения производительности, если Excel содержит дополнительные пустые строки и столбцы после последних использованных:
private static void DeleteRows(List<int> rowsToDelete, Microsoft.Office.Interop.Excel.Worksheet worksheet)
{
// the rows are sorted high to low - so index's wont shift
List<int> NonEmptyRows = Enumerable.Range(1, rowsToDelete.Max()).ToList().Except(rowsToDelete).ToList();
if (NonEmptyRows.Max() < rowsToDelete.Max())
{
// there are empty rows after the last non empty row
Microsoft.Office.Interop.Excel.Range cell1 = worksheet.Cells[NonEmptyRows.Max() + 1,1];
Microsoft.Office.Interop.Excel.Range cell2 = worksheet.Cells[rowsToDelete.Max(), 1];
//Delete all empty rows after the last used row
worksheet.Range[cell1, cell2].EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
} //else last non empty row = worksheet.Rows.Count
foreach (int rowIndex in rowsToDelete.Where(x => x < NonEmptyRows.Max()))
{
worksheet.Rows[rowIndex].Delete();
}
}
private static void DeleteCols(List<int> colsToDelete, Microsoft.Office.Interop.Excel.Worksheet worksheet)
{
// the cols are sorted high to low - so index's wont shift
//Get non Empty Cols
List<int> NonEmptyCols = Enumerable.Range(1, colsToDelete.Max()).ToList().Except(colsToDelete).ToList();
if (NonEmptyCols.Max() < colsToDelete.Max())
{
// there are empty rows after the last non empty row
Microsoft.Office.Interop.Excel.Range cell1 = worksheet.Cells[1,NonEmptyCols.Max() + 1];
Microsoft.Office.Interop.Excel.Range cell2 = worksheet.Cells[1,NonEmptyCols.Max()];
//Delete all empty rows after the last used row
worksheet.Range[cell1, cell2].EntireColumn.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftToLeft);
} //else last non empty column = worksheet.Columns.Count
foreach (int colIndex in colsToDelete.Where(x => x < NonEmptyCols.Max()))
{
worksheet.Columns[colIndex].Delete();
}
}
проверьте мой ответ в Get Last, не пустой столбец и индекс строки из Excel, используя Interop
Может быть, что-то рассмотреть:
Sub usedRangeDeleteRowsCols()
Dim LastRow, LastCol, i As Long
LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LastCol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For i = LastRow To 1 Step -1
If WorksheetFunction.CountA(Range(Cells(i, 1), Cells(i, LastCol))) = 0 Then
Cells(i, 1).EntireRow.Delete
End If
Next
For i = LastCol To 1 Step -1
If WorksheetFunction.CountA(Range(Cells(1, i), Cells(LastRow, i))) = 0 Then
Cells(1, i).EntireColumn.Delete
End If
Next
End Sub
Я думаю, что есть две эффективности по сравнению с эквивалентными функциями в исходном коде. Во-первых, вместо использования ненадежного свойства UsedRange в Excel, мы находим последнее значение и сканируем только строки и столбцы в пределах реального используемого диапазона.
Во-вторых, функция подсчета рабочих листов снова работает только в пределах реального используемого диапазона - например, при поиске пустых строк мы смотрим только в диапазоне используемых столбцов (а не .EntireRow).
For Циклы работают в обратном направлении, потому что, например, каждый раз, когда строка удаляется, адрес строки следующих данных изменяется. Работа в обратном направлении означает, что адреса строк "данных для обработки" не меняются.
На мой взгляд, наиболее трудоемкой частью может быть перечисление и поиск пустых строк и столбцов.
Как насчет: http://www.howtogeek.com/206696/how-to-quickly-and-easily-delete-blank-rows-and-columns-in-excel-2013/
РЕДАКТИРОВАТЬ:
Как насчет:
m_XlWrkSheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
m_XlWrkSheet.Rows("1:1").SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
Результаты тестирования на примерах данных выглядят нормально, производительность выше (тестируется на VBA, но разница огромна).
ОБНОВИТЬ:
Протестировано на примере Excel с исходными кодами из 14 тыс. Строк (из данных примера), эта версия <1 с
Самый простой способ, который я знаю, это скрыть непустые ячейки и удалить видимые:
var range = m_XlWrkSheet.UsedRange;
range.SpecialCells(XlCellType.xlCellTypeConstants).EntireRow.Hidden = true;
range.SpecialCells(XlCellType.xlCellTypeVisible).Delete(XlDeleteShiftDirection.xlShiftUp);
range.EntireRow.Hidden = false;
Более быстрые методы - вообще ничего не удалять, а перемещать (вырезать + вставлять) непустые области.
Самый быстрый способ взаимодействия (есть более быстрые и сложные методы без открытия файла) - получить все значения в массиве, переместить значения в массив и вернуть значения обратно:
object[,] values = m_XlWrkSheet.UsedRange.Value2 as object[,];
// some code here (the values start from values[1, 1] not values[0, 0])
m_XlWrkSheet.UsedRange.Value2 = values;
Вы можете открыть соединение ADO с рабочим листом, получить список полей, выполнить оператор SQL, который включает только известные поля, а также исключить записи без значений в известных полях.