The correct method is to tell Excel exactly where in the worksheet to find your column headers and data. Importing the entire sheet and trying to reconstruct your headers from an arbitrary data row is asking for serious trouble. OPENQUERY does not guarantee row order. In testing it will appear to always import in order, but as soon as you move it to a system with a multi-volume tempdb or a heavily loaded production system, your imports will no longer be ordered, and your code will be trying to interpret your data as column headers.
instead of:
string query = String.Format("select * from [{0}$]", "Sheet1");
use:
string query = String.Format("select * from [{0}${1}]", "Sheet1","A2:ZZ");
EDIT: use "A2:end"
instead of "A2:ZZ"
.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…