Congratulations to all monthly winners of May 2013 !!! They have won INR 2900 cash and INR 27497 worth prize.
DotNetFunda.Com Logo
Twitter TwitterLinkedIn
YouTubeGoogle
 Online : 3546 |  Welcome, Guest!   Register  Login
 Home > Blogs > C# > Export Data with Formulas with C#, VB.NET ...
Lacy

Export Data with Formulas with C#, VB.NET

 Blog author: Lacy | Posted on: 5/24/2012 | Category: C# Blogs | Views: 885 | Status: [Member] | Points: 75 | Alert Moderator   
Ads

Data is the main role in excel and export data is really indispensably used in excel. But sometimes, we have to export data with formulas in excel. Few days ago, one of my friends encountered a scenario that he had to place the formula in a column in excel. In the scenario, he had no choice but to create a sheet of Bidding price and export some data along with quantity and two empty columns. In the meantime, he had to place the formula in one of them, when he asked me, I introduce a method to him and quickly resolved this problem. In many cases, we have to with formulas export data in excel. Today, I will share with you how to export data with formulas in Excel with C#, VB.NET.
 
Necessary Tools: Spire.XLS and Visual Studio. 


Procedure

Step1. Create a new project.

Create a new project in Visual Studio, set .NET Framework 4 as the Target framework and then, add System. Data, System.Windows.Form and Spire.Xls as references.

C# Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.IO;
using Spire.Xls;
 
VB.NET Code:
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data
Imports System.Windows.Forms
Imports System.IO
Imports Spire.Xls

Step2.Export Data with Formulas.
First, load a new workbook.

C# Code:
        //load
        Workbook workbook = new Workbook();

VB.NET Code:       
          'load
          Dim workbook As New Workbook()

Second, load a file and import the data

C# Code:
               //load the file and import the data
             workbook.LoadFromStream(buffer);  
            //import the data of the buffer into the excel file;
              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();
            }
VB.NET Code:
          'load the file and import the data
         workbook.LoadFromStream(buffer) );
        'import the data of the buffer into the excel file;
         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
Third, calculate all cells and write formulas.
C# Code:
                //calculate all cells
             workbook.CalculateAllValue();
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;
VB.NET Code

         'calculate all cells
        workbook.CalculateAllValue()
    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
Step3. Save the project.

C# Code:

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


VB.NET Code:      

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

Preview
           




Found interesting? Add this to:


About Keating Arly

Experience:1 year(s)
Home page:http://www.e-iceblue.com
Member since:Tuesday, March 27, 2012
Level:Starter
Status: [Member]
Biography:
>> Write Response - Respond to this post and get points

More Blogs

About Us | Contact Us | The Team | Advertise | Software Development | Write for us | Testimonials | Privacy Policy | Terms of Use | Link Exchange | Members | Go Top
General Notice: If you find plagiarised (copied) contents on this page, please let us know the original source along with your correct email id (to communicate) for further action.
Copyright © DotNetFunda.Com. All Rights Reserved. Copying or mimicking the site design and layout is prohibited. Logos, company names used here if any are only for reference purposes and they may be respective owner's right or trademarks. | 6/18/2013 5:50:56 PM