UOS is the spreadsheet format of the UOF specification, saved with the .uos extension, and office suites that implement the specification can open it directly. When an Excel report is handed to such an environment and .xlsx is not supported, the workbook has to be converted to UOS first; a UOS file received in return has to be converted back to Excel before it can be edited in the usual tools. Conversion only changes the saved format: formulas, merged areas, number formats and text formatting travel with the file. Spire.XLS for JavaScript performs the conversion in the browser through WebAssembly, and the input and output files are managed by a virtual file system (VFS), with no backend service required.

This article covers two feature points:

• Convert Excel to UOS
• Convert UOS to Excel

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module has been initialized.

Convert Excel to UOS

Saving an Excel workbook as UOS lets an office suite that only recognizes the UOF format open the report directly. Only the saved format changes; worksheet names, merged areas and number formats stay as they are. Formula cells need one extra step: they have to be calculated before saving so that both the formula and its result are written.

How to convert Excel to UOS and what it produces:

The steps are:

1. Load the font file and the Excel test file into the VFS with window.spire.FetchFileToVFS.
2. Create a workbook with new Workbook() and load the test file with workbook.LoadFromFile.
3. Calculate the formulas with workbook.CalculateAllValue().
4. Call workbook.SaveToFile with fileFormat set to xlsModule.FileFormat.UOS, writing ExcelToUos.uos.
5. Release the workbook with workbook.Dispose, then read the result out of the VFS and trigger the download.

The following is a complete code example that shows how to convert an Excel workbook to UOS in React:

function App() {
  const excelToUos = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check that the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'SalesReport.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // Calculate the formulas so the formula cells carry their results
    workbook.CalculateAllValue();

    // Save as UOS, with the format given by fileFormat
    const outputFileName = 'ExcelToUos.uos';
    workbook.SaveToFile({ fileName: outputFileName, fileFormat: xlsModule.FileFormat.UOS });

    // Dispose of the workbook to release its resources
    workbook.Dispose();

    // Read the result back out of the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/octet-stream' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Convert Between Excel and UOS in React</h1>
      <button id="excel-to-uos" onClick={excelToUos}>Convert Excel to UOS</button>
    </div>
  );
}

export default App;

The UOS file produced from the Excel workbook:

Convert UOS to Excel

Converting a received UOS file back to Excel is the reverse of the same process. UOS and Excel store the same workbook content; loading restores worksheet names, cell data and styles, and formulas come back together with their cached results, so nothing has to be recalculated before saving. The converted file can then be opened in Excel or an online spreadsheet tool for further editing, summarizing or printing.

How to convert a UOS file to Excel and what it produces:

The steps are:

1. Load the font file and the UOS test file into the VFS with window.spire.FetchFileToVFS.
2. Create a workbook with new Workbook() and load the UOS file with workbook.LoadFromFile.
3. Call workbook.SaveToFile with fileFormat set to xlsModule.FileFormat.Version2010, writing UosToExcel.xlsx.
4. Release the workbook with workbook.Dispose, then read the result out of the VFS and trigger the download.

The following is a complete code example that shows how to convert a UOS file back to Excel in React:

function App() {
  const uosToExcel = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check that the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'SalesReport.uos';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the UOS file
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // Save as Excel, with the version given by fileFormat
    const outputFileName = 'UosToExcel.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, fileFormat: xlsModule.FileFormat.Version2010 });

    // Dispose of the workbook to release its resources
    workbook.Dispose();

    // Read the result back out of the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Convert Between Excel and UOS in React</h1>
      <button id="uos-to-excel" onClick={uosToExcel}>Convert UOS to Excel</button>
    </div>
  );
}

export default App;

The workbook produced from the UOS file:

FAQ

Why are the formula cells empty in the UOS file after conversion

Cause: During export, the formula and the value of each cell are taken from the workbook's current calculation results. If CalculateAllValue has not been called after loading, the formula cells are still uncalculated and are written as empty cells, so neither the formula nor the value survives.

Solution: Call workbook.CalculateAllValue() after workbook.LoadFromFile and before workbook.SaveToFile, then save with FileFormat.UOS; the formula cells will carry both the formula and the calculated result.

