如何将xml架构/设计文件添加到Excel Interop？

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

我正在使用excel互操作中的数据表创建一个excel文件

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)

我有一个模式文件,其中包含设计细节1)使所有标题加粗2)列定义(天气,字符串,百分比)

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

我在快速报告导出中使用此文件,但因此我必须使用interop for excel export有一种方法可以添加该模式文件

  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

我想以粗体显示此值并格式为%0.04301071

make this value Bold and round 250.8982989

使这个值大胆并且圆形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

或者我想按照datatable中的columns数据类型加载该单元格

I have tried :-

我努力了 :-

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

but it is raising an exception

但它提出了一个例外

Regards EP

1 个解决方案

#1

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.########%"

NumberFormat属性采用使用Excel格式化语法的字符串。例如,格式化为百分比(最多)8个小数位是“0。########%”

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

以下是其他人提供的示例,说明如何实现您正在描述的numberingFormat类型:

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

Create a stylesheet:

创建样式表:

sp.Stylesheet = new Stylesheet();

Create a numberingFormat:

创建一个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);

#1