Oh, Summer

Summer is coming. My eyes are filled with Green.

Summer is a nice season but it makes me be irritated. OK, let me drink ice water and calm down.

Someone told me that beach is the most beautiful scene in Summer. Is it ture?


OK, it’s summer. Let’s enjoy it!

Advertisements

Generate Invoice with Mail Merge by Using C#

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 a .NET Word component, Spire.Doc.

Report Overview

This report includes multiple invoices, and each invoice starts in a new page. Invoice logo and supplier information will present in the header of every page.

Order, shipment, customer, order details and total price make up a completed invoice.

The following pictures show the appearance of invoice:

Content details in each invoice are shown as following:

Order Data Overview

All data in this example is from NorthWind database, which is a sample database provided by Microsoft Access 2003.

We will export data from table Orders, Shippers, Customers, Employees, [Order Details] and Products to generate our report. The following picture shows the relationship between the 6 tables.

Steps

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

  1. Create a mail merge template.
  2. Load data from database.
  3. 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

How to Create the 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.

1. We can create the template in MS Word or by other program. Please see the following picture. It is the template we need to create. Data will be filled in the red party.

2. Insert mail-merge-field as placeholder into the red-block. There are three types of mail-merge-field which will be used in this example:

  a). GeneralField is a general Word mail-merge-field. It is real data field and our data will be filled in it during merge process. We need to insert a GeneralField to every red-block and name these fields with the corresponding data name. After inserting GeneralFields, our template will looks like:

   b). 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. For example, fields in Customer information block will be filled with data from data table Customer, so we need to put them in TableField Customer. Insert a mail-merge-field with field name TableStart:Customer immediately before the first CompanyName field and insert another mail-merge-field with field name TableEnd:Customer immediately after the field Country. And then our fields in Customer information block looks like:

During the merge process, data in column CompanyName of table Customer will be filled in the field CompanyName, Customer.Address to field Address, Customer.City to field City and so on.

Data of fields in column Salesperson in Order information table is from table Employee

Data of fields in column Ship Via in Order information table is from table Shipper

Data of fields in Order details table is from table Detail, except field ProductName. Data of field ProductName is from table Product. Dataof field InvoiceSubtotal and InvoiceTotal in Invoice total information is from table Total (virtual table)

 

  c). 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:Order in the bottom of the template body. After this, our template looks like:

 You could find the complete template named InvoiceTemplate.doc in the attached source package.

Load Data from Database

Spire.Doc provides merge data from DataSet. So we will use DataAdapter to fill data table from NorthWind database to a DataSet and merge it into our template. Difference from DataRelation of DataSet, Spire.Doc has owned table relation functionality. So we don’t need to create DataRelation instance for the DataSet object. The code below just shows load Order data. Please see the attached source package for other code.

Merge data into template and save

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

1. Create Spire.Doc.Document object and load template.

 2. Establish relationship between data tables.

In order to start each invoice in a new page, we insert a page-break-symbol immediately before the first paragraph when a new order row will be merged. To do this, we need to handle the event MergeField which is fired before a field merged.

Code of method InsertPageBreak

Conclusion

This article focuses on how to generate report by using mail merge with C#. In this method, we need to prepare data information and template firstly. Then, merge data in customized template. It helps us to generate report in bulk quickly and easily. 

For the component used in this example, please download from here.

Conclusion on Usual Data Export to Excel Methods with C#

Programmers often need to export data to Excel during programming. In this article, I will share three methods about how to export data to Excel from DataTable, GridView and Database. All these methods come from materials I collect. Therefore, this article is just a conclusion.

