I'm using Visual Studio 2010 (VB.Net) and Open XML SDK 2.0. How do you insert a formula into an Excel 2010 worksheet? When I do this I also wish to set the CellValue property of the cell to a DBNull or EmptyString to force Excel to recalculate the cell when the user opens the workbok.
Excel – Using Open XML how do you insert a formula into an Excel 2010 worksheet
excelopenxmlopenxml-sdkvb.net
Related Solutions
Here is the working code. This is a prototype. For a larger number of changes, one might open the document only once. Also, there are some hard-coded things like sheet name and cell type that would have to be parameterized before this can be called production-ready. http://openxmldeveloper.org/forums/4005/ShowThread.aspx was very helpful.
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Xml;
using System.IO;
using System.Diagnostics;
namespace OpenXMLWindowsApp
{
public class OpenXMLWindowsApp
{
public void UpdateSheet()
{
UpdateCell("Chart.xlsx", "20", 2, "B");
UpdateCell("Chart.xlsx", "80", 3, "B");
UpdateCell("Chart.xlsx", "80", 2, "C");
UpdateCell("Chart.xlsx", "20", 3, "C");
ProcessStartInfo startInfo = new ProcessStartInfo("Chart.xlsx");
startInfo.WindowStyle = ProcessWindowStyle.Normal;
Process.Start(startInfo);
}
public static void UpdateCell(string docName, string text,
uint rowIndex, string columnName)
{
// Open the document for editing.
using (SpreadsheetDocument spreadSheet =
SpreadsheetDocument.Open(docName, true))
{
WorksheetPart worksheetPart =
GetWorksheetPartByName(spreadSheet, "Sheet1");
if (worksheetPart != null)
{
Cell cell = GetCell(worksheetPart.Worksheet,
columnName, rowIndex);
cell.CellValue = new CellValue(text);
cell.DataType =
new EnumValue<CellValues>(CellValues.Number);
// Save the worksheet.
worksheetPart.Worksheet.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)
{
// The specified worksheet does not exist.
return null;
}
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)
document.WorkbookPart.GetPartById(relationshipId);
return worksheetPart;
}
// Given a worksheet, a column name, and a row index,
// gets the cell at the specified column and
private static Cell GetCell(Worksheet worksheet,
string columnName, uint rowIndex)
{
Row row = GetRow(worksheet, rowIndex);
if (row == null)
return null;
return row.Elements<Cell>().Where(c => string.Compare
(c.CellReference.Value, columnName +
rowIndex, true) == 0).First();
}
// Given a worksheet and a row index, return the row.
private static Row GetRow(Worksheet worksheet, uint rowIndex)
{
return worksheet.GetFirstChild<SheetData>().
Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
}
}
I used the code provided by Andrew J, but the DataType
CellValues.Date
produced a corrupted xlsx-file for me.
The DataType
CellValues.Number
worked fine for me (Don't forget to set NumberFormatId
):
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
My whole code:
DateTime valueDate = DateTime.Now;
string valueString = valueDate.ToOADate().ToString();
CellValue cellValue = new CellValue(valueString);
Cell cell = new Cell();
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
cell.StyleIndex = yourStyle; //StyleIndex of CellFormat cfBaseDate -> See below
cell.Append(cellValue);
My CellFormat for this cell in the Stylesheet looks like:
CellFormat cfBaseDate = new CellFormat() {
ApplyNumberFormat = true,
NumberFormatId = 14, //14 is a localized short Date (d/m/yyyy) -> See list below
//Some further styling parameters
};
If you'd like to format your date another way, here is a list of all default Excel NumberFormatId
's
ID FORMAT CODE 0 General 1 0 2 0.00 3 #,##0 4 #,##0.00 9 0% 10 0.00% 11 0.00E+00 12 # ?/? 13 # ??/?? 14 d/m/yyyy 15 d-mmm-yy 16 d-mmm 17 mmm-yy 18 h:mm tt 19 h:mm:ss tt 20 H:mm 21 H:mm:ss 22 m/d/yyyy H:mm 37 #,##0 ;(#,##0) 38 #,##0 ;[Red](#,##0) 39 #,##0.00;(#,##0.00) 40 #,##0.00;[Red](#,##0.00) 45 mm:ss 46 [h]:mm:ss 47 mmss.0 48 ##0.0E+0 49 @
Source of list: https://github.com/ClosedXML/ClosedXML/wiki/NumberFormatId-Lookup-Table
I know this list is from ClosedXML, but it's the same in OpenXML.
Best Answer
Just leave the CellValue as null, and instantiate a new CellFormula like so:
The cells value will be computed when the document is opened in Excel