Can I save as UOS by using only the .uos extension without fileFormat

Cause: fileFormat is an optional parameter of SaveToFile. When it is omitted, the save format is inferred from the extension of the output file name, and a recognizable extension selects that format.

Solution: Yes. Name the output file ExcelToUos.uos and call workbook.SaveToFile({ fileName: outputFileName }); the file is still written as UOS. Setting fileFormat explicitly is safer, because a missing or misspelled extension will not silently produce another format.

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.

A chart makes a trend visible at a glance, yet it does not give the exact numbers: the height of a column has to be read against the value axis, and the estimate drifts as more series crowd in. A data table lists the category names, series names and values directly below the chart, so readers do not have to go back to the source cells or add a data label to every point. As part of the chart, the table scales and moves with it and stays in place when the chart is exported as an image or printed. Spire.XLS for JavaScript performs these operations in the browser through WebAssembly; the input and output files are managed by a virtual file system (VFS), with no backend service required.

This article covers two feature points:

• Add a data table to the chart
• Adjust the data table borders, legend keys and text formatting

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module has been initialized.

Add a data table to the chart

Adding a data table to a chart solves the problem of numbers living apart from the picture. A default column chart only draws the columns; the values stay in the source worksheet and the reader has to move between the two. With several series, a data table sits directly under the category axis, one row per series, its columns aligned with the categories and its rows matching the colours in the legend, so the trend and the figures appear in one place. The sample workbook already holds a quarterly sales table and a column chart; what it lacks is the table below the chart.

Turning the data table on:

The steps are:

1. Load the font file and the test data file into the VFS with window.spire.FetchFileToVFS.
2. Create a workbook with new Workbook(), load the input file with workbook.LoadFromFile, and take the first worksheet with workbook.Worksheets.get(0).
3. Take the first chart on the worksheet with sheet.Charts.get(0).
4. Set chart.HasDataTable to true, which adds the data table below the chart.
5. Save the workbook with workbook.SaveToFile and dispose of it with workbook.Dispose to free resources.

The full code example below shows how to add a data table to an Excel chart in React:

function App() {
  const addDataTable = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check that the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'ChartSales.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook and take the first worksheet and its chart
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);
    const chart = sheet.Charts.get(0);

    // Turn on the chart's data table, which lists the categories and series values below the plot
    chart.HasDataTable = true;

    // Save the workbook
    const outputFileName = 'AddDataTable.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

    // Dispose of the workbook to release its resources
    workbook.Dispose();

    // Read the result back out of the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Add a Data Table to an Excel Chart</h1>
      <button id="add-data-table" onClick={addDataTable}>Add a Data Table to the Chart</button>
    </div>
  );
}

export default App;

The sales report after turning on the chart data table:

Adjust the data table borders, legend keys and text formatting

Adjusting the data table borders, legend keys and text formatting keeps the table readable when there are many series and lets it blend into the layout of a report. A newly opened data table carries an outer frame and both horizontal and vertical separators; with three rows of values stacked together it looks like a dense grid. Dropping the outer frame and the vertical lines leaves only the horizontal ones, so the eye follows a row straight to its numbers without being cut off by vertical rules. Turning on the legend keys then puts a colour swatch of the matching series in front of every series name, so readers no longer have to compare each row with the legend. By default the text draws on the chart's own settings -- ten points, black, not bold -- which does not stand out against dark columns and gridlines; dropping the size to nine points fits more numbers on a line, while bolding the text and switching it to a deeper blue ties it to the title and the header row. These settings apply to the data table alone; the chart title, axis labels and legend keep their own fonts.

Borders, legend keys and text formatting:

The steps are:

1. Load the font and the test data file into the VFS with window.spire.FetchFileToVFS, then load the workbook with workbook.LoadFromFile.
2. Take the first worksheet with workbook.Worksheets.get(0) and the first chart with sheet.Charts.get(0).
3. Set chart.HasDataTable to true to open the data table, then take the object with chart.DataTable.
4. Set table.HasBorders and table.HasVertBorder to false and table.HasHorzBorder to true, so only the horizontal lines remain.
5. Set table.ShowSeriesKeys to true to show the legend key before each series name.
6. Set table.FontName, table.Size and table.IsBold to choose the typeface, the size and the bold weight.
7. Build a deep blue with xlsModule.Color.FromArgb and assign it to table.Color.
8. Save the workbook with workbook.SaveToFile and dispose of it with workbook.Dispose to free resources.

