Jump to content

excel + gridview winforms


diogom
 Share

Recommended Posts

Olá,

Tenho uma grid q nas 2 primeiras colunas tenho uma checkbox e um botao.

Quero passar a informação q esta na grid para o excel, excepto a checkbox e o botao. Alias, se ficar da me erro.

if (txtexcel.Text != string.Empty)
	 {
		 if (dataGridView1.Rows.Count > 0)
		 {
			 try
			 {
				 //dataGridView1.Columns[0].Visible = false;
				 //dataGridView1.Columns[1].Visible = false;
				 // Bind Grid Data to Datatable
				 DataTable dt = new DataTable();
				 foreach (DataGridViewColumn col in dataGridView1.Columns)
				 {
					 dt.Columns.Add(col.HeaderText, col.ValueType);
				 }
				 int count = 0;
				 foreach (DataGridViewRow row in dataGridView1.Rows)
				 {
					 if (count < dataGridView1.Rows.Count - 1)
					 {
						 dt.Rows.Add();
						 foreach (DataGridViewCell cell in row.Cells)
						 {
							 dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
						 }
					 }
					 count++;
				 }
				 // Bind table data to Stream Writer to export data to respective folder
				 StreamWriter wr = new StreamWriter(@"C:/Users/Diogo/Documents/visual studio 2015/Projects/CJProj/CJProj/App_Data/" + txtexcel.Text + ".xls");
				 // Write Columns to excel file
				 for (int i = 2; i < dt.Columns.Count; i++)
				 {
					 wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
				 }
				 wr.WriteLine();
				 //write rows to excel file
				 for (int i = 0; i < (dt.Rows.Count); i++)
				 {
					 for (int j = 0; j < dt.Columns.Count; j++)
					 {
						 if (dt.Rows[i][j] != null)
						 {
							 wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
						 }
						 else
						 {
							 wr.Write("\t");
						 }
					 }
					 wr.WriteLine();
				 }
				 wr.Close();
				 //dataGridView1.Columns[0].Visible = true;
				 //dataGridView1.Columns[1].Visible = true;
				 MessageBox.Show("Dados exportados com sucesso");
			 }
			 catch (Exception ex)
			 {
				 Log.Save("Erro inesperado: " + ex.Message + "", Util, "", "", 0);
			 }
		 }
	 }
	 else
	 { MessageBox.Show("Colocar nome no excel"); }
Link to comment
Share on other sites

Olá.

Não sei se responde a tua questão, mas o que tens que fazer é validar se o tipo da coluna que estás a iterar é do tipo checkbox ou botão, se for, saltas e não adicionas à datatable.

if (textBox1.Text != string.Empty)
	 {
		 if (dataGridView1.Rows.Count > 0)
		 {
			 try
			 {
				 //dataGridView1.Columns[0].Visible = false;
				 //dataGridView1.Columns[1].Visible = false;
				 // Bind Grid Data to Datatable
				 DataTable dt = new DataTable();
				 foreach (DataGridViewColumn col in dataGridView1.Columns)
				 {
					 Type tp = col.CellType;
					 if(tp == typeof(DataGridViewCheckBoxCell) || tp == typeof(DataGridViewButtonCell))
					 {
						 continue;
					 }
					 else
					 {
						 dt.Columns.Add(col.HeaderText, col.ValueType);
					 }
				 }
				 int count = 0;
				 foreach (DataGridViewRow row in dataGridView1.Rows)
				 {
					 if (count < dataGridView1.Rows.Count - 1)
					 {
						 dt.Rows.Add();
						 foreach (DataGridViewCell cell in row.Cells)
						 {
							 dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
						 }
					 }
					 count++;
				 }
				 // Bind table data to Stream Writer to export data to respective folder
				 StreamWriter wr = new StreamWriter(@"D:/" + textBox1.Text + ".xls");
				 // Write Columns to excel file
				 for (int i = 2; i < dt.Columns.Count; i++)
				 {
					 wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
				 }
				 wr.WriteLine();
				 //write rows to excel file
				 for (int i = 0; i < (dt.Rows.Count); i++)
				 {
					 for (int j = 0; j < dt.Columns.Count; j++)
					 {
						 if (dt.Rows[i][j] != null)
						 {
							 wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
						 }
						 else
						 {
							 wr.Write("\t");
						 }
					 }
					 wr.WriteLine();
				 }
				 wr.Close();
				 //dataGridView1.Columns[0].Visible = true;
				 //dataGridView1.Columns[1].Visible = true;
				 MessageBox.Show("Dados exportados com sucesso");
			 }
			 catch (Exception ex)
			 {
				 MessageBox.Show("erro: " + ex.Message);
				 //Log.Save("Erro inesperado: " + ex.Message + "", Util, "", "", 0);
			 }
		 }
	 }
	 else
	 { MessageBox.Show("Colocar nome no excel"); }

