#应用openxml读写excel代码详解编程语言

这个例子比较简单，没有考虑格式之类的问题。

using DocumentFormat.OpenXml.Packaging; 
using DocumentFormat.OpenXml.Spreadsheet; 
using System; 
using System.Collections.Generic; 
using System.IO; 
using System.Linq; 
using System.Text.RegularExpressions; 
namespace JobTool 
{ 
    public class ExcelBll 
    { 
        private void GetColumnNameAndRowIndex(string cellReference, out string columnName, out UInt32 rowIndex) 
        { 
            var regex = new Regex("[A-Za-z]+"); 
            var match = regex.Match(cellReference); 
            columnName = match.Value; 
            string s = cellReference.Replace(columnName, ""); 
            rowIndex = UInt32.Parse(s); 
        } 
        public List<ExcelCellEntity> ReadExcel(string excelPath, int columnCount, int rowCount) 
        { 
            List<ExcelCellEntity> ret = new List<ExcelCellEntity>(); 
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelPath, false)) 
            { 
                WorkbookPart wbPart = document.WorkbookPart; 
                var sheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(); 
                WorksheetPart worksheet = (WorksheetPart)wbPart.GetPartById(sheet.Id); 
                SheetData SheetData1 = worksheet.Worksheet.Elements<SheetData>().FirstOrDefault(); 
                foreach (var inst in SheetData1.Descendants<Cell>()) 
                { 
                    ExcelCellEntity entity = new ExcelCellEntity(); 
                    ret.Add(entity); 
                    entity.Value = this.GetValue(inst, wbPart); 
                    string columnName = ""; 
                    UInt32 rowIndex = 1; 
                    GetColumnNameAndRowIndex(inst.CellReference, out columnName, out rowIndex); 
                    entity.Column = columnName; 
                    entity.Row = rowIndex; 
                } 
            } 
            return ret; 
        } 
        public void WriteExcel(string excelPath, List<ExcelCellEntity> datas) 
        { 
            File.Copy("blank.xlsx", excelPath, true); 
            List<ExcelCellEntity> ret = new List<ExcelCellEntity>(); 
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelPath, true)) 
            { 
                WorkbookPart wbPart = document.WorkbookPart; 
                var sheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(); 
                WorksheetPart worksheet = (WorksheetPart)wbPart.GetPartById(sheet.Id); 
                SheetData SheetData1 = worksheet.Worksheet.Elements<SheetData>().FirstOrDefault(); 
                foreach (var inst in datas) 
                { 
                    this.WriteExcel1(SheetData1, inst.Column, inst.Row, inst.Value); 
                } 
                wbPart.Workbook.Save(); 
            } 
        } 
        private Cell CreateTextCell(string header, UInt32 index, string text) 
        { 
            var cell = new Cell 
            { 
                DataType = CellValues.InlineString, 
                CellReference = header + index 
            }; 
            var istring = new InlineString(); 
            var t = new Text { Text = text }; 
            istring.AppendChild(t); 
            cell.AppendChild(istring); 
            return cell; 
        } 
        private void WriteExcel1(SheetData sheetData, string header, UInt32 index, string text) 
        { 
            Row r1 = sheetData.Descendants<Row>().Where(a => a.RowIndex == index).FirstOrDefault(); 
            if (r1 == null) 
            { 
                r1 = new Row() { RowIndex = index }; 
                sheetData.Append(r1); 
            } 
            r1.Append(CreateTextCell(header, index, text)); 
        } 
        public String GetValue(Cell cell, WorkbookPart wbPart) 
        { 
            SharedStringTablePart stringTablePart = wbPart.SharedStringTablePart; 
            if (cell.ChildElements.Count == 0) 
                return null; 
            String value = cell.CellValue.InnerText; 
            if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString)) 
                return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText; 
            return value; 
        } 
    } 
}