WPF 简拼查询Excel
获取一个简体中文字的拼音首字母
static public string getSpell(string cn)
{
byte[] arrCN = Encoding.Default.GetBytes(cn);
if (arrCN.Length > 1)
{
int area = (short)arrCN[0];
int pos = (short)arrCN[1];
int code = (area << 8) + pos;
int[] areacode = { 45217, 45253, 45761, 46318, 46826, 47010, 47297, 47614, 48119, 48119, 49062, 49324, 49896, 50371, 50614, 50622, 50906, 51387, 51446, 52218, 52698, 52698, 52698, 52980, 53689, 54481 };
for (int i = 0; i < 26; i++)
{
int max = 55290;
if (i != 25) max = areacode[i + 1];
if (areacode[i] <= code && code < max)
{
return Encoding.Default.GetString(new byte[] { (byte)(65 + i) });
}
}
return "?";
}
else return cn;
}
}
获取字符串的拼音首字母
static public string getSpells(string input)
{
int len = input.Length;
string reVal = "";
for (int i = 0; i < len; i++)
{
reVal += getSpell(input.Substring(i, 1));
}
return reVal;
}
创建Excel数据源并查询数据
public static DataSet ExcelToDS(string Path, string tableName)
{
DataSet ds = null;
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", tableName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
conn.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return ds;
}
获取Excel的Sheet集合
public static String[] GetExcelSheetNames(string Path)
{
DataTable dt = null;
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
return null;
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach (DataRow row in dt.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString();
excelSheets[i] = strSheetTableName.Substring(0, strSheetTableName.Length - 1);
i++;
}
return excelSheets;
}
