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); }