support iceblue

Thursday, 20 October 2011 09:26

Word to EPUB in C#, VB.NET

Demo
C# source
VB.Net source

The sample demonstrates how to export doc document to Epub file.

using Spire.Doc;

namespace DocToEpub
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create word document
            Document doc = new Document();
            doc.LoadFromFile(@"..\..\..\..\..\..\Data\DocToEpub.docx", FileFormat.Docx);
            doc.SaveToFile(@"DocToEupb.epub",FileFormat.EPub);
            System.Diagnostics.Process.Start(@"DocToEupb.epub");
        }
    }
}

Imports Spire.Doc

Namespace DocToEpub
	Friend Class Program
	Shared Sub Main(ByVal args() As String)
        'Create word document
	Dim doc As New Document()
	doc.LoadFromFile("..\..\..\..\..\..\Data\DocToEpub.docx",FileFormat.Docx)
	doc.SaveToFile("DocToEupb.epub",FileFormat.EPub)
	Process.Start("DocToEupb.epub")
	End Sub
	End Class
End Namespace

Published in Convertors

Tagged under

content
Doc

Wednesday, 21 September 2011 08:09

How to Leave Us a Note

Thanks for visiting e-iceblue store. The guide below is showing you how to leave a note when place an order. It's very simple to tell us anything you like.

Step 1: Choose the license which you need to renew from "Purchase" category and click "add to cart".

Step 2: Write in coupon code in the box as the image shows below.

Step 3: You'll find the price changed as 30/20% Off. Select payment method and click "Next".

Step 4: Leave a note as the sample below.

After your order is confirmed, we will send you new license file within 24 hours (usually 30 minutes to a few hours).

Published in Purchase

Tagged under

Purchase

Friday, 09 September 2011 03:37

The sample demonstrates how to provide a dropdownlist for valid data

                     
using System;

using Spire.Xls;

namespace DropDownList
{
    class Program
    {
        private static void CreateSampleData(Worksheet sheet)
        {
            sheet.Range["D8"].Text = "Country";
            //set the datasource of the E8
            sheet.Range["X1"].Text = "Lucy";
            sheet.Range["X2"].Text = "Bolivia";
            sheet.Range["X3"].Text = "Brazil";
            sheet.Range["X4"].Text = "Canada";
            sheet.Range["X5"].Text = "Chile";
            sheet.Range["X6"].Text = "Colombia";
            sheet.Range["X7"].Text = "Cuba";
            sheet.Range["X8"].Text = "Ecuador";
            sheet.Range["X9"].Text = " Salvado";
            sheet.Range["X10"].Text = "Guyana";
            sheet.Range["X11"].Text = "Jamaicatates";
            sheet.Range["X12"].Text = "Mexico";
            sheet.Range["X13"].Text = "Nicaragua";
            sheet.Range["X14"].Text = "Paraguay";
            sheet.Range["X15"].Text = "Peru";
            sheet.Range["X16"].Text = "United";
            sheet.Range["X17"].Text = "Uruguay";
            sheet.Range["X18"].Text = "Venezuel";

            CellRange rangeName = sheet.Range["E8"];
            rangeName.DataValidation.AllowType = CellDataType.Formula;
            rangeName.DataValidation.DataRange = sheet.Range["X1:X18"];
            rangeName.DataValidation.IgnoreBlank = true;
            rangeName.Activate();
            //hide column X                                 
            sheet.HideColumn(sheet.Range["X1"].Column);

            sheet.Range["D10"].Text = "Capital";
            //set the datasource of the E9
            sheet.Range["Y1"].Text = "Buenos Aires";
            sheet.Range["Y2"].Text = "La Paz";
            sheet.Range["Y3"].Text = "Brasilia";
            sheet.Range["Y4"].Text = "Ottawa";
            sheet.Range["Y5"].Text = "Santiago";
            sheet.Range["Y6"].Text = "Bagota";
            sheet.Range["Y7"].Text = "Havana";
            sheet.Range["Y8"].Text = "Quito";
            sheet.Range["Y9"].Text = "San Salvador";
            sheet.Range["Y10"].Text = "Georgetown";
            sheet.Range["Y11"].Text = "Kingston";
            sheet.Range["Y12"].Text = "Mexico City";
            sheet.Range["Y13"].Text = "Managua";
            sheet.Range["Y14"].Text = "Asuncion";
            sheet.Range["Y15"].Text = "Lima";
            sheet.Range["Y16"].Text = "Washington";
            sheet.Range["Y17"].Text = "Montevideo";
            sheet.Range["Y18"].Text = "Caracas";
            //Binding the datasource                 
            CellRange rangeCapital = sheet.Range["E10"];
            rangeCapital.DataValidation.AllowType = CellDataType.Formula;
            rangeCapital.DataValidation.DataRange = sheet.Range["Y1:Y18"];
            rangeCapital.DataValidation.IsSuppressDropDownArrow = false;
            rangeCapital.Activate();
            //hide coulumn Y
            sheet.HideColumn(sheet.Range["Y1"].Column);

            sheet.Range["D12"].Text = "Continent";
            //set the datasource of the F9
            CellRange rangeContinent = sheet.Range["E12"];
            rangeContinent.DataValidation.AllowType = CellDataType.Formula;
            rangeContinent.DataValidation.Values = new String[] { "South America", "North America" };
            rangeContinent.DataValidation.IsSuppressDropDownArrow = false;
            rangeContinent.Activate();

            //Set the column's width
            sheet.Range["E8"].Text = "Please select the country";
            sheet.Range["E10"].Text = "Please select the capital";
            sheet.Range["E12"].Text = "Please select the con tinent";
            sheet.AutoFitColumn(4);
            sheet.AutoFitColumn(5);

            //Style
            sheet.Range["D8:D12"].Style.Font.IsBold = true;
            sheet.Range["D8"].Style.KnownColor = ExcelColors.Color36;
            sheet.Range["D10"].Style.KnownColor = ExcelColors.Color36;
            sheet.Range["D12"].Style.KnownColor = ExcelColors.Color36;
            sheet.Range["E8"].Style.KnownColor = ExcelColors.LightGreen1;
            sheet.Range["E10"].Style.KnownColor = ExcelColors.LightGreen1;
            sheet.Range["E12"].Style.KnownColor = ExcelColors.LightGreen1;

            sheet.SetRowHeight(8, 16);
            sheet.SetRowHeight(10, 16);
            sheet.SetRowHeight(12, 16);
        }
        static void Main(string[] args)
        {
            //Build the workbook
            Workbook workbook = new Workbook();
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];
            sheet.GridLinesVisible = false;

