(操作Excel 2007以後)Open XML SDK 2.0 for Microsoft Office

Office 2007後，

是基於OPENXML來實現的，也就是說Office 2007都是用openxml來開發的。

 

如果不用安裝open xml sdk也可以,你會操作xml文檔就可以了,open xml sdk只不過封裝了一些操作xml的方法

 

因為office 2007文檔的本質就是xml文件,你把office 2007文檔的後綴名改為zip,然後解壓,你就會發現原來是一堆的xml文件而以

 

2003及以下的文檔本質是二進制文檔,2007以後的都是xml文檔,

不過生成的2007或之後的文檔在2000或2003都是可以打開的

 

具體使用參考資料：

http://msdn.microsoft.com/zh-cn/library/bb448854.aspx

 

 

 

 

//---------寫了一個　通過OpenXml來操作Excel封裝類---------

/*
 * 通過OpenXml來操作Excel封裝類
 *
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DocumentFormat.OpenXml.Packaging;
using System.Data;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;
using System.IO;

namespace Leo.RFID.Common.ExcelCommon
{
 /// <summary>
 /// 主要用於編輯單元格中的數據
 /// </summary>
 public class OpenXmlExcel
 {
  SpreadsheetDocument excelDoc;
  SharedStringTablePart shareStringPart;
  int tableId = 0;

  public OpenXmlExcel(string path)
  {

   excelDoc = SpreadsheetDocument.Open(path, true);

   //SharedStringTablePart shareStringPart;
   if (excelDoc.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
   {
    shareStringPart = excelDoc.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
   }
   else
   {
    shareStringPart = excelDoc.WorkbookPart.AddNewPart<SharedStringTablePart>();
   }
  }

  /// <summary>
  /// 增加一個新的WorkSheet
  /// </summary>
  public void AddNewWorksheet()
  {

   WorkbookPart workbookPart = excelDoc.WorkbookPart;

 

   WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();

   newWorksheetPart.Worksheet = new Worksheet(new SheetData());

   newWorksheetPart.Worksheet.Save();

   //CurrentWorksheetPart = newWorksheetPart;

   //workbookPart.SharedStringTablePart.SharedStringTable.Count = workbookPart.SharedStringTablePart.SharedStringTable.Count + 1; 

   //workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount = workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount + 1; 

   string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

   Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();

   uint sheetId = 1;

   if (sheets.Elements<Sheet>().Count() > 0)
   {

    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;

   }

 

   string sheetName = "Sheet" + sheetId;

 

   // Append the new worksheet and associate it with the workbook. 

   Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };

   sheets.Append(sheet);

   //workbookPart.Workbook.Save();

  }

  #region SetCellValue
  /// <summary>
  /// 給指定的單元格設置值
  /// </summary>
  /// <param name="rowIndex">行號,起始為1</param>
  /// <param name="columnIndex">列號,起始為1</param>
  /// <param name="value"></param>
  /// <returns >設定了值的單元格</returns>
  public void SetCellValue(uint rowIndex, uint columnIndex, string value, string worksheetName)
  {
   DataTable dt = new DataTable();

   dt.Columns.Add("Column1");

   dt.Rows.Add(value);

   //WorksheetPart worksheetPart = GetWorkSheetPart(excelDoc.WorkbookPart, worksheetName);
   WorksheetPart worksheetPart = GetWorkSheetPart(worksheetName);
   WriteDataIntoWorkSheet(rowIndex - 1, columnIndex - 1, dt, worksheetPart);
  }

  private void WriteDataIntoWorkSheet(uint startx, uint starty, DataTable dt, WorksheetPart worksheetPart)
  {

   //if (startx < 1) 

   //    startx = 1; 

   //if (starty < 1) 

   //    starty = 1; 

 

   //WorksheetPart worksheetPart = CurrentWorksheetPart;

   //starty -= 1; 

   int j = 0;

   foreach (DataRow dr in dt.Rows)
   {

    j++;

    for (uint i = 0; i < dt.Columns.Count; i++)
    {

     string name = GetColumnName(i + starty);

     string text = Convert.IsDBNull(dr[(int )i]) ? "" : dr[(int )i].ToString();

     int index = InsertSharedStringItem(text, shareStringPart);

     Cell cell = InsertCellInWorksheet(name, Convert.ToUInt32(j + startx), worksheetPart);

 

     cell.CellValue = new CellValue(index.ToString());

     cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

     worksheetPart.Worksheet.Save();

    }

   }

  }

  private int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
  {

   // If the part does not contain a SharedStringTable, create one. 

   if (shareStringPart.SharedStringTable == null)
   {

    shareStringPart.SharedStringTable = new SharedStringTable();

    shareStringPart.SharedStringTable.Count = 1;

    shareStringPart.SharedStringTable.UniqueCount = 1;

   }

   int i = 0;

   // Iterate through all the items in the SharedStringTable. If the text already exists, return its index. 

   foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
   {

    if (item.InnerText == text)
    {

     return i;

    }

    i++;

   }

 

   // The text does not exist in the part. Create the SharedStringItem and return its index. 

   shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));

   shareStringPart.SharedStringTable.Save();

 

   return i;

  }

  private string GetColumnName(uint index)
  {

   string name = "";

   char[] columnNames = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();

   int num = (int)index;

   do
   {

    int i = num % 26;

    name = columnNames[i] + name;

    num = num / 26 - 1;

   } while (num > -1);

   if (string.IsNullOrEmpty(name))

    name = "A";

   return name;

  }

  private WorksheetPart GetWorkSheetPart(string sheetName)
  {
   //取得工作表的ID關聯
   string relId = excelDoc.WorkbookPart.Workbook.Descendants<Sheet>()
   .Where(s => s.Name.Value.Equals(sheetName))
    .First()
    .Id;
   return (WorksheetPart)excelDoc.WorkbookPart.GetPartById(relId);
  }

  private Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
  {

   Worksheet worksheet = worksheetPart.Worksheet;

   SheetData sheetData = worksheet.GetFirstChild<SheetData>();

   string cellReference = columnName + rowIndex;

 

   // If the worksheet does not contain a row with the specified row index, insert one. 

   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);

   }

 

   // If there is not a cell with the specified column name, insert one.   

   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
   {

    // Cells must be in sequential order according to CellReference. Determine where to insert the new cell. 

    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;

   }

  }

  /// <summary>
  /// 設置單元格的公式
  /// </summary>
  /// <param name="rowIndex"></param>
  /// <param name="columnIndex"></param>
  /// <param name="cellFormula"></param>
  /// <param name="sheetName"></param>
  public void SetCellFormulaValue(uint rowIndex, uint columnIndex, string cellFormula,string sheetName)
  {
   WorksheetPart wp=GetWorkSheetPart (sheetName );
   string coulumName = GetColumnName(columnIndex);
   Cell cell = InsertCellInWorksheet(coulumName, rowIndex, wp );

   cell.CellFormula = new CellFormula(cellFormula);
   wp.Worksheet.Save();

  }

  /// <summary>
  ///
  /// </summary>
  /// <param name="sheetName"></param>
  public  void ClearAllFormulaValuesInSheet( string sheetName)
  {
   WorksheetPart worksheetPart = GetWorkSheetPart(sheetName);
   foreach (Row row in worksheetPart .Worksheet .GetFirstChild<Row>().Elements ())
   {
    foreach (Cell cell in row.Elements())
    {
     if (cell.CellFormula != null && cell.CellValue != null)
     {
      cell.CellValue.Remove();
     }
    }
   } worksheetPart.Worksheet.Save();
  }
  #endregion

  #region CopySheet

  /// <summary>
  /// Copy一個sheet,並插入到指定位置
  /// </summary>
  /// <param name="sourceSheetName">要copy的sheet名字</param>
  /// <param name="clonedSheetName">新Sheet的名字</param>
  /// <param name="positionIndex">新sheet的位置,以1為起始</param>
  public void CopySheet(string sourceSheetName, string clonedSheetName, int positionIndex)
  {
   //Open workbook
   //using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(filename, true))
   //{

   WorkbookPart workbookPart = excelDoc.WorkbookPart;
   //Get the source sheet to be copied
   WorksheetPart sourceSheetPart = GetWorkSheetPart(workbookPart, sourceSheetName);

   //Take advantage of AddPart for deep cloning
   SpreadsheetDocument tempSheet = SpreadsheetDocument.Create(new MemoryStream(), excelDoc.DocumentType);
   WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();
   WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart<WorksheetPart>(sourceSheetPart);
   //Add cloned sheet and all associated parts to workbook
   WorksheetPart clonedSheet = workbookPart.AddPart<WorksheetPart>(tempWorksheetPart);

   //Table definition parts are somewhat special and need unique ids...so let's make an id based on count
   int numTableDefParts = sourceSheetPart.GetPartsCountOfType<TableDefinitionPart>();
   tableId = numTableDefParts;
   //Clean up table definition parts (tables need unique ids)
   if (numTableDefParts != 0)
    FixupTableParts(clonedSheet, numTableDefParts);
   //There should only be one sheet that has focus
   CleanView(clonedSheet);

   //Add new sheet to main workbook part
   Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();

   Sheet copiedSheet = new Sheet();
   copiedSheet.Name = clonedSheetName;
   copiedSheet.Id = workbookPart.GetIdOfPart(clonedSheet);
   copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;
   sheets.InsertAt<Sheet>(copiedSheet, positionIndex);
   //sheets.Append(copiedSheet);
   //sheets.InsertAfter<Sheet >(copiedSheet ,sheets.Where (s=>s.GetFirstChild<Sheet >().Name == sheetName);
   //Save Changes
   workbookPart.Workbook.Save();

   //}
  }

  void CleanView(WorksheetPart worksheetPart)
  {
   //There can only be one sheet that has focus
   SheetViews views = worksheetPart.Worksheet.GetFirstChild<SheetViews>();
   if (views != null)
   {
    views.Remove();
    worksheetPart.Worksheet.Save();
   }
  }

  void FixupTableParts(WorksheetPart worksheetPart, int numTableDefParts)
  {
   //Every table needs a unique id and name
   foreach (TableDefinitionPart tableDefPart in worksheetPart.TableDefinitionParts)
   {
    tableId++;
    tableDefPart.Table.Id = (uint)tableId;
    tableDefPart.Table.DisplayName = "CopiedTable" + tableId;
    tableDefPart.Table.Name = "CopiedTable" + tableId;
    tableDefPart.Table.Save();
   }
  }

  WorksheetPart GetWorkSheetPart(WorkbookPart workbookPart, string sheetName)
  {
   //Get the relationship id of the sheetname
   string relId = workbookPart.Workbook.Descendants<Sheet>()
   .Where(s => s.Name.Value.Equals(sheetName))
   .First()
   .Id;
   return (WorksheetPart)workbookPart.GetPartById(relId);
  }

 

  #endregion

  #region Insert New Row By Copy
  /// <summary>
  /// 獲取指定行或通過指定行COPY後插入一個新行
  /// </summary>
  /// <param name="rowIndex"></param>
  /// <param name="wrksheetPartName"></param>
  /// <param name="Mod"></param>
  /// <returns></returns>
  public Row GetRow(uint rowIndex, string wrksheetPartName, RowMode Mod)
  {
   WorksheetPart wrksheetPart = GetWorkSheetPart(excelDoc.WorkbookPart, wrksheetPartName);
   Worksheet worksheet = wrksheetPart.Worksheet;
   SheetData sheetData = worksheet.GetFirstChild<SheetData>();
   // If the worksheet does not contain a row with the specified row index, insert one.
   Row row = null;
   if (sheetData.Elements<Row>().Where(r => rowIndex == r.RowIndex).Count() != 0)
   {
    Row refRow = sheetData.Elements<Row>().Where(r => rowIndex == r.RowIndex).First();
    if ((refRow != null) && (Mod == RowMode.Insert))
    {
     //Copy row from refRow and insert it
     row = CopyToLine(refRow, rowIndex, sheetData);
     //Update dataValidation (copy drop down list)
     DataValidations dvs = worksheet.GetFirstChild<DataValidations>();
     if (dvs != null)
     {
      foreach (DataValidation dv in dvs.Descendants<DataValidation>())
      {
       foreach (StringValue sv in dv.SequenceOfReferences.Items)
       {
        sv.Value = sv.Value.Replace(row.RowIndex.ToString(), refRow.RowIndex.ToString());
       }
      }
     }
    }
    else if ((refRow != null) && (Mod == RowMode.Update))
    {
     row = refRow;
    }
    else
    {
     row = new Row() { RowIndex = rowIndex };
     sheetData.Append(row);
    }
   }
   else
   {
    row = new Row() { RowIndex = rowIndex };
    sheetData.Append(row);
   }
   return row;
  }

  /// <summary>
  /// 拷貝源行的數據和格式後,在源行後新插入一行
  /// </summary>
  /// <param name="sourceRowIndex">源行行號,以1為開始</param>
  /// <param name="workSheetName">要操作的workSheetName</param>
  /// <returns></returns>
  public void InsertRowByCopy(uint sourceRowIndex, string workSheetName)
  {
   WorksheetPart wrksheetPart = GetWorkSheetPart(excelDoc.WorkbookPart, workSheetName);
   Worksheet worksheet = wrksheetPart.Worksheet;
   SheetData sheetData = worksheet.GetFirstChild<SheetData>();
   // If the worksheet does not contain a row with the specified row index, insert one.
   Row row = null;
   if (sheetData.Elements<Row>().Where(r => sourceRowIndex == r.RowIndex).Count() != 0)
   {
    Row refRow = sheetData.Elements<Row>().Where(r => sourceRowIndex == r.RowIndex).First();
    if ((refRow != null))
    {
     //Copy row from refRow and insert it
     row = CopyToLine(refRow, sourceRowIndex, sheetData);
     //Update dataValidation (copy drop down list)
     DataValidations dvs = worksheet.GetFirstChild<DataValidations>();
     if (dvs != null)
     {
      foreach (DataValidation dv in dvs.Descendants<DataValidation>())
      {
       foreach (StringValue sv in dv.SequenceOfReferences.Items)
       {
        sv.Value = sv.Value.Replace(row.RowIndex.ToString(), refRow.RowIndex.ToString());
       }
      }
     }
    }
    //else if ((refRow != null) && (Mod == RowMode.Update))
    //{
    //    row = refRow;
    //}
    else
    {
     row = new Row() { RowIndex = sourceRowIndex };
     sheetData.Append(row);
    }
   }
   else
   {
    row = new Row() { RowIndex = sourceRowIndex };
    sheetData.Append(row);
   }
   //儲存
   excelDoc.WorkbookPart.Workbook.Save();
   //return row;
  }

  /// <summary>
  /// Copy an existing row and insert it
  ///We don't need to copy styles of a refRow because a CloneNode() or Clone() methods do it for us
  /// </summary>
  /// <param name="refRow"></param>
  /// <param name="rowIndex"></param>
  /// <param name="sheetData"></param>
  /// <returns></returns>
  internal Row CopyToLine(Row refRow, uint rowIndex, SheetData sheetData)
  {
   uint newRowIndex;
   var newRow = (Row)refRow.CloneNode(true);
   // Loop through all the rows in the worksheet with higher row
   // index values than the one you just added. For each one,
   // increment the existing row index.
   IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= rowIndex);
   foreach (Row row in rows)
   {
    newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);

    foreach (Cell cell in row.Elements<Cell>())
    {
     // Update the references for reserved cells.
     string cellReference = cell.CellReference.Value;
     cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
    }
    // Update the row index.
    row.RowIndex = new UInt32Value(newRowIndex);
   }

   sheetData.InsertBefore(newRow, refRow);
   return newRow;
  }
  #endregion

  public void Save()
  {
   excelDoc.WorkbookPart.Workbook.Save();
  }

  public void Dispose()
  {
   excelDoc.Dispose();
  }

 }

 

 public enum RowMode
 {
  Insert,
  Update
 }

}