반응형
Interop을 사용하여 Excel 파일에서 빈 행과 열을 제거하는 가장 빠른 방법
데이터가 들어 있는 엑셀 파일이 많은데 빈 행과 빈 열이 들어 있습니다.아래 그림과 같이
interop을 사용하여 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)
하지만 큰 엑셀 파일을 정리할 때는 시간이 많이 걸립니다.이 코드를 최적화하기 위한 제안이 있습니까?아니면 이 엑셀 파일을 더 빨리 정리할 수 있는 다른 방법이 있을까요?빈 행을 원클릭으로 삭제할 수 있는 기능이 있나요?
답변이 C#을 사용해도 문제 없습니다.
편집:
샘플 파일 Sample File을 업로드했습니다.그러나 모든 파일의 구조가 동일한 것은 아닙니다.
워크시트가 크면 엑셀 워크시트를 반복하는 데 시간이 걸릴 수 있습니다.그래서 제 솔루션은 워크시트가 반복되지 않도록 했습니다. 위해, .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);
마지막으로 빈 행과 열을 모두 삭제한 후 파일을 새 파일 이름으로 저장합니다.
이게 도움이 됐으면 좋겠다.
편집:
워크시트 상단에 빈 행이 있으면 해당 행이 삭제되도록 Used Range 문제를 해결했습니다.또한 시작 데이터 왼쪽에 있는 빈 열이 제거됩니다.이렇게 하면 데이터가 시작되기 전에 빈 행이나 열이 있더라도 인덱싱이 제대로 작동할 수 있습니다.이것은 UsedRange의 첫 번째 셀 주소를 취함으로써 이루어졌습니다.이 주소는 "$A$1:$D$4" 형식의 주소가 됩니다.이렇게 하면 맨 위의 빈 행과 왼쪽의 빈 열이 그대로 남아 삭제되지 않을 경우 오프셋을 사용할 수 있습니다.이 경우 단순히 삭제만 하면 됩니다.위에서 삭제할 행의 수를 얻으려면 첫 번째 "$A$4" 주소로 계산할 수 있습니다. 여기서 "4"는 첫 번째 데이터가 나타나는 행입니다.따라서 상위 3개의 행을 삭제해야 합니다.컬럼 주소는 "A", "AB" 또는 "AAD" 형식입니다.이는 일부 번역이 필요했고 컬럼 번호(예: 127)를 엑셀 컬럼으로 변환하는 방법(예: 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;
}
}
EDIT 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();
}
}
}
그런 다음 두 가지 방법을 테스트/타이밍하기 위한 Main.
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();
}
EDIT 3 운영 요청에 따라...OP코드에 맞게 코드를 업데이트 및 변경하였습니다.이것으로 나는 몇 가지 흥미로운 결과를 발견했다.이하를 참조해 주세요.
사용하시는 기능에 맞추어 코드를 변경했습니다.즉, 전체 Row와 Count A입니다.아래 코드가 너무 엉성하게 작성되어 있는 것을 발견했습니다.몇 가지 테스트를 실행한 결과 아래 코드가 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행과 뒤에 과 컬럼이 되어 있는 성능을 시키기 위한 :
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();
}
}
Interop을 사용하여 Excel에서 마지막으로 비어 있지 않은 열 및 행 색인을 가져옵니다.
고려해야 할 사항:
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
원래 코드의 동등한 기능에 비해 두 가지 효율이 있다고 생각합니다.우선 Excel의 신뢰할 수 없는 UsedRange 속성을 사용하는 대신 마지막 값을 찾아 실제 사용된 범위 내의 행과 열만 스캔합니다.
둘째, 워크시트 카운트 함수는 실제 사용된 범위 내에서만 작동합니다. 예를 들어 빈 행을 검색할 때는 사용된 열 범위에서만 찾습니다..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에서 테스트했지만 큰 차이가 있습니다).
갱신:
14k행 (샘플 데이터로 작성)오리지널 코드를 가진 샘플 Excel에서 테스트 완료 (이 버전 <1s)
내가 알고 있는 가장 쉬운 방법은 공백이 아닌 셀을 숨기고 표시된 셀을 삭제하는 것입니다.
var range = m_XlWrkSheet.UsedRange;
range.SpecialCells(XlCellType.xlCellTypeConstants).EntireRow.Hidden = true;
range.SpecialCells(XlCellType.xlCellTypeVisible).Delete(XlDeleteShiftDirection.xlShiftUp);
range.EntireRow.Hidden = false;
더 빠른 방법은 아무것도 삭제하지 않고 공백이 아닌 영역을 이동(잘라내기+붙여넣기)하는 것입니다.
가장 빠른 Interop 방법(파일을 열지 않고 보다 빠른 방법이 있습니다)은 배열 내의 모든 값을 가져와 배열 내의 값을 이동하고 값을 되돌리는 것입니다.
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 문을 발급하고, 알려진 필드에 값이 없는 레코드를 제외할 수 있습니다.
언급URL : https://stackoverflow.com/questions/40574084/fastest-method-to-remove-empty-rows-and-columns-from-excel-files-using-interop
반응형
'programing' 카테고리의 다른 글
| DataGrid의 텍스트 정렬 (0) | 2023.04.20 |
|---|---|
| 키와 값의 개별 목록에서 사전(딕트)을 만들려면 어떻게 해야 합니까? (0) | 2023.04.20 |
| 작업 스케줄러의 WPF 앱에서 DispatcherTimer와 일반 타이머의 비교 (0) | 2023.04.20 |
| Visual Studio에서 게시 프로파일을 저장하는 위치 (0) | 2023.04.20 |
| Swift Beta 퍼포먼스: 어레이 정렬 (0) | 2023.04.20 |