Administrator

Saturday, 11 October 2014 06:28

Get alias, tag and id of content controls in a Word document in C#

Content controls provide a way for you to design documents. When you add a content control to a document, the control is identified by a border, a title, and temporary text that can provide instructions to the user. According to Microsoft, content controls mainly benefit from two features:

Prevent users from editing or deleting protected sections of a document.
Bind parts of a document or template to data. You can bind content controls to database fields, managed objects in the .NET Framework, XML elements that are stored in the document, and other data sources.

Therefore, it is necessary for developers to get the properties of content controls when dealing content controls at run time. This article illustrates how to get all controls and their properties including alias, id and tag via Spire.Doc.

Firstly, check the test file that contains six content controls distributed in lines and a table. By default, the border and the title of the control do not appear if we don't click the protected section.

Test File:

Get alias, tag and id of content controls in a Word document in C#

Main Steps:

Step 1: Create a new Word document and load the test file.

Step 2: Create two lists to store tags which are distributed in lines and a table separately. Here, each content control will be identified by tag.

Step 3: Use foreach sentence to get all tags in the Word document.

Full Code:

static void Main(string[] args)
        {
            using (Document document = new Document(@"..\..\TestData\test.docx"))
            {
                StructureTags structureTags = GetAllTags(document);
                List<StructureDocumentTagInline> tagInlines = structureTags.tagInlines;

                string alias = tagInlines[0].SDTProperties.Alias;
                decimal id = tagInlines[0].SDTProperties.Id;
                string tag = tagInlines[0].SDTProperties.Tag;

                List<StructureDocumentTag> tags = structureTags.tags;
                alias = tags[0].SDTProperties.Alias;
                id = tags[0].SDTProperties.Id;
                tag = tags[0].SDTProperties.Tag;

            }
        }
       static StructureTags GetAllTags(Document document)
        {
            StructureTags structureTags = new StructureTags();
            foreach (Section section in document.Sections)
            {
                foreach (DocumentObject obj in section.Body.ChildObjects)
                {
                    if (obj.DocumentObjectType == DocumentObjectType.Paragraph)
                    {
                        foreach (DocumentObject pobj in (obj as Paragraph).ChildObjects)
                        {
                            if (pobj.DocumentObjectType == DocumentObjectType.StructureDocumentTagInline)
                            {
                                structureTags.tagInlines.Add(pobj as StructureDocumentTagInline);
                            }
                        }
                    }
                    else if (obj.DocumentObjectType == DocumentObjectType.Table)
                    {
                        foreach (TableRow row in (obj as Table).Rows)
                        {
                            foreach (TableCell cell in row.Cells)
                            {
                                foreach (DocumentObject cellChild in cell.ChildObjects)
                                {
                                    if (cellChild.DocumentObjectType == DocumentObjectType.StructureDocumentTag)
                                    {
                                        structureTags.tags.Add(cellChild as StructureDocumentTag);
                                    }
                                    else if (cellChild.DocumentObjectType == DocumentObjectType.Paragraph)
                                    {
                                        foreach (DocumentObject pobj in (cellChild as Paragraph).ChildObjects)
                                        {
                                            if (pobj.DocumentObjectType == DocumentObjectType.StructureDocumentTagInline)
                                            {
                                                structureTags.tagInlines.Add(pobj as StructureDocumentTagInline);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            return structureTags;
        }
        public class StructureTags
           {
               List<StructureDocumentTagInline> m_tagInlines;
               public List<StructureDocumentTagInline> tagInlines
               {
                   get
                   {
                       if (m_tagInlines == null)
                           m_tagInlines = new List<StructureDocumentTagInline>();
                       return m_tagInlines;
                   }
                   set
                   {
                       m_tagInlines = value;
                   }
               }
               List<StructureDocumentTag> m_tags;
               public List<StructureDocumentTag> tags
               {
                   get
                   {
                       if (m_tags == null)
                           m_tags = new List<StructureDocumentTag>();
                       return m_tags;
                   }
                   set
                   {
                       m_tags = value;
                   }
               }
           }

Effect Screenshot:

Content controls in lines

Get alias, tag and id of content controls in a Word document in C#

Content controls in table

Get alias, tag and id of content controls in a Word document in C#

Published in Text

Monday, 29 September 2014 07:13

Modify and Format Annotation in PDF in C#/VB.NET

Text annotation is frequently used in PDF for creating a comment or explaining more about an item. Using Spire.PDF you can create a text mark-up annotation, edit an existing annotation and delete specified or all annotations. This article is aimed to demonstrate how to modify and format an existing text annotation in a PDF document.

Test File:

Modify and Format Annotation in PDF in C#, VB.NET

Changes we want to make:

Modify the annotation text.
Change the color of the annotation icon.
Fix the annotation flag including its position, color, and type.

Code Snippet

Step 1: Create a new PDF document and load the test file.

PdfDocument pdf = new PdfDocument("test.pdf");

Step 2: Get the annotation from the document.

PdfAnnotation annotation = PdfAnnotationCollection.Create(pdf.Pages[0])[0];

Step 3: Set the text, border, color of the annotation and lock the annotation flag.

annotation.Contents = "Revised annotation";
annotation.Border.Width = 0.75f;
annotation.Color = new PdfRGBColor(Color.White);
annotation.Flags = PdfAnnotationFlags.Locked;

Step 4: Save and launch the file.

pdf.SaveToFile("result.pdf");
Process.Start("result.pdf");

Target Effect:

Modify and Format Annotation in PDF in C#, VB.NET

Full Code:

[C#]

using Spire.Pdf;
using Spire.Pdf.Interactive.Annotations;
using Spire.Pdf.Graphics;
using System.Diagnostics;
using System.Drawing;

namespace ModifyAndFormatAnnotation
{
    class Program
    {
        static void Main(string[] args)
        {
            PdfDocument pdf = new PdfDocument("test.pdf");

            //Get the first annotation on the first page
            PdfPageBase page = pdf.Pages[0];
            PdfAnnotation annotation = PdfAnnotationCollection.Create(page)[0];

            //Modify the contents, the border width, the color of the annotation and lock the annotation flag
            annotation.Contents = "Revised annotation";
            annotation.Border.Width = 0.75f;
            annotation.Color = new PdfRGBColor(Color.White);
            annotation.Flags = PdfAnnotationFlags.Locked;

            pdf.SaveToFile("result.pdf");
            Process.Start("result.pdf");
        }
    }
}

[VB.NET]

Imports Spire.Pdf
Imports Spire.Pdf.Interactive.Annotations
Imports Spire.Pdf.Graphics
Imports System.Diagnostics
Imports System.Drawing

Namespace ModifyAndFormatAnnotation
    Class Program
        Private Shared Sub Main(args As String())
            Dim pdf As New PdfDocument("test.pdf")

            'Get the first annotation on the first page
            Dim page As PdfPageBase = pdf.Pages(0)
            Dim annotation As PdfAnnotation = PdfAnnotationCollection.Create(page)(0)

            'Modify the contents, the border width, the color of the annotation and lock the annotation flag
            annotation.Contents = "Revised annotation"
            annotation.Border.Width = 0.75F
            annotation.Color = New PdfRGBColor(Color.White)
            annotation.Flags = PdfAnnotationFlags.Locked

            pdf.SaveToFile("result.pdf")
            Process.Start("result.pdf")
        End Sub
    End Class
End Namespace

Published in Annotation

Tagged under

pdf net Annotation

Friday, 26 September 2014 08:01

Convert XLS to XLSM and Maintain Macro in C#, VB.NET

A file with the XLSM extension is an Excel Macro-Enabled Workbook file. For security reasons, XLS file or XLSX file does not enable macros by default. Thus, if you want to execute macros in Excel file, you need to convert XLS or XLSX to XLSM at the first place. In this article, I’ll introduce you how to convert XLS to XLSM with the macro maintained using Spire.XLS.

Here is the method:

Step 1: Create a new instance of Spire.Xls.Workbook class.

Workbook workbook = new Workbook();

Step 2: Load the test file and imports its data to workbook.

workbook.LoadFromFile("test.xls", ExcelVersion.Version97to2003);

Step 3: Save the workbook as a new XLSM file.

workbook.SaveToFile("result.xlsm", FileFormat.Version2007);

Full Code:

[C#]

using Spire.Xls;
namespace Convert
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("test.xls", ExcelVersion.Version97to2003);
            workbook.SaveToFile("result.xlsm", FileFormat.Version2007);
        }
    }
}

[VB.NET]

Imports Spire.Xls
Namespace Convert
	Class Program
		Private Shared Sub Main(args As String())
			Dim workbook As New Workbook()
			workbook.LoadFromFile("test.xls", ExcelVersion.Version97to2003)
			workbook.SaveToFile("result.xlsm", FileFormat.Version2007)
		End Sub

	End Class
End Namespace

Test File:

As is shown in the picture, Excel automatically disables macro in XLS file.

Convert XLS to XLSM and Maintain Macro in C#, VB.NET

Result:

No security warning in the converted XLSM file.

Convert XLS to XLSM and Maintain Macro in C#, VB.NET

Published in Conversion

Tagged under

xls net Conversion

Tuesday, 09 September 2014 03:39

Marker Designer

Data

Name	Capital	Continent	Area	Population
Argentina	Buenos Aires	South America	2777815	32300003
Bolivia	La Paz	South America	1098575	7300000
Brazil	Brasilia	South America	8511196	150400000
Canada	Ottawa	North America	9976147	26500000
Chile	Santiago	South America	756943	13200000
Colombia	Bagota	South America	1138907	33000000
Cuba	Havana	North America	114524	10600000
Ecuador	Quito	South America	455502	10600000
El Salvador	San Salvador	North America	20865	5300000
Guyana	Georgetown	South America	214969	800000

Option

Excel Version:

downloads

Demo
Java
C# source

This demo shows you the usage of WorkbookDesigner.

import com.spire.data.table.DataTable;
import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class MarkerDesignerDemo {

    public void markerDesignerDemo(String filePath, String dataFilePath, String resultFilePath){
        Workbook data_book = new Workbook();
        data_book.loadFromFile(dataFilePath);
        DataTable table = data_book.getWorksheets().get(0).exportDataTable();

        Workbook workbook = new Workbook();
        workbook.loadFromFile(filePath);
        Worksheet sheet = workbook.getWorksheets().get(0);
        Worksheet sheet2 = workbook.getWorksheets().get(1);

        sheet.setName( "Result");
        sheet2.setName("DataSource");
        sheet2.insertDataTable(table,true,1,1);

        workbook.getMarkerDesigner().addParameter("Variable1", 1234.5678);
        workbook.getMarkerDesigner().addDataTable("Country", table);
        workbook.getMarkerDesigner().apply();

        sheet.getAllocatedRange().autoFitRows();
        sheet.getAllocatedRange().autoFitColumns();

        workbook.saveToFile(resultFilePath, FileFormat.Version2013);


    }
}

using System.Data;
using Spire.Xls;

namespace DemoOnlineCode
{
    class MarkerDesigner
    {

        public void demoMarkerDesigner(string filePath, string dataFilePath, string resultFilePath)
        {
            Workbook data_book = new Workbook();
            data_book.LoadFromFile(dataFilePath);
            DataTable table = data_book.Worksheets[0].ExportDataTable();

            Workbook workbook = new Workbook();
            workbook.LoadFromFile(filePath);
            Worksheet sheet = workbook.Worksheets[0];
            Worksheet sheet2 = workbook.Worksheets[1];

            sheet.Name = "Result";
            sheet2.Name = "DataSource";
            sheet2.InsertDataTable(table, true, 1, 1);

            workbook.MarkerDesigner.AddParameter("Variable1", 1234.5678);
            workbook.MarkerDesigner.AddDataTable("Country", table);
            workbook.MarkerDesigner.Apply();

            sheet.AllocatedRange.AutoFitRows();
            sheet.AllocatedRange.AutoFitColumns();
            workbook.SaveToFile(resultFilePath, ExcelVersion.Version2010);
        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Tuesday, 09 September 2014 03:38

Calculate Formulas

Mathematic Functions:

Calculate symbol :

Calculate Data:

Logic Function:

Calculate symbol :

Calculate Data:

Simple Expression:

Calculate symbol :

Calculate Data:

MID Functions:

Text :

Start Number:

Number Charts:

Option:

Excel Version:

downloads

Demo
Java
C# source

This demo shows you how to calculate formulas and export data to datatable with calculating formulas.

import com.spire.xls.*;

public class CalculateFormulaDemo {
    public void CalculateFormulas(String resultFile){
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.getWorksheets().get(0);
        Calculate(workbook, sheet);
        workbook.saveToFile(resultFile, ExcelVersion.Version2010);
    }

    public void Calculate(Workbook workbook, Worksheet worksheet){
        int currentRow = 1;
        String currentFormula = null;
        Object formulaResult = null;
        String value = null;

        // Set width respectively of Column A ,Column B,Column C
        worksheet.setColumnWidth(1,32);
        worksheet.setColumnWidth(2,16);
        worksheet.setColumnWidth(3,16);

        //Set the value of Cell A1
        worksheet.getRange().get(currentRow++, 1).setValue("Examples of formulas :");

        // Set the value of Cell A2
        worksheet.getRange().get(++currentRow, 1).setValue("Test data:");

        // Set the style of Cell A1
        CellRange range = worksheet.getRange().get("A1");
        range.getStyle().getFont().isBold(true);
        range.getStyle().setFillPattern(ExcelPatternType.Solid);
        range.getStyle().setKnownColor(ExcelColors.LightGreen1);
        range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);

        // Additive operation of mutiple cells

        worksheet.getRange().get(currentRow, 2).setNumberValue(7.3);
        worksheet.getRange().get(currentRow, 3).setNumberValue(5);
        worksheet.getRange().get(currentRow, 4).setNumberValue(8.2);
        worksheet.getRange().get(currentRow, 5).setNumberValue(4);
        worksheet.getRange().get(currentRow, 6).setNumberValue(3);
        worksheet.getRange().get(currentRow, 7).setNumberValue(11.3);

        // Create arithmetic expression string about cells
        currentFormula = "=Sheet1!$B$3 + Sheet1!$C$3+Sheet1!$D$3+Sheet1!$E$3+Sheet1!$F$3+Sheet1!$G$3";

        //Caculate arithmetic expression  about cells
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        // Set the value and format of two head cell
        worksheet.getRange().get(currentRow,1).setValue("Formulas");
        worksheet.getRange().get(currentRow,2).setValue("Results");
        worksheet.getRange().get(currentRow,2).setHorizontalAlignment(HorizontalAlignType.Right);
        range = worksheet.getRange().get(currentRow,1,currentRow,2);
        range.getStyle().getFont().isBold(true);
        range.getStyle().setKnownColor(ExcelColors.LightGreen1);
        range.getStyle().setFillPattern(ExcelPatternType.Solid);
        range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);

        // Expression caculation
        // Create arithmetic tables enclosed type string
        currentFormula = "=33*3/4-2+10";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        // Caculate arithmetic expression
        formulaResult = workbook.calculateFormulaValue(currentFormula);

        value = formulaResult.toString();

        worksheet.getRange().get(currentRow,2).setValue(value);

        //Absolute value function
        // Create abosolute value function string
        currentFormula = "=ABS(-1.21)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        // Caculate abosulte value function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        // Sum function
        // Create sum function string
        currentFormula = "=SUM(18,29)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        // Caculate sum function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        //NOT function
        // Create NOT function string
        currentFormula = "=NOT(true)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        //Caculate NOT function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);
        worksheet.getRange().get(currentRow,2).setHorizontalAlignment(HorizontalAlignType.Right);

        //String Manipulation function
        //Get the substring
        // Build substring function
        currentFormula = "=MID(\"world\",4,2)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        //Caculate substring function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        worksheet.getRange().get(currentRow,2).setHorizontalAlignment(HorizontalAlignType.Right);

        // Random function

        // Create random function string.
        currentFormula = "=RAND()";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        //Caculate random function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);


    }
}

using Spire.Xls;

namespace DemoOnlineCode
{
    class CalculateFormulas
    {
        public void demoCalculateFormulas(string resultFile)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            Calculate(workbook, sheet);
            workbook.SaveToFile(resultFile, ExcelVersion.Version2010);
        }
        public void Calculate(Workbook workbook, Worksheet sheet)
        {
            int currentRow = 1;
            string currentFormula = string.Empty;
            object formulaResult = null;
            string value = string.Empty;

            // Set width respectively of Column A ,Column B,Column C
            sheet.SetColumnWidth(1, 32);
            sheet.SetColumnWidth(2, 16);
            sheet.SetColumnWidth(3, 16);

            //Set the value of Cell A1
            sheet.Range[currentRow++, 1].Value = "Examples of formulas :";

            // Set the value of Cell A2
            sheet.Range[++currentRow, 1].Value = "Test data:";

            // Set the style of Cell A1
            CellRange range = sheet.Range["A1"];
            range.Style.Font.IsBold = true;

            range.Style.FillPattern = ExcelPatternType.Solid;

            range.Style.KnownColor = ExcelColors.LightGreen1;

            range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            // Additive operation of mutiple cells
            sheet.Range[currentRow, 2].NumberValue = 7.3;
            sheet.Range[currentRow, 3].NumberValue = 5;
            sheet.Range[currentRow, 4].NumberValue = 8.2;
            sheet.Range[currentRow, 5].NumberValue = 4;
            sheet.Range[currentRow, 6].NumberValue = 3;
            sheet.Range[currentRow, 7].NumberValue = 11.3;

            // Create arithmetic expression string about cells
            currentFormula = "=Sheet1!$B$3 + Sheet1!$C$3+Sheet1!$D$3+Sheet1!$E$3+Sheet1!$F$3+Sheet1!$G$3";

            //Caculate arithmetic expression  about cells
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

            // Set the value and format of two head cell
            sheet.Range[++currentRow, 1].Value = "Formulas"; ;
            sheet.Range[currentRow, 2].Value = "Results";
            sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;
            range = sheet.Range[currentRow, 1, currentRow, 2];
            range.Style.Font.IsBold = true;
            range.Style.KnownColor = ExcelColors.LightGreen1;
            range.Style.FillPattern = ExcelPatternType.Solid;
            range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            // Expression caculation
            // Create arithmetic tables enclosed type string
            currentFormula = "=33*3/4-2+10";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            // Caculate arithmetic expression
            formulaResult = workbook.CaculateFormulaValue(currentFormula);

            value = formulaResult.ToString();

            sheet.Range[currentRow, 2].Value = value;

            /// The mathematics function ///

            //Absolute value function

            // Create abosolute value function string
            currentFormula = "=ABS(-1.21)";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            // Caculate abosulte value function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;


            ///  Statistical function///
            // Sum function

            // Create sum function string
            currentFormula = "=SUM(18,29)";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            // Caculate sum function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

            ///logic function///
            //NOT function
            // Create NOT function string
            currentFormula = "=NOT(true)";
            sheet.Range[currentRow, 1].Text = currentFormula;

            //Caculate NOT function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;
            sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;

            //String Manipulation function//
            //Get the substring
            // Build substring function
            currentFormula = "=MID(\"world\",4,2)";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            //Caculate substring function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

            sheet.Range[currentRow, 2].HorizontalAlignment = HorizontalAlignType.Right;

            // Random function

            // Create random function string.
            currentFormula = "=RAND()";
            sheet.Range[++currentRow, 1].Text = currentFormula;

            //Caculate random function
            formulaResult = workbook.CaculateFormulaValue(currentFormula);
            value = formulaResult.ToString();
            sheet.Range[currentRow, 2].Value = value;

        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Tuesday, 09 September 2014 03:33

Create Pivot Table

Data

e-iceblue

Option

Average Column:			Sum Column:
Max Column:			Min Column:
Excel Version:

downloads

Demo
Java
C# source

This demo shows you how to create a privot table in excel.

import com.spire.xls.*;
import com.spire.xls.core.IPivotField;


public class CreatePivotTableDemo {
    public void createPivotTableExcel(String filePath, String resultFilePath){
        Workbook workbook = createPivotTable(filePath);
        workbook.saveToFile(resultFilePath);
    }

    public Workbook createPivotTable(String filePath){
        Workbook workbook = new Workbook();
        workbook.loadFromFile(filePath, ExcelVersion.Version2010);
        Worksheet worksheet = workbook.getWorksheets().get(0);
        worksheet.setName("Data Source");
        Worksheet worksheet1 = workbook.createEmptySheet();
        worksheet1.setName("Pivot Table");

        CellRange dataRange = worksheet.getRange().get("A1:G17");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);
        PivotTable pt = worksheet1.getPivotTables().add("Pivot Table",worksheet.getRange().get("A1"),cache);

        IPivotField r1 = pt.getPivotFields().get("Vendor No");
        r1.setAxis(AxisTypes.Row);
        pt.getOptions().setRowHeaderCaption("Vendor No");

        IPivotField r2 = pt.getPivotFields().get("Name");
        r2.setAxis(AxisTypes.Row);

        pt.getDataFields().add(pt.getPivotFields().get("Area"), "Average of Area", SubtotalTypes.Average);
        pt.getDataFields().add(pt.getPivotFields().get("Sales"), "SUM of Sales", SubtotalTypes.Sum);
        pt.getDataFields().add(pt.getPivotFields().get("OnHand"), "Max of OnHand", SubtotalTypes.Max);
        pt.getDataFields().add(pt.getPivotFields().get("OnOrder"), "Min of OnOrder", SubtotalTypes.Min);
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12);
        pt.calculateData();
        worksheet1.getAllocatedRange().autoFitColumns();
        worksheet1.getAllocatedRange().autoFitRows();
        return workbook;
    }
}

using Spire.Xls;

namespace DemoOnlineCode
{
    class CreatePivotTableInExcel
    {
        public void demoCreatePivotTableInExcel(string filePath, string resultFilePath)
        {
            Workbook workbook = CreatePivotTable(filePath);
            workbook.SaveToFile(resultFilePath);
        }

        public Workbook CreatePivotTable(string filePath)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(filePath, ExcelVersion.Version2007);
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Data Source";
            Worksheet sheet2 = workbook.CreateEmptySheet();
            sheet2.Name = "Pivot Table";

            CellRange dataRange = sheet.Range["A1:G17"];
            PivotCache cache = workbook.PivotCaches.Add(dataRange);
            PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);

            var r1 = pt.PivotFields["Vendor No"];
            r1.Axis = AxisTypes.Row;
            pt.Options.RowHeaderCaption = "Vendor No";

            var r2 = pt.PivotFields["Name"];
            r2.Axis = AxisTypes.Row;

            pt.DataFields.Add(pt.PivotFields["Area"], "Average of Area", SubtotalTypes.Average);
            pt.DataFields.Add(pt.PivotFields["Sales"], "SUM of Sales", SubtotalTypes.Sum);
            pt.DataFields.Add(pt.PivotFields["OnHand"], "Max of OnHand", SubtotalTypes.Max);
            pt.DataFields.Add(pt.PivotFields["OnOrder"], "Min of OnOrder", SubtotalTypes.Min);
            pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;
            pt.CalculateData();
            sheet2.AllocatedRange.AutoFitColumns();
            sheet2.AllocatedRange.AutoFitRows();
            return workbook;

        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Tuesday, 09 September 2014 03:28

Charts

Data

e-iceblue

Chart Option

Chart format:

Excel Version:

downloads

Demo
Java
C# source

This demo shows you how to create chart in an excel workbook.

import com.spire.xls.*;

public class ChartDemo {
    public void chartDemo(String excelFile, ExcelChartType chartType, String resultFileName){
        Workbook workbook = new Workbook();
		workbook.loadFromFile(excelFile);
        Worksheet worksheet = workbook.getWorksheets().get(0);
        
        setChart(worksheet,chartType);
        sheetStyle(workbook,worksheet);
        workbook.saveToFile(resultFileName+".xlsx",FileFormat.Version2013);

    }
    private void setChart(Worksheet sheet, ExcelChartType chartType){
        sheet.setName("Chart data");
        sheet.setGridLinesVisible(false);

        //Add a new  chart worsheet to workbook
        Chart chart = sheet.getCharts().add();
        chart.setChartType(chartType);

        //Set region of chart data
        chart.setDataRange(sheet.getCellRange("A1:C7"));
        chart.setSeriesDataFromRange(false);

        //Set position of chart
        chart.setLeftColumn(4);
        chart.setTopRow(2);
        chart.setRightColumn(12);
        chart.setBottomRow(22);

        //Chart title
        chart.setChartTitle("Sales market by country");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        chart.getPrimarySerieAxis().setTitle("Country");
        chart.getPrimarySerieAxis().getFont().isBold(true);
        chart.getPrimarySerieAxis().getTitleArea().isBold(true);

        chart.getPrimarySerieAxis().setTitle("Sales(in Dollars)");
        chart.getPrimarySerieAxis().hasMajorGridLines(false);
        chart.getPrimarySerieAxis().getTitleArea().setTextRotationAngle(90);
        chart.getPrimarySerieAxis().setMinValue(1000);
        chart.getPrimarySerieAxis().getTitleArea().isBold(true);

        chart.getPlotArea().getFill().setFillType(ShapeFillType.SolidColor);
        chart.getPlotArea().getFill().setForeKnownColor(ExcelColors.White);

        for (int i = 0; i < chart.getSeries().getCount(); i++){
            chart.getSeries().get(i).getFormat().getOptions().isVaryColor(true);
            chart.getSeries().get(i).getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        }
        chart.getLegend().setPosition(LegendPositionType.Top);
    }

    public static void sheetStyle(Workbook workbook, Worksheet sheet){
        CellStyle oddStyle = workbook.getStyles().addStyle("oddStyle");
        oddStyle.getBorders().getByBordersLineType(BordersLineType.EdgeLeft).setLineStyle(LineStyleType.Thin);
        oddStyle.getBorders().getByBordersLineType(BordersLineType.EdgeTop).setLineStyle(LineStyleType.Thin);
        oddStyle.getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Thin);
        oddStyle.setKnownColor(ExcelColors.LightGreen1);

        CellStyle evenStyle = workbook.getStyles().addStyle("evenStyle");
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeLeft).setLineStyle(LineStyleType.Thin);
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeRight).setLineStyle(LineStyleType.Thin);
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeTop).setLineStyle(LineStyleType.Thin);
        evenStyle.getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Thin);
        evenStyle.setKnownColor(ExcelColors.LightTurquoise);

        for (int i = 0; i < sheet.getAllocatedRange().getRows().length; i++) {
            CellRange[] ranges = sheet.getAllocatedRange().getRows();
            if (ranges[i].getRow() != 0){
                if (ranges[i].getRow() % 2 == 0)
                {
                    ranges[i].setCellStyleName(evenStyle.getName());
                }
                else
                {
                    ranges[i].setCellStyleName(oddStyle.getName());
                }
            }
        }

        //Sets header style
        CellStyle styleHeader = workbook.getStyles().addStyle("headerStyle");
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeLeft).setLineStyle(LineStyleType.Thin);
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeRight).setLineStyle(LineStyleType.Thin);
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeTop).setLineStyle(LineStyleType.Thin);
        styleHeader.getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Thin);
        styleHeader.setVerticalAlignment(VerticalAlignType.Center);
        styleHeader.setKnownColor(ExcelColors.Green);
        styleHeader.getFont().setKnownColor(ExcelColors.White);
        styleHeader.getFont().isBold(true);
        styleHeader.setHorizontalAlignment(HorizontalAlignType.Center);

        for (int i = 0; i < sheet.getRows()[0].getCount(); i++) {
            CellRange range = sheet.getRows()[0];
            range.setCellStyleName(styleHeader.getName());
        }

        sheet.getColumns()[sheet.getAllocatedRange().getLastColumn() -1].getStyle().setNumberFormat("\"$\"#,##0");
        sheet.getColumns()[sheet.getAllocatedRange().getLastColumn() -2].getStyle().setNumberFormat("\"$\"#,##0");
        sheet.getRows()[0].getStyle().setNumberFormat("General");

        sheet.getAllocatedRange().autoFitColumns();
        sheet.getAllocatedRange().autoFitRows();

        sheet.getRows()[0].setRowHeight(20);
    }
}

using System.Data;
using Spire.Xls;

namespace DemoOnlineCode
{
    class Charts
    {
        public void demoChart(DataTable table, ExcelChartType chartFormat, string resultFileName)
        {
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            sheet.InsertDataTable(table, true, 1, 1);
            SetChart(sheet, chartFormat);
            sheetStyle(book, sheet);
            book.SaveToFile(resultFileName, ExcelVersion.Version2010);

        }
        private void SetChart(Worksheet sheet, ExcelChartType chartFormat)
        {
            sheet.Name = "Chart data";
            sheet.GridLinesVisible = false;

            //Writes chart data
            //CreateChartData(sheet);
            //Add a new  chart worsheet to workbook
            Chart chart = sheet.Charts.Add();

            //Set region of chart data

            chart.DataRange = sheet.Range["A1:C7"];
            chart.SeriesDataFromRange = false;

            //Set position of chart
            chart.LeftColumn = 1;
            chart.TopRow = 8;
            chart.RightColumn = 11;
            chart.BottomRow = 29;
            chart.ChartType = chartFormat;


            //Chart title
            chart.ChartTitle = "Sales market by country";
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size = 12;

            chart.PrimaryCategoryAxis.Title = "Country";
            chart.PrimaryCategoryAxis.Font.IsBold = true;
            chart.PrimaryCategoryAxis.TitleArea.IsBold = true;

            chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
            chart.PrimaryValueAxis.HasMajorGridLines = false;
            chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
            chart.PrimaryValueAxis.MinValue = 1000;
            chart.PrimaryValueAxis.TitleArea.IsBold = true;


            foreach (Spire.Xls.Charts.ChartSerie cs in chart.Series)
            {
                cs.Format.Options.IsVaryColor = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
            }

            chart.Legend.Position = LegendPositionType.Top;

        }
        public static void sheetStyle(Workbook book, Worksheet sheet)
        {
            CellStyle oddStyle = book.Styles.Add("oddStyle");
            oddStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            oddStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            oddStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            oddStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            oddStyle.KnownColor = ExcelColors.LightGreen1;

            CellStyle evenStyle = book.Styles.Add("evenStyle");
            evenStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            evenStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            evenStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            evenStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            evenStyle.KnownColor = ExcelColors.LightTurquoise;


            foreach (CellRange range in sheet.AllocatedRange.Rows)
            {
                if (range.Row % 2 == 0)
                    range.CellStyleName = evenStyle.Name;
                else
                    range.CellStyleName = oddStyle.Name;
            }

            //Sets header style
            CellStyle styleHeader = sheet.Rows[0].Style;
            styleHeader.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            styleHeader.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            styleHeader.VerticalAlignment = VerticalAlignType.Center;
            styleHeader.KnownColor = ExcelColors.Green;
            styleHeader.Font.KnownColor = ExcelColors.White;
            styleHeader.Font.IsBold = true;

            sheet.Columns[sheet.AllocatedRange.LastColumn - 1].Style.NumberFormat = "\"$\"#,##0";
            sheet.Columns[sheet.AllocatedRange.LastColumn - 2].Style.NumberFormat = "\"$\"#,##0";

            sheet.AllocatedRange.AutoFitColumns();
            sheet.AllocatedRange.AutoFitRows();

            sheet.Rows[0].RowHeight = 20;
        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Tuesday, 09 September 2014 02:17

Conversion

Upload

Maximum file size: 1 MB. Files accepted: xls, xlsx, xlsb, ods.

Click here to browse files.

fileerrors

Convert to

Source file:

filename

Target file type:

Demo
Java
C# source

This demo shows you how to convert a Excel document (xls/xlsx/xlsb/ods) to PDF, HTML, Image.

import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.core.spreadsheet.HTMLOptions;

import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;


public class ConvertDemo {

   public void convertDemo(String filePath, String convertTo, String resultFileName) throws IOException {
       Workbook workbook = new Workbook();
       workbook.loadFromFile(filePath);
       ConvertFormat(workbook,convertTo,resultFileName);

   }
    private void ConvertFormat(Workbook workbook, String convertTo, String resultFileName) throws IOException {
        switch (convertTo){
            case "PDF":
                workbook.getConverterSetting().setSheetFitToPage(true);
                workbook.saveToFile(resultFileName + ".pdf", FileFormat.PDF);
                break;
            case "IMAGE":
                BufferedImage[] images = (BufferedImage[]) new Image[workbook.getWorksheets().size()];
                for (int i = 0; i < workbook.getWorksheets().size();i++){
                    images[i] = workbook.saveAsImage(i,300,300);
                }
                if (images != null && images.length > 0){
                    if (images.length == 1){
                        ImageIO.write(images[0],".PNG", new File(resultFileName+".png"));
                    }
                }else {
                    for (int j = 0; j < images.length;j++){
                        String fileName = String.format("image-{0}.png",j);
                        ImageIO.write(images[j],".PNG",new File(fileName));
                    }
                }
                    break;
            case "HTML":
                for (int i = 0; i < workbook.getWorksheets().size(); i++) {
                    HTMLOptions options = new HTMLOptions();
                    options.setImageEmbedded(true);
                    String htmlPath = String.format(resultFileName+"-{0}.html",i++);
                    workbook.getWorksheets().get(i).saveToHtml(htmlPath,options);
                }
                break;
            case "TIFF":
                workbook.saveToTiff(resultFileName+".tiff");
                break;
            case "XPS":
                workbook.saveToFile(resultFileName+".xps",FileFormat.XPS);
                break;
        }
    }
}

using System;
using Spire.Xls;
using System.Drawing;
using System.Drawing.Imaging;

namespace DemoOnlineCode
{
    class Convertors
    {
        public void demoConvert(String filePath, string format, string resultFileName)
        {
            Workbook book = new Workbook();
            book.LoadFromFile(filePath);
            ConvertFormat(book, format, resultFileName);
        }
        private void ConvertFormat(Workbook workbook, string format, string resultFileName)
        {
            switch (format)
            {
                case "PDF":
                    workbook.ConverterSetting.SheetFitToPage = true;
                    workbook.SaveToFile(resultFileName + ".pdf", Spire.Xls.FileFormat.PDF);
                    break;
                case "IMAGE":
                    Image[] images = new Image[workbook.Worksheets.Count];
                    for (int i = 0; i < workbook.Worksheets.Count; i++)
                    {
                        images[i] = workbook.SaveAsImage(i, 300, 300);
                    }
                    if (images != null && images.Length > 0)
                    {
                        if (images.Length == 1)
                        {
                            images[0].Save(resultFileName + ".png", ImageFormat.Png);
                        }
                        else
                        {
                            for (int i = 0; i < images.Length; i++)
                            {
                                String fileName = String.Format("{0}-Image-{1}.png", resultFileName, i);
                                images[i].Save(fileName, ImageFormat.Png);
                            }
                        }
                    }
                    break;

                case "HTML":
                    for (int i = 0; i < workbook.Worksheets.Count; i++)
                    {
                        Worksheet sheet = workbook.Worksheets[i];
                        string htmlPath = string.Format(resultFileName + "-{0}.html", i++);
                        sheet.SaveToHtml(htmlPath);
                    }
                    break;
                case "TIFF":
                    JoinTiffImages(ToImage(workbook), resultFileName + ".tiff", EncoderValue.CompressionLZW);
                    break;
                case "XPS":
                    workbook.SaveToFile(resultFileName+".xps",FileFormat.XPS);
                    break;
            }
        }
        private static Image[] ToImage(Workbook workbook)
        {
            //Get the worksheet count of workbook
            int workSheetNo = workbook.Worksheets.Count;

            //Create an array
            Image[] images = new Image[workSheetNo];

            //Save worksheet to image and add the array
            for (int i = 0; i < workSheetNo; i++)
            {
                Worksheet workSheet = workbook.Worksheets[i];
                string output = string.Format("result{0}.jpg", i + 1);
                workSheet.SaveToImage(output);
                Image image = Image.FromFile(output);
                images[i] = image;
            }
            return images;
        }

        private static ImageCodecInfo GetEncoderInfo(string mimeType)
        {
            ImageCodecInfo[] encoders = ImageCodecInfo.GetImageEncoders();
            for (int j = 0; j < encoders.Length; j++)
            {
                if (encoders[j].MimeType == mimeType)
                    return encoders[j];
            }
            throw new Exception(mimeType + " mime type not found in ImageCodecInfo");
        }

        private void JoinTiffImages(Image[] images, string outFile, EncoderValue compressEncoder)
        {
            //Use the save encoder
            Encoder enc = Encoder.SaveFlag;
            EncoderParameters ep = new EncoderParameters(2);
            ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.MultiFrame);
            ep.Param[1] = new EncoderParameter(Encoder.Compression, (long)compressEncoder);
            Image pages = images[0];
            int frame = 0;
            ImageCodecInfo info = GetEncoderInfo("image/tiff");
            foreach (Image img in images)
            {
                if (frame == 0)
                {
                    pages = img;
                    //save the first frame
                    pages.Save(outFile, info, ep);
                }

                else
                {
                    //save the intermediate frames
                    ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.FrameDimensionPage);

                    pages.SaveAdd(img, ep);
                }
                if (frame == images.Length - 1)
                {
                    //flush and close.
                    ep.Param[0] = new EncoderParameter(enc, (long)EncoderValue.Flush);
                    pages.SaveAdd(ep);
                }
                frame++;
            }
        }
    }
}

No Matter How Big or Small Your Project is,

Any technical question related to our product, contact us at support@e-iceblue.com.

Any question related to the purchase of product, contact us at sales@e-iceblue.com.

If you don't find the function you want, please request a free demo from us.

Published in Spire.XLS

Thursday, 28 August 2014 06:12

Prevent Page Breaks in Word Tables in C#

All too often, we start a table near the bottom of a page. Microsoft Word automatically places a page break in a table if a single row or several rows are too long to fit between the top and bottom margins of the page. So we'd rather the same row can be placed on one page or have the entire table on the next page instead of being broken over two pages. In this article, I'll introduce you two ways to avoid page breaks in Word tables via Spire.Doc.

Assume that we have a Word table like this (Row 2 splits to different pages), we may want to optimize the layout by the following two methods.

Prevent Page Breaks in Word Tables

Method 1: Keep the entire table on the same page.

Step 1: Create a new Word document and load the test file.

Document doc = new Document("Test.docx");

Step 2: Get the table from Word document.

Table table = doc.Sections[0].Tables[0] as Table;

Step 3: Change the paragraph setting to keep them together.

           foreach (TableRow row in table.Rows)
            {
                foreach (TableCell cell in row.Cells)
                {
                  foreach (Paragraph p in cell.Paragraphs)
                   {
                       p.Format.KeepFollow = true;
                   }
                }
           }

Step 4: Save and launch the file.

      doc.SaveToFile("Result.docx", FileFormat.Docx2010);
      System.Diagnostics.Process.Start("Result.docx");

Output:

Prevent Page Breaks in Word Tables

Use this sentence to replace Step 3, you'll get the layout as below:

Prevent Page Breaks in Word Tables

Full C# Code:

Method 1

using Spire.Doc;
using Spire.Doc.Documents;
namespace PreventPageBreak
{
    class Program
    {
        static void Main(string[] args)
        {
            Document doc = new Document("Test.docx");
            Table table = doc.Sections[0].Tables[0] as Table;
            foreach (TableRow row in table.Rows)
            {
                foreach (TableCell cell in row.Cells)
                {
                    foreach (Paragraph p in cell.Paragraphs)
                    {
                        p.Format.KeepFollow = true;
                    }
                }
            }
            doc.SaveToFile("Result.docx", FileFormat.Docx2010);
            System.Diagnostics.Process.Start("Result.docx");
        }
    }
}

Published in Page Setup

Tagged under

doc net Page Setup

Tuesday, 12 August 2014 07:57

Remove borderline of textbox in Excel chart in C#, VB.NET

There is an article in the tutorials which demonstrates how to insert textbox with contents in Excel. Sometime back, a user of Spire.XLS wanted to know if it is possible to remove the borderline of the textbox that has been inserted in Excel chart. Yes, of course. This article focuses on delivering a solution to this issue.

In the following section, we're going to create two textboxes in the same chart, one textbox is built with borderline, the other one without. Then we can learn how to remove borderline using Spire.XLS by comparison.

Code snippet for remove borderline of textbox:

Step 1: Create a new instance of workbook.

Workbook workbook = new Workbook();
workbook.Version=ExcelVersion.Version2010;

Step 2: Create a new worksheet named "Remove Borderline" and add a chart to the worksheet.

Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Remove Borderline";
Chart chart = sheet.Charts.Add();

Step 3: Create textbox1 in the chart and input text information.

XlsTextBoxShape textbox1 = chart.TextBoxes.AddTextBox(50, 50, 100, 500) as XlsTextBoxShape;

Step 4: Create textbox2 in the chart, input text information and remove borderline.

XlsTextBoxShape textbox = chart.TextBoxes.AddTextBox(500, 50, 100, 500) as XlsTextBoxShape;
textbox.Text = "The solution without borderline";
textbox.Line.Weight = 0;

Step 5: Save and launch the file.

workbook.SaveToFile("Sample.xlsx", ExcelVersion.Version2010);
Process.Start("Sample.xlsx");

Result:

Remove borderline of textbox in Excel chart in C#, VB.NET

Full code:

[C#]

using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.Shapes;
using System.Diagnostics;

namespace RemoveBorderlineofTextbox
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a new workbook
            Workbook workbook = new Workbook();
            workbook.Version = ExcelVersion.Version2010;
            // Get the first worksheet and rename it
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Remove Borderline";
            // Add a chart to the worksheet
            Chart chart = sheet.Charts.Add();
            // Add the first text box (with visible border)
            XlsTextBoxShape textbox1 = chart.TextBoxes.AddTextBox(50, 50, 100, 500) as XlsTextBoxShape ;
            textbox1.Text = "The original with borderline";
            // Add the second text box (border will be hidden)
            XlsTextBoxShape textbox2 = chart.TextBoxes.AddTextBox(500, 50, 100, 500) as XlsTextBoxShape;
            textbox2.Text = "The solution without borderline";
            // Hide the border by setting line weight to 0
            textbox2.Line.Weight = 0;
            // Save the result file
            workbook.SaveToFile("Sample.xlsx", ExcelVersion.Version2010);
            Process.Start("Sample.xlsx");
        }
    }
}

