Dynamics Community Forum Thread Details

Problem cell number format/ date format change - X++ code to export data in form of Excel

Hi All,

I have created working runnable class to export D365 data to MS excel.

cell - font style change (bold, italic, UnderLine, size)
cell - font color change
cell - merging
cell - border
column - width change, auto fit
column - date format change/ number format change

I have found column formatting method to format cell - number format/data format. This method will apply changes to entire column.

For date format cells, need to focus cursor to cell text and press enter to apply right alignment and change other cells if formula apply based on this. Number format working properly.

Main issue >> Is it possible to format only given cell at once without apply changes to entire column. Help on this.

Sample runnable class code:

using OfficeOpenXml.Style;

internal final class HGITExcelTest3
{
public static void main(Args _args)
{

CustTable custTable;
int c;

//SysExcelApplication excelApplication;

DocuFileSaveResult saveResult =
DocuFileSave::promptForSaveLocation(/@ApplicationPlatform:OfficeDefaultWorkbookFileName/,/xlsx/, null, /excel create and export/);

if (saveResult&& saveResult.parmAction() != DocuFileSaveAction::Cancel)

{
saveResult.parmOpenParameters('web=1');
saveResult.parmOpenInNewWindow(false);
saveResult.parmFilename('Customer data.xlsx');

System.IO.Stream workbookStream = new System.IO.MemoryStream();
System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();

using(var package = new OfficeOpenXml.ExcelPackage(memoryStream))
{
var worksheets = package.get_Workbook().get_Worksheets();
var worksheet = worksheets.Add(/Test_sheet1/);
var cells = worksheet.get_Cells();

OfficeOpenXml.ExcelRange cellRange;
var row = 1;

cellRange = Worksheet.get_Cells().get_Item(/A1:B1/);
cellRange.Merge = true;

var cell = cells.get_Item(row, 1);
cell.set_Value(/Customer details/);

OfficeOpenXml.Style.ExcelStyle style = cells.get_Item(row,1).get_style();

var cellStyle = cells.get_item(row, 1).get_style();
cellStyle.get_Font().set_Bold(true);

var cellStyle2 = cellRange.get_style();
/*cellStyle2.get_Font().set_Bold(true);
cellStyle2.get_Font().set_Italic(true);*/

OfficeOpenXml.Style.ExcelFont font = cellStyle2.Font;
OfficeOpenXml.Style.ExcelColor color = font.Color;

color.SetColor(System.Drawing.Color::Red); //:FromArgb(170,0,0));

font.Size = 20;
font.Bold = true;
font.Italic = true;

row = 3;

cell = null;
cell = cells.get_Item(row, 1);
cell.set_Value(/Customer/);

cell = null;
cell = cells.get_Item(row, 2);
cell.set_Value(/Customer name/);

cellRange = Worksheet.get_Cells().get_Item(/A3:B3/);
cellStyle2 = cellRange.get_style();
font = cellStyle2.Font;

font.Size = 12;
font.Bold = true;
font.Italic = true;
font.UnderLine = true;

cellRange = Worksheet.get_Cells().get_Item(/F2:F3/);
cellRange.Merge = true;

while select custTable
{
row++;

cell = null;
cell = cells.get_Item(row, 1);
cell.set_Value(custTable.AccountNum);

cell = null;
cell = cells.get_Item(row, 2);
cell.set_Value(custTable.name());

c++;

if (c > 10)
break;
}

cell = null;
cell = cells.get_Item(20, 5);
cell.set_Value('11/29/2023');

cell = null;
cell = cells.get_Item(21, 5);
cell.set_Value('1/16/2024');

OfficeOpenXml.ExcelTextFormat textFormat;

str DateCellFormat = /mm/d/yyyy/;
var column = cell.get_Worksheet().Column(cell.get_Start().get_Column());
var columnStyle = column.get_Style();
var columnNumberFormat = columnStyle.get_Numberformat();
columnNumberFormat.set_Format(DateCellFormat);
//columnStyle.set_HorizontalAlignment(ExcelHorizontalAlignment::Right);

cell = null;
cell = cells.get_Item(21, 7);
cell.set_Value(123456.22);

str numberFormat = '_(* #,##0.00_);_(* (#,##0.00);_(* //-//??_);_(@_)'; //Accounting format
var column2 = cell.get_Worksheet().Column(cell.get_Start().get_Column());
var columnStyle2 = column2.get_Style();
var columnNumberFormat2 = columnStyle2.get_Numberformat();
columnNumberFormat2.set_Format(numberFormat);

cell = null;
cell = cells.get_Item(21, 6);
cell.set_Value('4/13/2024');

cell = null;
cell = cells.get_Item(21, 9);
cell.set_Value('abc123 abc123 abc123 abc123 abc123');
cell.AutoFitColumns();

ExcelStyle excelStyle;
ExcelFont excelFont;
ExcelBorderItem cellBorder;

cellBorder = cell.Style.Border.Top;
cellBorder.Style = 4;
cellBorder = cell.Style.Border.Left;
cellBorder.Style = 4;
cellBorder = cell.Style.Border.Right;
cellBorder.Style = 4;
cellBorder = cell.Style.Border.Bottom;
cellBorder.Style = 4;

cell = null;
cell = cells.get_Item(21, 11);
cell.set_Value('xyz123 xyz234');
var column1 = cell.get_Worksheet().Column(cell.get_Start().get_Column());
column1.Width = 50;

package.Save();
}

memoryStream.Seek(0,System.IO.SeekOrigin::Begin);

//Download the file.
DocuFileSave::processSaveResult(memoryStream,saveResult);
}
}

}

