facebooktwittermenuarrow-up

GemBox Support Forum

Creating frozen row pane fails

I’m playing around with creating a spreadsheet with row 1 as frozen pane, similar to Excel’s “Freeze pane top row”.

When I use the following (there are only 13 populate columns):

ws.Panes = new WorksheetPanes(PanesState.Frozen, 0, 13, "A1", PanePosition.BottomLeft);

I get no compile errors or run-time errors, but Excel does not like the file complaining about format errors.
It will open if I use “yes”, but there are no frozen panes.

Code attached:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GemBox.Spreadsheet;
using System.Data;
using System.Diagnostics;

namespace SQLTest
{
    class Excel2WriteGemBox
    {
        static int UNIQUEID = 1;
        public void write2GMExcel(DataTable dt)
        {
            string sUniqueId = string.Empty;
            Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();
            int iRow = 1;

            // If using Professional version, put your serial key below.
            SpreadsheetInfo.SetLicense("...");

            var workbook = new ExcelFile();

            ExcelWorksheet ws = workbook.Worksheets.Add("My Sheet");

            int columnsCount = dt.Columns.Count;

            //create header part 1
            for (int i = 0; i < columnsCount; i++)
            {
                ws.Cells[0, i].Value = dt.Columns[i].ToString();
                ws.Cells[0, i].Style.Font.Weight = ExcelFont.BoldWeight; //make cell bold
            }

            ws.Panes = new WorksheetPanes(PanesState.Frozen, 0, 13, "A1", PanePosition.BottomLeft);

            //populate cells part 2
            foreach (DataRow dr in dt.Rows)//<=failed to increment dt.row
            {
                    if (dr.ItemArray[UNIQUEID].ToString() != sUniqueId)
                    {
                        for (int iColumn = 0; iColumn < columnsCount; iColumn++)
                        {
                            ws.Cells[iRow, iColumn].Value = dr.ItemArray[iColumn].ToString();
                        }
                    }
                    else //specific columns population
                    {
                        for (int iColumn = 0; iColumn < columnsCount; iColumn++)
                        {
                            switch (iColumn)
                            {
                                case 2:
                                case 6:
                                case 7:
                                case 8:
                                case 9:
                                case 10:
                                case 11:
                                case 12:
                                    ws.Cells[iRow, iColumn].Value = dr.ItemArray[iColumn].ToString();
                                    break;
                            }//switch
                        }
                    }

                    sUniqueId = dr.ItemArray[UNIQUEID].ToString();
                iRow++;

            }//foreach (DataRow dr in dt.Rows)//<=failed to increment dt.row

            workbook.Save(@"D:\project\The Westridge Professional\TestProjects\SQLTest\bin\Debug\ResultGemBox.xlsx");

            stopWatch.Stop();
            // Get the elapsed time as a TimeSpan value.
            TimeSpan ts = stopWatch.Elapsed;
            string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
            ts.Hours, ts.Minutes, ts.Seconds,
            ts.Milliseconds / 10);
            ErrorFile EF = new ErrorFile();
            EF.WriteError("SQLTest::Excel2WriteGemBox", elapsedTime, 0);
        }//public void write2Excel()


        //!!!!!!!!!!!!!!!!!!!!!!!!
    }
}

Hi Herb,

The problem is with specifying topLeftCell as “A1”.
This cell must be outside the frozen panes so in your case, cell “A14” or some other that position even further.

In short, try using this:

ws.Panes = new WorksheetPanes(PanesState.Frozen, 0, 13, "A14", PanePosition.BottomLeft);

Does this solve your issue?

Also as an FYI, we’ll add throwing of ArgumentException for these cases.

Regards,
Mario

Works beautifully.
One suggestion you might want to add this comment some how in the description:
" The problem is with specifying topLeftCell as “A1”.
This cell must be outside the frozen panes so in your case, cell “A14” or some other that position even further."
Thanks again!