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