Tuesday, 09 September 2014 03:39

Marker Designer

Written by lisa chen
This demo shows you the usage of WorkbookDesigner.
{k2-demo src:$server/Spire.XLS/19_MarkerDesigner/MarkerDesigner.aspx|style:height:350px}
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);
          
      
        
        }
    }
}

Imports System.Data
Imports Spire.XLS
Namespace DemoOnlineCode
    Class MarkerDesigner

        Public Sub demoMarkerDesigner(filePath As String, dataFilePath As String, resultFilePath As String)
            Dim data_book As New Workbook()
            data_book.LoadFromFile(dataFilePath)
            Dim table As DataTable = data_book.Worksheets(0).ExportDataTable()

            Dim workbook As New Workbook()
            workbook.LoadFromFile(filePath)
            Dim sheet As Worksheet = workbook.Worksheets(0)
            Dim sheet2 As Worksheet = 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)



        End Sub
    End Class
End Namespace
Tuesday, 09 September 2014 03:38

Calculate Formulas

Written by lisa chen
This demo shows you how to calculate formulas and export data to datatable with calculating formulas.
{k2-demo src:$server/Spire.XLS/06_Formulas/CalculateFormulas.aspx|style:height:511px}
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;

        }
    }
}

Imports Spire.Xls

Namespace DemoOnlineCode
    Class CalculateFormulas
        Public Sub demoCalculateFormulas(resultFile As String)
            Dim workbook As New Workbook()
            Dim sheet As Worksheet = workbook.Worksheets(0)
            Calculate(workbook, sheet)
            workbook.SaveToFile(resultFile, ExcelVersion.Version2010)
        End Sub
        Public Sub Calculate(workbook As Workbook, sheet As Worksheet)
            Dim currentRow As Integer = 1
            Dim currentFormula As String = String.Empty
            Dim formulaResult As Object = Nothing
            Dim value As String = 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(System.Math.Max(System.Threading.Interlocked.Increment(currentRow), currentRow - 1), 1).Value = "Examples of formulas :"

            ' Set the value of Cell A2 
            sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Value = "Test data:"

            ' Set the style of Cell A1 
            Dim range As CellRange = 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(System.Threading.Interlocked.Increment(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(System.Threading.Interlocked.Increment(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(System.Threading.Interlocked.Increment(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(System.Threading.Interlocked.Increment(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(System.Threading.Interlocked.Increment(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(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula

            'Caculate random function 
            formulaResult = workbook.CaculateFormulaValue(currentFormula)
            value = formulaResult.ToString()
            sheet.Range(currentRow, 2).Value = value


        End Sub


    End Class


End Namespace
Tuesday, 09 September 2014 03:37

Import Data from Excel to Datatable

Written by lisa chen
This demo shows you how to read data from an excel workbook and export data.
{k2-demo src:$server/Spire.XLS/05_DataTable/ImportDatafromExceltoDatatable.aspx|style:height:495px}
using System.Data;
using Spire.Xls;

namespace DemoOnlineCode
{
    class ImportDatafromExceltoDatatable
    {
        public void demoImportDatafromExceltoDatatable(string filePath,string resultFilePath)
        {
            Workbook book = new Workbook();
            book.LoadFromFile(filePath);
            DataTable table = book.Worksheets[0].ExportDataTable();
            Workbook data_book = new Workbook();
            data_book.Worksheets[0].InsertDataTable(table,true,1,1);
            data_book.SaveToFile(resultFilePath,ExcelVersion.Version2010);

        
        }

    }
}
                                                          
Imports System.Data
Imports Spire.XLS

Namespace DemoOnlineCode
    Class ImportDatafromExceltoDatatable
        Public Sub demoImportDatafromExceltoDatatable(filePath As String, resultFilePath As String)
            Dim book As New Workbook()
            book.LoadFromFile(filePath)
            Dim table As DataTable = book.Worksheets(0).ExportDataTable()
            Dim data_book As New Workbook()
            data_book.Worksheets(0).InsertDataTable(table, True, 1, 1)
            data_book.SaveToFile(resultFilePath, ExcelVersion.Version2010)


        End Sub

    End Class
End Namespace
Tuesday, 09 September 2014 03:37

Export Datatable to Excel from Database

Written by lisa chen
This demo shows you how to import the data from datatable to spreadsheet.
{k2-demo src:$server/Spire.XLS/05_DataTable/ExportDatatabletoExcelfromDatabase.aspx|style:height:480px}
using System.Data.OleDb;
using System.Data;
using Spire.Xls;
namespace DemoOnlineCode
{
    class ExportDatatabletoExcelfromDatabase
    {
        public void demoExportDatatabletoExcel(string dataBasePath, string tableName,string resultName)
        {
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            DataTable dataTable = ExportDataTable(dataBasePath, tableName);
            sheet.InsertDataTable(dataTable, true, 1, 1);
            sheet.Name = tableName;
            book.SaveToFile(resultName,ExcelVersion.Version2010);
 
        }

        private DataTable ExportDataTable(string dataBasePath, string tableName)
        {
            OleDbConnection connection = new OleDbConnection();
         
            connection.ConnectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=" + dataBasePath + ";User Id=;Password=";
            OleDbCommand command = new OleDbCommand();
            command.CommandText = "select * from " + tableName;
            DataSet dataSet = new System.Data.DataSet();
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText, connection);
            dataAdapter.Fill(dataSet);
            DataTable dataTable = dataSet.Tables[0];
            return dataTable;
        }
    }
}

Imports System.Data.OleDb
Imports System.Data
Imports Spire.XLS
Namespace DemoOnlineCode
    Class ExportDatatabletoExcelfromDatabase
        Public Sub demoExportDatatabletoExcel(dataBasePath As String, tableName As String, resultName As String)
            Dim book As New Workbook()
            Dim sheet As Worksheet = book.Worksheets(0)
            Dim dataTable As DataTable = ExportDataTable(dataBasePath, tableName)
            sheet.InsertDataTable(dataTable, True, 1, 1)
            sheet.Name = tableName
            book.SaveToFile(resultName, ExcelVersion.Version2010)

        End Sub

        Private Function ExportDataTable(dataBasePath As String, tableName As String) As DataTable
            Dim connection As New OleDbConnection()

            connection.ConnectionString = "Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=" & dataBasePath & ";User Id=;Password="
            Dim command As New OleDbCommand()
            command.CommandText = "select * from " & tableName
            Dim dataSet As DataSet = New System.Data.DataSet()
            Dim dataAdapter As New OleDbDataAdapter(command.CommandText, connection)
            dataAdapter.Fill(dataSet)
            Dim dataTable As DataTable = dataSet.Tables(0)
            Return dataTable
        End Function
    End Class
End Namespace
Tuesday, 09 September 2014 03:33

Create Pivot Table In Excel

Written by lisa chen
This demo shows you how to create a privot table in excel.
{k2-demo src:$server/Spire.XLS/05_DataTable/CreatePivotTableInExcel.aspx|style:height:532px}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Spire.Xls;
namespace DemoOnlineCode
{
    class CreatePivotTableInExcel
    {
        public void demoCreatePivotTableInExcel(string filePath,string resultFilePath)
        {
            Workbook workbook = new 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;
            workbook.Worksheets[1].Remove();
            sheet = workbook.Worksheets[1];
            sheet.Columns[0].AutoFitColumns();
            sheet.Columns[0].AutoFitRows();
            sheet.AllocatedRange.AutoFitColumns();
            sheet.AllocatedRange.AutoFitRows();
            return workbook;

        }
}
}
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports System.Data
Imports Spire.XLS
Namespace DemoOnlineCode
    Class CreatePivotTableInExcel
        Public Sub demoCreatePivotTableInExcel(filePath As String, resultFilePath As String)
            Dim workbook As New Workbook()
            workbook = CreatePivotTable(filePath)
            workbook.SaveToFile(resultFilePath)
        End Sub

        Public Function CreatePivotTable(filePath As String) As Workbook
            Dim workbook As New Workbook()
            workbook.LoadFromFile(filePath, ExcelVersion.Version2007)
            Dim sheet As Worksheet = workbook.Worksheets(0)
            sheet.Name = "Data Source"
            Dim sheet2 As Worksheet = workbook.CreateEmptySheet()
            sheet2.Name = "Pivot Table"

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

            Dim r1 = pt.PivotFields("Vendor No")
            r1.Axis = AxisTypes.Row
            pt.Options.RowHeaderCaption = "Vendor No"

            Dim 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
            workbook.Worksheets(1).Remove()
            sheet = workbook.Worksheets(1)
            sheet.Columns(0).AutoFitColumns()
            sheet.Columns(0).AutoFitRows()
            sheet.AllocatedRange.AutoFitColumns()
            sheet.AllocatedRange.AutoFitRows()
            Return workbook

        End Function
    End Class
End Namespace
Tuesday, 09 September 2014 03:28

Charts

Written by lisa chen
This demo shows you how to create chart in an excel workbook.
{k2-demo src:$server/Spire.XLS/04_Charts/Charts.aspx|style:height:328px}
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;
        }
    }
}

Imports System.Data
Imports Spire.XLS

Namespace DemoOnlineCode
    Class Charts
        Public Sub demoChart(table As DataTable, chartFormat As ExcelChartType, resultFileName As String)
            Dim book As New Workbook()
            Dim sheet As Worksheet = book.Worksheets(0)
            sheet.InsertDataTable(table, True, 1, 1)
            SetChart(sheet, chartFormat)
            sheetStyle(book, sheet)
            book.SaveToFile(resultFileName, ExcelVersion.Version2010)

        End Sub
        Private Sub SetChart(sheet As Worksheet, chartFormat As ExcelChartType)
            sheet.Name = "Chart data"
            sheet.GridLinesVisible = False

            'Writes chart data
            'CreateChartData(sheet);
            'Add a new  chart worsheet to workbook
            Dim chart As 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


            For Each cs As Spire.Xls.Charts.ChartSerie In chart.Series
                cs.Format.Options.IsVaryColor = True
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True
            Next

            chart.Legend.Position = LegendPositionType.Top

        End Sub
        Public Shared Sub sheetStyle(book As Workbook, sheet As Worksheet)
            Dim oddStyle As CellStyle = 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

            Dim evenStyle As CellStyle = 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


            For Each range As CellRange In sheet.AllocatedRange.Rows
                If range.Row Mod 2 = 0 Then
                    range.CellStyleName = evenStyle.Name
                Else
                    range.CellStyleName = oddStyle.Name
                End If
            Next

            'Sets header style
            Dim styleHeader As CellStyle = 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
        End Sub
    End Class
End Namespace

Tuesday, 09 September 2014 02:17

Convertors

Written by Administrator
This demo shows you how to convert an Excel document (xls/xlsx) to PDF, HTML, Image file format.
{k2-demo src:$server/Spire.XLS/21_Misc/Convertors.aspx|style:height:189px}
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.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, 96, 96);
                    }
                    if (images != null && images.Length > 0)
                    {
                        if (images.Length == 1)
                        {
                            images[0].Save(resultFileName+".bmp", System.Drawing.Imaging.ImageFormat.Bmp);
                        }
                        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;
            }
        }
    }
}


Imports Spire.XLS
Imports System.Drawing
Imports System.Drawing.Imaging

Namespace DemoOnlineCode
    Class Convertors
        Public Sub demoConvert(filePath As [String], format As String, resultFileName As String)
            Dim book As New Workbook()
            book.LoadFromFile(filePath)
            ConvertFormat(book, format, resultFileName)
        End Sub
        Private Sub ConvertFormat(workbook As Workbook, format As String, resultFileName As String)
            Select Case format
                Case "PDF"
                    workbook.SaveToPdf(resultFileName & ".pdf", Spire.Xls.FileFormat.PDF)
                    Exit Select

                Case "Image"
                    Dim images As Image() = New Image(workbook.Worksheets.Count - 1) {}
                    For i As Integer = 0 To workbook.Worksheets.Count - 1
                        images(i) = workbook.SaveAsImage(i, 96, 96)
                    Next
                    If images IsNot Nothing AndAlso images.Length > 0 Then
                        If images.Length = 1 Then
                            images(0).Save(resultFileName & ".bmp", System.Drawing.Imaging.ImageFormat.Bmp)
                        Else
                            For i As Integer = 0 To images.Length - 1
                                Dim fileName As [String] = [String].Format("{0}-Image-{1}.png", resultFileName, i)
                                images(i).Save(fileName, ImageFormat.Png)
                            Next
                        End If
                    End If
                    Exit Select

                Case "HTML"
                    For i As Integer = 0 To workbook.Worksheets.Count - 1
                        Dim sheet As Worksheet = workbook.Worksheets(i)
                        Dim htmlPath As String = String.Format(resultFileName & "-{0}.html", System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1))
                        sheet.SaveToHtml(htmlPath)
                    Next
                    Exit Select
            End Select
        End Sub
    End Class
End Namespace



Thursday, 19 June 2014 01:50

MailMerge

Written by lisa chen
This demo shows you how to merge some data into a Word template. Our Spire.Doc provides also the function NestedMailMerge with which you can merge the main-table and sub-table into a Word template to get a professional report. You can get a full demo from the article How to Use Mail Merge to Create Report
{k2-demo src:$server/spire.doc/07_MailMerge/MailMerge.aspx|style:height:257px}
using System;
using Spire.Doc;

namespace DemoOnlineCode
{
    class MailMerge
    {
        public void demoMailMerge(String docFile)
        {
            Document document = new Document(docFile, FileFormat.Auto);

            string[] values = {
                                  DateTime.Today.AddYears(-5).ToString("yyyy-MM-dd"),
                                  DateTime.Today.AddYears(5).ToString("yyyy-MM-dd"),
                                  DateTime.Today.AddYears(6).ToString("yyyy-MM-dd"),
                                  DateTime.Today.AddYears(-2).ToString("yyyy-MM-dd"),
                                  DateTime.Today.AddYears(2).ToString("yyyy-MM-dd")
                              };
            string[] fields = {
                                   "SubGrantPAStartDateValue",
                                   "SubGrantPAEndDateValue",
                                   "SubGrantPAExtensionDateValue",
                                   "SubGrantPSStartDateValue",
                                   "SubGrantPSEndDateValue"
                                 };
            document.MailMerge.Execute(fields, values);
            document.SaveToFile("demo.doc", FileFormat.Doc);

        }
    }
}
Imports Spire.Doc

Namespace DemoOnlineCode
    Class MailMerge
        Public Sub demoMailMerge(docFile As [String])
            Dim document As New Document(docFile, FileFormat.Auto)
            Dim values As String() = {
                                      DateTime.Today.AddYears(-5).ToString("yyyy-MM-dd"),
                                      DateTime.Today.AddYears(5).ToString("yyyy-MM-dd"),
                                      DateTime.Today.AddYears(6).ToString("yyyy-MM-dd"),
                                      DateTime.Today.AddYears(-2).ToString("yyyy-MM-dd"),
                                      DateTime.Today.AddYears(2).ToString("yyyy-MM-dd")
                                  }
            Dim fields As String() = {
                "SubGrantPAStartDateValue",
                "SubGrantPAEndDateValue",
                "SubGrantPAExtensionDateValue",
                "SubGrantPSStartDateValue",
                "SubGrantPSEndDateValue"
                                      }
            document.MailMerge.Execute(fields, values)
            document.SaveToFile("demo.doc", FileFormat.Doc)
        End Sub
    End Class
End Namespace
Thursday, 19 June 2014 01:48

Table

Written by lisa chen
This demo shows you how to create a table with specified data in a Word document. We also show you how to set the border and background color of the table.
{k2-demo src:$server/spire.doc/04_Context/Table.aspx|style:height:514px}
using System;
using System.Data;
using System.Drawing;

using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Formatting;


namespace DemoOnlineCode
{
    class Table
    {
        public void demoTable(String docFile, DataTable dataTable,
            string borderColor = null,
            string headerBackColor = null,
            string rowBackColor = null,
            string alternationRowColor = null)
        {
            Document document = new Document(docFile, FileFormat.Auto);
            addTable(document.Sections[0], dataTable,
               ConvertStrToColor(borderColor),
               ConvertStrToColor(headerBackColor),
               ConvertStrToColor(rowBackColor),
               ConvertStrToColor(alternationRowColor));
            document.SaveToFile("demo.doc", FileFormat.Doc);
        }

        private void addTable(Section section,
            DataTable dataTable,
            Color borderColor,
            Color headerBackColor,
            Color rowBackColor,
            Color alternationRowColor)
        {
            Spire.Doc.Table table = section.AddTable();
            int rowCount = dataTable.Rows.Count;
            int columnCount = dataTable.Columns.Count;
            table.DefaultRowHeight = 25;
            table.DefaultColumnWidth = 0;
            table.ResetCells(rowCount + 1, columnCount);

            table.TableFormat.Borders.Left.BorderType
                = Spire.Doc.Documents.BorderStyle.Hairline;
            table.TableFormat.Borders.Left.Color = borderColor;

            table.TableFormat.Borders.Top.BorderType
                = Spire.Doc.Documents.BorderStyle.Hairline;
            table.TableFormat.Borders.Top.Color = borderColor;

            table.TableFormat.Borders.Right.BorderType
                = Spire.Doc.Documents.BorderStyle.Hairline;
            table.TableFormat.Borders.Right.Color = borderColor;

            table.TableFormat.Borders.Bottom.BorderType
                = Spire.Doc.Documents.BorderStyle.Hairline;
            table.TableFormat.Borders.Bottom.Color = borderColor;

            table.TableFormat.Borders.Horizontal.BorderType
                = Spire.Doc.Documents.BorderStyle.Hairline;
            table.TableFormat.Borders.Horizontal.Color = borderColor;

            table.TableFormat.Borders.Vertical.BorderType
                = Spire.Doc.Documents.BorderStyle.Hairline;
            table.TableFormat.Borders.Vertical.Color = borderColor;


            TableRow headerRow = table.Rows[0];
            headerRow.IsHeader = true;

            for (int c = 0; c < columnCount; c++)
            {
                Paragraph p = headerRow.Cells[c].AddParagraph();
                p.Format.HorizontalAlignment = HorizontalAlignment.Center;

                Spire.Doc.Fields.TextRange headerText = p.AppendText(dataTable.Columns[c].ColumnName);
                headerText.CharacterFormat.Bold = true;

                CellFormat cellStyle = headerRow.Cells[c].CellFormat;
                cellStyle.VerticalAlignment = VerticalAlignment.Middle;
                headerRow.Cells[c].CellFormat.BackColor = headerBackColor;
            }

            for (int i = 0; i < rowCount; i++)
            {
                object[] rowContent = dataTable.Rows[i].ItemArray;
                DataRow row = dataTable.Rows[i];

                for (int j = 0; j < columnCount; j++)
                {
                    Paragraph p = table.Rows[i + 1].Cells[j].AddParagraph();

                    if (rowContent[j] is byte[])
                    {
                        p.Format.HorizontalAlignment = HorizontalAlignment.Center;
                        p.AppendPicture(rowContent[j] as byte[]);
                    }
                    else
                    {
                        p.AppendText(rowContent[j].ToString());
                    }

                    CellFormat cellStyle = table.Rows[i + 1].Cells[j].CellFormat;
                    cellStyle.VerticalAlignment = VerticalAlignment.Middle;

                    cellStyle.BackColor = rowBackColor;
                    if (i % 2 == 1 && alternationRowColor != Color.Empty)
                    {
                        cellStyle.BackColor = alternationRowColor;
                    }
                }
            }
        }

        private Color ConvertStrToColor(string strColor)
        {
            if (String.IsNullOrWhiteSpace(strColor))
            {
                return Color.Empty;
            }
            else
            {
                return ColorTranslator.FromHtml("#" + strColor);
            }
        }
    }
}

Imports System.Data
Imports System.Drawing

Imports Spire.Doc
Imports Spire.Doc.Documents
Imports Spire.Doc.Formatting


Namespace DemoOnlineCode
    Class Table
        Public Sub demoTable(docFile As [String],
                             dataTable As DataTable,
                             Optional borderColor As String = Nothing,
                             Optional headerBackColor As String = Nothing,
                             Optional rowBackColor As String = Nothing,
                             Optional alternationRowColor As String = Nothing)
            Dim document As New Document(docFile, FileFormat.Auto)
            addTable(document.Sections(0),
                     dataTable, ConvertStrToColor(borderColor),
                     ConvertStrToColor(headerBackColor),
                     ConvertStrToColor(rowBackColor),
                     ConvertStrToColor(alternationRowColor))
            document.SaveToFile("demo.doc", FileFormat.Doc)
        End Sub
        Private Sub addTable(section As Section,
                             dataTable As DataTable,
                             borderColor As Color,
                             headerBackColor As Color,
                             rowBackColor As Color,
                             alternationRowColor As Color)
            Dim table As Spire.Doc.Table = section.AddTable()
            Dim rowCount As Integer = dataTable.Rows.Count
            Dim columnCount As Integer = dataTable.Columns.Count
            table.DefaultRowHeight = 25
            table.DefaultColumnWidth = 0
            table.ResetCells(rowCount + 1, columnCount)

            table.TableFormat.Borders.Left.BorderType = Spire.Doc.Documents.BorderStyle.Hairline
            table.TableFormat.Borders.Left.Color = borderColor

            table.TableFormat.Borders.Top.BorderType = Spire.Doc.Documents.BorderStyle.Hairline
            table.TableFormat.Borders.Top.Color = borderColor

            table.TableFormat.Borders.Right.BorderType = Spire.Doc.Documents.BorderStyle.Hairline
            table.TableFormat.Borders.Right.Color = borderColor

            table.TableFormat.Borders.Bottom.BorderType = Spire.Doc.Documents.BorderStyle.Hairline
            table.TableFormat.Borders.Bottom.Color = borderColor

            table.TableFormat.Borders.Horizontal.BorderType = Spire.Doc.Documents.BorderStyle.Hairline
            table.TableFormat.Borders.Horizontal.Color = borderColor

            table.TableFormat.Borders.Vertical.BorderType = Spire.Doc.Documents.BorderStyle.Hairline
            table.TableFormat.Borders.Vertical.Color = borderColor


            Dim headerRow As TableRow = table.Rows(0)
            headerRow.IsHeader = True

            For c As Integer = 0 To columnCount - 1
                Dim p As Paragraph = headerRow.Cells(c).AddParagraph()
                p.Format.HorizontalAlignment = HorizontalAlignment.Center

                Dim headerText As Spire.Doc.Fields.TextRange _
                    = p.AppendText(dataTable.Columns(c).ColumnName)
                headerText.CharacterFormat.Bold = True

                Dim cellStyle As CellFormat = headerRow.Cells(c).CellFormat
                cellStyle.VerticalAlignment = VerticalAlignment.Middle
                headerRow.Cells(c).CellFormat.BackColor = headerBackColor
            Next
            For i As Integer = 0 To rowCount - 1
                Dim rowContent As Object() = dataTable.Rows(i).ItemArray
                Dim row As DataRow = dataTable.Rows(i)

                For j As Integer = 0 To columnCount - 1
                    Dim p As Paragraph = table.Rows(i + 1).Cells(j).AddParagraph()

                    If TypeOf rowContent(j) Is Byte() Then
                        p.Format.HorizontalAlignment = HorizontalAlignment.Center
                        p.AppendPicture(TryCast(rowContent(j), Byte()))
                    Else
                        p.AppendText(rowContent(j).ToString())
                    End If

                    Dim cellStyle As CellFormat = table.Rows(i + 1).Cells(j).CellFormat
                    cellStyle.VerticalAlignment = VerticalAlignment.Middle

                    cellStyle.BackColor = rowBackColor
                    If i Mod 2 = 1 AndAlso alternationRowColor <> Color.Empty Then
                        cellStyle.BackColor = alternationRowColor
                    End If
                Next
            Next
        End Sub
        Private Function ConvertStrToColor(strColor As String) As Color
            If [String].IsNullOrWhiteSpace(strColor) Then
                Return Color.Empty
            Else
                Return ColorTranslator.FromHtml("#" & strColor)
            End If
        End Function
    End Class
End Namespace
Thursday, 19 June 2014 01:42

HeaderAndFooter

Written by lisa chen
This demo shows you how to set the page size, page header and footer of the Word document. We show you also set some picture as the background of the header/footer.
{k2-demo src:$server/spire.doc/04_Context/HeaderAndFooter.aspx|style:height:467px}
using System;
using System.Drawing;

using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;

namespace DemoOnlineCode
{
    class HeaderAndFooter
    {
        public void demoHeaderAndFooter(String docFile,
            String headerImageFile = null,
            String footerImageFile = null,
            string pageSizeName = "A4",
            String headerFontColor = null,
            String footerFontColor = null)
        {
            Document document = new Document(docFile, FileFormat.Auto);
            SizeF pageSize = (SizeF)typeof(PageSize).GetField(pageSizeName).GetValue(null);
            PageSetup(document, pageSize);
            SetHeader(document, ConvertStrToColor(headerFontColor), headerImageFile);
            SetFooter(document,ConvertStrToColor(footerFontColor), footerImageFile);
            document.SaveToFile("demo.doc", FileFormat.Doc);

        }

        private void PageSetup(Document document, SizeF pageSize)
        {
            for (int i = 0; i < document.Sections.Count; i++)
            {
                Section section = document.Sections[i];
                section.PageSetup.PageSize = pageSize;
                section.PageSetup.Margins.Top = (float)72.0;
                section.PageSetup.Margins.Bottom = (float)72.0;
                section.PageSetup.Margins.Left = (float)89.0;
                section.PageSetup.Margins.Right = (float)89.0;
            }
        }

        private void SetFooter(Document document,
            Color fontColor,
            string footerImageFile = null,
            string footerText = "Spire.Doc",
            string fontName = "Calibri",
            float fontSize = (float)12.0,
            HorizontalAlignment alignment = HorizontalAlignment.Left)
        {
            for (int i = 0; i < document.Sections.Count; i++)
            {
                Section section = document.Sections[i];
                HeaderFooter footer = section.HeadersFooters.Footer;
                Paragraph footerParagraph = footer.AddParagraph();
                TextRange text = footerParagraph.AppendText(footerText);

                text.CharacterFormat.FontName = fontName;
                text.CharacterFormat.FontSize = fontSize;
                text.CharacterFormat.TextColor = fontColor;
                footerParagraph.Format.HorizontalAlignment = alignment;

                //border
                footerParagraph.Format.Borders.Top.BorderType
                    = (BorderStyle)Spire.Doc.Documents.BorderStyle.Hairline;
                footerParagraph.Format.Borders.Top.Space = (float)0.03;
                if (footerImageFile != null)
                {
                    Byte[] imagedata = System.IO.File.ReadAllBytes(footerImageFile);
                    //insert picture to footer
                    DocPicture footerPicture = footerParagraph.AppendPicture(imagedata);

                    //footer picture layout
                    footerPicture.TextWrappingStyle = TextWrappingStyle.Behind;
                    footerPicture.HorizontalOrigin = HorizontalOrigin.Page;
                    footerPicture.HorizontalAlignment = ShapeHorizontalAlignment.Left;
                    footerPicture.VerticalOrigin = VerticalOrigin.Page;
                    footerPicture.VerticalAlignment = ShapeVerticalAlignment.Bottom;

                }
                //insert page number
                footerParagraph = footer.AddParagraph();
                footerParagraph.AppendField("page number", FieldType.FieldPage);
                footerParagraph.AppendText("of");
                footerParagraph.AppendField("number of pages", FieldType.FieldNumPages);
                footerParagraph.Format.HorizontalAlignment
                    = (HorizontalAlignment)HorizontalAlignment.Right;
            }
        }

        private void SetHeader(Document document,
            Color fontColor,
            string headerImageFile = null,
            string headerText = "E-iceblue",
            string fontName = "Calibri",
            float fontSize = (float)12.0,
            HorizontalAlignment alignment = HorizontalAlignment.Center)
        {
            for (int i = 0; i < document.Sections.Count; i++)
            {
                Section section = document.Sections[i];
                HeaderFooter header = section.HeadersFooters.Header;

                //insert picture and text to header
                Paragraph headerParagraph = header.AddParagraph();
                if (headerImageFile != null)
                {
                    Byte[] imagedata = System.IO.File.ReadAllBytes(headerImageFile);
                    DocPicture headerPicture = headerParagraph.AppendPicture(imagedata);

                    //header picture layout - text wrapping
                    headerPicture.TextWrappingStyle = TextWrappingStyle.Behind;

                    //header picture layout - position
                    headerPicture.HorizontalOrigin = HorizontalOrigin.Page;
                    headerPicture.HorizontalAlignment = ShapeHorizontalAlignment.Left;
                    headerPicture.VerticalOrigin = VerticalOrigin.Page;
                    headerPicture.VerticalAlignment = ShapeVerticalAlignment.Top;
                }

                //header text
                TextRange text = headerParagraph.AppendText(headerText);
                text.CharacterFormat.FontName = fontName;
                text.CharacterFormat.FontSize = Convert.ToSingle(fontSize);
                text.CharacterFormat.TextColor = fontColor;
                headerParagraph.Format.HorizontalAlignment = alignment;

                //border
                headerParagraph.Format.Borders.Bottom.BorderType
                    = (Spire.Doc.Documents.BorderStyle)Spire.Doc.Documents.BorderStyle.Hairline;
                headerParagraph.Format.Borders.Bottom.Space = (float)0.03;

            }
        }

        private Color ConvertStrToColor(string strColor)
        {
            if (String.IsNullOrWhiteSpace(strColor))
            {
                return Color.Empty;
            }
            else
            {
                return ColorTranslator.FromHtml("#" + strColor);
            }
        }

    }
}
Imports System.Drawing

Imports Spire.Doc
Imports Spire.Doc.Documents
Imports Spire.Doc.Fields

Namespace DemoOnlineCode
    Class HeaderAndFooter
        Public Sub demoHeaderAndFooter(docFile As [String],
                                       Optional headerImageFile As [String] = Nothing,
                                       Optional footerImageFile As [String] = Nothing,
                                       Optional pageSizeName As String = "A4",
                                       Optional headerFontColor As [String] = Nothing,
                                       Optional footerFontColor As [String] = Nothing)
            Dim document As New Document(docFile, FileFormat.Auto)
            Dim pageSize As SizeF _
                = CType(GetType(PageSize).GetField(pageSizeName).GetValue(Nothing), SizeF)
            PageSetup(document, pageSize)
            SetHeader(document, ConvertStrToColor(headerFontColor), headerImageFile)
            SetFooter(document, ConvertStrToColor(footerFontColor), footerImageFile)
            document.SaveToFile("demo.doc", FileFormat.Doc)

        End Sub
        Private Sub PageSetup(document As Document, pageSize As SizeF)
            For i As Integer = 0 To document.Sections.Count - 1
                Dim section As Section = document.Sections(i)
                section.PageSetup.PageSize = pageSize
                section.PageSetup.Margins.Top = CSng(72.0)
                section.PageSetup.Margins.Bottom = CSng(72.0)
                section.PageSetup.Margins.Left = CSng(89.0)
                section.PageSetup.Margins.Right = CSng(89.0)
            Next
        End Sub
        Private Sub SetFooter(document As Document,
                              fontColor As Color,
                              Optional footerImageFile As String = Nothing,
                              Optional footerText As String = "Spire.Doc",
                              Optional fontName As String = "Calibri",
                              Optional fontSize As Single = CSng(12.0), _
                              Optional alignment As HorizontalAlignment = HorizontalAlignment.Left)
            For i As Integer = 0 To document.Sections.Count - 1
                Dim section As Section = document.Sections(i)
                Dim footer As HeaderFooter = section.HeadersFooters.Footer
                Dim footerParagraph As Paragraph = footer.AddParagraph()
                Dim text As TextRange = footerParagraph.AppendText(footerText)

                text.CharacterFormat.FontName = fontName
                text.CharacterFormat.FontSize = fontSize
                text.CharacterFormat.TextColor = fontColor
                footerParagraph.Format.HorizontalAlignment = alignment

                'border
                footerParagraph.Format.Borders.Top.BorderType _
                    = DirectCast(Spire.Doc.Documents.BorderStyle.Hairline, BorderStyle)
                footerParagraph.Format.Borders.Top.Space = CSng(0.03)
                If footerImageFile IsNot Nothing Then
                    Dim imagedata As [Byte]() = System.IO.File.ReadAllBytes(footerImageFile)
                    'insert picture to footer
                    Dim footerPicture As DocPicture = footerParagraph.AppendPicture(imagedata)

                    'footer picture layout
                    footerPicture.TextWrappingStyle = TextWrappingStyle.Behind
                    footerPicture.HorizontalOrigin = HorizontalOrigin.Page
                    footerPicture.HorizontalAlignment = ShapeHorizontalAlignment.Left
                    footerPicture.VerticalOrigin = VerticalOrigin.Page

                    footerPicture.VerticalAlignment = ShapeVerticalAlignment.Bottom
                End If
                'insert page number
                footerParagraph = footer.AddParagraph()
                footerParagraph.AppendField("page number", FieldType.FieldPage)
                footerParagraph.AppendText("of")
                footerParagraph.AppendField("number of pages", FieldType.FieldNumPages)
                footerParagraph.Format.HorizontalAlignment _
                    = DirectCast(HorizontalAlignment.Right, HorizontalAlignment)
            Next
        End Sub
        Private Sub SetHeader(document As Document,
                              fontColor As Color,
                              Optional headerImageFile As String = Nothing,
                              Optional headerText As String = "E-iceblue",
                              Optional fontName As String = "Calibri",
                              Optional fontSize As Single = CSng(12.0), _
         Optional alignment As HorizontalAlignment = HorizontalAlignment.Center)
            For i As Integer = 0 To document.Sections.Count - 1
                Dim section As Section = document.Sections(i)
                Dim header As HeaderFooter = section.HeadersFooters.Header

                'insert picture and text to header
                Dim headerParagraph As Paragraph = header.AddParagraph()
                If headerImageFile IsNot Nothing Then
                    Dim imagedata As [Byte]() = System.IO.File.ReadAllBytes(headerImageFile)
                    Dim headerPicture As DocPicture = headerParagraph.AppendPicture(imagedata)

                    'header picture layout - text wrapping
                    headerPicture.TextWrappingStyle = TextWrappingStyle.Behind

                    'header picture layout - position
                    headerPicture.HorizontalOrigin = HorizontalOrigin.Page
                    headerPicture.HorizontalAlignment = ShapeHorizontalAlignment.Left
                    headerPicture.VerticalOrigin = VerticalOrigin.Page
                    headerPicture.VerticalAlignment = ShapeVerticalAlignment.Top
                End If

                'header text
                Dim text As TextRange = headerParagraph.AppendText(headerText)
                text.CharacterFormat.FontName = fontName
                text.CharacterFormat.FontSize = Convert.ToSingle(fontSize)
                text.CharacterFormat.TextColor = fontColor
                headerParagraph.Format.HorizontalAlignment = alignment

                'border
                headerParagraph.Format.Borders.Bottom.BorderType _
                    = DirectCast(BorderStyle.Hairline, Spire.Doc.Documents.BorderStyle)

                headerParagraph.Format.Borders.Bottom.Space = CSng(0.03)
            Next
        End Sub
        Private Function ConvertStrToColor(strColor As String) As Color
            If [String].IsNullOrWhiteSpace(strColor) Then
                Return Color.Empty
            Else
                Return ColorTranslator.FromHtml("#" & strColor)
            End If
        End Function
    End Class
End Namespace