使用OpenXmlWriter SAX创建具有样式标签的Excel文件

本文介绍了使用OpenXmlWriter SAX创建具有样式标签的Excel文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试使用OpenXmlWriter（SAX）编写一个带有样式的Excel Xlsx电子表格。

我可以使用行和列创建文件（将它们填充为字符串）。
我正在寻找一个简单的代码如何使用粗体字体的第一行（标题）。

我没有一个模板文件开始因为文件将是动态的。

我发现了几篇关于如何添加WorkbookStylesPart的文章，但他们都在使用DOM。因为我需要写大量的行，所以DOM对我来说是不行的。

任何人都可以指向正确的方向吗？
使用WriteStartElement和OpenXmlAttribute时，将标题行添加为粗体的简单代码。

谢谢odansky

解决方案

添加StyleSheet是一次性工作。之后，您只需要在创建新单元格时简单地引用定义的样式ID。

热点将样式表添加到电子表格[使用粗体文本样式]

  private WorkbookStylesPart AddStyleSheet（SpreadsheetDocument电子表格）
 {
 WorkbookStylesPart stylesheet = spreadsheet.WorkbookPart.AddNewPart< WorkbookStylesPart>（）; 
 
样式表workbookstylesheet = new Stylesheet（）; 
 
 Font font0 = new Font（）; //默认字体
 
字体font1 = new Font（）; // Bold font 
 Bold bold = new Bold（）; 
 font1.Append（bold）; 
 
字体字体=新字体（）; //< APENDING Fonts> 
 fonts.Append（font0）; 
 fonts.Append（font1）; 
 
 //< Fills> 
填充fill0 = new Fill（）; //默认填充
 
填充fills = new Fills（）; //< APENDING Fills> 
 fills.Append（fill0）; 
 
 //< Borders> 
边框border0 =新的Border（）; // Defualt border 
 
边框边框=新的边框（）; //< APENDING Borders> 
 borders.Append（border0）; 
 
 //< CellFormats> 
 CellFormat cellformat0 = new CellFormat（）{FontId = 0，FillId = 0，BorderId = 0}; //默认风格：强制|样式ID = 0 
 
 CellFormat cellformat1 = new CellFormat（）{FontId = 1}; //带粗体的样式样式ID = 1 
 
 
 //< APENDING CellFormats> 
 CellFormats cellformats = new CellFormats（）; 
 cellformats.Append（cellformat0）; 
 cellformats.Append（cellformat1）; 
 
 
 //附加FONTS，FILLS，BORDERS& CellFormats到样式表<保留ORDER> 
 workbookstylesheet.Append（fonts）; 
 workbookstylesheet.Append（fills）; 
 workbookstylesheet.Append（border）; 
 workbookstylesheet.Append（cellformats）; 
 
 // Finalize 
 stylesheet.Stylesheet = workbookstylesheet; 
 stylesheet.Stylesheet.Save（）; 
 
返回样式表; 
}

现在，当您创建单元格时，请执行以下操作以引用粗体文本

  Cell c1 = new Cell（）{StyleIndex = Convert.ToUInt32（1）}; //使用粗体文本分配我们定义的样式;样式ID 1

附加注意：添加工作簿后，您需要添加样式表。

更多关于SAX方法：您可以在首次创建要打开的模板文件插入数据单元时定义样式。而在添加数据单元格时，使用ID可以引用定义的样式。

一个简单的工作电子表格（）

  public static void CreateSpreadsheetWorkbook（string filepath）
 {
 //通过提供文件路径创建一个电子表格文档。 
 //默认情况下，AutoSave = true，Editable = true，Type = xlsx。 
 SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create（filepath，SpreadsheetDocumentType.Workbook）; 
 
 //将WorkbookPart添加到文档。 
 WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart（）; 
 workbookpart.Workbook = new Workbook（）; 
 
 AddStyleSheet（spreadsheetDocument）//< ==使用上面的函数添加样式表
 
 //将WorksheetPart添加到WorkbookPart。 
 WorksheetPart worksheetPart = workbookpart.AddNewPart< WorksheetPart>（）; 
 worksheetPart.Worksheet = new Worksheet（new SheetData（））; 
 
 //将工作簿添加到工作簿。 
表格Sheet = spreadsheetDocument.WorkbookPart.Workbook.AppendChild< Sheets>（new Sheets（））; 
 
 //附加一个新的工作表并将其与工作簿相关联。 
 Sheet sheet = new Sheet（）{Id = spreadsheetDocument.WorkbookPart.GetIdOfPart（worksheetPart），SheetId = 1，Name =mySheet}; 
 sheets.Append（sheet）; 
 
 workbookpart.Workbook.Save（）; 
 
 //关闭文档。 
 spreadsheetDocument.Close（）; 
}

