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