在代码写的导出的或生成的excel再用代码导入时报错：外部表不是预期的格式

外部表不是预期格式：

excel在系统中新自己用office建的 ----------右击 打开方式 记事本    打开后是乱码

用代码导出的excel文件               ----------右击 打开方式 记事本    打开后是xml格式的文本

 

具体的文件：

<?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Version>11.6360</Version> </DocumentProperties> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>10005</WindowHeight> <WindowWidth>10005</WindowWidth> <WindowTopX>120</WindowTopX> <WindowTopY>135</WindowTopY> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Center"/> <Borders/> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="s21"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font ss:FontName="宋体" ss:Size="11"/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="s24"> <Alignment ss:Vertical="Center"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#969696"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#969696"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#969696"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#969696"/> </Borders> <Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8"/> <Interior/> <NumberFormat ss:Format="@"/> <Protection/> </Style> <Style ss:ID="s26"> <Alignment ss:Horizontal="Center" ss:Vertical="Center"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#969696"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#969696"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#969696"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#969696"/> </Borders> <Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8" ss:Color="#FFFFFF"/> <Interior ss:Color="#666699" ss:Pattern="Solid"/> <NumberFormat ss:Format="@"/> <Protection/> </Style> </Styles> <Worksheet ss:Name="Sheet1"> <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="3" x:FullColumns="1" x:FullRows="1" ss:StyleID="s21" ss:DefaultColumnWidth="54"> <Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="69.75" ss:Span="2"/> <Row ss:AutoFitHeight="0" ss:Height="14.25"> <Cell ss:StyleID="s26"><Data ss:Type="String" x:Ticked="1"> 考号</Data></Cell> <Cell ss:StyleID="s26"><Data ss:Type="String" x:Ticked="1"> 姓名</Data></Cell> <Cell ss:StyleID="s26"><Data ss:Type="String" x:Ticked="1"> 性别</Data></Cell> </Row> <Row ss:AutoFitHeight="0" ss:Height="14.25"> <Cell ss:StyleID="s24"><Data ss:Type="Number">20100101</Data></Cell> <Cell ss:StyleID="s24"><Data ss:Type="String"> 郎寒安</Data></Cell> <Cell ss:StyleID="s24"><Data ss:Type="String"> 男</Data></Cell> </Row> <Row ss:AutoFitHeight="0" ss:Height="14.25"> <Cell ss:StyleID="s24"><Data ss:Type="Number">20100102</Data></Cell> <Cell ss:StyleID="s24"><Data ss:Type="String"> 吕冬儿</Data></Cell> <Cell ss:StyleID="s24"><Data ss:Type="String"> 女</Data></Cell> </Row> </Table> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <Unsynced/> <Selected/> <Panes> <Pane> <Number>3</Number> <ActiveRow>12</ActiveRow> <ActiveCol>11</ActiveCol> </Pane> </Panes> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> <x:ViewableRange>R1:R262144</x:ViewableRange> <x:Selection>R1C1</x:Selection> </WorksheetOptions> <c:WorksheetOptions> </c:WorksheetOptions> </Worksheet> <Worksheet ss:Name="Sheet2"> <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1" x:FullRows="1" ss:StyleID="s21" ss:DefaultColumnWidth="54"/> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <Unsynced/> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> <x:ViewableRange>R1:R262144</x:ViewableRange> <x:Selection>R1C1</x:Selection> </WorksheetOptions> <c:WorksheetOptions> </c:WorksheetOptions> </Worksheet> <Worksheet ss:Name="Sheet3"> <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1" x:FullRows="1" ss:StyleID="s21" ss:DefaultColumnWidth="54"/> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <Unsynced/> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> <x:ViewableRange>R1:R262144</x:ViewableRange> <x:Selection>R1C1</x:Selection> </WorksheetOptions> <c:WorksheetOptions> </c:WorksheetOptions> </Worksheet> <c:ComponentOptions> <c:Label> <c:Caption>Microsoft Office 电子表格</c:Caption> </c:Label> <c:MaxHeight>80%</c:MaxHeight> <c:MaxWidth>80%</c:MaxWidth> <c:NextSheetNumber>4</c:NextSheetNumber> </c:ComponentOptions> <x:WorkbookOptions> <c:OWCVersion>12.0.0.4518 </c:OWCVersion> </x:WorkbookOptions> </Workbook>  

 

此时用excel作为数据源放到dataset中用excel的方式就会出现错误：外部表不是预期格式

 

 

此时，可以用读取xml的方式进行文件的读取，自己写的代码如下：

 

XmlDocument xml = new XmlDocument(); xml.Load(@"M:/测试数据.xls"); //选择所有的row节点 XmlNodeList model = xml.GetElementsByTagName("ss:Row"); DataTable dt = new DataTable(); //循环获取每个row节点的子节点cell XmlNodeList cell; foreach (XmlElement xe in model) { cell = xe.GetElementsByTagName("ss:Cell"); AddRowToTb(cell, dt); } // //这时dt中存放的便是最终的数据了 //自己写其他的数据处理代码  

 

用到的方法代码如下：

/// <summary> /// 循环XmlNodeList的每一项并存放到dt中 /// </summary> /// <param name="cell">XmlNodeList 行</param> /// <param name="dt">要存放的datatable</param> public void AddRowToTb(XmlNodeList cell,DataTable dt) { if (dt.Columns.Count ==0)/*生成表头*/ { for (int i = 0; i < cell.Count; i++) { dt.Columns.Add(cell[i].InnerText.Replace(" ",""), typeof(string)); } } else/*读取表内容*/ { DataRow newRow= dt.NewRow(); for (int i = 0; i < cell.Count; i++) { newRow[i] = cell[i].InnerText.Replace(" ",""); } dt.Rows.Add(newRow); dt.AcceptChanges(); } }