            //Write sample data
            CreateSampleData(sheet);
            workbook.SaveToFile("test.xls");
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

                                                                                                          
Imports Spire.Xls

Module Module1
    Private Sub CreateSampleData(ByVal sheet As Worksheet)
        sheet.Range("D8").Text = "Country"
        'Set the datasource of D9
        sheet.Range("X1").Text = "Lucy"
        sheet.Range("X2").Text = "Bolivia"
        sheet.Range("X3").Text = "Brazil"
        sheet.Range("X4").Text = "Canada"
        sheet.Range("X5").Text = "Chile"
        sheet.Range("X6").Text = "Colombia"
        sheet.Range("X7").Text = "Cuba"
        sheet.Range("X8").Text = "Ecuador"
        sheet.Range("X9").Text = " Salvado"
        sheet.Range("X10").Text = "Guyana"
        sheet.Range("X11").Text = "Jamaicatates"
        sheet.Range("X12").Text = "Mexico"
        sheet.Range("X13").Text = "Nicaragua"
        sheet.Range("X14").Text = "Paraguay"
        sheet.Range("X15").Text = "Peru"
        sheet.Range("X16").Text = "United"
        sheet.Range("X17").Text = "Uruguay"
        sheet.Range("X18").Text = "Venezue"

        'Binding the datasource of D9
        Dim rangeCountry As CellRange = sheet.Range("E8")
        rangeCountry.DataValidation.AllowType = CellDataType.Formula
        rangeCountry.DataValidation.DataRange = sheet.Range("X1:X18")
        rangeCountry.DataValidation.IsSuppressDropDownArrow = False
        rangeCountry.DataValidation.IgnoreBlank = True
        rangeCountry.Activate()
        sheet.HideColumn(sheet.Range("X1").Column)

        sheet.Range("D10").Text = "Capital"
        'Set the datasource of E9
        sheet.Range("Y1").Text = "Buenos Aires"
        sheet.Range("Y2").Text = "La Paz"
        sheet.Range("Y3").Text = "Brasilia"
        sheet.Range("Y4").Text = "Ottawa"
        sheet.Range("Y5").Text = "Santiago"
        sheet.Range("Y6").Text = "Bagota"
        sheet.Range("Y7").Text = "Havana"
        sheet.Range("Y8").Text = "Quito"
        sheet.Range("Y9").Text = "San Salvador"
        sheet.Range("Y10").Text = "Georgetown"
        sheet.Range("Y11").Text = "Kingston"
        sheet.Range("Y12").Text = "Mexico City"
        sheet.Range("Y13").Text = "Managua"
        sheet.Range("Y14").Text = "Asuncion"
        sheet.Range("Y15").Text = "Lima"
        sheet.Range("Y16").Text = "Washington"
        sheet.Range("Y17").Text = "Montevideo"
        sheet.Range("Y18").Text = "Caracas"

        'Binding the datasource of E9
        Dim rangeCapital As CellRange = sheet.Range("E10")
        rangeCapital.DataValidation.AllowType = CellDataType.Formula
        rangeCapital.DataValidation.DataRange = sheet.Range("Y1:Y18")
        rangeCapital.DataValidation.IsSuppressDropDownArrow = False
        rangeCapital.DataValidation.IgnoreBlank = True
        rangeCapital.Activate()
        sheet.HideColumn(sheet.Range("Y1").Column)

        sheet.Range("D12").Text = "Continent"
        'Set the datasource of F9
        Dim rangeContinent As CellRange = sheet.Range("E12")
        rangeContinent.DataValidation.AllowType = CellDataType.Formula
        rangeContinent.DataValidation.Values = New String() {"South America", "North America"}
        rangeContinent.DataValidation.IsSuppressDropDownArrow = False
        rangeContinent.Activate()

        'Set the width of the column
        sheet.Range("E8").Text = "Please select the country "
        sheet.Range("E10").Text = "Please select the capital "
        sheet.Range("E12").Text = "Please select the continent "
        sheet.AutoFitColumn(4)
        sheet.AutoFitColumn(5)

        'Style                                                            
        sheet.Range("D8:D12").Style.Font.IsBold = True
        sheet.Range("D8").Style.KnownColor = ExcelColors.Color36
        sheet.Range("D10").Style.KnownColor = ExcelColors.Color36
        sheet.Range("D12").Style.KnownColor = ExcelColors.Color36
        sheet.Range("E8").Style.KnownColor = ExcelColors.LightGreen1
        sheet.Range("E10").Style.KnownColor = ExcelColors.LightGreen1
        sheet.Range("E12").Style.KnownColor = ExcelColors.LightGreen1
        sheet.SetRowHeight(8, 16)
        sheet.SetRowHeight(10, 16)
        sheet.SetRowHeight(12, 16)
    End Sub

