vsto c# openxml操作excel

word ppt excel 后缀加rar 都可以解压看到资源文件

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Text.RegularExpressions;

namespace OpenXmlTest
{
    class TLExcel
    {
        #region 全局属性及方法
        public static int TLOK = 0;
        public static int TLERR = -1;
        public static void ShowMessage(string strMsg)
        {
            Console.WriteLine(strMsg);
        }
        #endregion

        #region 私有属性
        private SpreadsheetDocument _spreadsheetDocument;
        #endregion

        public TLExcel()
        { }

        /* 打开EXCEL */
        public int Open(string szPath) 
        {
            try
            {
                _spreadsheetDocument = SpreadsheetDocument.Open(szPath, true);
                if (null == _spreadsheetDocument)
                    return TLERR;

                return TLOK;
            }
            catch (Exception ex)
            {
                ShowMessage("[Func:Open], Exception:" + ex.Message);
                return TLERR;
            }
            
        }
        
        /* 创建EXCEL */
        public int Create(string szPath)
        {
            try
            {
                _spreadsheetDocument = SpreadsheetDocument.Create(szPath, SpreadsheetDocumentType.Workbook);
                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = _spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();

                // 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 = "Sheet1" };
                sheets.Append(sheet);

                workbookpart.Workbook.Save();

                return TLOK;
            }
            catch (Exception ex)
            {
                ShowMessage("[Func:Create], Exception:" + ex.Message);
                return TLERR;
            }
        }

        /* 保存Excel */
        public void Save()
        {
            try
            {
                _spreadsheetDocument.WorkbookPart.Workbook.Save();
            }
            catch (Exception ex)
            {
                ShowMessage("[Func:Save],Exception:" + ex.Message);
            }
            
        }

        /* 关闭Excel */
        public void Close()
        {
            try
            {
                this.Save();    //先保存
                _spreadsheetDocument.Close();
            }
            catch (Exception ex)
            {
                ShowMessage("[Func:Save],Exception:" + ex.Message);
            }
        }
        #region 读写EXCEL
        /// <summary>
        /// 读取单元格数据
        /// </summary>
        /// <param name="iSheetNum">Sheet编号</param>
        /// <param name="iRowNum">行号</param>
        /// <param name="strColName">列名</param>
        /// <param name="iColNum">返回的单元格数据</param>
        /// <returns>状态</returns>
        public int ReadCellValue(int iSheetNum, uint iRowNum, string strColName, ref string strValue)
        {
            Worksheet sheet = GetSpreadsheetWorksheet(iSheetNum);
            if (null == sheet)
            {
                return TLERR;
            }

            Cell cell = GetSpreadsheetCell(sheet, strColName, iRowNum);

            if (null == cell)
            {
                strValue = null;
                return TLERR;
            }

            string value = cell.CellValue.InnerText;

            SharedStringTablePart shareStringPart = this.GetSharedStringTable();
            strValue = shareStringPart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;

            return TLOK;
        }

        //写入数据
        public int WriteCellValue(int iSheetNum, uint iRowNum, string strColName, string strValue)
        {
            //bool bIsNewCell = false;    //是否是新单元格

            Worksheet sheet = GetSpreadsheetWorksheet(iSheetNum);
            if (null == sheet)
            {
                return TLERR;
            }

            // Get the SharedStringTablePart and add the result to it.
            // If the SharedStringPart does not exist, create a new one.
            SharedStringTablePart shareStringPart = this.GetSharedStringTable();

            Cell cell = GetSpreadsheetCell(sheet, strColName, iRowNum);

            if (null == cell)
            {
                //插入一个单元格
                cell = InsertCellInWorksheet(strColName, iRowNum, sheet);

                int iIndex = InsertSharedStringItem(strValue, shareStringPart);
                // Set the value of the cell.
                cell.CellValue = new CellValue(iIndex.ToString());
                cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
            }
            else
            {
                int iIndex;
                if (null == cell.CellValue)
                {
                    cell.CellValue = new CellValue();
                    iIndex = InsertSharedStringItem(strValue, shareStringPart);
                    cell.CellValue.Text = iIndex.ToString();
                }
                else 
                { 
                    iIndex = Int32.Parse(cell.CellValue.Text);
                }
              
                OpenXmlElement xmlElement = shareStringPart.SharedStringTable.ChildElements[iIndex];
                ((SharedStringItem)xmlElement).Text.Text = strValue;
            }
            
            return TLOK;
        }
        #endregion

        //获取行数
        public int GetRowsAndCols(int iSheetNum, ref int iRows)
        {
            Worksheet workSheet = GetSpreadsheetWorksheet(iSheetNum);
            if (null == workSheet)
            {
                return TLERR;
            }

            IEnumerable<Row> rows = workSheet.Descendants<Row>();
            iRows = rows.Count();

            return TLOK;
        }

        //获取Sheet数目
        public int GetWorksheetNumber()
        {
            IEnumerable<Sheet> sheets = _spreadsheetDocument.WorkbookPart.Workbook.Descendants<Sheet>();
            return sheets.Count();
        }

        //获取sheetName
        public int GetWorksheetName(int iSheetNum, ref string strSheetName)
        {
            IEnumerable<Sheet> sheets = _spreadsheetDocument.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.SheetId == iSheetNum);
            if (1 != sheets.Count())
            {
                ShowMessage("[Func:ReadCellValue], 指定的Sheet不存在");
                return TLERR;
            }

            strSheetName = sheets.First().Name;

            return TLOK;
        }

