Mehmet Fatih

Mehmet Fatih

  • 855
  • 931
  • 39.9k

Preventing hidden fields from being transferred to Word

Dec 8 2023 12:59 PM

I want to transfer the optionally selected columns in the datagridview to word. The columns I did not select are transferred to Word as empty. I don't want it to appear at all.

My project is working as in the following :

This is the result of my project.

This is exactly what I want.

using System;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Windows.Forms;
using Word = Microsoft.Office.Interop.Word;

namespace SinifListesi
{
    public partial class WordeAktar : Form
    {
        public WordeAktar()
        {
            InitializeComponent();
        }
        OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = siniflisteleri25.accdb; Jet OLEDB:Database Password = Fatih2541; Mode = ReadWrite");
        OleDbCommand sorgu;
        OleDbDataReader veriler;


        private void SinifCek()
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                sorgu = new OleDbCommand
                {
                    CommandText = "SELECT * from siniflar order by sin_id asc",
                    Connection = conn
                };
                veriler = sorgu.ExecuteReader();
                while (veriler.Read())
                {

                    ComboBox1.Items.Add(veriler["sinifi"].ToString());
                    ComboBox1.ValueMember = "sinifi";
                    ComboBox1.DisplayMember = "sinifi";
                }
                veriler.Close();
                sorgu.Dispose();
            }
            catch (Exception hata)
            {
                MessageBox.Show("Islem Sirasinda Hata Olustu." + hata.Message);
            }
        }

        public void Export_Data_To_Word(DataGridView DGV, string filename)
        {
            if (File.Exists(filename))
            {
                File.Delete(filename);
            }
            Word.Document oDoc = new Word.Document();
            oDoc.Application.Visible = true;


            if (DGV.Rows.Count != 0)
            {
                int RowCount = DGV.Rows.Count;
                int ColumnCount = DGV.Columns.Count;
                Object[,] DataArray = new object[RowCount + 1, ColumnCount + 1];

                //add rows
                int r = 0;
                for (int c = 0; c <= ColumnCount - 1; c++)
                {
                    if (DGV.Columns[c].Visible) // Check if the column is visible
                    {
                        for (r = 0; r <= RowCount - 1; r++)
                        {
                            DataArray[r, c] = DGV.Rows[r].Cells[c].Value;
                        } //end row loop

                    } //end column loop               
                }


                //page orintation
                oDoc.PageSetup.Orientation = Word.WdOrientation.wdOrientLandscape;
                dynamic oRange = oDoc.Content.Application.Selection.Range;
                string oTemp = "";
                for (r = 0; r <= RowCount - 1; r++)
                {
                    for (int c = 0; c <= ColumnCount - 1; c++)
                    {
                        oTemp = oTemp + DataArray[r, c] + "\t";
                    }
                }

                //table format
                oRange.Text = oTemp;

                object Separator = Word.WdTableFieldSeparator.wdSeparateByTabs;
                object ApplyBorders = true;
                object AutoFit = true;
                object AutoFitBehavior = Word.WdAutoFitBehavior.wdAutoFitContent;

                oRange.ConvertToTable(ref Separator, ref RowCount,
                                           ref ColumnCount,
                                      Type.Missing, Type.Missing,
                                           ref ApplyBorders,
                                      Type.Missing, Type.Missing, Type.Missing,
                                      Type.Missing, Type.Missing, Type.Missing,
                                      Type.Missing, ref AutoFit,
                                           ref AutoFitBehavior, Type.Missing);
                oRange.Select();
                oDoc.Application.Selection.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle; //tablo çizgisi
                oDoc.Application.Selection.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleSingle; // tablo çizgisi
                oDoc.Application.Selection.Tables[1].Select();
                oDoc.Application.Selection.Tables[1].Rows.AllowBreakAcrossPages = 0;
                oDoc.Application.Selection.Tables[1].Rows.Alignment = 0;
                oDoc.Application.Selection.Tables[1].Rows[1].Select();
                oDoc.Application.Selection.InsertRowsAbove(1);
                oDoc.Application.Selection.Tables[1].Rows[1].Select();

                //header row style
                oDoc.Application.Selection.Tables[1].Rows[1].Range.Bold = 1;
                oDoc.Application.Selection.Tables[1].Rows[1].Range.Font.Name ="Tahoma";
                oDoc.Application.Selection.Tables[1].Rows[1].Range.Font.Size = 14;

                //add header row manually
                for (int c = 0; c <= DGV.Columns.Count - 1; c++)
                {
                    if (DGV.Columns[c].Visible) // Check if the column is visible
                    {
                        oDoc.Application.Selection.Tables[1].Cell(1, c + 1).Range.Text = DGV.Columns[c].HeaderText;
                    }
                }

                //table style 
                oDoc.Application.Selection.Tables[1].Rows[1].Select();
                oDoc.Application.Selection.Cells.VerticalAlignment =
                    Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;

                //header text
                foreach (Word.Section section in
                         oDoc.Application.ActiveDocument.Sections)
                {
                    Word.Range headerRange =section.Headers[Word.WdHeaderFooterIndex.wdHeaderFooterPrimary].Range;
                    headerRange.Fields.Add(headerRange,
                       Word.WdFieldType.wdFieldPage);
                    headerRange.Text = "ÖGRENCI BILGILERI";
                    headerRange.Font.Size = 16;
                    headerRange.ParagraphFormat.Alignment =
                       Word.WdParagraphAlignment.wdAlignParagraphCenter;
                }

                //save the file
                oDoc.SaveAs2(filename);
                MessageBox.Show("Ögrenci bilgileri word dosyasina aktarildi.");
                oDoc.Close();
                oDoc.Application.Quit();
            }
        }

        private void WordeAktar_Load(object sender, EventArgs e)
        {
            SinifCek();
            ComboBox1.SelectedIndex = 0;

            DGV.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            DGV.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;

            DGV.Columns["tcno"].Visible = tcnochk.Checked; // ilk açilista hangi sütunlari getirecegiyle ilgili
            DGV.Columns["dtarihi"].Visible = dogchk.Checked;
            DGV.Columns["atel"].Visible = atelchk.Checked;
            DGV.Columns["btel"].Visible = btelchk.Checked;


            DGV.TopLeftHeaderCell.Value = "S.No";
            DGV.Columns[0].HeaderText = "T.C.No";
            DGV.Columns[1].HeaderText = "Ö.No";
            DGV.Columns[2].HeaderText = "Adi";
            DGV.Columns[3].HeaderText = "Soyadi";
            DGV.Columns[4].HeaderText = "Cinsiyeti";
            DGV.Columns[5].HeaderText = "Sinifi";
            DGV.Columns[7].HeaderText = "Anne Tel";
            DGV.Columns[8].HeaderText = "Baba Tel";
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();

            sfd.Filter = "Word Documents (*.docx)|*.docx";

            sfd.FileName = "Ögrenci Bilgileri.docx";

            if (sfd.ShowDialog() == DialogResult.OK)
            {

                Export_Data_To_Word(DGV, sfd.FileName);
            }
        }

        private void ComboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = siniflisteleri25.accdb; Jet OLEDB:Database Password = Fatih2541; Mode = ReadWrite"))
            {

                string query = "SELECT tcno,ono,isim,soyisim,cinsiyet,sinifi,dtarihi,atel,btel from ogrencibilgileri25 where sinifi='" + ComboBox1.Text + " '";

                OleDbCommand command = new OleDbCommand(query, conn);
                conn.Open();
                var adapter = new OleDbDataAdapter(command);
                var table = new DataTable();
                adapter.Fill(table);
                DGV.DataSource = table;
                conn.Close();
            }
        }

        private void tcnochk_CheckedChanged(object sender, EventArgs e)
        {
            DGV.Columns["tcno"].Visible = tcnochk.Checked;
        }

        private void dogchk_CheckedChanged(object sender, EventArgs e)
        {
            DGV.Columns["dtarihi"].Visible = dogchk.Checked;
        }

        private void atelchk_CheckedChanged(object sender, EventArgs e)
        {
            DGV.Columns["atel"].Visible = atelchk.Checked;
        }

        private void btelchk_CheckedChanged(object sender, EventArgs e)
        {
            DGV.Columns["btel"].Visible = btelchk.Checked;
        }

        private void DGV_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            var grid = sender as DataGridView;
            var rowIdx = (e.RowIndex + 1).ToString();

            var centerFormat = new StringFormat()
            {
                // right alignment might actually make more sense for numbers
                Alignment = StringAlignment.Center,
                LineAlignment = StringAlignment.Center
            };

            var headerBounds = new Rectangle(e.RowBounds.Left, e.RowBounds.Top, grid.RowHeadersWidth, e.RowBounds.Height);
            e.Graphics.DrawString(rowIdx, this.Font, SystemBrushes.ControlText, headerBounds, centerFormat);
        }
    }
}

 


Answers (2)