一是不通用(仅能处理微软的Office,不能与其它非Windows平台交换数据),
二是性能差(导出一个Excel,如果记录数上万条,速度很慢),
三是服务器通常要安装Office Web Components(即OWC组件)。
using System; using System.IO; using System.Linq; using System.Windows.Forms; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; namespace ExcelSample { class Program { static void Main(string[] args) { string excelFilePath = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "Sample.xlsx"); using (SpreadsheetDocument excelDocument = CreateSpreadsheetDocument(excelFilePath)) { //添加工作表(Sheet)-"博客园" WorksheetPart worksheetPart = InsertWorksheet(excelDocument.WorkbookPart,"博客园"); //工作表插入单元格A1 Cell cell = InsertCellInWorksheet("A", 1, worksheetPart); //创建多个工作表可共用的字符串容器 SharedStringTablePart shareStringPart = CreateSharedStringTablePart(excelDocument.WorkbookPart); //在共用字符串容器里插入一个字符串 int strIndex = InsertSharedStringItem("菩提树下的杨过 http://www.cnblogs.com/yjmyzz", shareStringPart); //设置单元格的"值" cell.DataType = new EnumValue<CellValues>(CellValues.SharedString); cell.CellValue = new CellValue(strIndex.ToString());//注:这里要设置为目标字符串在SharedStringTablePart中的索引 worksheetPart.Worksheet.Save(); } Console.WriteLine("{0} 已经成功生成!", excelFilePath); Console.Read(); } /// <summary> /// 创建一个SpreadsheetDocument对象 /// </summary> /// <param name="excelFileName"></param> /// <returns></returns> static SpreadsheetDocument CreateSpreadsheetDocument(string excelFileName) { SpreadsheetDocument excel = SpreadsheetDocument.Create(excelFileName, SpreadsheetDocumentType.Workbook, true); WorkbookPart workbookpart = excel.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); return excel; } /// <summary> /// 插入worksheet /// </summary> /// <param name="workbookPart"></param> /// <returns></returns> private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName = null) { //创建一个新的WorkssheetPart(后面将用它来容纳具体的Sheet) WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); //取得Sheet集合 Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>(); if (sheets == null) { sheets = workbookPart.Workbook.AppendChild<Sheets>(new Sheets()); } string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); //得到Sheet的唯一序号 uint sheetId = 1; if (sheets.Elements<Sheet>().Count() > 0) { sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1; } string sheetTempName = "Sheet" + sheetId; if (sheetName != null) { bool hasSameName = false; //检测是否有重名 foreach (var item in sheets.Elements<Sheet>()) { if (item.Name == sheetName) { hasSameName = true; break; } } if (!hasSameName) { sheetTempName = sheetName; } } //创建Sheet实例并将它与sheets关联 Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetTempName }; sheets.Append(sheet); workbookPart.Workbook.Save(); return newWorksheetPart; } /// <summary> /// 创建一个SharedStringTablePart(相当于各Sheet共用的存放字符串的容器) /// </summary> /// <param name="workbookPart"></param> /// <returns></returns> private static SharedStringTablePart CreateSharedStringTablePart(WorkbookPart workbookPart) { SharedStringTablePart shareStringPart = null; if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0) { shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First(); } else { shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>(); } return shareStringPart; } /// <summary> /// 向工作表插入一个单元格 /// </summary> /// <param name="columnName">列名称</param> /// <param name="rowIndex">行索引</param> /// <param name="worksheetPart"></param> /// <returns></returns> private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart) { Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild<SheetData>(); string cellReference = columnName + rowIndex;//列的引用字符串,类似:"A3"或"B5" //如果指定的行存在,则直接返回该行,否则插入新行 Row row; if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) { row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First(); } else { row = new Row() { RowIndex = rowIndex }; sheetData.Append(row); } //如果该行没有指定ColumnName的列,则插入新列,否则直接返回该列 if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0) { return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First(); } else { //列必须按(字母)顺序插入,因此要先根据"列引用字符串"查找插入的位置 Cell refCell = null; foreach (Cell cell in row.Elements<Cell>()) { if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) { refCell = cell; break; } } Cell newCell = new Cell() { CellReference = cellReference }; row.InsertBefore(newCell, refCell); worksheet.Save(); return newCell; } } /// 向SharedStringTablePart添加字符串 /// </summary> /// <param name="text">字符串内容</param> /// <param name="shareStringPart">sharedStringTablePart内容</param> /// <returns>如果要添加的字符串已经存在,则直接返回该字符串的索引</returns> private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart) { //检测SharedStringTable是否存在,如果不存在,则创建一个 if (shareStringPart.SharedStringTable == null) { shareStringPart.SharedStringTable = new SharedStringTable(); } int i = 0; //遍历SharedStringTable中所有的Elements,查看目标字符串是否存在 foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>()) { if (item.InnerText == text) { return i; } i++; } //如果目标字符串不存在,则创建一个,同时把SharedStringTable的最后一个Elements的"索引+1"返回 shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text))); shareStringPart.SharedStringTable.Save(); return i; } } }