本次转换用到了NPOI里面的方法，会经过封装成为dll,在.net平台和unity都测试通过。下面主要讲一下思路：
1.将表格读取为DataTable类型
2.逐行读取DataTable的内容
3.根据读取到内容生成对应的XML表即可。
下面讲述一下具体的实现：
在ReadTable中进行路径设置
public class ReadTable
{
string tablepath = "";
public ReadTable(string path)
{
tablepath = path;
}
public DataTable TableToDataTable()
{
DataTable table = new DataTable();
if (tablepath == ""|| tablepath == string.Empty)
{
return null;
}
else
{
if (File.Exists(tablepath))
{
ExcelAnalysis excel = new ExcelAnalysis();
table= excel.ExcelToDataTable(tablepath, false);
}
else
{
table = null;
}
return table;
}
}
}ExcelAnalysis这个类里面的方法，将表格转换为DataTable
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.IO;
namespace TableToXml
{
class ExcelAnalysis
{
/// <summary>
/// 将excel导入到datatable
/// </summary>
/// <param name="filePath">excel路径</param>
/// <param name="isColumnName">第一行是否是列名</param>
/// <returns>返回datatable</returns>
public DataTable ExcelToDataTable(string filePath, bool isColumnName)
{
DataTable dataTable = null;
FileStream fs = null;
DataColumn column = null;
DataRow dataRow = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
int startRow = 0;
try
{
using (fs = File.OpenRead(filePath))
{
if (filePath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
else if (filePath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
if (workbook != null)
{
if (workbook.NumberOfSheets > 3)
{
sheet = workbook.GetSheetAt(4);//读取第三个sheet，也能循环读取每个sheet
}
else
{
sheet = workbook.GetSheetAt(0);//自己的读取默认的第一个
}
dataTable = new DataTable();
if (sheet != null)
{
int rowCount = sheet.LastRowNum + 1;//+1 包括列名的行，总行数
if (rowCount > 0)
{
IRow firstRow = sheet.GetRow(0);//第一行
int cellCount = firstRow.LastCellNum;//列数
//构建datatable的列
if (isColumnName)
{
startRow = 0;//要读取列名则=0 否则=1
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
cell = firstRow.GetCell(i);
if (cell != null)
{
if (cell.StringCellValue != null)
{
column = new DataColumn(cell.StringCellValue);
dataTable.Columns.Add(column);
}
}
}
}
else
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
column = new DataColumn("column" + (i + 1));
dataTable.Columns.Add(column);
}
}
//填充行
for (int i = startRow; i < rowCount; ++i) //要读取列名则i<rowCount 否则i <= rowCount
{
row = sheet.GetRow(i);
if (row == null) continue;
dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = "";
}
else
{
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
switch (cell.CellType)
{
case CellType.Blank:
dataRow[j] = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
if (format == 14 || format == 31 || format == 57 || format == 58)
dataRow[j] = cell.DateCellValue;
else
dataRow[j] = cell.NumericCellValue;
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
}
}
}
dataTable.Rows.Add(dataRow);
}
}
}
}
else
{
// ("文件打开失败，请关闭文档");
}
}
return dataTable;
}
catch (Exception e)
{
if (fs != null)
{
fs.Close();
}
return null;
}
}
}
}
DataTableToXml 是将读取到的数据源转换为XML，并输出
using System;
using System.Data;
using System.Xml;
namespace TableToXml
{
/// <summary>
/// 将读取到的datatable存储到XML中
/// </summary>
public class DataTableToXml
{
DataTable dataTable;
string xmlPath;
/// <summary>
/// 实例化对象
/// </summary>
/// <param name="dtb">自行加载的table</param>
/// <param name="outPath">Xml的输出路径，不填默认输出在桌面上</param>
public DataTableToXml(DataTable dtb, string outPath="")
{
if (outPath==""|| outPath==null)
{
outPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
}
dataTable = dtb;
xmlPath = outPath;
}
/// <summary>
/// 根据数据源生成Xml
/// </summary>
/// <param name="path">XML文件的输出路径</param>
/// <param name="outname">XML文件的输出文件名</param>
/// <param name="rootname">跟节点的名字</param>
private void CreateXml(string path,string outname="test",string rootname="root")
{
XmlDocument document = new XmlDocument();//创建XmlDocument对象
XmlDeclaration declaration = document.CreateXmlDeclaration("1.0", "UTF-8", "");//xml文档的声明部分
document.AppendChild(declaration);
XmlElement root = document.CreateElement(rootname);//根节点
document.AppendChild(root);
if (dataTable!=null)
{
DataRow[] drs = dataTable.Select();
for (int i = 0; i < drs.Length; i++)
{//行
XmlElement zwerks = document.CreateElement("Rows" + i);
root.AppendChild(zwerks);
for (int j = 0; j < dataTable.Columns.Count; j++)
{//列
XmlElement tab= document.CreateElement("coloumns"+j);
tab.InnerText = drs[i][j].ToString();
zwerks.AppendChild(tab);
}
}
}
document.Save(path+ outname + ".xml");//将生成好的xml保存到.xml文件中
}
/// <summary>
/// 将datatable生成Xml，默认存放在桌面上
/// </summary>
public void DtoXml(string path)
{
CreateXml(path);
}
}
}
里面的所有变量都需要在实例化的时候进行赋值
在.net中这样调用即可
在Unity中可将编译后的dll放置在unity 的Plugins文件夹下使用，同样能达到效果，具体如下：
最后我已经将所有需要的dll打包，放置在CSDN了，5个币就可下载（真不贵，我前前后后两天呢），今天六一，能不能赚个棒棒糖呢？如果你没有币可以私信我哦，给你发！！！
6215
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
