Write to Existing Excel File Using OpenXML

Question

Can anyone please tell me what I am missing in my code? It runs without any errors, but it will not write to my Excel file. I am just trying to get it to write to cell A1 on the Commercial tab of my spreadsheet.

My code is below:

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<string> 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>(CellValues.Number);
                worksheetPart.Worksheet.Save();
            }
            spreadSheet.WorkbookPart.Workbook.Save();
        }

    }

    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;
        string cellReference = columnName + rowIndex;
        if (worksheet.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            row = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
        else
        {
            row = new Row() { RowIndex = rowIndex };
            worksheet.Append(row);
        }

        if (row == null)
            return null;

        if (row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).Count() > 0)
        {
            return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
        }
        else
        {
            Cell refCell = null;
            foreach (Cell cell in row.Elements<Cell>())
            {
                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
    /// <summary>
    /// This enum provides a convenient shorthand within the scope of this class for setting the
    /// result of the script.
    /// 
    /// This code was generated automatically.
    /// </summary>
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion

}

}

Answer 1

The problem is that you have not created a SheetData object. Here is what you have to do.

Note that this is just to show how it must be created, the method 'GetCell' should check if the Worksheet and the SheetData already exist and, if not, create them.

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)
        {
            // Create new Worksheet
            Worksheet worksheet = new Worksheet();
            worksheetPart.Worksheet = worksheet;

            // Create new SheetData
            SheetData sheetData = new SheetData();

            // Create new row
            Row row = new Row(){ RowIndex = rowIndex };

            // Create new cell
            Cell cell = new Cell() { CellReference = columnName + rowIndex, DataType = CellValues.Number, CellValue = new CellValue(text) };

            // Append cell to row
            row.Append(cell);

            // Append row to sheetData
            sheetData.Append(row);

            // Append sheetData to worksheet
            worksheet.Append(sheetData);

            worksheetPart.Worksheet.Save();
        }
        spreadSheet.WorkbookPart.Workbook.Save();
    }

}