c# - 使用 Openxml sdk 合并 Excel 工作表中的单元格
问题描述
我正在使用 OpenXML sdk 将数据从列表导出到 Excel 工作表。数据完成后,我想在最后一行下方合并 10 行。
string templatePath = Server.MapPath("~/Content/Reports/Formate/someReport.xlsx");
string download_file_name = "Some__Report_" + Guid.NewGuid().ToString() + ".xlsx";
string newFilePath = Server.MapPath("~/Content/Reports/TempData/" + download_file_name);
System.IO.File.Copy(templatePath, newFilePath, true);
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(newFilePath, true);
var sheet = spreadsheetDocument.WorkbookPart.Workbook
.Sheets.Elements<Sheet>()
.FirstOrDefault();
var sheetReferenceId = sheet.Id;
// Map the Id to the worksheet part
WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheetReferenceId);
var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
uint rowNumber = 12;
string[] cellReferences = { "A", "B", "C", "D", "E" };
foreach (var item in someList)
{
var row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowNumber).FirstOrDefault();
if (row == null)
{
row = new Row();
row.RowIndex = rowNumber;
sheetData.Append(row);
}
if (row != null)
{
for (int j = 0; j < cellReferences.Length; j++)
{
string cellReference = cellReferences[j] + rowNumber.ToString();
// Find cell in row
var cell = row.Elements<Cell>()
.Where(c => c.CellReference == cellReference)
.FirstOrDefault();
if (cell == null)
{
cell = new Cell();
cell.CellReference = cellReference;
row.InsertAt(cell, j);
}
if (cell != null)
{
switch (cellReferences[j])
{
case "A":
cell.CellValue = new CellValue(item.AssignDate.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.String);
break;
case "B":
cell.CellValue = new CellValue(item.AssetTypeName);
cell.DataType = new EnumValue<CellValues>(CellValues.String);
break;
case "C":
cell.CellValue = new CellValue(item.Model);
cell.DataType = new EnumValue<CellValues>(CellValues.String);
break;
case "D":
cell.CellValue = new CellValue(item.SubscriberNo.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.String);
break;
case "E":
cell.CellValue = new CellValue(item.IMEI);
cell.DataType = new EnumValue<CellValues>(CellValues.String);
break;
}
}
}
rowNumber++;
}
}
//I want here to merge 10 rows below rowNumber
spreadsheetDocument.Close();
//string download_path = string.Format("{0}://{1}{2}", Request.Url.Scheme, Request.Url.Authority, Url.Content("~")) + "/Content/Reports/TempData/" + download_file_name;
this.Response.ClearContent();
this.Response.Clear();
this.Response.AddHeader("Content-Disposition", "attachment; filename=Assets_Report_" + DateTime.Now.ToShortDateString().Replace('/', '-') + ".xlsx");
this.Response.ContentType = "application/vnd.ms-excel.12";
this.Response.TransmitFile(newFilePath);
this.Response.Flush();
this.Response.End();
我已经尝试过使用 OpenXML 创建合并单元格 的解决方案,但它对我不起作用。我所做的是:
Row firstRow = new Row();
firstRow.RowIndex = (UInt32)rowNumber+1;
//create a cell in C1 (the upper left most cell of the merged cells)
Cell dataCell = new Cell();
dataCell.CellReference = "A" + firstRow.RowIndex.ToString(); ;
CellValue cellValue = new CellValue();
cellValue.Text = "";
dataCell.Append(cellValue);
firstRow.AppendChild(dataCell);
sheetData.AppendChild(firstRow);
// Add a WorkbookPart to the document.
worksheetPart.Worksheet = new Worksheet(sheetData);
//create a MergeCells class to hold each MergeCell
MergeCells mergeCells = new MergeCells();
//append a MergeCell to the mergeCells for each set of merged cells
mergeCells.Append(new MergeCell() { Reference = new StringValue(""+dataCell.CellReference+":F"+(firstRow.RowIndex+10)) });
worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetData>().First());
//this is the part that was missing from your code
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(spreadsheetDocument.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = "Sheet1"
});
我在这里遇到异常无法插入 OpenXmlElement “newChild”,因为它是树的一部分。
解决方案
我已经尝试过使用 OpenXML 创建合并单元格的解决方案,但它对我不起作用。
这是因为该解决方案是从头开始创建一个新文件,但您正在编辑一个现有文件。您不需要正在创建的代码部分Worksheets
等Sheets
。您只需要合并单元格代码:
Row firstRow = new Row();
firstRow.RowIndex = (UInt32)rowNumber + 1;
//create a cell in C1 (the upper left most cell of the merged cells)
Cell dataCell = new Cell();
dataCell.CellReference = "A" + firstRow.RowIndex.ToString(); ;
CellValue cellValue = new CellValue();
cellValue.Text = "";
dataCell.Append(cellValue);
firstRow.AppendChild(dataCell);
sheetData.AppendChild(firstRow);
//create a MergeCells class to hold each MergeCell
MergeCells mergeCells = new MergeCells();
//append a MergeCell to the mergeCells for each set of merged cells
mergeCells.Append(new MergeCell() { Reference = new StringValue("" + dataCell.CellReference + ":F" + (firstRow.RowIndex + 10)) });
worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetData>().First());
推荐阅读
- ios - SwiftUI - 使用淡入淡出动画更改文本
- azureservicebus - QueueClient 获取预定消息
- shell - 如何在以更高权限运行的 Window 上设置 HWND_TOPMOST?
- javascript - 循环遍历对象数组并使用 Javascript 在 HTML 中一次仅显示对象属性
- python - 如何将数据从 csv 文件导入和导出到 HTML?
- node.js - 使用 Node.js 和 MongoDB 限制返回的文档
- r - 要替换的项目数不是替换长度错误的倍数
- javascript - 使用 function-plot.js 绘制指数函数
- java - 在Java中将父类转换为子类
- c# - 是否可以在一定天数后删除特定的 blob?Azure Blob 存储