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()
//!!!!!!!!!!!!!!!!!!!!!!!!
}
}