Csharp: Create Excel Workbook or word from a Template File using aspose.Word 14.5 and aspose.Cell 8.1，aspose.cell

Csharp: Create Excel Workbook or word from a Template File using aspose.Word 14.5 and aspose.Cell 8.1，aspose.cell

winform:

/// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnGenWord_Click(object sender, EventArgs e)
        {

            Dictionary<string, string> dictSource = new Dictionary<string, string>();
            dictSource.Add("NO", "T0001");
            dictSource.Add("INDUSTRY", "捷為工作室");
            dictSource.Add("NAME", "塗聚文");

            string templateFile =("Templates/Templates.doc");
            Aspose.Words.Document doc = new Aspose.Words.Document(templateFile);

            //使用文本方式替換
            foreach (string name in dictSource.Keys)
            {
                doc.Range.Replace(name, dictSource[name], true, true);
            }

            #region 使用書簽替換模式

            Aspose.Words.Bookmark bookmark = doc.Range.Bookmarks["SEX"];
            if (bookmark != null)
            {
                bookmark.Text = "男";
            }
            bookmark = doc.Range.Bookmarks["TEL"];
            if (bookmark != null)
            {
                bookmark.Text = "13824350518*";
            }

            #endregion
            
            doc.Save("testAdvice"+DateTime.Now.ToString("yyyyMMddHHmmssfff")+".docx",Aspose.Words.SaveFormat.Docx);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnGenExcel_Click(object sender, EventArgs e)
        {
            Dictionary<string, string> dictSource = new Dictionary<string, string>();
            dictSource.Add("NO", "T0001");
            dictSource.Add("INDUSTRY", "捷為工作室");
            dictSource.Add("NAME", "塗聚文");

            string templateFile = ("Templates/Templates.xls");
            WorkbookDesigner designer = new WorkbookDesigner();
            //designer.Workbook.FileName=templateFile;
           Aspose.Cells.Workbook work = new Workbook(templateFile);
           designer.Workbook.Copy(work);   
            Aspose.Cells.Worksheet worksheet = designer.Workbook.Worksheets[0];
            worksheet.Name = "geovindu";
            //使用文本替換
            foreach (string name in dictSource.Keys)
            {
                worksheet.Replace(name, dictSource[name]);
            }

            //使用綁定數據方式替換
            designer.SetDataSource("SEX", "男");
            designer.SetDataSource("TEL", "13824350518*");
            designer.Process();     
            designer.Workbook.Save("testAdvice.xlsx",Aspose.Cells.SaveFormat.Xlsx);
        }

webform:

/// <summary>
        /// https://github.com/aspose-words/Aspose.Words-for-.NET
        /// https://asposewords.codeplex.com/
        /// https://asposednn.codeplex.com/
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnGenWord_Click(object sender, EventArgs e)
        {
            Dictionary<string, string> dictSource = new Dictionary<string, string>();
            dictSource.Add("NO", "T0001");
            dictSource.Add("INDUSTRY", "捷為工作室");
            dictSource.Add("NAME", "塗聚文");

            string templateFile = Server.MapPath("./Templates/Templates.doc");
            Aspose.Words.Document doc = new Aspose.Words.Document(templateFile);  //veb: 14.5

            //使用文本方式替換
            foreach (string name in dictSource.Keys)
            {
                doc.Range.Replace(name, dictSource[name], true, true);
            }

            #region 使用書簽替換模式

            Aspose.Words.Bookmark bookmark = doc.Range.Bookmarks["SEX"];
            if (bookmark != null)
            {
                bookmark.Text = "男";
            }
            //書簽方式
            bookmark = doc.Range.Bookmarks["TEL"];
            if (bookmark != null)
            {
                bookmark.Text = "13824350518*";
            }

            #endregion
            string savefile = Server.MapPath("./DuFile/geovindu.docx");
            doc.Save(savefile, Aspose.Words.SaveFormat.Docx);
            Response.Clear();
            Response.Buffer = true;

            //以字符流的形式下載文件     
            string fileName = "geovindu.docx"; //下載文件名稱
            FileStream fs = new FileStream(savefile, FileMode.Open);
            byte[] bytes = new byte[(int)fs.Length];
            fs.Read(bytes, 0, bytes.Length);
            fs.Close();
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.HeaderEncoding = System.Text.Encoding.UTF8;          
            Response.ContentType = "application/octet-stream";
            //通知浏覽器下載文件而不是打開     
            Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            //Response.AddHeader("Content-Length", fs.Length.ToString());
            Response.BinaryWrite(bytes);
            Response.Flush();
            Response.End();



        }
        /// <summary>
        /// http://aspose.github.io/
        /// https://github.com/asposemarketplace/Aspose_for_OpenXML
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button1_Click(object sender, EventArgs e)
        {
            Dictionary<string, string> dictSource = new Dictionary<string, string>();
            dictSource.Add("NO", "T0002");
            dictSource.Add("INDUSTRY", "捷為工作室");
            dictSource.Add("NAME", "塗聚文");

            string templateFile = Server.MapPath("./Templates/Templates.xls");
            WorkbookDesigner designer = new WorkbookDesigner();  //Veb:8.1
            Aspose.Cells.Workbook work = new Workbook(templateFile);
            designer.Workbook.Copy(work);   
            //designer.Open(templateFile);

            Aspose.Cells.Worksheet worksheet = designer.Workbook.Worksheets[0];
            worksheet.Name = "geovindu";
            //使用文本替換
            foreach (string name in dictSource.Keys)
            {
                worksheet.Replace(name, dictSource[name]);
            }

            //使用綁定數據方式替換
            designer.SetDataSource("SEX", "男");
            designer.SetDataSource("TEL", "13824350518*");
            designer.Process();
            string savefile = Server.MapPath("./DuFile/geovindu.xlsx");
            designer.Workbook.Save(savefile, Aspose.Cells.SaveFormat.Xlsx);
            string fileName = "geovindu.xlsx"; //下載文件名稱
            FileStream fs = new FileStream(savefile, FileMode.Open);
            byte[] bytes = new byte[(int)fs.Length];
            fs.Read(bytes, 0, bytes.Length);
            fs.Close();
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.HeaderEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/octet-stream";
            //通知浏覽器下載文件而不是打開     
            Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            //Response.AddHeader("Content-Length", fs.Length.ToString());
            Response.BinaryWrite(bytes);
            Response.Flush();
            Response.End();


        }

 /// <summary>
        /// 提取文檔中的圖片
        /// http://doc.evget.com/HelpDocument/AsposeWordsforNETDocumentationCHM/Aspose.Words.Drawing.Shape.html
        /// </summary>
        public void ExtractImagesToFiles()
        {
            Document doc = new Document("Image.SampleImages.doc");

            NodeCollection shapes = doc.GetChildNodes(NodeType.Shape, true);
            int imageIndex = 0;
            foreach (Shape shape in shapes)
            {
                if (shape.HasImage)
                {
                    string imageFileName = string.Format(
                        "Image.ExportImages.{0} Out{1}", imageIndex, FileFormatUtil.ImageTypeToExtension(shape.ImageData.ImageType));
                    shape.ImageData.Save(imageFileName);
                    imageIndex++;
                }
            }

            // Newer Microsoft Word documents (such as DOCX) may contain a different type of image container called DrawingML.
            // Repeat the process to extract these if they are present in the loaded document.
            NodeCollection dmlShapes = doc.GetChildNodes(NodeType.DrawingML, true);
            foreach (DrawingML dml in dmlShapes)
            {
                if (dml.HasImage)
                {
                    string imageFileName = string.Format(
                        "Image.ExportImages.{0} Out{1}", imageIndex, FileFormatUtil.ImageTypeToExtension(dml.ImageData.ImageType));
                    dml.ImageData.Save(imageFileName);
                    imageIndex++;
                }
            }
        }

            //同頁連續顯示
            Document dstDoc = new Document("TestFile.Destination.doc");
            Document srcDoc = new Document("TestFile.Source.doc");

            // Make the document appear straight after the destination documents content.
            srcDoc.FirstSection.PageSetup.SectionStart = SectionStart.Continuous;

            // Append the source document using the original styles found in the source document.
            dstDoc.AppendDocument(srcDoc, ImportFormatMode.KeepSourceFormatting);
            dstDoc.Save("TestFile.JoinContinuous Out.doc");


            //另起一頁顯示
            // Set the appended document to start on a new page.
            srcDoc.FirstSection.PageSetup.SectionStart = SectionStart.NewPage;
            // Append the source document using the original styles found in the source document.
            dstDoc.AppendDocument(srcDoc, ImportFormatMode.KeepSourceFormatting);
            dstDoc.Save("TestFile.JoinNewPage Out.doc");

//實例化一個新的Word Document
            //也可以在Aspose.Words.Document doc = new Aspose.Words.Document(path)中加path參數，

            //此path指向你設計好的Word模板路徑
            Aspose.Words.Document doc = new Aspose.Words.Document();
            DocumentBuilder builder = new DocumentBuilder(doc);
            //設置單元格內容對齊方式
            builder.ParagraphFormat.Alignment = ParagraphAlignment.Left;
            //清除設置
            builder.PageSetup.ClearFormatting();

            DataTable tbl = new DataTable();

            List<string> list = new List<string>();

            if (tbl != null && tbl.Rows.Count > 0)
            {
                //加載小組
                for (int i = 0; i < tbl.Rows.Count; i++)
                {
                    if (!list.Contains(tbl.Rows[i]["XZ"].ToString()))
                    {
                        list.Add(tbl.Rows[i]["XZ"].ToString());
                    }
                }
                double imgcellwidth = 85;
                double imgcellheight = 120;
                double cellwidth = 165;
                double cellheight = 18.5;
                //匹配小組中的學員
                builder.StartTable();//開始畫Table
                builder.RowFormat.Alignment = Aspose.Words.Tables.RowAlignment.Center;
                string xz = string.Empty;
                int count = 0;
                int rowcount = 0;
                for (int n = 0; n < list.Count; n++)
                {
                    xz = list[n];
                    builder.RowFormat.Height = 20;
                    //插入Table單元格
                    builder.InsertCell();
                    //Table單元格邊框線樣式
                    builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                    //Table此單元格寬度
                    builder.CellFormat.Width = 500;
                    //此單元格中內容垂直對齊方式
                    builder.CellFormat.VerticalAlignment = Aspose.Words.Tables.CellVerticalAlignment.Center;
                    builder.CellFormat.HorizontalMerge = Aspose.Words.Tables.CellMerge.None;
                    builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.None;
                    //字體大小
                    builder.Font.Size = 11;
                    //是否加粗
                    builder.Bold = true;
                    //向此單元格中添加內容
                    builder.Write(xz);
                    //Table行結束
                    builder.EndRow();
                    builder.Bold = false;
                    DataRow[] rows = tbl.Select("xz='" + xz + "'");
                    for (int i = 0; i < rows.Length; i = i + 2)
                    {
                        count++;
                        rowcount = (count - 1) * 6 + 1 + n;
                        //第一行
                        builder.InsertCell();
                        builder.RowFormat.Height = imgcellheight;
                        builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                        //合並行單元格
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.First;
                        builder.CellFormat.VerticalAlignment = Aspose.Words.Tables.CellVerticalAlignment.Center;
                        builder.CellFormat.TopPadding = 2;
                        builder.CellFormat.WrapText = false;
                        builder.CellFormat.Width = imgcellwidth;
                        if (!string.IsNullOrEmpty(rows[i]["ZPPATH"].ToString()))
                        {
                            //向此單元格中插入圖片
                            Shape shape = new Shape(doc, ShapeType.Image);
                            string url = System.Configuration.ConfigurationManager.AppSettings["UserPhotosSitePath"] + "\\" + rows[i]["ZPPATH"].ToString();
                            shape.ImageData.SetImage(url);
                            shape.Width = imgcellwidth - 2;
                            shape.Height = imgcellheight;
                            shape.HorizontalAlignment = HorizontalAlignment.Center;
                            CompositeNode node = shape.ParentNode;
                            //把此圖片移動到那個單元格中
                            builder.MoveToCell(0, rowcount, 0, 0);
                            builder.InsertNode(shape);
                        }
                        builder.InsertCell();
                        builder.RowFormat.Height = cellheight;
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                        builder.CellFormat.Width = cellwidth;
                        builder.Write(rows[i]["XM"].ToString());
                        builder.InsertCell();
                        builder.RowFormat.Height = imgcellheight;
                        builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                        //合並行單元格
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.First;
                        builder.CellFormat.VerticalAlignment = Aspose.Words.Tables.CellVerticalAlignment.Center;
                        builder.CellFormat.Width = imgcellwidth;
                        if (rows.Length > i + 1)
                        {
                            if (!string.IsNullOrEmpty(rows[i + 1]["ZPPATH"].ToString()))
                            {
                                Shape shape = new Shape(doc, ShapeType.Image);
                                string url = System.Configuration.ConfigurationManager.AppSettings["UserPhotosSitePath"] + "\\" + rows[i + 1]["ZPPATH"].ToString();
                                shape.ImageData.SetImage(url);
                                shape.Width = imgcellwidth - 2;
                                shape.Height = imgcellheight;
                                shape.HorizontalAlignment = HorizontalAlignment.Center;
                                CompositeNode node = shape.ParentNode;
                                builder.MoveToCell(0, rowcount, 2, 0);
                                builder.InsertNode(shape);
                            }
                        }
                        builder.InsertCell();
                        builder.RowFormat.Height = cellheight;
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                        builder.CellFormat.Width = cellwidth;
                        if (rows.Length > i + 1)
                        {
                            builder.Write(rows[i + 1]["XM"].ToString());
                        }
                        builder.EndRow();
                        //第二行
                        builder.InsertCell();
                        //此單元格與上一行單元格合並
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.Previous;
                        builder.CellFormat.HorizontalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = imgcellwidth;
                        builder.InsertCell();
                        builder.RowFormat.Height = cellheight;
                        builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = cellwidth;
                        builder.Write("單位及職務:" + rows[i]["SZDW"].ToString());
                        builder.InsertCell();
                        //此單元格與上一行單元格合並
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.Previous;
                        builder.CellFormat.HorizontalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = imgcellwidth;
                        builder.InsertCell();
                        builder.RowFormat.Height = cellheight;
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                        builder.CellFormat.Width = cellwidth;
                        if (rows.Length > i + 1)
                        {
                            builder.Write("單位及職務:" + rows[i + 1]["SZDW"].ToString());
                        }
                        builder.EndRow();
                        //第三行
                        builder.InsertCell();
                        //此單元格與上一行單元格合並
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.Previous;
                        builder.CellFormat.HorizontalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = imgcellwidth;
                        builder.InsertCell();
                        builder.RowFormat.Height = cellheight;
                        builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = cellwidth;
                        builder.Write("單位電話:" + rows[i]["DWDH"].ToString());
                        builder.InsertCell();
                        //此單元格與上一行單元格合並
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.Previous;
                        builder.CellFormat.HorizontalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = imgcellwidth;
                        builder.InsertCell();
                        builder.RowFormat.Height = cellheight;
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                        builder.CellFormat.Width = cellwidth;
                        if (rows.Length > i + 1)
                        {
                            builder.Write("單位電話:" + rows[i + 1]["DWDH"].ToString());
                        }
                        builder.EndRow();
                        //第四行
                        builder.InsertCell();
                        //此單元格與上一行單元格合並
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.Previous;
                        builder.CellFormat.HorizontalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = imgcellwidth;
                        builder.InsertCell();
                        builder.RowFormat.Height = cellheight;
                        builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = cellwidth;
                        builder.Write("手機:" + rows[i]["SJHM"].ToString());
                        builder.InsertCell();
                        //此單元格與上一行單元格合並
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.Previous;
                        builder.CellFormat.HorizontalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = imgcellwidth;
                        builder.InsertCell();
                        builder.RowFormat.Height = cellheight;
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                        builder.CellFormat.Width = cellwidth;
                        if (rows.Length > i + 1)
                        {
                            builder.Write("手機:" + rows[i + 1]["SJHM"].ToString());
                        }
                        builder.EndRow();
                        //第五行
                        builder.InsertCell();
                        //此單元格與上一行單元格合並
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.Previous;
                        builder.CellFormat.HorizontalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = imgcellwidth;
                        builder.InsertCell();
                        builder.RowFormat.Height = cellheight;
                        builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = cellwidth;
                        builder.Write("郵編:" + rows[i]["DWYB"].ToString());
                        builder.InsertCell();
                        //此單元格與上一行單元格合並
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.Previous;
                        builder.CellFormat.HorizontalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = imgcellwidth;
                        builder.InsertCell();
                        builder.RowFormat.Height = cellheight;
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                        builder.CellFormat.Width = cellwidth;
                        if (rows.Length > i + 1)
                        {
                            builder.Write("郵編:" + rows[i + 1]["DWYB"].ToString());
                        }
                        builder.EndRow();
                        //第六行
                        builder.InsertCell();
                        //此單元格與上一行單元格合並
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.Previous;
                        builder.CellFormat.HorizontalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = imgcellwidth;
                        builder.InsertCell();
                        builder.RowFormat.Height = cellheight;
                        builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = cellwidth;
                        builder.Write("Email:" + rows[i]["DZYJ"].ToString());
                        builder.InsertCell();
                        //此單元格與上一行單元格合並
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.Previous;
                        builder.CellFormat.HorizontalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Width = imgcellwidth;
                        builder.InsertCell();
                        builder.RowFormat.Height = cellheight;
                        builder.CellFormat.VerticalMerge = Aspose.Words.Tables.CellMerge.None;
                        builder.CellFormat.Borders.LineStyle = LineStyle.Single;
                        builder.CellFormat.Width = cellwidth;
                        if (rows.Length > i + 1)
                        {
                            builder.Write("Email:" + rows[i + 1]["DZYJ"].ToString());
                        }
                        builder.EndRow();
                    }
                }
                builder.EndTable();
            }
            string name = "學員通訊錄.doc";
            //以下載Word的形式打開Wrod
            //如圖所示：
            doc.Save(name, Aspose.Words.SaveFormat.Docx);

 https://github.com/asposemarketplace/Aspose_for_OpenXML