c# – 在Openxml中冻结窗格和列
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了c# – 在Openxml中冻结窗格和列，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6963字，纯文字阅读大概需要10分钟。
内容图文
我需要帮助.
我有一个问题要问使用OpenXMLWriter.
我目前正在使用下面的代码来创建我的excel文件,但我想设置列的宽度和冻结窗格.我该怎么办？
因为我已经为此编写了以下代码.我不知道为什么不工作.
示例非常有用.感谢它,谢谢！
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;
}
我需要请.请帮我….
解决方法:
这让我有点过去了.您必须在数据之前将视图添加到工作表.你可以尝试这样的事情：
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 = $"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);
#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;
}
如果您的SheetView不起作用,我提供了一个适合我的示例：
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);
内容总结
以上是互联网集市为您收集整理的c# – 在Openxml中冻结窗格和列全部内容，希望文章能够帮你解决c# – 在Openxml中冻结窗格和列所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】