获取Excel Sheet名称,对特殊字符、重名进行了处理代码,代码库,德仔网

代码语言

代码分类

【】获取Excel Sheet名称,对特殊字符、重名进行了处理

作者:艳双 / 发布于2015/3/24/ 963


	/// <summary>
	 /// 获取指定Excel内Sheet集合
	 /// </summary>
	 /// <param name="pFilePath"></param>
	 /// <param name="pOutInfo"></param>
	 /// <returns></returns>
	 public static string[] GetExcelSheetNames(string pFilePath, out string pOutInfo)
	 {
	 string vOutInfo = string.Empty;
	 List<string> vList = new List<string>();
	 try
	 {
	 string strConn = string.Format("Provider=Microsoft.Ace.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=2'", pFilePath);
	 OleDbConnection conn = new OleDbConnection(strConn);
	 conn.Open();
	 DataTable sheetNames = conn.GetOleDbSchemaTable
	 (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
	 conn.Close();

	 string[] vSheets = new string[sheetNames.Rows.Count];
	 string vName = string.Empty;

	 //填充 vSheets 数组
	 for (int i = 0; i < sheetNames.Rows.Count; i++)
	 {
	 vSheets[i] = sheetNames.Rows[i][2].ToString().Trim();
	 }
	 //对特殊字符进行规范处理
	 string pSheetName = string.Empty;
	 for (int i = 0; i < vSheets.Length; i++)
	 {
	 string pStart = vSheets[i].Substring(0, 1);
	 string pEnd = vSheets[i].Substring(vSheets[i].Length - 1, 1);
	 if (pStart == "'" && pEnd == "'")
	 {
	 vSheets[i] = vSheets[i].Substring(1, vSheets[i].Length - 2);
	 }
	 Char[] pChar = vSheets[i].ToCharArray();
	 pSheetName = string.Empty;
	 for (int j = 0; j < pChar.Length; j++)
	 {
	 if (pChar[j].ToString() == "'" && pChar[j + 1].ToString() == "'")
	 {
	 pSheetName += pChar[j].ToString();
	 j++;
	 }
	 else
	 {
	 pSheetName += pChar[j].ToString();
	 }
	 }
	 vSheets[i] = pSheetName;
	 }
	 //当最后字符为$时移除
	 for (int i = 0; i < vSheets.Length; i++)
	 {
	 pSheetName = vSheets[i];
	 if (pSheetName.Substring(pSheetName.Length - 1, 1) == "$")
	 {
	 vSheets[i] = pSheetName.Substring(0, pSheetName.Length - 1);
	 }
	 }
	 //移除重复的Sheet名(因为特殊原因，通过这个方法获取的Sheet会有重名)
	 for (int i = 0; i < vSheets.Length; i++)
	 {
	 if (vList.IndexOf(vSheets[i].ToLower()) == -1)
	 {
	 vList.Add(vSheets[i]);
	 }
	 }
	 }
	 catch (Exception vErr)
	 {
	 vOutInfo = vErr.Message;
	 }
	 pOutInfo = vOutInfo;
	 return vList.ToArray();
	 }

试试其它关键字

　Excel　

同语言下

可能有用的

艳双贡献的其它代码(11)