Method 1: Export Data to Excel from DataTable

         public static void ExportExcel(string fileName, DataTable dataSource)

         {

            System.Web.UI.WebControls.GridView dgExport = null;

            System.Web.HttpContext curContext = System.Web.HttpContext.Current;

            //IO is used to export and return Excel

            System.IO.StringWriter strWriter = null;

            System.Web.UI.HtmlTextWriter htmlWriter = null;

            if (dataSource != null)

            {

                //Set encoding and attachment format

                curContext.Response.Clear();

                curContext.Response.Buffer = true;

                curContext.Response.AddHeader(“content-disposition”, “attachment; filename=” + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + “.xls”);

                curContext.Response.ContentType = “application/vnd.ms-excel”;

                //Avoid unreadable code to appearing in contents exported.

                curContext.Response.Write(“<meta http-equiv=Content-Type content=text/html;charset=UTF-8>”);

                //Export to Excel

                strWriter = new System.IO.StringWriter();

                htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);

                //Redefine one GridView without paging to solve the problem that there is paging in daData.

                dgExport = new System.Web.UI.WebControls.GridView();

                dgExport.DataSource = dataSource;

                dgExport.AllowPaging = false;

                dgExport.DataBind();

                //Download to server.

                dgExport.RenderControl(htmlWriter);

                curContext.Response.Write(strWriter.ToString());

                curContext.Response.End();

Method 2: Export Data to Excel from GridView

        public static void ExportExcel(string fileName, GridView gvMain)

        {

            System.Web.HttpContext curContext = System.Web.HttpContext.Current;

            //IO is used to export and return Excel.

            System.IO.StringWriter strWriter = null;

            System.Web.UI.HtmlTextWriter htmlWriter = null;

            if (gvMain.DataSource != null)

            {

                //Set encoding and attachment format.

                curContext.Response.Clear();  

                curContext.Response.Buffer = true;  

                curContext.Response.AddHeader(“content-disposition”, “attachment;filename=” + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + “.xls”);

                curContext.Response.ContentType = “application/vnd.ms-excel”;

                //Avoid unreadable code to appearing in contents exported.

                curContext.Response.Write(“<meta http-equiv=Content-Type content=text/html;charset=UTF-8>”);

                //Export to Excel

                strWriter = new System.IO.StringWriter();

                htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);

                //Download to Server

                gvMain.RenderControl(htmlWriter);

                curContext.Response.Write(strWriter.ToString());

                curContext.Response.End();

            }

        }

Problems and Solutions

We can find that method 2 is similar to method 1. But method 2 can customize format for Excel column, for example: ID card number, currency format.

However, there is one problem in method 2, wrong with the command: GridView widget must put in Form Marker which has the sentence: runat = server.

Solution: Override for VerifyRenderingInServerForm method of WebForm.

        public override void VerifyRenderingInServerForm(Control control)

        {

            //OverRide

        }

The following code shows how to display ID card number and currency format after exporting data.

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)

        {

            if (e.Row.RowType == DataControlRowType.DataRow)

            {

                e.Row.Cells[ID Number].Attributes.Add(“style”, “vnd.ms-excel.numberformat:@”);

            }

        }

The third method is based on a component, Spire.DataExport. Because it shows method to export data from database, so we need to get data information in database firstly. In this example, the data information which will be exported is customers table.

Method 3: Export Data from DataBase

            OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection();

            oleDbConnection.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DB1.mdb”;

            OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand();

            oleDbCommand.CommandText = “select * from Customers”;

            oleDbCommand.Connection = oleDbConnection;

            Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();

            Spire.DataExport.XLS.WorkSheet workSheet1 = new Spire.DataExport.XLS.WorkSheet();

            cellExport.FileName = “Customers.xls”;

            cellExport.SheetOptions.AggregateFormat.Font.Name = “Calibri”;

            cellExport.SheetOptions.CustomDataFormat.Font.Name = “Calibri”;

            cellExport.SheetOptions.TitlesFormat.Font.Name = “Calibri”;

            workSheet1.AutoFitColWidth = true;

            workSheet1.Options.TitlesFormat.Font.Color = Spire.DataExport.XLS.CellColor.Pink;

            workSheet1.Options.TitlesFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Center;

            workSheet1.Options.TitlesFormat.Font.Bold = true;

            workSheet1.Options.TitlesFormat.Font.Size = 11F;

            workSheet1.SheetName = “Customers”;

            workSheet1.SQLCommand = oleDbCommand;

            cellExport.Sheets.Add(workSheet1);

            workSheet1.ItemType = Spire.DataExport.XLS.CellItemType.Row;

            oleDbConnection.Open();

            cellExport.SaveToFile(“Customers.xls”);

            oleDbConnection.Close();

            System.Diagnostics.Process.Start(“Customers.xls”);

Conclusion

This article concludes three useful methods on exporting data to Excel. I hope that each of the three methods will be helpful for you.

About the component used in the third method, learn more by click here and download here.

Two Methods to Export Data from DataSet to Excel in C#

Dataset is often used to save data by users because it is available even though we close database. And users would like to export data in dataset to Excel for displaying because Excel is very convenient for formatting and calculating data. This article introduces two solutions to export in C#.

Solution with Microsoft Office Installed:

