We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

worksheet.Cells.DeleteBlankRows() method is cutting the text

Aspose.Cells Product Family
, ,
#1

Hello,

I am using worksheet.Cells.DeleteBlankRows() method to delete the blank rows from the excel sheet, but its cutting some of the text. Please note that we are using Aspose total license version 21.3.0

My sample code is as below.

                string dataDir = System.IO.Path.GetFullPath(@"..\..\");
                Aspose.Cells.License license = new Aspose.Cells.License();
                license.SetLicense(dataDir + "\\Aspose.Total.lic");
                var loadOptions = new LoadOptions();
                loadOptions.CheckExcelRestriction = false;
                Workbook workbook = new Workbook(pInFile, loadOptions);
                Aspose.Cells.PdfSaveOptions pdfSaveOptions = new Aspose.Cells.PdfSaveOptions();
                // option to set all the columns of excel in one page.
                pdfSaveOptions.AllColumnsInOnePagePerSheet = true;
                pdfSaveOptions.MergeAreas = true;
                /* Retain the structure of original excel */
                pdfSaveOptions.ExportDocumentStructure = true;
                /* Formula calculation for any digit formulla applied in excels */
                /*it is best to call Workbook.CalculateFormula() just before rendering the spreadsheet to PDF. 
               * This ensures  that the formula dependent values are recalculated, and the correct
               * values are rendered in the PDF.*/
                workbook.CalculateFormula();
                /* For some sheet, print area is applied, to display all the data in pdf, 
                 * below setting is needed */
                foreach (Worksheet wks in workbook.Worksheets)
                {
                    foreach (Cell cell in wks.Cells)
                    {
                        Style style = cell.GetStyle();
                        /* if cell is numeric, consider its category type as numeric and update the format */
                        if (cell.Type == CellValueType.IsNumeric &&
                            cell.NumberCategoryType != NumberCategoryType.Number)
                        {
                            style.SetCustom("#,##0.00", true);
                        }
                        style.VerticalAlignment = TextAlignmentType.Center;
                        /* set fonts */
                        style.Font.Name = "Helvetica";
                        cell.SetStyle(style);
                    }
                    wks.PageSetup.PrintArea = "";
                    wks.PageSetup.BottomMargin = 1;
                    wks.PageSetup.LeftMargin = 1;
                    wks.PageSetup.RightMargin = 1;
                    wks.PageSetup.TopMargin = 1;
                   
                    wks.Cells.DeleteBlankRows();
                    wks.AutoFitColumns();

                }
                workbook.Save(pOutFile, pdfSaveOptions);

Please find original excel and converted pdf in attachment. Thank you.1538040656_1.zip (156.8 KB)

#2

@Vaidehi123,

The issue is due to the picture on top. Please note, DeleteBlankRows method also removes the blank rows in b/w data in the sheet. For your information, the images are stored/displayed independently in the sheet. So when you remove blank rows in b/w your data/text, the picture will overlap on the text a bit. So, some text goes behind the image. For this case, you do not need to use the method to remove blank rows. In any case if you want to blank rows, you should remove rows or the text after your data row (i.e., after row 16) using specified method, this will work for other such cases/sheets.

#3

@Amjad_Sahi - Understood. We have a lot of document and format is not fixed for any document as it may have different locations for image. So, it seems custom logic is not feasible and we require to use DeleteBlankRows() method. Do we have any method/solution where it can exclude image from document irrespective of its location?

#4

@Vaidehi123,

Please note, DeleteBlankRows method does not do anything with images. The main thing is if there are images and text is around the image(s) with some blank rows. Now when you call the method, it will remove those blank rows in between data. Consequently, the image overlaps automatically and data goes behind the image frame/boundary, data/text is hidden behind the image.

#5

@Amjad_Sahi Got it, Thank you.

#6

@Vaidehi123,

You are welcome.