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";
}
}
下一篇： C语言预处理