Example #1
0
        /// <summary>
        /// 通过模板导出数据
        /// </summary>
        /// <typeparam name="T">数据源类型</typeparam>
        /// <param name="path">需要绝对路径</param>
        /// <param name="dataTitleRowIndex">模板数据标题列所在行()</param>
        /// <param name="replaceDic">替换模板的标示符</param>
        /// <param name="datas">数据源</param>
        /// <param name="count">数据源总条数</param>
        /// <param name="filterData">过滤数据</param>
        /// <param name="sheetName">工作簿名字</param>
        /// <returns></returns>
        public static byte[] Export <T>(string path, int dataTitleRowIndex, Dictionary <string, string> replaceDic, IEnumerable <T> datas, int count, Func <T, object> filterData,
                                        string sheetName = "sheet1")
        {
            string extension = Path.GetExtension(path);

            if (!extension.Equals(".xls") && !extension.Equals(".xlsx"))
            {
                throw new FileLoadException("文件格式错误");
            }
            if (!File.Exists(path))
            {
                throw new FileLoadException("文件不存在");
            }
            using (FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                HSSFWorkbook book  = new HSSFWorkbook(stream);
                ISheet       sheet = book.GetSheetAt(0);
                //先执行替换操作
                var rows = sheet.GetEnumerator();
                while (rows.MoveNext())
                {
                    var cuurentRow = (HSSFRow)rows.Current;
                    ReplaceCell(cuurentRow, replaceDic);
                }
                //如果底部有替换行 将底部的行 移动到插入数据之后
                var rowCount = sheet.LastRowNum;
                if (sheet.LastRowNum > dataTitleRowIndex)
                {
                    for (int i = dataTitleRowIndex + 1; i <= rowCount; i++)
                    {
                        sheet.MoveCell(i, i + count);
                    }
                }
                int    j          = dataTitleRowIndex + 1;
                object exportData = null;
                foreach (var data in datas)
                {
                    var row = sheet.GetRow(j);
                    if (row == null)
                    {
                        row = sheet.CreateRow(j);
                    }
                    j++;
                    exportData = data;
                    if (filterData != null)
                    {
                        exportData = filterData(data);
                    }
                    row.CreateCells(exportData);
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    book.Write(ms);
                    ms.Seek(0, SeekOrigin.Begin);
                    byte[] bytedatas = new byte[ms.Length];
                    ms.Read(bytedatas, 0, bytedatas.Length);
                    return(bytedatas);
                }
            }
        }
Example #2
0
        /// <summary>
        /// 获取<see cref="ISheet"/>中的行
        /// </summary>
        /// <param name="sheet">指定获取的<see cref="ISheet"/>实例</param>
        /// <returns><see cref="ISheet"/>中的行</returns>
        private IEnumerable <IRow> GetRows(ISheet sheet)
        {
            var enumerator = sheet.GetEnumerator();

            while (enumerator.MoveNext())
            {
                yield return(enumerator.Current as IRow);
            }
        }
Example #3
0
        private void ChectSheet(ISheet xsheet)
        {
            var datalist = xsheet.GetEnumerator();

            while (datalist.MoveNext())
            {
                IRow xrow = datalist.Current as IRow;
                if (xrow != null)
                {
                    foreach (ICell cell in xrow)
                    {
                        Dictionary <short, List <string> > _cellList;

                        if (FileExt == "xlsx")
                        {
                            _cellList = AnalysisCellString(cell as XSSFCell);
                        }
                        else
                        {
                            _cellList = AnalysisCellString(cell as HSSFCell);
                        }
                        foreach (short s in _cellList.Keys)
                        {
                            string st = "OutSheetColoris" + s.ToString();
                            ISheet sheet2;
                            if (_workBook.GetSheetIndex(st) < 0)
                            {
                                sheet2 = _workBook.CreateSheet(st);
                            }
                            else
                            {
                                sheet2 = _workBook.GetSheetAt(_workBook.GetSheetIndex(st));
                            }
                            List <string> vs  = _cellList[s];
                            string        st2 = string.Empty;
                            foreach (string t in vs)
                            {
                                st2 += t;
                            }
                            IRow r1;
                            if (sheet2.GetRow(cell.RowIndex) == null)
                            {
                                r1 = sheet2.CreateRow(cell.RowIndex);
                            }
                            else
                            {
                                r1 = sheet2.GetRow(cell.RowIndex);
                            }
                            ICell cell1 = r1.CreateCell(cell.ColumnIndex);
                            cell1.SetCellValue(st2);
                        }
                    }
                }
            }
        }
