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