Exporting DataGridView records to Excel-CodePudding

I have a datagridview with several columns and different report types, where I have a method to export the records to Excel spreadsheet, in this datagridview I leave some columns like: visible = false according to the selected report type.

In the export method for spreadsheet I have a validation to consider only visible cells, true but it is not working.

        int XLRow = 1;
        int XLCol = 1;

        // Export header
        for (int i = 0; i < datagrid.Columns.Count; i  )
        {
            if (datagrid.Columns[i].Visible == true)
                xlWorkSheet.Cells[XLRow, XLCol  ] = datagrid.Columns[i].HeaderText;
        }

        XLRow = 2;
        XLCol = 1;

        // Controls for scrolling through records do DataGridView
        for (int i = 0; i < datagrid.RowCount; i  )
        {
            for (int j = 0; j < datagrid.ColumnCount; j  )
            {
                DataGridViewCell cell = datagrid[j, i];
                string conteudo = string.Empty;
                if ((cell.Value != null) && (!string.IsNullOrEmpty(cell.Value.ToString())) && cell.Visible == true)
                {
                    conteudo = cell.Value.ToString();
                    if ((Funcoes.EhNumerico(conteudo)) && (conteudo.Length > 8))
                    {
                        conteudo = string.Concat("'", conteudo);
                    }
                    xlWorkSheet.Cells[XLRow, XLCol  ] = conteudo;
                }
                XLRow  ;
                XLCol = 1;
            }
        }

The spreadsheet leaves with the columns that are visible = false in white, as follows:

How can I resolve this?

CodePudding user response：

In other words… in the first for loop… if the column is not visible… then you DO NOT want to increment i… and this is obviously going to mess up the for loop. Hence my suggestion to create two (2) int variables… int XLRow and int XLColumn, then use those indexes specifically for the WORKSHEET. Then loop through the grid columns as your code does using i and j, however, when a column is found that is not visible, then you DO NOT want to increment the XLCol index.

It will be a challenging juggling the loops i or j variables to also be used as an index into the worksheet columns as they may be completely “different” indexes. This is why I say “separate” them from the git go and keep it simple. Something like…

int XLRow = 1;
int XLCol = 1;
for (int i = 0; i < datagrid.Columns.Count; i  ) {
  if (datagrid.Columns[i].Visible == true)
    xlWorkSheet.Cells[XLRow, XLCol  ] = datagrid.Columns[i].HeaderText;
}
XLRow = 2;
XLCol = 1;
for (int i = 0; i < datagrid.RowCount; i  ) {
  for (int j = 0; j < datagrid.ColumnCount; j  ) {
    if (datagrid.Columns[j].Visible) {
      DataGridViewCell cell = datagrid[j, i];
      string conteudo = string.Empty;
      if ((cell.Value != null) && (!string.IsNullOrEmpty(cell.Value.ToString()))) {
        conteudo = cell.Value.ToString();
        if ((Funcoes.EhNumerico(conteudo)) && (conteudo.Length > 8)) {
          conteudo = string.Concat("'", conteudo);
        }
        xlWorkSheet.Cells[XLRow, XLCol  ] = conteudo;
      }
    }
  }
  XLRow  ;
  XLCol = 1;
}