The full code example below shows how to adjust the data table borders, legend keys and text formatting in React:

function App() {
  const customizeDataTable = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check that the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'ChartSales.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook and take the first worksheet and its chart
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);
    const chart = sheet.Charts.get(0);

    // Turn on the data table and take its object
    chart.HasDataTable = true;
    const table = chart.DataTable;

    // Drop the outer frame and the vertical lines, keeping only the horizontal ones
    table.HasBorders = false;
    table.HasVertBorder = false;
    table.HasHorzBorder = true;

    // Show the legend key in front of each series name
    table.ShowSeriesKeys = true;

    // Set the data table font, size, bold weight and text colour
    table.FontName = 'Arial';
    table.Size = 9;
    table.IsBold = true;
    table.Color = xlsModule.Color.FromArgb(255, 28, 68, 118);

    // Save the workbook
    const outputFileName = 'DataTableFormatting.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

    // Dispose of the workbook to release its resources
    workbook.Dispose();

    // Read the result back out of the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Add a Data Table to an Excel Chart</h1>
      <button id="data-table-formatting" onClick={customizeDataTable}>Adjust the Borders, Legend Keys and Text</button>
    </div>
  );
}

export default App;

The data table after adjusting the borders, legend keys and text formatting:

FAQ

What is the difference between a data table and data labels?

Cause: Data labels sit next to each data point and show only that point's value. A data table sits below the chart, one row per series, and gives the category names and all the values together.

Solution: Use data labels when each point needs a callout without a table taking up space; use a data table when readers should take in a whole set of values at once. Both can be present on the same chart and turned on or off as needed.

How do I remove the data table from a chart?

Cause: The data table is controlled by a single switch on the chart; once it is on, it does not disappear with a change of source data or chart type.

Solution: Set chart.HasDataTable to false and save the workbook; the data table below the chart is removed.

How can the data table use a different font from the rest of the chart?

Cause: The data table carries its own text formatting. Until it is set, it follows the rest of the chart's text; once set, only the data table changes.

Solution: Set FontName, Size, IsBold and Color on chart.DataTable. These properties act on the data table only and leave the chart title, axis labels and legend untouched.

What is the legend key in a data table?

Cause: A legend key is the small colour swatch shown in front of a series name, matching the series' marker in the legend. It is off by default, so the data table shows plain text.

Solution: Set table.ShowSeriesKeys to true; a swatch then appears in front of every series name, so a reader can match a row of the data table with the legend directly.

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.

Text boxes in a worksheet carry notices, reminders or a single conclusion, yet their default look is plain: small black text on white, framed by a thin border that blends into the grid. Enlarging and bolding the font and laying down a light fill make the text rise above the table; inner margins and text wrapping keep long sentences off the border and inside the box; removing the border lets a text box read as a quiet annotation. These changes can be applied in code in one pass instead of one box at a time. Spire.XLS for JavaScript runs in the browser on WebAssembly and handles the input and output files through a virtual file system (VFS), with no backend service required.

This article covers two core feature points:

• Adjust the Font, Background and Border of Text Boxes
• Set the Inner Margins and Text Wrapping of Text Boxes

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module has been initialised.

Adjust the Font, Background and Border of Text Boxes

The default 11-point black text on a white background lacks contrast against the sheet. A larger size, a bold weight, another color and a pale fill lift a notice above the grid; clearing a border that competes with the cell lines makes the text read as if written straight on the page, which suits footnotes and corner notes. The same two kinds of change can be applied to one text box or split across different boxes, and this feature point adjusts the font, background and border of text boxes.

The steps are:

1. Take the first text box with sheet.TextBoxes.get(0).
2. Create a font object with workbook.CreateFont() and set its name, size, bold weight and color through FontName, Size, IsBold and Color.
3. Get the rich text object with xlsModule.RichTextShape.Convert(textBox.RichText), then call SetFont(0, textBox.Text.length - 1, font) to apply the font to the whole text.
4. Set textBox.Fill.FillType to xlsModule.ShapeFillType.SolidColor, then name the fill color with textBox.Fill.ForeColor.
5. Take the fourth text box with sheet.TextBoxes.get(3) and set textBox.Line.Weight to 0 to remove its border.
6. Save the workbook with workbook.SaveToFile.

