private void WriteWorksheetData(XmlTextWriter writer, DataTable data, IDictionary <string, int> lookupTable, bool isFirstRowColumnNames)
{
var rowsCount = data.Rows.Count;
var columnsCount = data.Columns.Count;
string relPos;
int oldPercent = 0;
int newPercent = 0;
for (int row = 0; row < rowsCount; row++)
{
writer.WriteStartElement("row");
relPos = RowIndexToName(row);
writer.WriteAttributeString("r", relPos);
writer.WriteAttributeString("spans", "1:" + columnsCount.ToString(CultureInfo.InvariantCulture));
for (int column = 0; column < columnsCount; column++)
{
object value = data.Rows[row][column];
if (isFirstRowColumnNames && row == 0)
{
value = data.Columns[column].ColumnName;
}
writer.WriteStartElement("c");
relPos = RowColumnToPosition(row, column);
writer.WriteAttributeString("r", relPos);
var str = value as string;
if (str != null)
{
writer.WriteAttributeString("t", "s");
value = lookupTable[str];
}
writer.WriteElementString("v", value.ToString());
writer.WriteEndElement();
}
writer.WriteEndElement();
//发起进度变化事件
newPercent = XlsxFileHelper.ProgressPercent(row + 1, 0, rowsCount - 1);
if (newPercent > oldPercent)
{
oldPercent = newPercent;
if (this.ProgressPercentChanged != null)
{
this.ProgressPercentChanged(oldPercent);
}
}
}
}
/// <summary>
/// 读取工作表单文件
/// </summary>
/// <param name="filename"></param>
/// <param name="stringTable"></param>
/// <param name="isFirstRowColumnNames"></param>
/// <returns></returns>
private DataTable ReadWorksheetFile(string filename, IList <string> stringTable, bool isFirstRowColumnNames)
{
FileInfo fi = new FileInfo(filename);
if (fi.Extension.ToLower() != ".xml")
{
throw new Exception("文件扩展名只能为.xml！");
}
string sheetName = fi.Name.Substring(0, fi.Name.Length - 4);
DataTable dt = new DataTable(sheetName);
// Open XML file with worksheet data..
using (var stream = new FileStream(filename, FileMode.Open, FileAccess.Read))
// ..and call helper method that parses that XML and fills DataTable with values.
{
using (var reader = XmlReader.Create(stream))
{
DataRow row = null;
int columnStartSN = 0;
int columnFinalSN = 0;
int rowStartSN = 0;
int rowFinalSN = 0;
int rowSN = 0;
int columnIndex = 0;
string columnRow;
string columnCode;
string type = "";
int intValue;
double doubleValue;
string stringValue;
int oldPercent = 0;
int newPercent = 0;
for (reader.MoveToContent(); reader.Read();)
{
if (reader.NodeType == XmlNodeType.Element)
{
switch (reader.Name)
{
case "dimension":
//读取表的维度，用于构建表结构
string r = reader.GetAttribute("ref");
string[] fromTo = r.Split(':');
if (fromTo.Length == 0)
{
throw new Exception("文件已被损坏！");
}
if (fromTo.Length > 0)
{
string columnFrom = Regex.Match(fromTo[0], "[a-zA-Z]+").Value;
columnStartSN = Leter2Num(columnFrom);
string rowFrom = Regex.Match(fromTo[0], @"\d+").Value;
rowStartSN = int.Parse(rowFrom);
rowSN = rowStartSN;
}
if (fromTo.Length > 1)
{
string columnTo = Regex.Match(fromTo[1], "[a-zA-Z]+").Value;
columnFinalSN = Leter2Num(columnTo);
string rowTo = Regex.Match(fromTo[1], @"\d+").Value;
rowFinalSN = int.Parse(rowTo);
}
else
{
columnFinalSN = columnStartSN;
rowFinalSN = rowStartSN;
}
for (int i = columnStartSN; i <= columnFinalSN; i++)
{
dt.Columns.Add(Num2Letter(i));
}
break;
case "row":
row = dt.NewRow();
break;
case "c":
columnRow = reader.GetAttribute("r");
columnCode = Regex.Match(columnRow, "[a-zA-Z]+").Value;
columnIndex = Leter2Num(columnCode) - columnStartSN;
type = reader.GetAttribute("t");
break;
case "v":
//解析
stringValue = reader.ReadElementString();
if (type == "s")
{
if (int.TryParse(stringValue, out intValue))
{
stringValue = stringTable[intValue];
}
}
else if (this.DateTimeDataColumnName != null && this.DateTimeDataColumnName.Contains(dt.Columns[columnIndex].ColumnName))
{
if (double.TryParse(stringValue, out doubleValue))
{
stringValue = DateTime.FromOADate(doubleValue).ToString();
}
}
//赋值
if (rowSN >= (isFirstRowColumnNames ? rowStartSN + 1 : rowStartSN))
{
row[columnIndex] = stringValue;
}
else
{
dt.Columns[columnIndex].ColumnName = stringValue;
}
break;
}
}
else if (reader.NodeType == XmlNodeType.EndElement)
{
if (reader.Name == "row")
{
if (rowSN >= (isFirstRowColumnNames ? rowStartSN + 1 : rowStartSN))
{
dt.Rows.Add(row);
//发起进度变化事件
newPercent = XlsxFileHelper.ProgressPercent(rowSN, rowStartSN, rowFinalSN);
if (newPercent > oldPercent)
{
oldPercent = newPercent;
if (this.ProgressPercentChanged != null)
{
this.ProgressPercentChanged(oldPercent);
}
}
}
rowSN++;
}
}
}
}
}
return(dt);
}