    Sub Main()
        Dim workbook As Workbook = New Workbook()
        workbook.CreateEmptySheet(1)
        Dim sheet As Worksheet = workbook.Worksheets(0)
        sheet.GridLinesVisible = False
        'Writes sample data
        CreateSampleData(sheet)
        workbook.SaveToFile("test.xls")
        System.Diagnostics.Process.Start(workbook.FileName)
    End Sub

End Module

Published in Validation

Tagged under

Wednesday, 07 September 2011 06:00

Excel Read Data With Formulas

Demo
C# source
VB.Net source

The sample demonstrates how to read data with fomulas

using System.IO;
using System.Windows.Forms;
using System;
using System.Data;
using Spire.Xls;

namespace DataReadWithFormulas
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dataTable = new DataTable();
            using (Stream buffer = WriteFormulas())
            {
                //load
                Workbook workbook = new Workbook();
                workbook.LoadFromStream(buffer);
                //calculate all cells' formula
                workbook.CalculateAllValue();
                //read cells' value to data table
                Worksheet sheet = workbook.Worksheets[0];
                dataTable.Columns.Add("Formulas", typeof(String));
                dataTable.Columns.Add("Results", typeof(Object));
                foreach (CellRange row in sheet["A5:B46"].Rows)
                {
                    String formula = row.Columns[1].Formula;
                    Object value = row.Columns[1].FormulaValue;
                    dataTable.Rows.Add(formula, value);
                }
            }
            using (Form frm1 = new Form())
            {
                DataGrid dataGrid = new DataGrid();
                dataGrid.CaptionVisible = false;
                dataGrid.ReadOnly = true;
                dataGrid.DataSource = dataTable;
                dataGrid.Dock = DockStyle.Fill;

                frm1.Text = "Data Export with Formulas";
                frm1.Width = 480;
                frm1.Height = 360;
                frm1.StartPosition = FormStartPosition.CenterParent;
                frm1.Controls.Add(dataGrid);
                frm1.ShowDialog();
            }
        }

        private static Stream WriteFormulas()
        {
            //Create Excel files
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];//it's the first sheet of the worksheets

            int currentRow = 1;
            string currentFormula = string.Empty;
            sheet.SetColumnWidth(1, 32);//SetColumnWidth(columnIndex,width);
            sheet.SetColumnWidth(2, 16);
            sheet.SetColumnWidth(3, 16);
            sheet.Range[currentRow++, 1].Value = "Examples of formulas :";
            sheet.Range[++currentRow, 1].Value = "Test data:";

            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;

            //test data
            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;

            sheet.Range[++currentRow, 1].Value = "Formulas"; ;
            sheet.Range[currentRow, 2].Value = "Results";
            range = sheet.Range[currentRow, 1, currentRow, 2];

            //range.Value = "Formulas";
            range.Style.Font.IsBold = true;
            range.Style.KnownColor = ExcelColors.LightGreen1;
            range.Style.FillPattern = ExcelPatternType.Solid;
            range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            //str.
            currentFormula = "=\"hello\"";
            sheet.Range[++currentRow, 1].Text = "=\"hello\"";
            sheet.Range[currentRow, 2].Formula = currentFormula;
            sheet.Range[currentRow, 3].Formula = "=\"\u4f60\u597d\"";

            //int.
            currentFormula = "=300";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            // float                        
            currentFormula = "=3389.639421";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //bool.
            currentFormula = "=false";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=1+2+3+4+5-6-7+8-9";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=33*3/4-2+10";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;


            // sheet reference
            currentFormula = "=Sheet1!$B$3";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            // sheet area reference
            currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            // Functions
            currentFormula = "=Count(3,5,8,10,2,34)";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;


            currentFormula = "=NOW()";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;
            sheet.Range[currentRow, 2].Style.NumberFormat = "yyyy-MM-DD";

            currentFormula = "=SECOND(11)";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=MINUTE(12)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=MONTH(9)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=DAY(10)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=TIME(4,5,7)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=DATE(6,4,2)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=RAND()";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=HOUR(12)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=MOD(5,3)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=WEEKDAY(3)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=YEAR(23)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=NOT(true)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=OR(true)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=AND(TRUE)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=VALUE(30)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=LEN(\"world\")";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=MID(\"world\",4,2)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=ROUND(7,3)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=SIGN(4)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=INT(200)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=ABS(-1.21)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=LN(15)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=EXP(20)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=SQRT(40)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=PI()";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=COS(9)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=SIN(45)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=MAX(10,30)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=MIN(5,7)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=AVERAGE(12,45)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=SUM(18,29)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=IF(4,2,2)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            MemoryStream buffer = new MemoryStream();
            workbook.SaveToStream(buffer);
            buffer.Position = 0;
            return buffer;
        }
    }
}

