c# - 从右到左打开 XML Sheetview

Question

我需要在 ExportToExcel 类中添加 SheetView 设置。但导出后，即使创建了工作表并设置了 SheetView 设置，它仍显示 Excel 单元格内容为空。我检查了这个网站和其他网站上的所有帖子，但没有成功。每次我收到文档损坏消息或电子表格为空时。

private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
{
    spreadsheet.AddWorkbookPart();
    spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

    spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

    WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
    //Stylesheet stylesheet = new Stylesheet();
    workbookStylesPart.Stylesheet = CreateStylesheet();

    uint worksheetNumber = 1;
    Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
    foreach (DataTable dt in ds.Tables)
    {
        string worksheetName = dt.TableName;
        WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
        Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = worksheetNumber, Name = worksheetName };
        newWorksheetPart.Worksheet = new Worksheet(new SheetViews(new SheetView() { WorkbookViewId=0,RightToLeft=true}),new SheetData());
        newWorksheetPart.Worksheet.Save();

        sheets.Append(sheet);

        WriteDataTableToExcelWorksheet(dt, newWorksheetPart);

        worksheetNumber++;
    }
    spreadsheet.WorkbookPart.Workbook.Save();
}

private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
{
    OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII);
    writer.WriteStartElement(new Worksheet());
    writer.WriteStartElement(new SheetData());

    string cellValue = "";

    int numberOfColumns = dt.Columns.Count;
    bool[] IsNumericColumn = new bool[numberOfColumns];
    bool[] IsDateColumn = new bool[numberOfColumns];

    string[] excelColumnNames = new string[numberOfColumns];
    for (int n = 0; n < numberOfColumns; n++)
        excelColumnNames[n] = GetExcelColumnName(n);

    //
    //  Create the Header row in our Excel Worksheet
    //
    uint rowIndex = 1;

    writer.WriteStartElement(new Row { RowIndex = rowIndex });
    for (int colInx = 0; colInx < numberOfColumns; colInx++)
    {
        DataColumn col = dt.Columns[colInx];
        AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
        IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
        IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
    }
    writer.WriteEndElement();   //  End of header "Row"

    double cellNumericValue = 0;
    foreach (DataRow dr in dt.Rows)
    {
        ++rowIndex;

        writer.WriteStartElement(new Row { RowIndex = rowIndex });
        for (int colInx = 0; colInx < numberOfColumns; colInx++)
        {
            cellValue = dr.ItemArray[colInx].ToString();
            cellValue = ReplaceHexadecimalSymbols(cellValue);
            if (IsNumericColumn[colInx])
            {
                cellNumericValue = 0;
                if (double.TryParse(cellValue, out cellNumericValue))
                {
                    cellValue = cellNumericValue.ToString();
                    AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
                }
            }
            else if (IsDateColumn[colInx])
            {
                //  This is a date value.
                DateTime dtValue;
                string strValue = "";
                if (DateTime.TryParse(cellValue, out dtValue))
                    strValue = dtValue.ToShortDateString();
                AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer);
            }
            else
            {
                AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
            }
        }
        writer.WriteEndElement(); //  End of Row
    }
    writer.WriteEndElement(); //  End of SheetData
    writer.WriteEndElement(); //  End of worksheet

    writer.Close();
}

Answer 1

使工作表从右到左阅读的代码是正确的。但是，WriteDataTableToExcelWorksheet 和 CreateStylesheet 的代码并不完整，存在一些问题。

我能够在 this link 找到 WriteDataTableToExcelWorksheet 的原始代码.然后，我使用 Open XML Productivity Tool 将一个普通的 Excel 文件反向工程为 C# 代码，并重新使用一种方法来创建一个新的样式表。

我用这段代码生成的 excel 文件如下所示:

I've pushed this new code to GitHub .有一个包装 WPF 应用程序可指导在您的计算机上的何处生成文件。

这是一个guide on installing the Open XML Productivity Tool .