#应用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;

        }

    }

}
巴特西

#应用openxml读写excel代码

最新文章

热门文章
