public DataTable XmlReadDataTable()
{
current = current ?? configs.FirstOrDefault();
var sheet = $"sheet{current.SheetIndex}";
var entry = zip.GetEntry($"xl/worksheets/{sheet}.xml");
DataTable dt = new DataTable();
DataRow ndr = default;
IDictionary <int, string> Columns = new Dictionary <int, string>();
var changing = new List <Action <DataRowChangeEventArgs> >();
if (current.ColumnSetting != null)
{
foreach (var item in current.ColumnSetting)
{
var dc = dt.Columns.Add(item.ColumnName, item.ColumnType);
if (item.Column != null)
{
Columns.Add(item.ColumnIndex.Value, item.ColumnName);
}
dc.AllowDBNull = item.IsAllowNull;
switch (item.NormalType)
{
case ColumnNormal.Guid:
dc.DefaultValue = Guid.NewGuid();
changing.Add(e => { dc.DefaultValue = Guid.NewGuid(); });
break;
case ColumnNormal.NowDate:
dc.DefaultValue = DateTime.Now;
break;
case ColumnNormal.Increment:
int increment = item.DefaultValue == null ? 0 : Convert.ToInt32(item.DefaultValue);
dc.DefaultValue = increment;
changing.Add(e => { dc.DefaultValue = ++increment; });
break;
case ColumnNormal.Default:
if (item.DefaultValue != null)
{
dc.DefaultValue = item.DefaultValue;
}
break;
}
}
dt.RowChanging += RowChanging;
}
void RowChanging(object sender, DataRowChangeEventArgs e)
{
if (e.Action == DataRowAction.Add)
{
foreach (var item in changing)
{
item(e);
}
}
}
XmlReadKit xmlReadKit = new XmlReadKit(entry.Open());
bool readColumns = false;
bool readData = false;
bool isValue = false;
int rowIndex = default;
int colIndex = default;
string dataType = default;
string xfs = default;
xmlReadKit.XmlReadXlsx("sheetData", (node, attrs, content) =>
{
switch (node)
{
case "end":
if (ndr != null)
{
dt.Rows.Add(ndr);
}
break;
case "row":
rowIndex = int.Parse(attrs.SingleOrDefault(x => x.Name.Equals("r", StringComparison.OrdinalIgnoreCase)).Text);
if (current.StartRow <= current.ColumnNameRow)
{
throw new XlsxRowConfigException();
}
if (current.ColumnSetting == null &&
rowIndex == current.ColumnNameRow)
{
readColumns = true;
}
if (rowIndex > current.EndRow)
{
return(false);
}
if (rowIndex >= current.StartRow)
{
readColumns = false;
readData = true;
if (ndr != null)
{
dt.Rows.Add(ndr);
}
ndr = dt.NewRow();
}
break;
case "c":
if (readColumns || readData)
{
string colEn = new string(CellPosition(attrs.SingleOrDefault(x => x.Name.Equals("r", StringComparison.OrdinalIgnoreCase)).Text).ToArray());
colIndex = ExcelHelper.ColumnToIndex(colEn).Value;
dataType = attrs.FirstOrDefault(x => x.Name.Equals("t", StringComparison.OrdinalIgnoreCase)).Text;
xfs = attrs.FirstOrDefault(x => x.Name.Equals("s", StringComparison.OrdinalIgnoreCase)).Text;
}
break;
case "v":
if (colIndex >= current.StartColumnIndex &&
(current.EndColumnIndex != null ? colIndex <= current.EndColumnIndex : true))
{
isValue = true;
}
break;
case "f":
break;
case "text":
if (isValue)
{
if (readColumns)
{
if (dataType == "s")
{
dt.Columns.Add(sharedStrings[int.Parse(content)], typeof(string));
Columns.Add(colIndex, sharedStrings[int.Parse(content)]);
}
else
{
dt.Columns.Add(content, typeof(string));
Columns.Add(colIndex, content);
}
}
if (readData)
{
if (Columns.ContainsKey(colIndex))
{
if (dataType == "s")
{
ndr[Columns[colIndex]] = sharedStrings[int.Parse(content)];
}
else
{
ndr[Columns[colIndex]] = content;
}
}
}
isValue = false;
}
break;
default:
isValue = false;
break;
}
return(true);
});
dt.RowChanging -= RowChanging;
return(dt);
}
public IEnumerable <IDictionary <string, object> > XmlReaderDictionary()
{
current = current ?? configs.FirstOrDefault();
var sheet = $"sheet{current.SheetIndex}";
var entry = zip.GetEntry($"xl/worksheets/{sheet}.xml");
List <IDictionary <string, object> > dicList = new List <IDictionary <string, object> >();
IDictionary <int, string> Columns = new Dictionary <int, string>();
IDictionary <string, object> temp = null;
XmlReadKit xmlReadKit = new XmlReadKit(entry.Open());
bool readColumns = false;
bool readData = false;
bool isValue = false;
int rowIndex = default;
int colIndex = default;
string dataType = default;
string xfs = default;
xmlReadKit.XmlReadXlsx("sheetData", (node, attrs, content) =>
{
switch (node)
{
case "end":
if (temp != null)
{
dicList.Add(temp);
}
break;
case "row":
rowIndex = int.Parse(attrs.SingleOrDefault(x => x.Name.Equals("r", StringComparison.OrdinalIgnoreCase)).Text);
if (current.StartRow <= current.ColumnNameRow)
{
throw new XlsxRowConfigException();
}
if (rowIndex == current.ColumnNameRow)
{
readColumns = true;
}
if (rowIndex > current.EndRow)
{
return(false);
}
if (rowIndex >= current.StartRow)
{
readColumns = false;
readData = true;
if (temp != null)
{
dicList.Add(temp);
}
temp = new Dictionary <string, object>();
}
break;
case "c":
if (readColumns || readData)
{
string colEn = new string(CellPosition(attrs.SingleOrDefault(x => x.Name.Equals("r", StringComparison.OrdinalIgnoreCase)).Text).ToArray());
colIndex = ExcelHelper.ColumnToIndex(colEn).Value;
dataType = attrs.FirstOrDefault(x => x.Name.Equals("t", StringComparison.OrdinalIgnoreCase)).Text;
xfs = attrs.FirstOrDefault(x => x.Name.Equals("s", StringComparison.OrdinalIgnoreCase)).Text;
}
break;
case "v":
if (colIndex >= current.StartColumnIndex &&
(current.EndColumnIndex != null ? colIndex <= current.EndColumnIndex : true))
{
isValue = true;
}
break;
case "f":
break;
case "text":
if (isValue)
{
if (readColumns)
{
if (dataType == "s")
{
Columns.Add(colIndex, sharedStrings[int.Parse(content)]);
}
else
{
Columns.Add(colIndex, content);
}
}
if (readData)
{
if (dataType == "s")
{
temp.Add(Columns[colIndex], sharedStrings[int.Parse(content)]);
}
else
{
temp.Add(Columns[colIndex], content);
}
}
isValue = false;
}
break;
default:
isValue = false;
break;
}
return(true);
});
return(dicList);
}
public void Save(DataSet ds)
{
if (ds.IsNull())
{
throw new ArgumentNullException();
}
var template = new Dictionary <string, string>(fixedTemplate);
Dictionary <string, string> sheets = new Dictionary <string, string>();
int index = 1;
foreach (DataTable dt in ds.Tables)
{
StringBuilder builder = new StringBuilder();
builder.Append($@"<?xml version=""1.0"" encoding=""utf-8""?>");
builder.Append($@"<x:worksheet xmlns:x=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"">");
builder.Append($@"<x:dimension ref=""A1:{ExcelHelper.IndexToColumn(dt.Columns.Count - 1)}{dt.Rows.Count}""/><x:sheetData>");
sheets.Add("sheet" + index, dt.TableName);
for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
{
DataRow dr = dt.Rows[rowIndex];
builder.Append($"<x:row r=\"{(rowIndex + 1)}\">");
for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
DataColumn dc = dt.Columns[colIndex];
builder.Append($"<x:c r=\"{ExcelHelper.IndexToColumn(colIndex)}{(rowIndex + 1)}\" t=\"str\">");
builder.Append($"<x:v>{dr[dc.ColumnName]?.ToString()}");
builder.Append($"</x:v>");
builder.Append($"</x:c>");
}
builder.Append($"</x:row>");
}
builder.Append("</x:sheetData></x:worksheet>");
template.Add($"xl/worksheets/sheet{index}.xml", builder.ToString());
index++;
}
var template1 = StreamToString(Common.GetXlsxResource("[Content_Types].xml"));
string dynamicTemplate1 = "";
foreach (var item in sheets)
{
string contenttypes = $"<Override ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml\" PartName=\"/xl/worksheets/{item.Key}.xml\" />";
dynamicTemplate1 += contenttypes;
}
template.Add("[Content_Types].xml", template1.RulesFormat(dynamicTemplate1));
int rId = 1;
var template2 = StreamToString(Common.GetXlsxResource("workbook.xml.rels"));
var template3 = StreamToString(Common.GetXlsxResource("workbook.xml"));
string dynamicTemplate2 = "";
string dynamicTemplate3 = "";
foreach (var item in sheets)
{
string workbook = $"<x:sheet xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" name=\"{item.Value}\" sheetId=\"{rId}\" r:id=\"rId{rId}\"/>";
string workbookrels = $"<Relationship Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet\" Target=\"/xl/worksheets/{item.Key}.xml\" Id=\"rId{rId}\" />";
dynamicTemplate2 += workbookrels;
dynamicTemplate3 += workbook;
rId++;
}
dynamicTemplate2 += $"<Relationship Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles\" Target=\"/xl/styles.xml\" Id=\"rId{rId}\" />";
template.Add("xl/_rels/workbook.xml.rels", template2.RulesFormat(dynamicTemplate2));
template.Add("xl/workbook.xml", template3.RulesFormat(dynamicTemplate3));
foreach (var item in template)
{
var entry = zipArchive.CreateEntry(item.Key);
using var stream = entry.Open();
using StreamWriter writer = new StreamWriter(stream, _utf8WithBom);
writer.Write(item.Value);
}
}
