winForm 里datagrid数据如何 导出到excel

Question

带格式的数据导出
 public void OpenExcelFile()
        {
            try
            {
                Excel.Application excelapp;
                excelapp = new Microsoft.Office.Interop.Excel.Application();
                Excel.Workbook book = excelapp.Workbooks.Open(Application.StartupPath + @"\ReportFile\018.xlt",
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);                Excel.Worksheet st1 = (Excel.Worksheet)book.Worksheets[1];                int rowBase = 3;
                int colBase = 1;
                int rowIndex = rowBase;
                int colIndex = colBase;                foreach (DataRowView row in this.m_DataView)
                {
                    Excel.Range range = (Excel.Range)st1.Rows[rowIndex + 1, Missing.Value];
                    range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Missing.Value);                    st1.Cells[rowIndex, colIndex] ="";
                    colIndex++;                    st1.Cells[rowIndex, colIndex] = "'" + row["YeWuLeiXings"].ToString();
                    colIndex++;                    st1.Cells[rowIndex, colIndex] = "'" + row["QuYuHuaFen"].ToString();
                    colIndex++;                    st1.Cells[rowIndex, colIndex] = "'" + row["BeiZhu"].ToString();                    rowIndex++;
                    colIndex = colBase;
                }                excelapp.Visible = true;
            }
            catch (Exception ex)
            {
                Wip.Common.ShowExecResult(Wip.Common.GetExecResult(ex), false);
            }
            finally
            {
                Cursor.Current = Cursors.Default;
            }        }

Answer 1

//帮朋友写了一个在.Net中将DataGridView到处为Exce
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace ExcelExports
...{
    public class ExcelExports
    ...{
        /**//// <summary>
        /// 把DataGridView到处到Excel中
        /// </summary>
        /// <param name="gridView">目标DataGridView</param>
        /// <param name="fileName">保存文件名称</param>
        /// <param name="isShowExcle">是否显示Excel界面</param>
        /// <returns>导出是否成功</returns>
        public static bool ExportForDataGridview(DataGridView gridView,string fileName,bool isShowExcle)
        ...{
            
            //建立Ｅｘｃｅｌ对象
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            try
            ...{
                if (app == null)
                ...{
                    return false;
                }
                app.Visible = isShowExcle;
                Workbooks workbooks = app.Workbooks;
                _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                Sheets sheets = workbook.Worksheets;
                _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
                if (worksheet == null)
                ...{
                    return false;
                }
                string sLen = "";
                //取得最后一列列名
                char H=(char)(64+gridView.ColumnCount /26 );
                char L = (char)(64 + gridView.ColumnCount % 26);
                if (gridView.ColumnCount < 26)
                ...{
                    sLen = L.ToString();
                }
                else
                ...{
                    sLen = H.ToString() + L.ToString();
                }
                //标题
                string sTmp = sLen + "1";
                Range ranCaption = worksheet.get_Range(sTmp, "A1");
                string[] asCaption = new string[gridView.ColumnCount];
                for (int i = 0; i < gridView.ColumnCount; i++)
                ...{
                    asCaption[i] = gridView.Columns[i].HeaderText;
                }
                ranCaption.Value2 = asCaption;                //数据
                object[] obj = new object[gridView.Columns.Count];
                for (int r = 0; r < gridView.RowCount-1; r++)
                ...{
                    for (int l = 0; l < gridView.Columns.Count; l++)
                    ...{
                        if (gridView[l, r].ValueType==typeof(DateTime))
                        ...{
                            obj[l] = gridView[l, r].Value.ToString();
                        }
                        else
                        ...{
                            obj[l] = gridView[l, r].Value;
                        }
                    }
                    string cell1 = sLen + ((int)(r + 2)).ToString();
                    string cell2="A"+((int)(r+2)).ToString();
                    Range ran = worksheet.get_Range(cell1, cell2);
                    ran.Value2 = obj;
                }
                //保存
                workbook.SaveCopyAs(fileName);
                workbook.Saved = true;
            }
            finally
            ...{
                //关闭
                app.UserControl = false;
                app.Quit();
            }
            return true;        }
    }
}

Answer 2

我也写一个
//声明一个
Excel.Application myExcel=new Excel.ApplicationClass();
myExcel.Visible=false; //创建一个导入数据的object,保证所有的线程都是安全的
object miss=Missing.Value;
Excel.Workbook wb=myExcel.Workbooks.Add(miss);
//建立一个sheet
Excel.Worksheet ws=(Excel.Worksheet)wb.Sheets.get_Item(1);
//获得当前sdatagrid里面的行和列
int RowCont=ds.Tables[0].Rows.Count;
int ColCont=ds.Tables[0].Columns.Count; //将信息写入数组里面
string[,] myarr=new string[RowCont,ColCont];
for(int i=0;i<RowCont;i++)
{
for(int j=0;j<ColCont;j++)
{
myarr[i,j]=ds.Tables[0].Rows[i][j].ToString();
}
} //将数组写入里面
Excel.Range range=(Excel.Range)ws.Cells[1,1];
range=(Excel.Range)range.get_Item(RowCont,ColCont);
range.Value2=myarr; //
wb.SaveAs("D:\\"+DateTime.Now.ToLongDateString()+".xls",miss,miss,miss,miss,miss,Excel.XlSaveAsAccessMode.xlExclusive,miss,miss,miss,miss,miss); if(wb.Saved)
{
wb.Close(null,null,null);
myExcel.Workbooks.Close();
myExcel.Quit();
} if(range==null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range=null;
}
if(ws==null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
ws=null;
}
if(wb==null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
wb=null;
}
if(myExcel==null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
myExcel=null;
}
GC.Collect();