关于C#读取Excel表中sheet1为任意名字的问题
希望读取excel文件的sheet1,sheet1可以为任意名字,参考了一个如下的代码,可以实现。但是当sheet1的名字过长的时候就报错了,请教一下修改哪里可以使读取35个字符以内的sheet1名称。新手编程,谢谢了
private static string GetSheetName(string filePath)
{
string sheetName = "";
System.IO.FileStream tmpStream = File.OpenRead(filePath);
byte[] fileByte = new byte[tmpStream.Length];
tmpStream.Read(fileByte, 0, fileByte.Length);
tmpStream.Close();
byte[] tmpByte = new byte[]{Convert.ToByte(11),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),
Convert.ToByte(11),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),
Convert.ToByte(30),Convert.ToByte(16),Convert.ToByte(0),Convert.ToByte(0)};
int index = GetSheetIndex(fileByte, tmpByte);
if (index > -1)
{
index += 16 + 12;
System.Collections.ArrayList sheetNameList = new System.Collections.ArrayList();
for (int i = index; i < fileByte.Length - 1; i++)
{
byte temp = fileByte[i];
if (temp != Convert.ToByte(0))
sheetNameList.Add(temp);
else
break;
}
byte[] sheetNameByte = new byte[sheetNameList.Count];
for (int i = 0; i < sheetNameList.Count; i++)
sheetNameByte[i] = Convert.ToByte(sheetNameList[i]);
sheetName = System.Text.Encoding.Default.GetString(sheetNameByte);
}
return sheetName;
}
/// <summary>
/// 只供方法GetSheetName()使用
/// </summary>
/// <returns></returns>
private static int GetSheetIndex(byte[] FindTarget, byte[] FindItem)
{
int index = -1;
int FindItemLength = FindItem.Length;
if (FindItemLength < 1) return -1;
int FindTargetLength = FindTarget.Length;
if ((FindTargetLength - 1) < FindItemLength) return -1;
for (int i = FindTargetLength - FindItemLength - 1; i > -1; i--)
{
System.Collections.ArrayList tmpList = new System.Collections.ArrayList();
int find = 0;
for (int j = 0; j < FindItemLength; j++)
{
if (FindTarget[i + j] == FindItem[j]) find += 1;
}
if (find == FindItemLength)
{
index = i;
break;
}
}
return index;
}
[最优解释]
....没这么麻烦吧,给你个类,直接来获取sheet名,然后......你应该搞的定了
我用你的sheet1的名字是“ArgosData_2012_11_23_08_32_09”试过,没问题
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
using System.Runtime;
using System.IO;
namespace TXRECE
{
/// <summary>
/// 获取Excel中表的名称
/// </summary>
/// <param name="p_ExcelFile">Excel的路径</param>
/// <returns></returns>
class sheet
{
public static DataTable GetExcelTableName(string p_ExcelFile)
{
try
{
if (System.IO.File.Exists(p_ExcelFile))
{
OleDbConnection _ExcelConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0";Data Source=" + p_ExcelFile);
_ExcelConn.Open();
DataTable _Table = _ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
_ExcelConn.Close();
return _Table;
}
return null;
}
catch
{
return null;
}
}
}
}