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.
谁能告诉我我的代码中缺少什么吗?它运行时没有任何错误，但不会写入我的Excel文件。我只是想让它在我的电子表格的商业选项卡上写入到单元格A1。
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
}
}
}
1 个解决方案
#1
1
The problem is that you have not created a SheetData object. Here is what you have to do.
问题是您没有创建一个SheetData对象。这就是你要做的。
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.
注意，这只是为了显示必须如何创建它，方法“GetCell”应该检查工作表和SheetData是否已经存在，如果不存在，则创建它们。
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();
}
}
#1
1
The problem is that you have not created a SheetData object. Here is what you have to do.
问题是您没有创建一个SheetData对象。这就是你要做的。
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.
注意，这只是为了显示必须如何创建它，方法“GetCell”应该检查工作表和SheetData是否已经存在，如果不存在，则创建它们。
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();
}
}