Report output :

void numberFormating(
        ExcelRange _cell,
        Int         _value)
    {
        _cell.set_Value(_value); //Assing Value
        ExcelNumberFormat NumFormat       =   _cell.Style.Numberformat;// Get the ExcelStyle for the cell

NumFormat.Format = '0'; // Change the format as needed
}

--------------------------------------------------------------------------------------------------------------------------------------

you can set the NumberFormat property to achieve different formatting effects. Here are some common formatting types:

General Number Format:

"General": Default format without any specific formatting.

Currency Formats:

"Currency": Currency format based on system settings.

"Currency [Symbol]": Custom currency format with a specified currency symbol.

Date and Time Formats:

"Short Date": Displays the date using the short date format.

"Long Date": Displays the date using the long date format.

"Short Time": Displays the time using the short time format.

"Long Time": Displays the time using the long time format.

Percentage Format:

"Percentage": Displays the number as a percentage.

Scientific Format:

"Scientific": Displays the number in scientific notation.

Fraction Format:

"Fraction": Displays the number as a fraction.

Custom Number Formats:

You can specify custom number formats using a combination of symbols. For example:

"0.00": Displays the number with two decimal places.

"#,##0": Displays the number with a thousand separator.

Text Formats:

"Text": Treats the cell value as text, preserving leading zeros.

Accounting Formats:

"Accounting": Displays the number in accounting format with currency symbol and negative numbers in parentheses.

Custom Date and Time Formats:

You can create custom date and time formats using symbols like "yyyy-mm-dd".

Examples

void ApplyNumberFormats(ExcelRange _cell)
{
// General Number Format
_cell.Style.NumberFormat.Format = "General";

// Currency Formats
_cell.Style.NumberFormat.Format = "Currency";
// OR
_cell.Style.NumberFormat.Format = "Currency [$€-407]"; // Custom currency format with Euro symbol

// Date and Time Formats
_cell.Style.NumberFormat.Format = "Short Date";
// OR
_cell.Style.NumberFormat.Format = "yyyy-mm-dd"; // Custom date format

// Percentage Format
_cell.Style.NumberFormat.Format = "Percentage";

// Scientific Format
_cell.Style.NumberFormat.Format = "Scientific";

// Fraction Format
_cell.Style.NumberFormat.Format = "Fraction";

// Custom Number Formats
_cell.Style.NumberFormat.Format = "0.00";
// OR
_cell.Style.NumberFormat.Format = "#,##0";

// Text Formats
_cell.Style.NumberFormat.Format = "Text";

// Accounting Formats
_cell.Style.NumberFormat.Format = "Accounting";

// Custom Date and Time Formats
_cell.Style.NumberFormat.Format = "Long Date";
// OR
_cell.Style.NumberFormat.Format = "yyyy-mm-dd hh:mm:ss"; // Custom date and time format
}
Thanks

Quick Links

Leaderboard