Free Support Forum - aspose.com

Deleting blank rows from an xls file

is it possible to delete blank rows from a worksheet without having to iterate every row and column to check if ther eis data.

even with that if the cell does not have any string and just a style or formatting, it still says there is something there.

the reason for this is we try to convert to pdf and if there are blank rows, there turn out to be tons of blank pages.

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Well, we are not very clear about the problem you are facing. Please try the attached latest version and try your scenario again. If with the latest version you still face any problem, please provide us your template file, source code and explain the process you want to perform in a bit detail and we will look into it.

Thank you & Best Regards,

Hi

I do not have any source as i cant find a specific method to do waht i need.

here is some information

I have an excel that has content all the way to 65000 row

many rows in between are blank. when i try to print this document i land up with nearly 13000 papes to print as it prints all the blank rows too.

i would like to know if there is any way to find all the blank rows and delete them before we print. this way we are not printing that may pages.

i did try with looping through each used row colum. but it is extremely slow to go that way

here is what i used. im hoping there is a more efficient and faster way to find and delete unused rows.

For Each wksheet In wkbook.Worksheets

Dim rows As Int64 = wksheet.Cells.MaxDataRow

For i As Int32 = rows To 0 Step -1

Dim hasdata As Boolean = False

Console.Write(backspace & wksheet.Name & ": Row " & i + 1 & " of " & (rows + 1).ToString())

For j As Int32 = 0 To wksheet.Cells.MaxDataColumn

If Not IsNothing(wksheet.Cells(i, j).Value) Then

hasdata = True

Exit For

End If

Next

If hasdata = False Then

wksheet.Cells.DeleteRow(i)

End If

Next

Console.WriteLine("")

Next

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Can you please save your file as XLS and send us the Excel file. We will test your issue and provide you with a better solution. You can use Workbook.Save(filename,FileFormatType.Excel2003) to save your file in Excel 2003 file format.

Thank you & Best Regards,

here is a sample

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for Considering Aspose.

Please try attached fix and try the following codes to delete empty rows.

Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\ir65536.xls");
Cells cells = workbook.Worksheets[0].Cells;
int preRow = -1;
for(int i =cells.Count - 1; i >= 0 ; i--)
{
Cell cell = cells[i];
if (cell.Type != CellValueType.IsNull)
{
if (preRow == -1)
{
preRow = cell.Row;
continue;
}
if (preRow != cell.Row)
{
if (preRow - cell.Row > 1)
{
cells.DeleteRows(cell.Row + 1, preRow - cell.Row - 1);
}
preRow = cell.Row;
}
}
}
if (preRow != 0)
{
cells.DeleteRows(0, preRow);
}

Thank you & Best Regards,

unfortunately the soultion provided by you is deleting all the data in the rows as well.

the idea here it to just leave the rows that have data.

especially when trying to print. just for sample, open the excel file. go to print preview, it shows some 13000 pages to print since there is data on row 65536 and colum IR

we need to delete all rows that do not have any data so that when you preview for print it shows one page.

this is very important and really is possible using a simple macro in excel. where they work with usedrange. i can provide the macro if need.

In most legal industries, the end result of these native excel files is to print to Tif format. and when printing excel files like the one i provided we get 13000 tif pages and most of them are blank.

any help in solving this issue will be greatly appreciated

Hi,


Thank you for Considering Aspose.


Please try the latest fixed attached in my previous post and we have updated the code a bit, so hopefully it will help you get your desired results.

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Please try the following code,


Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\ir65536.xls");
Cells cells = workbook.Worksheets[0].Cells;
if (cells.Count > 0)
{
int preRow = -1;
Cell cell = cells[cells.Count - 1];
if (cell.Type == CellValueType.IsNull)
{
preRow = cell.Row + 1;
}
for (int i = cells.Count - 1; i >= 0; i--)
{
cell = cells[i];
if (cell.Type != CellValueType.IsNull)
{
if (preRow == -1)
{
preRow = cell.Row;
continue;
}
if (preRow != cell.Row)
{
if (preRow - cell.Row > 1)
{
cells.DeleteRows(cell.Row + 1, preRow - cell.Row - 1);
}
preRow = cell.Row;
}
}
}
if (preRow > 0)
{
cells.DeleteRows(0, preRow);
}
}

If you still face any problem, please mark the cells in you template file which gets deleted while executing this code, we will figure it out ASAP.

Thank you & Best Regards,

the only way i could get it working was to use deleterange.

here is the code i used

the only problem here is im trying to use the clearrange method to remove the formatting from the range before deleting it, doesnt sem to work. as u can see there is a yellow format on the first column on all the rows, which also needs to cleared before removing the rows. can u help as to why that is not working?

If objcells.Count > 0 Then

Dim preRow As Int64 = -1

'Console.Write(backspace & "Row " & Format((inti + 1), "###########0") & " of " & Format(objcells.Count, "###########0"))

Dim objCell As Cell = objcells(objcells.Count - 1)

If objCell.Type = CellValueType.IsNull Then

preRow = objCell.Row + 1

End If

For i As Int32 = objcells.Count - 1 To 0 Step -1

Console.Write(backspace & "Row " & Format((i + 1), "###########0") & " of " & Format(objcells.Count, "###########0"))

objCell = objcells(i)

If objCell.Type <> CellValueType.IsNull Then

If (preRow = -1) Then

preRow = objCell.Row

End If

If (preRow <> objCell.Row) Then

If (preRow - objCell.Row > 1) Then

objcells.ClearRange((objCell.Row + 1), 0, CInt(objCell.Row + 1) + CInt(preRow - objCell.Row - 1) - 1, objcells.MaxDataColumn + 1)

DelRows.Add(objCell.Row + 1 & "**" & preRow - objCell.Row - 1)

End If

End If

preRow = objCell.Row

End If

Next

If (preRow <> 0) Then

objcells.DeleteRows(0, preRow)

End If

End If

For Each strdel As String In DelRows

Dim src As String() = Split(strdel, "**")

objcells.DeleteRange((src(0)), 0, CInt(src(0)) + CInt(src(1)) - 1, objcells.MaxDataColumn + 1, ShiftType.Up)

Next

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

We have added Cells.DeleteBlankRows() and Cells.DeleteBlankColumns() methods to delete blank rows and columns.

Please try the attached latest fix V4.5.1.20.

Thank You & Best Regards,

Hi Aslam,

Is there any possibility to delete the blank rows and columns using Java.

I want to delete blank rows , if the Excel sheet have more than 3 blank rows continuously.

Regards

Janakiraman

Hi,

Thank you for considering Aspose.

I am afraid, currently there is no API as deleteBlankRows()/deleteBlankColumns() in Aspose.Cells for Java. We will try to support it in our future version.

Thank You & Best Regards,

Hi,

Please download new version/fix Aspose.Cells for Java (Latest Version) for deleting blank rows and columns for java version of the product:

Delete Blank Rows and Columns in a Worksheet

Thank you.

The issues you have found earlier (filed as CELLSJAVA-8458) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.