exporting only visible datagridview columns to excel

Question

0.00/5 (No votes)

See more:

Need help on exporting only visible DataGridView columns to excel, I have this code for hiding columns in DataGridView.

C#

this.dg1.Columns[0].Visible = false;

And then I have button click event for exporting to excel.

C#

// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

// see the excel sheet behind the program
app.Visible = true;

// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;


// changing the name of active sheet
worksheet.Name = "PIN korisnici";

// storing header part in Excel
for (int i = 1; i < dg1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dg1.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dg1.Rows.Count - 1; i++)
{
for (int j = 0; j < dg1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dg1.Rows[i].Cells[j].Value.ToString();
}
}

But I want to export only visible columns, while I get all of them, anyone, help on this. :)

Posted 25-Nov-11 0:44am

shonezi

Updated 25-Nov-11 0:47am

RaisKazi

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wayne Gaylard · Answer 1 · 2011-11-25T00:57:00

Solution 1

You should do a check first to see if the row is visible first like this

C#

// storing Each row and column value to excel sheet
            for (int i = 0; i < dg1.Rows.Count - 1; i++)
            {
                if (dg1.Rows[i].Visible)
                {
                    for (int j = 0; j < dg1.Columns.Count; j++)
                    {
                        worksheet.Cells[i + 2, j + 1] = dg1.Rows[i].Cells[j].Value.ToString();
                    }
                }
            }

Hope this helps

Posted 25-Nov-11 0:57am

Wayne Gaylard

Comments

shonezi 25-Nov-11 7:07am

I tried but its not working

Wayne Gaylard 25-Nov-11 7:11am

Did you get an exception or what?

shonezi 25-Nov-11 7:20am

no, nothing, I do an export but those columns that I made invisible
are exported also

Wayne Gaylard 25-Nov-11 7:36am

Put a breakpoint in the code where you check if the row is visible and check if it return true always.

jocherry · Answer 2 · 2013-11-12T17:14:00

Try this code

for (iC = 0; iC <= colsTotal; iC++)
{
if (dgvstocklist.Columns[iC].Visible)
{
_with1.Cells[1, iC + 1].Value = dgvstocklist.Columns[iC].HeaderText;
}

}
for (I = 0; I <= rowsTotal - 1; I++)
{ for (j = 0; j <= colsTotal; j++)
{
if (dgvstocklist.Columns[j].Visible)
{
_with1.Cells[I + 2, j + 1].value = dgvstocklist.Rows[I].Cells[j].Value;
}

}
}

And put the Columns (whatever you want to hide) at last in the select command,and make those columns invisible.

vinay.Singh · Answer 3 · 2013-11-12T19:52:00

Solution 4

Hi have a look of the below link

http://www.dotnetpools.com/2012/10/how-to-export-data-from-gridview-to.html[^]

http://www.dotnetpools.com/2012/09/gridview-export-to-excel-in-aspnet-c.html[^]

Posted 12-Nov-13 19:52pm

vinay.Singh

Manisha Tambade · Answer 4 · 2011-11-25T02:17:00

You make one manual dataSet for invisible columns as no.(number) and invisible column's Name.
loop to grids visible false columns and add those in dataset.

Now export all grid data to Excel.
After completion of excel writing,do formating to excel-do loop to above dataset
and do those excels columns as visible false.

if it helps then accept else reply.

exporting only visible datagridview columns to excel

4 solutions

Solution 1

Solution 3

Solution 4

Solution 2

Add your solution here

Preview 0