一个用微软官方的OpenXml读写Excel 目前网上不太普及的方法。

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

传统的excel处理方法，我真的感觉像屎。用Oldeb不方便，用com组件要实际调用excel打开关闭，很容易出现死。

对于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
{//http://simpleooxml.codeplex.com///http://www.pin5i.com/showtopic-21817.html//http://blog.csdn.net/lbj147123/article/details/6603942class 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 tableif ((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;  //最高位需要加1int 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";}
}

最后附送一个项目文件，可以自己测试：

http://pan.baidu.com/s/1msFuY