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
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:
|
More Blogs from Lacy
- Easy Way to Save Excel File as CSV with C#, VB.NET
- Create Excel Line Chart with C#, VB.NET
- Extract Text from Word File with C#, VB.NET
- Extract Images from Word File with C#, VB.NET
- Best Solution to Create Excel Pivot Table with C#, VB.NET
- Split One PDF Document to Multiple Files with C#, VB.NET
- Three Steps to Save Html File as Word Document with C#, VB.NET
- Page PDF Document by PDF Viewer with C#,VB.NET
- Quickly Convert Image to PDF with C#,VB.NET
- Easiest Method to Save RTF File as PDF Document with C#, VB.NET
- Easily Save .Txt File as PDF Document with C#/VB.NET
- Export Data and Pictures into a PDF Table and Set Table Format with C#, VB.NET
  More ...
|
|
|