Freeze Pane and Columns in Openxml

I need help.

I have a question to ask using OpenXMLWriter.

I am currently using the code below to create my excel file, but I want to set up width of the columns and freeze panes. How should I do it?

Because I have written the following code for that. I do not know why is not working.

Example would be very helpful. Appreciate it and Thanks!

public bool ExportData(DataSet ds, string destination, List<Tuple<string, string>> parms)

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(destination, SpreadsheetDocumentType.Workbook))

WorkbookPart wbp = spreadsheetDocument.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet worksheet = new Worksheet();
SheetData sheetData = new SheetData();


foreach (DataTable table in ds.Tables)

Row headerRow = new Row();

int lp = 1;
foreach (var parm in parms)

Row newRow = new Row();

// Write the parameter names
Cell parmNameCell = new Cell();
parmNameCell.DataType = CellValues.String;
parmNameCell.CellValue = new CellValue(parm.Item1.ToString()); //
parmNameCell.StyleIndex = 1;

newRow.AppendChild(parmNameCell);

// Write the parameter values

Cell parmValCell = new Cell();
parmValCell.DataType = CellValues.InlineString;
parmValCell.DataType = CellValues.String;
parmValCell.CellValue = new CellValue(parm.Item2?.ToString()); //
newRow.AppendChild(parmValCell);

sheetData.AppendChild(newRow);

lp++;


Columns columns = new Columns();
int i = 1;
foreach (DataColumn column in table.Columns)

Column column1 = new Column();
column1.Min = Convert.ToUInt32(i);
column1.Max = Convert.ToUInt32(i);
column1.Width = insertSpaceBeforeUpperCAse(column.ColumnName).Length + 2;
column1.BestFit = true;
columns.Append(column1);
i++;

worksheet.Append(columns);

int freezeRow = lp;

Row blankRow = new Row();
sheetData.AppendChild(blankRow);

//// Write the column names
List<string> columns2 = new List<string>();
foreach (DataColumn column in table.Columns)

columns2.Add(column.ColumnName);

Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(insertSpaceBeforeUpperCAse(column.ColumnName));

cell.StyleIndex = 1;
headerRow.AppendChild(cell);


sheetData.AppendChild(headerRow);

foreach (DataRow dsrow in table.Rows)

Row newRow = new Row();
foreach (string col in columns2)

Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);


sheetData.AppendChild(newRow);


//worksheet.Append(sheetData);
//wsp.Worksheet = worksheet;
//wsp.Worksheet.Save();

Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = table.TableName;
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);

sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);



#region Freeze Panel

string freezeRangeFrom = $"AfreezeRow + 2";

SheetViews sheetViews = new SheetViews();
SheetView sheetView = new SheetView()

TabSelected = false,
WorkbookViewId = (UInt32Value)0U
;

Pane pane = new Pane()

VerticalSplit = 7D,
TopLeftCell = freezeRangeFrom,
ActivePane = PaneValues.BottomLeft,
State = PaneStateValues.Frozen
;

sheetView.Append(pane);
sheetViews.Append(sheetView);
worksheet.Append(sheetViews);
worksheet.Append(sheetData);
wsp.Worksheet = worksheet;
wsp.Worksheet.Save();

#endregion




spreadsheetDocument.WorkbookPart.Workbook = wb;
spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();



return true;



I need please. Please Help me....

2 Answers
2

This has bit me in the past. You have to add the view to the sheet before the data. You can try something like this:

public bool ExportData(DataSet ds, string destination, List<Tuple<string, string>> parms)

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(destination, SpreadsheetDocumentType.Workbook))

WorkbookPart wbp = spreadsheetDocument.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";

#region Freeze Panel

var freezeRow = parms.Count;
string freezeRangeFrom = $"AfreezeRow + 2";

SheetViews sheetViews = new SheetViews();
SheetView sheetView = new SheetView()

TabSelected = false,
WorkbookViewId = (UInt32Value)0U
;

Pane pane = new Pane()

VerticalSplit = 7D,
TopLeftCell = freezeRangeFrom,
ActivePane = PaneValues.BottomLeft,
State = PaneStateValues.Frozen
;

sheetView.Append(pane);

#endregion

Worksheet worksheet = new Worksheet(new SheetViews(sheetView));
SheetData sheetData = new SheetData();


foreach (DataTable table in ds.Tables)

Row headerRow = new Row();

foreach (var parm in parms)

Row newRow = new Row();

// Write the parameter names
Cell parmNameCell = new Cell();
parmNameCell.DataType = CellValues.String;
parmNameCell.CellValue = new CellValue(parm.Item1.ToString()); //
parmNameCell.StyleIndex = 1;

newRow.AppendChild(parmNameCell);

// Write the parameter values

Cell parmValCell = new Cell();
parmValCell.DataType = CellValues.InlineString;
parmValCell.DataType = CellValues.String;
parmValCell.CellValue = new CellValue(parm.Item2?.ToString()); //
newRow.AppendChild(parmValCell);

sheetData.AppendChild(newRow);


Columns columns = new Columns();
int i = 1;
foreach (DataColumn column in table.Columns)

Column column1 = new Column();
column1.Min = Convert.ToUInt32(i);
column1.Max = Convert.ToUInt32(i);
column1.Width = insertSpaceBeforeUpperCAse(column.ColumnName).Length + 2;
column1.BestFit = true;
columns.Append(column1);
i++;

worksheet.Append(columns);

Row blankRow = new Row();
sheetData.AppendChild(blankRow);

//// Write the column names
List<string> columns2 = new List<string>();
foreach (DataColumn column in table.Columns)

columns2.Add(column.ColumnName);

Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(insertSpaceBeforeUpperCAse(column.ColumnName));

cell.StyleIndex = 1;
headerRow.AppendChild(cell);


sheetData.AppendChild(headerRow);

foreach (DataRow dsrow in table.Rows)

Row newRow = new Row();
foreach (string col in columns2)

Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);


sheetData.AppendChild(newRow);


//worksheet.Append(sheetData);
//wsp.Worksheet = worksheet;
//wsp.Worksheet.Save();

Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = table.TableName;
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);

sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);

spreadsheetDocument.WorkbookPart.Workbook = wb;
spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();



return true;



In case your SheetView does not work, I included an example of one that worked for me:

SheetView sheetView = new SheetView() TabSelected = true, WorkbookViewId = (UInt32Value)0U ;
Pane pane = new Pane() VerticalSplit = 1D, TopLeftCell = "A2", ActivePane = PaneValues.BottomLeft, State = PaneStateValues.Frozen ;
Selection selection = new Selection() Pane = PaneValues.BottomLeft, ActiveCell = "A2", SequenceOfReferences = new ListValue<StringValue>() InnerText = "A2:XFD2" ;
sheetView.Append(pane);
sheetView.Append(selection);


Here is an example of how to freeze the first column.

var sheetViews = new SheetViews();
var sheetView = new SheetView() TabSelected = true, WorkbookViewId = (UInt32Value)0U ;
var pane = new Pane() ActivePane = PaneValues.TopRight, HorizontalSplit = 1D, State = PaneStateValues.Frozen, TopLeftCell = "B1" ;
var selection = new Selection() Pane = PaneValues.TopRight ;
sheetView.Append(pane);
sheetView.Append(selection);
sheetViews.Append(sheetView);


Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.