Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Tue Nov 14, 2017 8:39 pm

FOR COLUMN B I like to calculate the last filled row or last row.
So I can do
sheet.Range["B4:B"+ last row].NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM";

mguha
 
Posts: 11
Joined: Thu Nov 09, 2017 8:54 pm

Wed Nov 15, 2017 2:54 am

Hi mguha,

Thanks for your inquiry.
Generally, when we talk about "last row", it means the last row of a worksheet. If you want to access the last filled row of a certain column, you need to traverse the cells of this column and do the judgment. Please refer to the code below.
Code: Select all
 string xlsPath = @"sample.xlsx";
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(xlsPath);
            Worksheet sheet = workbook.Worksheets[0];
            // sheet.LastRow returns the last row of the sheet.
            int lastFilledRow = sheet.LastRow;
            for (int i = sheet.LastRow; i >=0; i--)
            {
                CellRange cr =sheet.Rows[i-1].Columns[1];
                if (!cr.IsBlank)
                {
                    lastFilledRow =i ;
                    break;
                }
            }
            //to find the last filled row of this column
            sheet.Range["B4:B"+ lastFilledRow ].NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM";
            workbook.SaveToFile("12153.xlsx",ExcelVersion.Version2013);


Sincerely,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Wed Nov 15, 2017 3:24 pm

thanks that worked

mguha
 
Posts: 11
Joined: Thu Nov 09, 2017 8:54 pm

Thu Nov 16, 2017 1:03 am

Hello,

Thanks for your feedback.
Please feel free to contact us if you need any assistance.

Sincerely,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Return to Spire.XLS