Saving .xlsx files

XlsxWriter saves a workbook as an .xlsx file: one read by XlsxReader, with whatever you changed, or one built in code.

Workbook workbook = XlsxReader.Load(templatePath);
workbook["Budget"]["B2"].Value = 1350;   // an input
workbook.Recalculate();                  // required before saving
XlsxWriter.Save(workbook, resultPath);   // charts, comments and other cells stay as they were

var workbook = new Workbook();
Worksheet sheet = workbook.AddSheet("Report");
sheet["A1"].Value = "Total";
sheet["A1"].Style = sheet["A1"].Style with { Font = sheet["A1"].Style.Font with { Bold = true } };
sheet["B1"].Formula = "=SUM(B2:B4)";
sheet["B2"].Value = 10;
sheet["B3"].Value = 20;
sheet["B4"].Value = 30;
sheet["B1"].NumberFormat = "#,##0.00";
sheet.SetColumnWidth(1, 15);
workbook.Recalculate();
XlsxWriter.Save(workbook, path);   // a new .xlsx file; Excel shows Total 60.00

What is written

Everything SharpCell models:

• Cell values and formulas, with the results SharpCell calculated. Formulas are written the way Excel stores them, so newer functions such as XLOOKUP, FILTER or LET work when Excel opens the file.
• Dynamic arrays, with the area they spill over. A formula that reads a range where one value is expected (=SUM(A1:A3*2)) is saved as a dynamic array formula, so Excel does not reduce it as Excel 2016 would.
• Cell styles and number formats, column widths, hidden columns, row heights, hidden rows, merged cells, frozen panes, gridlines, the default column width and row height, and whether a sheet is filtered.
• Hyperlinks, notes, data validation, conditional formats, sheet and workbook protection, and locked and hidden cells.
• Page orientation and margins, print areas, print titles and manual page breaks.
• New sheets, defined names, new tables, the date system and the iteration settings.

A workbook read from a file keeps everything SharpCell does not model, copied unchanged from the file: charts, pivot tables, threaded comments, the settings of colour scales, data bars and icon sets, pictures, macros, headers and footers, paper size and scaling, pattern fills, and parts SharpCell does not know. A cell that did not change is copied as it was, and a new style starts from the cell's old one, so what SharpCell does not read (rotated text, a pattern fill) stays. Saving a workbook in which nothing changed writes the original file byte for byte.

New text goes into the cell itself, not into the shared string table. A new table's header cells are saved holding the column names as text, as Excel requires. When formulas change, the file's calculation chain is left out; Excel builds it again. #SPILL! and #CALC! are saved the way Excel saves them, so Excel shows them before it recalculates.

After rows or columns were inserted or deleted, everything in the file that points at cells follows them, including what SharpCell does not model: filters, pictures and shapes, threaded comments, chart series and pivot table sources. A comment on a deleted cell goes; a picture anchored in deleted rows shrinks to where they were. A pivot table moves with the rows above it, but rows inserted into it or deleted from it make the save fail, as Excel refuses that edit. A sheet holding an Excel data table cannot be saved after its rows or columns moved (NotSupportedException). Formulas SharpCell cannot read are kept as the file has them, with the results Excel saved.

Saving to a path writes a new file next to the target and then replaces it, so the file the workbook was read from can be the target.

What cannot be saved

Nothing is written when a save is refused.

Formulas SharpCell cannot calculate

A formula that calls a function SharpCell does not know, or links to another workbook, has no trustworthy result. By default Save refuses with an XlsxWriteException; its Problems list the cells. With new XlsxWriteOptions { KeepUncalculated = true } those cells keep the result Excel saved, and the file asks Excel to recalculate every formula when it opens it. Until then, programs that read saved results (pandas, openpyxl, XlsxReader) see what SharpCell calculated for the cells that read such a formula.

Memory

A workbook read by XlsxReader keeps the file's bytes in memory for saving: count the file's size on top of the workbook.