Запись в существующий файл Excel с использованием OpenXML ⇐ C#

[Anonymous]

Может кто-нибудь сказать мне, чего мне не хватает в моем коде? Он работает без каких-либо ошибок, но не записывает данные в мой файл Excel. Я просто пытаюсь заставить его записать в ячейку A1 на вкладке «Коммерческий» моей электронной таблицы.

Мой код приведен ниже:

Код: Выделить всё

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.IO;
using System.Reflection;
using System.Data.OleDb;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Xml;
using System.Diagnostics;

namespace Application.Model
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public class TempCode :         Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
string FilePath;
string FileName;
string FileExceptionsPath;
string FileExceptionsName;
string Exceptions = "";

public void Main()
{
try
{
FilePath = (string)Dts.Variables["FilePath"].Value;
FileName = (string)Dts.Variables["User::FileName"].Value;
FileExceptionsPath = (string)Dts.Variables["FileExceptionsPath"].Value;
FileExceptionsName = (string)Dts.Variables["User::FileExceptionsName"].Value;
Dts.Variables["User::FileAbsolutePath"].Value = (string)Dts.Variables["FilePath"].Value + (string)Dts.Variables["User::FileName"].Value;
Dts.Variables["User::FileAbsoluteExceptionsPath"].Value = (string)Dts.Variables["FileExceptionsPath"].Value + (string)Dts.Variables["User::FileExceptionsName"].Value;

CreateExcel(new Object(), new EventArgs());

Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Events.FireError(0, "File Task", ex.Message, String.Empty, 0);

Dts.TaskResult = (int)ScriptResults.Failure;
}
}

public void CreateExcel(object sender, EventArgs e)
{
Directory.CreateDirectory(FilePath);

string fileTest = FilePath + FileName;
if (File.Exists(fileTest))
{
File.Delete(fileTest);
}
string templateTest = FilePath + "Test.xlsx";

CopyFile(templateTest, fileTest);

UpdateCell(fileTest, "120", 1, "A");

if (Exceptions != "")
{
LogExceptions(FileExceptionsPath, FileExceptionsName, Exceptions);
}
}

public void LogExceptions(string FileExceptionsPath, string FileExceptionsName, string data)
{
Directory.CreateDirectory(FileExceptionsPath);
using (var writer = new StreamWriter(FileExceptionsPath + FileExceptionsName))
{
List exceptionsList = data.Split('~').ToList();

foreach (var ex in exceptionsList)
{
writer.WriteLine(ex, true);
}
}
}

private string CopyFile(string source, string dest)
{
string result = "Copied file";
try
{
File.Copy(source, dest, true);
}
catch (Exception ex)
{
result = ex.Message;
}
return result;
}

public static void UpdateCell(string docName, string text, uint rowIndex, string columnName)
{
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
{
WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, "Commercial");
if (worksheetPart != null)
{
Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex);
cell.CellValue = new CellValue(text);
cell.DataType = new EnumValue(CellValues.Number);
worksheetPart.Worksheet.Save();
}
spreadSheet.WorkbookPart.Workbook.Save();
}

}

private static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
{
IEnumerable sheets = document.WorkbookPart.Workbook.GetFirstChild().
Elements().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;
string cellReference = columnName + rowIndex;
if (worksheet.Elements().Where(r => r.RowIndex == rowIndex).Count() != 0)
row = worksheet.GetFirstChild().Elements().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
else
{
row = new Row() { RowIndex = rowIndex };
worksheet.Append(row);
}

if (row == null)
return null;

if (row.Elements().Where(c => c.CellReference.Value == cellReference).Count() > 0)
{
return row.Elements().Where(c => c.CellReference.Value == cellReference).First();
}
else
{
Cell refCell = null;
foreach (Cell cell in row.Elements())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
Cell newCell = new Cell()
{
CellReference = cellReference,
StyleIndex = (UInt32Value)1U

};
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;
}
}

#region ScriptResults declaration
/// 
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// 
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

}

Подробнее здесь: https://stackoverflow.com/questions/429 ... ng-openxml