Example #4
0
        private void ChectSheet(ISheet xsheet)
        {
            var datalist = xsheet.GetEnumerator();

            while (datalist.MoveNext())
            {
                IRow xrow = datalist.Current as IRow;
                if (xrow != null)
                {
                    ICell cell1 = xrow.GetCell(0);
                    IFont font  = cell1.CellStyle.GetFont(_workBook);
                    foreach (ICell cell in xrow)
                    {
                        CheckCell(cell);
                    }
                }
            }
        }
Example #5
0
        protected void Button1_Click(object sender, EventArgs e) //读取2003或2007 Excel
        {
            IWorkbook wb = WorkbookFactory.Create(FileUpload1.PostedFile.InputStream);

            ISheet sheet = wb.GetSheetAt(0);

            var rows = sheet.GetEnumerator();

            while (rows.MoveNext())
            {
                IRow         row   = rows.Current as IRow;
                List <ICell> cells = row.Cells;
                if (row != null && cells != null)
                {
                    Response.Write(row.RowNum);
                    foreach (var cell in cells)
                    {
                        Response.Write(cell.ToString());
                        Response.Write("<br/>");
                    }
                }
            }
        }
Example #6
0
        /// <summary>
        /// 读取Sheet中的数据到DataTable
        /// </summary>
        /// <param name="sheet">当前数据表</param>
        /// <returns>DataTable</returns>
        private DataTable GetSheetData(ISheet sheet)
        {
            var table = InitTable(sheet);

            if (table == null)
            {
                return(null);
            }

            var rows = sheet.GetEnumerator();

            while (rows.MoveNext())
            {
                var row = (IRow)rows.Current;
                if (row.RowNum == 0)
                {
                    continue;
                }

                var dr = table.NewRow();
                for (var i = 0; i < table.Columns.Count; i++)
                {
                    try
                    {
                        var type = table.Columns[i].DataType;
                        dr[i] = GetCellData(row.GetCell(i), type);
                    }
                    catch (Exception)
                    {
                        dr[i] = DBNull.Value;
                    }
                }
                table.Rows.Add(dr);
            }

            return(table);
        }
Example #7
0
        public System.Data.DataTable Import(string filepath)
        {
            IWorkbook         workBook  = this.InitializeWorkbook(filepath);
            IFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workBook);
            ISheet            sheet     = workBook.GetSheetAt(0);
            IEnumerator       rows      = sheet.GetEnumerator();
            DataTable         dt        = new DataTable();

            string[] headerNames = new string[sheet.GetRow(0).PhysicalNumberOfCells];
            for (int j = 0; j < headerNames.Length; j++)
            {
                headerNames[j] = Convert.ToChar(((int)'A') + j % 26).ToString() + ((j / 26) > 0 ? (j / 26).ToString() : string.Empty); // A-Z A1-Z1 An-Zn
            }

            this.AddColumn(dt, headerNames);

            while (rows.MoveNext())
            {
                IRow row = rows.Current as HSSFRow;
                this.AddRow(dt, row, headerNames, evaluator);
            }

            return(dt);
        }
Example #8
0
        /// <summary>
        /// 读取Sheet中的数据到DataTable
        /// </summary>
        /// <param name="sheet">当前数据表</param>
        /// <returns>DataTable</returns>
        private DataTable GetSheetData(ISheet sheet)
        {
            var table = InitTable(sheet);

            if (table == null)
            {
                return(null);
            }

            var rows = sheet.GetEnumerator();

            while (rows.MoveNext())
            {
                var row = (IRow)rows.Current;
                if (row.RowNum == 0)
                {
                    continue;
                }

                var dr = table.NewRow();
                for (var i = 0; i < table.Columns.Count; i++)
                {
                    try
                    {
                        var cell = row.GetCell(i);
                        switch (cell.CellType)
                        {
                        case CellType.Numeric:
                            // 如单元格格式为数字,判断列数据类型是否为DateTime
                            var type = table.Columns[i].DataType.Name;
                            if (type == "DateTime")
                            {
                                dr[i] = cell.DateCellValue;
                            }
                            else
                            {
                                dr[i] = cell.NumericCellValue;
                            }

                            break;

                        case CellType.Boolean:
                            dr[i] = cell.BooleanCellValue;
                            break;

                        case CellType.String:
                            dr[i] = cell.StringCellValue;
                            break;

                        default:
                            dr[i] = DBNull.Value;
                            break;
                        }
                    }
                    catch (Exception)
                    {
                        dr[i] = DBNull.Value;
                    }
                }
                table.Rows.Add(dr);
            }

            return(table);
        }
