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