C# Open XML SDK 2.0 Excel spreadsheet – load range of cells from string array

cexcelinsertopenxmlrows

In a .Net windows desktop application, I am able to import a string array of data into a range of cells located in Excel spreadsheet. The C# code is as follows:

using Excel = Microsoft.Office.Interop.Excel;

// Create Application, Workbook, and Worksheet
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWs = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

// Move data from temp array into Excel spreadsheet.
Excel.Range c1 = (Excel.Range)xlWs.Cells[startRowNum, 1];
Excel.Range c2 = (Excel.Range)xlWs.Cells[startRowNum + myTable.Rows.Count - 1,   Columns.Count];
Excel.Range range = xlWs.get_Range(c1, c2);
range.Value = tempArray;

I am trying to duplicate this process in an ASP.Net webpage. Using Visual Studio 2010 and C#. How can I do this same import of a string array into an Open XML SDK 2.0 Excel spreadsheet range of cells?

Best Answer

As answered, a library might be easier to use. An alternative library choice is SpreadsheetLight. Here's how the code might look like:

SLDocument sl = new SLDocument("YourFile.xlsx");
// row 1 column 1
sl.SetCellValue(1, 1, "String1");
// row 1 column 2
sl.SetCellValue(1, 2, "String2");
sl.SaveAs("AnotherFile.xlsx");

You don't have to worry about which order you set the cell values. Internally, SpreadsheetLight runs on Open XML SDK. Disclaimer: I wrote SpreadsheetLight.

Related Solutions

Excel – Return empty cell from formula in Excel

Excel does not have any way to do this.

The result of a formula in a cell in Excel must be a number, text, logical (boolean) or error. There is no formula cell value type of "empty" or "blank".

One practice that I have seen followed is to use NA() and ISNA(), but that may or may not really solve your issue since there is a big differrence in the way NA() is treated by other functions (SUM(NA()) is #N/A while SUM(A1) is 0 if A1 is empty).

C# – getting an Out of Memory Error doing ASP .NET Excel Interop

I'm not sure if this is your issue or not, but it very well may be. You are not cleaning up your excel objects properly. They are unmanaged code and can be tricky to clean up. Finally should look something like this: And as the comments have noted working with excel from asp.net is not a good idea. This cleanup code is from a winform app:

 GC.Collect();
 GC.WaitForPendingFinalizers();


 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(range);
 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(sheet);
 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(book);

 WB.Close(false, Type.Missing, Type.Missing);

 Excel.Quit();
 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Excel);

EDIT

An alternative would be to use ado.net to open the workbook.

            DataTable dt = new DataTable();

            string connectionString;
            System.Data.OleDb.OleDbConnection excelConnection;
            System.Data.OleDb.OleDbDataAdapter da;
            DataTable dbSchema;
            string firstSheetName;
            string strSQL;

            connectionString = @"provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + @";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""";
            excelConnection = new System.Data.OleDb.OleDbConnection(connectionString);
            excelConnection.Open();
            dbSchema = excelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();
            strSQL = "SELECT * FROM [" + firstSheetName + "]";
            da = new OleDbDataAdapter(strSQL, excelConnection);
            da.Fill(dt);

            da.Dispose();
            excelConnection.Close();
            excelConnection.Dispose();

Best Answer

Related Solutions

Excel – Return empty cell from formula in Excel

C# – getting an Out of Memory Error doing ASP .NET Excel Interop

Related Topic