Example #9
0
        protected void Button1_Click(object sender, EventArgs e) //导入专业
        {
            IWorkbook wb = WorkbookFactory.Create(FileUpload1.PostedFile.InputStream);

            ISheet sheet = wb.GetSheetAt(0);

            var rows = sheet.GetEnumerator();

            Dictionary <string, int> dict      = new Dictionary <string, int>();
            List <Major>             majorList = new List <Major>();

            while (rows.MoveNext())
            {
                IRow         row   = rows.Current as IRow;
                List <ICell> cells = row.Cells;
                if (row != null && cells != null)
                {
                    if (row.RowNum == 0)
                    {
                        for (int i = 0; i < cells.Count; i++)
                        {
                            var txt = cells[i].ToString().Trim();
                            switch (txt)
                            {
                            case "名称": dict.Add("名称", i); break;

                            case "专业": dict.Add("专业", i); break;

                            case "所属学科": dict.Add("所属学科", i); break;

                            case "所属门类": dict.Add("所属门类", i); break;

                            case "专业代码": dict.Add("专业代码", i); break;

                            case "专业介绍": dict.Add("专业介绍", i); break;
                            }
                        }
                    }    //第一行

                    else //数据行
                    {
                        ICell cellName    = row.GetCell(dict["名称"]);
                        ICell cellZhuanYe = row.GetCell(dict["专业"]);
                        ICell cellXueKe   = row.GetCell(dict["所属学科"]);
                        ICell cellMenLei  = row.GetCell(dict["所属门类"]);
                        ICell cellDaiMa   = row.GetCell(dict["专业代码"]);
                        ICell cellJieShao = row.GetCell(dict["专业介绍"]);

                        if (cellName != null)
                        {
                            string name = cellName.ToString().Trim();
                            if (name != "")
                            {
                                Major major = new Major();
                                major.Name = name;
                                if (cellZhuanYe != null)
                                {
                                    major.MajorType = (cellZhuanYe.ToString().Trim() == "本科" ? 1 : 2);
                                }

                                if (cellXueKe != null)
                                {
                                    major.SubjectName = cellXueKe.ToString().Trim();
                                }

                                if (cellMenLei != null)
                                {
                                    major.CategoryName = cellMenLei.ToString().Trim();
                                }

                                if (cellDaiMa != null)
                                {
                                    major.MajorCode = cellDaiMa.ToString().Trim();
                                }

                                if (cellJieShao != null)
                                {
                                    major.Introduction = cellJieShao.ToString().Trim();
                                }
                                majorList.Add(major);
                            }
                        }
                    }
                }
            }


            foreach (var item in majorList)
            {
                AddMajor(item);
            }
        }
Example #10
0
        protected void Button2_Click(object sender, EventArgs e) //导入职业
        {
            IWorkbook wb = WorkbookFactory.Create(FileUpload1.PostedFile.InputStream);

            ISheet sheet = wb.GetSheetAt(0);

            var rows = sheet.GetEnumerator();

            Dictionary <string, int> dict = new Dictionary <string, int>();
            List <Job> jobList            = new List <Job>();

            while (rows.MoveNext())
            {
                IRow         row   = rows.Current as IRow;
                List <ICell> cells = row.Cells;
                if (row != null && cells != null)
                {
                    if (row.RowNum == 0)
                    {
                        for (int i = 0; i < cells.Count; i++)
                        {
                            var txt = cells[i].ToString().Trim();
                            switch (txt)
                            {
                            case "职业": dict.Add("职业", i); break;

                            case "所属行业一级分类": dict.Add("所属行业一级分类", i); break;

                            case "所属行业二级分类": dict.Add("所属行业二级分类", i); break;

                            case "介绍": dict.Add("介绍", i); break;
                            }
                        }
                    }    //第一行

                    else //数据行
                    {
                        ICell cellName    = row.GetCell(dict["职业"]);
                        ICell cellPname   = row.GetCell(dict["所属行业一级分类"]);
                        ICell cellCname   = row.GetCell(dict["所属行业二级分类"]);
                        ICell cellJieShao = row.GetCell(dict["介绍"]);

                        if (cellName != null)
                        {
                            string name = cellName.ToString().Trim();
                            if (name != "")
                            {
                                Job job = new Job();
                                job.Name = name;

                                if (cellPname != null)
                                {
                                    job.SubjectName = cellPname.ToString().Trim();
                                }

                                if (cellCname != null)
                                {
                                    job.CategoryName = cellCname.ToString().Trim();
                                }

                                if (cellJieShao != null)
                                {
                                    job.Introduction = cellJieShao.ToString().Trim();
                                }
                                jobList.Add(job);
                            }
                        }
                    }
                }
            }


            foreach (var item in jobList)
            {
                AddJob(item);
            }
        }
