How to Save Excel Version as 2007 and Set Excel 2007 Interior Format with C#

Although MS Office are always updating their products, some people would like to use the older versions. Then, there is a problem that format in later version will lost when opening with older version, even cannot be opened with older version. Therefore, MS Office offers conversion between older versions and newer versions to users. 

Detailed Steps:

  1. Create an Excel workbook and set its version as Version2007.
  2. Add Information as header for A1 to E1 and then set format.
  3. Set Interior format for worksheet.
  4. Save and launch file.

Main Coding:

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

namespace Excel2007
{
    class Excel2007
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();

            //Initialize Worksheet
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];

            //Set Excel Version
            workbook.Version = ExcelVersion.Version2007;
            int maxColor = Enum.GetValues(typeof(ExcelColors)).Length;
            Random random = new Random((int)System.DateTime.Now.Ticks);
            for (int i = 2; i < 40; i++)
            {
                ExcelColors backKnownColor = (ExcelColors)(random.Next(1, maxColor / 2));

                //Set Text Range and Format
                sheet.Range["A1"].Text = "Color Name";
                sheet.Range["B1"].Text = "Red";
                sheet.Range["C1"].Text = "Green";
                sheet.Range["D1"].Text = "Blue";
                sheet.Range["E1:K1"].Merge();
                sheet.Range["E1:K1"].Text = "Gradient";
                sheet.Range["A1:K1"].Style.Font.FontName = "Meiryo";               
                sheet.Range["A1:K1"].Style.Font.IsBold = true;
                sheet.Range["A1:K1"].Style.Font.Size = 11;
                sheet.Range["A1:K1"].Style.Font.Color = Color.LimeGreen;

                //Set Excel 2007 Interior Format
                string colorName = backKnownColor.ToString();
                sheet.Range[string.Format("A{0}", i)].Text = colorName;
                sheet.Range[string.Format("B{0}", i)].Text = workbook.GetPaletteColor(backKnownColor).R.ToString();
                sheet.Range[string.Format("C{0}", i)].Text = workbook.GetPaletteColor(backKnownColor).G.ToString();
                sheet.Range[string.Format("D{0}", i)].Text = workbook.GetPaletteColor(backKnownColor).B.ToString();
                sheet.Range[string.Format("E{0}:K{0}", i)].Merge();
                sheet.Range[string.Format("E{0}:K{0}", i)].Text = colorName;
                sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.FillPattern = ExcelPatternType.Gradient;
                sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.BackKnownColor = backKnownColor;
                sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.ForeKnownColor = ExcelColors.White;
                sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.GradientStyle = GradientStyleType.Vertical;
                sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.GradientVariant = GradientVariantsType.ShadingVariants1;
            }
            sheet.AutoFitColumn(1);

                 //Save and Launch File
            workbook.SaveToFile("Excel2007.xlsx");
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

After running, you can get the following effect.

______________________________________________________________________________________________

Click here to LEARN MORE about Spire.XLS

Click here to DOWNLOAD Spire.XLS

Advertisements

Author: janewdaisy

.NET Program Beginner. Share methods about how to use C#/VB.NET to export data, operate Word, Excel, PDF and other useful skills.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s