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