The code below is the complete example of adjusting the font, background and border of a text box:

function App() {
  const adjustTextAppearance = async () => {
    // get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // check that the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // load the font and the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'TextBoxSample.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // load the workbook and take the first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // take the first text box and create a font with its name, size, bold weight and color
    const textBox = sheet.TextBoxes.get(0);
    const font = workbook.CreateFont();
    font.FontName = 'Arial';
    font.Size = 14;
    font.IsBold = true;
    font.Color = xlsModule.Color.FromArgb(255, 28, 68, 118);

    // apply the font to all the text in the text box
    const richText = xlsModule.RichTextShape.Convert(textBox.RichText);
    richText.SetFont(0, textBox.Text.length - 1, font);

    // fill the text box background with a solid color
    textBox.Fill.FillType = xlsModule.ShapeFillType.SolidColor;
    textBox.Fill.ForeColor = xlsModule.Color.FromArgb(255, 232, 240, 250);

    // take the fourth text box and set the border weight to 0, which removes the border
    const borderBox = sheet.TextBoxes.get(3);
    borderBox.Line.Weight = 0;

    // save the workbook
    const outputFileName = 'TextAppearance.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

    // dispose of the workbook to release its resources
    workbook.Dispose();

    // read the result back out of the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Format Excel Text Boxes</h1>
      <button onClick={adjustTextAppearance}>Adjust the Font, Background and Border of a Text Box</button>
    </div>
  );
}

export default App;

The notice board after adjusting the font, background and border:

Set the Inner Margins and Text Wrapping of Text Boxes

Text pressed against the border is hard to read, especially on a dark fill. Inner margins hold the text away from each edge and can be set separately for the four sides. A long sentence in a narrow box stays on one line by default and runs past the border, while text wrapping folds it onto the next line. The sample leaves about 1 cm on the left and right and 0.4 cm at the top and bottom, and aligns the text to the top left so the shift caused by the margins is visible. This feature point sets the inner margins and text wrapping of text boxes.

The steps are:

1. Take the second text box with sheet.TextBoxes.get(1).
2. Set InnerLeftMargin, InnerRightMargin, InnerTopMargin and InnerBottomMargin in turn to leave room on all four sides.
3. Set HAlignment to xlsModule.CommentHAlignType.Left and VAlignment to xlsModule.CommentVAlignType.Top, so the text starts from the top-left corner.
4. Take the third text box with sheet.TextBoxes.get(2) and set IsWrapText to true to turn on text wrapping.
5. Save the workbook with workbook.SaveToFile.

The code below is the complete example of setting the inner margins and text wrapping of a text box:

function App() {
  const setTextLayout = async () => {
    // get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // check that the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // load the font and the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'TextBoxSample.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // load the workbook and take the first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // take the second text box and set the space between the text and its four borders
    const textBox = sheet.TextBoxes.get(1);
    textBox.InnerLeftMargin = 1;
    textBox.InnerRightMargin = 1;
    textBox.InnerTopMargin = 0.4;
    textBox.InnerBottomMargin = 0.4;

    // align the text to the top left so the effect of the margins is visible
    textBox.HAlignment = xlsModule.CommentHAlignType.Left;
    textBox.VAlignment = xlsModule.CommentVAlignType.Top;

    // take the third text box and turn on wrapping, so text folds at the box width
    const wrapBox = sheet.TextBoxes.get(2);
    wrapBox.IsWrapText = true;

    // save the workbook
    const outputFileName = 'TextLayout.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

    // dispose of the workbook to release its resources
    workbook.Dispose();

    // read the result back out of the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Format Excel Text Boxes</h1>
      <button onClick={setTextLayout}>Set the Inner Margins and Text Wrapping of a Text Box</button>
    </div>
  );
}

export default App;

The notice board after setting the inner margins and text wrapping:

FAQ

How do I get a text box by name?

