c# - 导出mysql获取的Gridview数据

嗨，我将gridview数据导出到Excel中，但是不幸的是，导出文件中的数据不同，应该是一个数据表。



以下是我的脚本在“导出”按钮中，您能告诉我我的脚本有什么问题吗？我是ASP.net新手，谢谢

try
{
    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
    excel.Visible = true;
    Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
    Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
    int StartCol = 1;
    int StartRow = 1;
    int j = 0, i = 0;

    //Write Headers
    for (j = 0; j < GridView1.Columns.Count; j++)
    {
        Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow, StartCol + j];
        myRange.Value = GridView1.Columns[j].HeaderText;
    }

    StartRow++;

     //Write datagridview content
     for (i = 0; i < GridView1.Rows.Count; i++)
     {
         for (j = 0; j < GridView1.Columns.Count; j++)
         {
             try
             {
                 Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow + i, StartCol + j];
                 myRange.Value2 = GridView1.Rows[i].Cells[j].Text + ";" == null ? "" : GridView1.Rows[i].Cells[j].Text + ";";

             }
             catch
             {
                 GridView1.DataBind();
             }
         }
     }
 }
 catch (Exception ex)
 {
     MessageBox.Show(ex.ToString());

     // ScriptManager.RegisterClientScriptBlock(this, this.GetType(),
     //  "alertMessage",
     //   "alert(ex.ToString());", true);

}

最佳答案

您可以使用以下方法将datagridview数据导出到excel：

public void ExportToExcel(DataGridView dgv)
    {
        try
        {
            dgv.SelectAll();
            dgv.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
            DataObject doj = dgv.GetClipboardContent();
            Clipboard.SetDataObject(doj);

            dgv.ClearSelection();

            Microsoft.Office.Interop.Excel.Application exap = new Microsoft.Office.Interop.Excel.Application();
            exap.Visible = true;

            Workbook exwb = (Workbook)exap.Workbooks.Add();
            Worksheet exws = (Worksheet)exwb.Sheets["Sheet1"];

            exws.Paste();

            Clipboard.Clear();

            Range cell1 = exws.Cells[1, 2];
            Range cell2 = exws.Cells[dgv.Rows.Count + 1, dgv.ColumnCount + 1];
            Range cell3 = exws.Cells[1, dgv.ColumnCount + 1];

            Range range = exws.get_Range(cell1, cell2);
            Range colorrange = exws.get_Range(cell1, cell3);

            range.Borders.Weight = XlBorderWeight.xlThin;
            colorrange.Interior.Color = System.Drawing.Color.SteelBlue;
            colorrange.Font.Color = System.Drawing.Color.White;

            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "Excel File 2010 (*.xlsx)|*.xlsx|Excel File 2003 (*.xls)|*.xls";

            if (sfd.ShowDialog() == DialogResult.OK)
            {
                exwb.SaveAs(sfd.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }

        }

        catch(System.Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

    }

此方法将所有数据复制到datagridview中并将其粘贴到excel中。您当然需要添加对Microsoft.Office.Interop.Excel的引用。

或者，如果您希望将数据表导出为ex​​cel，则可以尝试以下方法：

public void ExporttoExcel(System.Data.DataTable dtbl)
    {
        StringBuilder Output = new StringBuilder();

        //The first "line" will be the Headers.
        for (int i = 0; i < dtbl.Columns.Count; i++)
        {
            Output.Append(dtbl.Columns[i].ColumnName + "\t");
        }

        Output.Append("\n");

        //Generate Cell Value Data
        foreach (DataRow Row in dtbl.Rows)
        {
            if (Row.RowState != DataRowState.Deleted)
            {
                for (int i = 0; i < Row.ItemArray.Length; i++)
                {
                    //Handling the last cell of the line.
                    if (i == (Row.ItemArray.Length - 1))
                    {

                        Output.Append(Row.ItemArray[i].ToString() + "\n");
                    }
                    else
                    {

                        Output.Append(Row.ItemArray[i].ToString() + "\t");
                    }
                }
            }
        }

        Clipboard.SetText(Output.ToString());

        Microsoft.Office.Interop.Excel.Application exap = new Microsoft.Office.Interop.Excel.Application();
        exap.Visible = true;

        Workbook exwb = (Workbook)exap.Workbooks.Add();
        Worksheet exws = (Worksheet)exwb.Sheets["Sheet1"];


        exws.Paste();

        Clipboard.Clear();

        Range cell1 = exws.Cells[1, 1];
        Range cell2 = exws.Cells[dtbl.Rows.Count, dtbl.Columns.Count];
        Range cell3 = exws.Cells[1, dtbl.Columns.Count];

        Range range = exws.get_Range(cell1, cell2);
        Range colorrange = exws.get_Range(cell1, cell3);

        range.Borders.Weight = XlBorderWeight.xlThin;
        colorrange.Interior.Color = System.Drawing.Color.SteelBlue;
        colorrange.Font.Color = System.Drawing.Color.White;

        SaveFileDialog sfd = new SaveFileDialog();

        sfd.Filter = "Excel File 2010 (*.xlsx)|*.xlsx|Excel File 2003 (*.xls)|*.xls";

        if (sfd.ShowDialog() == DialogResult.OK)
        {
            exwb.SaveAs(sfd.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        }


    }

请检查这些方法是否有帮助。