ASP.NET打印EXCEl报表技术总结

序言：我们在做企业项目或者一些管理系统的时候往往会用到导出到excel报表这项功能，下面我介绍的是用windows自带的excel来打印

首先必须引入：Interop.Excel.dll、Interop.Microsoft.Office.Core.dll这两个分别是microsoft对应excel的驱动

如何加如：在bin文件夹---右键添加引用

选择对应的office版本加入上面两个dll；

打印：

在后台打印操作如下：

假设得到了数据集DataSet ds=new DataSet（）；

 #region copy template to target dir

        string sourceFile = "";

        sourceFile = templateFilePath + @"\excel.xls";

        string targetFile = Server.MapPath(".") + @"\template\" + System.DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";

        try

        {

            System.IO.File.Copy(sourceFile, targetFile);

        }

        catch { }

        #endregion

这一行代码是将模板复制到新的文件夹并按时间来命名，牵扯到文件操作，需要引用system.io

        Excel.Application xApp = null;//创建excel应用程序

        Excel.Workbook xBook = null;//工作簿

        Excel.Worksheet xSheet = null;//工作簿中创建工作表

        bool PrintNote = false;

        try

        {

            xApp = new Excel.ApplicationClass();

            xBook = xApp.Workbooks.Open(targetFile,

                    Type.Missing, Type.Missing, Type.Missing, Type.Missing

                    , Type.Missing, Type.Missing, Type.Missing, Type.Missing

                    , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            xSheet = (Excel.Worksheet)xBook.Sheets[1];//有三个工作簿选择第一个，也可选择其他

            int startrow = 5;//从第几行开始打印

            int col = 0;

            int i = 0;
  for (i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
          col = startrow + i;//数据行
           //下面就要对xsheet填入数据了
          有两种方式：方式一
           xSheet.get_Range("A" + col , Type.Missing).Value2 = ds.Tables[0].Rows[i]["NAME"].ToString().Trim();
           xSheet.get_Range("A" + col , Type.Missing).Value2 = ds.Tables[0].Rows[i]["sex"].ToString().Trim();
           xSheet.get_Range("A" + col , Type.Missing).Value2 = ds.Tables[0].Rows[i]["age"].ToString().Trim();
           方式二：参数为行、列
           xSheet.cells[col,1]=ds.Tables[0].Rows[i]["NAME"].ToString().Trim();
           xSheet.cells[col,1]=ds.Tables[0].Rows[i]["NAME"].ToString().Trim();
           xSheet.cells[col,1]=ds.Tables[0].Rows[i]["NAME"].ToString().Trim();
          }
       数据打印完成后加边框或其他操作　
                xSheet.get_Range("A" + (3).ToString(), "K" + (col).ToString()).Borders.LineStyle = 1;//加边框
                xSheet.get_Range("A" + (3).ToString(), "A" + (col).ToString()).ShrinkToFit = true;//内容适应文本框大小
xBook.Save();//用于保存xSheet的数据

在需要合并单元格时序特别注意，必须要在没有填入数据前进行和并，否则会提示：覆盖前内容等提示；

合并方法 xSheet.get_Range("A" + (j + 3).ToString(), "A" + (j + 4).ToString()).MergeCells = true;

xSheet.get_Range(xSheet.cells[col,1], xSheet.cells[col,1]).MergeCells = true;

在操作完excel之后一定要记得关闭应用不然下次操作会提示excel占用。操作方法：

 if (xSheet != null)

                {

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

                    xSheet = null;

                }

                if (xBook != null)

                {

                    try

                    {

                        xBook.Close(false, System.Type.Missing, System.Type.Missing);

                    }

                    catch { }

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

                    xBook = null;

                }

                if (xApp != null)

                {

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

                    xApp = null;

                }

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

                DownLoadFile(targetFile, IYear.ToString() + ".xls");//下载文档

                return true;

下载文档的方法：

  protected void DownloadFile(string filename, string downname)

    {

        FileStream f = new FileStream(filename, FileMode.Open);

        byte[] buffer = new byte[f.Length];

        f.Read(buffer, 0, buffer.Length);

        f.Close();

        filename = filename.Replace(@"/", @"\");

        string saveFileName = "";

        int intStart = filename.LastIndexOf("\\") + 1;

        saveFileName = filename.Substring(intStart, filename.Length - intStart);

        Response.Clear();

        Response.Buffer = true;

        Response.Charset = "GB2312";

        try

        {

            if (filename.ToLower().IndexOf("doc") >= 0)

            {

                Response.ContentType = "application/ms-word";

            }

            else

            {

                if (filename.ToLower().IndexOf("xls") >= 0)

                {

                    Response.ContentType = "application/ms-excel";

                }

                else

                {

                    Response.ContentType = "application/octet-stream";

                }

            }

        }

        catch

        {

            Response.ContentType = "application/octet-stream";

        }

        Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(downname));

        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

        Response.BinaryWrite(buffer);

        Response.GetHashCode();

        Response.End();

    }

文章打印成功

三：读取excel'中的数据到dataTable中

#region 

        public DataTable ReadExcelFile(string Filename, string SheetName, int _startIndex)

        {

            DataTable dt = null;

            OleDbConnection Con = null;

            if (Filename.ToLower().IndexOf(".xlsx") >= 0)

            {

                Con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Filename + ";Persist Security Info=False;Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1'");

            }

            else

            {

                Con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Filename + ";Persist Security Info=False;Extended Properties='Excel 8.0;HDR=NO;IMEX=1'");

            }

            Con.Open();

            OleDbCommand Cmd = new OleDbCommand("Select * From [" + SheetName + "$]", Con);

            OleDbDataReader Reader = Cmd.ExecuteReader();

            dt = GetDataTableFromDataReader(Reader, _startIndex);

            Con.Close();

            return dt;

        }

        /// <summary>

        /// Return a DataTable From OleDbDataReader  
        /// </summary>

        /// <param name="reader">OleDbDataReader</param>

        /// <param name="_startIndex">起始行</param>

        /// <returns></returns>

        private DataTable GetDataTableFromDataReader(OleDbDataReader reader, int _startIndex)

        {

            DataTable schema = reader.GetSchemaTable();

            DataColumn[] columns = new DataColumn[schema.Rows.Count];

            DataColumn column;

            //Build the schema

            for (int i = 0; i < schema.Rows.Count; i++)

            {

                column = new DataColumn(); column.AllowDBNull = (bool)schema.Rows[i]["AllowDBNull"];

                column.AutoIncrement = (bool)schema.Rows[i]["IsAutoIncrement"];

                column.ColumnName = (string)schema.Rows[i]["ColumnName"];

                column.DataType = Type.GetType(schema.Rows[i]["DataType"].ToString());

                if (column.DataType == Type.GetType("System.String"))

                { column.MaxLength = (int)schema.Rows[i]["ColumnSize"]; }

                column.ReadOnly = (bool)schema.Rows[i]["IsReadOnly"];

                column.Unique = (bool)schema.Rows[i]["IsUnique"];

                columns[i] = column;

            }

            DataTable data = new DataTable();

            data.Columns.AddRange(columns);

            //Get the data itself.

            int j = 0;

            while (reader.Read())

            {

                if (j >= _startIndex)

                {

                    if (j == _startIndex)

                    {

                        for (int i = 0; i < schema.Rows.Count; i++)

                        {

                            try

                            {

                                data.Columns[i].ColumnName = reader[i].ToString();

                            }

                            catch

                            {

                                data.Columns[i].ColumnName = " ";

                            }

                        }

                    }

                    else

                    {

                        DataRow row = data.NewRow();

                        for (int i = 0; i < schema.Rows.Count; i++)

                        { row[i] = reader[i]; }

                        data.Rows.Add(row);

                    }

                }

                j = j + 1;

            }

            return data;

        }

        #endregion

    }

巴特西

ASP.NET打印EXCEl报表技术总结

最新文章

热门文章