OpenXML库保存excel文件

Question

我有下面的代码，它没有将值保存到单元格中，也保存在文件中。它在cell.cellvalue字段中显示了值，但它没有将其写入excel。我不知道如何保存这个文件。我使用OpenXml，并将datatable值写入创建的电子表格文档的每个cell/row。

 using (SpreadsheetDocument ssd=SpreadsheetDocument.Open(Server.MapPath(@"\ExcelPackageTemplate.xlsx"),true))
        {
            WorkbookPart wbPart = ssd.WorkbookPart;
            WorksheetPart worksheetPart = wbPart.WorksheetParts.First();

            SheetData sheetdata = worksheetPart.Worksheet.GetFirstChild<SheetData>();
            string[] headerColumns = new string[] { dt.Columns[0].ColumnName, dt.Columns[1].ColumnName,dt.Columns[2].ColumnName };
            DocumentFormat.OpenXml.Spreadsheet.Row r = new DocumentFormat.OpenXml.Spreadsheet.Row();
            int RowIndexer = 1;
            //int colInd=0;
            r.RowIndex = (UInt32)RowIndexer;

            string test = ColumnName(RowIndexer);

            foreach (DataColumn dc in dt.Columns)
            {

                DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                cell.CellReference = test+RowIndexer;
                cell.DataType = CellValues.InlineString;
                cell.InlineString = new InlineString(new Text(dc.ColumnName.ToString()));
                DocumentFormat.OpenXml.Spreadsheet.CellValue value = new DocumentFormat.OpenXml.Spreadsheet.CellValue();

                r.AppendChild(cell);

                // colInd++;
            }

            //r.RowIndex = (UInt32)RowIndexer;
            RowIndexer = 2;
            foreach (DataRow dr in dt.Rows)
            {
                DocumentFormat.OpenXml.Spreadsheet.Row Row = new DocumentFormat.OpenXml.Spreadsheet.Row();
                string Index = ColumnName(RowIndexer);
                Row.RowIndex = (UInt32)RowIndexer;
                foreach (object value in dr.ItemArray)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.DataType = CellValues.InlineString;
                    cell.InlineString =  new InlineString(new Text(value.ToString()));
                    cell.CellReference = Index+RowIndexer;
                   // cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value.ToString());
                    Row.AppendChild(cell);

                }
                RowIndexer++;

            }

            worksheetPart.Worksheet.Save();
            wbPart.Workbook.Save();
            ssd.Close();

Answer 1

试试这个：

using (SpreadsheetDocument ssd = SpreadsheetDocument.Open(Server.MapPath(@"\ExcelPackageTemplate.xlsx"), true))
{
    WorkbookPart wbPart = ssd.WorkbookPart;
    WorksheetPart worksheetPart = wbPart.WorksheetParts.First();

    SheetData sheetdata = worksheetPart.Worksheet.GetFirstChild<SheetData>();
    string[] headerColumns = new string[] { dt.Columns[0].ColumnName, dt.Columns[1].ColumnName, dt.Columns[2].ColumnName };
    DocumentFormat.OpenXml.Spreadsheet.Row r = new DocumentFormat.OpenXml.Spreadsheet.Row();
    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
    int RowIndexer = 1;
    int ColumnIndexer = 1;

    r.RowIndex = (UInt32)RowIndexer;
    foreach (DataColumn dc in dt.Columns)
    {
        cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
        cell.CellReference = ColumnName(ColumnIndexer) + RowIndexer;
        cell.DataType = CellValues.InlineString;
        cell.InlineString = new InlineString(new Text(dc.ColumnName.ToString()));
        // consider using cell.CellValue. Then you don't need to use InlineString.
        // Because it seems you're not using any rich text so you're just bloating up
        // the XML.

        r.AppendChild(cell);

        ColumnIndexer++;
    }
    // here's the missing part you needed
    sheetdata.Append(r);

    RowIndexer = 2;
    foreach (DataRow dr in dt.Rows)
    {
        r = new DocumentFormat.OpenXml.Spreadsheet.Row();
        r.RowIndex = (UInt32)RowIndexer;
        // this follows the same starting column index as your column header.
        // I'm assuming you start with column 1. Change as you see fit.
        ColumnIndexer = 1;
        foreach (object value in dr.ItemArray)
        {
            cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
            // I moved it here so it's consistent with the above part
            // Also, the original code was using the row index to calculate
            // the column name, which is weird.
            cell.CellReference = ColumnName(ColumnIndexer) + RowIndexer;
            cell.DataType = CellValues.InlineString;
            cell.InlineString = new InlineString(new Text(value.ToString()));

            r.AppendChild(cell);
            ColumnIndexer++;
        }
        RowIndexer++;

        // missing part
        sheetdata.Append(r);
    }

    worksheetPart.Worksheet.Save();
    wbPart.Workbook.Save();
    ssd.Close();
}

一些评论：

• ColumnName()函数来自here。
• 我假设您希望列标题位于一行，而数据位于后续行中(因为原始代码的行索引用于计算列名)。
• 清理代码的某些部分，这样更容易阅读，而且列标题和数据行部分在写作风格上更加一致。
• 我建议您考虑使用CellValue而不是InlineString。根据您的代码，您正在导入一个DataTable，并且您似乎不需要丰富的文本，因此InlineString的代码有点过分，可能会使结果文件更大(臃肿的XML)。使用一种或另一种，并记住相应地设置DataType。
• 此外，该代码仅适用于完全空的SheetData。

Answer 2

这里有一些可能的问题：

• 创建并添加新的行和单元格:它假定打开工作表时工作表完全为空白(即:没有具有相同索引/地址的行或单元格)。
• 由于您将CellType设置为InlineString，所以需要设置Cell.InlineString而不是Cell.CellValue cell.DataType = CellValues.InlineString；cell.InlineString =新InlineString(新文本(value.TsString()；
• 此外，您的代码中有一些与CellReference的使用有关的错误。这些代码部分没有意义： cell.CellReference = dc.ColumnName.ToString()；cell.CellReference = value.ToString()； 单元格地址应该类似于"A1“。您必须检查您的代码，以设置正确的地址。你已经有了rowIndex。你需要得到列名。这个Translate a column index into an Excel Column Name可以帮上忙。