Before using this solution, please make sure that MS Office has been installed on your system and add Microsoft Object library as reference in project.

        ///<summary>
        ///</summary>
        ///<param name=”DataSet”>Export DataSet</param>
        ///<param name=”isShowExcle”>Show Excel or not</param>
        ///<returns></returns>

        public bool DataSetToExcel(DataSet dataSet, bool isShowExcle, string fileName)
        {

            DataTable dataTable = dataSet.Tables[0];
            int rowNo = dataTable.Rows.Count;
            int columnNo = dataTable.Columns.Count;
            int colIndex = 0;
            if (rowNo == 0)
            {
                return false;
            }

            //Create Excel Object
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

            //excel.Application.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            worksheet.Name = DateTime.Now.ToString().Replace(“:”, “”).Replace(“-“, “”).Replace(” “, “”);
            excel.Visible = isShowExcle;

            //Microsoft.Office.Interop.Excel.Worksheet worksheet =(Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
            Microsoft.Office.Interop.Excel.Range range;

            //Generate Fields Name
            foreach (DataColumn col in dataTable.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }
            object[,] objData = new object[rowNumber, columnNumber];
            for (int row = 0; row < rowNo; r++)
            {
                for (int col = 0; col < columnNo; c++)
                {
                    objData[row, col] = dataTable.Rows[r][c];
                }
                //Application.DoEvents();
            }
            range = worksheet.Range[excel.Cells[4, 3], excel.Cells[rowNo + 1, columnNo]];

            //Set Cell Format as Text
            range.NumberFormat = “@”;
            range.Value2 = objData;
            worksheet.Range[excel.Cells[4, 3], excel.Cells[rowNo + 1, 1]].NumberFormat = “yyyy-m-d h:mm”;
            workbook.SaveAs(example, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
            System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
            System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            workbook.Close();

            excel.Quit();
            GC.Collect();
            KillExcel();
            return true;
        }

Solution with Data Export Component

This solution is based on a Free C# .NET Data Export component, Free Spire.DataExport. So, please download and install this component and then add its dll as reference in project. Now, follow the steps to realize exporting and formatting the exported data.

Firstly, Create a Windows Form project. Design the form and add essential elements in forms as following.

Secondly, double click Run button and write the following code.

            string connStr = txtConnstr.Text.Trim();
            string cmd = txtCmd.Text.Trim();

            //Fill DataSet
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd, conn))
                {
                    using (OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(adapter))

                    {
                        using (DataSet ds = new DataSet())
                        {
                            ds.Locale = System.Globalization.CultureInfo.InvariantCulture;
                            adapter.Fill(ds);
                            SaveFileDialog saveFileDLG = new SaveFileDialog();
                            saveFileDLG.Filter = “(*.xls)|*.xls”;
                            string currentDirectory = Directory.GetCurrentDirectory();
                            CellExport cellExport = new CellExport();
                            saveFileDLG.ShowDialog();
                            this.txtExportFile.Text = saveFileDLG.FileName;
                            if (string.IsNullOrEmpty(txtExportFile.Text))
                            {
                                MessageBox.Show(“Please input a directory!”);
                                return;
                            }
                            OleDbCommand oleDbCommand1 = new OleDbCommand(txtCmd.Text, conn);
                            WorkSheet workSheet1 = new WorkSheet();

                            StripStyle stripStyle1 = new StripStyle();
                            stripStyle1.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;
                            stripStyle1.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;
                            stripStyle1.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;
                            stripStyle1.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;
                            stripStyle1.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;

                            //Export Style
                            cellExport.ActionAfterExport = ActionType.OpenView;
                            cellExport.AutoFitColWidth = true;
                            cellExport.FileName = this.txtExportFile.Text.Trim();

                            cellExport.SheetOptions.TitlesFormat.Font.Name = “Times New Roman”;

                            workSheet1.AutoFitColWidth = true;
                            workSheet1.Options.TitlesFormat.Font.Color = Spire.DataExport.XLS.CellColor.Pink;
                            workSheet1.Options.TitlesFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Center;
                            workSheet1.Options.TitlesFormat.Font.Italic = true;
                            workSheet1.Options.TitlesFormat.Font.Bold = true;
                            workSheet1.Options.TitlesFormat.Font.Size = 12F;

                            //Export Data           
                            workSheet1.SheetName = “Demo”;
                            workSheet1.SQLCommand = oleDbCommand1;
                            cellExport.Sheets.Add(workSheet1);
                            workSheet1.ItemType = Spire.DataExport.XLS.CellItemType.Row;
                            workSheet1.ItemStyles.Add(stripStyle1);
                            workSheet1.ItemStyles.Add(stripStyle2);
                            cellExport.SaveToFile(this.txtExportFile.Text.Trim());
                        }
                    }
                }
            }

Conclusion

The two solutions can export data from dataset to Excel. The first one is based on Microsoft Office, and the second one is based a component. If you don’t want to install MS Office on your computer, the second will be a good choice to export data.

