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); } } } }
public void Write(string fileName, DataSet dataSet, bool isFirstRowColumnNames) { if (Path.GetExtension(fileName).ToLower() != ".xlsx") { throw new ArgumentException("不能写入扩展名非.xlsx的文件!"); } string rootDir = Path.GetDirectoryName(fileName); string tempDir = rootDir + "/" + Path.GetFileNameWithoutExtension(fileName); if (!Directory.Exists(tempDir)) { Directory.CreateDirectory(tempDir); } // Delete contents of the temporary directory. XlsxFileHelper.DeleteDirectoryContents(tempDir); // Create template XLSX file from resource string dllpath = Assembly.GetExecutingAssembly().CodeBase; dllpath = dllpath.Substring(8); string templateFile = Path.GetDirectoryName(dllpath) + "/template.xlsx"; if (!Directory.Exists(Path.GetDirectoryName(templateFile))) { Directory.CreateDirectory(Path.GetDirectoryName(templateFile)); } if (!File.Exists(templateFile)) { File.WriteAllBytes(templateFile, Resource.template); } // Unzip template XLSX file to the temporary directory. XlsxFileHelper.UnzipFile(templateFile, tempDir); //删除模板文件 if (File.Exists(templateFile)) { File.Delete(templateFile); } //将表名写入workbook.xml文件 IList <string> tableNames = new List <string>(); foreach (DataTable dt in dataSet.Tables) { tableNames.Add(dt.TableName); } WriteSheetNamesToWorkbook(Path.Combine(tempDir, @"xl\workbook.xml"), tableNames); // We will need two string tables; a lookup IDictionary<string, int> for fast searching // an ordinary IList<string> where items are sorted by their index. IDictionary <string, int> lookupTable; // Call helper methods which creates both tables from input data. var stringTable = CreateStringTable(dataSet, out lookupTable); // Create XML file.. using (var stream = new FileStream(Path.Combine(tempDir, @"xl\sharedStrings.xml"), FileMode.Create)) // ..and fill it with unique strings used in the workbook WriteStringTable(stream, stringTable); for (int i = 0; i != dataSet.Tables.Count; i++) { string sheetFileName = @"xl\worksheets\sheet" + (i + 1).ToString() + ".xml"; // Create XML file.. using (var stream = new FileStream(Path.Combine(tempDir, sheetFileName), FileMode.Create)) // ..and fill it with rows and columns of the DataTable. WriteWorksheet(stream, dataSet.Tables[i], lookupTable, isFirstRowColumnNames); } // ZIP temporary directory to the XLSX file. XlsxFileHelper.ZipDirectory(tempDir, fileName); if (Directory.Exists(tempDir)) { Directory.Delete(tempDir, true); } }
/// <summary> /// 由Xlsx文件读取数据集 /// </summary> /// <param name="data"></param> /// <param name="fileName"></param> public DataSet Read(string fileName, bool isFirstRowColumnNames) { DataSet dataSet = null; if (string.IsNullOrEmpty(fileName)) { throw new Exception("路径为空!"); } if (!File.Exists(fileName)) { throw new Exception("不存在文件" + fileName); } if (fileName.Length < 5) { throw new Exception("无法读取文件" + fileName); } if (fileName.Substring(fileName.Length - 5, 5).ToLower() != ".xlsx") { throw new Exception("无法读取文件" + fileName); } FileInfo fi = new FileInfo(fileName); string fullName = fi.FullName; string tempDir = fullName.Substring(0, fullName.Length - 5) + "/"; // Delete contents of the temporary directory. XlsxFileHelper.DeleteDirectoryContents(tempDir); // Unzip input XLSX file to the temporary directory. XlsxFileHelper.UnzipFile(fileName, tempDir); IDictionary <int, string> sheetNames; using (var stream = new FileStream(Path.Combine(tempDir, @"xl\workbook.xml"), FileMode.Open, FileAccess.Read)) sheetNames = ReadSheetNamesFromWorkbookFile(stream); IList <string> stringTable; // Open XML file with table of all unique strings used in the workbook.. using (var stream = new FileStream(Path.Combine(tempDir, @"xl\sharedStrings.xml"), FileMode.Open, FileAccess.Read)) // ..and call helper method that parses that XML and returns an array of strings. stringTable = ReadStringTable(stream); DirectoryInfo worksheetsDirInfo = new DirectoryInfo(Path.Combine(tempDir, @"xl\worksheets")); FileInfo[] sheetsFileInfoArr = worksheetsDirInfo.GetFiles(); if (sheetsFileInfoArr.Length != 0) { dataSet = new DataSet(fi.Name.Substring(0, fi.Name.Length - 5)); } if (sheetNames.Count <= sheetsFileInfoArr.Count <FileInfo>()) { for (int i = 1; i <= sheetNames.Count; i++) { string sheetFileName = "sheet" + i + ".xml"; DataTable dt = ReadWorksheetFile(Path.Combine(worksheetsDirInfo.FullName, sheetFileName), stringTable, isFirstRowColumnNames); dt.TableName = sheetNames[i]; dataSet.Tables.Add(dt); } } else { throw new Exception(string.Format("文件异常,无法读取{0}!", fileName)); } // 删除临时目录 Directory.Delete(tempDir, true); return(dataSet); }
/// <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); }