OpenXml读写Excel实例代码

新版本的xlsx是使用新的存储格式，貌似是处理过的xml。

对于openxml我网上搜了一下，很多人没有介绍。所以我就这里推荐下，相信会成为信息系统开发的必备。

先写出个例子，会发现如此的简介：

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

写出代码

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>
        /// 数字26进制，转换成字母，用递归算法
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        private static string num_to_letter(int value)
        {
            //此处判断输入的是否是正确的数字，略（正在表达式判断）
            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进制字母转换成数字
        /// </summary>
        /// <param name="letter"></param>
        /// <returns></returns>
        private static int letter_to_num(string str)
        {
            //此处判断是否是由a-z字母组成的字符串，略（正在表达式片段）
            char[] letter = str.tochararray(); //拆分字符串
            int renum = 0;
            int power = 1; //用于次方算值
            int times = 1;  //最高位需要加1
            int num = letter.length;//得到字符串个数
            //得到最后一个字母的尾数值
            renum += char_num(letter[num - 1]);
            //得到除最后一个字母的所以值,多于两位才执行这个函数
            if (num >= 2)
            {
                for (int i = num - 1; i > 0; i--)
                {
                    power = 1;//致1，用于下一次循环使用次方计算
                    for (int j = 0; j < i; j++)           //幂，j次方，应该有函数
                    {
                        power *= 26;
                    }
                    renum += (power * (char_num(letter[num - i - 1]) + times));  //最高位需要加1，中间位数不需要加一
                    times = 0;
                }
            }
            //console.writeline(letter.length);
            return renum;
        }

        /// <summary>
        /// 输入字符得到相应的数字，这是最笨的方法，还可用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;
        }
    }
}

using system;
using system.collections.generic;
using system.text;
using system.data;
using system.data.oledb;

namespace xtar_biz_codegen
{
    class excelhelper
    {
        public static string postfix_97 = "xls";

        public static string postfix_03 = "xlsx";
    }
}