aspose.cells导出ＥＸＣＥＬ分页分组函数

     引用命名空间

　　using System.Data.SqlClient;
　　using Aspose.Cells;
　　using System.Drawing;
　　using System.IO;

       /// <summary>
       /// 导出ＥＸＣＥＬ分页分组函数
       /// </summary>
       /// <param name="PageNum">每页显示数据条数</param>
       /// <param name="PageCount">每页明细＋表头的行数</param>
       /// <param name="HeadRow">等於ＰageCount-PageNum 表頭所占行數</param>
       /// <param name="dtExcel">所有的信息</param>
       /// <param name="dt">按条件分组数据</param>
       /// <param name="imagePath">图片路径</param>
       /// <param name="ExcelName">ＥＸＣＥＬ名称</param>
       /// <param name="ImageWidth">图片宽度</param>
       /// <param name="ImageHight">图片高度</param>
        /// <param name="strFlag">分組分頁為１，分組為２，分頁為３</param>
        public void ImportGroupExcel(int PageNum,int PageCount,int HeadRow,DataTable dtExcel,DataTable dt,string imagePath,string ExcelName,int ImageWidth,int ImageHight,string strFlag)
        {
            Workbook workbook = new Workbook();

            Worksheet sheet = (Worksheet)workbook.Worksheets[0];
            Cells cells = sheet.Cells;//单元格

         
            byte[] bytes = SmallImageDatabytes(imagePath,ImageWidth,ImageHight);//自動縮小圖片後轉換為二進制流
            MemoryStream ms = new MemoryStream(bytes);

            sheet.Pictures.Add(0, 0, ms);//導入圖片到ＥＸＣ
          
         　 int intCount = 0;
            int intflag = 0;
            int intGroup = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //串過濾條件
                string strWhere = "";
                for (int t = 0; t < dt.Columns.Count; t++)
                {

                    if (strWhere == "")
                        strWhere = dt.Columns[t].ToString() + "='" + dt.Rows[i][dt.Columns[t].ToString()] + "'";
                    else
                        strWhere += "and" + dt.Columns[t].ToString() + "='" + "dt[" + i + "][" + dt.Columns[t].ToString() + "]'";
                }

                DataRow[] arrayDR = dtExcel.Select(strWhere);
                int intOrdernoNum = dtExcel.Select(strWhere).Length;

              
                if (strFlag == "1")
                {
                    if (intOrdernoNum > PageNum)
                    {
                        int intPageNum = 0;

                        intPageNum = int.Parse(Math.Ceiling(intOrdernoNum / decimal.Parse(PageNum.ToString())).ToString());

                        intflag += 1;
                        for (int n = 1; n <= intPageNum; n++)
                        {
                           
                            sheet.Pictures.Add((n + intCount - intflag + i) * PageCount, 0, ms);
                            for (int m = 0; m < PageNum; m++)
                            {

                                if (intOrdernoNum == m + (n - 1) * PageNum)
                                    break;
                                for (int k = 0; k < dtExcel.Columns.Count; k++)
                                {
                                    Cell cell = sheet.Cells[m + HeadRow-1 + (n + intCount - intflag + i) * PageCount, k];
                                    cell.PutValue(arrayDR[m + (n - 1) * PageNum][dtExcel.Columns[k].ToString()].ToString());
                                }
                               
                            }

                        }
                        intCount += intPageNum;

                    }
                    else
                    {
                        sheet.Pictures.Add((i + intCount - intflag) * PageCount, 0, ms);
                        for (int j = 0; j < PageNum; j++)
                        {
                            if (intOrdernoNum == j)
                                break;
                            for (int k = 0; k < dtExcel.Columns.Count; k++)
                            {
                                Cell cell = sheet.Cells[j + HeadRow-1 + (i + intCount - intflag) * PageCount, k];
                                cell.PutValue(arrayDR[j][dtExcel.Columns[k].ToString()].ToString());
                            }
                           
                        }

                    }

                }

                else if (strFlag == "2")
                {
                  
                    for (int r = 0; r < intOrdernoNum; r++)
                    {
                        for (int k = 0; k < dtExcel.Columns.Count; k++)
                        {
                            Cell cell = sheet.Cells[r + HeadRow-1 + intGroup + i * HeadRow, k];
                            cell.PutValue(arrayDR[r][dtExcel.Columns[k].ToString()].ToString());
                        }
                    }
                    intGroup += intOrdernoNum;
                }
              
            }
            if (strFlag == "3")
            {
                for (int intPagecount = 1; intPagecount <= int.Parse(Math.Ceiling(dtExcel.Rows.Count / decimal.Parse(PageNum.ToString())).ToString()); intPagecount++)
                {
                    sheet.Pictures.Add((intPagecount - 1) * PageCount, 0, ms);
                    for (int n = 0; n < PageNum; n++)
                    {
　　　　　　　　　　　 if (n + (intPagecount - 1) * PageNum == dtExcel.Rows.Count)
                            break;
                        for (int t = 0; t < dtExcel.Columns.Count; t++)
                        {
                            Cell cell = sheet.Cells[n + HeadRow + (intPagecount - 1) * PageCount, t];
                            cell.PutValue(dtExcel.Rows[n + (intPagecount-1) * PageNum][dtExcel.Columns[t].ToString()].ToString());
                        }
                    }

                }
            }
            string filename = string.Format("{0}{1}.xls", ExcelName, Convert.ToDateTime(DateTime.Now).ToString("yyyyMMdd")); //文件默认命名方式，可以自定义
          
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "utf-8";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename);
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/ms-excel";
            Response.BinaryWrite(workbook.SaveToStream().ToArray());
            Response.End(); 
        }

       

        /// <summary>
        /// 按給寬與長自動縮小圖片後轉換為二進制流
        /// </summary>
        /// <param name="FilePath"></param>
        /// <param name="intWidth"></param>
        /// <param name="intHeight"></param>
        /// <returns></returns>
        public static byte[] SmallImageDatabytes(string FilePath, int intWidth, int intHeight)
        {
            if (!File.Exists(FilePath))
                return null;

            Bitmap objPic = new System.Drawing.Bitmap(FilePath);

            Bitmap myBitmap = new System.Drawing.Bitmap(objPic, intWidth, intHeight);

            using (MemoryStream curImageStream = new MemoryStream())
            {
                myBitmap.Save(curImageStream, System.Drawing.Imaging.ImageFormat.Png);
                curImageStream.Flush();

                byte[] bmpBytes = curImageStream.ToArray();
                //如果转字符串的话
                //string BmpStr = Convert.ToBase64String(bmpBytes);
                return bmpBytes;
            }
        }

 

用法：

 protected void Button3_Click(object sender, EventArgs e)
        {
           string imagePath = @"D:\mywebform\MyStudyWeb\MyStudyWeb\MyStudyWeb\image\Okahata.bmp";//自動縮小圖片後轉換為二進制流
             DBHelper.strCon = System.Configuration.ConfigurationManager.ConnectionStrings["ＤＢＮＡＭＥ"].ToString();
         
            DataTable dtExcel = DBHelper.GetDataTable("select * from sa030_temp order by orderno ");
          
            DataTable dt = dtExcel.DefaultView.ToTable(true, "orderno");//分组生成新的ＤＡＴＡＴＡＢＬＥ
           
           ImportGroupExcel(12, 17,5,dtExcel, dt, imagePath, "test", 120, 60,"3");
        }