OpenXML SDK - Excel Ranges

Question

With Microsoft's OpenXML SDK is there any way to insert from a DataSet into Excel using ranges?

Previously, the Excel API (Interop Assemblies) would let you do this. Now, at least by trying and reading through samples, the only way I found is by using loops to create each row and cell.

I've seen some 3rd Party tools doing this but I'd like it out of the box. Thanks.

What's wrong with using 3rd party tools? They tend to make your work much easier... — Nuffin, Jan 20 '12 at 15:45

score 0 · Answer 1 · answered Jan 20 '12 at 18:58

Forget about using interop, you will have to have excel installed locally. The best 3rd party tool I have ever used with excel is Gembox Spreedsheet and it's free:

http://www.gemboxsoftware.com/

There are full articles on how to use the software in the support tab on the site. Here's an example of how easy it is to insert data to excel from a dataset:

// Create new ExcelFile.
ExcelFile ef2 = new ExcelFile();

// Imports all the tables from DataSet to new file.
foreach (DataTable dataTable in dataSet.Tables)
{
    // Add new worksheet to the file.
    ExcelWorksheet ws = ef2.Worksheets.Add(dataTable.TableName);

    // Change the value of the first cell in the DataTable.
    dataTable.Rows[0][0] = "This is new file!";

    // Insert the data from DataTable to the worksheet starting at cell "A1".
    ws.InsertDataTable(dataTable, "A1", true);
}

// Save the file to XLS format.
ef2.SaveXls("DataSet.xls");

You can read and write to files with openXML like this:

ExcelFile ef = new ExcelFile();

// Loads OpenXML file.
ef.LoadXlsx("filename.xlsx", XlsxOptions.None);

// Selects first worksheet.
ExcelWorksheet ws = ef.Worksheets[0];

// Change the value of the cell "A1".
ws.Cells["A1"].Value = "Hello world!";

// Saves the file in OpenXML format.
ef.SaveXlsx("NewFile.xlsx")

I have used this .Net component many times with great success and minimal amount of code.

so there isn't a way to do this out of the box? I was looking at the DefinedName class but not sure this will work. (http://msdn.microsoft.com/en-us/library/ee841230.aspx) — user1005806, Jan 20 '12 at 19:53
Trust me, you wont want to go through all that just to complete a task that has already been accomplished by someone else. There's no need to reinvent the wheel. — MDL, Jan 20 '12 at 19:57
Check this out: http://stackoverflow.com/questions/643643/how-to-create-and-download-excel-document-using-asp-net — MDL, Jan 20 '12 at 19:59

OpenXML SDK - Excel Ranges

1 Answers1