Thursday, 28 July 2011 05:39

Read Excel Formulas in C#, VB.NET

Introduction

After we wrote formulas for Excel, the values would be displayed in specified cells. It is possible that the formulas are wrongly used or lost because there are too many formulas. Therefore, sometimes, we need to read the formula for one cell to ensure if the formula is right. Well then, how to read Excel formula?

Read Formula in Microsoft Excel

Through Formulas tab in Excel 2007, we can find one button named Show Formulas. Select the cells which we want to read formulas, and then click the button. After that, we can see the formulas instead of values.

Besides using the button, we can also use keyboard shortcut to read formulas in Excel. Select the cells where we want to show the formulas, and then press Ctrl + ` to read.

Read Excel Formulas via Spire.XLS

Spire.XLS presents you an easy way to read formula in the worksheet. You can get the formula through the value you provide, while you should specify where the value is. In the demo, we load a workbook from file named "ReadFormulaSample.xls" which has a formula written in the sheet["B5"], we can read the formula through its value in sheet["C5"]. In this example, in order to view, we read the formula to the sheet["D5"].

The following code displays the method to read formulas for cells with C#/VB.NET:

[C#]
using Spire.Xls;

namespace ReadFormula
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a new workbook
            Workbook workbook = new Workbook();

            //Load a workbook from file
            workbook.LoadFromFile("ReadFormulaSample.xls");
           
            //Initialize the worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Read the formula
            sheet.Range["D5"].Text = sheet.Range["C5"].Formula;

            //Save the file
            workbook.SaveToFile("sample.xls",ExcelVersion.Version97to2003);

            //Launch the file
            System.Diagnostics.Process.Start("Sample.xls");
        }
    }
[VB.NET]
Imports Spire.Xls

Module Module1

    Sub Main()
        'Create a new workbook
        Dim workbook As New Workbook()

        'Load a workbook from file
        workbook.LoadFromFile("ReadFormulaSample.xls")

        'Initialize the worksheet
        Dim sheet As Worksheet = workbook.Worksheets(0)

        'Read the formula
        sheet.Range("D5").Text = sheet.Range("C5").Formula

        'Save doc file.
        workbook.SaveToFile("Sample.xls",ExcelVersion.Version97to2003)

        'Launching the MS Word file.
        System.Diagnostics.Process.Start("Sample.xls")
    End Sub
End Module

After running the demo, you may find a formula appear in the worksheet you specify:

Read Formula

Thursday, 28 July 2011 05:23

Unlock Sheet in Excel File in C#, VB.NET

This section aims at providing developers a solution to unlock sheet in Excel workbook with C#, VB.NET via this Excel library Spire.XLS for .NET.

Spire.XLS for .NET enables you to unlock any sheet in Excel file only by one line of key code: Spire.Xls.Worksheet.Unprotect(string password); Besides, as an MS Excel component, Spire.XLS for .NET also enables you to create, read and handle Excel files with fast speed. Below is an Excel file with protected worksheets which will be unlocked in my task.

Unlock Excel Worksheet

Since you will use Spire.XLS for .NET, you have to download Spire.XLS for .NET and install it on system. When you create your project, please do not forget to add Spire.XLS.dll as reference from Bin folder. The default path is "..\Spire.XLS\Bin\NET4.0\Spire.XLS.dll". Please note that Spire.XLS for .NET supports .NET Framework 2.0 and above. Here is the whole code for unlocking Excel sheet:

[C#]
namespace UnlockExcelSheet
{
    class Program
    {
        static void Main(string[] args)
        {
            //initialize an instance of Workbook
            Workbook workbook = new Workbook();
            //Load an Excel file with protected worksheet
            workbook.LoadFromFile(@"..\Unlock Excel Worksheet.xlsx");
            //get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];
            //Unprotect worksheet
            sheet.Unprotect("iceblue");
            //Save the file
            workbook.SaveToFile("Sample.xlsx",ExcelVersion.Version2010);
            //Launch the file
            System.Diagnostics.Process.Start("Sample.xlsx");
        }
    }
}
          
[VB.NET]
Namespace UnlockExcelSheet
	Class Program
		Private Shared Sub Main(args As String())
			'initialize an instance of Workbook
			Dim workbook As New Workbook()
			'Load an Excel file with protected worksheet
			workbook.LoadFromFile("..\Unlock Excel Worksheet.xlsx")
			'get the first worksheet
			Dim sheet As Worksheet = workbook.Worksheets(0)
			'Unprotect worksheet
			sheet.Unprotect("iceblue")
			'Save the file
			workbook.SaveToFile("Sample.xlsx",ExcelVersion.Version2010)
			'Launch the file
			System.Diagnostics.Process.Start("Sample.xlsx")
		End Sub
	End Class
End Namespace
          

After executing above code, you can see that the protected worksheet in the original Excel file has been unlocked, we can edit it also. Please see following image.

Unlock Excel Worksheet

In this section, I have introduced the solution to unlock any sheet in Excel file via Spire.XLS for .NET. I hope it can help you. If you have any questions, feedbacks and advice, you can put them on E-iceblue Forum. We will promise a prompt reply.

Wednesday, 27 July 2011 03:30

Load/Save Excel VBA in C#, VB.NET

By running VBA within the Office applications, developers/programmers can build customized solutions and programs to enhance the capabilities of those applications. The VBA function of Excel is very powerful. Below I will show you how to use VBA by Spire.XLS.

VBA is the acronym for VB.NET for Applications. It is an implementation of Microsoft's event-driven programming language VB.NET 6 and its associated integrated development environment (IDE), which are built into most Microsoft Office applications. VBA is closely related to VB.NET and uses the VB.NET Runtime Library, but can normally only run code within a host application rather than as a standalone program. It can be used to control one application from another via OLE Automation.

Spire.XLS for .NET is a professional Excel .NET component that can be linked into any type of .NET 2.0, 3.5 or 4.0 projects, either ASP.NET web sites or Windows Forms application. Spire.XLS for .NET offers a combination of APIs and GUI controls for speeding up Excel programming in .NET platform-create new Excel documents from scratch, edit existing Excel documents and convert Excel files. At the same time, Spire.XLS supports VBA and it can load/Save Excel VBA.

Here comes to the steps:

  • Write a template with VBA program with which you can execute your work in Excel.
  • Create another workbook to load the VBA template.

In this demo, it generates a new worksheet named "test" with the VBA template we provide.

Please check the codes as below:

[C#]
using Spire.Xls;

namespace NumberFormat
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a workbook
            Workbook workbook = new Workbook();

            //Initailize worksheet
            workbook.LoadFromFile("VBASample.xls");
            Worksheet sheet = workbook.Worksheets[0];

            //VBA function
            sheet.Range["A1"].Text = "test";

            //Save the file
            workbook.SaveToFile("Sample.xls",ExcelVersion.Version97to2003);

            //Launch the file
            System.Diagnostics.Process.Start("Sample.xls");
        }
    }
}
[VB.NET]
Imports Spire.Xls

Module Module1

    Sub Main()
        'Create a workbook
        Dim workbook As New Workbook()

        'Initailize worksheet
        workbook.LoadFromFile("VBASample.xls")
        Dim sheet As Worksheet = workbook.Worksheets(0)

        'VBA function
        sheet.Range("A1").Text = "test"

        'Save doc file.
        workbook.SaveToFile("Sample.xls",ExcelVersion.Version97to2003)

        'Launching the MS Word file.
        System.Diagnostics.Process.Start("Sample.xls")
    End Sub
End Module
Tuesday, 26 July 2011 07:05

How to Use Mail Merge in Silverlight

Introduction

Mail merge is often used to print reports in bulk, such as financial statement, payroll or transcript. And the merged documents can be sent by E-mail.

In this article, I will show one way to generate mail merged reports via Spire.Doc.

Steps

We need to finish the following 3 steps to generate our report.

  • Create a mail merge template.
  • Load data from database.
  • Merge data into template and save.

Every step includes several sub-steps and in #2 and #3 we need to write some code.

Create mail merge template

A template is a reusable document. It renders the pattern of our report. We could modify it to change our report without any modification of code.

Note: in this section, all tables mean DataTable instance, not physical table in database.

First, We can create the template in MS Word or by other program. It is the template we need to create. Data will be filled in the red party.

Mail Merge Fax

Second, Insert mail-merge-field as placeholder into the red-block. There are three types of mail-merge-field:

  • GeneralField is a general Word mail-merge-field. It is real data field and our data will be filled in it during merge process.
  • TableField is assistant mail-merge-field and used as a container of multiple related GeneralFields and other TableFields. So it is not data placeholder and no data will be filled in. It is composed of two special mail-merge-fields: TableStart:TableName and TableEnd:TableName. During merge process, the data of related GeneralFields contained by one same TableField will be from one same data table.
  • GroupField is assistant mail-merge-field too. It can contain multiple related GeneralFields and TableFields. It is composed of two special mail-merge-fields: GroupStart:GroupName and GroupEnd:GroupName. During merge process, all Word document elements included in a GroupField will be copied. One row in data table has one copy and data in the row will be filled into the fields in the copy. If the row has sub data table, the data in sub data table will be filled into the fields included in the corresponding TableField. If the sub data table has multiple data rows, the corresponding TableField will be copied and filled too. We need to insert a mail-merge-field named GroupStart:Order in the top of the template body and insert a mail-merge-field named GroupEnd:OrderM in the bottom of the template body.

In this Silverlight application we only use GeneralField. As the image shows below. Insert mail-merge-field as placeholder into the red-block.

Mail Merge Fax

Load Data

Spire.Doc provides merge data from various kind of datasources. This program merge data from a string array which is filled by user.

Mail Merge Fax

String[] fieldNames
                = new String[] { "Contact Name", "Fax", "From", "Date", "Subject", "Content" };
            DateTime faxDate
                = this.datePickerFaxDate.SelectedDate.HasValue ? 
                    this.datePickerFaxDate.SelectedDate.Value : DateTime.Now;
            String[] fieldValues
                = new String[] 
                {
                    this.textBoxTo.Text,
                    this.textBoxFax.Text,
                    this.textBoxFrom.Text,
                    faxDate.ToShortDateString(),
                    this.textBoxSubject.Text,
                    this.textBoxContent.Text
                };

Merge data into template and save

In this section, we need to write some code to call Spire.Doc to merge our data and template.

this.documentTemplate.MailMerge.Execute(fieldNames, fieldValues);

            bool? result = this.saveFileDialog.ShowDialog();
            if (result.HasValue && result.Value)
            {
                using (Stream stream = this.saveFileDialog.OpenFile())
                {
                    //this.documentTemplate.SaveToStream(stream, FileFormat.Doc);
                    this.documentTemplate.SaveToFile(stream, FileFormat.Doc);
                }
            }

The Result

Mail Merge Fax

Tuesday, 26 July 2011 03:59

Import Data from Excel to Datatable

Excel import or export is always a hot issue for developers. When you go across technical forums, these forums are glutted with solutions, which make developers hard to find a really useful one. This section will introduce an effective way to easily and quickly import data from Excel to datatable via a .NET Excel component in C#, VB.NET.

By using Spire.XLS for .NET, you only need two lines of core code to finish the import or export data from excel to datatable. One is to import an Excel file by the Load method: Workbook.LoadFromFile(string fileName); the other is to get the datasource displayed in datagridview by calling the method: DataTable Xlsworksheet.ExportDataTable().

View the effective screenshot of Excel to datatable task as below firstly:

excel to datatable

Here comes to the steps:

  • Download Spire.XLS for .NET (or Spire.Office for .NET) and install it on your system.
  • Add Spire.XLS.dll as reference in the downloaded Bin folder thought the below path: "..\Spire.XLS\Bin\NET4.0\ Spire.XLS.dll".
  • Check the main codes as below:
[C#]
//Create a new workbook
Workbook workbook = new Workbook();       
//Load a file and imports its data
workbook.LoadFromFile(@”..\FandH.xlsx”);          
//Initialize worksheet
Worksheet sheet = workbook.Worksheets[0];
// get the data source that the grid is displaying data for
this.dataGridView1.DataSource = sheet.ExportDataTable();
[VB.NET]
Dim workbook As Workbook = New Workbook
workbook.LoadFromFile(..\ FandH.xlsx)
Dim sheet As Worksheet = workbook.Worksheets(0)
Me.dataGridView1.DataSource = sheet.ExportDataTable

Spire.XLS for .NET is a professional Excel component which enables developers/programmers to fast generate, read, write and modify Excel document for .NET. It supports C#, VB.NET, ASP.NET, ASP.NET MVC.

Monday, 25 July 2011 04:02

Create Excel Radar Chart in C#, VB.NET

Radar chart is one kind of excel charts. It is also known as spider chart, star chart, cobweb chart, web chart, star plot, irregular polygon, polar chart, or kiviat diagram which is a graphical method of displaying multivariate data in the form of a two-dimensional chart of three or more quantitative variables represented on axes starting from the same point.

Radar chart is very useful to display multivariate observations with an arbitrary number of variables. Each star represents a single observation. Typically, radar charts are generated in a multi-plot format with many stars on each page and each star represents one observation.

How to Use C# Create Excel Radar Chart

Spire.XLS allows user to create kinds of charts in Excel including Radar Chart. It is easy to create Excel Radar chart with C# via Spire.XLS as in MS Excel. The whole process is almost the same such as write chart data, set region/position of chart, Write chart title, chart data information, etc. The difference is we need fill these data in C# application with codes around. The following is a sample for C# code used to create Excel Radar charts. Download Spire.XLS (or Spire.Office) with .NET framework 2.0 (or above) together and use the code to create Excel Radar Chart right now.

[C#]
		private void btnRun_Click(object sender, System.EventArgs e)
		{
			Workbook workbook = new Workbook();

			//Initailize worksheet
			workbook.CreateEmptySheets(1);
			Worksheet sheet = workbook.Worksheets[0];
			sheet.Name = "Chart data";
			sheet.GridLinesVisible = false;

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

			//Set position of chart
			chart.LeftColumn = 1;
			chart.TopRow = 6;
			chart.RightColumn = 11;
			chart.BottomRow = 29;

			//Set region of chart data
			chart.DataRange = sheet.Range["A1:C5"];
			chart.SeriesDataFromRange = false;

			if (checkBox1.Checked)
			{
				chart.ChartType =  ExcelChartType.RadarMarkers;
			}
			else
			{
				chart.ChartType = ExcelChartType.Radar;
			}

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

			chart.PlotArea.Fill.Visible = false;

			chart.Legend.Position = LegendPositionType.Corner;
			workbook.SaveToFile("Sample.xls",ExcelVersion.Version97to2003);
			ExcelDocViewer(workbook.FileName);
		}

		private void CreateChartData(Worksheet sheet)
		{
			//Product
			sheet.Range["A1"].Value = "Product";
			sheet.Range["A2"].Value = "Bikes";
			sheet.Range["A3"].Value = "Cars";
			sheet.Range["A4"].Value = "Trucks";
			sheet.Range["A5"].Value = "Buses";

			//Paris
			sheet.Range["B1"].Value = "Paris";
			sheet.Range["B2"].NumberValue = 4000;
			sheet.Range["B3"].NumberValue = 23000;
			sheet.Range["B4"].NumberValue = 4000;
			sheet.Range["B5"].NumberValue = 30000;

			//New York
			sheet.Range["C1"].Value = "New York";
			sheet.Range["C2"].NumberValue = 30000;
			sheet.Range["C3"].NumberValue = 7600;
			sheet.Range["C4"].NumberValue = 18000;
			sheet.Range["C5"].NumberValue = 8000;

			//Style
			sheet.Range["A1:C1"].Style.Font.IsBold = true;
			sheet.Range["A2:C2"].Style.KnownColor = ExcelColors.LightYellow;
			sheet.Range["A3:C3"].Style.KnownColor = ExcelColors.LightGreen1;
			sheet.Range["A4:C4"].Style.KnownColor = ExcelColors.LightOrange;
			sheet.Range["A5:C5"].Style.KnownColor = ExcelColors.LightTurquoise;

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

			sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";
		}

After running your application with the code above, you will find an Excel Radar Chart created.

Generate Excel Radar Chart

Friday, 22 July 2011 06:19

Create Excel Line Chart in C#, VB.NET

Line chart is a type of graph, which displays information as a series of data points connected by straight line segments. It is a basic type of chart common in many fields. It is an extension of a scatter graph, and is created by connecting a series of points that represent individual measurements with line segments. A line chart is often used to visualize a trend in data over intervals of time – a time series – thus the line is often drawn chronologically.

Spire.XLS for .NET allows user to create kinds of charts in Excel including Line Chart. Use C# Create Excel Line chart could be as easy as in Microsoft Excel. The whole process is almost the same such as write chart data, set region/position of chart, Write chart title, chart data information, etc. The difference is we need fill these data in C# application with codes around. Below is a sample for C# code used to create Excel line charts. Download Spire.XLS (or Spire.Office) with .NET framework 2.0 (or above) together and use the code to create Excel Line Chart right now.

[C#]
using Spire.Xls;
using Spire.Xls.Charts;

namespace Spire.Xls.Sample
{
	public class Form1 : System.Windows.Forms.Form
	{
		static void Main()
		{
			Application.Run(new Form1());
		}

		private void btnRun_Click(object sender, System.EventArgs e)
		{
			Workbook workbook = new Workbook();

			//Initailize worksheet
			workbook.CreateEmptySheets(1);
			Worksheet sheet = workbook.Worksheets[0];
			sheet.Name = "Chart data";
			sheet.GridLinesVisible = false;

			//Writes chart data
			CreateChartData(sheet);
            //Add a new  chart worsheet to workbook
			Chart chart = sheet.Charts.Add();
			if (checkBox1.Checked)
			{
				chart.ChartType = ExcelChartType.Line3D;
			}
			else
			{
				chart.ChartType = ExcelChartType.Line;
			}

			//Set region of chart data
			chart.DataRange = sheet.Range["A1:E5"];

			//Set position of chart
			chart.LeftColumn = 1;
			chart.TopRow = 6;
			chart.RightColumn = 11;
			chart.BottomRow = 29;


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

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

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

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

			    if (!checkBox1.Checked)
				    cs.DataFormat.MarkerStyle = ChartMarkerType.Circle;
            }

            chart.PlotArea.Fill.Visible = false;

			chart.Legend.Position = LegendPositionType.Top;
			workbook.SaveToFile("Sample.xls",ExcelVersion.Version97to2003);
			ExcelDocViewer(workbook.FileName);
		}

		private void CreateChartData(Worksheet sheet)
		{
			//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 = 3300;
			sheet.Range["B3"].NumberValue = 2300;
			sheet.Range["B4"].NumberValue = 4500;
			sheet.Range["B5"].NumberValue = 6700;

			//Jul
			sheet.Range["C1"].Value = "Jul";
			sheet.Range["C2"].NumberValue = 7500;
			sheet.Range["C3"].NumberValue = 2900;
			sheet.Range["C4"].NumberValue = 2300;
			sheet.Range["C5"].NumberValue = 4200;

			//Aug
			sheet.Range["D1"].Value = "Aug";
			sheet.Range["D2"].NumberValue = 7700;
			sheet.Range["D3"].NumberValue = 6900;
			sheet.Range["D4"].NumberValue = 8400;
			sheet.Range["D5"].NumberValue = 4200;

			//Sep
			sheet.Range["E1"].Value = "Sep";
			sheet.Range["E2"].NumberValue = 8000;
			sheet.Range["E3"].NumberValue = 7200;
			sheet.Range["E4"].NumberValue = 8100;
			sheet.Range["E5"].NumberValue = 5600;

			//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.NumberFormat = "\"$\"#,##0";
		}

		private void ExcelDocViewer( string fileName )
		{
			try
			{
				System.Diagnostics.Process.Start(fileName);
			}
			catch{}
		}

		private void btnAbout_Click(object sender, System.EventArgs e)
		{
			Close();
		}


	}
}

After running your application with the code above, you will find an Excel Line Chart created.

Generate Excel Line Chart

Why Export Datatable to RTF?

RTF is a Microsoft specification and certified file format used with DOC and DOCX. It is a core part of the Microsoft Office system. RTF usually used for cut and paste, including paste special and used when opening documents into Word. RTF does not cause document corruption.

RTF allows Workshare tremendous flexibility in successfully translating between any number of document types because of its ubiquity. Workshare products can not only maintain document fidelity, but provide portability advantages when moving around different Microsoft Word systems, or translating to other document types.

How to Export DataTable to RTF through DataGridView?

Download Spire.DataExport (or Spire.Office) with .NET Framework together. Only 2 Simple steps you can finish the whole datatable to RTF exporting process.

Step 1: Load Data Information

Before exporting data from DataTable, we should load data information from data source. And select which information we need export. Through DataGridVew, we even can preview and modify data information. So, in this step, our job is to prepare data which is about to be exported out.

[C#]
        private void btnLoad_Click(object sender, EventArgs e)
        {
            oleDbConnection.ConnectionString = this.textBox1.Text;
            OleDbCommand oleDbCommand = new OleDbCommand();
            oleDbCommand.CommandText = this.textBox2.Text;
            oleDbCommand.Connection = oleDbConnection;
            using (OleDbDataAdapter da = new OleDbDataAdapter(oleDbCommand))
            {
                DataTable dt = new DataTable();
                da.Fill(dt);
                dataGridView1.DataSource = dt;
            }
        }

Effect Screenshot

Datatable to RTF

Step 2: Set Export into RTF

Spire.DataExport allows user to export data into most popular file formats including MS Excel, RTF, HTML, PDF, XML, CSV, DBF, DIF, etc. Here we need set it as RTF format. Spire.DataExport will create a new RTF file and through DataGridView export data into RTF file. You also can rename the file as you like.

[C#]
        private void btnRun_Click(object sender, EventArgs e)
        {
            Spire.DataExport.RTF.RTFExport RTFExport = new Spire.DataExport.RTF.RTFExport();
            RTFExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
            RTFExport.DataTable = this.dataGridView1.DataSource as DataTable;
            RTFExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
            RTFExport.FileName = "RTF0722.rtf";
            RTFExport.SaveToFile();
        }

Effect Screenshot

Datatable to RTF

When we export data out from Database we may have requirements of exporting data from Datatable to CSV because CSV is a simple file format that is widely supported. CSV (The comma-separated values) file format is a set of file formats used to store tabular data in which numbers and text are stored in plain textual form that can be read in a text editor. Lines in the text file represent rows of a table, and commas in a line separate what are fields in the table row.

Here we mainly discuss how to Export Datatable to CSV with Spire.DataExport for .NET.

Download Spire.DataExport (or Spire.Office) with .NET Framework together. Only 2 Simple steps you can finish the whole datatable to CSV exporting process.

Step 1: Load Data Information

Before exporting data from DataTable, we should load data information from data source. And select which information we need export. Through DataGridVew, we even can preview and modify data information. So, in this step, our job is to prepare data which is about to be exported out.

[C#]
	
private void btnLoad_Click(object sender, EventArgs e)
{
  using (OleDbConnection oleDbConnection = new OleDbConnection())
  {
    oleDbConnection.ConnectionString = this.textBox1.Text;
    OleDbCommand oleDbCommand = new OleDbCommand();
    oleDbCommand.CommandText = this.textBox2.Text;
    oleDbCommand.Connection = oleDbConnection;
    using (OleDbDataAdapter da = new OleDbDataAdapter(oleDbCommand))
      {
      DataTable dt = new DataTable();
      da.Fill(dt);
       dataGridView1.DataSource = dt;
      }
  }
}

Effect Screenshot

Datatable to CSV

Step 2: Set Export into CSV

Spire.DataExport allows user to export data into most popular file formats including MS Excel, MS Word, HTML, PDF, XML, CSV, DBF, DIF, etc. Here we need set it as CSV format. Spire.DataExport will create a new CSV file and through DataGridView export data into CSV file. You also can rename the file as you like.

[C#]
private void btnRUN_Click(object sender, EventArgs e)
{
  TXTExport CSVExport = new TXTExport();
  CSVExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
  CSVExport.DataTable = this.dataGridView1.DataSource as DataTable;
  CSVExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
  CSVExport.FileName = "CSV0721.csv";
  CSVExport.SaveToFile();
}

Effect Screenshot

Datatable to CSV

Thursday, 21 July 2011 03:51

Create Excel Bar Chart in C#, VB.NET

Why Create Excel Bar Chart?

For numerical comparisons, bar chart is a classic solution. Bar chart (graph) is a chart with rectangular bars with lengths proportional to the values that they represent. The bars can be plotted vertically or horizontally. A bar chart is very useful if you are trying to record certain information whether it is continuous or not continuous data. In bar charts, a class or group can have a single category of data, or they can be broken down further into multiple categories for greater depth of analysis. And in Microsoft Excel, it is the same useful!

How to Use C# to Create Excel Bar Chart?

Spire.XLS for .NET allows user to use C# create Excel bar chart. Use C# to create Excel bar chart could be as easy as in Microsoft Excel. The whole process is almost the same such as write chart data, set region/position of chart, Write chart title, chart data information, etc. The difference is we need fill these data in C# application with codes around. Below is a sample for C# code used to create Excel bar charts. Download Spire.XLS (or Spire.Office) with .NET framework 2.0 (or above) together and use the code to create Excel Bar Chart right now.

[C#]
using Spire.Xls;
using Spire.Xls.Charts;

namespace Spire.Xls.Sample
{
	class program
	{
		static void Main() 
		{
			Application.Run(new Form1());
		}

		private void btnRun_Click(object sender, System.EventArgs e)
		{
			Workbook workbook = new Workbook();
			
			//Initailize worksheet
			workbook.CreateEmptySheets(1);
			Worksheet sheet = workbook.Worksheets[0];
			sheet.Name = "Chart data";
			sheet.GridLinesVisible = false;

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

			//Set region of chart data
			chart.DataRange = sheet.Range["A1:C5"];
			chart.SeriesDataFromRange = false;

			//Set position of chart
			chart.LeftColumn = 1;
			chart.TopRow = 6;
			chart.RightColumn = 11;
			chart.BottomRow = 29;


			if (checkBox1.Checked)
			{
				chart.ChartType =  ExcelChartType.Bar3DClustered;
			}
			else
			{
				chart.ChartType = ExcelChartType.BarClustered;
			}

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

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

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


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

			chart.Legend.Position = LegendPositionType.Top;
			workbook.SaveToFile("Sample.xls",ExcelVersion.Version97to2003);
			ExcelDocViewer(workbook.FileName);
		}

		private void CreateChartData(Worksheet sheet)
		{
			//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 = 6000;
			sheet.Range["B3"].NumberValue = 8000;
			sheet.Range["B4"].NumberValue = 9000;
			sheet.Range["B5"].NumberValue = 8500;

			//Jun
			sheet.Range["C1"].Value = "Aug";
			sheet.Range["C2"].NumberValue = 3000;
			sheet.Range["C3"].NumberValue = 2000;
			sheet.Range["C4"].NumberValue = 2300;
			sheet.Range["C5"].NumberValue = 4200;

			//Style
			sheet.Range["A1:C1"].Style.Font.IsBold = true;
			sheet.Range["A2:C2"].Style.KnownColor = ExcelColors.LightYellow;
			sheet.Range["A3:C3"].Style.KnownColor = ExcelColors.LightGreen1;
			sheet.Range["A4:C4"].Style.KnownColor = ExcelColors.LightOrange;
			sheet.Range["A5:C5"].Style.KnownColor = ExcelColors.LightTurquoise;

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

			sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";
		}

		private void ExcelDocViewer( string fileName )
		{
			try
			{
				System.Diagnostics.Process.Start(fileName);
			}
			catch{}
		}

		private void btnAbout_Click(object sender, System.EventArgs e)
		{
			Close();
		}
	}
}

After running your application with the code above, you will find an Excel bar Chart created.

Generate Excel Bar Chart