OpenXml讀寫(xiě)Excel實(shí)例代碼

更新時(shí)間：2013年12月02日 14:50:47 作者：

這篇文章主要介紹了OpenXml讀寫(xiě)Excel代碼分享，大家參考使用

新版本的xlsx是使用新的存儲(chǔ)格式，貌似是處理過(guò)的XML。

對(duì)于OpenXML我網(wǎng)上搜了一下，很多人沒(méi)有介紹。所以我就這里推薦下，相信會(huì)成為信息系統(tǒng)開(kāi)發(fā)的必備。

先寫(xiě)出個(gè)例子，會(huì)發(fā)現(xiàn)如此的簡(jiǎn)介：

復(fù)制代碼代碼如下:

using System;
using System.Collections.Generic;
using System.Text;
using XFormular.config;
using System.IO;
using com.xtar.amfx;
using System.Runtime.Serialization.Formatters.Binary;
using System.Data;

namespace XFormular.test
{
    class Class1
    {
        public void test()
        {
            DataTable table = new DataTable("1");
            table.Columns.Add("2");
            for (int i = 0; i < 10; i++)
            {
                DataRow row = table.NewRow();
                row[0] = i;
                table.Rows.Add(row);
            }

List<DataTable> lsit = new List<DataTable>();
lsit.Add(table);

            OpenXmlSDKExporter.Export(AppDomain.CurrentDomain.BaseDirectory + "\\excel.xlsx", lsit);
        }
    }
}

寫(xiě)出代碼

復(fù)制代碼代碼如下:

using System;
using System.IO;
using System.Windows.Forms;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Extensions;
using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;

namespace XFormular
{
    class OpenXmlSDKExporter
    {
        private static string[] Level = {"A", "B", "C", "D", "E", "F", "G",
    "H", "I", "G", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T",
    "U", "V", "W", "X", "Y", "Z" };

        public static List<DataTable> Import(string path)
        {
            List<DataTable> tables = new List<DataTable>();

if (path.EndsWith(ExcelHelper.POSTFIX_SVN))
return tables;

            using (MemoryStream stream = SpreadsheetReader.StreamFromFile(path))
            {
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true))
                {
                    foreach (Sheet sheet in doc.WorkbookPart.Workbook.Descendants<Sheet>())
                    {
                        DataTable table = new DataTable(sheet.Name.Value);

WorksheetPart worksheet = (WorksheetPart)doc.WorkbookPart.GetPartById(sheet.Id);

List<string> columnsNames = new List<string>();

                        foreach (Row row in worksheet.Worksheet.Descendants<Row>())
                        {
                            foreach (Cell cell in row)
                            {
                                string columnName = Regex.Match(cell.CellReference.Value, "[a-zA-Z]+").Value;

                                if (!columnsNames.Contains(columnName))
                                {
                                    columnsNames.Add(columnName);
                                }

}
}

columnsNames.Sort(CompareColumn);

                        foreach (string columnName in columnsNames)
                        {
                            table.Columns.Add(columnName);
                        }

                        foreach (Row row in worksheet.Worksheet.Descendants<Row>())
                        {
                            DataRow tableRow = table.NewRow();
                            table.Rows.Add(tableRow);

                            foreach (Cell cell in row)
                            {
                                string columnName = Regex.Match(cell.CellReference.Value, "[a-zA-Z]+").Value;
                                tableRow[columnName] = GetValue(cell, doc.WorkbookPart.SharedStringTablePart);
                            }
                        }

                        if (table.Rows.Count <= 0)
                            continue;
                        if (table.Columns.Count <= 0)
                            continue;

                        tables.Add(table);
                    }
                }
            }

return tables;
}

public static String GetValue(Cell cell, SharedStringTablePart stringTablePart)
{

if (cell.ChildElements.Count == 0)

return null;

//get cell value

String value = cell.CellValue.InnerText;

//Look up real value from shared string table

if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))

value = stringTablePart.SharedStringTable

.ChildElements[Int32.Parse(value)]

.InnerText;

