UWP OpenXML 未将更改写入电子表格文档

Question

我有一个 UWP 应用程序。我从 SharePoint 驱动器中取出一个 excel 文件，将其更改为字节数组，然后将其保存到我的硬盘驱动器中。编辑所以我意识到此时我已经打开了文件，因此无需再次打开它。所以我做了一些修改（这次是全班）：class FileHelper{    public static string saveLocation;    public static SpreadsheetDocument spreadsheetDoc;    public static async void GetFileAsync()    {        var (authResult, message) = await Authentication.AquireTokenAsync();        var httpClient = new HttpClient();        HttpResponseMessage response;        var request = new HttpRequestMessage(HttpMethod.Get, MainPage.fileurl);        request.Headers.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", authResult.AccessToken);        response = await httpClient.SendAsync(request);        byte[] fileBytes = await response.Content.ReadAsByteArrayAsync();        StorageLibrary videoLibrary = await StorageLibrary.GetLibraryAsync(KnownLibraryId.Videos);        string saveFolder = videoLibrary.SaveFolder.Path;        string saveFileName = App.Date + "-" + App.StartTime + "-" + App.IBX + "-" + App.Generator + ".xlsx";        saveLocation = saveFolder + "\\" + saveFileName;        using (MemoryStream stream = new MemoryStream())        {            stream.Write(fileBytes, 0, (int)fileBytes.Length);            using (spreadsheetDoc = SpreadsheetDocument.Open(stream, true))            {                UpdateCell(spreadsheetDoc, App.Date, 2, "D");                await Task.Run(() =>                {                    File.WriteAllBytes(saveLocation, stream.ToArray());                });            }        }               }写入文件，但不使用 UpdateCell() 调用的数据输入更新文件。

Answer 1

好的....这就是我最终的结果。它可能不漂亮，但它确实有效。

首先，我更改了单击事件以将更新与模板文件的传输分开。

    private async void FileButton_Click(object sender, RoutedEventArgs e)

    {

        await FileHelper.GetFileAsync();

        await FileHelper.UpdateCell(FileHelper.saveLocation, App.Date, 2, "D");

        await FileHelper.UpdateCell(FileHelper.saveLocation, App.Maximo, 3, "D");

        ...

        await FileHelper.UpdateCell(FileHelper.saveLocation, App.StopHours, 26, "J");

    }

然后我将 GetFileAsync() 和 UpdateCell() 都更新为 Task 而不是 void。然后，当我得到 await Task.Run 时，我添加了一个返回 TaskStatus.RanToCompletion。

class FileHelper

{

    public static string saveLocation;

    public static SpreadsheetDocument spreadsheetDoc;

    public static async Task GetFileAsync()

    {

        var (authResult, message) = await Authentication.AquireTokenAsync();

        var httpClient = new HttpClient();

        HttpResponseMessage response;

        var request = new HttpRequestMessage(HttpMethod.Get, MainPage.fileurl);

        request.Headers.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", authResult.AccessToken);

        response = await httpClient.SendAsync(request);

        byte[] fileBytes = await response.Content.ReadAsByteArrayAsync();

        StorageLibrary videoLibrary = await StorageLibrary.GetLibraryAsync(KnownLibraryId.Videos);

        string saveFolder = videoLibrary.SaveFolder.Path;

        string saveFileName = App.Date + "-" + App.StartTime + "-" + App.IBX + "-" + App.Generator + ".xlsx";

        saveLocation = saveFolder + "\\" + saveFileName;

        using (MemoryStream stream = new MemoryStream())

        {

            stream.Write(fileBytes, 0, (int)fileBytes.Length);

            using (spreadsheetDoc = SpreadsheetDocument.Open(stream, true))

            {

                await Task.Run(() =>

                {

                    File.WriteAllBytes(saveLocation, stream.ToArray());

                    return TaskStatus.RanToCompletion;

                });                    

            }

        }           

    }

    public async static Task UpdateCell(string docName, string text,

        uint rowIndex, string columnName)

    {

        StorageLibrary videoLibrary = await StorageLibrary.GetLibraryAsync(KnownLibraryId.Videos);

        string saveFolder = videoLibrary.SaveFolder.Path;

        string saveFileName = App.Date + "-" + App.StartTime + "-" + App.IBX + "-" + App.Generator + ".xlsx";

        saveLocation = saveFolder + "\\" + saveFileName;

        await Task.Run(() =>

        {

            using (spreadsheetDoc = SpreadsheetDocument.Open(saveLocation, true))

            {

                WorksheetPart worksheetPart =

                    GetWorksheetPartByName(spreadsheetDoc, "GenRun");

                if (worksheetPart != null)

                {

                    Cell cell = GetCell(worksheetPart.Worksheet,

                                                columnName, rowIndex);

                    cell.CellValue = new CellValue(text);

                    cell.DataType =

                        new EnumValue<CellValues>(CellValues.String);

                    worksheetPart.Worksheet.Save();

                }

            }

            return TaskStatus.RanToCompletion;

        });                              

    }

    private static WorksheetPart

         GetWorksheetPartByName(SpreadsheetDocument document,

         string sheetName)

    {

        IEnumerable<Sheet> sheets =

           document.WorkbookPart.Workbook.GetFirstChild<Sheets>().

           Elements<Sheet>().Where(s => s.Name == sheetName);

        if (sheets.Count() == 0)

        {

            return null;

        }

        string relationshipId = sheets.First().Id.Value;

        WorksheetPart worksheetPart = (WorksheetPart)

             document.WorkbookPart.GetPartById(relationshipId);

        return worksheetPart;

    }

    private static Cell GetCell(Worksheet worksheet,

              string columnName, uint rowIndex)

    {

        Row row = GetRow(worksheet, rowIndex);

        if (row == null)

            return null;

        return row.Elements<Cell>().Where(c => string.Compare

               (c.CellReference.Value, columnName +

               rowIndex, true) == 0).First();

    }

    private static Row GetRow(Worksheet worksheet, uint rowIndex)

    {

        return worksheet.GetFirstChild<SheetData>().

          Elements<Row>().Where(r => r.RowIndex == rowIndex).First();

    }

}