Freeze Pane and Columns in Openxml

Viewed 3227

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 = $"A{freezeRow + 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

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);
Related