Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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);
        }
Exemplo n.º 3
0
        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);
            }
        }