首页新闻找找看学习计划

怎样把数据源导入到Excel

0
悬赏园豆:30 [已关闭问题]

我想做的功能是:数据从数据库里面读取出来,是存放在一个数据源当中,怎样把这个数据源中的数据导入到Excel中呢?帮各位大侠帮帮忙!万分感谢。我先做的思路是把数据源弄到GridView中显示,在从GridView中导入到Excel中,但是当涉及到分页的时候,不是每一次都要从新导一次,显的很麻烦,怎样能不能直接把读取到的直接导入到Excel中呢?

something的主页 something | 初学一级 | 园豆:5
提问于:2010-04-20 16:20
< >
分享
其他回答(2)
0

//先要在工具箱中添加 Microsoft.Office.Interop.Excel 组建DLL的引用

   private const string DATAWORDPATH = @"C:\folder\doc\datadoc\";
        private const string IMAGEWORDPATH = @"C:\folder\doc\imagedoc\";
        private const string IMAGEPATH = @"C:\folder\image\";
        private const string EXCELPATH = @"C:\folder\excel\";
        private const string TXTPATH = @"C:\folder\txt\";
        private const string IMAGEPOSTFIX = ".bmp";
        private const string WORDPOSTFIX = ".doc";
        private const string EXCELPOSTFIX = ".xls";
        private const string TXTPOSTFIX = ".txt";
        private const int DATADISTANCE = 5;
        private const int TABDISTANCE = 8;

 public string ExportToExcel(DataTable dt)
        {
            try
            {
                if (dt == null)
                    return "数据为空!";

                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    return "无法创建Excel对象,可能您的电脑未安装Excel";
                }
                else
                {
                    Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
                    Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
                    Microsoft.Office.Interop.Excel.Range range = null;
                    long totalCount = dt.Rows.Count;
                    long rowRead = 0;
                    float percent = 0;
                    //string fileName = saveDia.FileName;
                    string d = DateTime.Now.ToString();
                   // string fileName = @"e:\" + d + ".xls";
                    string fileName = @"e:\aa.xls";
                    //if (!System.IO.File.Exists(fileName))
                    //{
                    //    System.IO.File.Create(fileName);//创建文件
                    //}

                    //写入标题
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                        //range.Interior.ColorIndex = 15;//背景颜色
                        range.Font.Bold = true;//粗体
                        range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中
                        //加边框
                        range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
                        //range.ColumnWidth = 4.63;//设置列宽
                        //range.EntireColumn.AutoFit();//自动调整列宽
                        //r1.EntireRow.AutoFit();//自动调整行高
                    }
                    //写入内容
                    for (int r = 0; r < dt.DefaultView.Count; r++)
                    {
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];
                            range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];
                            range.Font.Size = 9;//字体大小
                            //加边框
                            range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
                            range.EntireColumn.AutoFit();//自动调整列宽
                        }
                        rowRead++;
                        percent = ((float)(100 * rowRead)) / totalCount;
                        //System.Windows.Forms.Application.DoEvents();
                    }
                    range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
                    if (dt.Columns.Count > 1)
                    {
                        range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
                    }
                    try
                    {
                        workbook.Saved = true;
                        workbook.SaveCopyAs(fileName);
                    }
                    catch (Exception ex)
                    {
                        return "导出文件时出错,文件可能正被打开!"+ex.Message;
                    }

                    workbooks.Close();
                    if (xlApp != null)
                    {
                        xlApp.Workbooks.Close();
                        xlApp.Quit();
                        int generation = System.GC.GetGeneration(xlApp);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                        xlApp = null;
                        System.GC.Collect(generation);
                    }
                    GC.Collect();//强行销毁

                    #region 强行杀死最近打开的Excel进程
                    System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
                    System.DateTime startTime = new DateTime();
                    int m, killId = 0;
                    for (m = 0; m < excelProc.Length; m++)
                    {
                        if (startTime < excelProc[m].StartTime)
                        {
                            startTime = excelProc[m].StartTime;
                            killId = m;
                        }
                    }
                    if (excelProc[killId].HasExited == false)
                    {
                        excelProc[killId].Kill();
                    }
                    #endregion

                    return "导出成功!";
                }
            }
            catch
            {
                return "导出失败,请检查您的电脑是否安装office2003或以上版本!";
            }
       
        }

yeyang | 园豆:418 (菜鸟二级) | 2010-04-20 17:27
0

简单的两种方法:

        #region 将整个页面输出为Excel文件
        /// <summary>
        /// 将整个页面输出为Excel文件
        /// </summary>
        /// <param name="aPage">当前页面</param>
        /// <param name="fileName">文件名</param>
        public static void ExportExcel(System.Web.UI.Page aPage, string fileName)
        {
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            aPage.EnableViewState = false;
        }
        #endregion

        #region 根据传入的控件并导出为Excel文件
        /// <summary>
        /// 根据传入的控件并导出为Excel文件
        /// </summary>
        /// <param name="source">控件名</param>
        /// <param name="filename">导出文件名</param>
        public void ExportExcel(System.Web.UI.Control source, string filename)
        {
            //设置Http的头信息,编码格式Excel
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename + ".xls", System.Text.Encoding.UTF8));
            HttpContext.Current.Response.ContentType = "application/ms-excel";

            HttpContext.Current.Response.Charset = "UTF-8";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;

            //关闭控件的视图状态
            source.Page.EnableViewState = false;

            //初始化HtmlWriter
            System.IO.StringWriter writer = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWriter = new System.Web.UI.HtmlTextWriter(writer);
            source.RenderControl(htmlWriter);

            //输出
            HttpContext.Current.Response.Write(writer.ToString());
            HttpContext.Current.Response.End();
        }
        #endregion

Astar | 园豆:40805 (高人七级) | 2010-04-20 18:08
0
快速开发平台 | 园豆:177 (初学一级) | 2010-04-20 20:43
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册