How to set max rows based on Datatable length in excel

Aspose.Cells Product Family
1

Hi All,

BasBed on Datatable values i have created the excel and converted to pdf, but i am getting one extra blank page in generated pdf file.

Below my code:
worksheet.Cells.ImportDataTable(dt, true, “A23”);
Aspose.Cells.Range range2 = cells.CreateRange(“A23”, Aspose.Cells.CellsHelper.CellIndexToName(cells.MaxDataRow, cells.MaxDataColumn));

                Aspose.Cells.Style st2 = workbook.CreateStyle();
                st2.IsTextWrapped = true;
                st2.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
                st2.Borders[Aspose.Cells.BorderType.TopBorder].Color = System.Drawing.Color.Black;
                st2.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
                st2.Borders[Aspose.Cells.BorderType.LeftBorder].Color = System.Drawing.Color.Black;
                st2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
                st2.Borders[Aspose.Cells.BorderType.BottomBorder].Color = System.Drawing.Color.Black;
                st2.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
                st2.Borders[Aspose.Cells.BorderType.RightBorder].Color = System.Drawing.Color.Black;
                Aspose.Cells.StyleFlag flg2 = new Aspose.Cells.StyleFlag();
                flg2.Borders = true;
                flg2.WrapText = true;                  
                range2.ApplyStyle(st2, flg2);

                Aspose.Cells.PdfSaveOptions saveOptions = new Aspose.Cells.PdfSaveOptions(Aspose.Cells.SaveFormat.Pdf);
               
                saveOptions.OptimizationType = Aspose.Cells.Rendering.PdfOptimizationType.MinimumSize;
                saveOptions.OutputBlankPageWhenNothingToPrint = true;
                worksheet.AutoFitRows();
                workbook.Save(desPath + "\\" + filename);
2

@knr,
We have tried this scenario by using a sample data table but could not observe any issue in the output PDF using the latest version Aspose.Cells for .NET 21.1. Could you please give it a try using the latest version and share the feedback? If still issue is there, you may share complete runnable solution for our reference along with the filled data table.

// Instantiating a "Products" DataTable object
DataTable dt = new DataTable("Products");

// Adding columns to the DataTable object
dt.Columns.Add("Product ID", typeof(Int32));
dt.Columns.Add("Product Name", typeof(string));
dt.Columns.Add("Units In Stock", typeof(Int32));

// Creating an empty row in the DataTable object
DataRow dr = dt.NewRow();

// Adding data to the row
dr[0] = 1;
dr[1] = "Aniseed Syrup";
dr[2] = 15;

// Adding filled row to the DataTable object
dt.Rows.Add(dr);

// Creating another empty row in the DataTable object
dr = dt.NewRow();

// Adding data to the row
dr[0] = 2;
dr[1] = "Boston Crab Meat";
dr[2] = 123;

// Adding filled row to the DataTable object
dt.Rows.Add(dr);
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
//worksheet.Cells.ImportDataTable(dt, true, "A23");
ImportTableOptions importTableOptions = new ImportTableOptions();
worksheet.Cells.ImportData(dt, 22,0, importTableOptions);
Cells cells = worksheet.Cells;
Aspose.Cells.Range range2 = cells.CreateRange("A23", Aspose.Cells.CellsHelper.CellIndexToName(cells.MaxDataRow, cells.MaxDataColumn));

Aspose.Cells.Style st2 = workbook.CreateStyle();
st2.IsTextWrapped = true;
st2.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
st2.Borders[Aspose.Cells.BorderType.TopBorder].Color = System.Drawing.Color.Black;
st2.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
st2.Borders[Aspose.Cells.BorderType.LeftBorder].Color = System.Drawing.Color.Black;
st2.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
st2.Borders[Aspose.Cells.BorderType.BottomBorder].Color = System.Drawing.Color.Black;
st2.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
st2.Borders[Aspose.Cells.BorderType.RightBorder].Color = System.Drawing.Color.Black;
Aspose.Cells.StyleFlag flg2 = new Aspose.Cells.StyleFlag();
flg2.Borders = true;
flg2.WrapText = true;
range2.ApplyStyle(st2, flg2);

//Aspose.Cells.PdfSaveOptions saveOptions = new Aspose.Cells.PdfSaveOptions(Aspose.Cells.SaveFormat.Pdf);
PdfSaveOptions saveOptions = new PdfSaveOptions();
saveOptions.OptimizationType = Aspose.Cells.Rendering.PdfOptimizationType.MinimumSize;
saveOptions.OutputBlankPageWhenNothingToPrint = true;
worksheet.AutoFitRows();
workbook.Save("output.pdf", saveOptions);

output.pdf (17.9 KB)