Export Customized Format Data to Excel without Microsoft Office Installed

This article is from Dotnetspider.com. I viewed it by chance and I thought that it was a helpful article for beginners. So I contacted with its author and get the permission to share this article in my blog. However, the following words I show is not all the same with original article but change something according to my own requirements.

Original Article: http://www.dotnetspider.com/resources/42388-Export-Customized-Format-Data-Excel-without.aspx

Introduction

This article focuses on how to export data from database to Excel with customized formatting by using C#, including font style, cell fill and borders.

Background

Generally speaking, when we export data from database to Excel, there is no formatting for any cells. While, this article introduces a simple method to export data with customized formatting. This method is based on a component, Spire.DataExport. Therefore, we need to add its dll file as reference before coding.

Steps

1.  Connect to database and get the needed datatable. Select necessary information columns and export to Excel. By default, the data will be put in the first sheet in Excel.

2. Set title format, including font style, cell fill and borders.

3. Set format for others cells. In this step, I set two styles. The two styles have different background colors. After formatting, autofit columns. 

4. Set sheet name and apply styles into worksheet. Then save and launch file.

Code in Details

Step 1, Connect Database and Get Data

            OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection();

            oleDbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=VIP.mdb";

            OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand();

            oleDbCommand.CommandText = "select Name,Gender,Birthday,Email,Number,Country from VIP";

            oleDbCommand.Connection = oleDbConnection;

            Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();

            Spire.DataExport.XLS.WorkSheet workSheet1 = new Spire.DataExport.XLS.WorkSheet();

Step 2, Customize Data Formatting 

Firstly, set title format, including font name, size, color, background color and borders. The other format I need to set is data type. Because there are some data about date, so set date time format for data information at the beginning.

            cellExport.DataFormats.DateTime = "yyyy-M-d H:mm";

            cellExport.FileName = "VIP.xls";

            workSheet1.Options.TitlesFormat.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightBlue;

            workSheet1.Options.TitlesFormat.Font.Name = "Calibri";

            workSheet1.Options.TitlesFormat.Font.Color = Spire.DataExport.XLS.CellColor.White;

            workSheet1.Options.TitlesFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Center;

            workSheet1.Options.TitlesFormat.Font.Bold = true;

            workSheet1.Options.TitlesFormat.Font.Size = 13.5F;

            workSheet1.Options.TitlesFormat.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;

            workSheet1.Options.TitlesFormat.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;

            workSheet1.Options.TitlesFormat.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;

            workSheet1.Options.TitlesFormat.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;

Secondly, define two styles and set different format for them.

            Spire.DataExport.XLS.StripStyle stripStyle1 = new Spire.DataExport.XLS.StripStyle();

            Spire.DataExport.XLS.StripStyle stripStyle2 = new Spire.DataExport.XLS.StripStyle();

stripStyle 1

            stripStyle1.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightYellow;

            stripStyle1.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.None;

            stripStyle1.Font.Name = "Calibri";

            stripStyle1.Font.Size = 11.5F;

            stripStyle1.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Center

            stripStyle1.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;

            stripStyle1.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;

            stripStyle1.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;

            stripStyle1.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;

stripStyle 2

            stripStyle2.FillStyle.Background = Spire.DataExport.XLS.CellColor.LightGreen;

            stripStyle2.Font.Name = "Calibri";

            stripStyle2.Font.Size = 11.5F;

            stripStyle2.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Center;

            stripStyle2.Borders.Top.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;

            stripStyle2.Borders.Bottom.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;

            stripStyle2.Borders.Right.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;

            stripStyle2.Borders.Left.Style = Spire.DataExport.XLS.CellBorderStyle.Thin;

Thirdly, auto fit columns.

            workSheet1.AutoFitColWidth = true;

Step 3, Name Worksheet and Apply Style

            workSheet1.SheetName = "VIP";

            workSheet1.SQLCommand = oleDbCommand;

            cellExport.Sheets.Add(workSheet1);

            workSheet1.ItemType = Spire.DataExport.XLS.CellItemType.Row;

            workSheet1.ItemStyles.Add(stripStyle1);

            workSheet1.ItemStyles.Add(stripStyle2);

Step 4, Save and Launch

            oleDbConnection.Open();

            cellExport.SaveToFile(@"result.xls");

            oleDbConnection.Close();

            System.Diagnostics.Process.Start(@"result.xls");

Result shown as following:

Conclusion

This method is used to export data to Excel with customized style. When we open the result file, we can find that there are three different styles has been set, title format, stripStyle 1 and stripStyle 2. 

For downloading component used in method, please click  here.