Interop을 사용하여 Excel에서 비어 있지 않은 마지막 열 및 행 인덱스 가져오기
인터럽트 라이브러리를 사용하여 엑셀 파일에서 빈 행과 열을 모두 제거하려고 합니다.
저는 이 질문에 따라 인터op을 사용하여 Excel 파일에서 빈 행과 열을 제거하는 가장 빠른 방법을 선택했고 도움이 되었습니다.
그러나 데이터 집합은 작지만 빈 행과 열이 많은 Excel 파일이 있습니다(마지막 비공백 행(또는 열)에서 워크시트 끝까지).
행과 열 위에서 루프를 시도했지만 루프에 몇 시간이 걸립니다.
비어 있지 않은 마지막 행 및 열 인덱스를 가져오려고 합니다. 따라서 한 줄에서 전체 빈 범위를 삭제할 수 있습니다.
XlWks.Range("...").EntireRow.Delete(xlShiftUp)
참고: 모든 추가 빈칸을 제거하기 위해 데이터가 포함된 마지막 행을 가져오려고 합니다(이 행 또는 열 뒤).
좋은 의견이라도 있나?
참고: 코드가 SSIS 스크립트 작업 환경과 호환되어야 합니다.
업데이트 1
목표가 c#을 사용하여 Excel 데이터를 가져오는 것이라면 워크시트에서 가장 많이 사용된 인덱스(올린 이미지에서 Col = 10, Row = 16)를 식별했다고 가정하고 최대 사용된 인덱스를 문자로 변환하여 다음과 같이 할 수 있습니다.J16할 수 .OLEDBCommand
SELECT * FROM [Sheet1$A1:J16]
그렇지 않으면 더 빠른 방법을 찾기가 쉽지 않을 것 같습니다.
다음 문서를 참조하여 인덱스를 알파벳으로 변환하고 OLEDB를 사용하여 Excel에 연결할 수 있습니다.
초기 답변
당신이 다음 질문에서 시작했다고 말한 것처럼
그리고 "데이터가 들어 있는 마지막 행을 가져와 모든 추가 공백(이 행 또는 열 뒤)을 제거하려고 합니다."
따라서 수락 답변(@JohnG 제공)으로 작업하고 있다고 가정하면 마지막으로 사용한 행과 열을 얻기 위해 코드 줄을 추가할 수 있습니다.
Rows는 됩니다.rowsToDelete
다음 코드를 사용하여 마지막 빈 행보다 작은 인덱스를 가진 비어 있지 않은 마지막 행을 가져올 수 있습니다.
List<int> NonEmptyRows = Enumerable.Range(1, rowsToDelete.Max()).ToList().Except(rowsToDelete).ToList();
그리고 만약에NonEmptyRows.Max() < rowsToDelete.Max() 있지 않은 은 비어있않마막행은입니다.NonEmptyRows.Max()그렇지 않으면worksheet.Rows.Count마지막으로 사용한 행 뒤에는 빈 행이 없습니다.
비어 있지 않은 마지막 열을 가져오는 경우에도 동일한 작업을 수행할 수 있습니다.
코드는 가편위치된에서 됩니다.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();
}
}
몇 년 전에 저는 개발자가 워크시트에서 마지막으로 사용한 행과 열을 가져올 수 있는 MSDN 코드 샘플을 만들었습니다.저는 그것을 수정했고, 작업을 시연하기 위해 필요한 모든 코드를 윈도우 형태의 프론트엔드가 있는 클래스 라이브러리에 배치했습니다.
기본 코드는 Microsoft를 사용합니다.사무실. 인터럽트.훌륭합니다.
Microsoft One 드라이브의 위치 https://1drv.ms/u/s!AtGAgKKpqdWjiEGdBzWDCSCZAMaM
여기서 저는 엑셀 파일의 첫 번째 시트를 받고, 마지막으로 사용한 행과 색상을 받아 유효한 셀 주소로 제시합니다.
Private Sub cmdAddress1_Click(sender As Object, e As EventArgs) Handles cmdAddress1.Click
Dim ops As New GetExcelColumnLastRowInformation
Dim info = New UsedInformation
ExcelInformationData = info.UsedInformation(FileName, ops.GetSheets(FileName))
Dim SheetName As String = ExcelInformationData.FirstOrDefault.SheetName
Dim cellAddress = (
From item In ExcelInformationData
Where item.SheetName = ExcelInformationData.FirstOrDefault.SheetName
Select item.LastCell).FirstOrDefault
MessageBox.Show($"{SheetName} - {cellAddress}")
End Sub
데모 프로젝트에서 엑셀 파일의 모든 시트를 받아 목록 상자에 표시합니다.목록 상자에서 시트 이름을 선택하고 해당 시트의 마지막 행과 열을 유효한 셀 주소로 가져옵니다.
Private Sub cmdAddress_Click(sender As Object, e As EventArgs) Handles cmdAddress.Click
Dim cellAddress =
(
From item In ExcelInformationData
Where item.SheetName = ListBox1.Text
Select item.LastCell).FirstOrDefault
If cellAddress IsNot Nothing Then
MessageBox.Show($"{ListBox1.Text} {cellAddress}")
End If
End Sub
위의 링크에서 솔루션을 열면 코드가 많다는 것을 한눈에 알 수 있습니다.코드는 최적이며 모든 개체를 즉시 해제합니다.
저는 유용한 'Last Used Row' 및 'Last Used Column' 메서드가 있는 ClosedXml을 사용하고 있습니다.
var wb = new XLWorkbook(@"<path>\test.xlsx", XLEventTracking.Disabled);
var sheet = wb.Worksheet("Sheet1");
for (int i = sheet.LastRowUsed().RowNumber() - 1; i >= 1; i--)
{
var row = sheet.Row(i);
if (row.IsEmpty())
{
row.Delete();
}
}
wb.Save();
이 단순 루프는 38초 만에 10000개 행 중 5000개 행을 삭제했습니다.빠르지는 않지만 '시간'보다 훨씬 낫습니다.이는 물론 언급하지 않은 행/열 수에 따라 달라집니다.그러나 50000개 중 25000개의 빈 행을 추가로 테스트한 후 루프의 빈 행을 삭제하는 데 약 30분이 걸립니다.행을 삭제하는 것은 효율적인 프로세스가 아닙니다.
더 나은 해결책은 새 시트를 만든 다음 유지할 행을 복사하는 것입니다.
1단계 - 50000개의 행과 20개의 열이 있는 시트를 만들고 다른 모든 행과 열은 비어 있습니다.
var wb = new XLWorkbook(@"C:\Users\passp\Documents\test.xlsx");
var sheet = wb.Worksheet("Sheet1");
sheet.Clear();
for (int i = 1; i < 50000; i+=2)
{
var row = sheet.Row(i);
for (int j = 1; j < 20; j += 2)
{
row.Cell(j).Value = i * j;
}
}
2단계 - 데이터가 있는 행을 새 시트에 복사합니다.이 작업은 10초가 걸립니다.
var wb = new XLWorkbook(@"C:\Users\passp\Documents\test.xlsx", XLEventTracking.Disabled);
var sheet = wb.Worksheet("Sheet1");
var sheet2 = wb.Worksheet("Sheet2");
sheet2.Clear();
sheet.RowsUsed()
.Where(r => !r.IsEmpty())
.Select((r, index) => new { Row = r, Index = index + 1} )
.ForEach(r =>
{
var newRow = sheet2.Row(r.Index);
r.Row.CopyTo(newRow);
}
);
wb.Save();
3단계 - 열에 대해 동일한 작업을 수행합니다.
- 있지 않은 열 함수 비행어않열막마/를인가덱면 Excel 함
Find사용할 수 있습니다.»GetLastIndexOfNonEmptyCell. - 그런 다음 Excel 워크시트 함수
CountA셀이 비어 있는지 확인하고 전체 행/테이블을 하나의 행/테이블 범위로 결합하는 데 사용됩니다. - 이 범위는 한 번에 최종적으로 삭제됩니다.
public void Yahfoufi(string excelFile)
{
var exapp = new Microsoft.Office.Interop.Excel.Application {Visible = true};
var wrb = exapp.Workbooks.Open(excelFile);
var sh = wrb.Sheets["Sheet1"];
var lastRow = GetLastIndexOfNonEmptyCell(exapp, sh, XlSearchOrder.xlByRows);
var lastCol = GetLastIndexOfNonEmptyCell(exapp, sh, XlSearchOrder.xlByColumns);
var target = sh.Range[sh.Range["A1"], sh.Cells[lastRow, lastCol]];
Range deleteRows = GetEmptyRows(exapp, target);
Range deleteColumns = GetEmptyColumns(exapp, target);
deleteColumns?.Delete();
deleteRows?.Delete();
}
private static int GetLastIndexOfNonEmptyCell(
Microsoft.Office.Interop.Excel.Application app,
Worksheet sheet,
XlSearchOrder searchOrder)
{
Range rng = sheet.Cells.Find(
What: "*",
After: sheet.Range["A1"],
LookIn: XlFindLookIn.xlFormulas,
LookAt: XlLookAt.xlPart,
SearchOrder: searchOrder,
SearchDirection: XlSearchDirection.xlPrevious,
MatchCase: false);
if (rng == null)
return 1;
return searchOrder == XlSearchOrder.xlByRows
? rng.Row
: rng.Column;
}
private static Range GetEmptyRows(
Microsoft.Office.Interop.Excel.Application app,
Range target)
{
Range result = null;
foreach (Range r in target.Rows)
{
if (app.WorksheetFunction.CountA(r.Cells) >= 1)
continue;
result = result == null
? r.EntireRow
: app.Union(result, r.EntireRow);
}
return result;
}
private static Range GetEmptyColumns(
Microsoft.Office.Interop.Excel.Application app,
Range target)
{
Range result = null;
foreach (Range c in target.Columns)
{
if (app.WorksheetFunction.CountA(c.Cells) >= 1)
continue;
result = result == null
? c.EntireColumn
: app.Union(result, c.EntireColumn);
}
return result;
}
행/열의 빈 범위를 가져오는 두 가지 함수는 다음과 같은 하나의 함수로 리팩터링될 수 있습니다.
private static Range GetEntireEmptyRowsOrColumns(
Microsoft.Office.Interop.Excel.Application app,
Range target,
Func<Range, Range> rowsOrColumns,
Func<Range, Range> entireRowOrColumn)
{
Range result = null;
foreach (Range c in rowsOrColumns(target))
{
if (app.WorksheetFunction.CountA(c.Cells) >= 1)
continue;
result = result == null
? entireRowOrColumn(c)
: app.Union(result, entireRowOrColumn(c));
}
return result;
}
그리고 그냥 불러요.
Range deleteColumns = GetEntireEmptyRowsOrColumns(exapp, target, (Func<Range, Range>)(r1 => r1.Columns), (Func<Range, Range>)(r2 => r2.EntireColumn));
Range deleteRows = GetEntireEmptyRowsOrColumns(exapp, target, (Func<Range, Range>)(r1 => r1.Rows), (Func<Range, Range>)(r2 => r2.EntireRow));
deleteColumns?.Delete();
deleteRows?.Delete();
편집
마지막으로 사용한 셀 이후의 모든 셀 내용을 간단히 지우십시오.
public void Yahfoufi(string excelFile)
{
var exapp = new Microsoft.Office.Interop.Excel.Application {Visible = true};
var wrb = exapp.Workbooks.Open(excelFile);
var sh = wrb.Sheets["Sheet1"];
var lastRow = GetLastIndexOfNonEmptyCell(exapp, sh, XlSearchOrder.xlByRows);
var lastCol = GetLastIndexOfNonEmptyCell(exapp, sh, XlSearchOrder.xlByColumns);
// Clear the columns
sh.Range(sh.Cells(1, lastCol + 1), sh.Cells(1, Columns.Count)).EntireColumn.Clear();
// Clear the remaining cells
sh.Range(sh.Cells(lastRow + 1, 1), sh.Cells(Rows.Count, lastCol)).Clear();
}
데이터가 있는 마지막 코너 셀이 J16이라고 가정해 보겠습니다. 따라서 K열 이후 또는 아래쪽 17행에는 데이터가 없습니다.실제로 삭제하는 이유는 무엇입니까?시나리오는 무엇이고 무엇을 달성하기 위해 노력하고 있습니까?서식을 삭제하는 건가요?빈 문자열을 보여주는 공식을 지우는 것입니까?
어쨌든, 루프는 방법이 아닙니다.
아래 코드는 범위 개체의 지우기() 메서드를 사용하여 범위에서 모든 내용과 수식 및 형식을 지우는 방법을 보여줍니다.또는 삭제하려는 경우 Delete() 방법을 사용하여 한 번에 전체 직사각형 범위를 삭제할 수 있습니다.루프하는 것보다 훨씬 빠를 것입니다...
//code uses variables declared appropriately as Excel.Range & Excel.Worksheet Using Interop library
int x;
int y;
// get the row of the last value content row-wise
oRange = oSheet.Cells.Find(What: "*",
After: oSheet.get_Range("A1"),
LookIn: XlFindLookIn.xlValues,
LookAt: XlLookAt.xlPart,
SearchDirection: XlSearchDirection.xlPrevious,
SearchOrder: XlSearchOrder.xlByRows);
if (oRange == null)
{
return;
}
x = oRange.Row;
// get the column of the last value content column-wise
oRange = oSheet.Cells.Find(What: "*",
After: oSheet.get_Range("A1"),
LookIn: XlFindLookIn.xlValues, LookAt: XlLookAt.xlPart,
SearchDirection: XlSearchDirection.xlPrevious,
SearchOrder: XlSearchOrder.xlByColumns);
y = oRange.Column;
// now we have the corner (x, y), we can delete or clear all content to the right and below
// say J16 is the cell, so x = 16, and j=10
Excel.Range clearRange;
//set clearRange to ("K1:XFD1048576")
clearRange = oSheet.Range[oSheet.Cells[1, y + 1], oSheet.Cells[oSheet.Rows.Count, oSheet.Columns.Count]];
clearRange.Clear(); //clears all content, formulas and formatting
//clearRange.Delete(); if you REALLY want to hard delete the rows
//set clearRange to ("A17:J1048576")
clearRange = oSheet.Range[oSheet.Cells[x + 1, 1], oSheet.Cells[oSheet.Rows.Count, y]];
clearRange.Clear(); //clears all content, formulas and formatting
//clearRange.Delete(); if you REALLY want to hard delete the columns
비어 있지 않은 마지막 행과 열을 찾을 수 있어야 합니다.
with m_XlWrkSheet
lastRow = .UsedRange.Rows.Count
lastCol = .UsedRange.Columns.Count
end with
VB입니다.NET, 하지만 어느 정도는 효과가 있을 것입니다.그러면 16행과 10열이 반환됩니다(위 사진을 기준으로).그런 다음 이 값을 사용하여 한 줄에서 모두 삭제할 범위를 찾을 수 있습니다.
Microsoft에서 문제를 해결한 것 같습니다.Range(범위)를 살펴봅니다.CurrentRegion 특성 - 빈 행과 빈 열의 조합으로 경계가 지정된 범위를 반환합니다.한 가지 불편한 점이 있습니다. 보호된 워크시트에서 이 속성을 사용할 수 없습니다.
자세한 내용은 다음을 참조하십시오.VBA 매크로를 사용하여 Excel에서 현재 영역, 사용된 범위, 마지막 행 및 마지막 열을 찾는 방법
일부 SO 멤버들은 UsedRange 속성에 대해 언급했는데, 이는 유용할 수도 있지만, 이와는 다릅니다.CurrentRegion그것은UsedRange사용된 모든 셀을 포함하는 범위를 반환합니다.
그래서, 만약 당신이 원한다면,LAST(row)그리고.LAST(column)데이터에 사용되는 End 속성을 사용해야 합니다.XlDirection:xlToLeft및/또는xlUp.
참고 #1:
데이터가 표 형식이면 다음을 사용하여 마지막 셀을 쉽게 찾을 수 있습니다.
lastCell = yourWorkseet.UsedRange.End(xlUp)
firstEmtyRow = lastCell.Offset(RowOffset:=1).EntireRow
참고 #2:
데이터가 표 형식이 아닌 경우 행과 열 집합을 반복하여 비어 있지 않은 마지막 셀을 찾아야 합니다.
행운을 빕니다.
레인지를 사용해 볼 수 있을 것 같습니다.
Application excel = new Application();
Workbook workBook= excel.Workbooks.Open("file.xlsx")
Worksheet excelSheet = workBook.ActiveSheet;
Range excelRange = excelSheet.UsedRange.Columns[1, Missing.Value] as Range;
var lastNonEmptyRow = excelRange.Cells.Count;
위의 코드는 저에게 적합합니다.
언급URL : https://stackoverflow.com/questions/43910117/get-last-non-empty-column-and-row-index-from-excel-using-interop
'programing' 카테고리의 다른 글
| MongoDB에서 중첩 인덱스를 만드는 방법은 무엇입니까? (0) | 2023.05.14 |
|---|---|
| Postgres가 서버에 연결할 수 없습니다. (0) | 2023.05.14 |
| .on('click')과 .click()의 차이 (0) | 2023.05.14 |
| VB.NET: 시스템에서 확장 메서드를 사용할 수 없습니다.객체 인스턴스 (0) | 2023.05.14 |
| 사전 저장 콜백에서 레코드가 "새로 작성되었는지" 확인 (0) | 2023.05.14 |