vb.net - DatagridView 导出到 excel “只有 1 行正在导出”

问题描述

大家好，我目前正在使用 Visual Studio 做一个小项目，我很难将所有数据从数据网格导出到 Excel。如果我全部导出，只有 1 行正在导出，我几乎完成了这个但我被困在这部分我真的需要帮助谢谢你（对不起我的英语不好）这是我的代码：

Public Class Form1


    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Me.DataGridView1.Rows.Add(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, TextBox7.Text, DateTimePicker1.Text, TextBox8.Text, TextBox9.Text)


    End Sub

    Public Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        ExportExcel()
    End Sub

    Private Sub ExportExcel()
        'Create excel objects
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim oValue As Object = System.Reflection.Missing.Value
        Dim dlgSave As New SaveFileDialog
        Dim dt As New Data.DataTable

        dlgSave.DefaultExt = "xls"

        dlgSave.Filter = "Microsoft Excel|*.xls"

        If dlgSave.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            Try

                xlApp = New Microsoft.Office.Interop.Excel.Application

                xlBook = xlApp.Workbooks.Add(oValue)

                xlSheet = xlBook.Worksheets("sheet1")

                Dim xlRow As Long = 2
                Dim xlCol As Short = 1

                For k As Integer = 0 To DataGridView1.ColumnCount - 1

                    xlSheet.Cells(1, xlCol) = DataGridView1(k, 0).Value

                    xlCol += 1

                Next

                For i As Integer = 0 To DataGridView1.RowCount - 1

                    xlCol = 1

                    For k As Integer = 0 To DataGridView1.ColumnCount - 1

                        xlSheet.Cells(xlRow, xlCol) = DataGridView1(k, i).Value


                        xlCol += 1
                    Next

                    xlRow = 1

                Next

                xlSheet.Columns.AutoFit()
                Dim sFileName As String = Replace(dlgSave.FileName, ".xlsx", "xlx")

                xlSheet.SaveAs(sFileName)

                xlBook.Close()

                xlApp.Quit()

                MsgBox("Data successfully exported.", MsgBoxStyle.Information, "Closing..")
            Catch
                MsgBox(ErrorToString)
            Finally

            End Try
        End If
    End Sub

    Private Sub Clear_Click(sender As Object, e As EventArgs) Handles Clear.Click
        DataGridView1.Rows.Clear()
    End Sub

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub
End Class

如您所见，我输入了 2 行数据

在我导出它之后，只有 1 行在 excel 里面

所以我实际上想通了，我改用 c# 语言谢谢你帮助那些想要我的代码的人：

namespace gridviewtoexcel
{
    public partial class ThreeHundredSixtyDegrees : Form
    {
        public ThreeHundredSixtyDegrees()
        {
            InitializeComponent();
        }


        private void ToCsV(DataGridView dGV, string filename)
        {
            string stOutput = "";
            // Export titles:
            string sHeaders = "";

            for (int j = 0; j < dGV.Columns.Count; j++)
                sHeaders = sHeaders.ToString() + Convert.ToString(dGV.Columns[j].HeaderText) + "\t";
            stOutput += sHeaders + "\r\n";
            // Export data.
            for (int i = 0; i < dGV.RowCount - 1; i++)
            {
                string stLine = "";
                for (int j = 0; j < dGV.Rows[i].Cells.Count; j++)
                    stLine = stLine.ToString() + Convert.ToString(dGV.Rows[i].Cells[j].Value) + "\t";
                stOutput += stLine + "\r\n";
            }
            Encoding utf16 = Encoding.GetEncoding(1254);
            byte[] output = utf16.GetBytes(stOutput);
            FileStream fs = new FileStream(filename, FileMode.Create);
            BinaryWriter bw = new BinaryWriter(fs);
            bw.Write(output, 0, output.Length); //write the encoded file
            bw.Flush();
            bw.Close();
            fs.Close();
        }

        private void Button2_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "Excel Documents (*.xls)|*.xls";
            sfd.FileName = ".xls";
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                //ToCsV(dataGridView1, @"c:\.xls");
                ToCsV(DataGridView1, sfd.FileName); // Here dataGridview1 is your grid view name
            }
        }

        private void DataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void Button1_Click(object sender, EventArgs e)
        {
            string firstColum = TextBox1.Text;
            string secondColum = TextBox2.Text;
            string thirdColum = TextBox3.Text;
            string fourthColum = TextBox4.Text;
            string fifthColum = TextBox5.Text;
            string sixthColum = TextBox6.Text;
            string seventhColum = TextBox7.Text;
            string dateColum = DateTimePicker1.Text;
            string eightColum = TextBox8.Text;
            string tenthColum = TextBox9.Text;

            string[] row = { firstColum, secondColum,thirdColum,fourthColum,fifthColum,sixthColum,seventhColum,dateColum,eightColum,tenthColum };
            DataGridView1.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.Fill;
            DataGridView1.Rows.Add(row);
        }

        private void Clear_Click(object sender, EventArgs e)
        {
            DataGridView1.Rows.Clear();
        }
    }
}

标签： vb.net

vb.net - DatagridView 导出到 excel “只有 1 行正在导出”

问题描述

解决方案

推荐阅读