[VB.NET]

Imports Spire.Xls
Imports Spire.Xls.Core.Spreadsheet.Shapes
Imports System.Diagnostics
Namespace RemoveBorderlineofTextbox
	Class Program

		Private Shared Sub Main(args As String())
			'Create a new workbook
			Dim workbook As New Workbook()
			workbook.Version = ExcelVersion.Version2010
			'  Get the first worksheet And rename it
			Dim sheet As Worksheet = workbook.Worksheets(0)
			sheet.Name = "Remove Borderline"
			'  Add a chart to the worksheet
			Dim chart As Chart = sheet.Charts.Add()
			'  Add the first text box (with visible border)
			Dim textbox1 As XlsTextBoxShape = TryCast(chart.TextBoxes.AddTextBox(50, 50, 100, 500), XlsTextBoxShape)
			textbox1.Text = "The original with borderline"
			'Add the second text box (border will be hidden)
			Dim textbox2 As XlsTextBoxShape = TryCast(chart.TextBoxes.AddTextBox(500, 50, 100, 500), XlsTextBoxShape)
			textbox2.Text = "The solution without borderline"
			'Hide the border by setting line weight to 0
			textbox2.Line.Weight = 0
			'Save the result file
			workbook.SaveToFile("Sample.xlsx", ExcelVersion.Version2010)
			Process.Start("Sample.xlsx")

		End Class
End Namespace

Published in Objects

Tagged under

xls net Objects