This project has moved. For the latest updates, please go here.

Add an array of values

Jan 18, 2011 at 12:04 PM

Hi All, I am having difficulty understanding how to add an array of values to an existing template file.

I have downloaded the EPPlusSamples solution but couldn't see an example of how to do this.

Is it even possible?

Below is what I've got so far, that doesn't work:

interal static void WriteToExcel(FileInfo template, DirectoryInfo outputDir, object[] header, Array arr3)
{
using (var p = new ExcelPackage(template))
{
var ws = p.Workbook.Worksheets["BoM"];
ExcelRange range = ws.Cells[0,0,arr3.GetLength(0), header.Length];
range.Value = new object[] {arr3};
}
}
Can anyone offer assistance?

Jan 18, 2011 at 3:16 PM
Hi Alex,
Check out my work in progress extension method.
The grey out text are what's being develop.
As for the rest they are in the latest release and are ready to try out.
Let me know if it is any help.

Kind regards,

Michael Tran
about.me/MichaelTran

On 18/01/2011, at 8:05 PM, AlexF1980 <notifications@codeplex.com> wrote:

From: AlexF1980

Hi All, I am having difficulty understanding how to add an array of values to an existing template file.

I have downloaded the EPPlusSamples solution but couldn't see an example of how to do this.

Is it even possible?

Below is what I've got so far, that doesn't work:

interal static void WriteToExcel(FileInfo template, DirectoryInfo outputDir, object[] header, Array arr3)
{
using (var p = new ExcelPackage(template))
{
var ws = p.Workbook.Worksheets["BoM"];
ExcelRange range = ws.Cells[0,0,arr3.GetLength(0), header.Length];
range.Value = new object[] {arr3};
}
}
Can anyone offer assistance?

Jan 18, 2011 at 4:52 PM

Thanks Michael, that does go some way to help out.

I'm a bit puzzled though with the output I now see in my Excel file:

I guess I need to pass in the values stored in my Array rather than just the Array itself (as I'm doing now):

    internal static void WriteToExcel(FileInfo template, DirectoryInfo outputDir, object[] header, Array arr3)
    {
        // for debugging only we make a new file:
        FileInfo newFile = new FileInfo(outputDir.FullName + @"\test1.xlsx");
        if (FileInfo.Exists)
        {
            newFile.Delete();
            newFile = new FileInfo(outputDir.FullName + @"\test1.xlsx");
        }
        using (var p = new ExcelPackage(newFile))
        {
            var ws = p.Workbook.Worksheets.Add("Test1");
            ExcelRange range = ws.Cells[0, 0, arr3.GetLength(0), header.Length];
            range.SetValue(new object[] { arr3 });
            p.Save();
        }
    }
Thoughts?

Coordinator
Jan 18, 2011 at 7:39 PM

Hi,

All non value types will be converted to strings (calling the ToString-method). Same thing with arrays in this release. In the future the Value property should rewritten to handle arrays the same way VBA does.

For now, set the values for each value in the array.

Jan

Jan 25, 2011 at 12:31 PM
jankallman wrote:

Hi,

All non value types will be converted to strings (calling the ToString-method). Same thing with arrays in this release. In the future the Value property should rewritten to handle arrays the same way VBA does.

For now, set the values for each value in the array.

Jan

Hi Jan,

I'm a bit puzzled as to what you mean by: "Set the values for each value in the array"? Can you elaborate on this?

Thanks,

Alex.

 

Coordinator
Jan 30, 2011 at 6:11 PM

Hi,

Iterate the array in a for-loop and set the values per cell.

Jan