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