Imports System.IO
Imports System.Windows.Forms
Imports Spire.Xls
Module Module1

    Private Function WriteFormulas() As Stream
        Dim workbook As Workbook = New Workbook()
        Dim sheet As Worksheet = workbook.Worksheets(0)

        Dim currentRow As Integer = 1
        Dim currentFormula As String = String.Empty

        sheet.SetColumnWidth(1, 32)
        sheet.SetColumnWidth(2, 16)
        sheet.SetColumnWidth(3, 16)

        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Value = "Examples of formulas :"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Value = "Test data:"

        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

        'test data
        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

        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Value = "Formulas"

        sheet.Range(currentRow, 2).Value = "Results"
        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
        'str.
        currentFormula = "=""hello"""
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = "=""hello"""
        sheet.Range(currentRow, 2).Formula = currentFormula
        sheet.Range(currentRow, 3).Formula = "=""" & ChrW(20320) & ChrW(22909) + """"

        'int.
        currentFormula = "=300"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        ' float
        currentFormula = "=3389.639421"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        'bool.
        currentFormula = "=false"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=1+2+3+4+5-6-7+8-9"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=33*3/4-2+10"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula


        ' sheet reference
        currentFormula = "=Sheet1!$B$3"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        ' sheet area reference
        currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        ' Functions
        currentFormula = "=Count(3,5,8,10,2,34)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula


        currentFormula = "=NOW()"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula
        sheet.Range(currentRow, 2).Style.NumberFormat = "yyyy-MM-DD"


        currentFormula = "=SECOND(11)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=MINUTE(12)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=MONTH(9)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=DAY(10)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=TIME(4,5,7)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=DATE(6,4,2)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=RAND()"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=HOUR(12)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=MOD(5,3)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=WEEKDAY(3)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=YEAR(23)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=NOT(true)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=OR(true)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=AND(TRUE)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=VALUE(30)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=LEN(""world"")"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=MID(""world"",4,2)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=ROUND(7,3)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=SIGN(4)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=INT(200)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=ABS(-1.21)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=LN(15)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=EXP(20)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=SQRT(40)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=PI()"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=COS(9)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=SIN(45)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=MAX(10,30)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=MIN(5,7)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=AVERAGE(12,45)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=SUM(18,29)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=IF(4,2,2)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        Dim buffer As New MemoryStream()
        workbook.SaveToStream(buffer)
        buffer.Position = 0

        Return buffer
    End Function
    Sub Main()
        Using buffer As Stream = WriteFormulas()
            'load
            Dim workbook As New Workbook()
            workbook.LoadFromStream(buffer)

            'calculate formula
            Dim b3 As [Object] = workbook.CaculateFormulaValue("Sheet1!$B$3")
            Dim c3 As [Object] = workbook.CaculateFormulaValue("Sheet1!$C$3")
            Dim formula As [String] = "Sheet1!$B$3 + Sheet1!$C$3"
            Dim value As [Object] = workbook.CaculateFormulaValue(formula)
            Dim message As [String] = [String].Format("Sheet1!$B$3 = {0}, Sheet1!$C$3 = {1}, {2} = {3}", b3, c3, formula, value)
            MessageBox.Show(message)
        End Using
    End Sub
End Module

Published in Formulas

Tagged under

Wednesday, 07 September 2011 05:39

Calculate With Formula

Demo
C# source
VB.Net source

The sample demonstrates how to calculate formulas

using System;
using System.IO;
using System.Windows.Forms;
using Spire.Xls;

namespace DataCalculateWithFormulas
{
    class Program
    {
        static void Main(string[] args)
        {
            using (Stream buffer = WriteFormulas())
            {
                //load
                Workbook workbook = new Workbook();
                workbook.LoadFromStream(buffer);

                //calculate formula
                Object b3 = workbook.CaculateFormulaValue("=Sheet1!$B$3");
                Object c3 = workbook.CaculateFormulaValue("=Sheet1!$C$3");
                String formula = "=Sheet1!$B$3 + Sheet1!$C$3";
                Object value = workbook.CaculateFormulaValue(formula);
                String message
                    = String.Format("Sheet1!$B$3 = {0}, Sheet1!$C$3 = {1}, {2} = {3}", b3, c3, formula.Substring(1), value);
                MessageBox.Show(message, "Calculate Formulas");
            }
        }
        private static Stream WriteFormulas()
        {
            //Create Excel files

            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];//it's the first sheet of the worksheets

            int currentRow = 1;
            string currentFormula = string.Empty;
            sheet.SetColumnWidth(1, 32);//SetColumnWidth(columnIndex,width);
            sheet.SetColumnWidth(2, 16);
            sheet.SetColumnWidth(3, 16);
            sheet.Range[currentRow++, 1].Value = "Examples of formulas :";
            sheet.Range[++currentRow, 1].Value = "Test data:";

            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;

            //test data
            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;

            MemoryStream buffer = new MemoryStream();
            workbook.SaveToStream(buffer);
            buffer.Position = 0;
            return buffer;
        }
    }
}

Imports System.IO
Imports System.Windows.Forms
Imports Spire.Xls

Module Module1
    Private Function WriteFormulas() As Stream
        Dim workbook As Workbook = New Workbook()
        Dim sheet As Worksheet = workbook.Worksheets(0)

        Dim currentRow As Integer = 1
        Dim currentFormula As String = String.Empty

        sheet.SetColumnWidth(1, 32)
        sheet.SetColumnWidth(2, 16)
        sheet.SetColumnWidth(3, 16)

        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Value = "Examples of formulas :"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Value = "Test data:"

        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

        'test data
        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

        Dim buffer As New MemoryStream()
        workbook.SaveToStream(buffer)
        buffer.Position = 0

        Return buffer
    End Function
    Sub Main()
        Using buffer As Stream = WriteFormulas()
            'load
            Dim workbook As New Workbook()
            workbook.LoadFromStream(buffer)

            'calculate formula
            Dim b3 As Object = workbook.CaculateFormulaValue("=Sheet1!$B$3")
            Dim c3 As Object = workbook.CaculateFormulaValue("=Sheet1!$C$3")
            Dim formula As String = "=Sheet1!$B$3 + Sheet1!$C$3"
            Dim value As Object = workbook.CaculateFormulaValue(formula)
            Dim message As String = _
                String.Format("Sheet1!$B$3 = {0}, Sheet1!$C$3 = {1}, {2} = {3}", b3, c3, formula.Substring(1), value)
            MessageBox.Show(message, "Calculate Formulas")
        End Using
    End Sub
End Module

Published in Formulas

Tagged under

Wednesday, 07 September 2011 04:56

Excel Data Export With Formulas

Demo
C# source
VB.Net source

The sample demonstrates how to export data to datatable with calculating formulas

using System.Data;
using System.IO;
using System.Windows.Forms;

using Spire.Xls;

namespace DataExportWithFormulas
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dataTable = null;
            using (Stream buffer = WriteFormulas())
            {
                //load
                Workbook workbook = new Workbook();

                //load the file and import the data
                workbook.LoadFromStream(buffer);  //import the data of the buffer into the excel file;

                //calculate all cells
                workbook.CalculateAllValue();

                //export
                Worksheet sheet = workbook.Worksheets[0];
                dataTable = sheet.ExportDataTable(sheet["A4:B46"], true, true);
            }

            using (Form frm1 = new Form())
            {
                DataGrid dataGrid = new DataGrid();
                dataGrid.CaptionVisible = false;
                dataGrid.ReadOnly = true;
                dataGrid.DataSource = dataTable;
                dataGrid.Dock = DockStyle.Fill;

                frm1.Text = "Data Export with Formulas";
                frm1.Width = 480;
                frm1.Height = 360;
                frm1.StartPosition = FormStartPosition.CenterParent;
                frm1.Controls.Add(dataGrid);
                frm1.ShowDialog();
            }
        }

        private static Stream WriteFormulas()
        {
            //Create Excel files

            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];//it's the first sheet of the worksheets

            int currentRow = 1;
            string currentFormula = string.Empty;
            sheet.SetColumnWidth(1, 32);//SetColumnWidth(columnIndex,width);
            sheet.SetColumnWidth(2, 16);
            sheet.SetColumnWidth(3, 16);
            sheet.Range[currentRow++, 1].Value = "Examples of formulas :";
            sheet.Range[++currentRow, 1].Value = "Test data:";

            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;

            //test data
            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;

            sheet.Range[++currentRow, 1].Value = "Formulas"; ;
            sheet.Range[currentRow, 2].Value = "Results";
            range = sheet.Range[currentRow, 1, currentRow, 2];

            //range.Value = "Formulas";
            range.Style.Font.IsBold = true;
            range.Style.KnownColor = ExcelColors.LightGreen1;
            range.Style.FillPattern = ExcelPatternType.Solid;
            range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            //str.
            currentFormula = "=\"hello\"";
            sheet.Range[++currentRow, 1].Text = "=\"hello\"";
            sheet.Range[currentRow, 2].Formula = currentFormula;
            sheet.Range[currentRow, 3].Formula = "=\"\u4f60\u597d\"";
            
            //int.
            currentFormula = "=300";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            // float                        
            currentFormula = "=3389.639421";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //bool.
            currentFormula = "=false";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=1+2+3+4+5-6-7+8-9";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=33*3/4-2+10";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;


            // sheet reference
            currentFormula = "=Sheet1!$B$3";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            // sheet area reference
            currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            // Functions
            currentFormula = "=Count(3,5,8,10,2,34)";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;


            currentFormula = "=NOW()";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;
            sheet.Range[currentRow, 2].Style.NumberFormat = "yyyy-MM-DD";

            currentFormula = "=SECOND(11)";
            sheet.Range[++currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=MINUTE(12)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=MONTH(9)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=DAY(10)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=TIME(4,5,7)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=DATE(6,4,2)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=RAND()";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=HOUR(12)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=MOD(5,3)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=WEEKDAY(3)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=YEAR(23)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=NOT(true)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=OR(true)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=AND(TRUE)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=VALUE(30)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=LEN(\"world\")";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=MID(\"world\",4,2)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=ROUND(7,3)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=SIGN(4)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=INT(200)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=ABS(-1.21)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=LN(15)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=EXP(20)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=SQRT(40)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=PI()";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=COS(9)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=SIN(45)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=MAX(10,30)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=MIN(5,7)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=AVERAGE(12,45)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=SUM(18,29)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            currentFormula = "=IF(4,2,2)";
            sheet.Range[currentRow, 1].Text = currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            MemoryStream buffer = new MemoryStream();
            workbook.SaveToStream(buffer);
            buffer.Position = 0;
            return buffer;
        }
    }
}

Imports System.IO
Imports Spire.Xls
Imports System.Windows.Forms

Module Module1

    Private Function WriteFormulas() As Stream
        Dim workbook As Workbook = New Workbook()
        Dim sheet As Worksheet = workbook.Worksheets(0)

        Dim currentRow As Integer = 1
        Dim currentFormula As String = String.Empty

        sheet.SetColumnWidth(1, 32)
        sheet.SetColumnWidth(2, 16)
        sheet.SetColumnWidth(3, 16)

        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Value = "Examples of formulas :"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Value = "Test data:"

        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

        'test data
        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

        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Value = "Formulas"

        sheet.Range(currentRow, 2).Value = "Results"
        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
        'str.
        currentFormula = "=""hello"""
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = "=""hello"""
        sheet.Range(currentRow, 2).Formula = currentFormula
        sheet.Range(currentRow, 3).Formula = "=""" & ChrW(20320) & ChrW(22909) + """"

        'int.
        currentFormula = "=300"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        ' float
        currentFormula = "=3389.639421"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        'bool.
        currentFormula = "=false"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=1+2+3+4+5-6-7+8-9"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=33*3/4-2+10"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula


        ' sheet reference
        currentFormula = "=Sheet1!$B$3"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        ' sheet area reference
        currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        ' Functions
        currentFormula = "=Count(3,5,8,10,2,34)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula


        currentFormula = "=NOW()"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula
        sheet.Range(currentRow, 2).Style.NumberFormat = "yyyy-MM-DD"


        currentFormula = "=SECOND(11)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=MINUTE(12)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=MONTH(9)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=DAY(10)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=TIME(4,5,7)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=DATE(6,4,2)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=RAND()"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=HOUR(12)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=MOD(5,3)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=WEEKDAY(3)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=YEAR(23)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=NOT(true)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=OR(true)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=AND(TRUE)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=VALUE(30)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=LEN(""world"")"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=MID(""world"",4,2)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=ROUND(7,3)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=SIGN(4)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=INT(200)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=ABS(-1.21)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=LN(15)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=EXP(20)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=SQRT(40)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=PI()"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=COS(9)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=SIN(45)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=MAX(10,30)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=MIN(5,7)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=AVERAGE(12,45)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=SUM(18,29)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        currentFormula = "=IF(4,2,2)"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Text = currentFormula
        sheet.Range(currentRow, 2).Formula = currentFormula

        Dim buffer As New MemoryStream()
        workbook.SaveToStream(buffer)
        buffer.Position = 0

        Return buffer
    End Function
    Sub Main()
        Dim dataTable As DataTable
        Using buffer As Stream = WriteFormulas()
            'load
            Dim workbook As New Workbook()
            workbook.LoadFromStream(buffer)

            'calculate all cells
            workbook.CalculateAllValue()

            'export
            Dim sheet As Worksheet = workbook.Worksheets(0)
            dataTable = sheet.ExportDataTable(sheet("A4:B46"), True, True)
        End Using

        Using frm1 As New Form()
            Dim dataGrid As New DataGrid()
            dataGrid.CaptionVisible = False
            dataGrid.ReadOnly = True
            dataGrid.DataSource = dataTable
            dataGrid.Dock = DockStyle.Fill

            frm1.Text = "Data Export with Formulas"
            frm1.Width = 480
            frm1.Height = 360
            frm1.StartPosition = FormStartPosition.CenterParent
            frm1.Controls.Add(dataGrid)
            frm1.ShowDialog()
        End Using
    End Sub
End Module

Published in Formulas

Tagged under

Tuesday, 06 September 2011 08:03

SparkLine Chart

Demo
C# source
VB.Net source

The sample demonstrates how to insert SparkLine into an excel workbook.

using Spire.Xls;
using System.Drawing;

namespace SparkLine
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.Version = ExcelVersion.Version2010;
            workbook.CreateEmptySheets(1);

            Worksheet sheet = workbook.Worksheets[0];

            //Country
            sheet.Range["A1"].Value = "Country";
            sheet.Range["A2"].Value = "Cuba";
            sheet.Range["A3"].Value = "Mexico";
            sheet.Range["A4"].Value = "France";
            sheet.Range["A5"].Value = "German";

            //Jun
            sheet.Range["B1"].Value = "Jun";
            sheet.Range["B2"].NumberValue = 0.23;
            sheet.Range["B3"].NumberValue = 0.37;
            sheet.Range["B4"].NumberValue = 0.15;
            sheet.Range["B5"].NumberValue = 0.25;

            //Jul
            sheet.Range["C1"].Value = "Jul";
            sheet.Range["C2"].NumberValue = 0.1;
            sheet.Range["C3"].NumberValue = 0.35;
            sheet.Range["C4"].NumberValue = 0.22;
            sheet.Range["C5"].NumberValue = 0.33;


            //Aug
            sheet.Range["D1"].Value = "Aug";
            sheet.Range["D2"].NumberValue = 0.14;
            sheet.Range["D3"].NumberValue = 0.36;
            sheet.Range["D4"].NumberValue = 0.25;
            sheet.Range["D5"].NumberValue = 0.25;


            //Sep
            sheet.Range["E1"].Value = "Sep";
            sheet.Range["E2"].NumberValue = 0.17;
            sheet.Range["E3"].NumberValue = 0.28;
            sheet.Range["E4"].NumberValue = 0.39;
            sheet.Range["E5"].NumberValue = 0.32;

            //Style
            sheet.Range["A1:E1"].Style.Font.IsBold = true;
            sheet.Range["A2:E2"].Style.KnownColor = ExcelColors.LightYellow;
            sheet.Range["A3:E3"].Style.KnownColor = ExcelColors.LightGreen1;
            sheet.Range["A4:E4"].Style.KnownColor = ExcelColors.LightOrange;
            sheet.Range["A5:E5"].Style.KnownColor = ExcelColors.LightTurquoise;

            //Border
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

            sheet.Range["B2:D5"].Style.NumberFormatIndex = 9;

            SparklineGroup sparklineGroup
                = sheet.SparklineGroups.AddGroup(SparklineType.Line);
            SparklineCollection sparklines = sparklineGroup.Add();
            sparklines.Add(sheet["B2:E2"], sheet["F2"]);
            sparklines.Add(sheet["B3:E3"], sheet["F3"]);
            sparklines.Add(sheet["B4:E4"], sheet["F4"]);
            sparklines.Add(sheet["B5:E5"], sheet["F5"]);

            workbook.SaveToFile("Sample.xlsx");
            System.Diagnostics.Process.Start(workbook.FileName);

        }
    }
}

Imports Spire.Xls
Imports System.Drawing
Module Module1
    Sub Main()
        Dim workbook As New Workbook()
        workbook.Version = ExcelVersion.Version2010
        workbook.CreateEmptySheets(1)

        Dim sheet As Worksheet = workbook.Worksheets(0)

        'Country
        sheet.Range("A1").Value = "Country"
        sheet.Range("A2").Value = "Cuba"
        sheet.Range("A3").Value = "Mexico"
        sheet.Range("A4").Value = "France"
        sheet.Range("A5").Value = "German"

        'Jun
        sheet.Range("B1").Value = "Jun"
        sheet.Range("B2").NumberValue = 0.23
        sheet.Range("B3").NumberValue = 0.37
        sheet.Range("B4").NumberValue = 0.15
        sheet.Range("B5").NumberValue = 0.25

        'Jul
        sheet.Range("C1").Value = "Jul"
        sheet.Range("C2").NumberValue = 0.1
        sheet.Range("C3").NumberValue = 0.35
        sheet.Range("C4").NumberValue = 0.22
        sheet.Range("C5").NumberValue = 0.33


        'Aug
        sheet.Range("D1").Value = "Aug"
        sheet.Range("D2").NumberValue = 0.14
        sheet.Range("D3").NumberValue = 0.36
        sheet.Range("D4").NumberValue = 0.25
        sheet.Range("D5").NumberValue = 0.25


        'Sep
        sheet.Range("E1").Value = "Sep"
        sheet.Range("E2").NumberValue = 0.17
        sheet.Range("E3").NumberValue = 0.28
        sheet.Range("E4").NumberValue = 0.39
        sheet.Range("E5").NumberValue = 0.32

        'Style
        sheet.Range("A1:E1").Style.Font.IsBold = True
        sheet.Range("A2:E2").Style.KnownColor = ExcelColors.LightYellow
        sheet.Range("A3:E3").Style.KnownColor = ExcelColors.LightGreen1
        sheet.Range("A4:E4").Style.KnownColor = ExcelColors.LightOrange
        sheet.Range("A5:E5").Style.KnownColor = ExcelColors.LightTurquoise

        'Border
        sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
        sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
        sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
        sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 128)
        sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin

        sheet.Range("B2:D5").Style.NumberFormatIndex = 9

        Dim sparklineGroup As SparklineGroup = sheet.SparklineGroups.AddGroup(SparklineType.Line)
        Dim sparklines As SparklineCollection = sparklineGroup.Add()
        sparklines.Add(sheet("B2:E2"), sheet("F2"))
        sparklines.Add(sheet("B3:E3"), sheet("F3"))
        sparklines.Add(sheet("B4:E4"), sheet("F4"))
        sparklines.Add(sheet("B5:E5"), sheet("F5"))

        workbook.SaveToFile("Sample.xlsx")
        System.Diagnostics.Process.Start(workbook.FileName)

    End Sub
End Module

Published in Charts

Tagged under

Tuesday, 06 September 2011 06:39

Excel Cell Names

Demo
C# source
VB.Net source

The sample demonstrates how to define named cell references or ranges in excel workbook.

Download MiscDataTable.xls

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

namespace Names
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();

            workbook.LoadFromFile(@"..\..\MiscDataTable.xls");
            Worksheet sheet = workbook.Worksheets[0];
            sheet.InsertRow(1, 2);
            sheet.Rows[0].RowHeight = 16;

            //Style
            sheet.Range["A1:B1"].Style.Font.IsBold = true;
            sheet.Range["A3:E3"].Style.KnownColor = ExcelColors.LightOrange;

            //set the color of le event/odd row color
            int index = 4;
            while (sheet.Range[String.Format("A{0}", index)].HasString)
            {
                sheet.Range[String.Format("A{0}:E{0}", index)].Style.KnownColor
                    = index % 2 == 0 ? ExcelColors.PaleBlue : ExcelColors.LightTurquoise;
                index++;
            }

            //define named cell ranges
            sheet.Names.Add("Countries", sheet[String.Format("A4:A{0}", index - 1)]);
            sheet.Names.Add("Cities", sheet[String.Format("B4:B{0}", index - 1)]);
            sheet.Names.Add("Continents", sheet[String.Format("C4:C{0}", index - 1)]);
            sheet.Names.Add("Area", sheet[String.Format("D4:D{0}", index - 1)]);
            sheet.Names.Add("Population", sheet[String.Format("E4:E{0}", index - 1)]);
            sheet.Names.Add("NumberOfCountries", sheet[String.Format("A{0}", index)]);

            //references of names
            sheet.Range["A1"].Value = "Number of Countries:";
            sheet.Range["B1"].Formula = "=NumberOfCountries";
            sheet[String.Format("A{0}", index)].Formula = "=COUNTA(Countries)";
            sheet[String.Format("D{0}", index)].Formula = "=SUM(Area)";
            sheet[String.Format("E{0}", index)].Formula = "=SUM(Population)";

            //style
            sheet.Rows[index - 1].RowHeight = 16;
            String range = String.Format("A{0}:E{0}", index);
            sheet.Range[range].Style.Font.IsBold = true;
            sheet.Range[range].Style.KnownColor = ExcelColors.LightOrange;
            sheet.Range[range].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 0);
            sheet.Range[range].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thick;
            sheet.Range[range].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 0);
            sheet.Range[range].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range[range].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 0);
            sheet.Range[range].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range[range].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 0);
            sheet.Range[range].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            workbook.SaveToFile("Sample.xls");
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Imports Spire.Xls
Imports System.Drawing

Module Module1
    Sub Main()
        Dim workbook As New Spire.Xls.Workbook()

        workbook.LoadFromFile("..\..\MiscDataTable.xls")
        Dim sheet As Worksheet = workbook.Worksheets(0)
        sheet.InsertRow(1, 2)
        sheet.Rows(0).RowHeight = 16

        'Style
        sheet.Range("A1:B1").Style.Font.IsBold = True
        sheet.Range("A3:E3").Style.KnownColor = ExcelColors.LightOrange

        Dim index As Integer = 4
        While sheet.Range([String].Format("A{0}", index)).HasString
            sheet.Range([String].Format("A{0}:E{0}", index)).Style.KnownColor _
                = If(index Mod 2 = 0, ExcelColors.PaleBlue, ExcelColors.LightTurquoise)
            index += 1
        End While

        'define named cell ranges
        sheet.Names.Add("Countries", sheet([String].Format("A4:A{0}", index - 1)))
        sheet.Names.Add("Cities", sheet([String].Format("B4:B{0}", index - 1)))
        sheet.Names.Add("Continents", sheet([String].Format("C4:C{0}", index - 1)))
        sheet.Names.Add("Area", sheet([String].Format("D4:D{0}", index - 1)))
        sheet.Names.Add("Population", sheet([String].Format("E4:E{0}", index - 1)))
        sheet.Names.Add("NumberOfCountries", sheet([String].Format("A{0}", index)))

        'references of names
        sheet.Range("A1").Value = "Number of Countries:"
        sheet.Range("B1").Formula = "=NumberOfCountries"
        sheet([String].Format("A{0}", index)).Formula = "=COUNTA(Countries)"
        sheet([String].Format("D{0}", index)).Formula = "=SUM(Area)"
        sheet([String].Format("E{0}", index)).Formula = "=SUM(Population)"

        'style
        sheet.Rows(index - 1).RowHeight = 16
        Dim range As [String] = [String].Format("A{0}:E{0}", index)
        sheet.Range(range).Style.Font.IsBold = True
        sheet.Range(range).Style.KnownColor = ExcelColors.LightOrange
        sheet.Range(range).Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 0)
        sheet.Range(range).Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thick
        sheet.Range(range).Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 0)
        sheet.Range(range).Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
        sheet.Range(range).Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 0)
        sheet.Range(range).Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
        sheet.Range(range).Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 0)
        sheet.Range(range).Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin

        workbook.SaveToFile("Sample.xls")
        System.Diagnostics.Process.Start(workbook.FileName)
    End Sub

End Module

Published in Formulas

Tagged under

Wednesday, 17 August 2011 06:53

Convert Specific Worksheet Cells to Image

This section aims at providing a detail solution for developers on how to convert specific worksheet cells to image via this .NET Excel component Spire.XLS for .NET in C#, VB.NET. This Excel library helps us quickly convert certain excel cells to different image formats such as jpeg, png, bmp, tiff, gif, ico, emf, exif etc.

When we convert worksheet to image, Spire.XLS for .NET provides us a method: Spire.Xls.Worksheet.SaveToImage(int firstRow, int firstColumn, int lastRow, int lastColumn); As we see, there are four parameters passed in this method. These four parameters determine the certain range of cells. After deciding the cell range, we can successfully convert cells to image. Now, let us see the whole task step by step.

Step 1: Create a template Excel Workbook in MS Excel

I create a new Excel file in MS Excel and add some data which have different formats in the first sheet, here is a screenshot of created file.

Excel to Image

Step 2: Download and Install Spire.XLS for .NET

Spire.XLS for .NET is an Excel Api which enables users to quickly generate, read, edit and manipulate Excel files on .NET Platform. Here you can download Spire.XLS for .NET and install it on your development computer. After installing it, Spire.XLS for .NET will run in evaluation mode which is the same when you install other Spire components. This evaluation mode has no time limit.

Step 3: Create a project and Add References

We can create a new project of Console Application either in C# or VB.NET. I create in C#, but we can choose VB.NET too.

In this project, we need to add references in our project. Apart from adding System.Drawing, we will use Spire.XLS for .NET, so we also have to add Spire.Xls.dll, Spire.Common.dll and Spire.License.dll in our download Bin folder of Spire.Xls. Here we can see the default path: "..\Spire.XLS\Bin\NET4.0\Spire.XLS.dll"

Step 4: Convert Specific Worksheet Cells to Image

In this step, first we can initialize a new object of the class Spire.Xls.Workbook, then, load the template Excel file. Since I want to convert cells in the first sheet to image, I need get the first worksheet data in Excel file. You also can get other worksheet data. Finally specify cell range and save the cells in the range as image file. Spire.XLS for .NET supports 12 image formats: Bmp, Emf, Equals, Exif, Gif, Icon, Jpeg, MemoryBmp, Png, ReferenceEquals, Tiff and Wmf.

[C#]

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //Initialize a new Workbook object
            Workbook workbook = new Workbook();
            //Open Template Excel file
            workbook.LoadFromFile(@"..\excel to image.xlsx");
            //Get the first wirksheet in Excel file
            Worksheet sheet = workbook.Worksheets[0];
            //Specify Cell Ranges and Save to certain Image formats
            sheet.SaveToImage(1, 1, 6, 3).Save("image1.png",ImageFormat.Png);
            sheet.SaveToImage(7, 1, 12, 3).Save("image2.jpeg", ImageFormat.Jpeg);
            sheet.SaveToImage(13, 1, 18, 3).Save("image3.bmp", ImageFormat.Bmp);
        }
    }
}

[VB.NET]

Imports System.Drawing
Imports System.Drawing.Imaging
Imports Spire.Xls
Imports Spire.Xls.Converter

Namespace ConsoleApplication1
	Class Program
		Private Shared Sub Main(args As String())
			'Initialize a new Workbook object
			Dim workbook As New Workbook()
			'Open Template Excel file
			workbook.LoadFromFile("..\excel to image.xlsx")
			'Get the first wirksheet in Excel file
			Dim sheet As Worksheet = workbook.Worksheets(0)
			'Specify Cell Ranges and Save to certain Image formats
			sheet.SaveToImage(1, 1, 6, 3).Save("image1.png", ImageFormat.Png)
			sheet.SaveToImage(7, 1, 12, 3).Save("image2.jpeg", ImageFormat.Jpeg)
			sheet.SaveToImage(13, 1, 18, 3).Save("image3.bmp", ImageFormat.Bmp)
		End Sub
	End Class
End Namespace

Result Task

After executing above code, cells in the first worksheet named "Sheet1" has been converted to three images. They are named "image1.png", "image2.jpeg" and "image3.bmp". You can see them as below:

Excel to Image

In this section, I have introduced how we can convert specific cells to image by using Spire.XLS for .NET. I sincerely hope it can help you and give you some insight.

Spire.XLS for .NET offers fast speed, high efficiency and reliability to satisfy development application requirements. As you see above, the results do show that Spire.XLS for .NET benefit customers from years of research.

We appreciate any kind of queries, comments and suggestions at E-iceblue Forum. We promise a quick reply within minutes or hours as we can.

Published in Conversion

Tagged under

xls net Conversion

Wednesday, 17 August 2011 06:16

How to Replace Text in a Word Template with HTML

Question:

I want to replace text in a word template with HTML. I know how to append HTML to last paragraph but this paragraph is in the middle of word document.

Answer:

Below is a VB project demo about text replacing with multiple paragraphs and html, hope it will help you.

TextReplaceWithMultipleParagraph2.zip

If this doesn't answer your question, please do not hesitate to add your question in our forum. Our technical support will answer soon (we promise response within 1 business day)!

Published in Text

Page 237 of 242