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,
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,
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,
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.
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,
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.