PS: O código está ligeiramente alterado por causa dos meus testes.

Diz se funciona.

Cumps.

PS2: A abordagem acima pode ser um pouco perigosa. Se o que apenas queres exportar para o excel são apenas campos de texto, talvez seja melhor assim, em que apenas validas se o tipo da coluna é texto, se sim, então adiciona à Datatable, caso contrario, saltas:

if (textBox1.Text != string.Empty)
	 {
		 if (dataGridView1.Rows.Count > 0)
		 {
			 try
			 {
				 //dataGridView1.Columns[0].Visible = false;
				 //dataGridView1.Columns[1].Visible = false;
				 // Bind Grid Data to Datatable
				 DataTable dt = new DataTable();
				 foreach (DataGridViewColumn col in dataGridView1.Columns)
				 {
					 Type tp = col.CellType;
					 if(tp == typeof(DataGridViewTextBoxCell))
					 {
						 dt.Columns.Add(col.HeaderText, col.ValueType);
					 }
					 else
					 {
						 continue;
					 }
				 }
				 int count = 0;
				 foreach (DataGridViewRow row in dataGridView1.Rows)
				 {
					 if (count < dataGridView1.Rows.Count - 1)
					 {
						 dt.Rows.Add();
						 foreach (DataGridViewCell cell in row.Cells)
						 {
							 dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
						 }
					 }
					 count++;
				 }
				 // Bind table data to Stream Writer to export data to respective folder
				 StreamWriter wr = new StreamWriter(@"D:/" + textBox1.Text + ".xls");
				 // Write Columns to excel file
				 for (int i = 2; i < dt.Columns.Count; i++)
				 {
					 wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
				 }
				 wr.WriteLine();
				 //write rows to excel file
				 for (int i = 0; i < (dt.Rows.Count); i++)
				 {
					 for (int j = 0; j < dt.Columns.Count; j++)
					 {
						 if (dt.Rows[i][j] != null)
						 {
							 wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
						 }
						 else
						 {
							 wr.Write("\t");
						 }
					 }
					 wr.WriteLine();
				 }
				 wr.Close();
				 //dataGridView1.Columns[0].Visible = true;
				 //dataGridView1.Columns[1].Visible = true;
				 MessageBox.Show("Dados exportados com sucesso");
			 }
			 catch (Exception ex)
			 {
				 MessageBox.Show("erro: " + ex.Message);
				 //Log.Save("Erro inesperado: " + ex.Message + "", Util, "", "", 0);
			 }
		 }
	 }
	 else
	 { MessageBox.Show("Colocar nome no excel"); }

Cumps.

Edited by Guest
Link to comment
Share on other sites

Olá.

Não sei se responde a tua questão, mas o que tens que fazer é validar se o tipo da coluna que estás a iterar é do tipo checkbox ou botão, se for, saltas e não adicionas à datatable.

