新版本的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 lsit = new List();
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 Import(string path)
{
List tables = new List();
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())
{
DataTable table = new DataTable(sheet.Name.Value);
WorksheetPart worksheet = (WorksheetPart)doc.WorkbookPart.GetPartById(sheet.Id);
List columnsNames = new List();
foreach (Row row in worksheet.Worksheet.Descendants())
{
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())
{
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; 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;
}
///
/// 输入字符得到相应的数字，这是最笨的方法，还可用ASIICK编码；
///
///
///
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";
}
}