Example #11
0
        /// <summary>
        /// Формирование отчета
        /// </summary>
        /// <param name="name">Имя соединения</param>
        /// <param name="drv">Драйвер соединения</param>
        /// <param name="qname">Имя запроса</param>
        /// <param name="group">Группа запроса</param>
        /// <param name="subgroup">Подгруппа запроса</param>
        /// <param name="sql">SQL запроса</param>
        /// <param name="repId">ID отчета</param>
        /// <param name="person">Текущий пользователь</param>
        /// <returns>Путь формированного файла-отчета</returns>
        public string GenerateReport(string name, asv.Models.eDriverType drv, string sql, object [] args, string qname, string group, string subgroup, int repId, MemberPrincipal person)
        {
            this.person = person;
            GenerateParams(qname, group, subgroup);

            string cv      = null,
                   tplPath = GetTemplate(repId),
                   ext     = Path.GetExtension(tplPath),
                   path    = Path.GetFileNameWithoutExtension(tplPath) + "_" + person.Id + ext,
                   newPath = repPath + repId + @"\" + path;

            Type t = typeof(HSSFWorkbook);

            if (ext == ".xlsx")
            {
                repFormat = eOfficeFormat.OfficeXml;
                t         = typeof(XSSFWorkbook);
            }

            FileStream fs = new FileStream(repPath + repId + @"\" + tplPath, FileMode.Open, FileAccess.Read);
            IWorkbook  wb = (IWorkbook)Activator.CreateInstance(t, new object[] { fs });

            fs.Close();

            bool isComplex = false;

            ICell  cell  = null;
            IRow   row   = null;
            ISheet sheet = null;

            ComplexRows         compl     = null;
            IList <ComplexRows> complrows = new List <ComplexRows>();

            Regex rx = new Regex(@"#Запрос,?\s?(\d+)*-?\s?(\d+)*", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            Match m;

            IEnumerator cells, rows, sheets = wb.GetEnumerator();

            while (sheets.MoveNext())
            {
                sheet = (ISheet)sheets.Current;

                rows = sheet.GetEnumerator();
                while (rows.MoveNext())
                {
                    row = (IRow)rows.Current;

                    cells = row.GetEnumerator();
                    while (cells.MoveNext())
                    {
                        cell = (ICell)cells.Current;
                        try
                        {
                            cv = cell.StringCellValue;
                            m  = rx.Match(cv);
                            if (m.Success)
                            {
                                isComplex = true;
                                compl     = new ComplexRows(sheet.SheetName);
                                compl.R1  = row.RowNum;

                                compl.P1 = Misc.GetConfigValue(m.Groups[1].Value, -1);
                                compl.P2 = Misc.GetConfigValue(m.Groups[2].Value, -1);

                                if (compl.P1 != -1)
                                {
                                    compl.P1--;
                                }

                                if (compl.P2 != -1)
                                {
                                    compl.P2--;
                                }

                                break;
                            }
                            else if (cv.StartsWith("#Конец_Запрос", StringComparison.CurrentCultureIgnoreCase))
                            {
                                compl.R2 = row.RowNum;
                                complrows.Add(compl);

                                isComplex = false;
                                break;
                            }
                            else if (!isComplex)
                            {
                                InsertParam(cell);
                            }
                        }
                        catch
                        {
                        }
                    }
                }
            }

            #region запрос
            DataManager dm = new DataManager();
            dm.Person = person;

            List <dynamic> complexParams = dm.GetQData(name, drv, sql, args, -1).ToList();
            #endregion

            dynamic data;
            int     i, len = complexParams.Count() - 1,
                    rn, total;

            string sn = string.Empty;
            foreach (ComplexRows cr in complrows)
            {
                if (!sn.Equals(cr.S))
                {
                    sheet = wb.GetSheet(cr.S);
                    sn    = cr.S;
                }

                total = cr.TotalRows;

                if (cr.R2 == sheet.LastRowNum)
                {
                    sheet.CreateRow(cr.R2 + 1);
                }

                // удалить ряд 'Запрос' --> смещение -1
                sheet.RemoveRow(sheet.GetRow(cr.R1));
                sheet.ShiftRows(cr.R1 + 1, sheet.LastRowNum, -1);

                // удалить ряд 'Конец_Запрос' --> смещение -1
                cr.R2--;
                sheet.RemoveRow(sheet.GetRow(cr.R2));
                sheet.ShiftRows(cr.R2 + 1, sheet.LastRowNum, -1);

                if (cr.P2 == -1)
                {
                    cr.P2 = len;
                }

                for (i = cr.P1; i < cr.P2 - 1; ++i)
                {
                    data = complexParams[i];
                    for (rn = 0; rn < total; ++rn)
                    {
                        sheet.CopyRow(cr.R1, cr.R1 + total);
                        row = sheet.GetRow(cr.R1);

                        cells = row.GetEnumerator();
                        while (cells.MoveNext())
                        {
                            cell = (ICell)cells.Current;
                            InsertParam(cell, data);
                        }

                        cr.R1++;
                    }
                }

                // последний ряд
                data = complexParams[i];
                row  = sheet.GetRow(cr.R1);

                cells = row.GetEnumerator();
                while (cells.MoveNext())
                {
                    cell = (ICell)cells.Current;
                    InsertParam(cell, data);
                }
            }

            switch (repFormat)
            {
            case eOfficeFormat.OfficeXls:
                HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
                break;

            case eOfficeFormat.OfficeXml:
                XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
                break;
            }

            fs = new FileStream(repPath + repId + @"\" + path, FileMode.Create, FileAccess.Write);
            wb.Write(fs);
            fs.Close();

            return(path);
        }
Example #12
0
        public XmlDocument GetExcelXml(string file)
        {
            var xml = new XmlDocument();

            using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = null;
                ISheet    sheet    = null;

                if (file.IndexOf(".xlsx") > 0)
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (file.IndexOf(".xls") > 0)
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else
                {
                    MessageBox.Show("EXCEL文件格式错误");
                    return(null);
                }

                sheet = workbook.GetSheetAt(0);

                var node = xml.CreateNode(XmlNodeType.XmlDeclaration, "", "");
                xml.AppendChild(node);

                var root = xml.CreateNode(XmlNodeType.Element, "Root", "");
                xml.AppendChild(root);

                var header = xml.CreateNode(XmlNodeType.Element, "Header", "");
                root.AppendChild(header);

                var body = xml.CreateNode(XmlNodeType.Element, "Body", "");
                root.AppendChild(body);

                var date            = xml.CreateNode(XmlNodeType.Element, "HDate", "");
                var contractID      = xml.CreateNode(XmlNodeType.Element, "HContractID", "");
                var companyName     = xml.CreateNode(XmlNodeType.Element, "HCompanyName", "");
                var goodsName       = xml.CreateNode(XmlNodeType.Element, "HGoodsName", "");
                var specifications  = xml.CreateNode(XmlNodeType.Element, "HSpecifications", "");
                var singlePrice     = xml.CreateNode(XmlNodeType.Element, "HSinglePrice", "");
                var priceUnit       = xml.CreateNode(XmlNodeType.Element, "HPriceUnit", "");
                var buyCount        = xml.CreateNode(XmlNodeType.Element, "HBuyCount", "");
                var buyCountUnit    = xml.CreateNode(XmlNodeType.Element, "HBuyCountUnit", "");
                var totalPrice      = xml.CreateNode(XmlNodeType.Element, "HTotalPrice", "");
                var totalPriceUnit  = xml.CreateNode(XmlNodeType.Element, "HTotalPriceUnit", "");
                var buyType         = xml.CreateNode(XmlNodeType.Element, "HBuyType", "");
                var importanceLevel = xml.CreateNode(XmlNodeType.Element, "HImportanceLevel", "");
                var auditor         = xml.CreateNode(XmlNodeType.Element, "HAuditor", "");
                var reason          = xml.CreateNode(XmlNodeType.Element, "HReason", "");
                var description     = xml.CreateNode(XmlNodeType.Element, "HDescription", "");
                header.AppendChild(date);
                header.AppendChild(contractID);
                header.AppendChild(companyName);
                header.AppendChild(goodsName);
                header.AppendChild(specifications);
                header.AppendChild(singlePrice);
                header.AppendChild(priceUnit);
                header.AppendChild(buyCount);
                header.AppendChild(buyCountUnit);
                header.AppendChild(totalPrice);
                header.AppendChild(totalPriceUnit);
                header.AppendChild(buyType);
                header.AppendChild(importanceLevel);
                header.AppendChild(auditor);
                header.AppendChild(reason);
                header.AppendChild(description);

                var rows = sheet.GetEnumerator();
                while (rows.MoveNext())
                {
                    XmlNode data             = null;
                    XmlNode date1            = null;
                    XmlNode contractID1      = null;
                    XmlNode companyName1     = null;
                    XmlNode goodsName1       = null;
                    XmlNode specifications1  = null;
                    XmlNode singlePrice1     = null;
                    XmlNode priceUnit1       = null;
                    XmlNode buyCount1        = null;
                    XmlNode buyCountUnit1    = null;
                    XmlNode totalPrice1      = null;
                    XmlNode totalPriceUnit1  = null;
                    XmlNode buyType1         = null;
                    XmlNode importanceLevel1 = null;
                    XmlNode auditor1         = null;
                    XmlNode reason1          = null;
                    XmlNode description1     = null;

                    if (((IRow)rows.Current).RowNum > 0)
                    {
                        data = xml.CreateNode(XmlNodeType.Element, "Data", "");
                        body.AppendChild(data);

                        date1            = xml.CreateNode(XmlNodeType.Element, "Date", "");
                        contractID1      = xml.CreateNode(XmlNodeType.Element, "ContractID", "");
                        companyName1     = xml.CreateNode(XmlNodeType.Element, "CompanyName", "");
                        goodsName1       = xml.CreateNode(XmlNodeType.Element, "GoodsName", "");
                        specifications1  = xml.CreateNode(XmlNodeType.Element, "Specifications", "");
                        singlePrice1     = xml.CreateNode(XmlNodeType.Element, "SinglePrice", "");
                        priceUnit1       = xml.CreateNode(XmlNodeType.Element, "PriceUnit", "");
                        buyCount1        = xml.CreateNode(XmlNodeType.Element, "BuyCount", "");
                        buyCountUnit1    = xml.CreateNode(XmlNodeType.Element, "BuyCountUnit", "");
                        totalPrice1      = xml.CreateNode(XmlNodeType.Element, "TotalPrice", "");
                        totalPriceUnit1  = xml.CreateNode(XmlNodeType.Element, "TotalPriceUnit", "");
                        buyType1         = xml.CreateNode(XmlNodeType.Element, "BuyType", "");
                        importanceLevel1 = xml.CreateNode(XmlNodeType.Element, "ImportanceLevel", "");
                        auditor1         = xml.CreateNode(XmlNodeType.Element, "Auditor", "");
                        reason1          = xml.CreateNode(XmlNodeType.Element, "Reason", "");
                        description1     = xml.CreateNode(XmlNodeType.Element, "Description", "");
                        data.AppendChild(date1);
                        data.AppendChild(contractID1);
                        data.AppendChild(companyName1);
                        data.AppendChild(goodsName1);
                        data.AppendChild(specifications1);
                        data.AppendChild(singlePrice1);
                        data.AppendChild(priceUnit1);
                        data.AppendChild(buyCount1);
                        data.AppendChild(buyCountUnit1);
                        data.AppendChild(totalPrice1);
                        data.AppendChild(totalPriceUnit1);
                        data.AppendChild(buyType1);
                        data.AppendChild(importanceLevel1);
                        data.AppendChild(auditor1);
                        data.AppendChild(reason1);
                        data.AppendChild(description1);
                    }

                    var row = (IRow)rows.Current;
                    foreach (var cell in row.Cells)
                    {
                        if (row.RowNum == 0)
                        {
                            switch (cell.ColumnIndex)
                            {
                            case 0:
                                date.InnerText = cell.StringCellValue;
                                break;

                            case 1:
                                companyName.InnerText = cell.StringCellValue;
                                break;

                            case 2:
                                goodsName.InnerText = cell.StringCellValue;
                                break;

                            case 3:
                                specifications.InnerText = cell.StringCellValue;
                                break;

                            case 4:
                                singlePrice.InnerText = cell.StringCellValue;
                                break;

                            case 5:
                                buyCount.InnerText = cell.StringCellValue;
                                break;

                            case 6:
                                buyCountUnit.InnerText = cell.StringCellValue;
                                break;

                            case 7:
                                totalPrice.InnerText = cell.StringCellValue;
                                break;

                            case 8:
                                reason.InnerText = cell.StringCellValue;
                                break;

                            default:
                                break;
                            }
                        }
                        else
                        {
                            if (row.RowNum > 0)
                            {
                                switch (cell.ColumnIndex)
                                {
                                case 0:
                                    if (cell.CellType == CellType.Numeric)
                                    {
                                        date1.InnerText = cell.NumericCellValue.ToString();
                                    }
                                    else if (cell.CellType == CellType.String)
                                    {
                                        date1.InnerText = cell.StringCellValue;
                                    }
                                    break;

                                case 1:
                                    if (cell.CellType == CellType.Numeric)
                                    {
                                        companyName1.InnerText = cell.NumericCellValue.ToString();
                                    }
                                    else if (cell.CellType == CellType.String)
                                    {
                                        companyName1.InnerText = cell.StringCellValue;
                                    }
                                    break;

                                case 2:
                                    if (cell.CellType == CellType.Numeric)
                                    {
                                        goodsName1.InnerText = cell.NumericCellValue.ToString();
                                    }
                                    else if (cell.CellType == CellType.String)
                                    {
                                        goodsName1.InnerText = cell.StringCellValue;
                                    }
                                    break;

                                case 3:
                                    if (cell.CellType == CellType.Numeric)
                                    {
                                        specifications1.InnerText = cell.NumericCellValue.ToString();
                                    }
                                    else if (cell.CellType == CellType.String)
                                    {
                                        specifications1.InnerText = cell.StringCellValue;
                                    }
                                    break;

                                case 4:
                                    if (cell.CellType == CellType.Numeric)
                                    {
                                        singlePrice1.InnerText = cell.NumericCellValue.ToString();
                                    }
                                    else if (cell.CellType == CellType.String)
                                    {
                                        singlePrice1.InnerText = cell.StringCellValue;
                                    }
                                    break;

                                case 5:
                                    if (cell.CellType == CellType.Numeric)
                                    {
                                        buyCount1.InnerText = cell.NumericCellValue.ToString();
                                    }
                                    else if (cell.CellType == CellType.String)
                                    {
                                        buyCount1.InnerText = cell.StringCellValue;
                                    }
                                    break;

                                case 6:
                                    if (cell.CellType == CellType.Numeric)
                                    {
                                        buyCountUnit1.InnerText = cell.NumericCellValue.ToString();
                                    }
                                    else if (cell.CellType == CellType.String)
                                    {
                                        buyCountUnit1.InnerText = cell.StringCellValue;
                                    }
                                    break;

                                case 7:
                                    if (cell.CellType == CellType.Numeric)
                                    {
                                        totalPrice1.InnerText = cell.NumericCellValue.ToString();
                                    }
                                    else if (cell.CellType == CellType.String)
                                    {
                                        totalPrice1.InnerText = cell.StringCellValue;
                                    }
                                    break;

                                case 8:
                                    if (cell.CellType == CellType.Numeric)
                                    {
                                        reason1.InnerText = cell.NumericCellValue.ToString();
                                    }
                                    else if (cell.CellType == CellType.String)
                                    {
                                        reason1.InnerText = cell.StringCellValue;
                                    }
                                    break;

                                default:
                                    break;
                                }
                            }
                        }
                    }
                }
            }
            return(xml);
        }
Example #13
0
 public IEnumerator GetEnumerator()
 {
     return(_sheet.GetEnumerator());
 }