I have arrays of points that contain series data (x & y). Is there a quick way to output these arrays into an excel file?
Thanks
Add a comment
|
5 Answers
Output the data to a file, separating the array elements with commas. Then save the file as name.csv
Use FileWriter to output the file.
Comments
One of this nice things about range object is that you can assign a two dimensional array to directly to the value property. It is important that the range be the same number of cells as the array has elements.
//using Excel = Microsoft.Office.Interop.Excel;
String[,] myArr = new string[10, 10];
for (int x = 0; x < 10; x++)
{
for (int y = 0; y < 10; y++)
{
myArr[x, y] = "Test " + y.ToString() + x.ToString();
}
}
Excel.Application xlApp = new Excel.Application();
xlApp.Visible = true;
Excel.Workbook wb = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(1);
Excel.Range rng = ws.Cells.get_Resize(myArr.GetLength(0), myArr.GetLength(1));
rng.Value2 = myArr;
2 Comments
Mike Rosenblum
+1. This is the way I would go. But I would make use of the Range.get_Resize() method in order to size your range to the correct dimensions, instead of hardcoding it with strings. But you've got my vote. :-)
user8128167
With Excel 2016, had to use Excel.Range rng = ws.Cells.Resize(myArr.GetLength(0), myArr.GetLength(1));
If CSV is not satisfactory, you can use Microsoft.Office.Interop.Excel. An example is at How to: Use COM Interop to Create an Excel Spreadsheet (C# Programming Guide).
Comments
I would use a third-party xsl export component. This would save you the hassle of excel automation, and you wouldn't have to bundle the excel interop assemblies with your application.
MyXls is a simple open-source component that does excel exports. It should cover your needs just fine.
Comments
You could do it using Ado.net. My code below assumes that there's an excel spreadsheet called Book1.xls in a folder C:\Stuff\ and that the spread sheet has the headers ID, Name, Site already present in a sheet called Sheet1.
private void button1_Click(object sender, EventArgs e)
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Stuff\Book1.xls;Extended Properties=
""Excel 8.0;HDR=YES;""";
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
using (OleDbCommand command = conn.CreateCommand())
{
command.CommandText = @"INSERT INTO [Sheet1$] (ID, Name, Site) VALUES(1, ""Phil"", ""StackOverflow.com"")";
conn.Open();
command.ExecuteNonQuery();
}
}
}
