WEB转EXCEL详细说明

WEB转EXCEL详细说明

一、运用方法

主要运用namespace Microsoft.Office.Interop.Excel中的Applicatio

二、实现步骤

引用EXCEL：在COM中引用Microsoft Office 11.0 Object Libray和Microsoft EXCEL 11.0 Object Library。

 

代码：

/// <summary>

        /// 创建Excel转Excel

        /// </summary>

        /// <param name="fileName">文件名</param>

        /// <param name="grid">转的Dataset</param>

        /// <param name="i">写入Exel的行坐标</param>

        /// <param name="j">写入Exel的列坐标</param>

        /// <param name="templetFilePath">保存URL</param>

        /// <returns></returns>

        /// <param name="pd"></param>

        /// <returns></returns>

        public bool TOExcel(string fileName, DataSet grid, int r, int c, string templetFilePath)

        {

            try

            {

                //创建一个Application对象并使其不可见

                app = new Microsoft.Office.Interop.Excel.ApplicationClass();

                app.Visible = false;

                    //创建一个WorkBook对象

                    workBook = app.Workbooks.Add(missing);

                //得到WorkSheet对象

                workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);

                int rowCount = grid.Tables[0].Rows.Count + 1; //DataTable行数+GirdHead

                int colCount = grid.Tables[0].Columns.Count; //DataTable列数

 

                //利用二维数组批量写入

                string[,] arr = new string[rowCount, colCount];

 

                for (int j = 0; j < rowCount; j++)

                {

                    for (int k = 0; k < colCount; k++)

                    {

                        if (j == 0)

                        {

                            arr[j, k] = grid.Tables[0].Rows[0].ItemArray[k].ToString();

 

                        }

                        else

                        {

                            arr[j, k] = grid.Tables[0].Rows[j - 1].ItemArray[k].ToString();

                        }

                    }

                }

 

                range = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[r, c]; //写入Exel的坐标

                range = range.get_Resize(rowCount, colCount);

                range.Value2 = arr;

 

                workBook.SaveAs(templetFilePath + fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);

 

                if (workBook.Saved)

                {

                    workBook.Close(null, null, null);

                    app.Workbooks.Close();

                    app.Quit();

                }

 

                if (range != null)

                {

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(range);

                    range = null;

                }

 

                if (workSheet != null)

                {

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);

                    workSheet = null;

                }

                if (workBook != null)

                {

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);

                    workBook = null;

                }

                if (app != null)

                {

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

                    app = null;

                }

 

                GC.Collect();//强制代码垃圾回收

                return true;

            }

            catch { return false; }

        }

        /// <summary>

        /// 通过模板转Excel

        /// </summary>

        /// <param name="fileName">文件名</param>

        /// <param name="grid">转的Dataset</param>

        /// <param name="i">写入Exel的行坐标</param>

        /// <param name="j">写入Exel的列坐标</param>

        /// <param name="templetFilePath">保存URL</param>

        /// <returns></returns>

        /// <param name="pd"></param>

        /// <returns></returns>

        public bool MTOExcel(string fileName, DataSet grid, int r, int c, string templetFilePath)

        {

            try

            {

                //创建一个Application对象并使其不可见

                app = new Microsoft.Office.Interop.Excel.ApplicationClass();

                app.Visible = false;

                    //打开模板文件，得到WorkBook对象

                    workBook = app.Workbooks.Open(templetFilePath + fileName, missing, missing, missing, missing, missing,

                        missing, missing, missing, missing, missing, missing, missing, missing, missing);

                //得到WorkSheet对象

                workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);

                int rowCount = grid.Tables[0].Rows.Count + 1; //DataTable行数+GirdHead

                int colCount = grid.Tables[0].Columns.Count; //DataTable列数

 

                //利用二维数组批量写入

                string[,] arr = new string[rowCount, colCount];

 

                for (int j = 0; j < rowCount; j++)

                {

                    for (int k = 0; k < colCount; k++)

                    {

                        if (j == 0)

                        {

                            arr[j, k] = grid.Tables[0].Rows[0].ItemArray[k].ToString();

 

                        }

                        else

                        {

                            arr[j, k] = grid.Tables[0].Rows[j - 1].ItemArray[k].ToString();

                        }

                    }

                }

 

                range = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[r, c]; //写入Exel的坐标

                range = range.get_Resize(rowCount, colCount);

                range.Value2 = arr;

 

                workBook.SaveAs(templetFilePath + fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);

 

                if (workBook.Saved)

                {

                    workBook.Close(null, null, null);

                    app.Workbooks.Close();

                    app.Quit();

                }

 

                if (range != null)

                {

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(range);

                    range = null;

                }

 

                if (workSheet != null)

                {

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);

                    workSheet = null;

                }

                if (workBook != null)

                {

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);

                    workBook = null;

                }

                if (app != null)

                {

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

                    app = null;

                }

 

                GC.Collect();//强制代码垃圾回收

                return true;

            }

            catch { return false; }

        }