[WinForm]dataGridView导出到EXCEL

方法一：

 

SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "Execl files (*.xls)|*.xls";
dlg.FilterIndex = 0;
dlg.RestoreDirectory = true;
dlg.CreatePrompt = true;
dlg.Title = "保存为Excel文件";
dlg.FileName = "不合格记录";//保存的Excel名字
if (dlg.ShowDialog() == DialogResult.OK)
{
    Stream myStream;
    myStream = dlg.OpenFile();
    StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
    string columnTitle = "";
    try
    {
        //写入列标题  
        for (int i = 0; i < dgv.ColumnCount; i++)
        {
            if (i > 0)
            {
                columnTitle += "\t";
            }
            columnTitle += dgv.Columns[i].HeaderText;
        }
        sw.WriteLine(columnTitle);
        //写入列内容  
        for (int j = 0; j < dgv.Rows.Count; j++)
        {
            string columnValue = "";
            for (int k = 0; k < dgv.Columns.Count; k++)
            {
                if (k > 0)
                {
                    columnValue += "\t";
                }
                if (dgv.Rows[j].Cells[k].Value == null)
                    columnValue += "";
                else
                    columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();
            }
            sw.WriteLine(columnValue);
        }
        sw.Close();
        myStream.Close();
    }
    catch (Exception e)
    {
        MessageBox.Show(e.ToString());
    }
    finally
    {
        sw.Close();
        myStream.Close();
    }
}

 

 

方法二：包含图片

 

Microsoft.Office.Interop.Excel.Application Myexcel = new Microsoft.Office.Interop.Excel.Application();
            if (Myexcel == null)
            {
                return;
            }
            Microsoft.Office.Interop.Excel._Workbook xBk;
            xBk = Myexcel.Application.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel._Worksheet xSt;
            xSt = (Microsoft.Office.Interop.Excel._Worksheet)xBk.ActiveSheet;

            //设置标题等 
            string Title = null;
            Title = DateTime.Now.ToLongDateString() + "报价表";
            xSt.Name = Title;
            //报表的格式设置 
            //xSt.Cells[1, 6] = Title;
            // xSt.get_Range(Myexcel.Cells[1, 6], Myexcel.Cells[1, 6]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐 
            // xSt.get_Range(Myexcel.Cells[1, 6], Myexcel.Cells[1, 6]).Font.Bold = true;
            //xSt.get_Range(Myexcel.Cells[1, 6], Myexcel.Cells[1, 6]).Font.Size = 20;
            


            xSt.Cells[1, 1] = "品号";
            xSt.Cells[1, 2] = "品名";
            xSt.Cells[1, 3] = "客户品号";
            xSt.Cells[1, 4] = "图片";
            xSt.Cells[1, 5] = "客户编码";
            xSt.Cells[1, 6] = "客户名称";
            xSt.Cells[1, 7] = "数量";
            xSt.Cells[1, 8] = "币种";
            xSt.Cells[1, 9] = "汇率";
            xSt.Cells[1, 10] = "原币单价";
            xSt.Cells[1, 11] = "原币总价";
            xSt.Cells[1, 12] = "本币单价";
            xSt.Cells[1, 13] = "本币总价";
            xSt.Cells[1, 14] = "创建时间";
     
            //下面是用循环把datagridview中的内容写到excel
            for (int rowIndex = 0; rowIndex < this.dgvQuotation.Rows.Count; rowIndex++)
            {
                int colIndex = 0;
                for (colIndex = 1; colIndex <= dgvQuotation.ColumnCount; colIndex++)
                {
                    String value = null;
                    if (dgvQuotation.Rows[rowIndex].Cells[colIndex - 1].Value != null)
                    {
                        value = dgvQuotation.Rows[rowIndex].Cells[colIndex - 1].Value.ToString();
                        if (dgvQuotation.Columns[colIndex - 1].Name == "图片") //处理
                        {
                            Image img;
                            //如果是二进制形式:
                            //MemoryStream ms = new MemoryStream((byte[])dgvQuotation.Rows[rowIndex].Cells[colIndex - 1].Value);
                            if (File.Exists(@"D:\产品图片\" + dgvQuotation.Rows[rowIndex].Cells[0].Value.ToString() + ".jpg"))
                            {
                                //需要判断是否存在图片
                                 img = Image.FromFile(@"D:\产品图片\" + dgvQuotation.Rows[rowIndex].Cells[0].Value.ToString() + ".jpg");//双引号里是图片的路径
                                
                            }
                            else
                            {
                                //需要判断是否存在图片
                                 img = Image.FromFile(@"D:\产品图片\LOGO.jpg");//双引号里是图片的路径
                             
                            }
                            
                            //Image img = Image.FromStream(ms);
                            //路径
                           // Image img = GetImage(value);

                            string tmpName = tmpPath + "\\temp" + rowIndex + ".bmp";
                            img.Save(tmpName);
                            string cellAddr = (GetAddress(rowIndex + 4, colIndex));
                            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)xSt.get_Range(cellAddr, Type.Missing);
                            float left = float.Parse(range.Left.ToString());
                            float top = float.Parse(range.Top.ToString());
                            float width = float.Parse(range.Width.ToString());
                            float height = float.Parse(range.Height.ToString());
                            xSt.Shapes.AddPicture(tmpName, MsoTriState.msoFalse, MsoTriState.msoTrue, left, top, width, height);
                            //xSt.Shapes.AddPicture(tmpName, MsoTriState.msoFalse, MsoTriState.msoTrue, left, top, 200, 200);
                            continue;

                        }

                    }

                    xSt.Cells[rowIndex + 4, colIndex] = value;
                }

            }

            //后台处理 
            //用户无法看到
            Myexcel.Visible = false;
            //允许打开对话框保存文件
            Myexcel.DisplayAlerts = true;
            
           // xBk.Close(true, "D:\\1.xls", null);
            xSt = null;
            xBk = null;
            Myexcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Myexcel);
            Myexcel = null;