Cause: Getting text boxes by index means remembering their order in the collection, and inserting or removing a text box changes that order.

Solution: Use sheet.TextBoxes.get("TextBox 2") to get a text box by name.

How do I format only part of the text in a text box?

Cause: The text of a text box is stored as rich text, and SetFont applies to a character range; anything outside the range keeps its original format.

Solution: Get the rich text object with xlsModule.RichTextShape.Convert(textBox.RichText), then call richText.SetFont(startIndex, endIndex, font) with the range you want. For example, SetFont(0, 3, font) applies to the first four characters only.

Can the border of a text box have another color or a dashed style?

Cause: Besides its weight, the Line object also provides color and dash style settings.

Solution: Set the border color with textBox.Line.ForeColor, the weight with textBox.Line.Weight, and the dash style with textBox.Line.DashStyle, whose value is a member of xlsModule.ShapeDashLineStyleType, such as Dashed for a dashed line.

What if the text does not wrap as expected?

Cause: Text only wraps when IsWrapText is true, and when the text box is wide enough for the whole sentence no line break appears either.

Solution: Confirm that textBox.IsWrapText = true has been set, and check that the width of the text box is smaller than the length of the sentence. When several lines are needed, leave enough height in the text box as well.

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.

A cell whose formatting is already right often has to be reused elsewhere: the unit price column carries a money format, a light blue fill and thin borders, while the amount column on the same row is still plain. Copying a cell format hands the look the source cell has already settled on to the target cell -- number format, font, fill, border and alignment in one go -- and leaves the data in the target cell untouched. Spire.XLS for JavaScript performs these operations in the browser through WebAssembly; the input and output files are managed by a virtual file system (VFS), with no backend service required.

This article covers two feature points:

• Copy the whole cell format
• Clone and reuse a named style

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module has been initialized.

Copy the whole cell format

Every part of a cell's look is kept in a style object of its own. Handing the source cell's style object straight to the target cell carries the number format, font, fill, border and alignment across in one go, while the data in the target cell stays where it is. The sample workbook's unit price column already carries a money format and a light blue fill, and the amount column is still plain, so copying the former onto the latter row by row shows the difference.

Assigning the source cell's whole Style to the target cell's Style completes the copy:

The steps are:

1. Load the font file and the test data file into the VFS with window.spire.FetchFileToVFS.
2. Create a workbook with new Workbook(), load the input file with workbook.LoadFromFile, and take the first worksheet with workbook.Worksheets.get(0).
3. Take the source column's cell and the target column's cell row by row with sheet.Range.get.
4. Assign the source cell's Style to the target cell's Style; the whole cell format follows, and the target cell's value is left untouched.
5. Save the workbook with workbook.SaveToFile and dispose of it with workbook.Dispose to free resources.

The full code example below shows how to copy the whole cell format in React:

function App() {
  const copyCellStyle = async () => {
    // get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // check that the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // load the font and the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'CopyCellStyle.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // load the workbook and take the first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // row by row, put the unit price column's format onto the amount column; the amounts stay put
    for (let row = 2; row <= 7; row++) {
      sheet.Range.get(`E${row}`).Style = sheet.Range.get(`C${row}`).Style;
    }

    // save the workbook
    const outputFileName = 'CopyCellStyle-output.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2016 });

    // dispose of the workbook to free resources
    workbook.Dispose();

    // read the result file from the VFS and start the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Copy Cell Format</h1>
      <button id="copy-cell-style" onClick={copyCellStyle}>Copy the Whole Cell Format</button>
    </div>
  );
}

export default App;

The worksheet after copying the whole cell format:

Clone and reuse a named style

When one set of formatting is needed in several places, registering it as a named style lets it be applied to any range by name, and a colour change has to be made in one place only. A named style lives on the workbook: create it with workbook.Styles.Add, set its properties, then apply it by name through Range.CellStyleName. The example builds a style of white text on a steel blue fill for the header row, then clones it and changes only the colours for the total line -- the change lands on the clone alone, and the original style is unaffected.

A named style is registered on the workbook and applied to a range by name:

The steps are:

