How to add xml schema/design file to Excel Interop?

Question

I am creating an excel file using a data table in excel interop

Price           Profit/Loss%

250.8982989       0.04301071

I have a schema file which has details for design as 1) make all headers bold 2) column definition (weather,string,percentage)

I used this file in fast report export but as such i have to use interop for excel export is there a way i can add that schema file

  Excel.Application excelApp = new Excel.Application();

            //Create an Excel workbook instance and open it from the predefined location
            Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);


            //Add a new worksheet to workbook with the Datatable name
            Excel.Worksheet excelWorkSheet = (Excel.Worksheet)excelWorkBook.Sheets.Add();


            for (int i = 1; i < table.Columns.Count + 1; i++)
            {
                excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
            }

            for (int j = 0; j < table.Rows.Count; j++)
            {
                for (int k = 0; k < table.Columns.Count; k++)
                {
                    excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
                }
            }

            excelWorkBook.SaveAs(@"D:\sample excel.xls");
            excelWorkBook.Close();
            excelApp.Quit();

i want to show this value in bold and format as % 0.04301071

make this value Bold and round 250.8982989

This all information will be stored in a schema file i want to load that file

or else i want to load that cell as per the columns datatype in datatable

I have tried :-

clmnrange.NumberFormat = (Object)table.Columns[k - 1].DataType;

but it is raising an exception

Regards EP

Can you possibly provide a full blown example (like 2 columns and 2 rows) with schema XML, data and expected outcome? The question is kindof incomplete right now (`table` is not even defined, ...) — grek40, Apr 28 '17 at 07:56
The NumberFormat property takes a string that uses Excel's formatting syntax. For example formatting as a percentage (up to) 8 decimal places is "0.########%". I'm guessing that the "DataType" property you are trying to assign is not a string setup for Excel formatting. I would think that you would need to convert any formatting definitions from the table into methods Excel uses for formatting. — Wedge, Apr 28 '17 at 17:00
Possible duplicate of [microsoft.interop.excel Formatting cells](https://stackoverflow.com/questions/7401996/microsoft-interop-excel-formatting-cells) — peinearydevelopment, May 30 '17 at 13:39

lax1089 · Accepted Answer · 2017-05-30T13:58:20.510

As mentioned in above comment:

The NumberFormat property takes a string that uses Excel's formatting syntax. For example formatting as a percentage (up to) 8 decimal places is "0.########%"

Here is an example that someone else provided showing how to implement the type of numberingFormat you are describing:

WorkbookStylesPart sp = workbookPart.AddNewPart<WorkbookStylesPart>();

Create a stylesheet:

sp.Stylesheet = new Stylesheet();

Create a numberingFormat:

sp.Stylesheet.NumberingFormats = new NumberingFormats();
// #.##% is also Excel style index 1

NumberingFormat nf2decimal = new NumberingFormat();
nf2decimal.NumberFormatId = UInt32Value.FromUInt32(3453);
nf2decimal.FormatCode = StringValue.FromString("0.0%");
sp.Stylesheet.NumberingFormat.Append(nf2decimal);

How to add xml schema/design file to Excel Interop?

1 Answers1