How to Use Mark Desinger in Excel to Generate Table with C#

As Word mail merge, Excel mark designer can be used to export data to Excel template files. It is convenient for generating reports in bulk. In this post, I will show my method about how to use mark designer to export data to Excel.

At first, we need to prepare data and template. In my example, data shows VIP customers information which is saved in MS Access. There are five items, Name, Gender, Birthday, Email and Country.

For template, create a blank workbook. Then, enter item name in one row and format this row, such as setting text font, borders, cell fill and so on. Below this row, put placeholders. Each placeholder must match item above. At the end of the first placeholder, add (add:styles) to make sure that the following data will be formatted with styles placeholder row. Then format this row as well.

Template

After completing data and template preparation, start with coding.

Step 1: add references. I use Spire.XLS in this method, so add its dll file as reference. Then, add system.data and system.data.oledb.

Step 2: load template I create and initialize the worksheet.

Step 3: connect database which stores data I need and get data.

Step 4: export data with mark designer to template file.

Step 5: save and launch file.

Coding:

using System;
using System.Data;
using System.Data.OleDb;
using Spire.Xls;

namespace MarkDesigner
{
    class ExcelMD
    {
        static void Main(string[] args)
        {

            //Load File
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\VIP Customers.xlsx”, ExcelVersion.Version2010);
            Worksheet sheet = workbook.Worksheets[0];

