How can I not show grid lines if I don't have data when I export it to Excel

Aspose.Cells Product Family
,
1

GridDesktop has a grid display where there is data, and no grid display where there is no data.
How can I not show grid lines if I don’t have data when I export it to Excel.微信截图_20210125122658.png (16.2 KB)

2

@Diego2020,

Yes, this is default behavior of Aspose.Cells.GridDesktop. When importing Excel file into its grid matrix, it will show gridlines based on data range in the sheet. This behavior is included due to performance, so it will not insert those unnecessary empty cells. We will check if we can provide you some option or way around to accomplish your task. We will get back to you soon.

3

@Diego2020,

After further evaluation, we found you may easily cope with it using the following sample code for your reference:
e.g.
Sample code:

        private void Form16_Load(object sender, EventArgs e)
        { 
              ...........
              Aspose.Cells.GridDesktop.License license = new Aspose.Cells.GridDesktop.License();
            license.SetLicense(stringFilePath);

                         //handle the event - you may also it design time by double clicking on the Event name.
                         //this.gridDesktop1.FinishLoadFile += FinishLoadFile;


            gridDesktop1.ImportExcelFile("e:\\test2\\Book1.xlsx");  

       }


 //handle FinishLoadFile event
 private void gridDesktop1_FinishLoadFile(object sender, WorkBookEvents args)
        {
            //set your desired grid matrix
            gridDesktop1.GetActiveWorksheet().RowsCount = 10;
            gridDesktop1.GetActiveWorksheet().ColumnsCount = 10;
        }

Hope, this helps a bit.

5

Microsoft.Win32.SaveFileDialog dlgSave = new Microsoft.Win32.SaveFileDialog
{
Filter = “Excel工作簿(.xlsx)|.xlsx|Excel97-2003工作簿(.xls)|.xls|CSV(逗号分隔)(.csv)|.csv”,
DefaultExt = “”
};
if ((bool)dlgSave.ShowDialog())
{
foreach (Aspose.Cells.GridDesktop.Worksheet worksheet in _GridDesktop.Worksheets)
{
worksheet.RowsCount = worksheet.Cells.MaxRow + 1;
worksheet.ColumnsCount = worksheet.Cells.MaxColumn + 1;
}
_GridDesktop.ExportExcelFile(dlgSave.FileName);
//_Workbook.Save(dlgSave.FileName);
}

My code looks like this. It doesn’t work as expected.
I’ll still have grid lines in the Excel that I export.
The results are shown in the picture.
微信截图_20210201115613.png (15.6 KB)

6

@Diego2020,

Please create a simple sample WinForm project using latest Aspose.Cells.GridDesktop v21.1, zip the application and post us (with all the files), we will check it soon.

7

WindowsFormsApp1.zip (15.7 KB)

8

@Diego2020,

I tried to achieve your desired functionality in MS Excel manually but could not achieve it. I guess this is MS Excel (UI) behavior which always shows those blank rows/cols (with gridlines) as an Excel Workbook contains 1048576 rows and 16384 columns (till column XFD) in total. Could you share your expected file using MS Excel (manually) with steps details, we will check it on how to do it via Aspose.Cells.GridDesktop, we might log a ticket for it for investigation.

9

Thank you very much for your reply.
Now I export the Excel file and hand it over to the customer after manual processing.
I am looking forward to Aspose.Cells or Aspose.Cells.GridDesktop solving this problem and replacing my manual operation.

10

@Diego2020,

Yes, you may use Aspose.Cells and Aspose.Cells.GridDesktop for your needs. As I asked you before please share your expected file using MS Excel (manually) having limited grid matrix or gridlines with steps details, we will check it on how to do it via Aspose.Cells.GridDesktop efficiently.

11

Aspose.Cells.GridDesktop: When exporting to an Excel file, you want to display blank space where there is no data.
I used 21.4.0.0 version of the program and it still didn’t meet the requirements.
Attached is the program I tested.
Can you help me?
Program_frame.png (4.5 KB)
now.png (9.9 KB)
iwant.png (8.4 KB)
WindowsFormsApp1.zip (11.2 KB)

12

@Diego2020,

Please handle FinishLoadFile event to specify your desired area to be rendered (number of rows and columns). See the following lines of code to include to your application and try it.
e.g.
Sample code:

........
private void gridDesktop1_FinishLoadFile(object sender, Aspose.Cells.GridDesktop.WorkBookEvents args)
        {
            //set your desired grid matrix
            //gridDesktop1.GetActiveWorksheet().RowsCount = 10;
            gridDesktop1.Worksheets[0].RowsCount = 10;
            gridDesktop1.Worksheets[0].ColumnsCount = 10;
        }

Hope, this helps a bit.

If you still could not make it work, kindly do share your output Excel file (by Aspose.Cells.GridDesktop) and your desired output Excel file (by MS Excel), we will check it soon.

PS. please zip the Excel files prior attaching here.