Deleting Empty Column

Aspose.Cells Product Family
1

Hi Everyone,

Unable to delete the extra columns from the web
grid. I am importing an excel into my web grid. The excel sheet has 6
worksheets. Each worksheet has more than 2 columns. The following line
of codes from the source codes.

WebWorksheet wrksht = grdWeb.WebWorksheets[grdWeb.ActiveSheetIndex];
wrksht.SetReadonlyRange(0, 0, wrksht.Cells.MaxRow + 1, 1);
int colcount = wrksht.Cells.MaxColumn;
int rowcount = wrksht.Cells.MaxRow;

WebCell cell;
for (int i = 0; i < colcount; i++)
{
cell = wrksht.Cells[0, i];
if (cell.StringValue != “”)
{
wrksht.Cells.SetColumnCaption(i, cell.StringValue);
}
else
{
wrksht.Cells.DeleteColumn(i);
}
}


Or is there anything such - the grid should at least have minimum
number of X columns in case. Where I need only 2 columns to display I
can see there arbitrary number of columns and I don’t really need
those. Any help will be appreciated.


Moreover, If there is any ways we can delete the cells having null values in Aspose grid?

I
have one more issue with this code is that. The MaxColumn property
doesn’t show the right value always. All the worksheets from the excel
have more than 2 cols. When I click particular worksheet having 2 cols
I can see in the quick watch, the value of MaxColumn is 1. Where else
it should have more than that because I can see few extra empty columns
on that sheet.

2

Hi,

Well, Cells.MaxColumn and Cells.MaxRow provide the maximum/farthest column/row indexes which contain value or data. For your info, when the line i.e.., wrksht.Cells.DeleteColumn(i); is processed all the columns are shifted on level to left (this is same with MS Excel), it might possible that valid column indexes are not retrieved (to either delete the column or set column caption by the value string accordingly) fine, so, there is a possibility the resultant matrix won't be ok as you thought.

I think you may try to modify the loop (higher to lower value) a bit to check if it works fine for your scenario, I have tested and it works fine for a simple case, e.g.,

WebWorksheet wrksht = grdWeb.WebWorksheets[grdWeb.ActiveSheetIndex];
wrksht.SetReadonlyRange(0, 0, wrksht.Cells.MaxRow + 1, 1);
int colcount = wrksht.Cells.MaxColumn;
int rowcount = wrksht.Cells.MaxRow;

WebCell cell;
for (int i = colcount; i >= 0; i--)
{
cell = wrksht.Cells[0, i];
if (cell.StringValue != "")
{
wrksht.Cells.SetColumnCaption(i, cell.StringValue);
}
else
{
wrksht.Cells.DeleteColumn(i);
}
}

Thank you.

3

Thanks for your time and tips.

As, MaxColumn counts only the number of cols having some values, how can we make the extra columns on the right side read only.

Okay, there must be some way to make the other empty cols to be read only. So that users cannot enter any value inside those cells of the extra-columns. I look forward to your reply.

Or any possible solution to avoid such entries by the user. Your help will be appreciated.

Thanks and Regards

Prithiraj Sengupta

4

Hi Prithiraj,

Please try the following code:

if (!IsPostBack)

{

gridWeb.WebWorksheets.Clear();

gridWeb.WebWorksheets.ImportExcelFile(@"c:\test.xls");

WebWorksheet sheet = gridWeb.WebWorksheets[0];


int maxRow = Math.Max(7, sheet.Cells.MaxRow);

int maxCol = sheet.Cells.MaxColumn;

if (maxCol < 7)

{

sheet.SetReadonlyRange(0, maxCol + 1, maxRow + 1, 7 - maxCol);

}

}

The GridWeb control will render 8 rows and 8 columns at least if the active worksheet is not in binding mode. So, if the sheet.Cells.MaxColumn is less than 7(column index), there are some empty columns.

Thank you.