代码之家 › 专栏 › 技术社区 › Simant

使用OpenXML用目录文件填充excel工作表列

openxml .net c#

Simant · 技术社区 · 8 年前

“文件”表为:

我的代码是:

//Open the Excel file in Read Mode using OpenXML
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(@"C:\TouristRecord.xlsx", true))
{
    WorksheetPart documents = GetWorksheetPart(doc.WorkbookPart, "Documents");
    Worksheet documentsWorksheet = documents.Worksheet;
    IEnumerable<Row> documentsRows = documentsWorksheet.GetFirstChild<SheetData>().Descendants<Row>();

    //Loop through the Worksheet rows
    foreach (var files in Directory.GetFiles(@"C:\DocumentsFolder"))
    {
        foreach (Row row in documentsRows)
        {                           
            // I am unable to write logic to update the excel sheet value here.
        }
    }
    doc.Save();
}

public WorksheetPart GetWorksheetPart(WorkbookPart workbookPart, string sheetName)
{
    string relId = workbookPart.Workbook.Descendants<Sheet>().First(s => sheetName.Equals(s.Name)).Id;
    return (WorksheetPart)workbookPart.GetPartById(relId);
}

1 回复 | 直到 8 年前

petelids 8 年前

要将单元格添加到C3,您需要创建一个新的 Cell 对象,为其分配一个单元格引用C3,设置其值,然后将其添加到 Row 表示图纸上的第3行。我们可以将该逻辑封装到如下方法中:

private void AddCellToRow(Row row, string value, string cellReference)
{
    //the cell might already exist, if it does we should use it.
    Cell cell = row.Descendants<Cell>().FirstOrDefault(c => c.CellReference == cellReference);
    if (cell == null)
    {
        cell = new Cell();
        cell.CellReference = cellReference;
    }
    cell.CellValue = new CellValue(value);
    cell.DataType = CellValues.String;
    row.Append(cell);
}

如果我们假设当前工作表有一组连续的行,那么写什么的逻辑非常简单:

迭代文档中的每一行
检查行索引是否大于2(因为您希望从3开始写入)。如果是:
- 抓住第三个如果它不存在,也可以创建它。
- 将文件列表的第n个元素添加到 单间牢房 .
- 增量n
迭代文件列表中的其余文件(因为原始文档中的文件可能比行多)。对于每一个:
- 添加新的 一行
- 添加新的 单间牢房 到

将其转化为代码,最终得到:

using (SpreadsheetDocument doc = SpreadsheetDocument.Open(@"C:\TouristRecord.xlsx", true))
{
    WorksheetPart documents = GetWorksheetPart(doc.WorkbookPart, "Documents");
    //get the she sheetdata as that's where we need to add rows
    SheetData sheetData = documents.Worksheet.GetFirstChild<SheetData>();
    IEnumerable<Row> documentsRows = sheetData.Descendants<Row>();
    //get all of the files into an array
    var filenames = Directory.GetFiles(@"C:\DocumentsFolder");

    if (filenames.Length > 0)
    {
        int currentFileIndex = 0;

        // keep the row index in case the rowindex property is null anywhere
        // the spec allows for it to be null, in which case the row
        // index is one more than the previous row (or 1 if this is the first row)
        uint currentRowIndex = 1;

        foreach (var documentRow in documentsRows)
        {
            if (documentRow.RowIndex.HasValue)
            {
                currentRowIndex = documentRow.RowIndex.Value;
            }
            else
            {
                currentRowIndex++;
            }

            if (currentRowIndex <= 2)
            {
                //this is row 1 or 2 so we can ignore it
                continue;
            }

            AddCellToRow(documentRow, filenames[currentFileIndex], "C" + currentRowIndex);

            currentFileIndex++;

            if (filenames.Length <= currentFileIndex)
            {
                // there are no more files so we can stop
                break;
            }
        }

        // now output any files we haven't already output. These will need a new row as there isn't one
        // in the document as yet.
        for (int i = currentFileIndex; i < filenames.Length; i++)
        {
            //there are more files than there were rows in the directory, add more rows
            Row row = new Row();
            currentRowIndex++;
            row.RowIndex = currentRowIndex;

            AddCellToRow(row, filenames[i], "C" + currentRowIndex);
            sheetData.Append(row);
        }
    }
}

上面假设当前工作表有一组连续的行。这可能并不总是正确的,因为规范允许不将空行写入XML。在这种情况下,您的输出可能会出现差距。假设原始文件在第1、2和5行中有数据;在这种情况下,foreach将导致您跳过写入第3行和第4行。这可以通过检查 currentRowIndex 在循环内并添加新的 一行 对于可能出现的任何间隙。我没有添加该代码,因为这是一个复杂的问题,有损于答案的基本原理。