I am trying to write an Excel Xlsx spreadsheet with styling using OpenXmlWriter (SAX).

I am able to create the file with rows and columns (populate them as strings).I am looking for a simple code on how to make the first row (header) with bold font.

I do not have a template file to start with as the file will be dynamic.

I found a few articles on how to add WorkbookStylesPart, but they are all using the DOM. As i need to write large number of rows, the DOM will not work for me.

Could anyone point me at the right direction?Simple code to add a header row as bold when using WriteStartElement and OpenXmlAttribute.

Thanks, odansky

解决方案

Adding StyleSheet is one time work. After that you just need to simply refer the defined style ID when creating new cells.

Hot to add a stylesheet to spreadsheet [With Bold text style]

private WorkbookStylesPart AddStyleSheet(SpreadsheetDocument spreadsheet)
 {
       WorkbookStylesPart stylesheet = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();

       Stylesheet workbookstylesheet = new Stylesheet();

       Font font0 = new Font();         // Default font

       Font font1 = new Font();         // Bold font
       Bold bold = new Bold();
       font1.Append(bold);

       Fonts fonts = new Fonts();      // <APENDING Fonts>
       fonts.Append(font0);
       fonts.Append(font1);

       // <Fills>
       Fill fill0 = new Fill();        // Default fill

       Fills fills = new Fills();      // <APENDING Fills>
       fills.Append(fill0);

       // <Borders>
       Border border0 = new Border();     // Defualt border

       Borders borders = new Borders();    // <APENDING Borders>
       borders.Append(border0);

       // <CellFormats>
        CellFormat cellformat0 = new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }; // Default style : Mandatory | Style ID =0

       CellFormat cellformat1 = new CellFormat() { FontId = 1 };  // Style with Bold text ; Style ID = 1


      // <APENDING CellFormats>
      CellFormats cellformats = new CellFormats();
      cellformats.Append(cellformat0);
      cellformats.Append(cellformat1);


       // Append FONTS, FILLS , BORDERS & CellFormats to stylesheet <Preserve the ORDER>
      workbookstylesheet.Append(fonts);
      workbookstylesheet.Append(fills);
      workbookstylesheet.Append(borders);
      workbookstylesheet.Append(cellformats);

      // Finalize
      stylesheet.Stylesheet = workbookstylesheet;
      stylesheet.Stylesheet.Save();

      return stylesheet;
}

Now when you create a cell do following to refer to the Bold text

Cell c1 = new Cell(){StyleIndex = Convert.ToUInt32(1)}; // Assign our defined style with Bold text ; Style ID 1

Additional Note : You need to add stylesheet after adding workbookpart of the spreadsheet.

More regarding SAX approach : You can define styles when you first create the template file which you gonna open to insert data cells. And when adding data cells refer to the defined styles using ID.

A simple working spreadsheet with style (MSDN)

public static void CreateSpreadsheetWorkbook(string filepath)
{
    // Create a spreadsheet document by supplying the filepath.
    // By default, AutoSave = true, Editable = true, and Type = xlsx.
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);

    // Add a WorkbookPart to the document.
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();

   AddStyleSheet(spreadsheetDocument) // <== Adding stylesheet using above function

    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet(new SheetData());

    // Add Sheets to the Workbook.
    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

    // Append a new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
    sheets.Append(sheet);

    workbookpart.Workbook.Save();

    // Close the document.
    spreadsheetDocument.Close();
}

这篇关于使用OpenXmlWriter SAX创建具有样式标签的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！