            //Connect DataBase and Get Data
            string connString = @”Provider=Microsoft.ACE.OLEDB.12.0;
                                Data Source=D:\work\My Documents\VIP.mdb;Persist Security Info=False;”;
            DataTable dataTable = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                conn.Open();
                string sql = “select Name,Gender,Birthday,Email,Number,Country from VIP”;
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, conn);
                dataAdapter.Fill(dataTable);
            }

            //Export Data with MarkDesigner
            workbook.MarkerDesigner.AddDataTable(“VIP”, dataTable);
            workbook.MarkerDesigner.Apply();

            //Save and Launch File
            workbook.SaveToFile(“Mark Designer.xlsx”, ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Result:

____________________________________________________________________________________

Click Here to LEARN MORE about Spire.XLS

Click Here to DOWNLOAD Spire.XLS

Advertisements

Create Column Chart Accoring to Data Information in Excel with C#

MS Excel provides several kinds of charts with users. Chart can display data information more intuitively to make viewers learn data more clearly. In this post, I will create a column chart according to specified data by using C#.

Column Chart is often used to present data changes during one period, for example, one year and comparison between items.

In my example, I prepare one Excel file. This file includes four items, product no., quantity, unite price and total sales. I will create a column chart to display information about quantity and unit price according to product no. There are several types of column chart. And I choose Column 3D Clustered chart.

Data

At the beginning, add dll file of Spire.XLS for .NET, a C# .NET Excel component used in this method, as reference because I use it to achieve my needing.

Then, start with creating.

Step 1: load my file from computer and initialize worksheet.

Step 2: add chart in worksheet and select chart data range.

Step 3: set chart location in worksheet.

Step 4: set chart type as column 3D clustered chart.

Step 5: set chart title, axes titles and legend location. Also, format them.

Step 6: save and launch data.

Coding:

using System;
using Spire.Xls;
using Spire.Xls.Charts;

namespace Column_Chart
{
    class Charts
    {
        static void Main(string[] args)
        {

            //Load File and Initialize Worksheet
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\work\My Documents\Sales Report.xlsx”, ExcelVersion.Version2010);
            Worksheet sheet = workbook.Worksheets[0];

            //Add Chart and Select Chart Range
            Chart chart = sheet.Charts.Add();
            chart.DataRange = sheet.Range[“B2:C9”];
            chart.SeriesDataFromRange = false;

            //Set Chart Location
            chart.LeftColumn = 1;
            chart.TopRow = 12;
            chart.RightColumn = 10;
            chart.BottomRow = 31;

            //Get Chart Type
            chart.ChartType = ExcelChartType.Column3DClustered;

            //Chart Title and Axes Title
            chart.ChartTitle = “Sales Info”;
            chart.ChartTitleArea.Font.FontName = “Calibri”;
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size = 12;
            chart.PrimaryCategoryAxis.Title = “Product No”;
            chart.PrimaryCategoryAxis.Font.FontName = “Calibri”;
            chart.PrimaryCategoryAxis.Font.Size = 11;
            chart.PrimaryCategoryAxis.Font.IsBold = true;
            chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
            chart.PrimaryValueAxis.Title = “Unit Price and Quantity”;
            chart.PrimaryValueAxis.HasMajorGridLines = false;
            chart.PrimaryValueAxis.Font.FontName = “Calibri”;
            chart.PrimaryValueAxis.Font.Size = 11;
            chart.PrimaryValueAxis.MinValue = 100;
            chart.PrimaryValueAxis.TitleArea.IsBold = true;
            chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;

            //Legend Position
            chart.Legend.Position = LegendPositionType.Right;

            //Save and Launch File
            workbook.SaveToFile(“Column Chart.xlsx”, ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

How to Calculate with Formulas in Excel by Using C#

Several days ago, I show my method about how to use subtotal in Excel with C#. Subtotal includes many functions. Get function number and calculation range. And then the result will be shown. In this post, I pick up some frequently used functions and show how to set their formulas to get result with C#.

The functions I select are sum, average, max and count. The four functions are often used in transcripts, such as getting courses total marks, average store and so on.

In my example, I use sales report file which I have used in subtotal post. I will get product numbers, the maximum quantity, average price of these products and finally calculate sum of sales.

I still use the C# .NET Component Spire.XLS for .NET. So, add Spire.XLS for .NET dll file at the beginning. Then add system.drawing for formatting colors.

Step:

  1. Load sales report and initialize worksheet.
  2. Add cue words under the last row of table. Then, get result below the cue words row by formulas.
  3. Set format for cue words and result row.
  4. Save and launch file.

Coding:

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

namespace Calculation
{
    class Calculate
    {
        static void Main(string[] args)
        {
            //Load File
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\work\My Documents\Sales Report.xlsx”,ExcelVersion.Version2010);
            Worksheet sheet = workbook.Worksheets[0];

            //Sum of Sales
            sheet.Range[“D10”].Text = “Sales”;
            sheet.Range[“D11”].Formula = “SUM(D3:D9)”;

            //Average Price
            sheet.Range[“C10”].Text = “Average Price”;
            sheet.Range[“C11”].Formula = “AVERAGE(C3:C9)”;

            //Max Quantity
            sheet.Range[“B10”].Text = “Max Quantity”;
            sheet.Range[“B11”].Formula = “MAX(B3:B9)”;

            //Product Count
            sheet.Range[“A10”].Text = “Products”;
            sheet.Range[“A11”].Formula = “COUNT(A3:A9)”;

            //Format Cells
            sheet.Range[“A10:D11”].Style.Font.FontName = “Times New Roman”;
            sheet.Range[“A10:D11”].Style.Font.Size = 13;
            sheet.Range[“A10:D10”].Style.Font.IsItalic = true;
            sheet.Range[“A10:D10”].Style.Font.Color = Color.Red;
            sheet.Range[“A10:D10”].Style.Font.IsBold = true;
            sheet.AllocatedRange.AutoFitColumns();
            sheet.Range[“A10:D11”].HorizontalAlignment = HorizontalAlignType.Center;

            //Save and Launch File
            workbook.SaveToFile(“calculation.xlsx”, ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Result:

Download Spire.XLS for .NET Here

How to Format Excel Cells (Set Cell Style) with C#

We often format cells after exporting data to Excel, for example, set different color or font size for important data and title. Also, a well formatted Excel file is more appealed to viewers.

Sometimes, in order to separate title from data information, we may set different style for title row and data rows. In this post, I will show my method to set cell style with C#.

In my example, I have an Excel file which displays parts of employee information. The first row shows title, including first name, last name, hire date and other information. What I will do is to set different styles for title and the following data. The style contains font style, background color and borders.

Employee

At the beginning, add essential references in project. Because I use a .NET Excel component Spire.XLS for .NET, so add its dll file as reference firstly. Then add system.drawing for setting colors.

Steps:

  1. Load employee.xlxs from my computer and initialize the worksheet.
  2. Add title style and cell style. Then add details format settings.
  3. Apply the two styles to title range and data range.
  4. Save and launch file.

Coding:

using System;

using System.Drawing;

using Spire.Xls;

 

namespace Cell

{

    class Style

    {

        static void Main(string[] args)

        {

            //Load File

            Workbook workbook = new Workbook();

            workbook.LoadFromFile(@”D:\work\My Documents\employee.xlsx”);

            Worksheet worksheet = workbook.Worksheets[0];

 

            //Add TitleStyle

            CellStyle titlestyle = workbook.Styles.Add(“style”);

            titlestyle.Font.FontName = “Times New Roman”;

            titlestyle.Font.Size = 12;

            titlestyle.Font.IsBold = true;

            titlestyle.Font.IsItalic = true;

            titlestyle.Color = Color.GreenYellow;

            titlestyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;

            titlestyle.Borders[BordersLineType.EdgeLeft].Color = Color.DarkCyan;

            titlestyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

            titlestyle.Borders[BordersLineType.EdgeRight].Color = Color.DarkCyan;

            titlestyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;

            titlestyle.Borders[BordersLineType.EdgeTop].Color = Color.DarkCyan;

            titlestyle.HorizontalAlignment = HorizontalAlignType.Center;

            titlestyle.VerticalAlignment = VerticalAlignType.Center;

 

            //Add CellStyle

 

            CellStyle cellstyle = workbook.Styles.Add(“cellstyle”);

            cellstyle.Font.FontName = “Calibri”;

            cellstyle.Font.Size = 12;

            cellstyle.Color = Color.WhiteSmoke;

            cellstyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;

            cellstyle.Borders[BordersLineType.EdgeBottom].Color = Color.DarkCyan;

            cellstyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;

            cellstyle.Borders[BordersLineType.EdgeLeft].Color = Color.DarkCyan;

            cellstyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

            cellstyle.Borders[BordersLineType.EdgeRight].Color = Color.DarkCyan;

            cellstyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;

            cellstyle.Borders[BordersLineType.EdgeTop].Color = Color.DarkCyan;

            cellstyle.HorizontalAlignment = HorizontalAlignType.Center;

            cellstyle.VerticalAlignment = VerticalAlignType.Center;

 

            //Apply Style

            worksheet.Range[“A1:F1”].Style = titlestyle;

            worksheet.Range[“A2:F19”].Style = cellstyle;

 

            //Save and Luanch File

            workbook.SaveToFile(“style.xlsx”, ExcelVersion.Version2010);

            System.Diagnostics.Process.Start(workbook.FileName);

        }

    }

}

Result:

Download Spire.XLS for .NET Here

C# – Write and Read XML

About XML

XML (Extensible Markup Language) is one kind of source language, which allows users defining their own markup language. It can be used to mark electronic files to make these files become markup language with structures. Also, it can mark data and define data type.

The main feature of XML is to store data but it is quite different from database. XML just displays data. Because it is very simple and convenient for delivering on Web, XML file is widely used.

Write & Read XML

In this post, I will talk something about how to write and read XML by using C# via Spire.XLS.

At the beginning, add necessary references, Spire.XLS dll file, System.Windows.Forms and System.IO.

Write XML:

  1. Create a new workbook and initialize worksheet.
  2. Write contents in this workbook and then set format for contents.
  3. Auto fit column and set row height for cells which are filled with data.
  4. Save file to XML and launch it.

Coding:

using System;

using System.IO;

using System.Windows.Forms;

using Spire.Xls;

namespace WritenReadXML

{

    class XML

    {

        static void Main(string[] args)

        {

            Workbook workbook = new Workbook();

            workbook.CreateEmptySheets(1);

            Worksheet sheet = workbook.Worksheets[0];

            //Write Contents

            sheet.Range[“C10”].Text = “WriteXML”;

            sheet.Range[“C10”].Style.Font.FontName = “Calibari”;

            sheet.Range[“C10”].Style.Font.Size = 14;

            sheet.Range[“C10”].Style.KnownColor = ExcelColors.LightBlue;

            sheet.Range[“C11”].Text = “ReadXML”;

            sheet.Range[“C11”].Style.Font.FontName = “Calibari”;

            sheet.Range[“C11”].Style.Font.Size = 14;

            sheet.Range[“C11”].Style.KnownColor = ExcelColors.YellowCustom;

            //Autofit Column and Set Row Hight

            sheet.AutoFitColumn(3);

            sheet.SetRowHeight(10, 20);

            sheet.SetRowHeight(11, 20);

            //Save as XML

            workbook.SaveAsXml(“WriteXML.xml”);

            //Launch XML

            System.Diagnostics.Process.Start(Path.Combine(Application.StartupPath, “WriteXML.xml”));

Open with Excel:

Open with Text:

Read XML:

Now, I want to read the xml file which I wrote right now.  Firstly, I save this file to specified path and read it.

  1. Create workbook and initialize worksheet.
  2. Load xml file from my computer.
  3. Save this xml file as Excel.
  4. Launch file.

Coding:

            Workbook workbook = new Workbook();

            Worksheet sheet = workbook.Worksheets[0];

            using (FileStream fileStream = File.OpenRead(@”D:\work\My Documents\WriteXML.Xml”))

            {

                workbook.LoadFromXml(fileStream);

            }

            workbook.SaveToFile(@”D:\ReadXML.xlsx”,ExcelVersion.Version2010);

            System.Diagnostics.Process.Start(workbook.FileName);

————————————————————————————————————————————–

Click Here to learn more about Spire.XLS

Click Here to download Spire.XLS