Microsoft Dynamics AX (Archived)

find the last row of excel sheet - dynamics AX 2009

(0) Share

Report

Posted on by Community Member

How can I find the last row of excel sheet - dynamics AX 2009?

Object SysExcelSheet ... (Excel objects).

Thanks :)

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

MukeshHirwani 1,180 on at

Like (0)

Report

static void ReadExcel(Args _args)

{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row;
FileName filename;

;

application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = "C:\\item.xls";
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}

workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
do
{
row++;
type = cells.item(row+1, 1).value().variantType();
if(type == COMVariantType::VT_EMPTY)
info(strfmt("Last row %1", row));
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I there any chance that the question type == COMVariantType::VT_EMPTY happens on the middle of the workshhet (suppose there is blank line on the middle).

And second thing - should I search the whole sheet (until I reach the last ... there is possibility of lot of records, and this involves time cosuming issue ...)

Thanks :)

Was this reply helpful? Yes No
Suggested answer

MukeshHirwani 1,180 on at

Like (0)

Report

Hi,

U can add the logic for figuring out couple of blank rows, say about 10 blank rows occurred then u break the read from excel.

No clues about tht kind of logic in X++, rather u can create c# code which does performs same thing.

int columns;

int rows;

Microsoft.Office.Interop.Sheet sheet;

sheet = Globals.ThisWorkbook.ActiveSheet as Microsoft.Office.Interop.Sheet;

if (sheet != null)

{

int NumberOfColumns = sheet.Cells.Find("*", Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlPrevious, false, false, Type.Missing).Column;

}

Follow attached URL to create, deploy and call C# code in AX 2009:

blogs.solidq.com/.../post.aspx

Was this reply helpful? Yes No
Verified answer

PA-22040759-0 6,194 on at

Like (0)

Report

The ExcelHelper class from this blog post does the trick: natepaine.blogspot.dk/.../excel-helper-class-for-ax.html.

I have used this in a current implementation.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

There is much more efficient way to get this than looping through all cells until the end – using Excel’s special cells, and returning the value of last used row. It is fast enough to be used e.g. to get the maximum number of rows and initialize the progress bar, before processing the whole Excel document.

Below is the code that returns the row number of the last cell in Excel document, and for more information you can search MSDN for SpeciallCells method in Excel Developer Reference.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

// Created on 07 Mar 2013 by Jovan Bulajic

// Returns the last used row in Excel file, using the Excel's Special Cells

static int Excel_LastRow(Filename excelFile = @"C:\Temp\Testw.xls")

{

SysExcelApplication excelApp;

SysExcelWorkbooks excelWorkBooks;

SysExcelWorksheet excelWorkSheet;

SysExcelCells excelCells;

COM excelCOM;

int lastRow;

;

// Initialize Excel, open the first worksheet and get the last row information

excelApp = SysExcelApplication::construct();

excelWorkBooks = excelApp.workbooks();

try

{

excelWorkbooks.open(excelFile);

}

catch (Exception::Error)

{

throw error(strFmt("Error opening Excel document (%1).", excelFile));

}

excelWorkSheet = excelWorkBooks.item(1).worksheets().itemFromNum(1);

excelCells = excelWorkSheet.cells();

excelCOM = excelCells.comObject();

excelCOM = excelCOM.SpecialCells(11); // 11 = xlCellTypeLastCell

lastRow = excelCOM.Row(); // Row for the last cell

excelApp.quit();

Box::info(strFmt("Last used row in Excel file is %1.", lastRow));

return lastRow;

}

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hey Jovan Bulajic,

this code works fine in my program but i change the records in my excel sheet the lastRow dont refresh the value.

Example:

I had a output in a SysOperationProgress Bar.

Record *current series* of *lastRow*

I change the rows from 700 to 39 but the variable lastRow stand on 700.

I reset lastRow at the end of my code

lastRow = 0;

but the stand on 700.

Need solutions.

Thanks for any help.

Felix

Was this reply helpful? Yes No