        private Sheet GetSheet(int iSheetNum)
        {
            IEnumerable<Sheet> sheets = _spreadsheetDocument.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.SheetId == iSheetNum);
            if (1 != sheets.Count())
            {
                ShowMessage("[Func:ReadCellValue], 指定的Sheet不存在");
                return null;
            }

            return sheets.First();
        }

        private Sheet GetSheet(string strSheetName)
        {
            IEnumerable<Sheet> sheets = _spreadsheetDocument.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == strSheetName);
            if (1 != sheets.Count())
            {
                ShowMessage("[Func:ReadCellValue], 指定的Sheet不存在");
                return null;
            }

            return sheets.First();
        }

        private SharedStringTablePart GetSharedStringTable()
        {
            SharedStringTablePart shareStringPart;
            if (_spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
            {
                shareStringPart = _spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
            }
            else
            {
                shareStringPart = _spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
            }

            return shareStringPart;
        }

        //获取workSheet
        private Worksheet GetSpreadsheetWorksheet(int iSheetNum)
        {
            Sheet sheet = GetSheet(iSheetNum);
            if (null == sheet)
                return null;

            WorksheetPart worksheetPart = (WorksheetPart)_spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id);
            Worksheet worksheet = worksheetPart.Worksheet;

            return worksheet;
        }

        //获取workSheet
        private Worksheet GetSpreadsheetWorksheet(string strSheetName)
        {
            Sheet sheet = GetSheet(strSheetName);
            if (null == sheet)
                return null;

            WorksheetPart worksheetPart = (WorksheetPart)_spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id);
            Worksheet worksheet = worksheetPart.Worksheet;

            return worksheet;
        }

        // Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
        private static Cell GetSpreadsheetCell(Worksheet worksheet, string columnName, uint rowIndex)
        {
            IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex);
            if (rows.Count() == 0)
            {
                // A cell does not exist at the specified row.
                return null;
            }

            IEnumerable<Cell> cells = rows.First().Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0);
            if (cells.Count() == 0)
            {
                // A cell does not exist at the specified column, in the specified row.
                return null;
            }

            return cells.First();
        }

        // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
        // If the cell already exists, returns it. 
        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, Worksheet worksheet/*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;
            }
        }

        // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
        // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
        {
            // If the part does not contain a SharedStringTable, create it.
            if (shareStringPart.SharedStringTable == null)
            {
                shareStringPart.SharedStringTable = new SharedStringTable();
            }

            int i = 0;
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                {
                    // The text already exists in the part. Return its index.
                    return i;
                }

                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem.
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            shareStringPart.SharedStringTable.Save();

            return i;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Text.RegularExpressions;
using Excel = Microsoft.Office.Interop.Excel;

namespace OpenXmlTest
{
    class Program
    {
        public static void UseCommom()
        {
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook expWorkBookConfig = null;//EXCEL 产品配置文件 （中文）
            Excel.Workbooks expWorkBooks = null;
            //打开Excle文件 创建表格对象
            excelApp.Visible = false; excelApp.DisplayAlerts = false;
            try
            {
                expWorkBooks = excelApp.Workbooks;
                expWorkBookConfig = expWorkBooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                

                Excel.Worksheet sheet = expWorkBookConfig.Worksheets[1];

                for (int iLine = 1; iLine < 200; iLine++)
                {
                    for (char ch = 'A'; ch <= 'Z'; ch++)
                    {
                        string str = string.Format("{0}", ch);
                        Excel.Range ACell = sheet.get_Range(str + iLine.ToString(), System.Reflection.Missing.Value);
                        ACell.Value2 = "TestTestTestTestTestTestTestTestTestTestTestTestTestTestTest";
                    }
                }

                expWorkBookConfig.SaveAs(@"D:\OpenXmlTest\bin\Release\Commom.xlsx",
              System.Reflection.Missing.Value,
              System.Reflection.Missing.Value,
              System.Reflection.Missing.Value,
              System.Reflection.Missing.Value,
              System.Reflection.Missing.Value,
              Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
              System.Reflection.Missing.Value,
              System.Reflection.Missing.Value,
              System.Reflection.Missing.Value,
              System.Reflection.Missing.Value,
              System.Reflection.Missing.Value);
                expWorkBookConfig.Close();
                excelApp.Quit();
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.Message);
                excelApp.Quit();
                expWorkBookConfig = null;
                excelApp = null;
            }

            GC.Collect();
        }

        public static void UseOpenXml()
        {
            TLExcel doc = new TLExcel();
            if (TLExcel.TLOK == doc.Create("OpenXml.xlsx"))
            {
                Console.WriteLine("开始时间：" + DateTime.Now);
                for (int iLine = 1; iLine < 200; iLine++)
                {
                    for (char ch = 'A'; ch <= 'Z'; ch++)
                    {
                        string str = string.Format("{0}", ch);

                        doc.WriteCellValue(1, (uint)iLine, str, "TestTestTestTestTestTestTestTestTestTestTestTestTestTestTest");
                    }
                }
                Console.WriteLine("结束时间：" + DateTime.Now);
            }
            doc.Close();
        }

        static void Main(string[] args)
        {
            Console.WriteLine("OpenXml:");
            UseOpenXml();

            Console.WriteLine("Commom:");
            Console.WriteLine("开始时间：" + DateTime.Now);
            UseCommom();
            Console.WriteLine("结束时间：" + DateTime.Now);
            Console.ReadKey();
        }
    }
}

http://officeopenxml.com/anatomyofOOXML-xlsx.php

https://www.ecma-international.org/publications-and-standards/standards/ecma-376/