Aspose.cell中的Excel模板导出数据

  //Excel模板导数据(Eexcel中根据DataTable中的个数，给多个Sheet中的模板赋值) dict中模板设置 &=$qyear datatable中设置 &=dtName.cloumnName
        public void DataSetToManyExcel(string fileName, string fileUrl, Page page, DataSet my_ds, Dictionary<string, string> dict = null, params int[] columns)
        {
            WorkbookDesigner wbookdesiger = new WorkbookDesigner();
            wbookdesiger.Open(fileUrl); //打开模板    
            try
            {
                if (my_ds.Tables.Count > 0)
                {
                    for (var i = 0; i < my_ds.Tables.Count; i++)
                    {
                        //设置DataTable
                        wbookdesiger.SetDataSource(my_ds.Tables[i]);
                        //设置变量
                        if (dict != null)
                        {
                            foreach (var di in dict)
                                wbookdesiger.SetDataSource(di.Key, di.Value);
                        }
                        wbookdesiger.Process(i, false);
                        //清除数据源
                        wbookdesiger.ClearDataSource();
                    }

　　　　　　　　　　　　　　

　　　　　　　　　　if (columns.Length > 0)
　　　　　　　　　　{
　　　　　　　　　　　　Style style = wbookdesiger.Workbook.Styles[wbookdesiger.Workbook.Styles.Add()];//新增样式
　　　　　　　　　　　　style.IsTextWrapped = true;//文本换行样式
　　　　　　　　　　　　columns.ForEach(c =>
　　　　　　　　　　　　{
　　　　　　　　　　　　　　var cells = wbookdesiger.Workbook.Worksheets[0].Cells[c];
　　　　　　　　　　　　　　cells.SetStyle(style);
　　　　　　　　　　　　});
　　　　　　　　　　}

                    fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls";
                    wbookdesiger.Save(fileName, SaveType.OpenInExcel, FileFormatType.Excel2003, page.Response);
                    page.Response.End();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Excel模板导数据(Eexcel中根据DataTable中的个数，复制第一个Sheet中的模板，然后分别赋值)
        public void DataSetToOneExcel(string fileName, string fileUrl, Page page, DataSet my_ds, string tabName = "", Dictionary<int, string> my_dic = null)
        {

            WorkbookDesigner my_designer = new WorkbookDesigner();
            my_designer.Open(fileUrl);//打开文件
            Workbook my_workbook = my_designer.Workbook;
            try
            {
                if (my_ds.Tables.Count > 0)
                {
                    //给表格赋值
                    for (var i = 0; i < my_ds.Tables.Count; i++)
                    {
                        var temp = i + 1 >= my_ds.Tables.Count ? i : i + 1;

                        //TableName
                        if (i == 0)
                            my_ds.Tables[i].TableName = tabName != "" ? tabName : "dt";
                        else
                            my_ds.Tables[temp].TableName = tabName != "" ? tabName + i : "dt" + temp;

                        my_workbook.Worksheets.AddCopy(0);//复制前一个Sheet模板
                        my_workbook.Worksheets[i + 1].Replace(my_ds.Tables[0].TableName, my_ds.Tables[temp].TableName);//替换当前Sheet的下个Sheet的TableName

                        my_designer.SetDataSource(my_ds.Tables[i]);
                        my_designer.Process(i, false);
                        my_designer.ClearDataSource();
                    }

                    //删除最后一个sheet
                    my_workbook.Worksheets.RemoveAt(my_workbook.Worksheets.Count - 1);

                    //给Sheet的Name赋值
                    if (my_dic != null)
                    {
                        for (var i = 0; i < my_workbook.Worksheets.Count; i++)
                        {
                            foreach (var dd in my_dic)
                            {
                                if (i == dd.Key)
                                    my_workbook.Worksheets[i].Name = dd.Value;
                            }
                        }
                    }

                    fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls";
                    my_designer.Save(fileName, SaveType.OpenInExcel, FileFormatType.Excel2003, page.Response);
                    page.Response.End();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

 补充：当表格数据列是动态的时候，显然模板就不大适用了，这时候我们可以使用Apose.Cells的填充方法

            Aspose.Cells.WorkbookDesigner wbookdesiger = new Aspose.Cells.WorkbookDesigner();
            wbookdesiger.Open(fileUrl); //打开模板    
            //wbookdesiger.SetDataSource(dt);
            //wbookdesiger.Process();//数据渲染 
            var workbook = wbookdesiger.Workbook;
            //最后合成一个DT来，一个合成的动态数据表
            Aspose.Cells.Cell cell = workbook.Worksheets[0].Cells.FindString("#$DATATABLE$#", null, true);//模板中的列，找出模板中固定的单元格
            if (cell != null)
            {
                workbook.Worksheets[0].Cells.ImportDataTable(dt, true, cell.Name);
            }
            int temp = 0;
　　　　　　  //合并单元格
            while (temp < dt.Rows.Count)
            {
                var rows = dt.Select(" 序号=" + dt.Rows[temp]["序号"]);
                workbook.Worksheets[0].Cells.Merge(1 + temp, 0, rows.Length, 1);
                workbook.Worksheets[0].Cells.Merge(1 + temp, 1, rows.Length, 1);
                workbook.Worksheets[0].Cells.Merge(1 + temp, 2, rows.Length, 1);
                workbook.Worksheets[0].Cells.Merge(1 + temp, 3, rows.Length, 1);
                workbook.Worksheets[0].Cells.Merge(1 + temp, 4, rows.Length, 1);
                workbook.Worksheets[0].Cells.Merge(1 + temp, 11, rows.Length, 1);
                workbook.Worksheets[0].Cells.Merge(1 + temp, 12, rows.Length, 1);
                workbook.Worksheets[0].Cells.Merge(1 + temp, 13, rows.Length, 1);
                temp += rows.Length;
            }

            var sheet = workbook.Worksheets[0];
            var cells = sheet.Cells;
            int columnCount = cells.MaxColumn;  //获取表页的最大列数
            int rowCount = cells.MaxRow;        //获取表页的最大行数
            //适应列宽
            for (int col = 0; col < columnCount; col++)
            {
                sheet.AutoFitColumn(col, 0, rowCount);
            }
            for (int col = 0; col < columnCount; col++)
            {
                cells.SetColumnWidthPixel(col, cells.GetColumnWidthPixel(col) + 30);
            }
            //表格线
            Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
            style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
            style.Borders[Aspose.Cells.BorderType.TopBorder].Color = System.Drawing.Color.Black;
            style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle =Aspose.Cells. CellBorderType.Thin;
            style.Borders[Aspose.Cells.BorderType.BottomBorder].Color = System.Drawing.Color.Black;
            style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
            style.Borders[Aspose.Cells.BorderType.LeftBorder].Color = System.Drawing.Color.Black;
            style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle =Aspose.Cells.CellBorderType.Thin;
            style.Borders[Aspose.Cells.BorderType.RightBorder].Color = System.Drawing.Color.Black;
            Aspose.Cells.Range wstrange = sheet.Cells.CreateRange(0, 0, sheet.Cells.MaxRow + 1, sheet.Cells.MaxColumn + 1);
            Aspose.Cells.StyleFlag stFlag = new Aspose.Cells.StyleFlag();
            stFlag.All = true;
            wstrange.ApplyStyle(style, stFlag);
            var stream = workbook.SaveToStream();
            return File(stream.ToArray(), "application/octet-stream", "xxxx.xls");