• DataGridView导出到Excel的三个方法 (转载)


    #region DataGridView数据显示到Excel     
    02./// <summary>      
    03./// 打开Excel并将DataGridView控件中数据导出到Excel     
    04./// </summary>      
    05./// <param name="dgv">DataGridView对象 </param>      
    06./// <param name="isShowExcle">是否显示Excel界面 </param>      
    07./// <remarks>     
    08./// add com "Microsoft Excel 11.0 Object Library"     
    09./// using Excel=Microsoft.Office.Interop.Excel;     
    10./// </remarks>     
    11./// <returns> </returns>      
    12.public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)     
    13.{     
    14.    if (dgv.Rows.Count == 0)     
    15.        return false;     
    16.    //建立Excel对象      
    17.    Excel.Application excel = new Excel.Application();     
    18.    excel.Application.Workbooks.Add(true);     
    19.    excel.Visible = isShowExcle;     
    20.    //生成字段名称      
    21.    for (int i = 0; i < dgv.ColumnCount; i++)     
    22.    {     
    23.        excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;     
    24.    }     
    25.    //填充数据      
    26.    for (int i = 0; i < dgv.RowCount - 1; i++)     
    27.    {     
    28.        for (int j = 0; j < dgv.ColumnCount; j++)     
    29.        {     
    30.            if (dgv[j, i].ValueType == typeof(string))     
    31.            {     
    32.                excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();     
    33.            }     
    34.            else    
    35.            {     
    36.                excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();     
    37.            }     
    38.        }     
    39.    }     
    40.    return true;     
    41.}    
    42.#endregion     
    43.  
    44.#region DateGridView导出到csv格式的Excel     
    45./// <summary>     
    46./// 常用方法,列之间加/t,一行一行输出,此文件其实是csv文件,不过默认可以当成Excel打开。     
    47./// </summary>     
    48./// <remarks>     
    49./// using System.IO;     
    50./// </remarks>     
    51./// <param name="dgv"></param>     
    52.private void DataGridViewToExcel(DataGridView dgv)     
    53.{     
    54.    SaveFileDialog dlg = new SaveFileDialog();     
    55.    dlg.Filter = "Execl files (*.xls)|*.xls";     
    56.    dlg.FilterIndex = 0;     
    57.    dlg.RestoreDirectory = true;     
    58.    dlg.CreatePrompt = true;     
    59.    dlg.Title = "保存为Excel文件";     
    60.    
    61.    if (dlg.ShowDialog() == DialogResult.OK)     
    62.    {     
    63.        Stream myStream;     
    64.        myStream = dlg.OpenFile();     
    65.        StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));     
    66.        string columnTitle = "";     
    67.        try    
    68.        {     
    69.            //写入列标题     
    70.            for (int i = 0; i < dgv.ColumnCount; i++)     
    71.            {     
    72.                if (i > 0)     
    73.                {     
    74.                    columnTitle += "/t";     
    75.                }     
    76.                columnTitle += dgv.Columns[i].HeaderText;     
    77.            }     
    78.            sw.WriteLine(columnTitle);     
    79.    
    80.            //写入列内容     
    81.            for (int j = 0; j < dgv.Rows.Count; j++)     
    82.            {     
    83.                string columnValue = "";     
    84.                for (int k = 0; k < dgv.Columns.Count; k++)     
    85.                {     
    86.                    if (k > 0)     
    87.                    {     
    88.                        columnValue += "/t";     
    89.                    }     
    90.                    if (dgv.Rows[j].Cells[k].Value == null)     
    91.                        columnValue += "";     
    92.                    else    
    93.                        columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();     
    94.                }     
    95.                sw.WriteLine(columnValue);     
    96.            }     
    97.            sw.Close();     
    98.            myStream.Close();     
    99.        }     
    100.        catch (Exception e)     
    101.        {     
    102.            MessageBox.Show(e.ToString());     
    103.        }     
    104.        finally    
    105.        {     
    106.            sw.Close();     
    107.            myStream.Close();     
    108.        }     
    109.    }     
    110.}     
    111.#endregion    
    112.  
    113.#region DataGridView导出到Excel,有一定的判断性     
    114./// <summary>      
    115.///方法,导出DataGridView中的数据到Excel文件      
    116./// </summary>      
    117./// <remarks>     
    118./// add com "Microsoft Excel 11.0 Object Library"     
    119./// using Excel=Microsoft.Office.Interop.Excel;     
    120./// using System.Reflection;     
    121./// </remarks>     
    122./// <param name= "dgv"> DataGridView </param>      
    123.public static void DataGridViewToExcel(DataGridView dgv)     
    124.{    
    125.  
    126.  
    127.    #region   验证可操作性     
    128.    
    129.    //申明保存对话框      
    130.    SaveFileDialog dlg = new SaveFileDialog();     
    131.    //默然文件后缀      
    132.    dlg.DefaultExt = "xls ";     
    133.    //文件后缀列表      
    134.    dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";     
    135.    //默然路径是系统当前路径      
    136.    dlg.InitialDirectory = Directory.GetCurrentDirectory();     
    137.    //打开保存对话框      
    138.    if (dlg.ShowDialog() == DialogResult.Cancel) return;     
    139.    //返回文件路径      
    140.    string fileNameString = dlg.FileName;     
    141.    //验证strFileName是否为空或值无效      
    142.    if (fileNameString.Trim() == " ")     
    143.    { return; }     
    144.    //定义表格内数据的行数和列数      
    145.    int rowscount = dgv.Rows.Count;     
    146.    int colscount = dgv.Columns.Count;     
    147.    //行数必须大于0      
    148.    if (rowscount <= 0)     
    149.    {     
    150.        MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);     
    151.        return;     
    152.    }     
    153.    
    154.    //列数必须大于0      
    155.    if (colscount <= 0)     
    156.    {     
    157.        MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);     
    158.        return;     
    159.    }     
    160.    
    161.    //行数不可以大于65536      
    162.    if (rowscount > 65536)     
    163.    {     
    164.        MessageBox.Show("数据记录数太多(最多不能超过65536条),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);     
    165.        return;     
    166.    }     
    167.    
    168.    //列数不可以大于255      
    169.    if (colscount > 255)     
    170.    {     
    171.        MessageBox.Show("数据记录行数太多,不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);     
    172.        return;     
    173.    }     
    174.    
    175.    //验证以fileNameString命名的文件是否存在,如果存在删除它      
    176.    FileInfo file = new FileInfo(fileNameString);     
    177.    if (file.Exists)     
    178.    {     
    179.        try    
    180.        {     
    181.            file.Delete();     
    182.        }     
    183.        catch (Exception error)     
    184.        {     
    185.            MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);     
    186.            return;     
    187.        }     
    188.    }    
    189.    #endregion     
    190.    Excel.Application objExcel = null;     
    191.    Excel.Workbook objWorkbook = null;     
    192.    Excel.Worksheet objsheet = null;     
    193.    try    
    194.    {     
    195.        //申明对象      
    196.        objExcel = new Microsoft.Office.Interop.Excel.Application();     
    197.        objWorkbook = objExcel.Workbooks.Add(Missing.Value);     
    198.        objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;     
    199.        //设置EXCEL不可见      
    200.        objExcel.Visible = false;     
    201.    
    202.        //向Excel中写入表格的表头      
    203.        int displayColumnsCount = 1;     
    204.        for (int i = 0; i <= dgv.ColumnCount - 1; i++)     
    205.        {     
    206.            if (dgv.Columns[i].Visible == true)     
    207.            {     
    208.                objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();     
    209.                displayColumnsCount++;     
    210.            }     
    211.        }     
    212.        //设置进度条      
    213.        //tempProgressBar.Refresh();      
    214.        //tempProgressBar.Visible   =   true;      
    215.        //tempProgressBar.Minimum=1;      
    216.        //tempProgressBar.Maximum=dgv.RowCount;      
    217.        //tempProgressBar.Step=1;      
    218.        //向Excel中逐行逐列写入表格中的数据      
    219.        for (int row = 0; row <= dgv.RowCount - 1; row++)     
    220.        {     
    221.            //tempProgressBar.PerformStep();      
    222.    
    223.            displayColumnsCount = 1;     
    224.            for (int col = 0; col < colscount; col++)     
    225.            {     
    226.                if (dgv.Columns[col].Visible == true)     
    227.                {     
    228.                    try    
    229.                    {     
    230.                        objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();     
    231.                        displayColumnsCount++;     
    232.                    }     
    233.                    catch (Exception)     
    234.                    {     
    235.    
    236.                    }     
    237.    
    238.                }     
    239.            }     
    240.        }     
    241.        //隐藏进度条      
    242.        //tempProgressBar.Visible   =   false;      
    243.        //保存文件      
    244.        objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,     
    245.                Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,     
    246.                Missing.Value, Missing.Value);     
    247.    }     
    248.    catch (Exception error)     
    249.    {     
    250.        MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);     
    251.        return;     
    252.    }     
    253.    finally    
    254.    {     
    255.        //关闭Excel应用      
    256.        if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);     
    257.        if (objExcel.Workbooks != null) objExcel.Workbooks.Close();     
    258.        if (objExcel != null) objExcel.Quit();     
    259.    
    260.        objsheet = null;     
    261.        objWorkbook = null;     
    262.        objExcel = null;     
    263.    }     
    264.    MessageBox.Show(fileNameString + "/n/n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);     
    265.    
    266.}    
    267.  
    268.#endregion    
    269. 
    270.#region DataGridView数据显示到Excel  
    271./// <summary>   
    272./// 打开Excel并将DataGridView控件中数据导出到Excel  
    273./// </summary>   
    274./// <param name="dgv">DataGridView对象 </param>   
    275./// <param name="isShowExcle">是否显示Excel界面 </param>   
    276./// <remarks>  
    277./// add com "Microsoft Excel 11.0 Object Library"  
    278./// using Excel=Microsoft.Office.Interop.Excel;  
    279./// </remarks>  
    280./// <returns> </returns>   
    281.public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)  
    282.{  
    283.    if (dgv.Rows.Count == 0)  
    284.        return false;  
    285.    //建立Excel对象   
    286.    Excel.Application excel = new Excel.Application();  
    287.    excel.Application.Workbooks.Add(true);  
    288.    excel.Visible = isShowExcle;  
    289.    //生成字段名称   
    290.    for (int i = 0; i < dgv.ColumnCount; i++)  
    291.    {  
    292.        excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;  
    293.    }  
    294.    //填充数据   
    295.    for (int i = 0; i < dgv.RowCount - 1; i++)  
    296.    {  
    297.        for (int j = 0; j < dgv.ColumnCount; j++)  
    298.        {  
    299.            if (dgv[j, i].ValueType == typeof(string))  
    300.            {  
    301.                excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();  
    302.            }  
    303.            else  
    304.            {  
    305.                excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();  
    306.            }  
    307.        }  
    308.    }  
    309.    return true;  
    310.}  
    311.#endregion   
    312. 
    313.#region DateGridView导出到csv格式的Excel  
    314./// <summary>  
    315./// 常用方法,列之间加/t,一行一行输出,此文件其实是csv文件,不过默认可以当成Excel打开。  
    316./// </summary>  
    317./// <remarks>  
    318./// using System.IO;  
    319./// </remarks>  
    320./// <param name="dgv"></param>  
    321.private void DataGridViewToExcel(DataGridView dgv)  
    322.{  
    323.    SaveFileDialog dlg = new SaveFileDialog();  
    324.    dlg.Filter = "Execl files (*.xls)|*.xls";  
    325.    dlg.FilterIndex = 0;  
    326.    dlg.RestoreDirectory = true;  
    327.    dlg.CreatePrompt = true;  
    328.    dlg.Title = "保存为Excel文件";  
    329.  
    330.    if (dlg.ShowDialog() == DialogResult.OK)  
    331.    {  
    332.        Stream myStream;  
    333.        myStream = dlg.OpenFile();  
    334.        StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));  
    335.        string columnTitle = "";  
    336.        try  
    337.        {  
    338.            //写入列标题  
    339.            for (int i = 0; i < dgv.ColumnCount; i++)  
    340.            {  
    341.                if (i > 0)  
    342.                {  
    343.                    columnTitle += "/t";  
    344.                }  
    345.                columnTitle += dgv.Columns[i].HeaderText;  
    346.            }  
    347.            sw.WriteLine(columnTitle);  
    348.  
    349.            //写入列内容  
    350.            for (int j = 0; j < dgv.Rows.Count; j++)  
    351.            {  
    352.                string columnValue = "";  
    353.                for (int k = 0; k < dgv.Columns.Count; k++)  
    354.                {  
    355.                    if (k > 0)  
    356.                    {  
    357.                        columnValue += "/t";  
    358.                    }  
    359.                    if (dgv.Rows[j].Cells[k].Value == null)  
    360.                        columnValue += "";  
    361.                    else  
    362.                        columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();  
    363.                }  
    364.                sw.WriteLine(columnValue);  
    365.            }  
    366.            sw.Close();  
    367.            myStream.Close();  
    368.        }  
    369.        catch (Exception e)  
    370.        {  
    371.            MessageBox.Show(e.ToString());  
    372.        }  
    373.        finally  
    374.        {  
    375.            sw.Close();  
    376.            myStream.Close();  
    377.        }  
    378.    }  
    379.}   
    380.#endregion  
    381. 
    382.#region DataGridView导出到Excel,有一定的判断性  
    383./// <summary>   
    384.///方法,导出DataGridView中的数据到Excel文件   
    385./// </summary>   
    386./// <remarks>  
    387./// add com "Microsoft Excel 11.0 Object Library"  
    388./// using Excel=Microsoft.Office.Interop.Excel;  
    389./// using System.Reflection;  
    390./// </remarks>  
    391./// <param name= "dgv"> DataGridView </param>   
    392.public static void DataGridViewToExcel(DataGridView dgv)  
    393.{  
    394. 
    395. 
    396.    #region   验证可操作性  
    397.  
    398.    //申明保存对话框   
    399.    SaveFileDialog dlg = new SaveFileDialog();  
    400.    //默然文件后缀   
    401.    dlg.DefaultExt = "xls ";  
    402.    //文件后缀列表   
    403.    dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";  
    404.    //默然路径是系统当前路径   
    405.    dlg.InitialDirectory = Directory.GetCurrentDirectory();  
    406.    //打开保存对话框   
    407.    if (dlg.ShowDialog() == DialogResult.Cancel) return;  
    408.    //返回文件路径   
    409.    string fileNameString = dlg.FileName;  
    410.    //验证strFileName是否为空或值无效   
    411.    if (fileNameString.Trim() == " ")  
    412.    { return; }  
    413.    //定义表格内数据的行数和列数   
    414.    int rowscount = dgv.Rows.Count;  
    415.    int colscount = dgv.Columns.Count;  
    416.    //行数必须大于0   
    417.    if (rowscount <= 0)  
    418.    {  
    419.        MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
    420.        return;  
    421.    }  
    422.  
    423.    //列数必须大于0   
    424.    if (colscount <= 0)  
    425.    {  
    426.        MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
    427.        return;  
    428.    }  
    429.  
    430.    //行数不可以大于65536   
    431.    if (rowscount > 65536)  
    432.    {  
    433.        MessageBox.Show("数据记录数太多(最多不能超过65536条),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
    434.        return;  
    435.    }  
    436.  
    437.    //列数不可以大于255   
    438.    if (colscount > 255)  
    439.    {  
    440.        MessageBox.Show("数据记录行数太多,不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
    441.        return;  
    442.    }  
    443.  
    444.    //验证以fileNameString命名的文件是否存在,如果存在删除它   
    445.    FileInfo file = new FileInfo(fileNameString);  
    446.    if (file.Exists)  
    447.    {  
    448.        try  
    449.        {  
    450.            file.Delete();  
    451.        }  
    452.        catch (Exception error)  
    453.        {  
    454.            MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);  
    455.            return;  
    456.        }  
    457.    }  
    458.    #endregion  
    459.    Excel.Application objExcel = null;  
    460.    Excel.Workbook objWorkbook = null;  
    461.    Excel.Worksheet objsheet = null;  
    462.    try  
    463.    {  
    464.        //申明对象   
    465.        objExcel = new Microsoft.Office.Interop.Excel.Application();  
    466.        objWorkbook = objExcel.Workbooks.Add(Missing.Value);  
    467.        objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;  
    468.        //设置EXCEL不可见   
    469.        objExcel.Visible = false;  
    470.  
    471.        //向Excel中写入表格的表头   
    472.        int displayColumnsCount = 1;  
    473.        for (int i = 0; i <= dgv.ColumnCount - 1; i++)  
    474.        {  
    475.            if (dgv.Columns[i].Visible == true)  
    476.            {  
    477.                objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();  
    478.                displayColumnsCount++;  
    479.            }  
    480.        }  
    481.        //设置进度条   
    482.        //tempProgressBar.Refresh();   
    483.        //tempProgressBar.Visible   =   true;   
    484.        //tempProgressBar.Minimum=1;   
    485.        //tempProgressBar.Maximum=dgv.RowCount;   
    486.        //tempProgressBar.Step=1;   
    487.        //向Excel中逐行逐列写入表格中的数据   
    488.        for (int row = 0; row <= dgv.RowCount - 1; row++)  
    489.        {  
    490.            //tempProgressBar.PerformStep();   
    491.  
    492.            displayColumnsCount = 1;  
    493.            for (int col = 0; col < colscount; col++)  
    494.            {  
    495.                if (dgv.Columns[col].Visible == true)  
    496.                {  
    497.                    try  
    498.                    {  
    499.                        objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();  
    500.                        displayColumnsCount++;  
    501.                    }  
    502.                    catch (Exception)  
    503.                    {  
    504.  
    505.                    }  
    506.  
    507.                }  
    508.            }  
    509.        }  
    510.        //隐藏进度条   
    511.        //tempProgressBar.Visible   =   false;   
    512.        //保存文件   
    513.        objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,  
    514.                Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,  
    515.                Missing.Value, Missing.Value);  
    516.    }  
    517.    catch (Exception error)  
    518.    {  
    519.        MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);  
    520.        return;  
    521.    }  
    522.    finally  
    523.    {  
    524.        //关闭Excel应用   
    525.        if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);  
    526.        if (objExcel.Workbooks != null) objExcel.Workbooks.Close();  
    527.        if (objExcel != null) objExcel.Quit();  
    528.  
    529.        objsheet = null;  
    530.        objWorkbook = null;  
    531.        objExcel = null;  
    532.    }  
    533.    MessageBox.Show(fileNameString + "/n/n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);  
    534.  
    535.}  
    536. 
    537.#endregion  
    View Code

     以测试的:

       public bool GetDatagridview(DataGridView dv, bool isShowExcel)
            {
                if (dv.Rows.Count == 0)
                {
                    return false;
                }
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                  excel.Application.Workbooks.Add(true);
                  excel.Visible = isShowExcel;
                  for (int i = 0; i < dv.ColumnCount; i++)
                  {
                      excel.Cells[1, i + 1] = dv.Columns[i].HeaderText; 
                  }
                  for (int i = 0; i < dv.RowCount - 1; i++)     
        {
            for (int j = 0; j < dv.ColumnCount; j++)     
            {
                if (dv[j, i].ValueType == typeof(string))     
               {
                   excel.Cells[i + 2, j + 1] = "'" + dv[j, i].Value.ToString();     
                }     
                else    
                {
                    excel.Cells[i + 2, j + 1] = dv[j, i].Value.ToString();     
                }     
            }     
        }     
             return true;    
    
            }
    View Code
  • 相关阅读:
    PHP基础知识(十一)
    PHP基础知识(十)
    PHP基础知识(九)
    PHP中禁用cookie后session的实现
    PHP基础知识(八)
    将博客搬至CSDN
    如何为预先数据库创建自定义SQL Server复制
    数据库同步工具
    如何终止与MySQL数据库的某些连接
    从Oracle到SQL Server和从SQL Server到Oracle的迁移指南
  • 原文地址:https://www.cnblogs.com/haimingkaifa/p/6069189.html
Copyright © 2020-2023  润新知