if (textBox1.Text != string.Empty)
	 {
		 if (dataGridView1.Rows.Count > 0)
		 {
			 try
			 {
				 //dataGridView1.Columns[0].Visible = false;
				 //dataGridView1.Columns[1].Visible = false;
				 // Bind Grid Data to Datatable
				 DataTable dt = new DataTable();
				 foreach (DataGridViewColumn col in dataGridView1.Columns)
				 {
					 Type tp = col.CellType;
					 if(tp == typeof(DataGridViewCheckBoxCell) || tp == typeof(DataGridViewButtonCell))
					 {
						 continue;
					 }
					 else
					 {
						 dt.Columns.Add(col.HeaderText, col.ValueType);
					 }
				 }
				 int count = 0;
				 foreach (DataGridViewRow row in dataGridView1.Rows)
				 {
					 if (count < dataGridView1.Rows.Count - 1)
					 {
						 dt.Rows.Add();
						 foreach (DataGridViewCell cell in row.Cells)
						 {
							 dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
						 }
					 }
					 count++;
				 }
				 // Bind table data to Stream Writer to export data to respective folder
				 StreamWriter wr = new StreamWriter(@"D:/" + textBox1.Text + ".xls");
				 // Write Columns to excel file
				 for (int i = 2; i < dt.Columns.Count; i++)
				 {
					 wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
				 }
				 wr.WriteLine();
				 //write rows to excel file
				 for (int i = 0; i < (dt.Rows.Count); i++)
				 {
					 for (int j = 0; j < dt.Columns.Count; j++)
					 {
						 if (dt.Rows[i][j] != null)
						 {
							 wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
						 }
						 else
						 {
							 wr.Write("\t");
						 }
					 }
					 wr.WriteLine();
				 }
				 wr.Close();
				 //dataGridView1.Columns[0].Visible = true;
				 //dataGridView1.Columns[1].Visible = true;
				 MessageBox.Show("Dados exportados com sucesso");
			 }
			 catch (Exception ex)
			 {
				 MessageBox.Show("erro: " + ex.Message);
				 //Log.Save("Erro inesperado: " + ex.Message + "", Util, "", "", 0);
			 }
		 }
	 }
	 else
	 { MessageBox.Show("Colocar nome no excel"); }

PS: O código está ligeiramente alterado por causa dos meus testes.

Diz se funciona.

Cumps.

PS2: A abordagem acima pode ser um pouco perigosa. Se o que apenas queres exportar para o excel são apenas campos de texto, talvez seja melhor assim, em que apenas validas se o tipo da coluna é texto, se sim, então adiciona à Datatable, caso contrario, saltas:

if (textBox1.Text != string.Empty)
	 {
		 if (dataGridView1.Rows.Count > 0)
		 {
			 try
			 {
				 //dataGridView1.Columns[0].Visible = false;
				 //dataGridView1.Columns[1].Visible = false;
				 // Bind Grid Data to Datatable
				 DataTable dt = new DataTable();
				 foreach (DataGridViewColumn col in dataGridView1.Columns)
				 {
					 Type tp = col.CellType;
					 if(tp == typeof(DataGridViewTextBoxCell))
					 {
						 dt.Columns.Add(col.HeaderText, col.ValueType);
					 }
					 else
					 {
						 continue;
					 }
				 }
				 int count = 0;
				 foreach (DataGridViewRow row in dataGridView1.Rows)
				 {
					 if (count < dataGridView1.Rows.Count - 1)
					 {
						 dt.Rows.Add();
						 foreach (DataGridViewCell cell in row.Cells)
						 {
							 dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
						 }
					 }
					 count++;
				 }
				 // Bind table data to Stream Writer to export data to respective folder
				 StreamWriter wr = new StreamWriter(@"D:/" + textBox1.Text + ".xls");
				 // Write Columns to excel file
				 for (int i = 2; i < dt.Columns.Count; i++)
				 {
					 wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
				 }
				 wr.WriteLine();
				 //write rows to excel file
				 for (int i = 0; i < (dt.Rows.Count); i++)
				 {
					 for (int j = 0; j < dt.Columns.Count; j++)
					 {
						 if (dt.Rows[i][j] != null)
						 {
							 wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
						 }
						 else
						 {
							 wr.Write("\t");
						 }
					 }
					 wr.WriteLine();
				 }
				 wr.Close();
				 //dataGridView1.Columns[0].Visible = true;
				 //dataGridView1.Columns[1].Visible = true;
				 MessageBox.Show("Dados exportados com sucesso");
			 }
			 catch (Exception ex)
			 {
				 MessageBox.Show("erro: " + ex.Message);
				 //Log.Save("Erro inesperado: " + ex.Message + "", Util, "", "", 0);
			 }
		 }
	 }
	 else
	 { MessageBox.Show("Colocar nome no excel"); }

Cumps.

Olá

desculpa so responder agora.

Continua com erro: A referência de objecto não foi definida como uma instância de um objecto.

Link to comment
Share on other sites

Olá,

Isso quer dizer que em alguma parte do código estás a passar um objecto a nulo. Faz debug ao código e descobre que valor e em que linha estar a passar a nulo.

Coloca aqui a linha em que está o valor nulo depois.

Rebenta logo na 1 vez q entra aqui dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.