将OpenXML excel数字格式代码应用于c#中的字符串值

Question

在c#中使用OpenXML，有人知道如何应用excel数字格式代码-例如(* #，##0.00);(* (#，##0.00);(* "-"??);(@_) -字符串值吗?

示例，如果我有一个值"10.52982"，我应该得到"10.53"

感谢

PS我不想使用Microsoft.Office.Interop.Excel程序集，它提供了工作表功能。短信功能

Answer 1

试试这个:

    //Create Document
    SpreadsheetDocument googleSpreadSheet = SpreadsheetDocument.Create(GoogleSpreadhSheetStream, SpreadsheetDocumentType.Workbook);
    WorkbookPart cWorkbookPart = googleSpreadSheet.AddWorkbookPart(); 
    //Create stylesheet
    WorkbookStylesPart spStyles = cWorkbookPart.AddNewPart<WorkbookStylesPart>();
    spStyles.Stylesheet = new Stylesheet();
    spStyles.Stylesheet.NumberingFormats = new NumberingFormats();
    uint iExcelIndex = 164;
    //Create format
    NumberingFormat nf2decimal = new NumberingFormat();
    nf2decimal.NumberFormatId = UInt32Value.FromUInt32(3453);//UInt32Value.FromUInt32(iExcelIndex++);
    nf2decimal.FormatCode = StringValue.FromString("0.00");
    spStyles.Stylesheet.NumberingFormats.Append(nf2decimal);
    //Create cellFormat 
    CellFormat numFormat = new CellFormat();
    numFormat.FormatId = 0;
    numFormat.FillId = 0;
    numFormat.BorderId = 0;
    numFormat.FormatId = 0;
    numFormat.NumberFormatId = nf2decimal.NumberFormatId;
    numFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
    //Apped cellformat for cells                   
    spStyles.Stylesheet.CellFormats.AppendChild<CellFormat>(cellFormat);
    //update styles and format count
    spStyles.Stylesheet.NumberingFormats.Count = UInt32Value.FromUInt32((uint)spStyles.Stylesheet.NumberingFormats.ChildElements.Count);
    spStyles.Stylesheet.CellFormats.Count = UInt32Value.FromUInt32((uint)spStyles.Stylesheet.CellFormats.ChildElements.Count);
    //save the changes to the stylesheet
    spStyles.Stylesheet.Save();
    googleSpreadSheet.WorkbookPart.Workbook.Save();

在设置单元格的值时，使用格式索引。因为我刚刚添加了一个，所以格式索引应该是1

     SheetData cSheetData = (SheetData)GetWorkSheet(sheetId).Where(x => x.LocalName == "sheetData").First();
     Row currentRow = new Row();
     Cell cell = new Cell();
     currentRow.RowIndex = Convert.ToUInt32(cSheetData.ChildElements.Count()) + 1;
     cell.DataType = new EnumValue<CellValues>(CellValues.Number);
     cell.CellValue = new CellValue(value.Value);
     cell.StyleIndex = 1;
     cell.CellReference = nextCol + rowIndex;
     currentRow.AppendChild(cell);
     cSheetData.Append(currentRow);

     GetWorkSheet(sheetId).Save(); //this returns the reference to my sheet doc to save it

希望这对你有所帮助