我需要帮助。
我有一个问题要使用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;
}
我需要帮助。请帮帮我...
这段内容与IT技术无关。