return value;

}

        public static void Export(string path, List<DataTable> tables)
        {
            using (MemoryStream stream = SpreadsheetReader.Create())
            {
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true))
                {
                    SpreadsheetWriter.RemoveWorksheet(doc, "Sheet1");
                    SpreadsheetWriter.RemoveWorksheet(doc, "Sheet2");
                    SpreadsheetWriter.RemoveWorksheet(doc, "Sheet3");

                    foreach (DataTable table in tables)
                    {
                        WorksheetPart sheet = SpreadsheetWriter.InsertWorksheet(doc, table.TableName);
                        WorksheetWriter writer = new WorksheetWriter(doc, sheet);

SpreadsheetStyle style = SpreadsheetStyle.GetDefault(doc);

                        foreach (DataRow row in table.Rows)
                        {
                            for (int i = 0; i < table.Columns.Count; i++)
                            {
                                string columnName = SpreadsheetReader.GetColumnName("A", i);
                                string location = columnName + (table.Rows.IndexOf(row) + 1);
                                writer.PasteText(location, row[i].ToString(), style);
                            }
                        }

                        writer.Save();
                    }
                    SpreadsheetWriter.StreamToFile(path, stream);//保存到文件中
                }
            }
        }

        private static int CompareColumn(string x, string y)
        {
            int xIndex = Letter_to_num(x);
            int yIndex = Letter_to_num(y);
            return xIndex.CompareTo(yIndex);
        }

        /// <summary>
        /// 數(shù)字26進(jìn)制，轉(zhuǎn)換成字母，用遞歸算法
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        private static string Num_to_letter(int value)
        {
            //此處判斷輸入的是否是正確的數(shù)字，略（正在表達(dá)式判斷）
            int remainder = value % 26;
            //remainder = (remainder == 0) ? 26 : remainder;
            int front = (value - remainder) / 26;
            if (front < 26)
            {
                return Level[front - 1] + Level[remainder];
            }
            else
            {
                return Num_to_letter(front) + Level[remainder];
            }
            //return "";
        }

        /// <summary>
        /// 26進(jìn)制字母轉(zhuǎn)換成數(shù)字
        /// </summary>
        /// <param name="letter"></param>
        /// <returns></returns>
        private static int Letter_to_num(string str)
        {
            //此處判斷是否是由A-Z字母組成的字符串，略（正在表達(dá)式片段）
            char[] letter = str.ToCharArray(); //拆分字符串
            int reNum = 0;
            int power = 1; //用于次方算值
            int times = 1; //最高位需要加1
            int num = letter.Length;//得到字符串個(gè)數(shù)
            //得到最后一個(gè)字母的尾數(shù)值
            reNum += Char_num(letter[num - 1]);
            //得到除最后一個(gè)字母的所以值,多于兩位才執(zhí)行這個(gè)函數(shù)
            if (num >= 2)
            {
                for (int i = num - 1; i > 0; i--)
                {
                    power = 1;//致1，用于下一次循環(huán)使用次方計(jì)算
                    for (int j = 0; j < i; j++)           //冪，j次方，應(yīng)該有函數(shù)
                    {
                        power *= 26;
                    }
                    reNum += (power * (Char_num(letter[num - i - 1]) + times)); //最高位需要加1，中間位數(shù)不需要加一
                    times = 0;
                }
            }
            //Console.WriteLine(letter.Length);
            return reNum;
        }

        /// <summary>
        /// 輸入字符得到相應(yīng)的數(shù)字，這是最笨的方法，還可用ASIICK編碼；
        /// </summary>
        /// <param name="ch"></param>
        /// <returns></returns>
        private static int Char_num(char ch)
        {
            switch (ch)
            {
                case 'A':
                    return 0;
                case 'B':
                    return 1;
                case 'C':
                    return 2;
                case 'D':
                    return 3;
                case 'E':
                    return 4;
                case 'F':
                    return 5;
                case 'G':
                    return 6;
                case 'H':
                    return 7;
                case 'I':
                    return 8;
                case 'J':
                    return 9;
                case 'K':
                    return 10;
                case 'L':
                    return 11;
                case 'M':
                    return 12;
                case 'N':
                    return 13;
                case 'O':
                    return 14;
                case 'P':
                    return 15;
                case 'Q':
                    return 16;
                case 'R':
                    return 17;
                case 'S':
                    return 18;
                case 'T':
                    return 19;
                case 'U':
                    return 20;
                case 'V':
                    return 21;
                case 'W':
                    return 22;
                case 'X':
                    return 23;
                case 'Y':
                    return 24;
                case 'Z':
                    return 25;
            }
            return -1;
        }
    }
}