c# - 如何在电子表格的一页上放置所有列?
问题描述
我已将报告导出到 Excel,它工作正常,但是当我打印文件时,电子表格的宽度并不适合所有列到一页中。为此,我必须更改页面布局,并将缩放设置为宽度为 1,高度为 43。我怎样才能从代码中得到这个?
using (var workbook = SpreadsheetDocument.Create(Savepath, SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new Workbook();
workbook.WorkbookPart.Workbook.Sheets = new Sheets();
//declare our MergeCells here
MergeCells mergeCells = null;
foreach (DataRow dsrow in table.Rows)
{
int innerColIndex = 0;
rowIndex++;
Row newRow = new Row();
foreach (String col in columns)
{
Stylesheet stylesheet1 = new Stylesheet();
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString());
cell.CellReference = excelColumnNames[innerColIndex] + rowIndex.ToString();
if (table.TableName == "Work Order Report")
{
string cellNameWorkOrder = dsrow[col].ToString();
if (cellNameWorkOrder == "POSTER: 10% MUST HAVE APPROACH AND CLOSE-UP SHOTS - PHOTO OF EACH CREATIVE" || cellNameWorkOrder == "BULLETINS: 100% CLOSE-UP AND APPROACH OF EACH UNIT")
{
if (mergeCells == null)
mergeCells = new MergeCells();
var cellAddress = cell.CellReference;
var cellAddressTwo = "I" + rowIndex.ToString();
mergeCells.Append(new MergeCell() { Reference = new StringValue(cellAddress + ":" + cellAddressTwo) });
}
}
newRow.AppendChild(cell);
innerColIndex++;
}
sheetData.AppendChild(newRow);
}
//add the mergeCells to the worksheet if we have any
if (mergeCells != null)
sheetPart.Worksheet.InsertAfter(mergeCells, sheetPart.Worksheet.Elements<SheetData>().First());
}
workbook.WorkbookPart.Workbook.Save();
}
当我现在打印时,excel 报告看起来像https://www.screencast.com/t/CCMR96Mw7u它像https://www.screencast.com/t/MkTpDc98RD0l,https://www.screencast.com/t/ MRyzpEiFICM预期结果是https://www.screencast.com/t/ztgvm6mISSwp
解决方案
为了实现这一点,您需要做两件事。首先,您需要将一个PageSetupProperties
实例添加到一个SheetProperties
实例中,该实例又应该添加到您的Worksheet
. PageSetupProperties
有一个FitToPage
属性,它是将 Excel 中的单选按钮设置为“适合”的部分。
接下来,您需要使用PageSetup
该类来设置所需的宽度和高度。这是通过FitToWidth
andFitToHeight
属性完成的。PageSetup
也需要添加Worksheet
到.
请注意,元素的顺序很重要,您可以在 ECMA 规范中看到正确的顺序。
以下是一个自包含的示例,它添加一个单元格,然后按照您需要的方式设置属性:
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = myDoc.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
SheetData sheetData = new SheetData();
//add a row
Row row = new Row();
row.RowIndex = 1;
//create a cell
Cell cell = new Cell();
cell.CellReference = "A1";
CellValue cellValue = new CellValue();
cellValue.Text = "123";
cell.Append(cellValue);
row.AppendChild(cell);
sheetData.AppendChild(row);
// Add a WorkbookPart to the document.
worksheetPart.Worksheet = new Worksheet(sheetData);
//this sets the "Fit to" radio in Excel.
//note this must come before the SheetData
SheetProperties sheetProperties = new SheetProperties();
PageSetupProperties pageSetupProperties = new PageSetupProperties() { FitToPage = true };
sheetProperties.Append(pageSetupProperties);
worksheetPart.Worksheet.InsertBefore(sheetProperties, sheetData);
// this changes the fit to width and height
PageSetup pageSetup = new PageSetup() { FitToWidth = 1, FitToHeight = 43 };
worksheetPart.Worksheet.AppendChild(pageSetup);
//append the sheets / sheet
Sheets sheets = myDoc.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet()
{
Id = myDoc.WorkbookPart.GetIdOfPart(myDoc.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = "Sheet1"
});
}
推荐阅读
- html - 引导间距问题
- python - join() 参数必须是 str 或 bytes,而不是 'DataFrame' 错误
- db2 - liquibase.exception.DatabaseException:[SQL7008]
- ios - 如何在 WatchKit 中检查 RTL 语言
- sql - 需要限制结果,同时包括对排除值的评估
- eclipse - 如何在 macOS Big Sur 上的 Eclipse 12s IDE(科学)中构建 fortran 代码
- vue.js - 通过提供使用异步数据
- python - 用带有python列表的for语句替换while循环
- php - 有 json_encode 但有线路返回
- json - 如何以这种格式将数据从 json 文件导入 PostgreSQL?