1. Load the font file and the test data file into the VFS with window.spire.FetchFileToVFS.
2. Create a workbook with new Workbook(), load the input file with workbook.LoadFromFile, and take the first worksheet with workbook.Worksheets.get(0).
3. Create a named style with workbook.Styles.Add and set its font name, size, bold, text colour, fill colour, fill pattern and horizontal alignment.
4. Apply the style to the header range by name with sheet.Range.get('A1:E1').CellStyleName.
5. Clone it with the style's clone(), change only the text colour and the fill colour, and get the variant the total line needs.
6. Apply the variant to the total line with sheet.Range.get('A8:E8').CellStyleName.
7. Save the workbook with workbook.SaveToFile and dispose of it with workbook.Dispose to free resources.

The full code example below shows how to clone a named style and reuse it for different ranges in React:

function App() {
  const cloneNamedStyle = async () => {
    // get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // check that the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // load the font and the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'CopyCellStyle.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // load the workbook and take the first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // create a named style: white text on a steel blue fill, centred
    const headerStyle = workbook.Styles.Add('OrderHeader');
    headerStyle.Font.FontName = 'Arial';
    headerStyle.Font.Size = 11;
    headerStyle.Font.IsBold = true;
    headerStyle.Font.Color = xlsModule.Color.get_White();
    headerStyle.Color = xlsModule.Color.get_SteelBlue();
    headerStyle.FillPattern = xlsModule.ExcelPatternType.Solid;
    headerStyle.HorizontalAlignment = xlsModule.HorizontalAlignType.Center;

    // apply the named style to the header row
    sheet.Range.get('A1:E1').CellStyleName = headerStyle.Name;

    // clone it and change only the colours, giving the variant the total line needs
    const totalStyle = headerStyle.clone();
    totalStyle.Font.Color = xlsModule.Color.get_DarkBlue();
    totalStyle.Color = xlsModule.Color.get_LightYellow();

    // apply the cloned style to the total line
    sheet.Range.get('A8:E8').CellStyleName = totalStyle.Name;

    // save the workbook
    const outputFileName = 'CloneNamedStyle.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2016 });

    // dispose of the workbook to free resources
    workbook.Dispose();

    // read the result file from the VFS and start the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Clone and Reuse a Named Style</h1>
      <button id="clone-named-style" onClick={cloneNamedStyle}>Clone and Reuse a Named Style</button>
    </div>
  );
}

export default App;

The worksheet after cloning a named style and reusing it:

FAQ

Copying the number format alone, without the fill or the border

Reason: A whole-cell copy takes the fill, font and border along with the number format, which is more than a change of display calls for.

Solution: Take NumberFormat from the source cell's style and assign that single property to the target cell's style, leaving the rest untouched:

// take the number format of the total line's quantity cell as the format to apply
const sourceStyle = sheet.Range.get('D8').Style;

// put only the number format onto the quantity column; the bold and the top border stay untouched
for (let row = 2; row <= 7; row++) {
  sheet.Range.get(`D${row}`).Style.NumberFormat = sourceStyle.NumberFormat;
}

Why a whole font object cannot be assigned to the target cell

Reason: Style.Font is a read-only property; it returns the font object the cell currently carries, so it cannot be replaced wholesale -- only the members hanging off it can be changed.

Solution: Set FontName, Size, Color, IsBold and the like one at a time. To bring the fill and the border across as well, assign the whole Style; to reuse a group of settings, register them as a named style and apply that style by name.

Does re-colouring a clone change the original style as well

Cause: clone() produces a copy that stands on its own, and once saved it takes a separate entry among the workbook's named styles; the name has a number appended to keep the two apart, so OrderHeader clones to OrderHeader1. Changing the text colour and the fill colour on the copy leaves the original where it was, still carrying the colours it had before the clone.

Solution: When several colour variants are needed, clone them all from the same base style and adjust each one separately; the definition of the base style never moves.

A named style or a plain Style assignment -- which one to use

Cause: Assigning a Style copies the source cell's current look onto the target cell in one go, which suits a one-off move; Range.CellStyleName applies a named style registered on the workbook, so a format used over and over is referred to by a single name in the code.

Solution: For a one-off move of the look, assign the whole Style; for a format reused in several places, build a named style with workbook.Styles.Add and apply it by name.

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.