Example #1
0
        private void CheckTmpl()
        {
            //验证Columns
            string row   = xmlConfig.Element("Columns").Attribute("Row").Value;
            var    query = xmlConfig.Element("Columns").Elements("Column");

            foreach (var item in query)
            {
                string name = item.Attribute("Name").Value;
                string col  = item.Attribute("Col").Value;

                string colName = "";
                while (colName == "")
                {
                    colName = GetExcelData(row, col);
                    if (colName == "Error")
                    {
                        CheckResultItem checkItem = new CheckResultItem();
                        checkItem.Msg = "配置文件的列数与模板文件不一致,请检查!";
                        _listTmplCheck.Add(checkItem);
                        return;
                    }
                    int x = int.Parse(row);
                    if (x <= 1)
                    {
                        break;
                    }
                    x--;
                    row = x.ToString();
                }

                row = xmlConfig.Element("Columns").Attribute("Row").Value;
                if (name != colName)
                {
                    // return false;
                    CheckResultItem checkItem = new CheckResultItem();
                    //checkItem.Col = int.Parse(col);
                    //checkItem.Row = int.Parse(row);
                    checkItem.Msg = string.Format("{0}{1}为:{2},配置文件为:{3}", row, col, colName, name);
                    _listTmplCheck.Add(checkItem);
                }
            }


            //验证Items
            //query = xmlConfig.Element("Items").Elements("Item");
            //foreach (var item in query)
            //{
            //    row = item.Attribute("Row").Value;
            //    string col = item.Attribute("Col").Value;
            //    GetExcelData(row, col);
            //}

            //return true;
        }
Example #2
0
        public ExcelIOHelper(string tmplCode, string excelFileName)
        {
            XElement xEle = XElement.Load(System.Configuration.ConfigurationManager.AppSettings["ServerMapPath"] + "/ExcelTmplConfig.xml");

            xmlConfig = (from item in xEle.Elements("ExcelTmpl")
                         where item.Attribute("Code").Value == tmplCode
                         select item).First();


            string excelFilePath = excelRootPath + excelFileName;
            string sheetName     = xmlConfig.Attribute("SheetName").Value;

            try
            {
                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + excelFilePath + "';User ID=Admin;Mode=Share Deny None;Extended Properties='Excel 12.0 xml;HDR=No;IMEX=1'";
                // 林飞 2010-10-26 修改连接Excel的字符串,根据判断不同后缀名来使用不同的连接字符串
                if (excelFilePath.ToLower().EndsWith("xls"))
                {
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + excelFilePath + "';Extended Properties='Excel 8.0; HDR=yes; IMEX=1;'";
                }

                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                DataTable table = OleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);

                #region 判断Sheet页是否存在
                bool existSheet = false;
                foreach (DataRow row in table.Rows)
                {
                    string tableName = row["Table_Name"].ToString().Trim('$');
                    if (tableName == sheetName)
                    {
                        existSheet = true;
                        break;
                    }
                }
                if (existSheet == false)
                {
                    CheckResultItem item = new CheckResultItem();
                    item.Msg = "sheet名称错误,可能是模板不对。";
                    _listTmplCheck.Add(item);
                    dtExcel = new DataTable();
                    return;
                }
                #endregion



                String sql = string.Format("SELECT * FROM  [{0}$]", sheetName);
                //可是更改Sheet名称,比如sheet2,等等
                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet          OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, "aa");
                OleConn.Close();
                dtExcel = OleDsExcle.Tables[0];
                string firstRow = xmlConfig.Element("Columns").FirstAttribute.Value;
                // 计算开始行
                int    firstColumnIndex = GetCol(xmlConfig.Element("Columns").Elements("Column").First().Attribute("Col").Value);
                string firstColumnName  = xmlConfig.Element("Columns").Elements("Column").First().Attribute("Name").Value;
                if (firstRow != "1")//处理两行标题的情况(为往Excel中写数据,第一行为标题行,第二个标题行下边为数据行。)
                {
                    for (int j = 1; j < dtExcel.Rows.Count; j++)
                    {
                        // 如果该行的第一个Column的文本与XML配置中的列名一致就为标题行。
                        if (dtExcel.Rows[j][firstColumnIndex].ToString() == firstColumnName)
                        {
                            intStartRowIndex = j + 1;
                            break;
                        }
                    }
                }
                else
                {
                    intStartRowIndex = 1;
                }
                // 计算结束行
                string endRowFlag = xmlConfig.Attribute("EndRowFlag").Value;
                bool   flag       = false;
                for (int i = dtExcel.Rows.Count - 1; i >= 0; i--)
                {
                    DataRow dataRow = dtExcel.Rows[i];
                    for (int j = 0; j < dtExcel.Columns.Count; j++)
                    {
                        if (dataRow[j].ToString().Trim().StartsWith(endRowFlag.Trim()))
                        {
                            flag = true;
                            break;
                        }
                    }
                    if (flag)
                    {
                        intEndRowIndex = i;
                        break;
                    }
                }

                if (intEndRowIndex == 0)
                {
                    CheckResultItem item = new CheckResultItem();
                    item.Msg = "Excel表中没有“注:结束 ”标记";
                    _listTmplCheck.Add(item);
                }
                //检测模板
                CheckTmpl();
            }
            catch (Exception err)
            {
                throw err;
            }
        }